系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

Mysql多表操作方法讲解教程

时间:2022-12-06来源:www.pcxitongcheng.com作者:电脑系统城

外键约束

概念

特点

定义一个外键时,需要遵守下列规则:

主表必须已经存在于数据库中,或者是当前正在创建的表。

必须为主表定义主键。

主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这 个外键的内容就是正确的。

在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

外键中列的数目必须和主表的主键中列的数目相同。

外键中列的数据类型必须和主表主键中对应列的数据类型相同。

操作

建立外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create database  mydb3;
use mydb3;
create table if not exists dep
(
pid int primary key,
name varchar(20)
);
create table if not exists per
(
id int primary key,
name varchar(20),
age int,
depid int,
constraint fok foreign key(depid) references dep(pid)
);
create table if not exists dep3
(
pid int primary key,
name varchar(20)
);
create table if not exists per3
(
id int primary key,
name varchar(20),
age int,
depid int
);
alter table per3 add constraint fok3 foreign key(depid) references dep3(pid);

数据插入

必须先给主表添加数据,且从表外键列的值必须依赖于主表的主键列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
insert into dep3 values('1001','研发部');
insert into dep3 values('1002','销售部');
insert into dep3 values('1003','财务部');
insert into dep3 values('1004','人事部');
-- 给per3表添加数据
insert into per3 values('1','乔峰',20, '1001');
insert into per3 values('2','段誉',21, '1001');
insert into per3 values('3','虚竹',23, '1001');
insert into per3 values('4','阿紫',18, '1001');
insert into per3 values('5','扫地僧',85, '1002');
insert into per3 values('6','李秋水',33, '1002');
insert into per3 values('7','鸠摩智',50, '1002');
insert into per3 values('8','天山童姥',60, '1003');
insert into per3 values('9','慕容博',58, '1003');

数据删除

主表数据被从表依赖时不能删除,否则可以删除;从表的数据可以随便删除。

如下,第一句和第二句执行成功,第三句执行失败

1
2
3
delete from per3 where depid=1003;
delete from dep3 where pid=1004;
delete from dep3 where pid=1002;

删除外键约束

语法:alter table 从表drop foreign key 关键词名;

1 alter table per3 drop foreign key fok3;

多表联合查询

概念

操作

交叉连接查询

1 select * from dept,emp;

内连接查询

注释;上面是隐式内连接,下面是显式内连接

1
2
3
4
5
6
7
8
9
select * from dept,emp where dept.deptno=emp.dept_id;
select * from dept join emp on dept.deptno=emp.dept_id;
select * from dept join emp on dept.deptno=emp.dept_id and name='研发部';
select * from dept join emp on dept.deptno=emp.dept_id and name='研发部';
select * from dept join emp on dept.deptno=emp.dept_id and (name='研发部' or name='销售部');
select * from dept join emp on dept.deptno=emp.dept_id and (name='研发部' or name ='销售部');
select * from dept join emp on dept.deptno=emp.dept_id and name in ('研发部','销售部');
select a.name,a.deptno,count(*) from dept a join emp on a.deptno=emp.dept_id group by dept_id;
select a.name,a.deptno,count(*) total from dept a join emp on a.deptno=emp.dept_id group by dept_id having total >=3 order by total desc;

外连接查询

若是对应的外表没有数据就补NULL

1
2
3
4
5
6
7
select * from dept a left join emp b on a.deptno=b.dept_id;
select * from dept a right join emp b on a.deptno=b.dept_id;
-- select * from dept a full join emp b on a.deptno=b.dept_id; --不能执行
-- 用下面的方法代替上面的full join
select * from dept a left join emp b on a.deptno=b.dept_id union select * from dept a right join emp b on a.deptno=b.dept_id;
-- 对比union all,发现union all没有去重过滤
select * from dept a left join emp b on a.deptno=b.dept_id union all select * from dept a right join emp b on a.deptno=b.dept_id;

子查询

1
2
3
4
5
select * from emp where age<(select avg(age) from emp);
select * from emp a where a.dept_id in (select deptno from dept where name in ('研发部','销售部'));
-- 对比关联查询和子查询如下
select * from emp a join dept b on a.dept_id=b.deptno and (b.name='研发部' and age<30);
select * from (select * from dept where name='研发部') a join (select * from emp where age<30) b on b.dept_id=a.deptno;

子查询关键字

all关键字的用法

1
2
select * from emp where age>all(select age from emp where dept_id='1003');
select * from emp a where a.dept_id!=all(select deptno from dept);

any(some)关键字的用法

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

in关键字的用法

1 select ename,eid from emp where dept_id in (select deptno from dept where name in ('研发部','销售部'));

exists关键字的用法

1
2
3
4
select * from emp a where a.age<30;
select * from emp a where exists(select * from emp where a.age<30);
select * from emp a where a.dept_id in (select deptno from dept b);
select * from emp a where exists (select * from dept b where a.dept_id = b.deptno);

自关联查询

多表操作总结

到此这篇关于Mysql多表操作方法讲解教程的文章就介绍到这了

分享到:

相关信息

  • MySQL数据库的约束使用实例

    1. NULL约束 2. UNIQUE(唯一约束) 3. DEFAULT(默认值约束) 4. PRIMARY KEY(主键约束) 5. FOREIGN KEY(外键约束)...

    2022-12-06

  • Mysql去重的几种方式分步讲解

    我们做数据分析的时候经常会遇到去重问题,下面总结 sql 去重的几种方式,后续如果还有再补充,大数据分析层面包括 hive、clickhouse 也可参考。...

    2022-12-06

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载