【MySQL数据库】多表关系与多表联合查询

目录

MySQL多表关系

一对一

一对多

多对多

MySQL外键约束

创建外键

数据插入

删除

多表联合查询

交叉连接查询

内连接查询

外连接查询

子查询关键字

自关联

MySQL多表关系

一对一

 一个学生对应一个身份证号码

一对多

一个部门可以有多名员工，但一个员工只能归于一个部门 在多的一方建立外外键指向一的一方

多对多

下表为多对多关系，由下表可知多对多关系的中间表至少需要2个外键

MySQL外键约束

创建外键

 格式： constraint <外键名> foreign key 字段名[，字段名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) –- 外键约束 
);

方式二 修改表添加外键

altertable <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> (<列名>); 

-- 创建部门表 
create table if not existsdept2( 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 emp2 add constraint dept_id_fk foreign key(dept_id)references dept2 (deptno);

数据插入

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

删除

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

多表联合查询

交叉连接查询

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积

• 笛卡尔积 可以理解为一张表的每一行去和另外一张表的任意一行进行匹配

• 假如 A 表有 m 行数据， B 表有 n 行数据，则返回 m*n 行数据

• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

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

内连接查询

• 隐式内连接（SQL92标准）：select * from A,B where 条件;
• 显示内连接（SQL99标准）：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;

外连接查询

•  左外连接：left outer join
•             select * from A left outer join B on 条件;
•   右外连接：right outer join
•             select * from A right outer join B on 条件;
• 满外连接: full outer join
•              select * from A full outer join B on 条件;

子查询关键字

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

返回的数据类型

• 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
• 单行多列：返回一行数据中多个列的内容；
• 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
• 多行多列：查询返回的结果是一张临时表

ALL

• ALL: 与子查询返回的所有值比较为true 则返回true
• ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
• ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

select …from …where c > all(查询语句)
--等价于：
select ...from ... where c > result1 and c > result2 and c > result3...

ANY/SOME

•ANY:与子查询返回的任何值比较为true 则返回true •ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。 •表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。 •SOME和ANY的作用一样，SOME可以理解为ANY的别名

select …from …where c > any(查询语句)
--等价于：
select ...from ... where c > result1 or c > result2 or c > result3...

IN

• IN关键字，用于判断某个记录的值，是否在指定的集合中
• 在IN关键字前边加上not可以将条件反过来

select …from …where c > in(查询语句)
--等价于：
select ...from ... where c > result1 or c > result2 or c > result3...

EXISTS

• 该子查询如果“有数据结果”(至少返回一行数据)， 则该EXISTS() 的结果为“true”，外层查询执行
• 该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行
• EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立
• 注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

select …from …where exists(查询语句)

自关联

 自关联.:自关联的基本含义就是 自己join自己，用一张表搞定多张表可以达到的效果. 需要注意的是，当表自关联的时候，需要使用 as 起别名，防止关联的时候引发重名的问题。

select. *. from. areas as p. inner join areas as c on c.cid=p.pid