MySQL学习笔记5 多表操作

Failur

已于 2024-03-18 20:17:47 修改

阅读量771

点赞数 29

文章标签：数据库 mysql

于 2024-03-18 20:16:43 首次发布

本文链接：https://blog.csdn.net/Failur/article/details/136820285

版权

一、多表关系

多表关系

1.一对一关系

一对一关系很少见,遇到一对一关系最好是合并表

在任意表中添加唯一外键,指向另一张表的主键

2.一对多/多对一关系

原则在多的一方建立外键,指向另一个的主键

3.多对多关系

多对多关系的实现需要借助中间表,中间表包含两个字段

将多对多关系拆成一对多关系

中间表至少有两个外键,这两个外键分别指向原来两张表的主键

外键约束

1.简介

外键约束常与主键约束一起使用
对于两个关联关系的表而言,相关联字段中主键所在表为主表,外键为子表
外键用来建立主从表的关联关系,建立链接,约束两个表中数据的一致性和完整性

2.规则

主表必须存在
主表必须有主键
主键不能包含空值,但允许在外键中出现空值
主表表名后指定列名(组合).必须为主键的主表/候选键
外键中列的数目必须和主键中列的数目相同
外键中列的数据类型必须和主表中对应列的数据类型相同

二、多表操作

1.设置外键约束

-- 例1.创建表时设置外键约束
-- 创建部门表
create table if not exists
dept(
    deptno varchar(20) primary key , -- 部门号
    name varchar(20) -- 部门名字
);



-- 创建员工表
create table if not exists emp(
    eid varchar(20) primary key  key , -- 员工编号
    ename varchar(20), -- 员工名字
    age int, -- 员工年龄
    dept_id varchar(20), -- 员工部门
    constraint enp_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  key , -- 员工编号
    ename varchar(20), -- 员工名字
    age int, -- 员工年龄
    dept_id varchar(20) -- 员工部门
);

-- 创建外键约束
alter table emp2
add constraint dept_id_fk
foreign key (dept_id)
references dept2(deptno);

2.数据添加

/*
1.必须先给主表添加数据
2.给从表添加数据时,外键列的值必须依赖主表的主键列
*/
-- 给主表添加数据
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','蔡徐坤',26,'1001');
insert into emp values('4','李毅',44,'1002');
insert into emp values('5','田三川',20,'1002');
insert into emp values('6','张志硕',19,'1004');

3.数据删除

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

