mysql查询练习_cid varchar(20) foreign key coach(cid),-CSDN博客

本文链接：https://blog.csdn.net/sugar_by/article/details/121211708

==mysql查询练习
==学生表
==Student
==学号
==姓名
==性别
==出生年月日
==所在班级

mysql> create table Student(
sid varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday date,
class varchar(20));
Query OK, 0 rows affected

==教师表
==Teacher
==教师编号
==教师名字
==教师性别
==出生年月日
==职称
==所在部门

mysql> create table Teacher(
tid varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday date,
prof varchar(20) not null,
depart varchar(20) not null);
Query OK, 0 rows affected

==课程表
==Course
==课程号
==课程名称
==教师编号

mysql> create table Course(
cid varchar(20) primary key,
cname varchar(20)not null,
tid varchar(20) not null,
foreign key(tid) references Teacher(tid));
Query OK, 0 rows affected

==成绩表
==Score
==学号
==课程号
==成绩

mysql> create table Score(
sid varchar(20) ,
cid varchar(20) not null,
degree decimal,
primary key(sid,cid),
foreign key(sid) references Student(sid),
foreign key(cid) references Course(cid));
Query OK, 0 rows affected

==往数据表中添加数据
#添加学生信息
insert into Student values(‘101’,‘曾华’,‘男’,‘1977-09-01’,‘95033’);
insert into Student values(‘102’,‘匡明’,‘男’,‘1975-10-02’,‘95031’);
insert into Student values(‘103’,‘王丽’,‘女’,‘1976-01-23’,‘95033’);
insert into Student values(‘104’,‘李军’,‘男’,‘1976-02-20’,‘95033’);
insert into Student values(‘105’,‘王芳’,‘女’,‘1975-02-10’,‘95031’);
insert into Student values(‘106’,‘陆君’,‘男’,‘1974-06-03’,‘95031’);
insert into Student values(‘107’,‘王尼玛’,‘男’,‘1976-02-20’,‘95033’);
insert into Student values(‘108’,‘张全三’,‘男’,‘1975-02-10’,‘95031’);
insert into Student values(‘109’,‘赵铁柱’,‘男’,‘1974-06-03’,‘95031’);

#添加教师表数据
insert into Teacher values(‘804’,‘李成’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’);
insert into Teacher values(‘856’,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’);
insert into Teacher values(‘825’,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’);
insert into Teacher values(‘831’,‘刘冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);

#添加课程表数据
insert into Course values(‘3-105’,‘计算机导论’,‘825’);
insert into Course values(‘3-245’,‘操作系统’,‘804’);
insert into Course values(‘6-166’,‘数字电路’,‘856’);
insert into Course values(‘9-888’,‘高等数学’,‘831’);

#添加成绩表数据
insert into Score values(‘103’,‘3-105’,‘92’);
insert into Score values(‘103’,‘3-245’,‘86’);
insert into Score values(‘103’,‘6-166’,‘85’);
insert into Score values(‘105’,‘3-105’,‘88’);
insert into Score values(‘105’,‘3-245’,‘75’);
insert into Score values(‘105’,‘6-166’,‘79’);
insert into Score values(‘109’,‘3-105’,‘76’);
insert into Score values(‘109’,‘3-245’,‘68’);
insert into Score values(‘109’,‘6-166’,‘81’);

==查询练习
==1.查询Student表的所有记录。
select表示查询 *表示所有字段 from表示从哪个表
mysql> select * from Student;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | 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 |
±----±-------±-----±-----------±------+
9 rows in set

==2.查询Student表中的所有记录的sname,ssex,class列。
指定sname,ssex,class字段
mysql> select sname,ssex,class from Student;
±-------±-----±------+
| sname | ssex | class |
±-------±-----±------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全三 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
±-------±-----±------+
9 rows in set

==3.查询教师所有单位即不重复的depart列

==会重复出现，所有得使用 distinct 进行排重

==4.查询Score表中成绩在60到80之间的所有记录。
方法一：查询区间 between … and …
mysql> select * from Score where degree between 60 and 80;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set

方法二：直接使用运算符比较
mysql> select * from Score where degree > 60 and degree < 80;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set

==5.查询Score表中成绩为85，86或88的记录。
==表示或者的查询 in
mysql> select * from Score where degree in(85,86,88);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
±----±------±-------+
3 rows in set

