创建数据表

学生基本信息表student

字段名 字段类型 约束条件 字段含义说明
sno Varchar2(8) Primary key 学号
sname Varchar2(8) Not null 姓名
ssex Varchar2(2) ‘男’或’女’ 性别
sbirthday date 出生日期
classno Varchar2(6) Foreign key 班级号
Totalcredit Smallint 初值为0 已选修的总学分 
1
2
3
4
5
6
7
8
9
CREATE TABLE student (
    sno VARCHAR(8) PRIMARY KEY COMMENT '学号',
    sname VARCHAR(8) NOT NULL COMMENT '姓名',
    ssex VARCHAR(2) CHECK (ssex IN ('男', '女')) COMMENT '性别',
    sbirthday DATE COMMENT '出生日期',
    classno VARCHAR(6) COMMENT '班级号',
    Totalcredit SMALLINT DEFAULT 0 COMMENT '已选修的总学分',
    FOREIGN KEY (classno) REFERENCES class(classno)
);

班级信息表class

字段名 字段类型 约束条件 字段含义说明
classno Varchar2(6) Primary key 班级号
classname Varchar(20) Not null 班级名称
classmajor Varchar2(20) 所属专业
classdept Varchar2(20) 所属系别
studentnumber smallint [20…40] 学生数 
1
2
3
4
5
6
7
CREATE TABLE class (
    classno VARCHAR(6) PRIMARY KEY COMMENT '班级号',
    classname VARCHAR(20) NOT NULL COMMENT '班级名称',
    classmajor VARCHAR(20) COMMENT '所属专业',
    classdept VARCHAR(20) COMMENT '所属系别',
    studentnumber SMALLINT CHECK (studentnumber BETWEEN 20 AND 40) COMMENT '学生数'
);

课程信息表course

字段名 字段类型 约束条件 字段含义说明
cno Varchar2 (6) Primary key 课程号
cname Varchar(30) Not null 课程名称
ccredit smallint [1…4] 学分 
1
2
3
4
5
CREATE TABLE course (
    cno VARCHAR(6) PRIMARY KEY COMMENT '课程号',
    cname VARCHAR(30) NOT NULL COMMENT '课程名称',
    ccredit SMALLINT CHECK (ccredit BETWEEN 1 AND 4) COMMENT '学分'
);

选修课程信息表sc

字段名 字段类型 约束条件 字段含义说明
sno varchar (8) Foreign key 学号
cno varchar (6) Foreign key 课程号
grade integer [0…100] 成绩 
1
2
3
4
5
6
7
CREATE TABLE sc (
    sno VARCHAR(8) COMMENT '学号',
    cno VARCHAR(6) COMMENT '课程号',
    grade INTEGER CHECK (grade BETWEEN 0 AND 100) COMMENT '成绩',
    FOREIGN KEY (sno) REFERENCES student(sno),
    FOREIGN KEY (cno) REFERENCES course(cno)
);

教师信息表teacher

字段名 字段类型 约束条件 字段含义说明
tno Number(6) Primary key 教师号
tname Varchar2(8) Not null 教师姓名
tsex Varchar2(2) ‘男’或’女’ 性别
tbirthday date 出生日期
ttitle Varchar2(20) 职称 
1
2
3
4
5
6
7
CREATE TABLE teacher (
    tno INT(6) PRIMARY KEY COMMENT '教师号',
    tname VARCHAR(8) NOT NULL COMMENT '教师姓名',
    tsex VARCHAR(2) CHECK (tsex IN ('男', '女')) COMMENT '性别',
    tbirthday DATE COMMENT '出生日期',
    ttitle VARCHAR(20) COMMENT '职称'
);

教师任课情况表teaching

字段名 字段类型 约束条件 字段含义说明
tno Number(6) Foreign key 教师号
cno Varchar (6) Foreign key 课程号
language Varchar2 (10) Chinese, Bilingual, English 授课语言 
1
2
3
4
5
6
7
CREATE TABLE teaching (
    tno INT(6) COMMENT '教师号',
    cno VARCHAR(6) COMMENT '课程号',
    language VARCHAR(10) CHECK (language IN ('Chinese', 'Bilingual', 'English')) COMMENT '授课语言',
    FOREIGN KEY (tno) REFERENCES teacher(tno),
    FOREIGN KEY (cno) REFERENCES course(cno)
);

插入数据

表student

