MySQL多表操作

熟能生巧，全部代码在最后！！！

一、多表关系

一对一关系、一对多关系、多对多关系

注意多对多关系必须有中间表进行关联

多对多的关系就相当于是两个一对多关系

二、创建外键约束

专门用于多表操作的一种约束方式

控制的那个表是主表，被控制的表是从表，外键列的值受主键列的值约束

就是让外键列受到主键列的约束，主键列控制外键列，主键列外键列类型要一致

关键字：foreign key

constraint是给表起别名，可以不使用这一步

记得要先创建主键

一般来说使用第一种方式，直接在创建表的时候创建外键约束

特点：主表的数据可以随便添加，从表的外键列数据必须依赖主表

主表的数据如果被从表依赖，则不能删除，而从表的数据可以随便删除

三、外键约束-一对多关系-验证外键约束的作用 

注意：必须先给主表添加数据，在给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列

外键列里的必须是主键列里有的

删除数据：

        主表的数据被从表依赖时，不能删除，否则可以删除

        从表的数据可以随便删除

删除表：

        必须先删除从表，在删除主表

1001不可以删除，但是1004可以删除

删除外键约束：

删除外键约束后，删除数据也可以了

四、外键约束-多对多关系-构建外键约束

修改和删除时，中间从表可以随便删除和修改，但是两边的主表受从表依赖的数据不能删除或者修改

五、多表联合查询

外键约束只是对增删改起作用，对多表查询并无影响。

数据准备

交叉连接查询

形成一个笛卡尔集：就是用一张表的每一行去匹配另外一张表的每一行

我们只需要让左表的数据等于右表的数据即可得到想要的数据

内连接查询

求的是交集

-- 查询每个部门的所属员工
-- 隐世内连接
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 d,emp3 e where d.deptno = e.dept_id;
-- 显世内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 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 ,emp3 b where a.deptno = b.dept_id and name = '研发部';
-- 显世内连接
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name = '研发部';

-- 查询研发部和销售部的所属员工
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name in('研发部','销售部');

-- 查询每个部门的员工数，并升序排序
select a.deptno,a.name,count(1) from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name;

-- 查询人数大于等于2的部门，并按照人数降序排序
select a.deptno,a.name,count(1) as total_cnt from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name  having total_cnt >= 2 order by total_cnt desc;
 

外连接查询

outer可以省略

左外连接：

会把左表的数据全部输出，右表的数据如果有对应的就输出，没有就补null

如果有多张表：select * from a left join b on 条件1 left join c on 条件2 left join d on 条件3；

右外连接：

会把右表的数据全部输出，左表的数据如果有对应的就输出，没有就补null

如果有多张表：select * from a right join b on 条件1 right join c on 条件2 right join d on 条件3；

满外连接：

就是把左表和右表一起输出

实现满外连接：full join

mysql不支持full join

union 是把上下的代码拼起来，相当于把左右两个结果并起来，并去重

union all 是把左右两个结果放一起，没有去重

基本子查询

单行单列，可以作为一个值使用

多行多列

一般来说关联效率更高一点

括号里面是2个结果，所以不能用=要使用in

多行多列

子查询的表必须要设置别名

子查询关键字     -      all

all是要大于所有的值

就是and

关键字      -     any和some

就是和最值比较

这两个是一样的，只是一个别名罢了

any是比任意一个值大就好了

关键字      -      in

就是or

关键字      -      exists

就是看有没有结果

exists比in的运算效率高，在实际开发中，特别是大数据容量时，推荐使用exists关键字

全表输出，只要exists关键字后面的是正确的就正常执行

因为没有大于60岁的所以输出null

因为有大于20分的所以全部输出

如果查询时没有使用别名就是整个表查询，如果使用了别名就是每一行一行查询

查询有所属部门的员工信息

自关联查询

需要对自身进行关联查询时使用，把一张表当成多张表来用，注意自关联时表必须给表起别名

对自身进行关联查询，结构示意图

六、MySQL多表操作-总结

思维导图

七、练习

数据准备

