MySQL的多表操作
外键约束——一对多
创建外键约束
在create table 语句中，通过foreign key 关键字来指定外键，具体语法如下：
[consaraint <外键名>] foreign key 字段名 references <主表名> 主键列实现：
create database mydb3;
use mydb3;
#创建部门表——主表
create table if not exists dept(
detpno varchar(20) primary key, #部门号
name varchar(20) #部门名字
);
#创建员工表——从表
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) refrerences dept (detpno) #外键约束
);修改表时添加外键约束，前提是：从表中的外键列中的数据必须和主表中的主键列中的数据保持一致或者没有数据。
alter table <数据表名> add constraint <外键名> foreign key （<列名>） refences < 主表名> (<列名>);实现：
#创建部门表——主表
create table if not exists dept(
detpno varchar(20) primary key, #部门号
name varchar(20) #部门名字
);
#创建员工表——从表
create table if not exists emp(
eid varchar(20) primary key, #员工编号
ename varchar(20), #员工名字
age int, #员工年龄
dept_id varchar(20), #员工所属部门
);
#创建外键约束
alter table emp2 add constraint dept_id_fk foreign(dpet_id) references dept2 (detpno);
外键约束——插入数据
数据插入必须先给主表添加数据，注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列。
外键约束——删除数据
主表数据被从表依赖时，不能删除，否者可以删除。
从表的数据可以随便删。
删除外键约束
语法格式：
alter table <表名> drop foreign key <外键约束名>;实现：
alter table emp2 drop foreign key dept_id_fk;外键约束——多对多
在多对多关系中，A表的一行对应B的多行，B表的一行对应A表的多行，我们要新增一个中间表，来建立多对多的关系。
实现：
#创建mydb3数据库
create database mydb3;
#使用mydb3
use mydb3;
#创建主表1——student
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
#创建主表2——course
create table if not exists course(
cid int primary key auto_increment,
cidname varchar(20)
);
#创建从表——score
create table score(
sid int,
cid int,
score double
);
#添加多对多的外键约束
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
#向表中插入数据
insert into student values(1,'小龙女',18,'女'),(2,'杨过',12,'男'),(3,'路明非',20,'男');
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
insert into score values(1,1,65),(1,2,55),(2,1,34),(2,3,59),(3,2,90),(3,3,88);删除外键约束需要注意只能先删除从表，不能从主表开始删除，主表和从表之间存在约束关系
多表联合查询
多表联合查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的的时候，需要显示的数据来自多张表。
多表查询有以下分类：
交叉连接查询
（产生笛卡尔积，了解）
格式：
select * from A,B;
实现：
select * from dept3,emp3;内连接查询
内连接查询求的是交集
#隐式内连接：
select * from A,B where 条件;
#显示内连接：
select * from A inner join B on 条件;查询每个部门的所属员工
#隐式内连接
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 a,emp3 b where a.deptno = b.dept_id;
#显示内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id;
查询研发部和销售部的所属员工
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 ('研发部' , '销售部');查询人数大于等于3的部门，并按照人数降序排序
select
a.deptno,
a.name,
count(1) as total_cnt
from dept3 a
join emp3 b on a.dept_no = b.dept_id
group by
a.deptno,a.name
having
total_cnt >= 3
order by
total_cnt desc;外连接查询
#左外连接：left outer join
select * from A left outer join B on 条件;
#右外连接：right outer join
select * from A right outer join B on 条件;
#满外连接：full outer join
select * from A full outer join B on 条件;
#注意：oracle里面有full join，可是在mysql对full join支持的不友好，可以使用union来达到目的。
union是将两个查询结果上下拼接，并去重
select * from A left outer join B on 条件;
union
select * from A right outer join B on 条件;union all是将两个查询结果上下拼接，不去重
select * from A left outer join B on 条件;
union all
select * from A right outer join B on 条件;子查询
select的嵌套
查询年龄最大的员工信息
1.查询最大年龄
select max(age) from emp3;
2.让每一个员工的年龄和最大年龄进行比较，相等则满足条件
select * from emp3 where age = (select max(age) from emp3); ——————单行单列查询研发部和销售部的员工信息，包括员工编号、名字
#方式一（关联查询）
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
#方式二（子查询）
先查询研发部和销售部的部门编号
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 = '销售部');查询研发部年龄小于30岁的员工信息
#方式一（关联查询）
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' and age <30);
#方式二（子查询）
先在部门表查询研发部信息
select * from dept3 where name = '研发部';
在员工表中查询年龄小于30岁的员工信息
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;子查询关键字
ALL——关键字
格式：
select ... from ... where c > all(查询语句);操作：
查询年纪大于‘1003’部门所有年龄的员工信息
select * from emp3 where age >all(select age from dept3 where dept_id= '1003');ANY和SOME——关键字
格式：
select ... from ... where c > any(查询语句);IN——关键字
格式：
select ... from ... where c in(查询语句);查询研发部和销售部的员工信息，包含员工号、名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部');EXISTS——关键字
这个关键字会根据条件一行一行的数据进行判断，只有exists条件为true才会进行下一步。
查询公司是否有大于60岁的员工，有则输出
select * from emp3 a where exists(select * from emp3 where a.age>60);
#必须要在外查询的表添加别名，通过外查询来决定条件是否成立。
#确切的说是外层的select是一行一行的执行的查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);表自关联
将一张表当成多张表来用，对表自身进行关联查询，注意自关联时必须给表起别名。
格式：
select * from 字段列表 from 表1 a, 表1 b where 条件;
或者
select * from 字段列表 from 表1 a [left] join 表1 b on 条件;操作：
#创建表，并建立自关联约束
create table t_sanguo(
eid int primary key,
ename varcahr(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);
#查询每一个三国人物及他的上级信息
select a.ename ,b.ename from t_sanguo a,t_asnguo b where a.manager_id = b.eid;
#查询所有人物及上级(左外连接)
select a.ename ,b.ename from t_sanguo a left join t_asnguo b on a.manager_id = b.eid;
#查询所有人物的上级、上上级
select
a.ename ,b.ename ,c.ename
from t_sanguo a
left join t_asnguo b on a.manager_id = b.eid
left join t_asnguo c on b.manager_id = c.eid