mycql 多表联合查询

 egon笔记:

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个字段,会尽可能的满足需求的情况下去原子化一个表格,尽可能去避免多表联查的情况,因为多表联查会很复杂,对使用者而言是一种挑战,工作效率会受到影响.

#建表
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

二 多表连接查询

#重点：外链接语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

1 交叉连接：不适用任何匹配条件。生成笛卡尔积

笛卡尔乘积的形成,是同时select两个表格,然后按照前一个表格的每一行去关联后一个表格的每一行,这样就得到了笛卡尔乘积

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 内连接：只连接匹配的行

两个表格里你有的部分,我也有,然后这一部分就会显示出来,就类似于集合里面的交集,两者共同拥有的部分拿出来合成一个表格.

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#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来填充,

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
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来填充,

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
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 全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：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会去掉相同的纪录

三 符合条件连接查询

#示例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;

四 子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

带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 in 
        (select dep_id from employee group by dep_id having count(id) <=1);

带比较运算符的子查询:

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
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)));