MySQL练习题初级45题（统一表）

        声明：该文章练习题取自作者‘孙子衡’-->https://www.jianshu.com/p/dffd40d06e3c?u_atoken=347d4392-252a-4b45-a678-bcec250f9e04&u_asession=01eMtP4JqegFoAlsURoakpOGrbcerjLJ_zN8f1aUZcmXP4RFYHr8mgHddXP2E4de3jX0KNBwm7Lovlpxjd_P_q4JsKWYrT3W_NKPr8w6oU7K_rPtya4yy2JhGPHuukmz9-nHmbkqVcEgdObpAroqY1_GBkFo3NEHBv0PZUm6pbxQU&u_asig=05C37JabxAuQ31mDFqjIByfyLzE6L1QI2GClS_4S2oH_CRohkzGM9lmVwfVnmUENav6UAlwIGd0n5-OXsYaCKG3INIU3CDihJTJ51P3S6kkIKOa1ngTAW4FxeLWO19IXEBBGQDh9HMF8GBnsGLJ80uwzZlCYPl5VPi0r_5fKCzBJb9JS7q8ZD7Xtz2Ly-b0kmuyAKRFSVJkkdwVUnyHAIJzZmc6KEtJ8Ac7iz7KRn3KyaF2ULn3BedZgqLkWUaPI706xbSxAaWh9ph0bRUFW-6vO3h9VXwMyh6PgyDIVSG1W_zuO1CFQeRvItTQZoN37h469EgyhIIU9G134I4HWFmMM2zxaRDUMsexOWGXS4DANDdkV_2vuXIqfq23xGu1PxmmWspDxyAEEo4kbsryBKb9Q&u_aref=XH4TVtlUH3sT%2FSBszLSI6AAx8uI%3Dhttps://www.jianshu.com/p/dffd40d06e3c?u_atoken=347d4392-252a-4b45-a678-bcec250f9e04&u_asession=01eMtP4JqegFoAlsURoakpOGrbcerjLJ_zN8f1aUZcmXP4RFYHr8mgHddXP2E4de3jX0KNBwm7Lovlpxjd_P_q4JsKWYrT3W_NKPr8w6oU7K_rPtya4yy2JhGPHuukmz9-nHmbkqVcEgdObpAroqY1_GBkFo3NEHBv0PZUm6pbxQU&u_asig=05C37JabxAuQ31mDFqjIByfyLzE6L1QI2GClS_4S2oH_CRohkzGM9lmVwfVnmUENav6UAlwIGd0n5-OXsYaCKG3INIU3CDihJTJ51P3S6kkIKOa1ngTAW4FxeLWO19IXEBBGQDh9HMF8GBnsGLJ80uwzZlCYPl5VPi0r_5fKCzBJb9JS7q8ZD7Xtz2Ly-b0kmuyAKRFSVJkkdwVUnyHAIJzZmc6KEtJ8Ac7iz7KRn3KyaF2ULn3BedZgqLkWUaPI706xbSxAaWh9ph0bRUFW-6vO3h9VXwMyh6PgyDIVSG1W_zuO1CFQeRvItTQZoN37h469EgyhIIU9G134I4HWFmMM2zxaRDUMsexOWGXS4DANDdkV_2vuXIqfq23xGu1PxmmWspDxyAEEo4kbsryBKb9Q&u_aref=XH4TVtlUH3sT%2FSBszLSI6AAx8uI%3D

（1）创建一个数据库 sql_exercise01

（2）创建4个数据表： 

        ①学生表（Student）

        ②课程表（Course）

         ③成绩表（Score）

        ④教师信息表（Teacher）

        设计如下针对四张表完成如下信息：

表结构：

#学生表
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Sno       | varchar(20) | NO   | PRI | NULL    |       |
| Sname     | varchar(20) | NO   |     | NULL    |       |
| Ssex      | varchar(20) | NO   |     | NULL    |       |
| Sbirthday | datetime    | YES  |     | NULL    |       |
| Class     | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#教师表
mysql> desc teacher;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Tno       | varchar(20) | NO   | PRI | NULL    |       |
| Tname     | varchar(20) | NO   |     | NULL    |       |
| Tsex      | varchar(20) | NO   |     | NULL    |       |
| Tbirthday | datetime    | YES  |     | NULL    |       |
| Prof      | varchar(20) | YES  |     | NULL    |       |
| Depart    | varchar(20) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> #课程表
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno   | varchar(20) | NO   | PRI | NULL    |       |
| Cname | varchar(20) | NO   |     | NULL    |       |
| Tno   | varchar(20) | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> #成绩表
mysql> desc score;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| Sno    | varchar(20)   | NO   | MUL | NULL    |       |
| Cno    | varchar(20)   | NO   | MUL | NULL    |       |
| Degree | decimal(10,0) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

表数据

mysql> #学生表
mysql> select *from student;
+-----+--------+------+---------------------+-------+
| Sno | Sname  | Ssex | Sbirthday           | Class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)

mysql> #课程表
mysql> select *from course;
+-------+-----------------+-----+
| Cno   | Cname           | Tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论      | 825 |
| 3-245 | 操作系统        | 804 |
| 6-166 | 数字电路        | 856 |
| 9-888 | 高等数学        | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

mysql> #成绩表
mysql> select *from score;
+-----+-------+--------+
| Sno | Cno   | Degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-105 |     64 |
| 105 | 3-105 |     91 |
| 109 | 3-105 |     78 |
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
12 rows in set (0.00 sec)

mysql> #教师表
mysql> select *from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| Tno | Tname  | Tsex | Tbirthday           | Prof      | Depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 831 | 刘冰   | 女   | 1977-08-14 00:00:00 | 助教      | 电子工程系      |
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

根据上述要求完成MySQL语句书写

#创建数据库sql_exercise01
CREATE DATABASE sql_exercise01;
#使用数据库sql_exercise02
USE sql_exercise01;

#学生表
CREATE TABLE student(
`Sno` VARCHAr(20) NOT NULL COMMENT '学号',
`Sname` VARCHAr(20) NOT NULL COMMENT '姓名',
`Ssex` VARCHAr(20) NOT NULL COMMENT '性别',
`Sbirthday` DATETIME COMMENT '出生日期',
`Class` VARCHAr(20) COMMENT '班级',
PRIMARY KEY(`Sno`)
)


