mysql多表操作

 

 

 

 

 

 

 

 

 

 

 

 

 

 

外键特点

 

 

 操作外键约束

 

 

 

 

 

 

create database mydb3;
use mydb3;
-- 创建部门表,主表
create table if not exists dept(
deptno varchar(20) PRIMARY key,#部门号
name varchar(20)#部门名字
);
-- 创建员工表，并创建dept_id外键约束
-- constraint 外键约束名 foreign 字段(列)名 [，字段名2...] references 主表名 主键列1 [,主键列2...]
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 数据表名 add constriant 外键约束名 foreign key(列名) references 主表名(列名);
alter table emp2 add constraint emp2_fk foreign key(dept_id) references dept2(deptno);

 

 

 

-- 1.添加主表数据
-- 必须先给主表添加数据，主表没有的副表添加不上
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');
-- 2.添加附表数据
-- 注意给从表添加数据时，外键列的值必须依赖主表的主键列
insert into emp values('1','qf',20,'1001');
insert into emp values('2','dy',20,'1001');
insert into emp values('3','xz',20,'1001');
insert into emp values('4','az',20,'1002');
insert into emp values('5','sds',20,'1002');

insert into emp values('6','lqs',20,'1003');

insert into emp values('7','jmz',20,'1005');#不可以，主键没有
-- 3。删除数据

 

 

 

-- 3.删除数据
-- 主表数据被从表依赖时,不能删除,否则可以删除
-- 从表数据随便删除
delete from dept where deptno = '1001';#不可删除
delete from dept where deptno = '1004';#可删除
delete from emp where eid='7';#可删除

删除外键约束

 

 

 

-- 4.删除外键约束
-- alter table 从表名 drop foreign key 外键约束名
alter table emp2 drop foreign key emp2_fk;

外键约束 ---多对多关系

 

 

 

 

 

 

-- 学生表和课程表（多对多）
-- 1.创建学生表student(左侧主表)
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
Iage int,
gender Varchar(20)
);
-- 2.创建课程表course(右侧主表)
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 3创建中间表student_course/score
-- (从表)
create table score(
sid int,
cid int,
score double
);
-- 4建立外键约束（2次）逆向表到模型先拖主表再拖从表
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
-- 5给学生表添加数据
insert into student values(1,'xln',18,'nv'),(2,'az',19,'nv'),(3,'zzr',20,'nan');
-- 给课程表添加数据
insert into course values(1,'yw'),(2,'sx'),(3,'yy');
-- 给中间表添加数据
insert into score values(1,1,78),(1,2,75),(2,1,12),(2,3,22),(3,2,23),(3,3,55);
insert into score values(11,11,78);
-- 修改和删除时，中间从表可以随便删除修改，但两边主表受从表以来的数据不能删除修改

 

 

 

 

