查询练习
-23 查询95033班和95031班全体学生的记录
//
在这里where添加条件限制
在条件为多个的时候用 in
单个条件可以用 =
//
mysql> select * from student where class in(‘95031’,’95033’);
+———–+—–+—–+————+——-+
| sname | no | sex | birthday | class |
+———–+—–+—–+————+——-+
| 曾华 | 101 | 男 | 1977-09-01 | 95033 |
| 匡明 | 102 | 男 | 1975-10-02 | 95031 |
| 王丽 | 103 | 女 | 1976-01-23 | 95033 |
| 李军 | 104 | 男 | 1976-02-20 | 95033 |
| 王芳 | 105 | 女 | 1975-02-10 | 95031 |
| 陆军 | 106 | 男 | 1974-06-03 | 95031 |
| 王尼玛 | 107 | 男 | 1976-02-20 | 95033 |
| 张全蛋 | 108 | 男 | 1975-02-10 | 95031 |
| 赵铁柱 | 109 | 男 | 1974-06-03 | 95031 |
+———–+—–+—–+————+——-+
-24.查询存在85分以上成绩的课程c_no
依然是练习where条件语句
mysql> select c_no,degree from score where degree >85;
+——-+——–+
| c_no | degree |
+——-+——–+
| 3-105 | 90 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 89 |
| 3-105 | 88 |
+——-+——–+
-25.查询出“计算机系”教师所教课程的成绩表
select * from teacher where department=’计算机系’；
+—–+——–+—–+————+————+————–+
| no | name | sex | birthday | profession | department |
+—–+——–+—–+————+————+————–+
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
+—–+——–+—–+————+————+————–+
select * from course where t_no in(select no from teacher where department=’计算机系’)；
+——-+—————–+——+
| no | name | t_no |
+——-+—————–+——+
| 3-245 | 操作系统 | 804 |
| 3-105 | 计算机导论 | 825 |
+——-+—————–+——+
//这里要注意多层套娃条件的时候，结尾处的括号个数一定要与前面的一致，要不会出错的
select * from score where c_no in(select no from course where t_no in(select no from teacher where department=’计算机系’));
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 81 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+——+——-+——–+
-26.查询“计算机系”与“电子工程系”不同职称的教师的名字 name
–union联合查询
–not in
为了方便理解这里先查看以下教师表格
mysql> select * from teacher;
+—–+——–+—–+————+————+—————–+
| no | name | sex | birthday | profession | department |
+—–+——–+—–+————+————+—————–+
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
+—–+——–+—–+————+————+—————–+
——问题是要查找什么再简单一点说明——
找到计算机系和电子工程系中 职称不重复的教师名字
用集合的概念去想
这里计算机系和电子工程系 重合部分是助教，计算机系非助教的集合+电子工程系非助教的集合
select * from teacher where department =’计算机系’ and profession not in(select
profession from teacher where department=’电子工程系’);
+—–+——–+—–+————+————+————–+
| no | name | sex | birthday | profession | department |
+—–+——–+—–+————+————+————–+
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
+—–+——–+—–+————+————+————–+
select * from teacher where department =’电子工程系’ and profession not in(select
profession from teacher where department=’计算机系’);
+—–+——–+—–+————+————+—————–+
| no | name | sex | birthday | profession | department |
+—–+——–+—–+————+————+—————–+
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
+—–+——–+—–+————+————+—————–+
两个语句通过union语句联合起来，代表同时满足两个条件
//分号代表语句结束 只能末尾有哦
select * from teacher where department =’计算机系’ and profession not in(select
profession from teacher where department=’电子工程系’)
union
select * from teacher where department =’电子工程系’ and profession not in(select
profession from teacher where department=’计算机系’);
+—–+——–+—–+————+————+—————–+
| no | name | sex | birthday | profession | department |
+—–+——–+—–+————+————+—————–+
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
+—–+——–+—–+————+————+—————–+
-27.查询选修编号为“3-105”课程且成绩至少高于选修编号“3-245”的同学的c_no，s_no和degree，并且按照degree从高到低次序排列
/问题理解/
至少高于?—>大于其中至少一个，any
选修“3-105”课程的同学，只要成绩高于选修“3-245”同学中的至少一名同学的成绩，这个数据就被选择
select * from score where c_no=’3-105’ and degree>any(select degree from score where c_no=’3-245’);
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 81 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+——+——-+——–+
//紧接着进行排序
select * from score where c_no=’3-105’ and degree>any(select degree from score where c_no=’3-245’) order by degree desc;
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 103 | 3-105 | 92 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 102 | 3-105 | 81 |
| 109 | 3-105 | 76 |
+——+——-+——–+
-28.查询选修编号为“3-105”课程且成绩至少高于选修编号“3-245”的同学的c_no，s_no和degree
–且？ all表示所有的关系
select * from score where c_no=’3-105’ and degree>all(select degree from score where c_no=’3-245’);
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 101 | 3-105 | 90 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
+——+——-+——–+
-29.查询所有教师和同学的name，sex，birthday
//
先看数据 在我进行操作之后name和sname都变成了name
因为所有数据名称都是按查找的第一组数据的命名来算的
//
select name ,sex,birthday from teacher
union
select sname ,sex,birthday from student;
+———–+—–+————+
| name | sex | birthday |
+———–+—–+————+
| 李诚 | 男 | 1958-12-02 |
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 张旭 | 男 | 1969-03-12 |
| 曾华 | 男 | 1977-09-01 |
| 匡明 | 男 | 1975-10-02 |
| 王丽 | 女 | 1976-01-23 |
| 李军 | 男 | 1976-02-20 |
| 王芳 | 女 | 1975-02-10 |
| 陆军 | 男 | 1974-06-03 |
| 王尼玛 | 男 | 1976-02-20 |
| 张全蛋 | 男 | 1975-02-10 |
| 赵铁柱 | 男 | 1974-06-03 |
| 张飞 | 男 | 1974-06-03 |
+———–+—–+————+
//为了看效果这里将两个查询互换位置//
select sname as trueename,sex,birthday from student
union
select name,sex,birthday from teacher;
+———–+—–+————+
| trueename | sex | birthday |
+———–+—–+————+
| 曾华 | 男 | 1977-09-01 |
| 匡明 | 男 | 1975-10-02 |
| 王丽 | 女 | 1976-01-23 |
| 李军 | 男 | 1976-02-20 |
| 王芳 | 女 | 1975-02-10 |
| 陆军 | 男 | 1974-06-03 |
| 王尼玛 | 男 | 1976-02-20 |
| 张全蛋 | 男 | 1975-02-10 |
| 赵铁柱 | 男 | 1974-06-03 |
| 张飞 | 男 | 1974-06-03 |
| 李诚 | 男 | 1958-12-02 |
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 张旭 | 男 | 1969-03-12 |
+———–+—–+————+
-30.查询所有女教师和女同学的name，sex，birthday
同理上一道题，到后面视频是越来越短。。。。就是加了一个where条件
我最开始输入的信息都是name,sex,birthday 其实不需要改变，这里换个大写
select name as Name,sex as Sex,birthday as Birthday from teacher where sex=’女’
union
select sname,sex,birthday from student where sex=’女’;
+——–+—–+————+
| Name | Sex | Birthday |
+——–+—–+————+
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 王丽 | 女 | 1976-01-23 |
| 王芳 | 女 | 1975-02-10 |
+——–+—–+————+
-31.查询成绩比该课程平均成绩低的同学的成绩表
各门课程的平均成绩
mysql> select avg(degree) from score group by c_no;
+————-+
| avg(degree) |
+————-+
| 86.0000 |
| 76.3333 |
| 81.6667 |
+————-+
这里为了找出低于平均分的成绩，将表格中的每一个数据都和平均成绩进行比较
//？？？为什么不用和之前一样的方法 where条件语句+group by 分类比较呢？？？//
验证一下
mysql> select * from score where degree<(select avg(degree) from score a group
by c_no);
ERROR 1242 (21000): Subquery returns more than 1 row
这里erro的意思大概是 子查询返回多行
我也没看定义，就可以理解成课程平均成绩有多个，这里寻找低于平均值数据的时候，是要在同一门课程比较的
用group by无法知道应该谁和谁比较
正确操作
//score a 和score b都是指向score表，数据相同 。不信的话动手实践
select * from score a;
select * from score b;
去看结果相同不？
这里用where a.c_no=b.c_no去相同课程一一对应比较//
select * from score a where degree<(select avg(degree) from score b where a.c_no=b.c_no);
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 102 | 3-105 | 81 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+——+——-+——–+
-32.查询排了课的教师的name和department
select name ,department from teacher where no in(select t_no from course);
+——–+—————–+
| name | department |
+——–+—————–+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
+——–+—————–+
-33.查询至少有两名男生的班级号
select class from student where sex=’男’ group by class having count(*)>1;
+——-+
| class |
+——-+
| 95033 |
| 95031 |
+——-+
-34.查询student表中不姓王的同学记录
//like ‘…..%’ 模糊查询
select * from student where sname not like ‘王%’;
+———–+—–+—–+————+——-+
| sname | no | sex | birthday | class |
+———–+—–+—–+————+——-+
| 曾华 | 101 | 男 | 1977-09-01 | 95033 |
| 匡明 | 102 | 男 | 1975-10-02 | 95031 |
| 李军 | 104 | 男 | 1976-02-20 | 95033 |
| 陆军 | 106 | 男 | 1974-06-03 | 95031 |
| 张全蛋 | 108 | 男 | 1975-02-10 | 95031 |
| 赵铁柱 | 109 | 男 | 1974-06-03 | 95031 |
| 张飞 | 110 | 男 | 1974-06-03 | 95038 |
+———–+—–+—–+————+——-+
-35.查询student表中的每个学生的姓名和年龄
–年龄=当前年份-出生年月
select year(now());
+————-+
| year(now()) |
+————-+
| 2021 |
+————-+
select year(birthday) from student;
+—————-+
| year(birthday) |
+—————-+
| 1977 |
| 1975 |
| 1976 |
| 1976 |
| 1975 |
| 1974 |
| 1976 |
| 1975 |
| 1974 |
| 1974 |
+—————-+
!!有点高级了
select sname,year(now())-year(birthday) as ‘年龄’ from student;
+———–+——–+
| sname | 年龄 |
+———–+——–+
| 曾华 | 44 |
| 匡明 | 46 |
| 王丽 | 45 |
| 李军 | 45 |
| 王芳 | 46 |
| 陆军 | 47 |
| 王尼玛 | 45 |
| 张全蛋 | 46 |
| 赵铁柱 | 47 |
| 张飞 | 47 |
+———–+——–+
36.查询student表中最大和最小的birthday日期值
select max(birthday) as ‘最大’,min(birthday) as ‘最小’ from student;
+————+————+
| 最大 | 最小 |
+————+————+
| 1977-09-01 | 1974-06-03 |
+————+————+
37.以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,birthday asc;
这里的asc升序排列，可以省略。
生日升序排列相当于年龄降序排列
+———–+—–+—–+————+——-+
| sname | no | sex | birthday | class |
+———–+—–+—–+————+——-+
| 张飞 | 110 | 男 | 1974-06-03 | 95038 |
| 王丽 | 103 | 女 | 1976-01-23 | 95033 |
| 李军 | 104 | 男 | 1976-02-20 | 95033 |
| 王尼玛 | 107 | 男 | 1976-02-20 | 95033 |
| 曾华 | 101 | 男 | 1977-09-01 | 95033 |
| 陆军 | 106 | 男 | 1974-06-03 | 95031 |
| 赵铁柱 | 109 | 男 | 1974-06-03 | 95031 |
| 王芳 | 105 | 女 | 1975-02-10 | 95031 |
| 张全蛋 | 108 | 男 | 1975-02-10 | 95031 |
| 匡明 | 102 | 男 | 1975-10-02 | 95031 |
+———–+—–+—–+————+——-+
-38.查询男教师及其所上的课程
select * from course where t_no in(select no from teacher where sex=’男’);
+——-+————–+——+
| no | name | t_no |
+——-+————–+——+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
+——-+————–+——+
-39.查询最高分同学的s_no,c_no,degree
select * from score where degree=(select max(degree) from score);
//这里用的where =
但是如果两个同学并列最高分的时候还是用where in 比较严谨
//
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 103 | 3-105 | 92 |
+——+——-+——–+
-40.查询和李军同性别的所有同学的sname
select sname from student where sex=(select sex from student where sname=’李军’);
+———–+
| sname |
+———–+
| 曾华 |
| 匡明 |
| 李军 |
| 陆军 |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
| 张飞 |
+———–+
-41.查询和李军同性别并且同班级的同学sname
select sname from student where sex=(select sex from student where sname=’李军’)
and
class=(select class from student where sname=’李军’);
+———–+
| sname |
+———–+
| 曾华 |
| 李军 |
| 王尼玛 |
+———–+
-42.查询所有选修计算机导论课程的男同学的成绩表
select * from score where c_no=(select no from course where name=’计算机导论’)
and
s_no in (select no from student where sex=’男’);
+——+——-+——–+
| s_no | c_no | degree |
+——+——-+——–+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 81 |
| 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’);
select s_no,c_no,grade from score,grade where degree between low and upp;
+——+——-+——-+
| s_no | c_no | grade |
+——+——-+——-+
| 101 | 3-105 | A |
| 102 | 3-105 | B |
| 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 |
+——+——-+——-+
例题到此结束
之后进入mysql第三部分，就剩下最后十节课啦！