sno sname ssex sbirthday classno
08300010 李在 男 1991-10-1 Rj0801
08300012 葛畅 男 1990-8-8 Rj0801
08300015 刘晶 女 1990-5-22 Rj0801
08300020 杨敏 女 1989-1-8 Rj0801
08300030 胡贤斌 男 1990-10-8 Rj0801
08300048 赵鸿泽 男 1989-6-6 Rj0802
08300050 王威 男 1990-6-10 Rj0802
08300067 赵玮 女 1990-8-21 Rj0803
08300075 王娜娜 女 1991-9-23 Rj0803
08300088 秦键 男 1989-3-1 Rj0803
08300100 田邦仪 女 1990-2-26 Rj0804
08300148 赵心砚 男 1991-4-25 Rj0805
08300150 杨青 女 1989-11-15 Rj0805
08300160 杨玲玲 女 1990-12-12 Rj0806 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO student (sno, sname, ssex, sbirthday, classno) VALUES
('08300010', '李在', '男', '1991-10-1', 'Rj0801'),
('08300012', '葛畅', '男', '1990-8-8', 'Rj0801'),
('08300015', '刘晶', '女', '1990-5-22', 'Rj0801'),
('08300020', '杨敏', '女', '1989-1-8', 'Rj0801'),
('08300030', '胡贤斌', '男', '1990-10-8', 'Rj0801'),
('08300048', '赵鸿泽', '男', '1989-6-6', 'Rj0802'),
('08300050', '王威', '男', '1990-6-10', 'Rj0802'),
('08300067', '赵玮', '女', '1990-8-21', 'Rj0803'),
('08300075', '王娜娜', '女', '1991-9-23', 'Rj0803'),
('08300088', '秦键', '男', '1989-3-1', 'Rj0803'),
('08300100', '田邦仪', '女', '1990-2-26', 'Rj0804'),
('08300148', '赵心砚', '男', '1991-4-25', 'Rj0805'),
('08300150', '杨青', '女', '1989-11-15', 'Rj0805'),
('08300160', '杨玲玲', '女', '1990-12-12', 'Rj0806');

表class

classno classname Classs-major classdept studentnumber
Rj0801 软件0801 软件工程 软件开发 24
Rj0802 软件0802 软件工程 软件开发 26
Rj0803 软件0803 软件工程 数字媒体 25
Rj0804 软件0804 软件工程 软件开发 25
Rj0805 软件0805 软件工程 数字媒体 24
Rj0806 软件0806 软件工程 软件开发 24 
1
2
3
4
5
6
7
INSERT INTO class (classno, classname, Classs-major, classdept, studentnumber) VALUES
('Rj0801', '软件0801', '软件工程', '软件开发', 24),
('Rj0802', '软件0802', '软件工程', '软件开发', 26),
('Rj0803', '软件0803', '软件工程', '数字媒体', 25),
('Rj0804', '软件0804', '软件工程', '软件开发', 25),
('Rj0805', '软件0805', '软件工程', '数字媒体', 24),
('Rj0806', '软件0806', '软件工程', '软件开发', 24);

表course

cno cname credit
800001 计算机基础 4
800002 程序设计语言 4
800003 数据结构 4
810011 数据库系统 4
810013 计算机网络 3
810015 微机原理与应用 4 
1
2
3
4
5
6
7
INSERT INTO course (cno, cname, credit) VALUES
(800001, '计算机基础', 4),
(800002, '程序设计语言', 4),
(800003, '数据结构', 4),
(810011, '数据库系统', 4),
(810013, '计算机网络', 3),
(810015, '微机原理与应用', 4);

表sc

sno cno grade
08300012 800003 88
08300015 800003
08300020 800003 91
08300030 800003 78
08300048 800003 95
08300100 810011 67
08300148 810011 58
08300150 810011 89
08300160 810011 71 
1
2
3
4
5
6
7
8
9
10
INSERT INTO sc (sno, cno, grade) VALUES
('08300012', 800003, 88),
('08300015', 800003, NULL),
('08300020', 800003, 91),
('08300030', 800003, 78),
('08300048', 800003, 95),
('08300100', 810011, 67),
('08300148', 810011, 58),
('08300150', 810011, 89),
('08300160', 810011, 71);

表teacher

tno tname tsex tbirthday ttitle
000001 李英 女 1975-11-3 讲师
000002 王大山 男 1969-3-2 副教授
000003 张朋 男 1970-2-13 讲师
000004 陈为军 男 1985-8-14 助教
000005 宋浩然 男 1976-4-23 讲师
000006 许红霞 女 1966-2-12 副教授
000007 徐永军 男 1962-1-24 教授
000008 李桂菁 女 1960-12-15 教授
000009 王一凡 女 1974-12-8 讲师
000010 田峰 男 1988-1-18 助教 
1
2
3
4
5
6
7
8
9
10
11
INSERT INTO teacher (tno, tname, tsex, tbirthday, ttitle) VALUES
('000001', '李英', '女', '1975-11-3', '讲师'),
('000002', '王大山', '男', '1969-3-2', '副教授'),
('000003', '张朋', '男', '1970-2-13', '讲师'),
('000004', '陈为军', '男', '1985-8-14', '助教'),
('000005', '宋浩然', '男', '1976-4-23', '讲师'),
('000006', '许红霞', '女', '1966-2-12', '副教授'),
('000007', '徐永军', '男', '1962-1-24', '教授'),
('000008', '李桂菁', '女', '1960-12-15', '教授'),
('000009', '王一凡', '女', '1974-12-8', '讲师'),
('000010', '田峰', '男', '1988-1-18', '助教');

