表2.11字段名定义表 字段名 字段说明 字段名 字段说明 Student_no 学号 Credit 学分 Student_name 姓名 Classhour 学时 Sex 性别 Student_no 学号 Birth 出生年月 Course_no 课程编号 Enter_date 入学年份 Ordinary_scores 平时成绩 Address 家庭住址 End_scores 期末成绩 Course_no 课程编号 Total_scores 总评成绩 Course_name 课程名 3 数据库操作 3.1 创建数据库 创建数据库Student。 3.2 创建数据表 根据图2.26和表2.11，创建数据表T_student_info、T_course_info 、T_student_scores。 3.3 创建数据表间的关系 根据物理数据原型，创建数据关系。 3.4 数据操作 用SQL语句完成如下操作： 向T_student_info表插入数据： “102011，刘德华，男，03/23/1991，2009，湖南长沙劳动东路168号”； 查询出1991年出生的学生信息； 查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩； 创建视图查询学生的学号、姓名、课程名、总评成绩； 统计每门课程的选课人数、最高分、平均分，并将统计结果保存在表T_total中。

以下是实现上述需求的 SQL 语句：

1. 创建数据库

CREATE DATABASE Student;

2. 创建数据表 T_student_info

CREATE TABLE T_student_info (
  Student_no CHAR(6) PRIMARY KEY,
  Student_name VARCHAR(20),
  Sex VARCHAR(2),
  Birth DATE,
  Enter_date CHAR(4),
  Address VARCHAR(50)
);

3. 创建数据表 T_course_info

CREATE TABLE T_course_info (
  Course_no CHAR(6) PRIMARY KEY,
  Course_name VARCHAR(20),
  Credit NUMERIC(3, 1),
  Classhour NUMERIC(3, 0)
);

4. 创建数据表 T_student_scores

CREATE TABLE T_student_scores (
  Student_no CHAR(6),
  Course_no CHAR(6),
  Ordinary_scores NUMERIC(4, 1),
  End_scores NUMERIC(4, 1),
  Total_scores NUMERIC(4, 1),
  PRIMARY KEY (Student_no, Course_no),
  FOREIGN KEY (Student_no) REFERENCES T_student_info (Student_no),
  FOREIGN KEY (Course_no) REFERENCES T_course_info (Course_no)
);

5. 创建数据表间的关系

-- 一个学生可以选多门课程
ALTER TABLE T_student_scores ADD CONSTRAINT FK_T_student_scores_1
  FOREIGN KEY (Student_no) REFERENCES T_student_info (Student_no);

-- 一门课程可以被多个学生选
ALTER TABLE T_student_scores ADD CONSTRAINT FK_T_student_scores_2
  FOREIGN KEY (Course_no) REFERENCES T_course_info (Course_no);

6. 向 T_student_info 表插入数据

INSERT INTO T_student_info (Student_no, Student_name, Sex, Birth, Enter_date, Address)
VALUES ('102011', '刘德华', '男', '1991-03-23', '2009', '湖南长沙劳动东路168号');

7. 查询出 1991 年出生的学生信息

SELECT *
FROM T_student_info
WHERE YEAR(Birth) = 1991;

8. 查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩

SELECT T_student_info.Student_name, T_student_scores.Ordinary_scores, T_student_scores.End_scores, T_student_scores.Total_scores
FROM T_student_info
JOIN T_student_scores ON T_student_info.Student_no = T_student_scores.Student_no
JOIN T_course_info ON T_student_scores.Course_no = T_course_info.Course_no
WHERE T_course_info.Course_name = '数据结构';

9. 创建视图查询学生的学号、姓名、课程名、总评成绩

CREATE VIEW V_student_scores AS
SELECT T_student_info.Student_no, T_student_info.Student_name, T_course_info.Course_name, T_student_scores.Total_scores
FROM T_student_info
JOIN T_student_scores ON T_student_info.Student_no = T_student_scores.Student_no
JOIN T_course_info ON T_student_scores.Course_no = T_course_info.Course_no;

10. 统计每门课程的选课人数、最高分、平均分，并将统计结果保存在表 T_total 中

CREATE TABLE T_total (
  Course_no CHAR(6) PRIMARY KEY,
  Num_selected INT,
  Max_score NUMERIC(4, 1),
  Avg_score NUMERIC(4, 1)
);

INSERT INTO T_total (Course_no, Num_selected, Max_score, Avg_score)
SELECT T_student_scores.Course_no, COUNT(*), MAX(T_student_scores.Total_scores), AVG(T_student_scores.Total_scores)
FROM T_student_scores
GROUP BY T_student_scores.Course_no;