sql练习（基于mysql数据库）

很久没有使用sql了，所以找了些题目来练习，边复习sql基本知识边编写，有更好的写法欢迎讨论。

一、创建表结构

CREATE TABLE student(
Sno VARCHAR(20) NOT NULL PRIMARY KEY,
Sname VARCHAR(20) NOT NULL,
Ssex VARCHAR(20) NOT NULL,
Sbrithday DATETIME,
Class VARCHAR(20)
);

CREATE TABLE course(
Cno VARCHAR(20) NOT NULL,
Cname VARCHAR(20) NOT NULL,
Tno VARCHAR(20) NOT NULL,
PRIMARY KEY(Cno),
FOREIGN KEY(Tno) REFERENCES teacher(Tno)
);

CREATE TABLE score (
  Sno varchar(20) NOT NULL,
  Cno varchar(20) NOT NULL,
  Degree decimal(4,1),
  PRIMARY KEY (Sno,Cno),
  FOREIGN KEY (Sno) REFERENCES student(Sno),
  FOREIGN KEY (Cno) REFERENCES course(Cno)
);

CREATE TABLE teacher(
Tno VARCHAR(20) NOT NULL,
Tname VARCHAR(20) NOT NULL,
Tsex VARCHAR(20) NOT NULL,
Tbirthday DATETIME,
Prof VARCHAR(20),
Depart VARCHAR(20) NOT NULL,
PRIMARY KEY(Tno)
);

二、向表中插入数据

INSERT INTO student (Sno,Sname,Ssex,Sbrithday,Class) 
VALUES 
('108','曾华','男','1977-09-01','95033'),
('105','匡明','男','1975-10-02','95031'),
('107','王丽','女','1976-01-23','95033'),
('101','李军','男','1976-02-20','95033'),
('109','王芳','女','1975-02-10','95031'),
('103','陆君','男','1974-06-03','95031');

INSERT INTO course
(Cno,Cname,Tno) 
VALUES 
('3-105','计算机导论','825'),
('3-245','操作系统','804'),
('6-166','数字电路','856'),
('9-888','高等数学','831');

INSERT INTO score
(Sno,Cno,Degree) 
VALUES 
('103','3-245','86'),
('105','3-245','75'),
('109','3-245','68'),
('103','3-105','92'),
('105','3-105','88'),
('109','3-105','76'),
('101','3-105','64'),
('107','3-105','91'),
('108','3-105','78'),
('101','6-166','85'),
('107','6-166','79'),
('108','6-166','81');

INSERT INTO teacher
(Tno,Tname,Tsex,Tbirthday,Prof,Depart) 
VALUES 
('804','李诚','男','1958-12-02','副教授','计算机系'),
('856','张旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系')

三、sql查询

1、查询Student表中的所有记录的Sname、Ssex和Class列。

2、查询教师所有的单位(即不重复的Depart列)。

知识点：SELECT DISTINCT 语句用于返回唯一不同的值。

3、查询Student表的所有记录。

4、查询Score表中成绩在60到80之间的所有记录。

知识点：（1）BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

（2）在不同的数据库中，BETWEEN 操作符会产生不同的结果！

在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

因此，请检查您的数据库是如何处理 BETWEEN 操作符！

5、查询Score表中成绩为85，86或88的记录。

知识点：（1）IN 操作符允许您在 WHERE 子句中规定多个值。

（2）IN 与 = 的异同

相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义
不同点：IN可以规定多个值，等于规定一个值

6、查询Student表中“95031”班或性别为“女”的同学记录。

知识点：

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

7、以Class降序查询Student表的所有记录。

知识点：

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

8、以Cno升序、Degree降序查询Score表的所有记录。

9、查询“95031”班的学生人数。

知识点：COUNT() 函数返回匹配指定条件的行数。

10、查询Score表中的最高分的学生学号和课程号。（子查询）

知识点：（1）WHERE 子句用于过滤记录。

（2）MAX() 函数返回指定列的最大值。

11、查询每门课的平均成绩。(查询每门课的平均成绩需要用到表score和course，先要联表，再通过课程名称/编号分组求平均值 )

知识点：（1）SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。包括left join 等

（2）GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。（1、筛选出以3开头的课程，2、分组求出每门课的平均分数 3、增加过滤条件：至少有5名学生选修）

知识点：（1）AVG() 函数返回数值列的平均值。

（2）LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

（3）通配符：% 替代 0 个或多个字符（如A%：以A开头，%A：以A结尾，%A%：包含A）； _ 替代一个字符（如：_ABC：以任意一个字符开始，后面是ABC）

（4）HAVING 子句可以让我们筛选分组后的各组数据。

13、查询分数大于70，小于90的Sno列。

14、查询所有学生的Sname、Cno和Degree列。

15、查询所有学生的Sno、Cname和Degree列。

16、查询所有学生的Sname、Cname和Degree列

17、查询“95033”班学生的平均分。

18、假设使用如下命令建立了一个grade表：

create table grade(low int(3),upp int(3),garden char(1));
insert into grade(low,upp,garden)
values
(90,100,'A'),(80,89,'B'),(70,79,'C'),(60,69,'D'),(0,59,'E');

执行后结果：

现查询所有同学的Sno、Cno和garden列。

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

22、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

知识点：mysql中EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。完整写法：EXTRACT(YEAR FROM Sbrithday) 与 YEAR（Sbrithday）查询结果一致

23、查询“张旭“教师任课的学生成绩。

24、查询选修某课程的同学人数多于5人的教师姓名。

25、查询95033班和95031班全体学生的记录。

26、查询存在有85分以上成绩的课程Cno.

27、查询出“计算机系“教师所教课程的成绩表。

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

31、查询所有教师和同学的name、sex和birthday.

知识点：（1）设置别名语法：SELECT column_name AS alias_name FROM table_name;

（2）SQL UNION 操作符合并两个或多个 SELECT 语句的结果。请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。