记录一下数据库综合查询，复习加深印象

1. 创建教学数据库中包含四个基本表：

• 教师情况表Teacher（Tno 教师号，TName 教师名，TDept 教师所在的院系）；
• 课程基本表Course（Cno 课号，Cname 课名，cpno 先修课号 , Tno 教师号）；
• 学生情况表Student（Sno学号，Sname 姓名，Sage 年龄，Sex 性别,sdept 所在的院系）；
• 学生成绩基本表SC 学生课程表（Sno 学号，Cno 课号，Grade 成绩）；

1. -- 删除表
   DROP TABLE IF EXISTS Student;
   -- 创建表
   CREATE TABLE Student(
   Sno CHAR(9) PRIMARY KEY,-- 学号 主键
   Sname VARCHAR(20) NOT NULL,-- 姓名 非空且唯一
   Sage INT NOT NULL,-- 年龄
   Sex VARCHAR(5) NOT NULL,-- 性别
   Sdept VARCHAR(10) NOT NULL -- 所在系
   )charset=utf8;
   
   -- 删除表
   DROP TABLE IF EXISTS Course;
   -- 创建表
   CREATE TABLE Course(
   Cno CHAR(5) PRIMARY KEY,-- 课程号 主键
   Cname VARCHAR(20) NOT NULL ,-- 课程名 非空
   Cpno CHAR(5) ,-- 先行课号
   Tno VARCHAR(5) -- 教师号
   )charset=utf8;
   
   -- 删除表
   DROP TABLE IF EXISTS Teacher;
   -- 创建表
   CREATE TABLE Teacher(
   Tno  VARCHAR(5) PRIMARY KEY,-- 教师号 主键
   Tname VARCHAR(20) NOT NULL ,-- 教师名 非空
   Tdept VARCHAR(20) NOT NULL -- 教师号
   )charset=utf8;
   
   -- 删除表
   DROP TABLE IF EXISTS SC;
   -- 创建表
   CREATE TABLE SC(
   Sno CHAR(9),-- 学号 主键
   Cno CHAR(5),-- 课程号 主键
   Grade INT NOT NULL, -- 成绩
   foreign key(Sno) references Student(Sno),
   foreign key(Cno) references Course(Cno)
   )charset=utf8;

   如果需要外键约束，额外添加即可，避免在后续sql删除修改语句中报错

• alter table course add foriegn key(Cpno) references course(Cno);

• alter table course add foreign key(Tno) references teacher(Tno);

• alter table sc add foreign key(Sno) references student(Sno);

• alter table sc add foreign key(Cno) references course(Cno);

1. 添加表信息

2. 添加学生表信息

3. ：

4. 添加课程表信息

5. 

6. 添加教师表信息

7. 

8. 添加选课表信息

9. 

11. 练习24个SQL语句，经过验证，都是正确能够运行的。

1. 查询所有女同学的姓名和年龄；

SELECT Sname,Sage FROM student where Sex = '女';

2. 查询计算机系教师开设的所有课程的课号和课名；

 SELECT Cname,Cno FROM Course
WHERE Tno IN (
		SELECT Tno FROM Teacher WHERE Tdept = '计算机系');

3. 查询所有女同学所选课程的课程号；

SELECT Cno FROM sc WHERE Sno IN (SELECT Sno FROM student WHERE Sex = '女');

4. 查询至少有一门课程的成绩高于90分的学生的姓名和年龄；

SELECT Sname,Sage
FROM student WHERE Sno IN (
		SELECT Sno FROM sc WHERE Grade > 90);

5. 查询选修“数据库原理“的所有学生的姓名和成绩；

两种方式：
SELECT student.Sname,sc.Grade FROM student
JOIN sc ON student.Sno = sc.Sno
JOIN course ON sc.Cno = course.Cno
WHERE Cname = '数据库原理';

SELECT student.Sname,sc.Grade
FROM student,sc,course
WHERE student.Sno = sc.Sno AND sc.Cno = course.Cno AND Cname = '数据库原理';

​

6. 查询未选修“VB设计“的学生的学号和姓名；

SELECT Sno,Sname
FROM Student
WHERE Sno IN (
		SELECT Sno FROM sc
		WHERE Cno NOT IN (
				SELECT Cno FROM course WHERE Cname = 'VB设计'));

7. 查询不是计算机系教师所讲授的课程的课名和课号；

SELECT Cname,Cno FROM Course
WHERE	Tno NOT IN (
		SELECT Tno FROM Teacher WHERE	Tdept = '计算机系');

8. 查询未选修“21“号课的学生的学号和课号；

