1. 全栈程序员必看首页

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

全栈程序员-用户IM • • 未分类

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

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

        声明：该文章练习题取自作者‘孙子衡’–>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%3DMySQL练习题初级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%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”的成绩的所有记录

#第一步：查询学号为109、课程号为3-105同学的成绩-->76.0
SELECT degree FROM score WHERE sno=109 AND cno='3-105';
#第二步：查询成绩高于76.0的同学的所有记录
SELECT * FROM student,score WHERE student.sno=score.sno AND score.degree>76.0;
#合并写：
SELECT * 
FROM student,score 
WHERE student.sno=score.sno 
AND score.degree>(SELECT degree FROM score WHERE sno=109 AND cno='3-105');

-->最终结果
mysql> SELECT *
    -> FROM student,score
    -> WHERE student.sno=score.sno
    -> AND score.degree>(SELECT degree FROM score WHERE sno=109 AND cno='3-105');
+-----+-------+------+---------------------+-------+-----+-------+--------+
| sno | sname | ssex | sbirthday           | class | sno | cno   | degree |
+-----+-------+------+---------------------+-------+-----+-------+--------+
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 |   86.0 |
| 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 |
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 |   85.0 |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 6-166 |   79.0 |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 |   81.0 |
+-----+-------+------+---------------------+-------+-----+-------+--------+
8 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

/*
    解析：意思就是，一个同学同时选了3-105和3-245的课程，查询出这个
    同学选修3-105课程的成绩大于他选修3-245课程的成绩的cno、sno和degree的记录
*/
SELECT * 
FROM score a 
WHERE a.cno = '3-105' AND a.degree > ( SELECT b.degree FROM score b WHERE cno = '3-245' AND a.sno = b.sno )

-->最终结果
mysql> SELECT *
    -> FROM score a
    -> WHERE a.cno = '3-105' AND a.degree > ( SELECT b.degree FROM score b WHERE cno = '3-245' AND a.sno = b.sno );
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.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 <s2.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 <s2.Degree;
+-----+-------+--------+
| 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)


#方式3
SELECT sno,cno,degree 
FROM score a 
WHERE a.degree<(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno)
-->最终结果
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)

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/194112.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号