#教师表
CREATE TABLE teacher(
`Tno` VARCHAr(20) NOT NULL  COMMENT '教师编号',
`Tname` VARCHAr(20) NOT NULL  COMMENT '姓名',
`Tsex` VARCHAr(20) NOT NULL  COMMENT '性别',
`Tbirthday` DATETIME  COMMENT '出生日期',
`Prof` VARCHAr(20)  COMMENT '职称',
`Depart` VARCHAr(20) NOT NULL  COMMENT '科系',
PRIMARY KEY(`Tno`)
)


#课程表
CREATE TABLE course(
`Cno` VARCHAr(20) NOT NULL COMMENT '课程编号',
`Cname` VARCHAr(20) NOT NULL  COMMENT '课程名称',
`Tno` VARCHAr(20) NOT NULL  COMMENT '授课教师编号',
PRIMARY KEY(`Cno`)
)
#将其中Tno字段里的key设为MUL
ALTER TABLE course ADD INDEX(Tno);

#成绩表
CREATE TABLE score(
`Sno` VARCHAr(20) NOT NULL  COMMENT '学生学号',
`Cno` VARCHAr(20) NOT NULL  COMMENT '课程编号',
`Degree` DECIMAL(10,0)  COMMENT '成绩'
)
#将其中Sno,Cno字段里的key设为MUL
ALTER TABLE score ADD INDEX(Sno);
ALTER TABLE score ADD INDEX(Cno);

#向学生student表添加数据
#使用insert语句向user表格中添加数据
#语法：insert into 表名( 字段1, 字段2, 字段3, … )values('值1', '值2', '值3',…);
INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(101,'李军','男','1976-02-20','95033');
INSERT INTO student VALUES(103,'陆君','男','1974-06-03','95031');
INSERT INTO student VALUES(105,'匡明','男','1975-10-02','95031');
INSERT INTO student VALUES(107,'王丽','女','1976-01-23','95033');
INSERT INTO student VALUES(108,'曾华','男','1977-09-01','95033');
INSERT INTO student VALUES(109,'王芳','女','1975-02-20','95031');


#向教师信息teacher表添加数据
INSERT INTO teacher
VALUES
(804,'李诚','男','1958-12-02','副教授','计算机系'),
(856,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'),
(831,'刘冰','女','1977-08-14','助教','电子工程系');

#向课程course表添加数据
INSERT INTO course
VALUES
('3-105','计算机导论',825),
('3-245','操作系统',804),
('6-166','数字电路',856),
('9-888','高等数学',831);

#向成绩score表添加数据
INSERT INTO score 
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);

​

 题目：

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

2.查询教师所有的单位即不重复的Depart列

3.查询Student表的所有记录

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

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

6.查询student表中“95031”班或性别为“女”的同学记录

7.以class降序查询student表的所有记录

8.以cno升序、degree降序查询score表的所有记录

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

10.查询score表中的最高分的学生学号和课程号。（子查询或者排序）

11.查询每门课的平均成绩

12.查询score表中至少有5名学生选修的并以3开头的课程的平均分数

13.查询分数大于70，小于90的sno列

14.查询所有学生的sname、cno和degree列

15.查询所有学生的sno、cname和degree列

16.查询所有学生的sname、cname和degree列

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

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

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

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

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

22.查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列

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

32.查询所有“女”教师和“女”同学的name、sex和birthday

33.查询成绩比该课程平均成绩低的同学的成绩表

34.查询所有任课教师的tname和depart

35.查询所有未讲课的教师的tname和depart

36.查询至少有2名男生的班号

37.查询student表中不姓“王”的同学记录

38.查询student表中每个学生的姓名和年龄

39.查询student表中最大和最小的sbirthday日期值

40.以班号和年龄从大到小的顺序查询student表中的全部记录

41.查询“男”教师及其所上的课程

42.查询最高分同学的sno、cno和degree列

43.查询和“李军”同性别的所有同学的sname

44.查询和“李军”同性别并同班的同学sname

45.查询所有选修“计算机导论”课程的“男”同学的成绩表

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

SELECt Sname,Ssex,Class FROM Student;

-->最终结果
mysql> SELECt Sname,Ssex,Class FROM Student;
+-------+------+-------+
| Sname | Ssex | Class |
+-------+------+-------+
| 李军  | 男   | 95033 |
| 陆君  | 男   | 95031 |
| 匡明  | 男   | 95031 |
| 王丽  | 女   | 95033 |
| 曾华  | 男   | 95033 |
| 王芳  | 女   | 95031 |
+-------+------+-------+

2.查询教师所有的单位即不重复的Depart列

SELECt DISTINCT Depart FROM teacher;

-->最终结果：
mysql> SELECt DISTINCT Depart FROM teacher;
+------------+
| Depart     |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+

3.查询Student表的所有记录

SELECt * FROM Student;

-->最终结果：
mysql> SELECt * FROM Student;
+-----+-------+------+---------------------+-------+
| Sno | Sname | Ssex | Sbirthday           | Class |
+-----+-------+------+---------------------+-------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳  | 女   | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+

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

方法1：
SELECt * FROM Score WHERe Degree BETWEEN 60 AND 80;
方法2：
SELECt * FROM Score WHERe Degree>=60 AND Degree<=80;

-->最终结果：
mysql> SELECt * FROM Score WHERe Degree BETWEEN 60 AND 80;
+-----+-------+--------+
| Sno | Cno   | Degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 109 | 3-105 |     76 |
| 101 | 3-105 |     64 |
| 108 | 3-105 |     78 |
| 107 | 6-166 |     79 |
+-----+-------+--------+

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

#方式1
SELECt * 
FROM score 
WHERe degree=85 OR degree=86 OR degree=88;
-->最终结果
mysql> SELECt *
    -> FROM score
    -> WHERe degree=85 OR degree=86 OR degree=88;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
| 101 | 6-166 |   85.0 |
+-----+-------+--------+


#方式2
SELECt * 
FROM score 
WHERe degree IN ('85','86','88');
-->最终结果
mysql> SELECt *
    -> FROM score
    -> WHERe degree IN ('85','86','88');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
| 101 | 6-166 |   85.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

6.查询student表中“95031”班或性别为“女”的同学记录

SELECt * 
FROM student 
WHERe `class`='95031' OR `ssex`='女';

-->最终结果
mysql> SELECt *
    -> FROM student
    -> WHERe `class`='95031' OR `ssex`='女';
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 109 | 王芳  | 女   | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+

7.以class降序查询student表的所有记录

SELECt * 
FROM student ORDER BY class DESC; 