SELECT student.Sno,Cno FROM student,sc WHERE student.Sno=sc.Sno and Cno !='21';

9. 查询年龄在18~20岁（包括20和18）之间的所有学生的信息；

SELECT * FROM student WHERE	Sage >= 18 AND Sage <= 20;
SELECT student.* FROM student WHERE Sage BETWEEN 18 AND 20;

10. 查询至少选修“21“和”41“两门课程的学生的学号；

SELECT	Sno FROM sc WHERE Cno IN ('21', '41')
GROUP BY Sno HAVING COUNT(DISTINCT Cno) >= 2;

11. 查询选修楚梁老师所讲授课程的学生的学号和成绩

SELECT Sno,Grade FROM sc
WHERE Cno IN (
		SELECT Cno FROM	course WHERE Tno IN (
				SELECT Tno FROM teacher WHERE	Tname = '楚梁'));

12. 查询至少选修了两门课程的学生的学号

SELECT Sno FROM sc WHERE Cno 
GROUP BY Sno HAVING COUNT(DISTINCT Cno) >= 2;

13. 查询选修高等数学课程的人数

SELECT	count(Sno) AS 人数 FROM sc
WHERE Cno IN (
		SELECT Cno FROM	Course WHERE Cname = '高等数学') 

14. 查询高等数学课程的平均分

SELECT avg(Grade) AS 高等数学平均分
	FROM sc WHERE Cno IN (
			SELECT Cno FROM Course WHERE Cname = '高等数学');

15. 查询黑姓的所有学生的姓名、年龄和性别

SELECT Sname,Sage,Sex FROM student WHERE Sname LIKE '黑%';

16. 查询学号比‘绯红女巫’同学大而年龄比他小的学生姓名

SELECT Sname FROM Student
WHERE Sno > (
		SELECT Sno FROM	student WHERE Sname = '绯红女巫'
)
AND Sage < (
	SELECT Sage FROM student WHERE Sname = '绯红女巫'
);

17. 查询年龄大于所有女同学平均年龄的男学生的姓名和年龄

SELECT Sname,Sage FROM student
WHERE Sage > (
		SELECT avg(Sage)FROM student WHERE Sex = '女')
AND Sex = '男'; 

18. 查询只选了一门课程的学生的学号

SELECT Student.Sno FROM Student
JOIN SC ON Student.Sno = SC.Sno
GROUP BY Student.Sno HAVING COUNT(SC.Cno) = 1;

19. 将每一门课程的成绩均大于等于80分的学生的学号、姓名和性别插入到另一个已经存在的基本表STU（Sno，SNAME，SEX）中

CREATE TABLE STU (
	Sno CHAR (9) PRIMARY KEY,
	-- 学号 主键
	SNAME VARCHAR (20) NOT NULL,
	-- 姓名 非空且唯一
	SEX VARCHAR (5) NOT NULL -- 性别
) charset = utf8;
INSERT INTO STU (Sno, SNAME, SEX) SELECT
	student.Sno,
	student.Sname,
	student.Sex
FROM student
WHERE student.Sno IN (
		SELECT sc.Sno FROM sc GROUP BY sc.Sno HAVING MIN(sc.Grade) >= 80);

20. 将英语课程的任课教师号修改为412

UPDATE teacher SET Tno = 413
WHERE Tno IN (
		SELECT Tno FROM	course WHERE Cname = '四级英语');

21. 将低于总平均成绩的女同学的成绩提高5%

UPDATE sc
SET Grade = Grade * 1.05
WHERE Sno IN (
		SELECT Sno	FROM student WHERE Sex = '女'
	)
AND Grade < (
	SELECT avg_grade FROM
		(
			SELECT	avg(Grade) AS avg_grade
			FROM	sc
		) AS avg_G
);

22. 从基本表Course中删除白墨老师的任课信息

update  course	set Tno=null
WHERE course.Tno IN (
		SELECT Tno FROM	teacher WHERE Tname = '白墨'); 


DELETE FROM course where Tno  =(Select Tno from teacher WHERE Tname='白墨');
​

23. 查询至少讲授两门课程的教师姓名和其所在的系

SELECT Tname,Tdept
FROM teacher left JOIN course ON teacher.Tno = course.Tno
GROUP BY Tname,Tdept HAVING COUNT(Cno) >= 2;

24. 查询计算机系的所有老师

SELECT * FROM teacher WHERE Tdept = '计算机系';

结语

外键约束的问题请查看

mysql，用sql语句，建立学生-课程数据库基本表_sql创建学生表-CSDN博客