Mysql基础（四）——多表查询内键外键、union、join、子查询、in/exists

Z时代
2024-01-10
分类：综合

database

1. 到底什么是主键，外键？

基本概念：

MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。

用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接

主键作用：

能够唯一表示数据表中的每个记录的字段或字段组合；

与其他表中的数据进行关联（不同表中各记录间的简单指针）；

主键不能是空值；

唯一约束是指用于指定一个或多个列的组合值具有唯一性，防止重复，所有找主键值对用户而言没有什么意义的。

外键：

C是表A的主键，B中也有C字段，则C是表B的外键

外键约束主要用来维护两个表之间数据的一致性

A为基本表，B为信息表，两个表的C字段列必须是数据类型相似，比如 int和tinyint可以，而int和char则不可以

方法1

建表指明外键：

create table 表B（... ,

　　　　　　　　　CONSTRAINT `外键名称` FOREIGNKEY(`外键字段名称`) REFERENCES 外键A表名(`主键字段名称`) ON DELETE SET NULL ON UPDATE SET NULL

　　　　　　　　) charset=utf8mb4;

例如：　　　　　　　　

createtable employee(
　　　　　　　　　　　no int comment "员工编号",
　　　　　　　　　　　name varchar(20) comment "姓名",
　　　　　　　　　　　 job varchar(40) comment "职位",
　　　　　　　　　　　mgr int comment "上司id",
　　　　　　　　　　　hiredate date comment "雇佣时间",
　　　　　　　　　　　sal double(10, 2) comment "工资",
　　　　　　　　　　 comm double(10, 2) comment "奖金", 　　
　　　　　　　　　　deptno int comment "部分id",
　　　　　　　　　　CONSTRAINT `fk` FOREIGNKEY(`deptno`) REFERENCES dept(`deptno`) ONDELETESET NULL ONUPDATESET NULL
　　　　　　　　　　) charset=utf8mb4;

方法2

后期为表添加外键语法：

alter table 表B add constraint `外键名称` foreign key(`外键字段名称`) references 外键A表名(`主键字段名称`) on delete set null on update set null;

例如：

altertable employee addCONSTRAINT `fkdd` FOREIGNKEY(`deptno`) REFERENCES dept(`deptno`) ONDELETESETNULLONUPDATESETNULL;

外键的几种模式：

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动，默认值）
CASCADE（跟随外键改动，表A影响到表B）
SET NULL（设空值，B表的外键列不能为NOT NULL）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）

外键优点：数据一致性，减少写代码的量，保证数据可靠性。

外键缺点：会影响性能，为保证数据完整性会使得并发性降低。

外键与id方式优缺点相反

2. 并集（union）　　　　　　　　

并集（union）也叫联合查询

union和union all 区别：

union:可以去重

union all:返回所有记录，效率高于union

在多个select语句中，第一个语句中的字段将被用于结果的字段名称显示

例如：

SELECT id, name, age FROM student
UNIONALL
SELECT id, name, age FROM teacher;

SELECT id, name, age FROM student 
UNION
SELECT age, name, id FROM teacher;

在联合查询中，当使用 ORDER BY 的时候，需要对 SELECT 语句添加括号，并且与LIMIT结合使用才生效

如：

(SELECT classId, id, name, age FROM student WHERE classId =1ORDERBY age DESC LIMIT 2) 
UNION
(SELECT classId, id, name, age FROM student WHERE classId =2ORDERBY age);

也可对最终结果进行排序：（...) union (...) order by id;

3. 笛卡尔积

先确定数据要用到哪些表，将多个表先通过笛卡尔积变成一个表

然后去除不符合逻辑的数据（根据两个表的关系去掉）

最后当做一个虚拟表来加上条件即可

A表m条数据，B表n条数据，一共产生m*n条数据

当然也可自交

例如：select * from 表A,表B；

4. 内连接外连接

内连接：join，inner join

外连接：left join，left outer join，right join，right outer join，union，union al

交叉连接：cross join

on: ...... on A.id=B.id;

用using简化当两张表的列相同：.......using (id)

where:也可用where 代替on

先看图

内连接

select * from a inner join b on a.id = b.id;
select * from a join b on a.id = b.id;
select * from a, b where a.id = b.id;

当表A中的一条记录对应表B中的多条记录时，会以重复的方式对应多条表B记录出现在结果集中。

当表B中的一条记录对应表A中的多条记录时，会以重复的方式对应多条表A记录出现在结果集中。

左连接：

也叫左外连接

select * from a left join b on a.id = b.id;
select * from a left outer join b on a.id = b.id;

左外连接，会以左边的表A为主表，返回所有行，即使右表B中没有匹配的行。

如果左边的表A在右表B中找不到一条记录，则返回表A所有记录并且表B相应的字段设为null。

如果左边的表A在右表B中找到多条记录，则以相同表A记录和不同表B记录多条显示在结果集中。

这种情况下，其实是把表A中所有记录都查询出来了，包括不满足条件的记录。

如果我们只想查出表A中满足条件的：

select * from a left join b on a.id = b.id where b.id is null;

右连接：

也叫右外连接

select * from a right join b on a.id = b.a_id;
select * from a right outer join b on a.id = b.a_id;

如果只想查表B满足要求的：