表teaching

cno tno Language
800001 000001 English
800002 000002 Chinese
800003 000002 Bilingual
810011 000003 Chinese
810013 000004 English
800001 000005 Chinese
800002 000006 Chinese
800003 000007 English
810011 000007 English
810013 000008 Bilingual 
1
2
3
4
5
6
7
8
9
10
11
INSERT INTO teaching (cno, tno, language) VALUES
(800001, 1, 'English'),
(800002, 2, 'Chinese'),
(800003, 2, 'Bilingual'),
(810011, 3, 'Chinese'),
(810013, 4, 'English'),
(800001, 5, 'Chinese'),
(800002, 6, 'Chinese'),
(800003, 7, 'English'),
(810011, 7, 'English'),
(810013, 8, 'Bilingual');

简单的数据操作

1

  • [x] 查询所有同学的所有基本信息。
1
SELECT * FROM student;

2

  • [x] 查询所有男同学的学号、姓名、出生日期。
1
SELECT sno, sname, sbirthday FROM student WHERE ssex = '男';

3

  • [x] 在基本表 student 中增加 addr：varchar(20) 列，然后将其长度由 20 改为 25。
1
2
3
ALTER TABLE student ADD COLUMN addr VARCHAR(20) comment "地址";     -- 添加
ALTER TABLE student MODIFY COLUMN addr VARCHAR(25) comment "地址";  -- 修改
ALTER TABLE student DROP COLUMN addr;   -- 删除

4

  • [x] 在基本表 student 中增加 register_date：date 列，并为其设置默认值为当前系统时间，再删除该列。
1
2
ALTER TABLE student ADD register_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '日期';
ALTER TABLE student DROP COLUMN register_date;

5

  • [x] 在基本表 student 中为 age 列，增加默认值为 18。
1
ALTER TABLE student MODIFY COLUMN age INT DEFAULT 18;

6

  • [x] 在基本表 sc 中将 sname 设置为唯一值（unique）。（应该是student表）
1
2
ALTER TABLE sc ADD CONSTRAINT unique_sname UNIQUE (sname);
ALTER TABLE sc ADD CONSTRAINT unique_sname UNIQUE (sname); -- 删除唯一索引

7

  • [x] 在基本表 course 中创建索引：（sno，cno desc）。（应该是sc表）
1
2
3
CREATE INDEX idx_sno_cno_desc ON course (sno, cno DESC);

-- 删除受到外键影响

8

  • [x] 在基本表 student 中增加约束条件：男生年龄小于 23 岁，女生年龄小于 21 岁。
1
2
3
4
5
ALTER TABLE student CHANGE sbirthday sage INT;
ALTER TABLE student ADD CONSTRAINT check_age CHECK (
    (ssex = '男' AND sage < 23) OR
    (ssex = '女' AND sage < 21)
);

9

  • [x] 创建视图 View_80，存放成绩高于 80 分的选课信息，显示学号、课程号和成绩，使用 WITH CHECK OPTION 选项。
1
2
3
CREATE VIEW View_80 AS
SELECT * FROM sc WHERE grade > 80
WITH CHECK OPTION;

10

  • [x] 在视图 View_80 中查询成绩高于 90 的选课信息。
1
SELECT * FROM View_80 WHERE grade > 90;

11

  • [x] 在视图 View_80 中依次插入如下元组：08301168，810011，87（可插入成功）

这个错误是由于外键约束导致的。在尝试向View_80视图插入数据时，MySQL检查了sc表的外键约束sc_ibfk_1。这个约束要求，sc表中的sno字段的值必须在student表的sno字段的值中存在。换句话说，你不能为一个不存在的学生分配成绩。

错误信息表明，你尝试插入的sno值（‘08301168’）在student表中不存在。你需要先确保student表中存在这个学生号，然后再尝试插入数据。

你可以使用以下SQL查询来检查student表中是否存在这个学生号：

1
SELECT * FROM student WHERE sno = '08301168';

如果这个查询没有返回任何结果，那么你需要先向student表中插入这个学生号，然后再尝试向View_80插入数据。

12

  • [x] 在视图 View_80 中依次修改如下元组：将（08301168，810011）所对应的成绩改为 90（可修改成功）
1
UPDATE View_80 SET grade = 90 WHERE sno = '08301168' AND cno = '810011';

