来自B站课程 "一天学会MYSQL数据库"1的大刘课程的全部练习题，老师有些单词拼的不对，我自己重新修改了字段名，整理如下
新建一个查询练习用的数据库 University
CREATE DATABASE University;创建学生表:students（学号， 姓名， 性别， 出生日期， 所在班级）
CREATE TABLE students( s_num VARCHAR(20) PRIMARY KEY COMMENT'学生学号', s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空', s_sex VARCHAR(10) NOT NULL COMMENT'学生性别', s_birthday DATETIME COMMENT'学生生日', s_class VARCHAR(20) COMMENT'学生所在的班级' );创建教师表：teachers（教师编号，教师名字，教师性别，出生日期，职称，所在部门）
CREATE TABLE teachers( t_num VARCHAR(20) PRIMARY KEY COMMENT'教师编号', t_name VARCHAR(20) NOT NULL COMMENT'教师姓名', t_sex VARCHAR(20) NOT NULL COMMENT'教师性别', t_birthday DATETIME COMMENT'教师生日', t_title VARCHAR(20) NOT NULL COMMENT'教师职称', t_department VARCHAR(20) NOT NULL COMMENT'教师所在的部门' );创建课程表:courses（课程号，课程课程名称，教师编号）
CREATE TABLE courses( c_num VARCHAR(20) PRIMARY KEY COMMENT'课程号', c_name VARCHAR(20) NOT NULL COMMENT'课程名称', t_num VARCHAR(20) NOT NULL COMMENT '教师编号 外键关联teacher表', FOREIGN KEY(t_num) references teachers(t_num) );创建成绩表：scores（学号，课程号，成绩）
CREATE TABLE scores ( s_num VARCHAR(20) NOT NULL COMMENT '成绩表的编号 依赖学生学号', c_num VARCHAR(20) NOT NULL COMMENT '课程号 依赖于课程表中的c_id', score decimal, foreign key(s_num) references students(s_num), foreign key(c_num) references courses(c_num), PRIMARY KEY(s_num,c_num) );分别向表中添加数据
--学生表数据 INSERT INTO students VALUES('101','曾华','男','1977-09-01','95033'); INSERT INTO students VALUES('102','匡明','男','1975-10-02','95031'); INSERT INTO students VALUES('103','王丽','女','1976-01-23','95033'); INSERT INTO students VALUES('104','李军','男','1976-02-20','95033'); INSERT INTO students VALUES('105','王芳','女','1975-02-10','95031'); INSERT INTO students VALUES('106','陆军','男','1974-06-03','95031'); INSERT INTO students VALUES('107','王尼玛','男','1976-02-20','95033'); INSERT INTO students VALUES('108','张全蛋','男','1975-02-10','95031'); INSERT INTO students VALUES('109','赵铁柱','男','1974-06-03','95031'); --教师表数据 INSERT INTO teachers VALUES('804','李诚','男','1958-12-02','副教授','计算机系'); INSERT INTO teachers VALUES('856','张旭','男','1969-03-12','讲师','电子工程系'); INSERT INTO teachers VALUES('825','王萍','女','1972-05-05','助教','计算机系'); INSERT INTO teachers VALUES('831','刘冰','女','1977-08-14','助教','电子工程系'); --添加课程表 INSERT INTO courses VALUES('3-105','计算机导论','825'); INSERT INTO courses VALUES('3-245','操作系统','804'); INSERT INTO courses VALUES('6-166','数字电路','856'); INSERT INTO courses VALUES('9-888','高等数学','831'); --添加成绩表 INSERT INTO scores VALUES('103','3-245','86'); INSERT INTO scores VALUES('105','3-245','75'); INSERT INTO scores VALUES('109','3-245','68'); INSERT INTO scores VALUES('103','3-105','92'); INSERT INTO scores VALUES('105','3-105','88'); INSERT INTO scores VALUES('109','3-105','76'); INSERT INTO scores VALUES('103','6-166','85'); INSERT INTO scores VALUES('105','6-166','79'); INSERT INTO scores VALUES('109','6-166','81');几张表的数据展现
mysql> select* from students; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+---------------------+---------+ mysql> select* from teachers; +-------+--------+-------+---------------------+---------+--------------+ | t_num | t_name | t_sex | t_birthday | t_title | t_department | +-------+--------+-------+---------------------+---------+--------------+ | 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 | 讲师 | 电子工程系 | +-------+--------+-------+---------------------+---------+--------------+ mysql> select * from courses; +-------+------------+-------+ | c_num | c_name | t_num | +-------+------------+-------+ | 3-105 | 计算机导论 | 825 | | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | | 9-888 | 高等数学 | 831 | +-------+------------+-------+ mysql> select * from scores; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +-------+-------+-------+开始 练习 1.查询student表中所有的记录
mysql> select* from students; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+---------------------+---------+2.查询student表中所有记录的s_name,s_sex和s_class列
mysql> SELECT s_num,s_name,s_class FROM students; +-------+--------+---------+ | s_num | s_name | s_class | +-------+--------+---------+ | 101 | 曾华 | 95033 | | 102 | 匡明 | 95031 | | 103 | 王丽 | 95033 | | 104 | 李军 | 95033 | | 105 | 王芳 | 95031 | | 106 | 陆军 | 95031 | | 107 | 王尼玛 | 95033 | | 108 | 张全蛋 | 95031 | | 109 | 赵铁柱 | 95031 | | 110 | 刘表 | NULL | +-------+--------+---------+3.查询教师所有的单位但是不重复的t_department列
mysql> SELECT distinct(t_department) FROM teachers; +--------------+ | t_department | +--------------+ | 计算机系 | | 电子工程系 | +--------------+ 2 rows in set (0.00 sec)4.查询score表中成绩在60-80之间所有的记录
注意:BETWEEN… ADN… 是包含边界的
mysql> SELECT * FROM scores WHERE score BETWEEN 61 AND 79; mysql> SELECT * FROM scores WHERE score >60 and score < 80; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | +-------+-------+-------+5.查询score表中成绩为85, 86, 或者88的记录
mysql> SELECT * FROM scores WHERE score IN(85, 86, 88); +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | +-------+-------+-------+6.查询student表中’95031’班或者性别为’女’的同学记录
mysql> SELECT * FROM students WHERE s_class = '95031' OR s_sex = '女'; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+ 6 rows in set (0.00 sec)7.以class降序查询student表中所有的记录
mysql> SELECT * FROM students ORDER BY s_class desc; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+---------------------+---------+8.以c_num升序.score降序插叙scores表中所有的数据
mysql> SELECT * FROM scores ORDER BY c_num ASC, score DESC; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 103 | 3-105 | 92 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 103 | 6-166 | 85 | | 109 | 6-166 | 81 | | 105 | 6-166 | 79 | +-------+-------+-------+9.查询’95031’班的学生人数
mysql> SELECT COUNT(s_num) FROM students WHERE s_class = '95031'; +--------------+ | COUNT(s_num) | +--------------+ | 5 | +--------------+10.查询score表中的最高分数的学生号和课程号.
mysql> SELECT c_num, s_num FROM scores WHERE score = (SELECT MAX(score) FROM scores); +-------+-------+ | c_num | s_num | +-------+-------+ | 3-105 | 103 | +-------+-------+11.查询每门课的平均成绩
mysql> select c_num, avg(score) from scores group by c_num; +-------+-------------------+ | c_num | avg(score) | +-------+-------------------+ | 3-105 | 85.33333333333333 | | 3-245 | 76.33333333333333 | | 6-166 | 81.66666666666667 | +-------+-------------------+12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
mysql> select c_num, avg(score) from scores group by c_num having count(s_num)>=2 -> and c_num like "3%"; +-------+-------------------+ | c_num | avg(score) | +-------+-------------------+ | 3-105 | 87.66666666666667 | | 3-245 | 76.33333333333333 | +-------+-------------------+ 2 rows in set (0.00 sec)13.查询分数大于70但是小于90的s_num列:
mysql> select s_num, c_num from scores where score > 70 and score < 90 ; +-------+-------+ | s_num | c_num | +-------+-------+ | 103 | 3-245 | | 103 | 6-166 | | 104 | 3-105 | | 105 | 3-105 | | 105 | 3-245 | | 105 | 6-166 | | 109 | 3-105 | | 109 | 6-166 | +-------+-------+14.多表查询 s_name , scores .
mysql>select s_name,students.s_num, c_num, score from students, scores where students.s_num = scores.s_num; +--------+-------+-------+-------+ | s_name | s_num | c_num | score | +--------+-------+-------+-------+ | 王丽 | 103 | 3-105 | 92 | | 王丽 | 103 | 3-245 | 86 | | 王丽 | 103 | 6-166 | 85 | | 王芳 | 105 | 3-105 | 88 | | 王芳 | 105 | 3-245 | 75 | | 王芳 | 105 | 6-166 | 79 | | 赵铁柱 | 109 | 3-105 | 76 | | 赵铁柱 | 109 | 3-245 | 68 | | 赵铁柱 | 109 | 6-166 | 81 | +--------+-------+-------+-------+15.查询所有学生的s_num, c_name, score 数据来自三个表
mysql> select students.s_num, c_name ,score from students, courses, scores -> where students.s_num = scores.s_num and scores.c_num = courses.c_num; +-------+------------+-------+ | s_num | c_name | score | +-------+------------+-------+ | 103 | 计算机导论 | 92 | | 103 | 操作系统 | 86 | | 103 | 数字电路 | 85 | | 105 | 计算机导论 | 88 | | 105 | 操作系统 | 75 | | 105 | 数字电路 | 79 | | 109 | 计算机导论 | 76 | | 109 | 操作系统 | 68 | | 109 | 数字电路 | 81 | +-------+------------+-------+16.查询所有的学生 s_name , c_name, sc_degree列 , 将上面的s_num 换成s_name
mysql> select students.s_name, c_name ,score from students, courses, scores -> where students.s_num = scores.s_num and scores.c_num = courses.c_num; +--------+------------+-------+ | s_name | c_name | score | +--------+------------+-------+ | 王丽 | 计算机导论 | 92 | | 王丽 | 操作系统 | 86 | | 王丽 | 数字电路 | 85 | | 王芳 | 计算机导论 | 88 | | 王芳 | 操作系统 | 75 | | 王芳 | 数字电路 | 79 | | 赵铁柱 | 计算机导论 | 76 | | 赵铁柱 | 操作系统 | 68 | | 赵铁柱 | 数字电路 | 81 | +--------+------------+-------+ 查询班级是’95031’班学生每门课的平均分 mysql>select avg(score) from scores ->where s_num in (select s_num from students where s_class = '95031') ->group by c_num; +-------+-------------------+ | c_no | AVG(sc.sc_degree) | +-------+-------------------+ | 3-105 | 82.0000 | | 3-245 | 71.5000 | | 6-166 | 80.0000 | +-------+-------------------+18.查询选修"3-105"课程的成绩高于’109’号同 学’3-105’成绩 的所有同学的姓名 学号 成绩
mysql> select students.s_num ,students.s_name ,score from students ,scores -> where c_num = '3-105' and score> (select score from scores where s_num = '109' and c_num ='3-105') and students.s_num = scores.s_num; +-------+--------+-------+ | s_num | s_name | score | +-------+--------+-------+ | 101 | 曾华 | 90 | | 102 | 匡明 | 91 | | 103 | 王丽 | 92 | | 104 | 李军 | 89 | | 105 | 王芳 | 88 | +-------+--------+-------+19.查询成绩 高于学号为’109’, 课程号为’3-105’的成绩的所有记录
mysql> SELECT * FROM scores WHERE score >(SELECT score FROM scores WHERE s_num = '109' AND c_num = '3-105'); +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +-------+-------+-------+ 查询所有学号为108. 101 的同学同年出生的所有学生的s_num,s_name和s_birthday mysql> SELECT * FROM students WHERE YEAR(s_birthday) -> IN (SELECT YEAR(s_birthday) FROM students WHERE s_num IN('108','101')); +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+21.查询 张旭 教师任课的学生的成绩
mysql> select * from students where s_num IN (SELECT s_num FROM scores WHERE c_num = (SELECT c_num FROM courses WHERE t_num = (SELECT t_num FROM teachers WHERE t_name='张旭'))); +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+22.查询选修课程的同学人数多余 5 人的教师姓名
mysql> INSERT INTO scores VALUES('101','3-105','90'); --为了效果，添加几条数据 Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO scores VALUES('102','3-105','91'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO scores VALUES('104','3-105','89'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM teachers WHERE t_num IN (SELECT t_num FROM courses WHERE c_num IN (SELECT c_num FROM scores GROUP BY c_num HAVING COUNT(s_num) > 5)); +-------+--------+-------+---------------------+---------+--------------+ | t_num | t_name | t_sex | t_birthday | t_title | t_department | +-------+--------+-------+---------------------+---------+--------------+ | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | +-------+--------+-------+---------------------+---------+--------------23.查询95033班和95031班全体学生的记录
mysql> SELECT * FROM students WHERE s_class IN('95031','95033') ORDER BY s_class ; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | +-------+--------+-------+---------------------+---------+24.查询存在85分以上成绩的课程c_num
mysql> SELECT * FROM scores where score > 85; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | +-------+-------+-------+25.查出所有’计算机系’ 教师所教课程的成绩表
mysql> SELECT * FROM scores WHERE c_num IN (SELECT c_num FROM courses WHERE t_num IN (SELECT t_num FROM teachers WHERE t_department = '计算机系')); +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +-------+-------+-------+26.查询’计算机系’与’电子工程系’ 不同职称的教师的name和title
mysql> SELECT * FROM teachers WHERE t_department = '计算机系' AND t_title NOT IN (SELECT t_title FROM teachers WHERE t_department = '电子工程系') -> UNION -> SELECT * FROM teachers WHERE t_department = '电子工程系' AND t_title NOT IN (SELECT t_title FROM teachers WHERE t_department = '计算机系'); +-------+--------+-------+---------------------+---------+--------------+ | t_num | t_name | t_sex | t_birthday | t_title | t_department | +-------+--------+-------+---------------------+---------+--------------+ | 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | | 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | +-------+--------+-------+---------------------+---------+--------------+27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_num,s_num和score,并且按照score从高到地次序排序
mysql> select * from scores where c_num = '3-105' AND score > ANY(SELECT score FROM scores WHERE c_num = '3-245' ) ORDER BY score desc ; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 103 | 3-105 | 92 | | 102 | 3-105 | 91 | | 101 | 3-105 | 90 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +-------+-------+-------+28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_num .s_num 和score
mysql> SELECT * FROM scores WHERE score > ALL (select score from scores WHERE c_num = '3-245') AND c_num= '3-105'; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | +-------+-------+-------+29查询所有教师和同学的 name ,sex, birthday
mysql> SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday FROM students -> UNION -> SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teachers; +--------+-----+---------------------+ | name | sex | birthday | +--------+-----+---------------------+ | 曾华 | 男 | 1977-09-01 00:00:00 | | 匡明 | 男 | 1975-10-02 00:00:00 | | 王丽 | 女 | 1976-01-23 00:00:00 | | 李军 | 男 | 1976-02-20 00:00:00 | | 王芳 | 女 | 1975-02-10 00:00:00 | | 陆军 | 男 | 1974-06-03 00:00:00 | | 王尼玛 | 男 | 1976-02-20 00:00:00 | | 张全蛋 | 男 | 1975-02-10 00:00:00 | | 赵铁柱 | 男 | 1974-06-03 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 | +--------+-----+---------------------+30.查询所有’女’教师和’女’学生的name,sex,birthday
mysql> SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday FROM students WHERE s_sex = '女' -> UNION -> SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teachers WHERE t_sex = '女'; +------+-----+---------------------+ | name | sex | birthday | +------+-----+---------------------+ | 王丽 | 女 | 1976-01-23 00:00:00 | | 王芳 | 女 | 1975-02-10 00:00:00 | | 王萍 | 女 | 1972-05-05 00:00:00 | | 刘冰 | 女 | 1977-08-14 00:00:00 | +------+-----+---------------------+31.查询成绩比该课程平均成绩低的同学的成绩表
mysql> select * from scores as a where score< -> (select avg(score) from scores as b where b.c_num = a.c_num group by c_num) ; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +-------+-------+-------+32.查询所有任课教师的t_name 和 t_depart(要在课程表中可以查得到)
mysql> SELECT * FROM teachers WHERE t_num IN(SELECT t_num FROM courses); +-------+--------+-------+---------------------+---------+--------------+ | t_num | t_name | t_sex | t_birthday | t_title | t_department | +-------+--------+-------+---------------------+---------+--------------+ | 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 | 讲师 | 电子工程系 | +-------+--------+-------+---------------------+---------+--------------+33.查出至少有2名男生的班号
mysql> SELECT s_class FROM students WHERE s_sex = '男' GROUP BY s_class HAVING COUNT(s_num) > 1; +---------+ | s_class | +---------+ | 95033 | | 95031 | +---------+34.查询student 表中 不姓"王"的同学的记录
mysql> SELECT * FROM students WHERE s_name NOT LIKE '王%'; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+ 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份) mysql> SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM students; +--------+------+ | s_name | age | +--------+------+ | 曾华 | 43 | | 匡明 | 45 | | 王丽 | 44 | | 李军 | 44 | | 王芳 | 45 | | 陆军 | 46 | | 王尼玛 | 44 | | 张全蛋 | 45 | | 赵铁柱 | 46 | | 刘表 | NULL | +--------+------+ 查询student中最大和最小的 s_birthday的值 mysql> select max(s_birthday),min(s_birthday) from students; +---------------------+---------------------+ | max(s_birthday) | min(s_birthday) | +---------------------+---------------------+ | 1977-09-01 00:00:00 | 1974-06-03 00:00:00 | +---------------------+---------------------+37.以班级号和年龄从大到小的顺序查询student表中的全部记录
mysql> SELECt * FROM students ORDER BY s_class DESC, s_birthday; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+---------------------+---------+38.查询"男"教师 及其所上的课
mysql> SELECT * FROM courses WHERE t_num IN (SELECT t_num FROM teachers WHERE t_sex = '男'); +-------+----------+-------+ | c_num | c_name | t_num | +-------+----------+-------+ | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | +-------+----------+-------+39.查询最高分同学的s_num ,c_num 和 score
mysql> SELECT * FROM scores WHERE score in (select MAX(score) FROM scores); +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 103 | 3-105 | 92 | +-------+-------+-------+ 查询和"李军"同性别的所有同学的 s_name mysql> SELECT s_name, s_sex FROM students WHERE s_sex = (SELECT s_sex FROM students WHERE s_name = '李军'); +--------+-------+ | s_name | s_sex | +--------+-------+ | 曾华 | 男 | | 匡明 | 男 | | 李军 | 男 | | 陆军 | 男 | | 王尼玛 | 男 | | 张全蛋 | 男 | | 赵铁柱 | 男 | | 刘表 | 男 | +--------+-------+41.查询和"李军"同性别并且同班的所有同学的s_name
mysql> SELECT s_name, s_sex FROM students WHERE s_sex = (SELECT s_sex FROM students WHERE s_name = '李军') AND s_class = (SELECT s_class FROM students WHERE s_name = '李军'); +--------+-------+ | s_name | s_sex | +--------+-------+ | 曾华 | 男 | | 李军 | 男 | | 王尼玛 | 男 | +--------+-------+ 查询所有选修’计算机导论’课程的’男’同学的成绩表 mysql> select * from scores where -> s_num in (select s_num from students where s_sex ='男') -> and -> c_num in (select c_num from courses where c_name = '计算机导论'); +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 104 | 3-105 | 89 | | 109 | 3-105 | 76 | +-------+-------+-------+个人觉得很不错的SQL课程 https://www.bilibili.com/video/BV1Vt411z7wy ↩︎