select * from a right join b on a.id = b.id where a.id is null;

全连接：

mysql并不支持全连接，不过有相应的替代方案，就是left join union right join 来代替。

select * from a left join b on a.id = b.id
union
select * from a right join b on a.id = b.id;

如果只想显示所有不满足条件的记录，则通过如下语句：

select * from a left join b on a.id = b.a_id where b.a_id is null
union
select * from a right join b on a.id = b.a_id where a.id is null;

交叉连接：

交叉连接实际上就是表A与表B的笛卡尔乘积

select * from a cross join b;
select * from a, b;

5. 子查询

概念：当一个查询时另一个查询的条件时，称之为子查询（内层查询）

使用原因：很多时候两张表可能非常大，做笛卡尔积之后，更大，会死机

子查询优点：减少查询次数！

好习惯：先通过select count(*)的方式查看记录数。

一般在where或者from子句中出现，外层的select为主查询。

子查询出现位置：

where子句子查询：返回单行单列，多行单列，单行多列数据.多行单列子查询通常包含 in ,any,all,exists关键字

from子句子查询：返回多行多列的数据，可以做一张临时表，要求必须起别名

select后面:仅支持标量子查询

#案例1：谁的工资比 Abel 高?

#①查询Abel的工资

SELECT salary
FROM employees
WHERE last_name ="Abel"；

#②查询员工的信息，满足 salary>①结果

SELECT*
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name ="Abel"
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资

#①查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id =141；

#②查询143号员工的salary

SELECT salary
FROM employees
WHERE employee_id =143

#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id =141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id =143
);

#案例3：返回公司工资最少的员工的last_name,job_id和salary

#①查询公司的最低工资

SELECTMIN(salary)
FROM employees；

#②查询last_name,job_id和salary，要求salary=①

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECTMIN(salary)
FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#①查询50号部门的最低工资

SELECTMIN(salary)
FROM employees
WHERE department_id =50;

#②查询每个部门的最低工资

SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id;

#③ 在②基础上筛选，满足min(salary)>①

SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id
HAVINGMIN(salary)>(
SELECTMIN(salary)
FROM employees
WHERE department_id =50;

#案例：查询员工编号最小并且工资最高的员工信息

SELECT*
FROM employees
WHERE (employee_id,salary)=(
SELECTMIN(employee_id),MAX(salary)
FROM employees
);

select后面/*仅仅支持标量子查询

#案例：查询每个部门的员工个数

SELECT d.*,(
SELECTCOUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
 ) 个数
FROM departments d;

any关键字：

　　　　=any ：和in关键字一样

　　　　>any (>=any)：比最小的大

　　　　<any (<=any)：比最大的小

案例：查询工资大于最小的经理的员工姓名和工资

select name,sale from employ where sal>any(
select sal from employ where job="manager");

all关键字：

　　　　>all：大于所有

　　　　<all：小于所有

案例：查询薪资比任意一个经理都要高的员工和薪资

select name,sale from employ where sal>all(
select sal from employ where job="manager");

6. 关键字：in和exists

exists返回的是一个布尔类型 True False

案列：显示所有不在部门的员工信息

select*from dept where not exists( select*from employee where deptno=dept.deptno);

in和exists的区别1：执行原理

　　外表 in 内表

　　外表 exists 内表

in：先遍历内表，再索引外表，故外表用大表

exists：先遍历外表，再索引内表，故内表用大表

那么外表大的用in,内表大的用exists，增加效率

区别2：字段限制

in 有字段限制

exists没有字段限制

例如：

select*from tabA where tabA.x in (select x from tabB where y>0 );

https://blog.csdn.net/haluoluo211/article/details/52638366

https://blog.csdn.net/qq_34306360/article/details/79717682

https://www.jb51.net/article/180763.htm

https://blog.csdn.net/qq_26594041/article/details/89438382

以上是 Mysql基础（四）——多表查询内键外键、union、join、子查询、in/exists 的全部内容，来源链接： utcz.com/z/534010.html

Mysql基础（四）——多表查询内键外键、union、join、子查询、in/exists

1. 到底什么是主键，外键？

create table 表B（... ,

CONSTRAINT `外键名称` FOREIGNKEY(`外键字段名称`) REFERENCES 外键A表名(`主键字段名称`) ON DELETE SET NULL ON UPDATE SET NULL

) charset=utf8mb4;

2. 并集（union）

3. 笛卡尔积

4. 内连接 外连接

内连接

左连接：

右连接：

5. 子查询

#案例1：谁的工资比 Abel 高?

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

#案例3：返回公司工资最少的员工的last_name,job_id和salary

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#案例：查询员工编号最小并且工资最高的员工信息

select后面/*仅仅支持标量子查询

#案例：查询每个部门的员工个数

案例：查询工资大于最小的经理的员工姓名和工资

案例：查询薪资比任意一个经理都要高的员工和薪资

6. 关键字：in和exists

其他人也看了：

　　　　　　　　　CONSTRAINT `外键名称` FOREIGNKEY(`外键字段名称`) REFERENCES 外键A表名(`主键字段名称`) ON DELETE SET NULL ON UPDATE SET NULL

　　　　　　　　) charset=utf8mb4;

2. 并集（union）　　　　　　　　

4. 内连接外连接

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资