目录
- 21.查询 张旭 教师任课的学生的成绩
- 22.查询选修课程的同学人数多余 5 人的教师姓名
- 23.查询95033班和95031班全体学生的记录
- 24.查询存在85分以上成绩的课程c_no
- 25.查出所有'计算机系' 教师所教课程的成绩表
- 26.查询'计算机系'与'电子工程系' 不同职称的教师的name和rof
- 27.
- 查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
- 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree
- 29.查询所有教师和同学的 name ,sex, birthday
- 30.查询所有'女'教师和'女'学生的name,sex,birthday
- 31.查询成绩比该课程平均成绩低的同学的成绩表
- 32.查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)
- 33.查出至少有2名男生的班号
- 34.查询student 表中 不姓"王"的同学的记录
- 35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
- 36. 查询student中最大和最小的 s_birthday的值
- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录
- 38.查询"男"教师 及其所上的课
- 39.查询最高分同学的s_no c_no 和 sc_degree;
- 40. 查询和"李军"同性别的所有同学的s_name
- 41.查询和"李军"同性别并且同班的所有同学的s_name
- 42.查询所有选修'计算机导论'课程的'男'同学的成绩表
- 43. 假设使用了以下命令建立了一个grade表
21.查询 张旭 教师任课的学生的成绩
mysql> select sc_degree from score where c_no in (select c.c_no from course c where c.t_no = (select t_no from teacher where t_name = '张旭'));
+-----------+
| sc_degree |
+-----------+
| 85 |
| 79 |
| 81 |
+-----------+
22.查询选修课程的同学人数多余 5 人的教师姓名
mysql> select t.t_name from course c,teacher t where c.c_no in (select c_no from score group by c_no having count(c_no) > 5) and c.t_no = t.t_no ;
+--------+
| t_name |
+--------+
| 王萍 |
+--------+
23.查询95033班和95031班全体学生的记录
mysql> select * from student where s_class in ('95033','95031');
+------+-----------+-------+---------------------+---------+
| s_no | 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 |
+------+-----------+-------+---------------------+---------+
mysql> select * from student where s_class = '95033' or s_class = '95031';
+------+-----------+-------+---------------------+---------+
| s_no | 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 |
+------+-----------+-------+---------------------+---------+
24.查询存在85分以上成绩的课程c_no
mysql> select distinct c_no from score where sc_degree >85;
+-------+
| c_no |
+-------+
| 3-105 |
| 3-245 |
+-------+
25.查出所有’计算机系’ 教师所教课程的成绩表
mysql> select * from score where c_no in (select c_no from course where t_no in
(select t_no from teacher where t_dept = '计算机系'));
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 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和rof
- union 用于合并两个或多个select 语句的结果集(union内部的 select语句必须拥有相同数量的列 , 列也必须拥有相似的数据类型, 每条 SELECT 语句中的列的顺序必须相同)
mysql> select * from teacher where t_dept = '计算机系' and t_rof not in (select t_rof from teacher where t_dept = '电子工程系') union select * from teacher where t_dept = '电子工程系' and t_rof not in (select t_rof from teacher where t_dept = '计算机系');
+------+--------+-------+---------------------+-----------+-----------------+
| t_no | t_name | t_sex | t_brithday | t_rof | t_dept |
+------+--------+-------+---------------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+------+--------+-------+---------------------+-----------+-----------------+
27.
查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
- any表示有任何一个满足就返回true(使用于子查询)
mysql> select * from score where c_no = '3-105' and sc_degree > any(select sc_degree from score where c_no = '3-245') order by sc_degree desc;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+-----------+
mysql> select * from score where c_no = '3-105' and sc_degree > (select min(sc_degree) from score where c_no = '3-245') order by sc_degree desc;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 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_no.s_no和sc_degree
- all表示全部都满足才返回true(使用于子查询)
mysql> select * from score where c_no = '3-105' and sc_degree > all(select sc_dgree from score where c_no = '3-245');
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 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 t_name as name,t_sex as sex,t_brithday as birthday from teacher union select s_name as name,s_sex as sex,s_birthday as birthday from student;
+-----------+-----+---------------------+
| name | sex | birthday |
+-----------+-----+---------------------+
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
| 曾华 | 男 | 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 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
+-----------+-----+---------------------+
30.查询所有’女’教师和’女’学生的name,sex,birthday
mysql> select t_name as name,t_sex as sex,t_brithday as birthday from teacher where t_sex = '女' union select s_name as name,s_sex as sex,s_birthday as bir ay from student where s_sex = '女';
+--------+-----+---------------------+
| name | sex | birthday |
+--------+-----+---------------------+
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
+--------+-----+---------------------+
31.查询成绩比该课程平均成绩低的同学的成绩表
mysql> select * from score a where sc_degree < (select avg(sc_degree) from score b where b.c_no = a.c_no );
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 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 t_name,t_dept from teacher where t_no in (select t_no from course);
+--------+-----------------+
| t_name | t_dept |
+--------+-----------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
+--------+-----------------+
33.查出至少有2名男生的班号
mysql> select s_class from student group by s_class having count(s_sex = '男') >1;
+---------+
| s_class |
+---------+
| 95033 |
| 95031 |
+---------+
mysql> select s_class from student where s_sex = '男' group by s_class having count(s_name) >1;
+---------+
| s_class |
+---------+
| 95033 |
| 95031 |
+---------+
34.查询student 表中 不姓"王"的同学的记录
- LIKE运算符的作用就是模糊匹配，NOT LIKE的使用方式与之相同，用于获取匹配不到的数据
mysql> select * from student where s_name not like '王%';
+------+-----------+-------+---------------------+---------+
| s_no | 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 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+------+-----------+-------+---------------------+---------+
35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
-
year() 获取括号内部的年
-
now() 获取当前时间
mysql> select s_name as name,(year(now()) - year(s_birthday)) as age from student; +-----------+------+ | name | age | +-----------+------+ | 曾华 | 44 | | 匡明 | 46 | | 王丽 | 45 | | 李军 | 45 | | 王芳 | 46 | | 陆军 | 47 | | 王尼玛 | 45 | | 张全蛋 | 46 | | 赵铁柱 | 47 | | 张飞 | 47 | +-----------+------+
36. 查询student中最大和最小的 s_birthday的值
- max() 获取最大值
- min() 获取最小值
mysql> select max(s_birthday) from student union select min(s_birthday) from student;
+---------------------+
| max(s_birthday) |
+---------------------+
| 1977-09-01 00:00:00 |
| 1974-06-03 00:00:00 |
+---------------------+
37.以班级号和年龄从大到小的顺序查询student表中的全部记录
mysql> select * from student order by s_class desc,s_birthday;
+------+-----------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+-----------+-------+---------------------+---------+
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
| 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 |
+------+-----------+-------+---------------------+---------+
38.查询"男"教师 及其所上的课
mysql> select t.t_no,c.c_name from teacher t,course c where t_sex = '男' and c.t_no = t.t_no;
+------+--------------+
| t_no | c_name |
+------+--------------+
| 804 | 操作系统 |
| 856 | 数字电路 |
+------+--------------+
39.查询最高分同学的s_no c_no 和 sc_degree;
mysql> select * from score where sc_degree = (select max(sc_degree) from score);
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-105 | 92 |
+------+-------+-----------+
- 取每一科的最高分
mysql> select s_no,c_no,max(sc_degree) from score group by c_no;
+-------+----------------+
| c_no | max(sc_degree) |
+-------+----------------+
| 3-105 | 92 |
| 3-245 | 86 |
| 6-166 | 85 |
+-------+----------------+
40. 查询和"李军"同性别的所有同学的s_name
mysql> select s_name from student where s_sex = (select s_sex from student where s_name = '李军') and s_name != '李军';
+-----------+
| s_name |
+-----------+
| 曾华 |
| 匡明 |
| 陆军 |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
| 张飞 |
+-----------+
41.查询和"李军"同性别并且同班的所有同学的s_name
mysql> select s_name from student where s_sex = (select s_sex from student where s_name = '李军') and s_name != '李军' and s_class = (select s_class from student where s_name = '李军');
+-----------+
| s_name |
+-----------+
| 曾华 |
| 王尼玛 |
+-----------+
42.查询所有选修’计算机导论’课程的’男’同学的成绩表
mysql> select * from score where s_no in (select s_no from student where s_sex = '男') and c_no = (select c_no from course where c_name = '计算机导论');
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
+------+-------+-----------+
43. 假设使用了以下命令建立了一个grade表
-
建表语句
CREATE TABLE grade(low INT(3),upp INT(3),grade CHAR(1)); -
插入数据
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'); -
查询所有同学的s_no , c_no 和grade列
mysql> select s_no,c_no,grade from score,grade where sc_degree between low and upp;
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | c |
| 105 | 6-166 | c |
| 109 | 3-105 | c |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
+------+-------+-------+