13

  • [x] 在视图 View_80 中删除如下元组：sno=08301168，cno=810011
1
DELETE FROM View_80 WHERE sno = '08301168' AND cno = '810011';

14

  • [x] 查询所有在“1980-01-01”之前出生的女同学的学号、姓名、性别、出生日期。
1
2
3
SELECT sno, sname, ssex, sbirthday 
FROM student 
WHERE sbirthday < '1980-01-01' AND ssex = '女';

15

  • [x] 查询所有姓“李”的男同学的学号、姓名、性别、出生日期。
1
2
3
SELECT sno, sname, ssex, sbirthday 
FROM student 
WHERE sname LIKE '李%' AND ssex = '男';

16

  • [x] 查询所有用英文授课的教师号、姓名及英语授课的门数。
1
2
3
4
5
SELECT t.tno, t.tname, COUNT(te.cno) AS '英语授课的门数'
FROM teacher t
JOIN teaching te ON t.tno = te.tno
WHERE te.language = 'English'
GROUP BY t.tno, t.tname;

17

  • [x] 查询所有职称不是“讲师”的教师号、姓名、职称。
1
2
3
4
5
6
7
SELECT tno, tname, ttitle 
FROM teacher 
WHERE ttitle <> '讲师';

SELECT tno, tname, ttitle 
FROM teacher 
WHERE ttitle != '讲师';

18

  • [x] 查询虽然选修了课程，但未参加考试的所有同学的学号。
1
2
3
select sno 
from sc 
where grade is null;

19

  • [x] 查询所有考试不及格的同学的学号、成绩，并按成绩降序排列。（降序desc升序asc并且默认为升序）
1
2
3
4
SELECT sno, grade 
FROM sc 
WHERE grade < 60 
ORDER BY grade DESC;

20

  • [x] 查询在1970年出生的教师号、姓名、出生日期。（也可以改成月份和年份）
1
2
3
SELECT tno, tname, tbirthday 
FROM teacher 
WHERE YEAR(tbirthday) = 1970;

21

  • [x] 查询各个课程号的选课人数。
1
2
3
SELECT cno, COUNT(*) AS student_count
FROM sc
GROUP BY cno;

22

  • [x] 查询讲授2门课以上的教师号。
1
2
3
4
SELECT tno
FROM teaching
GROUP BY tno
HAVING COUNT(*) >= 2;

23

  • [x] 查询选修了 800001 课程的学生平均分数、最低分数和最高分数。（此处课程号应为810011）
1
2
3
select avg(grade), min(grade), max(grade)
from sc
where cno = '810011';

24

  • [x] 查询1960年以后出生的，职称为讲师的教师的姓名、出生日期，并按出生日期升序排列。
1
2
3
4
SELECT tname, tbirthday 
FROM teacher 
WHERE ttitle = '讲师' AND tbirthday > '1960-01-01' 
ORDER BY tbirthday ASC;

复杂数据查询 3问题

1

  • [x] （1）创建视图 new_View 显示所有同学的选课及成绩情况，列出学生的学号、姓名、班号、课程名称和成绩。
1
2
3
4
5
-- 查看当前数据库中的所有视图
SHOW FULL TABLES IN your_database_name WHERE TABLE_TYPE LIKE 'VIEW';

-- 删除视图
DROP VIEW view_you_create;
1
2
3
4
5
6
-- AS关键字于定义视图结构
CREATE VIEW new_View AS
SELECT s.sno, s.sname, s.classno, c.cname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno;

2

  • [x] （2）在视图 new_View 中查询“软件0801”班的同学的选课及成绩情况，显示学号、姓名、课程名称、成绩。
1
2
3
SELECT sno, sname, cname, grade
FROM new_View
WHERE classno = 'Rj0801';

3

  • [x] （3）在视图 new_View 中插入如下元组：08300010，李在，R,j0801，数据库系统，88（此为不成功的操作）
1
2
3
4
5
6
7
8
9
```
## 4
- [x] （4）查询所有同学的学分情况（假设课程成绩>=60时可获得该门课程的学分），显示学号、姓名、总学分（用 JOIN）。
```SQL
SELECT s.sno, s.sname, SUM(IF(sc.grade >= 60, c.ccredit, 0)) AS total_credit
FROM student s
LEFT JOIN sc ON s.sno = sc.sno
LEFT JOIN course c ON sc.cno = c.cno
GROUP BY s.sno, s.sname;

5

  • [x] （5）查询所有同学的平均成绩及选课门数，显示学号、姓名、平均成绩、选课门数。
1
2
3
4
SELECT s.sno, s.sname, AVG(sc.grade) AS avg_grade, COUNT(sc.cno) AS course_count
FROM student s
LEFT JOIN sc ON s.sno = sc.sno
GROUP BY s.sno, s.sname;