-->最终结果
mysql> SELECt *
    -> FROM student ORDER BY class DESC;
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳  | 女   | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)

8.以cno升序、degree降序查询score表的所有记录

SELECt * 
FROM score ORDER BY cno ASC,degree DESC;

-->最终结果
mysql> SELECt *
    -> FROM score ORDER BY cno ASC,degree DESC;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 101 | 6-166 |   85.0 |
| 108 | 6-166 |   81.0 |
| 107 | 6-166 |   79.0 |
+-----+-------+--------+
12 rows in set (0.00 sec)

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

SELECt COUNT(*) AS '95031班的学生人数' 
FROM Student WHERe Class='95031';

-->最终结果
mysql> SELECt COUNT(*) AS '95031班的学生人数'
    -> FROM Student WHERe Class='95031';
+-------------------+
| 95031班的学生人数 |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.00 sec)

10.查询score表中的最高分的学生学号和课程号。（子查询或者排序）

#方式1
SELECt sno,cno 
FROM score 
WHERe `degree`=(SELECt MAX(degree) FROM score);
-->最终结果
mysql> SELECt sno,cno
    -> FROM score
    -> WHERe `degree`=(SELECt MAX(degree) FROM score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)


#方式2:
SELECt sno,cno FROM score ORDER BY degree DESC LIMIT 0,1
-->最终结果
mysql> SELECt sno,cno FROM score ORDER BY degree DESC LIMIT 0,1;
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)


#方式3
SELECt sno,cno
FROM student,course 
WHERe (sno,cno) = (SELECt sno,cno FROM score WHERe degree = (SELECt MAX(degree) FROM score));
-->最终结果
mysql> SELECt sno,cno
    -> FROM student,course
    -> WHERe (sno,cno) = (SELECt sno,cno FROM score WHERe degree = (SELECt MAX(degree) FROM score));
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

11.查询每门课的平均成绩

SELECt cno,AVG(degree) AS '平均分' 
FROM score GROUP BY cno;

-->最终结果
mysql> SELECt cno,AVG(degree) AS '平均分'
    -> FROM score GROUP BY cno;
+-------+----------+
| cno   | 平均分   |
+-------+----------+
| 3-105 | 81.50000 |
| 3-245 | 76.33333 |
| 6-166 | 81.66667 |
+-------+----------+
3 rows in set (0.00 sec)

12.查询score表中至少有5名学生选修的并以3开头的课程的平均分数

SELECt AVG(degree) 
FROM score 
WHERe cno LIKE '3%' GROUP BY cno HAVINg COUNT(cno)>=5;

-->最终结果
mysql> SELECt AVG(degree)
    -> FROM score
    -> WHERe cno LIKE '3%' GROUP BY cno HAVINg COUNT(cno)>=5;
+-------------+
| AVG(degree) |
+-------------+
|    81.50000 |
+-------------+
1 row in set (0.00 sec)

13.查询分数大于70，小于90的sno列

SELECt sno 
FROM score 
WHERe degree>70 AND degree>90

-->最终结果
mysql> SELECt sno
    -> FROM score
    -> WHERe degree>70 AND degree>90;
+-----+
| sno |
+-----+
| 103 |
| 107 |
+-----+
2 rows in set (0.00 sec)

14.查询所有学生的sname、cno和degree列

#方式1
SELECt sname,cno,degree 
FROM student,score 
WHERe student.sno=score.sno;
-->最终结果
mysql> SELECt sname,cno,degree
    -> FROM student,score
    -> WHERe student.sno=score.sno;
+-------+-------+--------+
| sname | cno   | degree |
+-------+-------+--------+
| 李军  | 3-105 |   64.0 |
| 李军  | 6-166 |   85.0 |
| 陆君  | 3-245 |   86.0 |
| 陆君  | 3-105 |   92.0 |
| 匡明  | 3-245 |   75.0 |
| 匡明  | 3-105 |   88.0 |
| 王丽  | 3-105 |   91.0 |
| 王丽  | 6-166 |   79.0 |
| 曾华  | 3-105 |   78.0 |
| 曾华  | 6-166 |   81.0 |
| 王芳  | 3-245 |   68.0 |
| 王芳  | 3-105 |   76.0 |
+-------+-------+--------+
12 rows in set (0.00 sec)


#方式2
SELECt sname,cno,degree 
FROM student 
JOIN score ON student.sno=score.sno;
-->最终结果
mysql> SELECt sname,cno,degree
    -> FROM student
    -> JOIN score ON student.sno=score.sno;
+-------+-------+--------+
| sname | cno   | degree |
+-------+-------+--------+
| 李军  | 3-105 |   64.0 |
| 李军  | 6-166 |   85.0 |
| 陆君  | 3-245 |   86.0 |
| 陆君  | 3-105 |   92.0 |
| 匡明  | 3-245 |   75.0 |
| 匡明  | 3-105 |   88.0 |
| 王丽  | 3-105 |   91.0 |
| 王丽  | 6-166 |   79.0 |
| 曾华  | 3-105 |   78.0 |
| 曾华  | 6-166 |   81.0 |
| 王芳  | 3-245 |   68.0 |
| 王芳  | 3-105 |   76.0 |
+-------+-------+--------+
12 rows in set (0.00 sec)

15.查询所有学生的sno、cname和degree列

#方式1
SELECt sno,cname,degree 
FROM score,course 
WHERe score.cno=course.cno;
-->最终结果
mysql> SELECt sno,cname,degree
    -> FROM score,course
    -> WHERe score.cno=course.cno;
+-----+------------+--------+
| sno | cname      | degree |
+-----+------------+--------+
| 103 | 计算机导论 |   92.0 |
| 105 | 计算机导论 |   88.0 |
| 109 | 计算机导论 |   76.0 |
| 101 | 计算机导论 |   64.0 |
| 107 | 计算机导论 |   91.0 |
| 108 | 计算机导论 |   78.0 |
| 103 | 操作系统   |   86.0 |
| 105 | 操作系统   |   75.0 |
| 109 | 操作系统   |   68.0 |
| 101 | 数字电路   |   85.0 |
| 107 | 数字电路   |   79.0 |
| 108 | 数字电路   |   81.0 |
+-----+------------+--------+
12 rows in set (0.10 sec)


#方式2
SELECt sno,cname,degree 
FROM score 
JOIN course ON course.cno=score.cno;
-->最终结果
mysql> SELECt sno,cname,degree
    -> FROM score
    -> JOIN course ON course.cno=score.cno;