练习：

八、全部代码

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),  -- 员工编号
  ename varchar(20),  -- 员工名字
  age int, -- 员工年龄
  dept_id varchar(20), -- 员工所属部门
  constraint emp_fk foreign key(dept_id) references dept(detpno) -- 外键约束
  );
  
use mydb3;
create table if not exists dept2(
  detpno varchar(20) primary key, -- 部门号
  name varchar(20) -- 部门名字
  );
create table if not exists emp2(
  eid varchar(20),  -- 员工编号
  ename varchar(20),  -- 员工名字
  age int, -- 员工年龄
  dept_id varchar(20) -- 员工所属部门 
  );
alter table emp2 add constraint emp2_fk foreign key(dept_id) references dept(detpno);

insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');

insert into emp values('1','琼安奉',20,'1001');
insert into emp values('2','阿坎德',21,'1001');
insert into emp values('3','阿达啊',23,'1001');
insert into emp values('4','阿达尔',18,'1001');
insert into emp values('5','凤飞飞',25,'1001');
insert into emp values('6','发发哇',35,'1001');
insert into emp values('7','阿强啊',33,'1005');

delete from dept where detpno = '1001'; -- 不可以删除
delete from dept where detpno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除

alter table emp2 drop foreign key emp2_fk;

insert into dept2 values('1001','研发部');
insert into dept2 values('1002','销售部');
insert into dept2 values('1003','财务部');
insert into dept2 values('1004','人事部');

insert into emp2 values('1','琼安奉',20,'1001');
insert into emp2 values('2','阿坎德',21,'1001');
delete from dept2 where detpno = '1001';

use mydb3;
create table if not exists student(
  sid int primary key auto_increment,
  name varchar(20),
  age int,
  gender varchar(20)
  );
  
create table course(
  cid int primary key auto_increment,
  cidname varchar(20)
  );
  
-- 从表
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,'ikun',18,'女'),(2,'zhangji',29,'男'),(3,'xiji',32,'男');
insert into course values(1,'语文'),(2,'数学'),(3,'英语');  
insert into score values(1,1,78),(1,2,76),(2,1,87),(2,3,75),(3,2,57),(3,3,96); 
  
use mydb3;  
create table if not exists dept3(
  deptno varchar(20),  -- 部门号
  name varchar(20) -- 部门名字
  );
create table if not exists emp3(
  eid varchar(20) primary key, -- 员工编号
  ename varchar(20), -- 员工名字
  age int, -- 年龄
  dept_id varchar(20) -- 所属部门
  );
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','人事部');
insert into dept3 values('1003','销售部');
insert into dept3 values('1004','财务部');
insert into dept3 values('1006','财务部');

insert into emp3 values('1','琼安奉',20,'1001');
insert into emp3 values('2','阿达啊',22,'1002');
insert into emp3 values('3','阿达发',18,'1003');
insert into emp3 values('4','阿尔法',24,'1004');
insert into emp3 values('5','阿飞发',33,'1001');
insert into emp3 values('6','爱蜂窝',29,'1005');

select * from dept3,emp3;

-- 查询每个部门的所属员工
-- 隐世内连接
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 d,emp3 e where d.deptno = e.dept_id;
-- 显世内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 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 ,emp3 b where a.deptno = b.dept_id and name = '研发部';
-- 显世内连接
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name = '研发部';

-- 查询研发部和销售部的所属员工
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name in('研发部','销售部');

-- 查询每个部门的员工数，并升序排序
select a.deptno,a.name,count(1) from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name;

-- 查询人数大于等于2的部门，并按照人数降序排序
select a.deptno,a.name,count(1) as total_cnt from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name  having total_cnt >= 2 order by total_cnt desc;

use mydb3;
select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id;

select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;

select * from dept3 a full join emp3 b on a.deptno = b.dept_id;

select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id
union all
select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;

select max(age) from  emp3;
select * from emp3 where age = 33;
-- 化简语法
select * from emp3 where age = (select max(age) from emp3); -- 单行单列，可以作为一个值使用