6

  • [x] （6）查询所有选修了课程但未参加考试的所有同学及相应的课程，显示学号、姓名、课程号、课程名称。
1
2
3
4
5
SELECT s.sno, s.sname, sc.cno, c.cname
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno
WHERE sc.grade IS NULL;

7

  • [x] （7）查询所有选修了课程但考试不及格的所有同学及相应的课程，显示学号、姓名、课程号、课程名称、成绩。
1
2
3
4
5
SELECT s.sno, s.sname, sc.cno, c.cname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno
WHERE sc.grade < 60;

8

  • [x] （8）查询选修了课程名为“程序设计语言”的所有同学及成绩情况，显示学生姓名、课程成绩（用 ANY 运算符）。
1
2
3
4
SELECT s.sname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
WHERE sc.cno = ANY (SELECT cno FROM course WHERE cname = '程序设计语言');

9

  • [x] （9）查询“软件开发系”的所有同学及成绩情况，显示学号、姓名、班级名称、课程号、课程名称、成绩。
1
2
3
4
5
6
SELECT s.sno, s.sname, cl.classname, sc.cno, c.cname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno
JOIN class cl ON s.classno = cl.classno
WHERE cl.classdept = '软件开发';

10

  • [x] （10）查询所有教师的任课情况，显示教师姓名、课程名称。
1
2
3
4
SELECT t.tname, c.cname
FROM teacher t
JOIN teaching tc ON t.tno = tc.tno
JOIN course c ON tc.cno = c.cno;

11

  • [x] （11）查询成绩低于同门课程平均成绩的信息，显示学生学号、姓名、课程名称及低于平均成绩的值（即比平均成绩低多少）。
1
2
3
4
5
6
7
8
9
10
SELECT 
    student.sno AS '学号',
    student.sname AS '姓名',
    course.cname AS '课程名称',
    (avg_grade - sc.grade) AS '低于平均成绩的值'
FROM student 
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
JOIN (SELECT cno, AVG(grade) AS avg_grade FROM sc GROUP BY cno) AS avg_sc ON sc.cno = avg_sc.cno
WHERE sc.grade < avg_sc.avg_grade;

12

  • [x] （12）查询和“葛畅”在同一班级的同学的姓名（使用子查询）。
1
2
3
SELECT sname
FROM student
WHERE classno = (SELECT classno FROM student WHERE sname = '葛畅');

13

  • [x] （13）查询没有选修“计算机基础”课程的学生姓名（用 NOT EXISTS）。
1
2
3
SELECT sname
FROM student s
WHERE NOT EXISTS (SELECT * FROM sc WHERE s.sno = sc.sno AND sc.cno = '800001');

14

  • [x] （14）查询主讲“数据库系统”和主讲“数据结构”的教师姓名（用 UNION）。
1
2
3
4
5
6
7
8
9
SELECT tname
FROM teacher
WHERE tno IN (SELECT tno FROM teaching WHERE cno IN (SELECT cno FROM course WHERE cname = '数据库系统'))

UNION

SELECT tname
FROM teacher
WHERE tno IN (SELECT tno FROM teaching WHERE cno IN (SELECT cno FROM course WHERE cname = '数据结构'));

15

  • [x] （15）查询讲授了所有课程的教师的姓名。
1
2
3
4
5
6
7
8
9
10
SELECT tname
FROM teacher
WHERE tno IN (
    SELECT tno
    FROM teaching
    GROUP BY tno
    HAVING COUNT(DISTINCT cno

) = (SELECT COUNT(*) FROM course)
);

16

  • [x] （16）查询同时选修学课程800001和800002的女同学的姓名。
1
2
3
4
5
SELECT DISTINCT sname
FROM student s
JOIN sc sc1 ON s.sno = sc1.sno AND sc1.cno = '800001'
JOIN sc sc2 ON s.sno = sc2.sno AND sc2.cno = '800002'
WHERE s.ssex = '女';

17

  • [x] （17）查询既未选修课程800001又未选修课程800002的学生姓名。
1
2
3
4
5
6
7
SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno FROM sc WHERE cno = '800001'
) AND sno NOT IN (
    SELECT sno FROM sc WHERE cno = '800002'
);

18

  • [x] （18）查询有一门课程成绩为95分的女同学的姓名。
1
2
3
4
SELECT sname
FROM student s
JOIN sc ON s.sno = sc.sno
WHERE s.ssex = '女' AND sc.grade = 95;

19

  • [x] （19）查询选课数量大于3门的女同学的姓名。
1
2
3
4
5
6
7
8
SELECT sname
FROM (
    SELECT sno, COUNT(*) AS course_count
    FROM sc
    GROUP BY sno
) AS course_counts
JOIN student ON course_counts.sno = student.sno
WHERE ssex = '女' AND course_count > 3;