-- 多表查询-数据准备
-- 创建部门表
create table if not exists dept3(
deptno varchar(20)
primary key,
name varchar(20)
);
-- 创建员工表
create table if not exists emp3(
eid varchar(20)
primary key,
ename varchar(20),
age int,
dept_id varchar(20)
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给empty表添加数据
insert into emp3 values('1','qf',20,'1001');
insert into emp3 values('2','dy',20,'1001');
insert into emp3 values('3','xz',20,'1001');
insert into emp3 values('4','az',20,'1002');
insert into emp3 values('5','sds',20,'1002');
insert into emp3 values('6','lqs',20,'1003');

 

 

 

 

 

 

 -- deptno=dept_id就能找到我们想要的数据

use mydb3;
select * from dept3,emp3;

 

 

 

 

 

 

-- 内连接查询
/* 隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
*/

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

-- 查询每部门的员工数，并升序排序
select name,deptno,count(0) from dept3 inner join emp3 on deptno=dept_id group by name,deptno;
select age,deptno,count(0) from dept3 inner join emp3 on deptno=dept_id group by deptno;#年龄和统计数目无法一一对应，所以会报错

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

-- 外连接查询
-- 左外连接：以左表为主，即A表，把左表的数据全部输出，右表有对应数据就输出，没有就补null；
-- 右外连接：以右表为主，即B表，把右表的数据全部输出，左表有对应数据就输出，没有就补null；
-- 满外连接：把左外和右外并起来，既显示左外，也显示右外

 

-- 外连接查询
-- 查询哪些部门有员工，那些没有
select * from dept3 left outer join emp3 on deptno=dept_id;
select * from A
left join B on 条件1
left join C on 条件2；
-- 查询哪些员工有对应部门，那些没有
select * from dept3 right outer join emp3 on deptno=dept_id;
select * from A
right join B on 条件1
right join C on 条件2；
-- 实现满外连接：full join
-- 使用union关键字实现左外连接和右外连接并集
select * from dept3 full join emp3 on deptno=dept_id;
select * from dept3 left join emp3 on deptno=dept_id
union all
select * from dept3 right join emp3 on deptno=dept_id;
-- union是将两个查询结果上下拼接，并去重。union all不去重

 

-- 子查询
-- 子查询就是指在一个完整的查询语句中，潜逃若干个不同功能的小查询，从而一起完成复杂查询的一种编写程序，通俗一点就是包含select的查询
-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select max(age) FROM emp3;#查询最大年龄
select * from emp3 where age = 20;#让每个员工最大年龄比较，相等则满足条件
select * from emp3 where age = (select max(age) FROM emp3);
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select * from dept3 join emp3 on deptno = dept_id and name in('研发部','销售部');#关联查询
-- 2子查询
-- 先查询研发部和销售部部门号：deptno
select deptno from dept3 where name = '研发部' or name = '销售部';
-- 查询那个员工的部门号
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部');
-- 查询研发部20岁以下的员工信息，包括员工号、员工名字，部门名字
-- 关联查询
select * from dept3 join emp3 on deptno = dept_id and name ='研发部' and age <=20;
-- 子查询
-- 在部门表中查询研发部信息
select * from dept3 where name = '研发部';
-- 在员工表中查询年龄小于20岁的员工信息
select * from emp3 where age <= 20;

-- 将两个查询结果关联
select * from (select * from dept3 where name = '研发部') t1 join (select * from emp3 where age <= 20) t2 on t1.deptno=t2.dept_id;

 

 

 

 

-- 子查询关键字
1.
-- 查询年龄大于1003部门所有年龄的员工信息
SELECT * FROM emp3 WHERE AGE > ALL(SELECT age FROM emp3 WHERE dept_id = '1003');
-- 2．查询不属于任何一个部门的员工信息
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';

 

 

 

-- 子查询关键字in
-- select ... from... where c in(查询语句)
-- 查询研发部和销售部的员工信息
select eid,ename from emp3 where dept_id in(select deptno from dept3 where name='研发部' or name ='销售部');

 

 

-- 子查询关键字exists
-- select ... from... where 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 dept_id in(select deptno from dept3 b where a.dept_id = b.deptno);

 

 

-- 自关联查询
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,'lb',1);
insert into t_sanguo values(3,'gy',2);
insert into t_sanguo values(4,'zf',2);
insert into t_sanguo values(5,'cc',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 left join t_sanguo b on a.manager_id = b.eid;
-- 查询所有人物，上级，上上级
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;

练习

-- 创建test1数据库
create database test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(
deptno int primary key comment '部门编号',
dname varchar(14) comment '部门名称',
loc varchar(13) comment '部门地址'
);

insert into dept values(10,'accounting','new york'),
(20,'research','dallas'),
(30,'sales','chicago'),
(40,'operations','boston');
-- 创建员工表
create table emp(
empno int primary key comment '员工编号',
ename varchar(10) comment '员工名',
job varchar(9) comment '员工工作',
mgr int comment '员工直属领导编号',
hiredate date comment '入职时间',
sal double comment '工资',
comm double comment '奖金',
deptno int comment '对应dept表的外键'
);
-- 添加部门和员工之间的外键关系
alter table emp add constraint foreign key emp(deptno) references dept (deptno);
-- 创建工资等级表
create table salgrade(
grand int comment '等级',
losal double comment '最低工资',
hisal double comment '最高工资'
);
insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
insert into emp values
(7369,'smith','clerk',7902,'1980-12-17',800,null,20),
(7499,'allen','salesman',7698,'1981-02-20',1600,300,30),
(7521,'ward','salesman',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

 

-- 练习
-- 1、返回拥有员工的部门名、部门号。
select distinct 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.ename,a.hiredate,b.ename,b.hiredate from emp a join emp b on a.mgr=b.empno and a.hiredate < b.hiredate;
-- 5、返回员工姓名及其所在的部门名称。
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;
-- 6、返回从事clerk工作的员工姓名和所在部门名称。
select a.ename,b.dname,a.job from emp a,dept b where a.deptno=b.deptno and job='clerk';
-- 7、返回部门号及其本部门的最低工资。
select deptno,min(sal) from emp group by deptno;
-- 8、返回销售部(sales)所有员工的姓名。
select b.ename from dept a,emp b where a.deptno=b.deptno and a.dname='sales';
-- 9、返回工资水平多于平均工资的员工。
select * from emp where sal >(select avg(sal) from emp);
-- 10、返回与Scott从事相同工作的员工。
select * from emp where job= (select job emp where ename='scott') and 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)) as year_sal from emp order by year_sal desc;
select * from emp order by (sal*12 + ifnull(comm,0));
-- 14、返回工资处于第四级别的员工的姓名。
select ename from emp where sal between (select losal from salgrade where grand =4) and (select hisal from salgrade where grand =4);
-- 15.返回工资的职员名字、部门所在地
select *
from dept a
join emp b on a.deptno=b.deptno
join salgrade c on grand=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 grand=2 and b.sal > c.losal and b.sal <=c.hisal;