mysql查询练习3篇--多表查询,in,where,union,not in,any,all,as,条件查询,notlike,year,now,max,min,多字段排序，按等级查询,连接查询

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

（1）数据不够五人再插几条
INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','89');
（2）3-105课程多于5人
mysql> select * from score;
（3）选课程号
mysql> select cno from score group by cno;
（4）课程号大于5个的课程号
mysql> select cno from score group by cno having count(*)>5;
（5）课程号对应的工号
mysql> select tno from course where cno=(select cno from score group by cno having count(*)>5);
（6）工号和课程号对应的工号相同的教师名字
mysql> select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));
+--------+
| tname  |
+--------+
| 王萍   |
+--------+

2.in表示或者关系
23.查询95033和95031班全体学生的记录

（1）加一条不是这两个班的信息
mysql> insert into student values('110','张飞','男','1972-06-30','95038');
（2）查看学生信息表
mysql> select * from student;  
（3）选出这两个班的学生信息                          
mysql> select * from student where class in(95031,95033)

3.where条件查询
24.查询存在有85分以上的cno

（1）查看分数表
mysql> select * from score;
（2）分数大于85的cno
mysql> select cno from score where degree>'85';
+-------+
| cno   |
+-------+
| 3-105 |
| 3-105 |
| 3-105 |
| 3-245 |
| 3-105 |
| 3-105 |
+-------+

4.子查询
25.查询计算机系所有教师所教课程的成绩表

（1）计算机系的信息
select * from teacher where depart='计算机系';
（2）计算机系对应的工号
select tno from teacher where depart ='计算机系';
（3）工号对应的课程号
select cno from course where tno in (select tno from teacher where depart ='计算机系');
（4）课程号对应的成绩
select * from score where cno in(select cno from course where tno in (select tno from teacher where depart ='计算机系'));

32.查询所有任课教师的tname和depart

mysql> select depart,tname from teacher where tno in(select tno from course);

38.查询男教师及其所上的课程

（1）男教师
mysql> select * from teacher where tsex='男';
（2）课程
mysql> select * from course where tno in(select tno from teacher where tsex='男');

40.查询和李军同性别的学生的sname

（1）李军的性别
mysql> select ssex from student where sname='李军';
（2）和他性别一样的人名
mysql> select sname from student where ssex=(select ssex from student where sname='李军');

41.查询和李军同性别同班的学生的sname

mysql> select sname from student where ssex=(select ssex from student where sname='李军') 
and class=(select class from student where sname='李军');

42.查询所有选修计算机导论课程的男同学的成绩表

(1)性别男的学生信息
mysql> select * from student where ssex='男';
(2)计算机导论的信息
mysql> select * from course where cname='计算机导论';
(3)符合两项
mysql> select * from score where cno=(select cno from course where cname='计算机导论') and sno in(select sno from student where ssex='男');

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

（1）查看教师信息
mysql> select * from teacher;
（2）查看职称在电子工程系但不在计算机系的老师信息
mysql> select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系');
（3）查看职称在计算机系但不在电子工程系的老师信息
mysql> select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');
（4）使用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='电子工程系');
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
+-----+--------+------+---------------------+-----------+-----------------+

6.any表示至少一个
27.查询编号为3-105课程且成绩至少高于选修编号为3-245的同学的cno、sno和degree，并按degree从高到低排序

（1）查看3-105课程
mysql> select * from score where cno='3-105';
（2）查看3-245课程
mysql> select * from score where cno='3-245';
（3）查看3-245成绩
mysql> select degree from score where cno='3-245';
（4）查看3-105课程大于任何一个3-245课程的信息（any：至少大于一个）
mysql> select * from score where cno='3-105'and degree> any(select degree from score where cno='3-245');
（5）按degree降序排序
mysql> select * from score where cno='3-105'and degree> any(select degree from score where cno='3-245') order by degree desc;

7.all表示所有
28.查询编号为3-105课程且成绩高于所有的选修编号为3-245的同学的cno、sno和degree