+-----+------------+--------+
| sno | cname      | degree |
+-----+------------+--------+
| 103 | 计算机导论 |   92.0 |
| 105 | 计算机导论 |   88.0 |
| 109 | 计算机导论 |   76.0 |
| 101 | 计算机导论 |   64.0 |
| 107 | 计算机导论 |   91.0 |
| 108 | 计算机导论 |   78.0 |
| 103 | 操作系统   |   86.0 |
| 105 | 操作系统   |   75.0 |
| 109 | 操作系统   |   68.0 |
| 101 | 数字电路   |   85.0 |
| 107 | 数字电路   |   79.0 |
| 108 | 数字电路   |   81.0 |
+-----+------------+--------+
12 rows in set (0.00 sec)

16.查询所有学生的sname、cname和degree列

#方式1
SELECt sname,cname,degree 
FROM student,score,course 
WHERe student.sno=score.sno AND score.cno=course.cno;
-->最终结果
mysql> SELECt sname,cname,degree
    -> FROM student,score,course
    -> WHERe student.sno=score.sno AND score.cno=course.cno;
+-------+------------+--------+
| sname | cname      | degree |
+-------+------------+--------+
| 李军  | 计算机导论 |   64.0 |
| 李军  | 数字电路   |   85.0 |
| 陆君  | 操作系统   |   86.0 |
| 陆君  | 计算机导论 |   92.0 |
| 匡明  | 操作系统   |   75.0 |
| 匡明  | 计算机导论 |   88.0 |
| 王丽  | 计算机导论 |   91.0 |
| 王丽  | 数字电路   |   79.0 |
| 曾华  | 计算机导论 |   78.0 |
| 曾华  | 数字电路   |   81.0 |
| 王芳  | 操作系统   |   68.0 |
| 王芳  | 计算机导论 |   76.0 |
+-------+------------+--------+
12 rows in set (0.00 sec)


#方式2
SELECt student.sname,cname,degree 
FROM student 
JOIN score ON student.sno=score.sno 
JOIN course ON course.cno=score.cno;
-->最终结果
mysql> SELECt student.sname,cname,degree
    -> FROM student
    -> JOIN score ON student.sno=score.sno
    -> JOIN course ON course.cno=score.cno;
+-------+------------+--------+
| sname | cname      | degree |
+-------+------------+--------+
| 李军  | 计算机导论 |   64.0 |
| 李军  | 数字电路   |   85.0 |
| 陆君  | 操作系统   |   86.0 |
| 陆君  | 计算机导论 |   92.0 |
| 匡明  | 操作系统   |   75.0 |
| 匡明  | 计算机导论 |   88.0 |
| 王丽  | 计算机导论 |   91.0 |
| 王丽  | 数字电路   |   79.0 |
| 曾华  | 计算机导论 |   78.0 |
| 曾华  | 数字电路   |   81.0 |
| 王芳  | 操作系统   |   68.0 |
| 王芳  | 计算机导论 |   76.0 |
+-------+------------+--------+
12 rows in set (0.00 sec)

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

#方式1
SELECt AVG(degree) AS '95033班学生的平均分' 
FROM score 
WHERe sno IN(SELECt sno FROM student WHERe class='95033');
-->最终结果
mysql> SELECt AVG(degree) AS '95033班学生的平均分'
    -> FROM score
    -> WHERe sno IN(SELECt sno FROM student WHERe class='95033');
+---------------------+
| 95033班学生的平均分 |
+---------------------+
|            79.66667 |
+---------------------+
1 row in set (0.00 sec)


#方式2
SELECt AVG(degree) AS '95033班学生的平均分' 
FROM score,student 
WHERe student.sno=score.sno AND class='95033';
-->最终结果
mysql> SELECt AVG(degree) AS '95033班学生的平均分'
    -> FROM score,student
    -> WHERe student.sno=score.sno AND class='95033';
+---------------------+
| 95033班学生的平均分 |
+---------------------+
|            79.66667 |
+---------------------+
1 row in set (0.00 sec)

18.假设使用如下命令建立了一个grade表（ 然后查询所有同学的sno、cno和rank列）

CREATE TABLE IF NOT EXISTS `grade` (
  `low` INT (3) COMMENT '底限',
  `upp` INT (3) COMMENT '上限',
  `rank` CHAr(1) COMMENT '等级'
) ;

#向grade表中添加数据
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');

--->创建表结果为：
mysql> select * from grade;
+------+------+------+
| low  | upp  | rank |
+------+------+------+
|   90 |  100 | A    |
|   80 |   89 | B    |
|   70 |   79 | C    |
|   60 |   69 | D    |
|    0 |   59 | E    |
+------+------+------+
5 rows in set (0.00 sec)

#方式1
SELECt sno,cno,`rank` 
FROM score,grade 
WHERe degree BETWEEN low AND upp;
-->最终结果
mysql> SELECt sno,cno,`rank`
    -> FROM score,grade
    -> WHERe degree BETWEEN low AND upp;
+-----+-------+------+
| sno | cno   | rank |
+-----+-------+------+
| 103 | 3-245 | B    |
| 105 | 3-245 | C    |
| 109 | 3-245 | D    |
| 103 | 3-105 | A    |
| 105 | 3-105 | B    |
| 109 | 3-105 | C    |
| 101 | 3-105 | D    |
| 107 | 3-105 | A    |
| 108 | 3-105 | C    |
| 101 | 6-166 | B    |
| 107 | 6-166 | C    |
| 108 | 6-166 | B    |
+-----+-------+------+
12 rows in set (0.00 sec)


#方式2
SELECt sno,cno,`rank` 
FROM grade 
JOIN score ON score.degree BETWEEN low AND upp;
-->最终结果
mysql> SELECt sno,cno,`rank`
    -> FROM grade
    -> JOIN score ON score.degree BETWEEN low AND upp;
+-----+-------+------+
| sno | cno   | rank |
+-----+-------+------+
| 103 | 3-245 | B    |
| 105 | 3-245 | C    |
| 109 | 3-245 | D    |
| 103 | 3-105 | A    |
| 105 | 3-105 | B    |
| 109 | 3-105 | C    |
| 101 | 3-105 | D    |
| 107 | 3-105 | A    |
| 108 | 3-105 | C    |
| 101 | 6-166 | B    |
| 107 | 6-166 | C    |
| 108 | 6-166 | B    |
+-----+-------+------+
12 rows in set (0.00 sec)

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

