5.mysql多表查询

MYSQL多表查询

create table student(
 id int auto_increment primary key COMMENT 'ID',
 name varchar(10) comment '姓名',
 no varchar(10) comment '学号'
)comment '学生表';

insert into student values(null,'lisa','20001'),(null,'tracy','20002'),(null,'janms','20003'),(null,'qiaodan','20004');

create table course(
 id int auto_increment primary key COMMENT 'ID',
 name varchar(10) comment '姓名'
)comment '课程表';

insert into course values(null,'Java'),(null,'PHP'),(null,'Mysql'),(null,'Hadoop');

create table student_course(
 id int auto_increment primary key COMMENT 'ID',
 studentid int not null  comment '学生id',
 courseid int not null  comment '课堂id',
 CONSTRAINT fk_courseid foreign key (courseid) REFERENCES course (id),
 CONSTRAINT fk_studentid foreign key (studentid) REFERENCES student(id)
)comment '学生课程中间表';

insert into student_course  values(null,1,2),(null,1,1),(null,2,1),(null,3,1);

1.多表关系

一对多：在多的一方设置外键，关联一的一方的主键
多对多：建立中间表，中间表包含两个外键，关联两张表的主键
一对多： 通常用于表结构拆分表，对应的拆分表字段任意一方设置unique外键，关联另一方的主键

笛卡尔积

select * from emp,dept
–多表查询，查询出结果的所有集合情况-笛卡尔积，消除无效的笛卡尔积，通过where条件关联字段筛选；
多表连查消除笛卡尔积

SELECT s.name,s.no,c.name from student s,student_course sc,course c where s.id=sc.studentid and c.id=sc.courseid;

2. 多表查询概述

• 连接查询

  1. 内连接：相当于查询A、B交集部分的数据

  2. 外连接
     左外连接：查询左表所有数据，以及两张表交集部分数据
     右外连接：查询右表所有数据，以及两张表交集部分数据

• 子查询

2.1 内连接

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件;

显示内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件;

内连接查询的是两张表的交集部分

#隐式连接
select * from emp,dept where  emp.dept_id=dept.id;
select e.name,d.name from emp e ,dept d where  e.dept_id=d.id;

#显示连接
SELECT e.name,d.name from emp e inner join dept  d on  e.dept_id=d.id;

2.2 外连接

左外连接

SELECT　字段列表　FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

右外连接

SELECT　字段列表　FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

#左外连接
SELECT * from emp e left  outer join  dept d on e.dept_id=d.id;

#右外连接
SELECT d.*,e.* from emp e right outer join dept d on e.dept_id=d.id;

2.3自连接

SELECT　字段列表　FROM 表A 别名A INNER JOIN 表A 别名B ON 条件;

SELECT e2.name,e1.name from emp e1 inner join emp e2 on e1.id=e2.managerid 

查询员工姓名，以及领导姓名；包括领导为空的员工

SELECT e2.name as '员工',e1.name from emp e2 left join emp e1 on e1.id=e2.managerid 

联合查询union ，union all

union将多个查询结果联合起来

SELECT＊FROM 表1
UNION ALL
SELECT＊FROM 表1；

#union all直接合并结果
SELECT * from emp where salary <10000
union all
SELECT * from emp where age>32

结果

union查询结果进行去重

#union查询结果进行去重
SELECT * from emp where salary <10000
union 
SELECT * from emp where age>32

对于联合查询的多张表的字段必须保持一致

2.4子查询

子查询：

• 标量子查询(查询结果为单个值)
• 列子查询（查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

概念：SQL语句中嵌套的SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHRER column1=(SELECT column1* FROM t2);
子查询外部的语句可以是INSERT /UPDATE/DELETE/SELECT 的任何一个

2.4.1标量子查询

子查询返回结果是单个值（数字、字符串、日期等），最简单的形式
常用的操作：=、 <> 、> 、>= 、< 、 <=

2.4.2列子查询

列子查询:子查询的返回结果是一列（可以使多行）
常用的操作符：IN 、NOT IN 、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询列表内，有任意一个满足
SOME	与some等同
ALL	子查询返回的列表的所有值必须满足

列子查询
查询比财务部所有人工资都高的员工信息

1.查询财务人员工资
SELECT id from dept where name='财务部';

SELECT salary from emp where dept_id= (SELECT id from dept where name='财务部');
2.比财务部人所有人工资都高

SELECT * from emp where salary >all (SELECT salary from emp where dept_id= (SELECT id from dept where name='财务部'));

2.4.3行子查询

子查询的返回结果是一行（可以使多列）
常用的操作：=、 <>、IN、NOT IN
查询与张无忌薪资及直属领导相同的员工信息

1.先查询张无忌的薪资与直属领导
SELECT salary,managerid from emp  where name='张无忌';
2.查询与张无忌薪资及领导一样的员工信息
SELECT * from emp where (salary,managerid)=(12500,1);
3.合并sql
SELECT * from emp where (salary,managerid)= (SELECT salary,managerid from emp  where name='张无忌');

2.4.4表子查询

子查询返回的结果是多行多列
常用的操作符:IN
与张三与张财务薪资职位相同的员工信息

SELECT job,salary from emp  where name='张三' or name='张财务';

SELECT * from emp where (job,salary) in (SELECT job,salary from emp  where name='张三' or name='张财务')

查询在2004-01-01之后入职的员工和对应的部门

1.先查询2004-01-01之后的员工信息
SELECT * from emp where entrydate>'2004-01-01'

2.查询员工对应的部门 左连接包含2004-01-01之后入职没有部门的员工
SELECT e.* ,d.* from (SELECT * from emp where entrydate>'2004-01-01') e left join dept d on e.dept_id=d.id;