delete
from dept
where deptno = '1004';
-- [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`mydb3`.`emp`, CONSTRAINT `enp_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`deptno`))

delete
from dept
where deptno = '1003'; -- 删除成功

delete
from emp
where eid = '6'; -- 删除成功

4.删除外键约束

/*用于删除外键约束
  alter table <表名> drop foreign key <外键名>;
*/

-- 实现
alter table emp drop foreign key dept_id_fk;

5.多对多关系

/*
多对多关系需要建立中间表
*必须先建立两侧主表
*/
-- 操作
-- |学生表|中间表|课程表|
-- 1.创建学生表
create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age 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,
    constraint enp_fks foreign key (sid) references student(sid),
    constraint enp_fkc foreign key (cid) references course(cid)
);

drop table score;

三、多表联合查询

用于同时查询多个表

交叉连接查询
内连接查询
外连接查询
子查询
表自关联

交叉连接查询

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
A表m行 B表n行返回m*n条查询根据需要自行筛选

格式

select * from 表1,表2,表3…

select *
from dept3,emp3;
# 可用 where deptno = dept_id #筛选出部门匹配的数据

内连接查询

内连接查询求多张表的交集

隐式内连接查询

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
inner join emp3
on dept3.deptno= emp3.dept_id;

-- 查询每个部门的所属员工
-- 隐式
select *
from dept3 a,emp3 b
where b.dept_id=a.deptno; #别名用法 也可不用
-- 显式 将','替换为 inner join (inner可省略)
select *
from dept3 a inner join emp3 b
where b.dept_id=a.deptno;
-- 查询研发部以及销售部的所属员工
select *
from dept3 a inner join emp3 b
on a.deptno = b.dept_id;
-- 查询每个部门的员工数,并升序排序
select a.name,a.deptno,count(*)
from dept3 a join emp3 b
on a.deptno = b.dept_id
group by a.deptno;
-- 查询人数大于等于三的部门,并按照人数降序排序
select
       a.deptno,
       a.name,
       count(*) 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;

外连接查询

外连接查询分为左外,右外,满外查询

分别输出左,右,全的内容

两表相交的部分输出,不相交的部分输出NULL

– MySQL对满外查询的支持不好,故常使用Union关键词

左外连接

select * from A left outer join B on 条件

右外连接

select * from A right outer join B on 条件

满外连接

select * from A full outer join B on 条件

-- 查询哪些部门有员工,哪些部门没有

select *
from dept3
left join emp3
on dept3.deptno = emp3.dept_id;

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

-- Union关键词的使用

select *
from dept3
left join emp3
on dept3.deptno = emp3.dept_id
union
select *
from dept3
right join emp3
on dept3.deptno = emp3.dept_id;

四、子查询

简介

即select功能的嵌套

– 查询年龄最大的员工信息,显示细节

/* 即为下面两个查询的嵌套
1,查询最大年龄
select max(age)
from emp3;

2.查询年龄为最大年龄的员工信息
select *
from emp3
where age = ();
*/

–>

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,子查询
-- 1. 查询研发部和销售部的部门号
select deptno
from dept3
where name = '销售部' or name = '研发部';
-- 2. 查询哪个员工部门号为~
select *
from emp3
where dept_id in (
    select deptno
from dept3
where name = '销售部' or name = '研发部'
    );

-- 查询人事部20岁以下的员工信息
-- 1.关联查询
select *
from dept3 a join emp3 b
on a.deptno = b.dept_id
and (name = '人事部' and age<20);
-- 2.子查询

select *
from emp3
where age <= 20
and dept_id = (
    select deptno
    from dept3
    where name = '人事部'
    );

子查询关键字

ALL
ANY
SOME
IN
EXISTS

-- 关键词ALL
/*格式
  select ... from ... where c > all(查询语句)
  等价于
  select ... from ... where c > result1 and c > result2 and ...
*/

/*介绍
  表示比较对于每个元素都成立
*/

select *
from dept3;
-- 1. 查询年龄大于'1001'部门所有年龄的员工信息
select *
from emp3
where age >= all (select age from emp3 where dept_id = 1001);
-- 2.查询不属于任何一个部门的员工信息

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



-- 关键词ANY和SOME
/*格式
sel ... from ...  where c > any(查询语句)
*/

/*特点
  SOME与ANY效果等同
  与查询语句中的每一个元素作比较,只要有一个成立就会返回True
*/

-- 操作 查询年龄大于1001 部门任意一个员工年龄的员工信息
select *
from emp3
where age > any (select age from emp3 where dept_id = 1001);



-- 关键词IN
/*格式
sel ... 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(...)
*/

/*特点
至少有一行数据返回True
没有则返回False
* EXISTS关键字比IN关键字运算效率高,尽量使用
*/

-- 查询公司是否有大于60岁的员工,有则输出
select *
from emp3 a
where exists(
    select * from emp3 b where a.age > 30
    );

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

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

五、自关联查询

将一个表当作多个表使用

自己与自己相关联，叫做自关联查询

格式

 select 字段列表 from 表1 a, 表1 b where 条件

操作

-- 创建表，并且建立自关连约束
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);

select *
from t_sanguo;

-- 进行关联查询
-- 1.查询每个人物及其上级信息
select a.ename,b.ename
from t_sanguo a, t_sanguo b
where a.manager_id = b.eid;

-- 2.查询上级以及上级的上级
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;