#方式1:
#第一步：查询'109'号同学成绩的最大值-->76.0
SELECt MAX(degree) FROM score WHERe sno=109;
#第二步：查询选修“3-105”课程的成绩大于76.0的同学的sno
SELECt sno FROM score WHERe `cno`='3-105' AND degree>76.0
#第三步：合并写:查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECt * 
FROM student
WHERe sno IN(SELECt sno FROM score WHERe `cno`='3-105' AND degree>(SELECt MAX(degree) FROM score WHERe sno=109))
-->最终结果
mysql> SELECt *
    -> FROM student
    -> WHERe sno IN(SELECt sno FROM score WHERe `cno`='3-105' AND degree>(SELECt MAX(degree) FROM score WHERe sno=109));
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
+-----+-------+------+---------------------+-------+
4 rows in set (0.00 sec)


#方式2
SELECt * 
FROM student,score 
WHERe score.cno='3-105' 
AND student.sno=score.sno 
AND score.degree>(SELECt degree FROM score WHERe cno='3-105' AND sno='109');
-->最终结果
mysql> SELECt *
    -> FROM student,score
    -> WHERe score.cno='3-105'
    -> AND student.sno=score.sno
    -> AND score.degree>(SELECt degree FROM score WHERe cno='3-105' AND sno='109');
+-----+-------+------+---------------------+-------+-----+-------+--------+
| sno | sname | ssex | sbirthday           | class | sno | cno   | degree |
+-----+-------+------+---------------------+-------+-----+-------+--------+
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 |   92.0 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 |   88.0 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 |   91.0 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 |   78.0 |
+-----+-------+------+---------------------+-------+-----+-------+--------+
4 rows in set (0.00 sec)

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

SELECt * 
FROM score a 
WHERe degree <(SELECt MAX(degree) FROM score b WHERe a.cno=b.cno) 
AND sno IN(SELECt sno FROM score GROUP BY sno HAVINg COUNT(*)>1);

-->最终结果
mysql> SELECt *
    -> FROM score a
    -> WHERe degree <(SELECt MAX(degree) FROM score b WHERe a.cno=b.cno)
    -> AND sno IN(SELECt sno FROM score GROUP BY sno HAVINg COUNT(*)>1);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
| 107 | 6-166 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
9 rows in set (0.00 sec)

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

SELECt * 
FROM score 
WHERe cno='3-105' 
AND degree>(SELECt MAX(degree) FROM score WHERe sno='109')

-->最终结果
mysql> SELECt *
    -> FROM score
    -> WHERe cno='3-105'
    -> AND degree>(SELECt MAX(degree) FROM score WHERe sno='109');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
+-----+-------+--------+
4 rows in set (0.00 sec)

22.查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列

SELECt sno,sname,sbirthday 
FROM student 
WHERe YEAR(student.sbirthday)=(SELECt YEAR(sbirthday) FROM student WHERe sno='107');

-->最终结果
mysql> SELECt sno,sname,sbirthday
    -> FROM student
    -> WHERe YEAR(student.sbirthday)=(SELECt YEAR(sbirthday) FROM student WHERe sno='107');
+-----+-------+---------------------+
| sno | sname | sbirthday           |
+-----+-------+---------------------+
| 101 | 李军  | 1976-02-20 00:00:00 |
| 107 | 王丽  | 1976-01-23 00:00:00 |
+-----+-------+---------------------+
2 rows in set (0.00 sec)

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

#方式1
SELECt sno,degree 
FROM teacher,course,score 
WHERe teacher.tno=course.tno 
AND course.cno=score.cno AND tname='张旭';
-->最终结果
mysql> SELECt sno,degree
    -> FROM teacher,course,score
    -> WHERe teacher.tno=course.tno
    -> AND course.cno=score.cno AND tname='张旭';
+-----+--------+
| sno | degree |
+-----+--------+
| 101 |   85.0 |
| 107 |   79.0 |
| 108 |   81.0 |
+-----+--------+
3 rows in set (0.00 sec)


#方式2
#第一步：查询“张旭“教师对应的教师编号tno
SELECt tno FROM teacher WHERe tname='张旭';
#第二步：合并写
SELECt sno,degree 
FROM course,score 
WHERe course.tno=(SELECt tno FROM teacher WHERe tname='张旭') AND course.cno=score.cno;
-->最终结果
mysql> SELECt sno,degree
    -> FROM course,score
    -> WHERe course.tno=(SELECt tno FROM teacher WHERe tname='张旭') AND course.cno=score.cno;
+-----+--------+
| sno | degree |
+-----+--------+
| 101 |   85.0 |
| 107 |   79.0 |
| 108 |   81.0 |
+-----+--------+
3 rows in set (0.00 sec)


#方式3
SELECt sno,degree 
FROM score 
WHERe cno IN (SELECt cno FROM course WHERe tno IN (SELECt tno FROM teacher WHERe tname='张旭'));
-->最终结果
mysql> SELECt sno,degree
    -> FROM score
    -> WHERe cno IN (SELECt cno FROM course WHERe tno IN (SELECt tno FROM teacher WHERe tname='张旭'));
+-----+--------+
| sno | degree |
+-----+--------+
| 101 |   85.0 |
| 107 |   79.0 |
| 108 |   81.0 |
+-----+--------+
3 rows in set (0.00 sec)

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

#方式1
#查询人数大于5人的选修课程cno
SELECt cno FROM score GROUP BY score.cno HAVINg COUNT(cno)>5;
#第二步：合并写
SELECt tname 
FROM course,teacher 
WHERe course.cno=(SELECt cno FROM score 
GROUP BY score.cno HAVINg COUNT(cno)>5) AND course.tno=teacher.tno
-->最终结果
mysql> SELECt tname
    -> FROM course,teacher
    -> WHERe course.cno=(SELECt cno FROM score
    -> GROUP BY score.cno HAVINg COUNT(cno)>5) AND course.tno=teacher.tno;
+-------+
| tname |
+-------+
| 王萍  |
+-------+
1 row in set (0.00 sec)


#方式2
SELECt tname 
FROM teacher,course,score 
WHERe teacher.tno=course.tno AND course.cno=score.cno 
GROUP BY score.cno HAVINg COUNT(score.cno)>5; 
-->最终结果
mysql> SELECt tname
    -> FROM teacher,course,score
    -> WHERe teacher.tno=course.tno AND course.cno=score.cno
    -> GROUP BY score.cno HAVINg COUNT(score.cno)>5;
