多表查询
2.多表连接方式
多表连接首先做笛卡尔积，然后根据on的规则选择符合规范的记录
语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
内连接：只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id ;
select * from employee inner join department on employee.dep_id = department.id where employee.id = 1;
左连接:在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id ;
右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id ;
全外连接（很少用）：在内连接的基础上保留左右两表没有对应关系的记录
#mysql不支持 full join!!!!!!如下会报错
select * from employee full join department on employee.dep_id = department.id ;
#可以用如下方式显示full join 的功能
select * from employee left join department on employee.dep_id = department.id union
select * from employee right join department on employee.dep_id = department.id ;
3.再谈执行顺序
参考我的博客-单表查询-8.语法总结http://www.cnblogs.com/liuchengdage/articles/8923251.html
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table> --连表操作
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
一句话总结select执行过程 先执行from，做笛卡尔积，执行on过滤，添加外部行执行join的类型，where进行过滤，分组，过滤，select
列表，去重，排序，限制条数
小试牛刀
#以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于2
5岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;
#以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
4.子查询
子查询 用一个括号将查询的结果当做另一个查询的条件 。
ps:可以在子查询虚表或者查询的字段后面加上一个别名，用"别名.相关字段名"调用在子查询虚表中的字段， 注意，MySQL不支持嵌套.，比如这种 t1.t2.t3, 这种调用是不支持的，可以将t1.t2 起一个别名 s,然后调用s.t3
注意：
- 子查询是将一个查询语句嵌套在另一个查询语句中。
- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
- 还可以包含比较运算符：= 、 !=、> 、<等
查询实例
ps:建议每个子虚拟表内的每个查询的字段（包括聚合函数）起一个别名，这个表也起一个表名，方便父级调用能识别
1 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名
select name from department where id not in
(select distinct dep_id from employee);
2 带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age > (select avg(age) from employee);
#查询大于部门内平均年龄的员工名、年龄 ,查了一个虚拟表我起了一个表名
select name,age from employee
inner join
(select avg(age) as avg_age,dep_id from employee group by dep_id) as f2
on
employee.dep_id = f2.dep_id
where employee.age > f2.avg_age;
-- 通过聚合函数查询的字段需要起一个别名，接下来执行的语句才能识别这个字段名！，
#查询每个部门最新入职的那位员工的信息(quiredb employee 表)
select * from employee
inner join
(select distinct post,max(hire_date) as new_hire_date
from employee
group by post ) as t2
on employee.post = t2.post and employee.hire_date = t2.new_hire_date;
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Ture
select * from employee
where exists
(select id from department where id=200);
#department表中存在dept_id=205，False
mselect * from employee
where exists
(select id from department where id=204);
5.一大波综合练习题
关系表(不关联外键)
自己建立一个数据库homewoek,切换到该库下面，将下列代码在mysql下执行一遍，库和表就建立好了
/*
Navicat MySQL Data Transfer
Source Server : root
Source Server Version : 50639
Source Host : localhost:3306
Source Database : homework
Target Server Type : MYSQL
Target Server Version : 50639
File Encoding : 65001
Date: 2018-04-21 18:06:59
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `changemsg`
-- ----------------------------
DROP TABLE IF EXISTS `changemsg`;
CREATE TABLE `changemsg` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xxxxx` char(3) DEFAULT NULL,
`grade_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of changemsg
-- ----------------------------
INSERT INTO `changemsg` VALUES ('1', '低', '1');
INSERT INTO `changemsg` VALUES ('2', '低', '2');
INSERT INTO `changemsg` VALUES ('3', '中', '3');
INSERT INTO `changemsg` VALUES ('4', '中', '4');
INSERT INTO `changemsg` VALUES ('5', '高', '5');
INSERT INTO `changemsg` VALUES ('6', '高', '6');
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` char(4) DEFAULT NULL,
`grade_id` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '一年一班', '1');
INSERT INTO `class` VALUES ('2', '二年一班', '2');
INSERT INTO `class` VALUES ('3', '三年二班', '3');
-- ----------------------------
-- Table structure for `class_grade`
-- ----------------------------
DROP TABLE IF EXISTS `class_grade`;
CREATE TABLE `class_grade` (
`gid` int(11) NOT NULL AUTO_INCREMENT,
`gname` char(3) DEFAULT NULL,
PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of class_grade
-- ----------------------------
INSERT INTO `class_grade` VALUES ('1', '一年级');
INSERT INTO `class_grade` VALUES ('2', '二年级');
INSERT INTO `class_grade` VALUES ('3', '三年级');
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` char(2) DEFAULT NULL,
`teacher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '体育', '1');
INSERT INTO `course` VALUES ('3', '物理', '2');
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
`score` float(5,2) unsigned DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '60.00');
INSERT INTO `score` VALUES ('2', '1', '2', '59.00');
INSERT INTO `score` VALUES ('3', '2', '2', '99.00');
INSERT INTO `score` VALUES ('4', '3', '3', '79.00');
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` char(4) DEFAULT NULL,
`gender` enum('男','女') DEFAULT '女',
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '乔丹', '女', '1');
INSERT INTO `student` VALUES ('2', '艾弗森', '女', '1');
INSERT INTO `student` VALUES ('3', '科比', '男', '2');
-- ----------------------------
-- Table structure for `teach2cls`
-- ----------------------------
DROP TABLE IF EXISTS `teach2cls`;
CREATE TABLE `teach2cls` (
`tcid` int(11) NOT NULL AUTO_INCREMENT,
`tid` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`tcid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teach2cls
-- ----------------------------
INSERT INTO `teach2cls` VALUES ('1', '1', '1');
INSERT INTO `teach2cls` VALUES ('2', '1', '2');
INSERT INTO `teach2cls` VALUES ('3', '2', '1');
INSERT INTO `teach2cls` VALUES ('4', '3', '2');
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` char(4) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张三');
INSERT INTO `teacher` VALUES ('2', '李四');
INSERT INTO `teacher` VALUES ('3', '王五');
题目
1、自行创建测试数据；
2、查询学生总人数；
3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
4、查询每个年级的班级数，取出班级数最多的前三个年级；
5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
6、查询每个年级的学生人数；
7、查询每位学生的学号，姓名，选课数，平均成绩；
8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
9、查询姓“李”的老师的个数和所带班级数；
10、查询班级数小于5的年级id和年级名；
11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；
班级id 班级名称 年级 年级级别
1 一年一班 一年级 低
12、查询学过“张三”老师2门课以上的同学的学号、姓名；
13、查询教授课程超过2门的老师的id和姓名；
14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
15、查询没有带过高年级的老师id和姓名；
16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
17、查询带过超过2个班级的老师的id和姓名；
18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
19、查询所带班级数最多的老师id和姓名；
20、查询有课程成绩小于60分的同学的学号、姓名；
21、查询没有学全所有课的同学的学号、姓名；
22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
25、删除学习“张三”老师课的score表记录；
26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
30、课程平均分从高到低显示（现实任课老师）；
31、查询各科成绩前三名的记录(不考虑成绩并列情况)
32、查询每门课程被选修的学生数；
33、查询选修了2门以上课程的全部学生的学号和姓名；
34、查询男生、女生的人数，按倒序排列；
35、查询姓“张”的学生名单；
36、查询同名同姓学生名单，并统计同名人数；
37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
40、求选修了课程的学生人数
41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
42、查询各个课程及相应的选修人数；
43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
44、查询每门课程成绩最好的前两名学生id和姓名；
45、检索至少选修两门课程的学生学号；
46、查询没有学生选修的课程的课程号和课程名；
47、查询没带过任何班级的老师id和姓名；
48、查询有两门以上课程超过80分的学生id及其平均成绩；
49、检索“3”课程分数小于60，按分数降序排列的同学学号；
50、删除编号为“2”的同学的“1”课程的成绩；
51、查询同时选修了物理课和生物课的学生id和姓名；
参考答案http://www.cnblogs.com/liuchengdage/articles/8930586.html