20

  • [x] （20）查询平均成绩大于80分的男同学的姓名。
1
2
3
4
5
SELECT 
    student.sname AS '姓名'
FROM student 
JOIN (SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno) AS avg_sc ON student.sno = avg_sc.sno
WHERE avg_sc.avg_grade > 80 AND student.ssex = '男';

21

  • [x] （21）查询徐永军老师所教的每一门课程的平均成绩。
1
2
3
4
5
6
SELECT c.cname, AVG(sc.grade) AS avg_grade
FROM teaching t
JOIN sc ON t.cno = sc.cno
JOIN course c ON t.cno = c.cno
WHERE t.tno = '000007'
GROUP BY c.cname;

22

  • [x] （22）查询男同学每一个年龄组的人数，要求按人数升序输出人数超过20人的年龄组。
1
2
3
4
5
6
SELECT FLOOR((YEAR(CURRENT_DATE) - YEAR(sbirthday)) / 10) AS age_group, COUNT(*) AS student_count
FROM student
WHERE ssex = '男'
GROUP BY age_group
HAVING student_count > 20
ORDER BY student_count ASC;

23

  • [x] （23）查询每门课程成绩都大于90分的学生姓名。
1
2
3
4
5
6
7
8
9
10
11
SELECT sname
FROM student s
WHERE NOT EXISTS (
    SELECT *
    FROM course c
    WHERE NOT EXISTS (
        SELECT *
        FROM sc
        WHERE s.sno = sc.sno AND c.cno = sc.cno AND grade > 90
    )
);

24

  • [x] （24）查询比所有女同学年龄要大的男同学的姓名。
1
2
3
4
5
6
7
SELECT sname
FROM student
WHERE ssex = '男' AND YEAR(sbirthday) < (
    SELECT MIN(YEAR(sbirthday))
    FROM student
    WHERE ssex = '女'
);

25

  • [x] （25）查询未选修 800002 课程的女同学的姓名。
1
2
3
4
5
SELECT sname
FROM student
WHERE ssex = '女' AND sno NOT IN (
    SELECT sno FROM sc WHERE cno = '800002'
);

26

  • [x] （26）查询所有课程成绩都及格的学生姓名。
1
2
3
4
5
6
7
SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno
    FROM sc
    WHERE grade < 60
);

27

  • [x] （27）查询选修课所有课程的学生姓名。
1
2
3
4
5
6
7
8
SELECT sname
FROM student
WHERE sno IN (
    SELECT sno
    FROM sc
    GROUP BY sno
    HAVING COUNT(DISTINCT cno) = (SELECT COUNT(*) FROM course)
);

28

  • [x] （28）查询选修了葛畅同学所选修的所有课程的学生姓名。
1
2
3
4
5
6
7
SELECT sname
FROM student
WHERE sno IN (
    SELECT sno FROM sc WHERE cno IN (
        SELECT cno FROM sc WHERE sno = (SELECT sno FROM student WHERE sname = '葛畅')
    )
);

29

  • [x] （29）查询平均成绩最高的学生姓名。
1
2
3
4
5
6
7
8
9
SELECT sname
FROM (
    SELECT sno, AVG(grade) AS avg_grade
    FROM sc
    GROUP BY sno
    ORDER BY avg_grade DESC
    LIMIT 1
) AS highest_avg_grade
JOIN student ON highest_avg_grade.sno = student.sno;

30

  • [x] （30）找出比所在班级平均成绩高的学生信息。
1
2
3
4
5
6
7
8
9
10
SELECT s.sno, s.sname, s.classno, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
WHERE sc.grade > (
    SELECT AVG(sc.grade)
    FROM student s2
    JOIN sc ON s2.sno = sc.sno
    WHERE s2.classno = s.classno
    GROUP BY s2.classno
);

数据操纵语言DML

  • [x] （1） 将选修徐永军老师所教课程的女同学的成绩提高5%
1
2
3
UPDATE sc
SET grade = grade * 1.05
WHERE cno IN (SELECT cno FROM teaching WHERE tno = '000007') AND sno IN (SELECT sno FROM student WHERE ssex = '女');

（2） 在基本表Student中检索每一门课程成绩都大于等于80分的学生学号、姓名、性别，并把检索到的值送往另一个已存在的基本表STUD（S#，SNAME，SEX）。

  • [x] （3） 在基本表sc中删除尚无成绩的选课记录。
1
DELETE FROM sc WHERE grade IS NULL;
  • [x] （4） 把王威同学的学习选课和成绩全部删除。
1
DELETE FROM sc WHERE sno = (SELECT sno FROM student WHERE sname = '王威');
  • [x] （5） 把选修数据结构课不及格的成绩全改为空值。
