SQL
AI-摘要
AnZhiYu GPT
AI初始化中...
介绍自己 🙈
生成本文简介 👋
推荐相关文章 📖
前往主页 🏠
前往爱发电购买
SQL
chfychin🥝创建数据表
学生基本信息表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 | CREATE TABLE student ( |
班级信息表class
字段名 | 字段类型 | 约束条件 | 字段含义说明 |
---|---|---|---|
classno | Varchar2(6) | Primary key | 班级号 |
classname | Varchar(20) | Not null | 班级名称 |
classmajor | Varchar2(20) | 所属专业 | |
classdept | Varchar2(20) | 所属系别 | |
studentnumber | smallint | [20…40] | 学生数 |
1 | CREATE TABLE class ( |
课程信息表course
字段名 | 字段类型 | 约束条件 | 字段含义说明 |
---|---|---|---|
cno | Varchar2 (6) | Primary key | 课程号 |
cname | Varchar(30) Not null | 课程名称 | |
ccredit | smallint | [1…4] | 学分 |
1 | CREATE TABLE course ( |
选修课程信息表sc
字段名 | 字段类型 | 约束条件 | 字段含义说明 |
---|---|---|---|
sno | varchar (8) | Foreign key | 学号 |
cno | varchar (6) | Foreign key | 课程号 |
grade | integer | [0…100] | 成绩 |
1 | CREATE TABLE sc ( |
教师信息表teacher
字段名 | 字段类型 | 约束条件 | 字段含义说明 |
---|---|---|---|
tno | Number(6) | Primary key | 教师号 |
tname | Varchar2(8) | Not null | 教师姓名 |
tsex | Varchar2(2) | ‘男’或’女’ | 性别 |
tbirthday | date | 出生日期 | |
ttitle | Varchar2(20) | 职称 |
1 | CREATE TABLE teacher ( |
教师任课情况表teaching
字段名 | 字段类型 | 约束条件 | 字段含义说明 |
---|---|---|---|
tno | Number(6) | Foreign key | 教师号 |
cno | Varchar (6) | Foreign key | 课程号 |
language | Varchar2 (10) | Chinese, Bilingual, English | 授课语言 |
1 | CREATE TABLE teaching ( |
插入数据
表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 | INSERT INTO student (sno, sname, ssex, sbirthday, classno) VALUES |
表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 | INSERT INTO class (classno, classname, Classs-major, classdept, studentnumber) VALUES |
表course
cno | cname | credit |
---|---|---|
800001 | 计算机基础 | 4 |
800002 | 程序设计语言 | 4 |
800003 | 数据结构 | 4 |
810011 | 数据库系统 | 4 |
810013 | 计算机网络 | 3 |
810015 | 微机原理与应用 | 4 |
1 | INSERT INTO course (cno, cname, credit) VALUES |
表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 | INSERT INTO sc (sno, cno, grade) VALUES |
表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 | INSERT INTO teacher (tno, tname, tsex, tbirthday, ttitle) VALUES |
表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 | INSERT INTO teaching (cno, tno, language) VALUES |
简单的数据操作
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
3ALTER 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
2ALTER 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
2ALTER 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
3CREATE INDEX idx_sno_cno_desc ON course (sno, cno DESC);
-- 删除受到外键影响8
- [x] 在基本表 student 中增加约束条件:男生年龄小于 23 岁,女生年龄小于 21 岁。
1
2
3
4
5ALTER 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
3CREATE 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
3SELECT sno, sname, ssex, sbirthday
FROM student
WHERE sbirthday < '1980-01-01' AND ssex = '女';15
- [x] 查询所有姓“李”的男同学的学号、姓名、性别、出生日期。
1
2
3SELECT sno, sname, ssex, sbirthday
FROM student
WHERE sname LIKE '李%' AND ssex = '男';16
- [x] 查询所有用英文授课的教师号、姓名及英语授课的门数。
1
2
3
4
5SELECT 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
7SELECT tno, tname, ttitle
FROM teacher
WHERE ttitle <> '讲师';
SELECT tno, tname, ttitle
FROM teacher
WHERE ttitle != '讲师';18
- [x] 查询虽然选修了课程,但未参加考试的所有同学的学号。
1
2
3select sno
from sc
where grade is null;19
- [x] 查询所有考试不及格的同学的学号、成绩,并按成绩降序排列。(降序desc升序asc并且默认为升序)
1
2
3
4SELECT sno, grade
FROM sc
WHERE grade < 60
ORDER BY grade DESC;20
- [x] 查询在1970年出生的教师号、姓名、出生日期。(也可以改成月份和年份)
1
2
3SELECT tno, tname, tbirthday
FROM teacher
WHERE YEAR(tbirthday) = 1970;21
- [x] 查询各个课程号的选课人数。
1
2
3SELECT cno, COUNT(*) AS student_count
FROM sc
GROUP BY cno;22
- [x] 查询讲授2门课以上的教师号。
1
2
3
4SELECT tno
FROM teaching
GROUP BY tno
HAVING COUNT(*) >= 2;23
- [x] 查询选修了 800001 课程的学生平均分数、最低分数和最高分数。(此处课程号应为810011)
1
2
3select avg(grade), min(grade), max(grade)
from sc
where cno = '810011';24
- [x] 查询1960年以后出生的,职称为讲师的教师的姓名、出生日期,并按出生日期升序排列。
1
2
3
4SELECT 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
3SELECT 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
4SELECT 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
5SELECT 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
5SELECT 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
4SELECT 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
6SELECT 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
4SELECT 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
10SELECT
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
3SELECT sname
FROM student
WHERE classno = (SELECT classno FROM student WHERE sname = '葛畅');13
- [x] (13)查询没有选修“计算机基础”课程的学生姓名(用 NOT EXISTS)。
1
2
3SELECT 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
9SELECT 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
10SELECT 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
5SELECT 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
7SELECT 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
4SELECT 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
8SELECT 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
5SELECT
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
6SELECT 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
6SELECT 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
11SELECT 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
7SELECT sname
FROM student
WHERE ssex = '男' AND YEAR(sbirthday) < (
SELECT MIN(YEAR(sbirthday))
FROM student
WHERE ssex = '女'
);25
- [x] (25)查询未选修 800002 课程的女同学的姓名。
1
2
3
4
5SELECT 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
7SELECT sname
FROM student
WHERE sno NOT IN (
SELECT sno
FROM sc
WHERE grade < 60
);27
- [x] (27)查询选修课所有课程的学生姓名。
1
2
3
4
5
6
7
8SELECT 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
7SELECT 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
9SELECT 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
10SELECT 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
3UPDATE 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
3UPDATE 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
20CREATE 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
7UPDATE 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
7delimiter //
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
9delimiter //
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
2call insert_course('CS101', '计算机科学', NULL);
call insert_course('CS102', '数据结构', 3); - [x] (3) 创建一个名称为query_student的存储过程,该存储过程的功能是从数据表student中根据学号查询某一同学的姓名、性别、出生日期、班级号。
调用存储过程query_student,查询学号为“08301050”的姓名、性别、出生日期、班级号,并写出完成此功能的SQL命令。1
2
3
4
5
6delimiter //
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
13delimiter //
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
23delimiter //
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 | 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
10delimiter //
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
10delimiter //
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
8delimiter //
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
11delimiter //
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
10delimiter //
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
11delimiter //
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
13delimiter //
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
25delimiter //
-- 当插入新的选课记录时,增加学生的总学分
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 ;
评论
匿名评论隐私政策
✅ 你无需删除空行,直接评论以获取最佳展示效果