MYSQL中的多表操作

-- 1.创建表时创建外键约束
create table if not exists emp(
	eid varchar(20) primary key,
	ename varchar(20),
	age int,
	dept_id varchar(20),
	constraint emp_fk foreign key (dept_id) references dept (deptno) -- 外键约束
);

-- 2.创建表后创建外键约束
create table if not exists dept2(
	deptno varchar(20) primary key,-- 部门号
	name varchar(20) -- 部门名字
);
create table if not exists emp2(
	eid varchar(20) primary key,
	ename varchar(20),
	age int,
	dept_id varchar(20)
);
-- 创建外键约束
alter table emp2 add constraint emp2_fk foreign key(dept_id) references dept2(deptno);

-- 3.删除外键约束
alter table emp2 drop foreign key dept_id_fk;

-- 4.交叉连接查询（笛卡儿积）
SELECT * from dept3,emp3;

-- 5.内连接查询
-- 5.1隐式内连接
SELECT * from dept3,emp3 where dept3.deptno=emp3.dept_id;
SELECT * from dept3 a,emp3 b where dept3.deptno=emp3.dept_id;
SELECT * from dept3 a,emp3 b where a.deptno=b.dept_id and name='研发部';
SELECT * from dept3 a,emp3 b where a.deptno=b.dept_id and (name='研发部' or name ='销售部');
SELECT * from dept3 a,emp3 b where a.deptno=b.dept_id and name in('研发部','销售部');-- 简化
-- 5.2显示内连接
SELECT * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id;
SELECT * from dept3 a join emp3 b on a.deptno=b.dept_id;
SELECT * from dept3 a join emp3 b on a.deptno=b.dept_id and name='研发部';
SELECT * from dept3 a join emp3 b on a.deptno=b.dept_id and (name='研发部' or name ='销售部');
SELECT * from dept3 a join emp3 b on a.deptno=b.dept_id and name in('研发部','销售部');-- 简化
SELECT a.name,a.deptno,count(1) from dept3 a join emp3 b on a.deptno=b.dept_id GROUP BY a.deptno,name;

-- 6.左外连接查询
SELECT * from dept3 a left outer join emp3 b on a.deptno=b.dept_id;
SELECT * from dept3 a left join emp3 b on a.deptno=b.dept_id;
-- SELECT * from A 
-- 	left join B on 条件1
-- 	left join C on 条件2
--  left join D on 条件3;

-- 7.右外连接查询
SELECT * from dept3 a right outer join emp3 b on a.deptno=b.dept_id;
-- SELECT * from A 
-- 	right join B on 条件1
-- 	right join C on 条件2
--  right join D on 条件3;

-- 8.满外查询 
-- 使用union关键字实现左外连接和右外连接的并集
-- SELECT * from dept3 a full join emp3 b on a.deptno =b.dept_id;-- 不能执行
-- 8.1.union是将两个查询结果上下拼接，并去重
SELECT * from dept3 a left join emp3 b on a.deptno =b.dept_id 
union
SELECT * from dept3 a right join emp3 b on a.deptno =b.dept_id;

-- 8.2.union ALL将查询结果上下拼接，不去重
SELECT * from dept3 a left join emp3 b on a.deptno =b.dept_id 
union all 
SELECT * from dept3 a right join emp3 b on a.deptno =b.dept_id;

-- 9.子查询
-- 9.1.查询年龄最大的员工信息，显示信息包括员工号，员工名字，员工年龄
-- 方式一：查询最大年龄：
SELECT max(age) from emp3;
-- 方式二：让每个员工的年龄的最大年龄进行比较，相满足条件
SELECT * from emp3 where age=(SELECT max(age) from emp3);--  单行单列，可以作为一个值来用

-- 9.2.查询研发部和销售部的员工信息，包括员工号，员工名字
-- 方式一：关联查询
SELECT * from dept3 a join emp3 b on a.deptno=b.dept_id and (name ='研发部' or name='销售部');
-- 方式二：子查询
-- 先查询研发部和销售部的部门号：deptno
SELECT deptno from dept3 where name='研发部' or name ='销售部';
-- 查询那个员工的部门号时1001或1002
SELECT * FROM emp3 where dept_id in (SELECT deptno from dept3 where name='研发部' or name ='销售部');-- 多行单列,多个值

-- 9.3.查询研发部20岁以下的员工信息,包括员工号/员工名字/部门名字
-- 方式1-关联查询
SELECT * from dept3 a join emp3 b on a.deptno =b.dept_id and (name='研发部' and age<20);
-- 方式2-子查询
-- 在部门表中查询研发部信息
SELECT * from dept3 where name='研发部';-- 一行多利
-- 在员工表中查询年龄小于20岁的年龄信息
SELECT * from emp3 where age<30;
-- 将以上两个查询结果进行关联查询
SELECT * from (SELECT * from dept3 where name='研发部') t1 join (SELECT * from emp3 where age<30) t2 on t1.deptno=t2.dept_id ;-- 多行多列

-- 10.子查询关键字ALL
-- 10.1.查询年龄大于'1003'部门所有年龄的员工信息
SELECT * from emp3 where age > all(SELECT age from emp3 where dept_id ='1003');
-- 10.2.查询不属于任何一个部门的员工信息
SELECT * from emp3 where dept_id !=all(SELECT deptno from dept3);

-- 11.子查询关键字ANY/SOME（大于一个就行）
-- 查询年龄大于'1003'部门任意一个员工年龄的的员工信息`
SELECT * from emp3 where age>any(SELECT age from emp3 where dept_id='1003') and dept_id!='1003';

-- 12.子查询关键字IN
-- 查询研发部和销售部员工信息,包括员工号,员工名字
SELECT eid,ename from emp3 where dept_id in(SELECT deptno from dept3 where name='研发部' or name ='销售部');

-- 13.子查询关键字-EXISTS
SELECT * from emp3 where exists(SELECT * from emp3);-- 全表输出
-- 查询公司是否有大于60岁的员工,有则输出
SELECT * from emp3 a where exists(SELECT * from emp3 where a.age>60);
SELECT * from emp3 a where eid in(SELECT eid from emp3 where a.age>60);
-- 查询有所属部门的员工信息
SELECT * from emp3 a where exists(SELECT * from dept3 b where a.dept_id=b.deptno);
SELECT * from emp3 a where dept_id in(SELECT deptno from dept3 b where a.dept_id=b.deptno);

-- 14.自关联查询
CREATE TABLE t_sanguo(
	eid int primary key,
	ename varchar(20),
	manager_id int,-- 外键列
	foreign key (manager_id) references t_sanguo(eid)-- 添加子关联约束
);
-- 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);

-- 14.1.查询每一个人物和它的上级
SELECT * from t_sanguo a,t_sanguo b where a.manager_id=b.eid;
SELECT a.ename,b.ename from t_sanguo a,t_sanguo b where a.manager_id=b.eid;
SELECT a.ename,b.ename from t_sanguo a join t_sanguo b on a.manager_id=b.eid;

-- 14.2.查询所有人物及上级
SELECT a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id=b.eid;

-- 14.3.查询所有人物/上级.上上级 比如:张飞 刘备 刘协
SELECT 
	a.ename,b.ename,c.ename
from t_sanguo a 
	left join t_sanguo b on a.manager_id=b.eid
	left join t_sanguo c on b.manager_id=c.eid;