4.MySQL多表操作-CSDN博客

create table dept(
detpno varchar(20) primary key,
name varchar(20)
)

create table emp(
eid varchar(20) primary key,
ename varchar(20),
age int,
dept_id varchar(20),
[constraint emp_fk] foreign key (dept_id) references dept(detpno)
)

方式二：创建表后添加外键约束

alter table <从表名> add constraint <外键名> foreign key （<从表列名>） reference <主表名>（<主键>）;

create table dept2(
detpno varchar(20) primary key,
name varchar(20)
)

create table emp2(
eid varchar(20) primary key,
ename varchar(20),
age int,
dept_id varchar(20)
)

alter table 
    emp2 
add CONSTRAINT 
    dept_id_fk 
foreign key
    (dept_id) 
REFERENCES 
    dept2(detpno);

数据插入

-- 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','乔峰',20,'1001');
insert into emp values ('2','段誉',21,'1001');
insert into emp values ('3','虚竹',23,'1001');
insert into emp values ('4','阿紫',18,'1002');
insert into emp values ('5','扫地僧',35, '1002');
insert into emp values ('6','李秋水',33,'1003');
insert into emp values ('7','鸠摩智',50, '1003') ;
insert into emp values ('8','天山童姥',60,'1004'); -- 可以
insert into emp values ('8','天山童姥',60,'1005') ;-- 不可以

删除数据

-- 3、删除数据
/*
注意：
		1.主表的数据被从表依赖时，不能删除，
		2.从表的数据可以随便删除
*/


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

删除约束

alter table <表名> drop foreign key <外键名>

alter table emp2 drop foreign key dept_id_fk

多对多

-- 1.创建学生表
create table student(
sid int PRIMARY KEY,
name varchar(20),
age int,
gender VARCHAR(20)
)

-- 2.创建课程表
create table course(
cid int primary key auto_increment, -- 自增长约束
cidname varchar(20)
)

-- 3.创建中间表
create table score(
sid int,
cid INT,
score double
)

-- 4.建立外键约束

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,'小林女',18,'女');
insert into student values(2,'小领女',20,'男');
insert into student values(3,'零零',22,'女');

-- 6.给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');

-- 7.给中间表添加数据

insert into score values (1,1,11),(1,2,56),(1,3,68),(2,3,99),(3,2,85)

修改和删除时，中间从表可以随意修改删除数据，但是主表受从表依赖，不可随意变动

3.多表联合查询

同时查询两个或两个以上的表

交叉连接查询

交叉连接查询返回被连接的两张表所有数据行的笛卡尔积
笛卡尔积可以理解为一张表的每一行和另外一张表的任意一行进行匹配
假如A表有m行数据，B表有n行数据，则返回m*n行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

→

内连接查询

隐式内连接、显式内连接

use mydb3;

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

select * from dept,emp;

-- 查询每个部门的所属员工

-- 显式
select * from dept,emp where detpno = dept_id;
select * from dept a,emp b where a.detpno = b.dept_id; -- 可以给表起别名，用表名.字段名指向
-- 隐式
select * from dept [inner] join emp on detpno = dept_id;
select * from dept a join emp b on a.detpno = b.dept_id; 

-- 查询研发部门的所属员工

	-- 显式
select * from dept,emp where detpno = dept_id and name = '研发部';
	-- 隐式
select * from dept join emp on detpno = dept_id and name = '研发部';


-- 查询研发部、销售部的所属员工
	-- 显式
select * from dept,emp where detpno = dept_id and (name = '研发部' or  name ='销售部');
select * from dept,emp where detpno = dept_id and name in ('研发部','销售部');
	-- 隐式
select * from dept join emp on detpno = dept_id and (name = '研发部' or  name ='销售部');
select * from dept join emp on detpno = dept_id and name in ('研发部','销售部');

-- 查询每个部门的员工数，并降序排序

	-- 显式
select dept.name,dept.detpno,count(*) from dept,emp where detpno = dept_id  group by dept.detpno,name order by count(*) asc;
	-- 隐式
select dept.name,dept.detpno,count(*) from dept join emp on detpno = dept_id  group by dept.detpno,name order by count(*) asc;
	

-- 查询人数大于等于3的部门，并按照人数降序排序

	-- 显式
select 
	dept.name,dept.detpno,count(*) c 
from dept,emp 
where detpno = dept_id  
group by dept.detpno,name 
having c >= 3 
order by c desc ;

	-- 隐式
select 
	dept.name,dept.detpno,count(*) c 
from dept join emp 
on detpno = dept_id  
group by dept.detpno,name 
having c >= 3 
order by c desc ;


INNER JOIN 连接五个数据表的用法：
SELECT * FROM 
(
	(
			(
				表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
			) 
						INNER JOIN 表3 ON 表1.字段号=表3.字段号
	) 
						INNER JOIN 表4 ON Member.字段号=表4.字段号
) 
						INNER JOIN 表5 ON Member.字段号=表5.字段号

外连接查询


-- 外连接查询
-- 查询哪些部门有员工，哪些没有
select * from dept3 a left join emp3 b on a.deptno = b.dept_id;

select * from A
	left join B on 条件1
	left join B on 条件2	
	left join B on 条件3	
	

-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 a right join emp3 b on a.deptno = b.dept_id;

-- 使用union关键字实现左外连接和右外连接的并集
-- 使用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;

select * from dept3 a full join emp3 b on a.deptno = b.dept_id； 
-- 满外连接 full join 不可识别

子查询

子查询略嫌麻烦，可以用关联查询

-- 子查询
-- 查询年龄最大的员工信息，显示信息包括员工号、员工名字、员工年龄
select max(age) from emp; -- 单行单列

-- 让每一个员工的年龄和最大年龄进行比较，相等则满足条件
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 = '销售部';

-- 方式二 子查询
-- 2.1 先查询研发部、销售部的部门号
select deptno from dept3 where name = '研发部'or name = '销售部'

-- 2.2 查询哪个员工的部门号是1001或1002
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部'or name = '销售部'); -- 多行多列

select * from dept3 join emp3 on dept_id in ('1001','1002');

select * from  emp3 where dept_id in ('1001','1002');

-- 查询研发部30岁以下的员工信息，包括员工号、员工名字、部门名字
-- 方式一 关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' and age < 30)

-- 方式二 子查询
-- 2.1 在部门表中查询研发部的信息
select * from dept3 where name ='研发部'
-- 2.2在员工表中查询年龄小于30的员工信息
select * from emp3 where age<30
-- 2.3 将以上两个查询的结果进行关联查询
select * from (select * from dept3 where name ='研发部') t1 join (select * from emp3 where age<30) t2 on t1.deptno = t2.dept_id

select * from (select * from dept3 where name ='研发部') t1,(select * from emp3 where age<30) t2 where  t1.deptno = t2.dept_id

子查询关键字

all

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

any/some

-- 查询年龄大于1003部门任意一个员工年龄的员工信息
select * from emp3 where age > any (SELECT age from emp3 where dept_id = '1003') and dept_id!= '1003';

in

-- 查询研发部和销售部的员工信息，包括员工号、员工名字
select eid,ename from emp3 where dept_id in (select deptno from dept3 where name ='研发部' or name = '销售部' )

exists

-- 查询公司是否有大于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);

⭐exists 还不是很懂，

表自关联

-- 创建表，并添加自关联约束
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;
select a.ename,b.ename from t_sanguo a join t_sanguo b on 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;