mysql> select * from score where cno='3-105'and degree> all(select degree from score where cno='3-245');

8.as取别名，union求并集
29.查询所有教师和同学的name、sex和birthday

（1）取数据
mysql> select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher;
（2）as起别名（都是sname、s......）
mysql> select sname as name,ssex as sex,sbirthday as birthday from student union select tname,tsex,tbirthday from teacher;

30.查询所有的女教师和女同学的 name、sex、birthday

mysql> select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女' union select tname,tsex,tbirthday from teacher where tsex='女';

9.复制表数据做条件查询

31.查询成绩比该课程平均成绩低的同学的成绩表

（1）课程平均成绩
mysql> select cno,avg(degree) from score group by cno;
（2）复制一份score表，从a表中遍历成绩比对b表中查到的平均成绩
select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 | 
| 109 | 6-166 |     81 |
+-----+-------+--------+
---一份平均成绩做参照
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

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

mysql> select class from student where ssex='男' group by class having count(*)>1;

11.notlike模糊查询取反
34.查询student表中不姓王的同学

mysql> select * from student where sname not like '王%';

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

（1）当前年份
mysql> select year(now());
（2）出生年份
mysql> select year(sbirthday) from student;
（3）每个同学的姓名和年龄
mysql> select sname,year(now())-year(sbirthday) as '年龄' from student;

13.max和min函数
36.查询student表中最大最小birthday

（1）学生生日
mysql> select sbirthday from student;
（2）生日排序
mysql> select sbirthday from student order by sbirthday;
（3）利用max、min选出最大最小
mysql> select max(sbirthday) as '最大',min(sbirthday) as '最小' from student;
+---------------------+---------------------+
| 最大                | 最小                |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1972-06-30 00:00:00 |
+---------------------+---------------------+

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

（1）以班号和年龄排序
mysql> select * from student order by class,sbirthday;
（2）从大到小的顺序
mysql> select * from student order by class desc,sbirthday;

15.max函数与子查询
39.查询最高分同学的sno、cno和degree列

（1）最高分数
select max(degree) from score;
（2）最高分数对应的信息
mysql> select * from score where degree=(select max(degree) from score);

16.按等级查询
43.假设使用了以下命令建立了一个grade表

CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    grade CHAR(1)
)ENGINE=innoDB
DEFAULT CHARACTER SET=utf8
;
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');

查询所有同学的sno、cno、grade列

mysql> select sno,cno,grade from score,grade where degree between low and upp;

17.连接查询
好处：不创建外键，通过他们之间某个字段的值相等求得交集并集…
SQL四种连接查询
内连接
inner join或者join
外连接
（1）左连接left join或者left outer join
（2）右连接right join或者right outer join
（3）完全外连接full join或者full outer join

创建表
create database testJoin;
create table person( 
	id int, 
	name varchar(20), 
	cardId int
	)ENGINE=innoDB
	DEFAULT CHARACTER SET=utf8
;
create table card( 
	id int,
	name varchar(20)
	)ENGINE=innoDB
	DEFAULT CHARACTER SET=utf8
;

添加数据
insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工商卡');
insert into card values(5,'邮政卡');
---并没有创建外键
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);

1）inner join查询（内连接）（类似交集）

mysql> select * from person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+
mysql> select * from card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+
mysql> select * from person inner join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

内联查询，其实就是两张表中的数据，通过某个字段相等查询出相关记录数据。
2）left join（左外连接）
左外连接，会把左边表里面所有的数据取出来，而右边表中的数据，如果有相等的，就显示出来，没有就补NULL

mysql> select  * from person left join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

3）right join（右外连接）
右外连接，会把右边表里面所有的数据取出来，而左边表中的数据，如果有相等的，就显示出来，没有就补NULL

mysql> select  * from person right join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

4）full join（全外连接）（类似并集）
mysql不支持full join

mysql> select  * from person full join card on person.cardId=card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
mysql> select  * from person right outer join card on person.cardId=card.id;

全连接就是左连接加右连接

mysql> select  * from person left join card on person.cardId=card.id union select  * from person right join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+