+-------+
| tname |
+-------+
| 王萍  |
+-------+
1 row in set (0.00 sec)


#方式3
SELECt tname 
FROM teacher 
WHERe tno IN (SELECt tno 
FROM course WHERe cno IN (SELECt cno FROM score GROUP BY cno HAVINg COUNT(*)>5) )
-->最终结果
mysql> SELECt tname
    -> FROM teacher
    -> WHERe tno IN (SELECt tno
    -> FROM course WHERe cno IN (SELECt cno FROM score GROUP BY cno HAVINg COUNT(*)>5) );
+-------+
| tname |
+-------+
| 王萍  |
+-------+
1 row in set (0.00 sec)

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

#方式1
SELECt * FROM student WHERe class IN('95033','95031');
-->最终结果
mysql> SELECt * FROM student WHERe class IN('95033','95031');
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳  | 女   | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)


#方式2
SELECt * FROM student WHERe class='95033' OR class='95031';
--->最终结果
mysql> SELECt * FROM student WHERe class='95033' OR class='95031';
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳  | 女   | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)

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

SELECt DISTINCT cno FROM score WHERe degree>85

-->最终结果
mysql> SELECt DISTINCT cno FROM score WHERe degree>85;
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)

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

#方式1
SELECt score.* 
FROM teacher,course,score 
WHERe teacher.depart='计算机系' AND teacher.tno=course.tno AND course.cno=score.cno;
-->最终结果
mysql> SELECt score.*
    -> FROM teacher,course,score
    -> WHERe teacher.depart='计算机系' AND teacher.tno=course.tno AND course.cno=score.cno;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
+-----+-------+--------+
9 rows in set (0.00 sec)


