MySQL——多表查询

创建外键：在创建表的时候建立

CREATE TABLE emp_mul(
eid VARCHAR(20) PRIMARY KEY COMMENT "员工编号",
ename VARCHAR(20) COMMENT "员工名字",
age INT COMMENT "年龄", 
dept_id VARCHAR(20) COMMENT "部门编号",
CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept_mul(deptno));

#CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept_mul(deptno))创建外表的关键语句，创建名称为emp_fk，该主键依附于dept_mul的deptno字段。

创建主键：建立表之后建立外键

#假设已建立主表student_mul和从表score，则创建名称为student_fk的外键，score表的sid依附于student_mul的sid;

ALTER TABLE score ADD CONSTRAINT student_fk FOREIGN KEY(sid) REFERENCES student_mul(sid);

多表查询案例:

1.数据准备

##主表
CREATE TABLE dept (
id INT PRIMARY KEY auto_increment COMMENT"ID",
name VARCHAR(50) NOT NULL COMMENT "部门名称") COMMENT "部门表";
INSERT INTO dept (name) VALUES ('研发部'), ('市场部'),('财务部'), ('销售部'), ('总经办'), ('人事部');
##从表
create table emp(
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
) comment '员工表';
#'直属领导ID'受到id列的影响，foregin KEY managerid REFERENCES (id) 
##添加外键
ALTER TABLE emp ADD CONSTRAINT fk_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate,managerid, dept_id) VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

dept表：

emp表：

2.案例

2.1交叉查询

SELECT * FROM emp,dept

一般不常用，只是作为查询的一个过程，计算的是笛卡儿积：产生冗余数据，若表1有16条数据，表2有7条数据，则结果数据存在16×7条数据。

2.2 内连接

内连接：WHERE

#模板
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

内连接：嵌入式内连接：JOIN ON

#模版：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

例：查询每个员工的姓名，及关联的部门名称

#方法一：隐式内连接 ：WHERE
SELECT emp.name as "姓名",dept.name as "部门名称" FROM emp,dept WHERE emp.dept_id=dept.id

#方法二：嵌入式内连接：JOIN ON
SELECT e.id,e.name as "姓名",d.name as "部门名称" FROM emp e JOIN dept d ON e.dept_id=d.id ORDER BY e.id;

2.3外连接

左外连接：LEFT JOIN ON（更倾向于用）

#模版：
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

以左表的数据为基础，结合左表和右表的交集，右表无对应的则返回null。

右外连接:RIGHT JOIN ON

#模版：
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

与左外连接相反

满外连接：full outer join 。MySQL不支持，oracle支持

例子：查询emp表的所有数据, 和对应的部门信息

#右外连接:RIGHT JOIN ON
SELECT emp.*,dept.name as "dept_name" FROM dept RIGHT JOIN emp ON emp.dept_id=dept.id order by id;
##自连接（join on）
#查询员工及其所属领导的名字
SELECT a.name "员工名字",b.name "领导名字" FROM emp a ,emp b WHERE a.id=b.managerid;

2.3自连接

①自己连接自己，将一张表作为多张表进行查询；②自连接的时候一定要取别名

自连接查询

#模版
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

自连接查询的时候也可以是内连接查询也可以是外面接查询

例子：查询员工 及其 所属领导的名字

select a.name , b.name from emp a , emp b where a.managerid = b.id;

联合查询：利用union 将查询结果拼在一起，上下拼接。

#模板
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

注：union与union all的区别：union并未去重，union all是去重之后的数据。

例子：将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来。

select * from emp where salary < 5000
union all
select * from emp where age > 50;

2.4子查询

就是嵌套select

#模板
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); 1

例子：子查询为单行单列，运算符为= ,<, <=,>,>=

查询年龄最大的员工

SELECT * 
FROM emp
WHERE age=(SELECT max(age) FROM emp);

例子：子查询为多行单列。运算符有IN,NOT IN,ANY,SOME,ALL（any与some的结果一致），any，all 要与运算符= ,<, <=,>,>=一起使用

查询研发部和销售部的员工信息

SELECT * from emp 
WHERE dept_id in(SELECT id FROM dept WHERE name="研发部" or name="销售部");

例子：子查询为单行多列，操作符：= ,<>,IN,NOT IN

查询与张无忌薪资及直属领导相同的员工信息

SELECT * FROM emp WHERE (salary,managerid)=(SELECT salary,managerid FROM emp WHERE name="张无忌");

例子：子查询为多行多列，操作符IN

查询 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

SELECT * FROM emp WHERE (job,salary) in (SELECT job,salary FROM emp WHERE name='鹿杖客' or name="宋远桥");

补充关键词：where EXISTS（查询语句），如果exists()返回true，则行使子查询，否则不执行，exists查询效率更高
查询公司里大于60岁的员工

SELECT * FROM emp e WHERE EXISTS(SELECT * FROM emp where e.age>60);

练习：

1.查询所有的部门信息，并统计部门的员工人数

2.查询低于本部门平均薪资的员工信息

SELECT new_table.dept_id,dept.name FROM(
SELECT dept_id,count(*) FROM emp GROUP BY dept_id) as new_table
join dept on new_table.dept_id=dept.id;

select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where
e1.dept_id = e2.dept_id );