-- 方式1：关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
-- 方式2：子查询
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部');

-- 方式1：关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' and age < 30);
-- 方式2：子查询
select * from (select * from dept3 where name = '研发部') t1 join (select * from emp3 where age < 30) t2 on t1.deptno = t2.dept_id;

select * from emp3 where age > all(select age from emp3 where dept_id = '1002');

select * from emp3 where dept_id != all(select deptno from dept3);

select * from emp3 where age > any(select age from emp3 where dept_id = '1003') and dept_id != '1003';

select eid,ename from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部');

select * from emp3 where exists(select * from emp3);
select * from emp3 where exists(select 1);

select * from emp3 where exists(select age from emp3 where age > 60);
select * from emp3 where exists(select age from emp3 where age > 20);
select * from emp3 a where exists(select age from emp3 where a.age > 20);

select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

use mydb2;
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);

-- 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;

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

-- 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;

create database test1;

use test1;

create table dept(
  deptno int primary key,  -- 部门编号
  dname varchar(14), -- 部门名称
  loc varchar(13) -- 部门地址
  );

insert into dept values (10,'accounting','new york');
insert into dept values (20,'research','dallas');
insert into dept values (30,'sales','chicago');
insert into dept values (40,'operations','boston');

create table emp(
  empno int primary key,  -- 员工编号
  ename varchar(10),  -- 姓名
  job varchar(9),  -- 工作
  mgr int,  -- 直属领导编号
  hiredate date,  -- 入职时间
  sal double,-- 工资
  comm double,  -- 奖金
  deptno int -- 对应dept表的外键
  );

-- 添加部门和员工 之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept(deptno);

insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3000,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13',1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','clerk',7782,'1981-01-23',1300,null,10);

create table salgrade(
  grade int,  -- 等级
  losal double,  -- 最低工资
  hisal DOUBLE -- 最高工资
  );
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

-- 1 、返回拥有员工的部门名、部门号
select d.dname,d.deptno from dept d join emp e on d.deptno = e.deptno;
select distinct d.dname,d.deptno from dept d join emp e on d.deptno = e.deptno; -- 去重

-- 2、工资水平多余smith的员工信息
select * from emp where sal > (select sal from emp where ename = 'smith');

-- 3、返回员工和所属经理的姓名
select a.ename,b.ename from emp a,emp b where a.mgr = b.empno;

-- 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select a.empno,a.hiredate,b.empno,b.hiredate from emp a,emp b where a.mgr = b.empno and a.hiredate < b.hiredate;

-- 5、返回员工姓名及其所在的部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

-- 6、返回从事clerk工作的员工姓名和所在部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'clerk';

-- 7、返回部门号及其本部门的最低工资
select deptno,min(sal) from emp group by deptno;

-- 8、返回销售部（sales）所有员工的姓名
select e.ename from emp e join dept d on e.deptno = d.deptno and d.dname = 'sales';

-- 9、返回工资水平多于平均工资的员工
select e.ename from emp e where sal > (select avg(sal) from emp);

-- 10、返回与scott从事相同工作的员工
select e.ename,e.job from emp e where job = (select job from emp where ename = 'scott');

-- 11、返回工资高于30部门所有员工工资水平的员工信息
select * from emp where sal > all(select sal from emp where deptno = 30);

-- 12、返回员工工作及其从事此工作的最低工资
select job,min(sal) from emp group by job;

-- 13、计算出员工的年薪，并且以年薪排序
select ename,(sal * 12 + ifnull(comm,0)) from emp order by (sal * 12 + ifnull(comm,0)) desc;

-- 14、返回工资处于第四级别的员工的姓名
select ename from emp where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4);

-- 15、返回工资为二等级的职员名称、部门所在地
select * from dept a join emp b on a.deptno = b.deptno join salgrade c  on grade = 2 and b.sal >= c.losal and b.sal <= c.hisal;
select * from dept a,emp b,salgrade c where a.deptno = b.deptno and grade = 2 and b.sal >= c.losal and b.sal <= c.hisal;