1
2
3
UPDATE sc
SET grade = NULL
WHERE cno = (SELECT cno FROM course WHERE cname = '数据结构') AND grade < 60;
  • [x] （6） 把低于总平均成绩的女同学的成绩提高5%
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TEMPORARY TABLE temp_table AS
SELECT s.sno
FROM student s
JOIN sc ON s.sno = sc.sno
WHERE s.ssex = '女' AND sc.grade < (
    SELECT AVG(grade)
    FROM sc
);

UPDATE sc
SET grade = grade * 1.05
WHERE sno IN (
    SELECT sno FROM temp_table
);

DROP TEMPORARY TABLE temp_table;

MySQL不允许在UPDATE语句的FROM子句中直接引用目标表。我们需要使用一个临时表或者将子查询结果保存到一个变量中来解决这个问题。

这段代码首先创建一个临时表，保存所有低于平均成绩的女同学的学号。然后，它更新sc表中的成绩，将所有在临时表中的学号对应的成绩提高5%。最后，它删除临时表。
  • [x] （7） 在基本表SC中修改800004课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个UPDATE语句实现）。
1
2
3
4
5
6
7
UPDATE sc
SET grade = grade * 1.05
WHERE cno = 800004 AND grade <= 75;

UPDATE sc
SET grade = grade * 1.04
WHERE cno = 800004 AND grade > 75;

存储过程

  • [x] （1） 创建一个能向学生表student中插入一条记录的存储过程insert_student，该过程需要5个参数，分别用来传递学号、姓名、性别、出生日期、班级号。写出调用存储过程insert_student的SQL语句，向数据表student中插入一个新同学，并提供相应的实参值。
1
2
3
4
5
6
7
delimiter //
create procedure insert_student(in p_sno varchar(20), in p_sname varchar(20), in p_ssex varchar(20), in p_sbirthday date, in p_classno varchar(20))
begin
    insert into student(sno, sname, ssex, sbirthday, classno)
    values (p_sno, p_sname, p_ssex, p_sbirthday, p_classno);
end //
delimiter ;
1
call insert_student('20301104', '廖学武', '男', '2001-06-29', 'Rj0801');
  • [x] （2） 创建一个向课程表course中插入一门新课程的存储过程insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为4，即当调用存储过程insert_course时，未给第三个参数“学分”提供实参值时，存储过程将按默认值4进行运算。
    调用存储过程insert_course，向课程表course中插入一门新课程。分两种情况（给出第三个参数和未给出第三个参数）写出相应的SQL命令，并比较结果。
1
2
3
4
5
6
7
8
9
delimiter //
create procedure insert_course(in p_cno varchar(20), in p_cname varchar(20), in p_ccredit smallint default 4)
begin
    if ccredit is null then
        set p_ccredit = 4;
    end if;
    insert into course(cno, cname, ccredit) values(p_cno, p_cname, p_ccredit);
end //
delimiter ;
1
2
call insert_course('CS101', '计算机科学', NULL);
call insert_course('CS102', '数据结构', 3);
  • [x] （3） 创建一个名称为query_student的存储过程，该存储过程的功能是从数据表student中根据学号查询某一同学的姓名、性别、出生日期、班级号。
    调用存储过程query_student，查询学号为“08301050”的姓名、性别、出生日期、班级号，并写出完成此功能的SQL命令。
1
2
3
4
5
6
delimiter //
create procedure query_student(in p_sno varchar(20))
begin
    select sname, ssex, sbirthday, classno from student where sno = p_sno;
end //
delimiter ;
1
call query_student('08300010');
  • [x] （4） 建立存储过程，输出平均成绩大于80分的学生的姓名、性别、年龄和平均成绩。调用该存储过程，并输出相应的结果。
1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create procedure get_low_course()
begin
    select student.sname as '姓名', 
           student.ssex as '性别', 
           year(curdate()) - year(student.sbirthday) as '年龄', 
           avg(sc.grade) as '平均成绩'
    from student
    join sc on student.sno = sc.sno
    group by student.sno
    having avg(sc.grade) < 80;
end //
delimiter ;
1
call get_high_achievers();
  • [x] （5） 写存储过程显示所有选择了与给定学生姓名选择的全部课程的学生的学号、姓名、选课数、平均成绩、总学分，调用该存储过程，并根据指定的学生姓名输出与其相应的结果。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
delimiter //
create procedure get_student_info(in student_name varchar(20))
begin
    select 
        s.sno as '学号',
        s.sname as '姓名',
        count(sc.cno) as '选课数',
        avg(sc.grade) as '平均成绩',
        sum(c.ccredit) as '总学分'
    from student s
    join sc on s.sno = sc.sno
    join course c on sc.cno = c.cno
    where 
        s.sno in (
            select sc.sno
            from student s
            join sc on s.sno = sc.sno
            where s.sname = student_name
        )
    group by 
        s.sno;