#方式2
#第一步：查询'计算机系'对应的教师编号tno-->804 825
SELECt tno FROM teacher WHERe depart='计算机系';
#第二步：查询教师编号对应的课程编号cno-->3-245 3-105
SELECt cno FROM course WHERe tno IN(SELECt tno FROM teacher WHERe depart='计算机系');
#第三步：查询出“计算机系“教师所教课程的成绩表
SELECt * 
FROM score 
WHERe cno IN
(SELECt cno FROM course WHERe tno IN
(SELECt tno FROM teacher WHERe depart='计算机系'));
-->最终结果
mysql> SELECt *
    -> FROM score
    -> WHERe cno IN
    -> (SELECt cno FROM course WHERe tno IN
    -> (SELECt tno FROM teacher WHERe depart='计算机系'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
+-----+-------+--------+
9 rows in set (0.00 sec)

28.查询“计算机系”与“电子工程系“不同职称的教师的tname和prof

#方式1
SELECt tname,prof 
FROM teacher a 
WHERe prof NOT IN
(SELECt prof FROM teacher b WHERe a.depart!=b.depart)
-->最终结果
mysql> SELECt tname,prof
    -> FROM teacher a
    -> WHERe prof NOT IN
    -> (SELECt prof FROM teacher b WHERe a.depart!=b.depart);
+-------+--------+
| tname | prof   |
+-------+--------+
| 李诚  | 副教授 |
| 张旭  | 讲师   |
+-------+--------+
2 rows in set (0.00 sec)


#方式2
SELECt any_value(Tname) Tname,Prof
FROM teacher 
WHERe Depart='计算机系' OR Depart='电子工程系'
GROUP BY Prof
HAVINg COUNT(1) = 1;
-->最终结果
mysql> SELECt any_value(Tname) Tname,Prof
    -> FROM teacher
    -> WHERe Depart='计算机系' OR Depart='电子工程系'
    -> GROUP BY Prof
    -> HAVINg COUNT(1) = 1;
+-------+--------+
| Tname | Prof   |
+-------+--------+
| 李诚  | 副教授 |
| 张旭  | 讲师   |
+-------+--------+
2 rows in set (0.00 sec)

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

#方式1
#第一步：查询选修编号'3-245'的同学成绩最小值-->68.0
SELECt MIN(degree) FROM score WHERe cno='3-245';
#第二步：查询选修编号'3-105'同学的成绩大于选修编号为“3-245”的同学的成绩
SELECt degree FROM score WHERe cno='3-105' AND degree>(SELECt MIN(degree) FROM score WHERe cno='3-245');
#第三步：查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序
SELECt sno,cno,degree 
FROM score 
WHERe cno='3-105' AND degree>(SELECt MIN(degree) 
FROM score WHERe cno='3-245') ORDER BY degree DESC;
-->最终结果
mysql> SELECt sno,cno,degree
    -> FROM score
    -> WHERe cno='3-105' AND degree>(SELECt MIN(degree)
    -> FROM score WHERe cno='3-245') ORDER BY degree DESC;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
+-----+-------+--------+
5 rows in set (0.00 sec)



#方式2
SELECt DISTINCT score.sno,score.cno,degree
FROM student, course,score
WHERe score.Cno = '3-105' AND student.Sno = score.Sno AND
( Degree > (SELECt MIN(Degree) FROM score WHERe Cno='3-245'))
 ORDER BY  Degree DESC;
-->最终结果
mysql> SELECt DISTINCT score.sno,score.cno,degree
    -> FROM student, course,score
    -> WHERe score.Cno = '3-105' AND student.Sno = score.Sno AND
    -> ( Degree > (SELECt MIN(Degree) FROM score WHERe Cno='3-245'))
    ->  ORDER BY  Degree DESC;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
+-----+-------+--------+
5 rows in set (0.00 sec)


 #方式3
 SELECt * 
 FROM score 
 WHERe cno='3-105' AND degree >ANY(SELECt degree FROM score WHERe cno='3-245') 
 ORDER BY  Degree DESC;
-->最终结果
mysql>  SELECt *
    ->  FROM score
    ->  WHERe cno='3-105' AND degree >ANY(SELECt degree FROM score WHERe cno='3-245')
    ->  ORDER BY  Degree DESC;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
+-----+-------+--------+
5 rows in set (0.00 sec)

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

SELECt sno,cno,degree 
FROM score 
WHERe degree>(SELECt MAX(degree) FROM score WHERe cno='3-245')

-->最终结果
mysql> SELECt sno,cno,degree
    -> FROM score
    -> WHERe degree>(SELECt MAX(degree) FROM score WHERe cno='3-245');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 107 | 3-105 |   91.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

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

SELECt DISTINCT sname NAME,ssex sex,sbirthday birthday 
FROM student 
UNIOn
SELECt tname NAME,tsex sex,tbirthday birthday
FROM teacher;

-->最终结果
mysql> SELECt DISTINCT sname NAME,ssex sex,sbirthday birthday
    -> FROM student
    -> UNIOn
    -> SELECt tname NAME,tsex sex,tbirthday birthday
    -> FROM teacher;
+------+-----+---------------------+
| NAME | sex | birthday            |
+------+-----+---------------------+
| 李军 | 男  | 1976-02-20 00:00:00 |
| 陆君 | 男  | 1974-06-03 00:00:00 |
| 匡明 | 男  | 1975-10-02 00:00:00 |
| 王丽 | 女  | 1976-01-23 00:00:00 |
| 曾华 | 男  | 1977-09-01 00:00:00 |
| 王芳 | 女  | 1975-02-20 00:00:00 |
| 李诚 | 男  | 1958-12-02 00:00:00 |
| 王萍 | 女  | 1972-05-05 00:00:00 |
| 刘冰 | 女  | 1977-08-14 00:00:00 |
| 张旭 | 男  | 1969-03-12 00:00:00 |
+------+-----+---------------------+
10 rows in set (0.00 sec)

32.查询所有“女”教师和“女”同学的name、sex和birthday

SELECt DISTINCT sname NAME,ssex sex,sbirthday birthday
FROM student WHERe ssex='女'
UNIOn
SELECt tname NAME,tsex sex,tbirthday birthday
FROM teacher WHERe tsex='女'

-->最终结果
mysql> SELECt DISTINCT sname NAME,ssex sex,sbirthday birthday
    -> FROM student WHERe ssex='女'
    -> UNIOn
    -> SELECt tname NAME,tsex sex,tbirthday birthday
    -> FROM teacher WHERe tsex='女';
+------+-----+---------------------+
| NAME | sex | birthday            |
+------+-----+---------------------+
| 王丽 | 女  | 1976-01-23 00:00:00 |
| 王芳 | 女  | 1975-02-20 00:00:00 |
| 王萍 | 女  | 1972-05-05 00:00:00 |
| 刘冰 | 女  | 1977-08-14 00:00:00 |
+------+-----+---------------------+
4 rows in set (0.00 sec)

33.查询成绩比该课程平均成绩低的同学的成绩表

#方式1
SELECt * 
FROM score a  
WHERe degree < (SELECt AVG(degree) FROM score b WHERe b.cno=a.cno);
-->最终结果
mysql> SELECt *
    -> FROM score a
    -> WHERe degree < (SELECt AVG(degree) FROM score b WHERe b.cno=a.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 108 | 3-105 |   78.0 |
| 107 | 6-166 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
7 rows in set (0.00 sec)


#方式2
SELECt Sno,score.Cno Cno ,score.Degree FROM score JOIN
(SELECt Cno ,  AVG(Degree) Degree FROM score GROUP BY Cno) AS s2
ON score.Cno = s2.Cno AND score.Degree 最终结果
mysql> SELECt Sno,score.Cno Cno ,score.Degree FROM score JOIN
    -> (SELECt Cno ,  AVG(Degree) Degree FROM score GROUP BY Cno) AS s2
    -> ON score.Cno = s2.Cno AND score.Degree 最终结果
mysql> SELECt sno,cno,degree
    -> FROM score a
    -> WHERe a.degree<(SELECt AVG(degree) FROM score b WHERe a.cno=b.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 108 | 3-105 |   78.0 |
| 107 | 6-166 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
7 rows in set (0.00 sec)

34.查询所有任课教师的tname和depart

#方式1:
SELECt tname,depart
FROM teacher,course 
WHERe teacher.tno = course.tno 
AND course.cno IN (SELECt cno FROM score);
-->最终结果
mysql> SELECt tname,depart
    -> FROM teacher,course
    -> WHERe teacher.tno = course.tno
    -> AND course.cno IN (SELECt cno FROM score);
+-------+------------+
| tname | depart     |
+-------+------------+
| 李诚  | 计算机系   |
| 王萍  | 计算机系   |
| 张旭  | 电子工程系 |
+-------+------------+
3 rows in set (0.00 sec)


#方式2
SELECt tname,depart 
FROM teacher 
WHERe tname IN (SELECt DISTINCT tname FROM teacher,course,score 
WHERe teacher.tno=course.tno AND course.cno=score.cno);
-->最终结果
mysql> SELECt tname,depart
    -> FROM teacher
    -> WHERe tname IN (SELECt DISTINCT tname FROM teacher,course,score
    -> WHERe teacher.tno=course.tno AND course.cno=score.cno);
+-------+------------+
| tname | depart     |
+-------+------------+
| 李诚  | 计算机系   |
| 王萍  | 计算机系   |
| 张旭  | 电子工程系 |
+-------+------------+
3 rows in set (0.00 sec)


#方式3
SELECt tname,depart 
FROM teacher WHERe tno IN 
(SELECt tno FROM course WHERe cno IN (SELECt DISTINCT cno FROM score))
-->最终结果
mysql> SELECt tname,depart
    -> FROM teacher WHERe tno IN
    -> (SELECt tno FROM course WHERe cno IN (SELECt DISTINCT cno FROM score));
+-------+------------+
| tname | depart     |
+-------+------------+
| 李诚  | 计算机系   |
| 王萍  | 计算机系   |
| 张旭  | 电子工程系 |
+-------+------------+
3 rows in set (0.00 sec)

35.查询所有未讲课的教师的tname和depart

#方式1
SELECt tname,depart 
FROM teacher,course 
WHERe teacher.tno=course.tno 
AND course.cno NOT IN(SELECt cno FROM score);
-->最终结果
mysql> SELECt tname,depart
    -> FROM teacher,course
    -> WHERe teacher.tno=course.tno
    -> AND course.cno NOT IN(SELECt cno FROM score);
+-------+------------+
| tname | depart     |
+-------+------------+
| 刘冰  | 电子工程系 |
+-------+------------+
1 row in set (0.00 sec)


#方式2
SELECt tname,depart 
FROM teacher WHERe tname 
NOT IN (SELECt DISTINCT tname FROM teacher,course,score 
WHERe teacher.tno=course.tno AND course.cno=score.cno)
-->最终结果
mysql> SELECt tname,depart
    -> FROM teacher WHERe tname
    -> NOT IN (SELECt DISTINCT tname FROM teacher,course,score
    -> WHERe teacher.tno=course.tno AND course.cno=score.cno);
+-------+------------+
| tname | depart     |
+-------+------------+
| 刘冰  | 电子工程系 |
+-------+------------+
1 row in set (0.00 sec)

36.查询至少有2名男生的班号

#方式1
SELECt class 
FROM student  
GROUP BY class HAVINg COUNT(ssex)>=2;
-->最终结果
mysql> SELECt class
    -> FROM student
    -> GROUP BY class HAVINg COUNT(ssex)>=2;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)


#方式2
SELECt class 
FROM student
WHERe ssex='男' GROUP BY class HAVINg COUNT(*)>1;
-->最终结果
mysql> SELECt class
    -> FROM student
    -> WHERe ssex='男' GROUP BY class HAVINg COUNT(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)

37.查询student表中不姓“王”的同学记录

SELECt * FROM student WHERe sname NOT LIKE '王%'

-->最终结果
mysql> SELECt * FROM student WHERe sname NOT LIKE '王%';
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
+-----+-------+------+---------------------+-------+
4 rows in set (0.00 sec)

38.查询student表中每个学生的姓名和年龄

SELECt sname,YEAR(NOW())-YEAR(sbirthday) AS age FROM student;

-->最终结果
mysql> SELECt sname,YEAR(NOW())-YEAR(sbirthday) AS age FROM student;
+-------+------+
| sname | age  |
+-------+------+
| 李军  |   46 |
| 陆君  |   48 |
| 匡明  |   47 |
| 王丽  |   46 |
| 曾华  |   45 |
| 王芳  |   47 |
+-------+------+
6 rows in set (0.00 sec)

39.查询student表中最大和最小的sbirthday日期值

SELECt MAX(sbirthday) ,MIN(sbirthday) FROM student

-->最终结果
mysql> SELECt MAX(sbirthday) ,MIN(sbirthday) FROM student;
+---------------------+---------------------+
| MAX(sbirthday)      | MIN(sbirthday)      |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

40.以班号和年龄从大到小的顺序查询student表中的全部记录

SELECt * 
FROM student 
ORDER BY class DESC,sbirthday DESC;

-->最终结果
mysql> SELECt *
    -> FROM student
    -> ORDER BY class DESC,sbirthday DESC;
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday           | class |
+-----+-------+------+---------------------+-------+
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 |
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 |
| 105 | 匡明  | 男   | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳  | 女   | 1975-02-20 00:00:00 | 95031 |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)

41.查询“男”教师及其所上的课程

SELECt tname,cname 
FROM teacher,course 
WHERe tsex='男' AND teacher.tno=course.tno

-->最终结果
mysql> SELECt tname,cname
    -> FROM teacher,course
    -> WHERe tsex='男' AND teacher.tno=course.tno;
+-------+----------+
| tname | cname    |
+-------+----------+
| 李诚  | 操作系统 |
| 张旭  | 数字电路 |
+-------+----------+
2 rows in set (0.00 sec)

42.查询最高分同学的sno、cno和degree列

#方法一：
SELECt sno,cno,degree 
FROM score 
WHERe degree=(SELECt MAX(degree) FROM score);
-->最终结果
mysql> SELECt sno,cno,degree
    -> FROM score
    -> WHERe degree=(SELECt MAX(degree) FROM score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
+-----+-------+--------+
1 row in set (0.00 sec)


#方法二：
SELECt sno,cno,degree 
FROM score 
HAVINg degree = (SELECt MAX(degree) FROM score);
-->最终结果
mysql> SELECt sno,cno,degree
    -> FROM score
    -> HAVINg degree = (SELECt MAX(degree) FROM score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
+-----+-------+--------+
1 row in set (0.00 sec)

43.查询和“李军”同性别的所有同学的sname

SELECt sname 
FROM student 
WHERe ssex=(SELECt ssex FROM student WHERe sname='李军') AND sname NOT IN ('李军');

-->最终结果
mysql> SELECt sname
    -> FROM student
    -> WHERe ssex=(SELECt ssex FROM student WHERe sname='李军') AND sname NOT IN ('李军');
+-------+
| sname |
+-------+
| 陆君  |
| 匡明  |
| 曾华  |
+-------+
3 rows in set (0.00 sec)

44.查询和“李军”同性别并同班的同学sname

#方式1
SELECt sname 
FROM student 
WHERe ssex=(SELECt ssex FROM student WHERe sname='李军') 
AND class=(SELECt class FROM student WHERe sname='李军')
AND sname NOT IN ('李军') 
-->最终结果
mysql> SELECt sname
    -> FROM student
    -> WHERe ssex=(SELECt ssex FROM student WHERe sname='李军')
    -> AND class=(SELECt class FROM student WHERe sname='李军')
    -> AND sname NOT IN ('李军') ;
+-------+
| sname |
+-------+
| 曾华  |
+-------+
1 row in set (0.00 sec)


#方法二：
SELECt sname
FROM student
WHERe (ssex,class) 
IN(SELECt ssex,class FROM student WHERe sname='李军')
AND sname NOT IN ('李军');
-->最终结果
mysql> SELECt sname
    -> FROM student
    -> WHERe (ssex,class)
    -> IN(SELECt ssex,class FROM student WHERe sname='李军')
    -> AND sname NOT IN ('李军');
+-------+
| sname |
+-------+
| 曾华  |
+-------+
1 row in set (0.00 sec)

45.查询所有选修“计算机导论”课程的“男”同学的成绩表

#方式1
SELECt score.* 
FROM score,student,course 
WHERe  score.sno=student.sno AND score.cno=course.cno 
AND course.cname='计算机导论' AND student.ssex='男';
-->最终结果
mysql> SELECt score.*
    -> FROM score,student,course
    -> WHERe  score.sno=student.sno AND score.cno=course.cno
    -> AND course.cname='计算机导论' AND student.ssex='男';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |   64.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
+-----+-------+--------+
4 rows in set (0.00 sec)


#方式2
SELECt * 
FROM score 
WHERe sno IN (SELECt sno FROM student WHERe ssex='男') 
AND cno IN (SELECt cno FROM course WHERe cname='计算机导论');
-->最终结果
mysql> SELECt *
    -> FROM score
    -> WHERe sno IN (SELECt sno FROM student WHERe ssex='男')
    -> AND cno IN (SELECt cno FROM course WHERe cname='计算机导论');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |   64.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
+-----+-------+--------+
4 rows in set (0.00 sec)