==6.查询Student表中"95031"班或性别为"女"的学生记录。
==or表示或者
mysql> select * from Student where class=‘95031’ or ssex=‘女’;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 | 95031 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
±----±-------±-----±-----------±------+
6 rows in set

==7.以class降序查询Student表的所有记录。
==升序(asc)、降序(desc)

mysql> select * from Student order by class desc;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 | 95031 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
±----±-------±-----±-----------±------+
9 rows in set

一般不写的话默认升序

mysql> select * from Student order by class;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 | 95031 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
±----±-------±-----±-----------±------+
9 rows in set

==8.以cid升序、degree降序查询Score表的所有记录。

mysql> select * from Score order by cid asc,degree desc;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 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 rows in set

==9.查询"95031"班的学生人数。
==统计 count
mysql> select count() from Student where class=‘95031’;
±---------+
| count() |
±---------+
| 5 |
±---------+
1 row in set

==10.查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
这里用了复合语句，子查询（后面会有子查询语句的详细笔记）
mysql> select sid,cid from Score where degree=(select max(degree) from Score);
±----±------+
| sid | cid |
±----±------+
| 103 | 3-105 |
±----±------+
1 row in set

==1.找到最高分

mysql> select max(degree) from Score;
±------------+
| max(degree) |
±------------+
| 92 |
±------------+
1 row in set

==2.找到最高分的sid,cid

mysql> select sid,cid from Score where degree=(select max(degree) from Score);
±----±------+
| sid | cid |
±----±------+
| 103 | 3-105 |
±----±------+
1 row in set

排序的做法：（存在一定缺陷，不建议）
limit 第一个数字表示从多少开始
第二个数字表示查多少条
limit 0,1 从0（也就是第一条）开始传1条
limit 1,2 从1（也就是第二条）开始传2条
mysql> select sid,cid,degree from Score order by degree desc limit 0,1;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
±----±------±-------+
1 row in set

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

mysql> select * from Course;
±------±-----------±----+
| cid | cname | tid |
±------±-----------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±----+
4 rows in set

