mycql 多表联合查询
egon笔记:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 单表查询 select distinct 字段1,字段2,字段3 from 表 where 约束条件 group by 分组字段 having 过滤条件 order by 排序字段 limit n; def from(file): f=open(file) return f def where(f,条件): lines=[] for line in f: if 条件: lines.append(line) def group(): dic={ 'male':迭代器, 'female':迭代器 } return dic def having(): pass def select(): res1=distinct(having_res) res2=order(res1) limit(res2) def distinct(): pass def order(): pass def limit(): pass f=from('emp') lines=where(f,条件) group_res=group(lines,字段) having_res=having(group_res,条件) select(having_res) 2 多表查询 select distinct 字段1,字段2,字段3 from 左表 left join 右表 on 链表条件 where 约束条件 group by 分组字段 having 过滤条件 order by 排序字段 limit n; #示例1:找出年龄大于25岁的员工以及员工部门名, select emp.name emp_name,dep.name dep_name from emp inner join dep on emp.dep_id = dep.id where age > 25; #查询平均年龄在25岁以上的部门名 select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep_id having avg(age) > 25 ; select name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25); #查看技术部员工姓名 select emp.name from dep inner join emp on emp.dep_id = dep.id where dep.name = '技术' ; #查看>1人的部门名 select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep_id having count(*) > 1; select t1.id,t1.name,t1.age,t1.hire_date,t1.post,t2.* from emp_old as t1 inner join (select post,max(hire_date) max_hire_date from emp_old group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_hire_date ; id province city commit_time x y z 1 山东省 烟台市 2017-01-01 11:11:11 1 2 3 2 山东省 烟台市 2017-01-01 12:11:11 1 2 3 3 山东省 烟台市 2017-01-03 12:11:11 1 2 3 create view emp2dep as select emp.*,dep.name as dname from emp inner join dep on emp.dep_id = dep.id;
一般情况下,我们设计一个库一个表的时候,会尽可能科学的去考虑以后的使用过程中会出现的情况,因为一个表格或者一个库都是供很多人使用的,这种需要大家一起共同使用的东西都会尽可能设计的简单一些,方便交流,所以,一般一个表格会有10-30个字段,会尽可能的满足需求的情况下去原子化一个表格,尽可能去避免多表联查的情况,因为多表联查会很复杂,对使用者而言是一种挑战,工作效率会受到影响.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#建表 create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into employee(name,sex,age,dep_id) values ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('liwenzhou','male',18,200), ('jingliyang','female',18,204) ; #查看表结构和数据 mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 表department与employee
二 多表连接查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#重点:外链接语法 SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
1 交叉连接:不适用任何匹配条件。生成笛卡尔积
笛卡尔乘积的形成,是同时select两个表格,然后按照前一个表格的每一行去关联后一个表格的每一行,这样就得到了笛卡尔乘积
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+
2 内连接:只连接匹配的行
两个表格里你有的部分,我也有,然后这一部分就会显示出来,就类似于集合里面的交集,两者共同拥有的部分拿出来合成一个表格.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果 #department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ #上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
3 外链接之左连接:优先显示左表全部记录
在上面的inner join 的基础上,把左边的表格的全部内容显示出来,也就是说我们先显示出来交集的部分,然后把非交集的部分里面左边表格的内容显示出来,剩余的部分就是右边表格里面无法与左表匹配的内容,就用null来填充,
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#以左表为准,即找出所有员工信息,当然包括没有部门的员工 #本质就是:在内连接的基础上增加左边有右边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技术 | | 5 | liwenzhou | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | jingliyang | NULL | +----+------------+--------------+
4 外链接之右连接:优先显示右表全部记录
同理可得,在inner join的基础上,把右边的表格里面的内容显示出来,也就是说我们先显示出来交集的部分,然后把非交集的部分里面右边的表格的内容显示出来,剩余的部分就是左表里面无法与右表匹配的内容,就用null来填充,
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#以右表为准,即找出所有部门信息,包括没有员工的部门 #本质就是:在内连接的基础上增加右边有左边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | liwenzhou | 技术 | | NULL | NULL | 运营 | +------+-----------+--------------+
5 全外连接:显示左右两个表全部记录
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 #注意:mysql不支持全外连接 full JOIN #强调:mysql可以使用此种方式间接实现全外连接 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 ; #查看结果 +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ #注意 union与union all的区别:union会去掉相同的纪录
三 符合条件连接查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25; #示例2:以内连接的方式查询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;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#1:子查询是将一个查询语句嵌套在另一个查询语句中。 #2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。 #3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4:还可以包含比较运算符:= 、 !=、> 、<等
带in关键字的子查询:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#查询平均年龄在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 in (select dep_id from employee group by dep_id having count(id) <=1);
带比较运算符的子查询:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#比较运算符:=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ rows in set (0.00 sec) #查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
以上是egon博客里面的一道题,第24题
http://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5 这里的博客地址,第24题
其余的题目解题思路以及方法答案如下
egon 博客练习题: http://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5 use db1 or db2 1、查询所有的课程的名称以及对应的任课老师姓名 select cname,tname from course left join teacher on course.teacher_id=teacher.tid ; select cname,tname from teacher right join course on course.teacher_id=teacher.tid; 2、查询学生表中男女生各有多少人 select sid,count(1) from student gruop by gender; 3、查询物理成绩等于100的学生的姓名 # select *from student inner join score on student.sid=score.student_id; select sname from student inner join score on student.sid=score.student_id group by course_id having course_id=2; 4、查询平均成绩大于八十分的同学的姓名和平均成绩 select avg(num),sname from student inner join score on student.sid=score.student_id group by student_id having avg(num)>80; 5、查询所有学生的学号,姓名,选课数,总成绩 select student.sid,sname,sum(num),count(1) from student inner join score on student.sid=score.student_id group by sname ; 6、 查询姓李老师的个数 select count(1) from teacher where tname like '李%'; 7、 查询没有报李平老师课的学生姓名 select sname from student where sid not in ( select student_id from course inner join score on course.cid=score.course_id where cid in (2,4) group by student_id ); 8、 查询物理课程比生物课程高的学生的学号 select A.student_id from (select student_id,num from score where course_id ='1') A ,(select student_id,num from score where course_id ='2') B where A.student_id = B.student_id and B.num > A.num; ========================================================================================================= select student_id from (select student_id,num n1 from score where course_id=1)as a inner join (select student_id,num n2 from score where course_id=2) as b on a.student_id=b.student_id where n2>n1; ---------这里报错,不知道怎么回事 select student_id,num n1 from score inner join student on student.sid=score.student_id where course_id=1 select student_id,num n2 from score inner join student on student.sid=score.student_id where course_id=2 # 要先查出来物理的成绩,然后再查出来生物的成绩,单独查出来还是可以操作的,然后就是 拼接的问题,拼接的时候需要使用到括号,然后括号里面需要把上面查出的物理成绩放进去,然后as 一个名字, 同理再来一个括号把生物的成绩拿出来放到一个括号里面,as 一个别名,然后再基于括号 里面的表格的基础上进行两个表格的关联,最后再在关联的表格上进行查询,确实需要三个 表格,两两关联形成了分别的两个表格,第三个就是把这前面的两两结果关联到一起.这个情况在王老师讲的 那些题里面有类似的题目,由于都没有整理,所以,看起来很陌生,接触的时候还是有很大障碍, 多做一做题目会缓解很多,所以egon的那24道题必须要做完,实在不行周末做也要搞定它. 这件事必须要做完,不可以再拖下去了,本来这个mysql就几乎没有做什么有难度的题目,老师讲的那些 难一点的题都没有做,也没有整理,这个题再不做完就真的是废掉了. =========================================================================================================== 9、 查询没有同时选修物理课程和体育课程的学生姓名 select sname from student where sid not in ( select student_id from course inner join score on course.cid=score.course_id where cid in (2,3) group by student_id ); 10、查询挂科超过两门(包括两门)的学生姓名和班级 select sname,caption from student inner join class on student.class_id=class.cid where sid= (select student_id from score where num<60 group by student_id having count(1)>1); select student_id from score where num<60 group by student_id having count(1)>1; 11 、查询选修了所有课程的学生姓名 select sname from student where sid in (select student_id from score group by student_id having count(1)=4); select student_id from score group by student_id having count(1)=4; 12、查询李平老师教的课程的所有成绩记录 select num from score where course_id in (select cid from course inner join teacher on teacher_id=tid where tname='李平老师'); 13、查询全部学生都选修了的课程号和课程名 select cid,cname from score inner join course on cid=course_id group by course_id having count(1)=16; 14、查询每门课程被选修的次数 select count(1) from score group by course_id; 15、查询只选修了一门课程的学生姓名和学号 select sname,student_id from student left join score on student.sid=student_id group by course_id having count(1)=1; 16、查询所有学生考出的成绩并按从高到低排序(成绩去重) select num from score group by num desc; 17、查询平均成绩大于85的学生姓名和平均成绩 select student_id,avg(num) from score left join student on student.sid=student_id group by student_id; 18、查询生物成绩不及格的学生姓名和对应生物分数 select num,sname from student,( select num,student_id from course left join score on cid=course_id where cname="生物" and num<60) un where sid=student_id ; 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程, 不是所有课程)平均成绩最高的学生姓名 select sname from student where sid=( select student_id from score ,( select cid from teacher inner join course on tid=teacher_id where tname="李平老师") te_co where cid=course_id group by student_id order by avg(num) desc limit 1); 20、查询每门课程成绩最好的前两名学生姓名{这一题是最难的,先留着吧,后面再做,如果有时间的话,有时间,必须有时间,} SELECT score.student_id, t3.course_id, t3.first_num, t3.second_num FROM score INNER JOIN ( SELECT t1.course_id, t1.first_num, t2.second_num FROM ( SELECT course_id, max(num) first_num FROM score GROUP BY course_id ) AS t1 INNER JOIN ( SELECT score.course_id, max(num) second_num FROM score INNER JOIN ( SELECT course_id, max(num) first_num FROM score GROUP BY course_id ) AS t ON score.course_id = t.course_id WHERE score.num < t.first_num GROUP BY course_id ) AS t2 ON t1.course_id = t2.course_id ) AS t3 ON score.course_id = t3.course_id WHERE score.num >= t3.second_num AND score.num <= t3.first_num ORDER BY course_id; 这一道,第20题还是没有求出最终的结果,我们的学生姓名还没有得到,需要更新,,,,,,,,
现在就来更新它:
SELECT
student_id,
num,
course_id
FROM score r1
WHERE (SELECT count(1)
FROM (SELECT DISTINCT
num,
course_id
FROM score) r2
WHERE r2.course_id = r1.course_id AND r2.num > r1.num) < 2
ORDER BY course_id, num DESC;
我们先根据课程分组得到每门课程的成绩,然后根据分数去重,以免同时好几个人都是同一个分数,排序用倒序,得到前两个成绩(这样就是每门课程成绩最好的前两个成绩的分数) 再拿着分数去找对应的学生的id,然后去学生表格里面找到学生的姓名 select num from score where course_id=1 group by num order by num desc limit 2; 这里我们得到了课程1的最高的两个分数结果, select num from score where course_id=2 group by num order by num desc limit 2; 课程2的最高的两个分数 select num from score where course_id=3 group by num order by num desc limit 2; 课程3最高的两个分数 select num from score where course_id=4 group by num order by num desc limit 2; 课程4最高的两个分数 select student_id from score where course_id=1 and num=( select num from score where course_id=1 group by num order by num desc limit 1 ); 这里我们不可以使用limit 因为得到的是两个分数的结果只能用in,然而我们的limit不能用in作为子查询方法 21、查询不同课程但成绩相同的学号,课程号,成绩 select DISTINCT s1.student_id s1si,s2.student_id s2si,s1.course_id s1ci,s2.course_id s2ci, s1.num s1n,s2.num s2n from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称; select distinct sname,cname from ( select *from score where course_id not in ( select cid from teacher inner join course on tid=teacher_id where tname="李平老师"))as uni inner join student on uni.student_id=student.sid,course where cid=course_id; ============================================================================== 这里实现了学生姓名,但是还没有查出课程名,把课程表直接加到后面即可, 使用了联表以及两个表格一起查询,上面已经得到了最终的结果 select distinct sname from ( select *from score where course_id not in ( select cid from teacher inner join course on tid=teacher_id where tname="李平老师"))as uni inner join student on uni.student_id=student.sid; ============================================================================== 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名; select course_id from score where student_id=1; select distinct student_id,sname from score inner join student on student_id=student.sid where course_id in (select course_id from score where student_id=1) ; 24、任课最多的老师中学生单科成绩最高的学生姓名 我们应该先找到任课最多的老师是谁, 然后从他教过的课里面反过来找他的课程都有哪些, 然后在score表格里面找到那些课程所对应的学生的分数, 最后在从中找出单科成绩最高的学生id, 拿着这个id去学生表格里面找对应的学生名字 select sname from student where sid in ( select distinct student_id from score where course_id in (select cid from course where teacher_id= (select teacher_id from course group by teacher_id order by count(1) desc limit 1) ) and num in(select max(num) from score where course_id in ( select cid from course where teacher_id= (select teacher_id from course group by teacher_id order by count(1) desc limit 1) )group by course_id having max(num))); 解题思路: ①这里是找到了任课最多的老师id--->2 select teacher_id from course group by teacher_id order by count(1)desc limit 1; 而这样写的话,即便我不知道表的内容我也一样可以得到想要的结果,这样更加严谨,写活了 select teacher_id from course group by teacher_id having count(1)>1;这样写是因为我知道结果,只有一个老师教了两门课程,所以这样写死了 ②这里是根据上面得到结果(找到了任课最多的老师)拿过来用得到的老师的课程 select cid from course inner join teacher on tid=teacher_id where tname="李平老师"; ③根据上面的课程得到了这些课程的最高分数 select max(num) from score where course_id in (2,4) group by course_id having max(num); select student_id,sname,num from score left join student s on score.student_id=s.sid where course_id in (2,4)order by num desc limit 5; 这里的limit 5 是因为提前查过了score表格里面学了任课最多的老师,他的的课程分数最高的学生的名字和id,知道有的同学是 ④根据最高分数倒推得到这些分数的同学的id,要去重, 有可能有的同学这些课程都是最高分 select distinct(student_id) from score where course_id in (2,4) and num in (select max(num) from score where course_id=2); ⑤根据同学的id去学生表格里找到学生名字{这里把上面的id合并到这里来了} select sname from student where sid in(select distinct student_id from score where course_id in (2,4) and num in (select max(num) from score where course_id=2)); [1得到课程,任课最多的老师的课程] select cid from course where teacher_id=( select teacher_id from course group by teacher_id order by count(1)desc limit 3 ); [2得到分数,任课最多的老师的课程对应的学生的最高的分数] select max(num)from score where course_id in ( select cid from course where teacher_id=( select teacher_id from course group by teacher_id order by count(1)desc limit 1 ) ) group by course_id having max(num); [3根据最高分数倒推学生的id,直接在得到的id结果外面包一层select student然后就可以拿到学生名字了] select sname from student where sid in ( select distinct student_id from score where course_id in (select cid from course where teacher_id= (select teacher_id from course group by teacher_id order by count(1) desc limit 1) ) and num in(select max(num) from score where course_id in ( select cid from course where teacher_id= (select teacher_id from course group by teacher_id order by count(1) desc limit 1) )group by course_id having max(num)));