end //
delimiter ;
1
call get_student_info('胡贤斌');
  • [x]（6） 写存储过程显示所有平均成绩小于给定班级号的平均成绩的学生的学号、

名字、平均成绩

调用该存储过程，并根据指定的班级号输出与其相应的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
delimiter //
create procedure get_students_below_class_average(in class_no varchar(20))
begin
    -- 计算给定班级的平均成绩
    declare class_avg_grade decimal(5,2);
    select avg(sc.grade) into class_avg_grade
    from student
    join sc on student.sno = sc.sno
    where student.classno = class_no;

    -- 查询所有平均成绩小于给定班级平均成绩的学生
    select student.sno, student.sname, avg(sc.grade) as avg_grade
    from student
    join sc on student.sno = sc.sno
    group by student.sno, student.sname
    having avg_grade < class_avg_grade;
end //
delimiter ;
1
call get_students_below_class_average('Rj0801');

触发器 1/5有问题

  • [x] （1） 创建一个当向学生表student中插入一新同学时能自动列出全部同学信息的触发器display_trigger。执行存储过程insert_student，向学生表中插入一个新同学，看触发器display_trigger是否被触发。
1
2
3
4
5
6
7
8
9
10
delimiter //
create trigger display_trigger
after insert on student
for each row
begin
    select * from student;
end //
delimiter ;

show triggers; -- 查看已创建的触发器
  • [x] （2） 创建一个触发器，当向学生表student中插入一新同学时能自动更新（增加1）class班级表中该生所在班级的总人数。
1
2
3
4
5
6
7
8
9
10
delimiter //
create trigger update_student_count_add
after insert on student
for each row
begin
    update class set studentnumber = studentnumber + 1 where classno = new.classno;
end //
delimiter ;

drop trigger if exists update_student_count;    -- 删除触发器
  • [x] （3） 创建一个触发器，当从学生表student中删除一个同学时能自动更新（减1）class班级表中该生所在班级的总人数。
1
2
3
4
5
6
7
8
delimiter //
create trigger update_student_number_delete
after delete on student
for each row
begin
   update class set studentnumber = studentnumber - 1 where classno = old.classno;
end //
delimiter ;
  • [x] （4） 创建一个触发器，当将学生表student中某一个同学从一个班级改为另一个班级时，能自动更新class班级表中该生所在原来班级的总人数（减1）和新班级的总人数（增加1）。
1
2
3
4
5
6
7
8
9
10
11
delimiter //
create trigger update_student_count
after update on student
for each row
begin
    if old.classno != new.classno then
        update class set studentnumber = studentnumber - 1 where classno = old.classno;
        update class set studentnumber = studentnumber + 1 where classno = new.classno;
    end if;
end;//
delimiter ;
  • [x] （5） 建一个触发器，当往SC表中插入一个在STUDENT 表中不存在的学号SNO时，就往STUDENT表中插入该学号，其他属性全是NULL
1
2
3
4
5
6
7
8
9
10
delimiter //
create trigger insert_student
after insert on sc
for each row
begin
    if not exists (select 1 from student where sno = new.sno) then
        insert into student (sno) values (new.sno);
    end if;
end;//
delimiter ;
  • [x] （6） 写一个触发器阻止将学生成绩降低
1
2
3
4
5
6
7
8
9
10
11
delimiter //
create trigger prevent_grade before update on sc
for each row
begin
    if new.grade > old.grade then
        signal sqlstate '45000'
        set message_text = 'cannot asc student grade';
    end if;
end;
//
delimiter ;
  • [x] （7） 在sc表上创建触发器，只要有人选修的课程超过3门，就中断操作并提示警告
1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create trigger check_course_limit
before insert on sc
for each row
begin
    declare course_count int;
    select count(*) into course_count from sc where sno = new.sno;
    if course_count >= 3 then
        signal sqlstate '45000' set message_text = '一个学生不能选修超过3门课程';
    end if;
end;
//
delimiter ;
  • [x] （8） 创建一个触发器，当往SC表插入选修的课程时自动将该课程的学分累加到STUDENT表的该生的总学分（TOTALCREDIT）中，当从SC表中退课时自动从STUDENT表的该生的总学分中减去该课程的学分。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
delimiter //

-- 当插入新的选课记录时，增加学生的总学分
create trigger increase_totalcredit
after insert on sc
for each row
begin
    update student
    set totalcredit = totalcredit + (select ccredit from course where cno = new.cno)
    where sno = new.sno;
end;
//

-- 当删除选课记录时，减少学生的总学分
create trigger decrease_totalcredit
after delete on sc
for each row
begin
    update student
    set totalcredit = totalcredit - (select ccredit from course where cno = old.cno)
    where sno = old.sno;
end;
//

delimiter ;