==平均 avg( ）
select avg(degree) from Score where cid=‘3-105’;
select avg(degree) from Score where cid=‘3-245’;
select avg(degree) from Score where cid=‘6-166’;
select avg(degree) from Score where cid=‘9-888’;

==我能不能够在一个 mysql 语句中写呢？
== group by 分组
mysql> select cid,avg(degree) from Score group by cid;
±------±------------+
| cid | avg(degree) |
±------±------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±------------+
3 rows in set

==12.查询Score表中至少有2名学生选修并且以3开头的课程的平均成绩。
先分组
select cid from score group by cid

添加条件 having 至少有2名学生选修 count(cid)>=2 以3开头的课程 like 以什么开头
mysql> select cid from score group by cid
having count(cid)>=2 and cid like ‘3%’;
±------+
| cid |
±------+
| 3-105 |
| 3-245 |
±------+
2 rows in set

加个显示平均分 avg(degree)
mysql> select cid,avg(degree) from score group by cid
having count(cid)>=2 and cid like ‘3%’;
±------±------------+
| cid | avg(degree) |
±------±------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
±------±------------+
2 rows in set

加个显示人数 count()
mysql> select cid,avg(degree),count() from score group by cid
having count(cid)>=2 and cid like ‘3%’;
±------±------------±---------+
| cid | avg(degree) | count(*) |
±------±------------±---------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
±------±------------±---------+
2 rows in se

范围查询的俩种方法
==13.查询分数大于70，小于90的sid列

mysql> select sid,degree from score where degree>70 and degree<90;
±----±-------+
| sid | degree |
±----±-------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
±----±-------+
7 rows in set

mysql> select sid,degree from score where degree between 70 and 90;
±----±-------+
| sid | degree |
±----±-------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
±----±-------+
7 rows in set

多表查询
==14.查询所有学生的sname、cid 和 degree 列。
sname来自于student（学生）表
cid 和 degree 来自score（成绩）表

mysql> select sname from student;
±-------+
| sname |
±-------+
| 曾华 |
| 匡明 |
| 王丽 |
| 李军 |
| 王芳 |
| 陆君 |
| 王尼玛 |
| 张全三 |
| 赵铁柱 |
±-------+
9 rows in set

mysql> select cid,degree from score;
±------±-------+
| cid | degree |
±------±-------+
| 3-105 | 92 |
| 3-245 | 86 |
| 6-166 | 85 |
| 3-105 | 88 |
| 3-245 | 75 |
| 6-166 | 79 |
| 3-105 | 76 |
| 3-245 | 68 |
| 6-166 | 81 |
±------±-------+
9 rows in set

==那要怎么把他们连接起来？

mysql> select sid,sname from student;
±----±-------+
| sid | sname |
±----±-------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆君 |
| 107 | 王尼玛 |
| 108 | 张全三 |
| 109 | 赵铁柱 |
±----±-------+
9 rows in set

mysql> select sid,cid,degree from score;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 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 |
±----±------±-------+
9 rows in set

把上表的sid替换成sname就是我们要的答案，那怎么替换呢

mysql> select sname,cid,degree from student,score
where student.sid = score.sid;
±-------±------±-------+
| sname | cid | degree |
±-------±------±-------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
±-------±------±-------+
9 rows in set

==15.查询所有学生的sid、cname 和 degree列。
cname来自于course（课程）表
sid 和 degree 来自score（成绩）表

mysql> select cid,cname from course;
±------±-----------+
| cid | cname |
±------±-----------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
±------±-----------+
4 rows in set

把上表的cid替换成cname就是我们要的答案，那怎么替换呢

mysql> select sid,cname,degree from course,score
where course.cid = score.cid;
±----±-----------±-------+
| sid | cname | degree |
±----±-----------±-------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
±----±-----------±-------+
9 rows in set

三表关联查询
==16.查询所有学生的sname、cname 和 degree列。
sname来自于student（学生）表
cname来自于course（课程）表
degree 来自score（成绩）表
只有score与其他俩张都有联系
mysql> select sname,cname,degree from student,course,score
where student.sid = score.sid and course.cid = score.cid;
±-------±-----------±-------+
| sname | cname | degree |
±-------±-----------±-------+
| 王丽 | 计算机导论 | 92 |
| 王芳 | 计算机导论 | 88 |
| 赵铁柱 | 计算机导论 | 76 |
| 王丽 | 操作系统 | 86 |
| 王芳 | 操作系统 | 75 |
| 赵铁柱 | 操作系统 | 68 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 数字电路 | 81 |
±-------±-----------±-------+
9 rows in set

mysql> select sname,cname,degree,student.sid as stu_id,score.sid,course.cid as cou_id,score.cid from student,course,score
where student.sid = score.sid and course.cid = score.cid;
±-------±-----------±-------±-------±----±-------±------+
| sname | cname | degree | stu_id | sid | cou_id | cid |
±-------±-----------±-------±-------±----±-------±------+
| 王丽 | 计算机导论 | 92 | 103 | 103 | 3-105 | 3-105 |
| 王芳 | 计算机导论 | 88 | 105 | 105 | 3-105 | 3-105 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 109 | 3-105 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 103 | 3-245 | 3-245 |
| 王芳 | 操作系统 | 75 | 105 | 105 | 3-245 | 3-245 |
| 赵铁柱 | 操作系统 | 68 | 109 | 109 | 3-245 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 103 | 6-166 | 6-166 |
| 王芳 | 数字电路 | 79 | 105 | 105 | 6-166 | 6-166 |
| 赵铁柱 | 数字电路 | 81 | 109 | 109 | 6-166 | 6-166 |
±-------±-----------±-------±-------±----±-------±------+
9 rows in set

子查询加分组查询求平均值
==17.查询"95031"班的学生每门课的平均分。
先找到"95031"班的人
mysql> select *from student where class=‘95031’;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 | 95031 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
±----±-------±-----±-----------±------+
5 rows in set
再找到他们班的成绩 in表示或者再前面有讲到
mysql> select * from score where sid in (select sid from student where class=‘95031’);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±----±------±-------+
6 rows in set

再分组（group by ）求平均分 avg(degree)

mysql> select cid,avg(degree) from score
where sid in (select sid from student where class=‘95031’)
group by cid;
±------±------------+
| cid | avg(degree) |
±------±------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
±------±------------+
3 rows in set

子查询
==18.查询选修"3-105"课程的成绩高于"109"号同学"3-105"的所有同学的记录
先找到学号为"109"的同学课程号为"3-105"的成绩

mysql> select degree from score where sid =‘109’ and cid=‘3-105’;
±-------+
| degree |
±-------+
| 76 |
±-------+
1 row in set

再从成绩表中查找选修"3-105"并且成绩比他高的

mysql> select * from score
where cid=‘3-105’
and degree>(select degree from score where sid =‘109’ and cid=‘3-105’);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set

==19.查询成绩高于学号为"109",课程号为"3-105"的成绩的所有记录
（在上一题的基础上去掉课程号，只查询比他高的）

mysql> select * from score
where degree>(select degree from score where sid =‘109’ and cid=‘3-105’);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±----±------±-------+
6 rows in set

year函数与带in关键字的子查询
==20.查询和学号为108、101的同学同年出生的所有同学的sid、sname和sbirthday列。

先找到108、101学生年份

mysql> select * from student where sid in (108,101);
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
±----±-------±-----±-----------±------+
2 rows in set

如果我们只想要它的年份呢就要用到 year

mysql> select year(sbirthday) from student where sid in (108,101);
±----------------+
| year(sbirthday) |
±----------------+
| 1977 |
| 1975 |
±----------------+
2 rows in set

因为上面找到的年份是俩个所有要用 in 不用 =（等于）

mysql> select sid,sname,sbirthday from student
where year(sbirthday) in (select year(sbirthday) from student where sid in (108,101));
±----±-------±-----------+
| sid | sname | sbirthday |
±----±-------±-----------+
| 101 | 曾华 | 1977-09-01 |
| 102 | 匡明 | 1975-10-02 |
| 105 | 王芳 | 1975-02-10 |
| 108 | 张全三 | 1975-02-10 |
±----±-------±-----------+
4 rows in set

多层嵌套子查询
==21.查询"张旭"教师任课的学生成绩。
先找到这个叫"张旭"的老师

mysql> select * from teacher where tname=‘张旭’;
±----±------±-----±-----------±-----±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-----±-----------+
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-----±-----------+
1 row in set

我们可以通过tid可以知道这个老师上的什么课，通过课程表就可以知道课程id

mysql> select cid from course
where tid=(select tid from teacher where tname=‘张旭’);
±------+
| cid |
±------+
| 6-166 |
±------+

通过这个课程id(cid)就能找到上这个课学生的成绩

mysql> select * from score
where cid=(select cid from course where tid=(select tid from teacher where tname=‘张旭’));
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±----±------±-------+
3 rows in set

多表查询
==22.查询选修某课程的同学人数多与5人的教师姓名。

mysql> select * from score;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 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 |
±----±------±-------+
9 rows in set
通过成绩表发现没有那门课超过5人，所以我们要添加成绩表数据
insert into Score values(‘101’,‘3-105’,‘90’);
insert into Score values(‘102’,‘3-105’,‘91’);
insert into Score values(‘104’,‘3-105’,‘89’);

先对课程号 (cid) 分组 group by 并且找到人数超过5的课程

mysql> select cid from score group by cid having count(*)>=5;
±------+
| cid |
±------+
| 3-105 |
±------+
1 row in set

然后再通过课程找到教师

mysql> select *from teacher;
±----±------±-----±-----------±-------±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±-----------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-------±-----------+
4 rows in set

但是发现在教师表中没有 cid ，所以要通过课程表找到满足5人条件的tid
mysql> select *from course;
±------±-----------±----+
| cid | cname | tid |
±------±-----------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±----+
4 rows in set

mysql> select tid from course where cid=(select cid from score group by cid having count(*)>=5);
±----+
| tid |
±----+
| 825 |
±----+
1 row in set

然后找到教师

mysql> select tname from teacher
where tid=(select tid from course where cid=(select cid from score group by cid having count(*)>=5));
±------+
| tname |
±------+
| 王萍 |
±------+
1 row in set

in 表示或者关系（前面讲过）
==23。查询95033和95031班全体同学的记录

insert into Student values(‘110’,‘张飞’,‘男’,‘1974-06-03’,‘95038’);

mysql> select * from student;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | 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 |
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
±----±-------±-----±-----------±------+
10 rows in set

mysql> select * from student
where class in (‘95031’,‘95033’);
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | 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 |
±----±-------±-----±-----------±------+
9 rows in set

where条件查询
==24.查询存在85分以上成绩的课程 cid 。

mysql> select cid from score where degree>85;
±------+
| cid |
±------+
| 3-105 |
| 3-105 |
| 3-105 |
| 3-245 |
| 3-105 |
| 3-105 |
±------+
6 rows in set

mysql> select cid,degree from score where degree>85;
±------±-------+
| cid | degree |
±------±-------+
| 3-105 | 90 |
| 3-105 | 91 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 89 |
| 3-105 | 88 |
±------±-------+
6 rows in set

子查询
==25.查询出"计算机系"教师所教课程的成绩表。

先找到计算机系的老师

mysql> select * from teacher ;
±----±------±-----±-----------±-------±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±-----------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-------±-----------+
4 rows in set

mysql> select * from teacher where depart=‘计算机系’;
±----±------±-----±-----------±-------±---------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±---------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
±----±------±-----±-----------±-------±---------+
2 rows in set

mysql> select * from score;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 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 |
±----±------±-------+
12 rows in set

通过课程表找到课程 cid

mysql> select * from course;
±------±-----------±----+
| cid | cname | tid |
±------±-----------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±----+
4 rows in set

mysql> select * from course
where tid in ( select tid from teacher where depart=‘计算机系’);
±------±-----------±----+
| cid | cname | tid |
±------±-----------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
±------±-----------±----+
2 rows in set

通过cid找到成绩

mysql> select * from score
where cid in (select cid from course where tid in ( select tid from teacher where depart=‘计算机系’));
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±----±------±-------+
9 rows in set

union和not in的使用
==26.查询 “计算机系” 与 “电子工程系” 不同职称的教师的 tname 和 prof。

mysql> select * from teacher ;
±----±------±-----±-----------±-------±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±-----------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-------±-----------+
4 rows in set

查询电子工程系的职称有哪些

mysql> select prof from teacher where depart=‘电子工程系’;
±-----+
| prof |
±-----+
| 助教 |
| 讲师 |
±-----+
2 rows in set

查询计算机系除了电子工程系的职称外 not in 还要哪些职称

mysql> select * from teacher
where depart=‘计算机系’ and prof not in(select prof from teacher where depart=‘电子工程系’);
±----±------±-----±-----------±-------±---------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±---------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
±----±------±-----±-----------±-------±---------+
1 row in set

另外一个就反过来就行

mysql> select * from teacher
where depart=‘电子工程系’ and prof not in(select prof from teacher where depart=‘计算机系’);
±----±------±-----±-----------±-----±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-----±-----------+
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-----±-----------+
1 row in set

这里用 union（求并）就能把他们放在一个表

mysql> select * from teacher where depart = ‘计算机系’ and prof not in(select prof from teacher where depart = ‘电子工程系’)
union
select * from teacher where depart = ‘电子工程系’ and prof not in(select prof from teacher where depart = ‘计算机系’);
±----±------±-----±-----------±-------±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±-----------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-------±-----------+
2 rows in set

any表示至少一个 -desc降序
==27.查询选修编号为"3-105"课程且成绩至少高于选修编号为"3-245"的同学的cid、sid、和degree
==并按degree从高到低排序
先把"3-245"的选出来

mysql> select * from score where cid=‘3-245’;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
±----±------±-------+
3 rows in set

再把"3-105"的选出来

mysql> select * from score where cid=‘3-105’;
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±----±------±-------+
6 rows in set

==至少？至少大于其中一个 any

mysql> select * from score
where cid=‘3-105’
and degree>any(select degree from score where cid=‘3-245’);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±----±------±-------+
6 rows in set

按degree降序排序

mysql> select * from score
where cid=‘3-105’
and degree>any(select degree from score where cid=‘3-245’)
group by degree desc;
±----±------±-------+
| sid | cid | 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 |
±----±------±-------+
6 rows in set

== all 表示所有
==28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"的同学的cid、sid、和degree
==且？就是 all 所有的意思选修编号为"3-105"成绩高于所有选修编号为"3-245"

mysql> select * from score
where cid=‘3-105’
and degree>all(select degree from score where cid=‘3-245’);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
5 rows in set

==as 取别名 union求并集
==29.查询所有教师和同学的name、sex和brithday
先找老师
mysql> select tname,tsex,tbirthday from teacher;
±------±-----±-----------+
| tname | tsex | tbirthday |
±------±-----±-----------+
| 李成 | 男 | 1958-12-02 |
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 张旭 | 男 | 1969-03-12 |
±------±-----±-----------+
4 rows in set

再找学生

mysql> select sname,ssex,sbirthday from student;
±-------±-----±-----------+
| sname | ssex | sbirthday |
±-------±-----±-----------+
| 曾华 | 男 | 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 |
±-------±-----±-----------+
10 rows in set

并起来

mysql> select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbirthday from student;
±-------±-----±-----------+
| tname | tsex | tbirthday |
±-------±-----±-----------+
| 李成 | 男 | 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 |
±-------±-----±-----------+
14 rows in set

但是我们会发现字段全是t开头老师的，需要取别名（as）让它表示所有而不是老师

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday 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 |
±-------±----±-----------+
14 rows in set

==union求并集
==30.查询所有"女"教师和"女"同学的name、sex和birthday。

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex=‘女’
union
select sname,ssex,sbirthday from student where ssex=‘女’;
±-----±----±-----------+
| name | sex | birthday |
±-----±----±-----------+
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 王丽 | 女 | 1976-01-23 |
| 王芳 | 女 | 1975-02-10 |
±-----±----±-----------+
4 rows in set

==复制表数据做条件查询
==31.查询成绩比该课程平均成绩低的同学的成绩表。
先看一下每门课程的平均成绩

mysql> select cid,avg(degree) from score group by cid;
±------±------------+
| cid | avg(degree) |
±------±------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±------------+
3 rows in set

mysql> select * from score ;
a b
±----±------±-------+ ±----±------±-------+
| sid | cid | degree | | sid | cid | degree |
±----±------±-------+ ±----±------±-------+
| 101 | 3-105 | 90 | | 101 | 3-105 | 90 |
| 102 | 3-105 | 91 | | 102 | 3-105 | 91 |
| 103 | 3-105 | 92 | | 103 | 3-105 | 92 |
| 103 | 3-245 | 86 | | 103 | 3-245 | 86 |
| 103 | 6-166 | 85 | | 103 | 6-166 | 85 |
| 104 | 3-105 | 89 | | 104 | 3-105 | 89 |
| 105 | 3-105 | 88 | | 105 | 3-105 | 88 |
| 105 | 3-245 | 75 | | 105 | 3-245 | 75 |
| 105 | 6-166 | 79 | | 105 | 6-166 | 79 |
| 109 | 3-105 | 76 | | 109 | 3-105 | 76 |
| 109 | 3-245 | 68 | | 109 | 3-245 | 68 |
| 109 | 6-166 | 81 | | 109 | 6-166 | 81 |
±----±------±-------+ ±----±------±-------+
12 rows in set 12 rows in set

把score复制成2张表（命名为a和命名为b）
如果从a 表中选中3-105，对应的需要在 b表中选中全部3-105求得平均值（也就是说通过b表来求平均值）
7
mysql> select * from score a
where degree<(select avg(degree) from score b where a.cid=b.cid);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±----±------±-------+
5 rows in set

==子查询
==32.查询所有任课教师的tname和depart(任课教师的意思是排了课的老师）
==课程表中安排了课程

把上表的 tid 筛选出来然后在教师表里面查找对应的老师 name和depart.

==条件加分组筛选
==33.查询至少有2名男生的班号

筛选性别为男的同学where 根据class 分组group by 然后添加条件至少有2名男生 having

mysql> select class from student where ssex=‘男’ group by class having count(*)>1;
±------+
| class |
±------+
| 95031 |
| 95033 |
±------+
2 rows in set

mysql> select class,count(ssex) from student where ssex=‘男’ group by class having count(*)>1;
±------±------------+
| class | count(ssex) |
±------±------------+
| 95031 | 4 |
| 95033 | 3 |
±------±------------+
2 rows in set

==notlike模糊查询取反
==34.查询student表中不姓“王”的同学记录。

mysql> select * from student where sname not like ‘王%’;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | 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 |
±----±-------±-----±-----------±------+
7 rows in set

==year函数与 now函数
==35.查询student表中每个同学的姓名和年龄
年龄=当前年份-出生年份

查看当前年份
mysql> select year(now());
±------------+
| year(now()) |
±------------+
| 2021 |
±------------+
1 row in set

查看出生年份
mysql> select year(sbirthday) from student;
±----------------+
| year(sbirthday) |
±----------------+
| 1977 |
| 1975 |
| 1976 |
| 1976 |
| 1975 |
| 1974 |
| 1976 |
| 1975 |
| 1974 |
| 1974 |
±----------------+
10 rows in set

年龄 = 当前年份year(now()) - 出生年份 year(sbirthday)

mysql> select sname,year(now())-year(sbirthday) as ‘年龄’ from student;
±-------±-----+
| sname | 年龄 |
±-------±-----+
| 曾华 | 44 |
| 匡明 | 46 |
| 王丽 | 45 |
| 李军 | 45 |
| 王芳 | 46 |
| 陆君 | 47 |
| 王尼玛 | 45 |
| 张全三 | 46 |
| 赵铁柱 | 47 |
| 张飞 | 47 |
±-------±-----+
10 rows in set

==max与min函数
==36.查询student表中最大和最小的sbirthday日期值。

这里的最大（max)最小(min)不是指年龄而是值年份

mysql> select max(sbirthday) as ‘最大’,min(sbirthday) as ‘最小’ from student;
±-----------±-----------+
| 最大 | 最小 |
±-----------±-----------+
| 1977-09-01 | 1974-06-03 |
±-----------±-----------+
1 row in set

==多字段排序
==37.以班号和年龄从大到小的顺序排序查询student表中全部的记录。
出生越早年龄越大

mysql> select * from student order by class desc,sbirthday;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
±----±-------±-----±-----------±------+
10 rows in set

==子查询
==38.查询 ”男“教师及其所上的课程。

先查询男教师
mysql> select * from teacher where tsex=‘男’;
±----±------±-----±-----------±-------±-----------+
| tid | tname | tsex | tbirthday | prof | depart |
±----±------±-----±-----------±-------±-----------+
| 804 | 李成 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
±----±------±-----±-----------±-------±-----------+
2 rows in set

然后通过 tid在课程表中·找到课程名称tname
mysql> select * from course where tid in (select tid from teacher where tsex=‘男’);
±------±---------±----+
| cid | cname | tid |
±------±---------±----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
±------±---------±----+
2 rows in set

==max查询与子查询
==36.查询最高分同学的 sid、cid和 degree列。

查询最高分
mysql> select max(degree) from score;
±------------+
| max(degree) |
±------------+
| 92 |
±------------+
1 row in set

mysql> select * from score where degree=(select max(degree) from score);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
±----±------±-------+
1 row in set

==子查询
==查询和 “李军” 同性别的所有同学的sname。
先查询李军的性别
mysql> select ssex from student where sname=‘李军’;
±-----+
| ssex |
±-----+
| 男 |
±-----+
1 row in set

mysql> select sname from student where ssex=(select ssex from student where sname=‘李军’);
±-------+
| sname |
±-------+
| 曾华 |
| 匡明 |
| 李军 |
| 陆君 |
| 王尼玛 |
| 张全三 |
| 赵铁柱 |
| 张飞 |
±-------+
8 rows in set

==子查询
==41.询和 “李军” 同性别并同班的同学的sname。
mysql> select sname from student where ssex=(select ssex from student where sname=‘李军’)
and class=(select class from student where sname=‘李军’);
±-------+
| sname |
±-------+
| 曾华 |
| 李军 |
| 王尼玛 |
±-------+

==子查询
==查询所有选修"计算机导论"课程的"男"同学的成绩表。

先找到男同学
mysql> select * from student where ssex=‘男’;
±----±-------±-----±-----------±------+
| sid | sname | ssex | sbirthday | class |
±----±-------±-----±-----------±------+
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 106 | 陆君 | 男 | 1974-06-03 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 108 | 张全三 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
±----±-------±-----±-----------±------+
8 rows in set

找到导论课
mysql> select * from course where cname=‘计算机导论’;
±------±-----------±----+
| cid | cname | tid |
±------±-----------±----+
| 3-105 | 计算机导论 | 825 |
±------±-----------±----+
1 row in set

因为男同学有多个，计算机只有一个，所有男同学条件用 in
mysql> select * from score where cid=(select cid from course where cname=‘计算机导论’)
and sid in (select sid from student where ssex=‘男’);
±----±------±-------+
| sid | cid | degree |
±----±------±-------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
±----±------±-------+
4 rows in set

==按等级查询
==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’);

mysql> select * from grade;
±----±----±------+
| low | upp | grade |
±----±----±------+
| 90 | 100 | A |
| 80 | 89 | B |
| 70 | 79 | C |
| 60 | 69 | D |
| 0 | 59 | E |
±----±----±------+
5 rows in set

==现查询所有学生的sid、cid 和grade 列.

mysql> select sid,cid,grade from score,grade where degree between low and upp;
±----±------±------+
| sid | cid | 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 |
±----±------±------+
12 rows in set