Mysql——多表操作

---

一、多表查询

 1.1 联合查询

联合查询：是多表查询的一种方式，在保证多个select语句的查询字段数相同的情况下，合并多个查询结果。常用在分表操作中。
基本语法

select ...
union[all|distinct] select...
[union[all|distinct] select...];

union是实现联合查询的关键字
all和distinct是联合查询的选项
distinct是默认值，可以省略，表示去除完全重复的记录

举个例子
在goods表中，以联合查询的方式获取category_id为9的商品id、name和price，以及category_id为6的商品id、name和keyword。

select id,name,pprice from goods where category_id=9
select id,name,keyword from goods where category_id=6;

若要对联合查询的记录进行排序等操作，需要用（）包裹每一个select语句，在select语句内或者在联合查询的最后添加order by语句。若要排序生效，必须在order by 后添加limit限定联合查询排序的数量
举个例子
以联合方式查询，对goods表中category_id为3的商品按价格升序排序，其他类型的产品按价格降序排序，查询的商品信息为id、name和price

(select id,name.price from goods where category_id<>3 order by price desc limit 7) union 
(select id,name,price from goods where category_id=3 order by price asc limit 3);

 1.2 连接查询

 1.2.0准备

建立部门表

CREATE TABLE dept(
	deptno	INT PRIMARY KEY,
	dname	VARCHAR(20) UNIQUE
);

建立员工表

 CREATE TABLE emp(
empno   INT PRIMARY KEY,
ename   VARCHAR(20) UNIQUE NOT NULL,
job     VARCHAR(20) NOT NULL,
mgr     INT ,
sal     DECIMAL(7,2),
comm    DECIMAL(7,2),
deptno  INT  
) ;

插入部门表数据

INSERT INTO dept 
VALUES
(10, '总裁办'),
(20, '研究院'),
(30, '销售部'),
(40, '运营部');

插入员工表数据

insert into emp values
(1,'张三','销售经理',110,5000,600,20),
(2,'张四','销售经理',110,4000,600,30),
(3,'张五','前台',111,3000,500,30),
(4,'张六','前台',113,3500,550,50),
(5,'张七','前台',112,4000,600,50);

 1.2.1 交叉连接cross join

交叉连接产生的结果是笛卡尔积，没有实际应用的意义。
基本语法格式：

select 查询字段 from 表1 cross join 表2;
或
select 查询字段 from 表1，表2;

cross join用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。

 1.2.2 内连接inner join

内连接根据匹配条件返回第一个表和第二个表所有匹配成功的记录。
基本语法格式：

select 查询字段 from 表1
[inner] join 表2 on 匹配条件;

on用于指定内连接的查询条件

举个例子
查询已经分配了部门（部门号不为NULL）的员工的信息，员工信息只需要显示员工姓名和对应部门的名称

SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;

 1.2.3 左外连接

用于返回连接关键字（left join）左表中所有的记录，以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配记录时，右表中相关记录将设为NULL。
基本语法格式：

select 查询字段
from 表1 left [outer] join 表2 on 匹配条件;

关键字left[outer]join左边的表称为左表（主表），右边的表叫右表（从表)。outer在查询时可以省略。
举个例子
查询所有部门名称及部门对应员工的姓名。因为需要查询出所有部门的名称，查询时可以使用左连接查询，将部门表作为查询中的左表

SELECT d.dname,e.ename FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;

 1.2.4 右外连接

用于返回连接关键字（right join）右表中所有的记录，以及左表中符合连接条件的记录。当右表的某行记录在左表中没有匹配记录时，左表中相关记录将设为NULL。

select 查询字段
from 表1 right [outer] join 表2 on 匹配条件;

举个例子
查询所有员工姓名及对应部门的名称，没有分配部门的员工也需要查询出来。因为需要查询出所有员工的名称，查询时可以使用右连接查询，将员工表作为查询中的右表

SELECT d.dname,e.ename FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;

多学一招（并不常用）
在连接查询时，若数据表连接的字段同名，则连接时的匹配条件可以使用using代替on。
基本语法格式：

select 查询字段 from 表1 [cross|inner|left|right] join 表2 using(同名的字段连接列表);

二、子查询

 2.1 什么是子查询？

在含有子查询的语句中，子查询必须书写在圆括号内。sql语句首先会执行子查询中的语句，然后再将返回的结果作为外层sql的过滤条件，执行顺序是从最里层的子查询开始执行。

 2.2 子查询的分类

 2.2.1 标量子查询

子查询返回的结果是一个数据，即一行一列。
基本语法格式：

where 条件判断 {=|<>} (select 字段名 from 数据源 [where][group by][having][order by][limit]);

  2.2.2 列子查询

子查询返回的结果是一个字符段符合条件的所有数据，即一列多行。
基本语法格式：

where 条件判断 {in|not in} (select 字段名 from 数据源 [where][group by][having][order by][limit]);

 2.2.3 行子查询

子查询的结果是一条包含多个字段的记录。
基本语法格式：

where (指定字段名1，指定字段名2)=(select 字段名1，字段名2···from 数据源 [where][group by][having][order by][limit]);

 2.2.4表子查询

子查询的返回结果用于from数据源。
基本语法格式：

select 字段列表 from (select语句) [as] 别名 [where][group by][having][order by][limit];
数据源后的都是表名;

 2.3 子查询关键字

 2.3.1 带exists关键字的子查询

其返回结果只有2种情况：0和1；0代表不成立，1代表成立。

EXISTS关键字用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回TRUE，否则返回FALSE。

使用EXISTS关键字结合子查询进行查询时，会先执行外层查询语句，再根据EXISTS关键字后面子查询的查询结果，判断是否保留外层语句查询出的记录，EXISTS的判断结果为TRUE时,保留对应的记录，否则去除记录。

基本语法格式：

where exists(子查询语句);

举个例子
查询工资大于2900的员工所在的部门信息。

select *from dept 
where exists(select * from emp where emp.deptno=dept.deptno and emp.sal>2900);

 2.3.2带any关键字的子查询

表示给定的判断条件，只要符合any子查询结果中的任意一个，就返回1，否则返回0；

ANY关键字表示“任意一个”的意思，必须和比较操作符一起使用，例如ANY和>结合起来使用，表示大于任意一个。

ANY关键字结合子查询时，表示子查询的查询结果集中的任一查询结果，例如“值1>ANY(子查询)”，比较值1是否大于子查询返回的结果集中任意一个结果。

基本语法格式：

where 表达式 比较运算符 any (子查询语句);

举个例子
查询部门编号为20的员工信息，要求查询到的员工信息中，工资都高于部门编号为30的部门中的最低工资。

SELECT * FROM emp WHERE deptno=20 AND
 sal>ANY(SELECT sal FROM emp WHERE deptno=30);

 2.3.3带all关键字的子查询

表示给定的判断条件只有全部符合all子查询的结果时，才返回1，否则返回0；

ANY关键字表示“所有”的意思，ALL关键字结合子查询时，表示子查询的所有查询集中是所有结果，例如“值1>ALL(子查询)”，比较值1是否大于子查询返回的结果集中所有结果。

基本语法格式：

where 表达式 比较运算符 all(子查询语句);

举个例子
查询部门编号为20的员工信息，要求查询到的员工信息中，工资都高于部门编号为30的部门中的最高工资

SELECT * FROM emp WHERE deptno=20 AND 
sal>ALL(SELECT sal FROM emp  WHERE deptno=30);

注意：

带any、some、all关键字的子查询，不能使用"<=>"比较运算符；
若子查询结果与条件匹配时有NULL，那此条记录不参与匹配；

 2.3.4带IN关键字的子查询

举个例子
查询工资大于2900的员工所属部门。

SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM emp WHERE sal>2900);

查询工资小于2900的员工所在的部门信息

SELECT * FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp WHERE sal>2900);

 2.3.5比较运算符结合子查询

举个例子
查询与张三职位相同的员工信息。

SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='张三')
 AND ename !='张三';

三、外键约束

 3.1 添加外键约束

 3.3.1添加外键约束

   在mydb数据库中，以员工表employees和部门表department为例，讲解如何在create table和alter table时添加外键约束的两种方式。

基本语法格式：

ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) 
REFERENCES 主表名(主键字段名);

上述语法格式中，ADD CONSTRAINT表示添加约束；外键名称是可选参数，用来指定添加的外键约束的名称；FOREIGN KEY表示外键约束；使用REFERENCES指定创建的外键引用哪个表的主键。

 （1）创建数据表（create table）时添加外键约束
   在从表添加外键约束时，首先要保证数据库中已经存在主表，否则会报错“不能添加外键约束”的错误。
   举个例子

#(1)在mydb数据库下创建主表
create table mydb.department(
	id int unsigned primary key auto_increment comment "部门编号",
	name varchar(255) not null comment "部门名称"
	)default charset=utf98;
#(2)在mydb数据库下创建从表，添加外键约束
create table mydb.employees(
	id int unsigned primary key auto_increment comment "员工编号",
	name varchar(255) not null comment "员工姓名",
	dept_id int unsigned not null comment "部门编号",
	constraint FK_ID foreign key(dept_id) references department(id)#设置外键约束
	on delete restrict on update cascade
	)default charset=utf8;

注意定义外键约束名称（如FK_ID）时，不可以加单引号或者双引号，constraint ‘FK_ID’ 或constraint “FK_ID”均会报错。

 （2）修改数据表（alter table）时添加外键约束
对于已经创建的数据表，使用alter table添加外键约束

举个例子
若mydb数据库中已经有2个数据表department和employees，employees表在创建时未添加外键约束，此时可以通过以下alter table方式实现

alter table mydb.employees
add constraint FK_ID foreign key(dept_id) references department(id)
on delete restrict on update cascade;

 3.3.2查看外键约束

可以使用desc查看数据表employees中添加了外键约束的字段信息，如下：

desc mydb.employees dept_id;
#dept_id 为字段名

使用show create table 查看employees表的详细结构，如下：

show create table mydb.employees\G;

 3.2 关联表操作

 3.2.1添加数据

举个例子
为department中添加id为3，分类名为“研发部”的记录，具体sql语句如下：

insert into mydb.department(id,name) values(3,'研发部');

注意
 若提前使用use mydb，则可以不用写mydb.depatment，可直接写department；

 3.2.2更新数据

若对主表进行更新操作，从表将按照其建立外键约束设置的on update参数自动执行相应的操作。
 举个例子

update mydb.department srt id=1 where name="研发部";

从上可知，仅将主表department中名为“研发部”的id修改为1后，employees中的相关用户的外键dept_id也同时被修改为1.

 3.2.3删除数据

若要删除具有on delete restrict 约束关系的主表记录时，一定要先删除从表中对应的数据，然后再删除主表中的数据。

delete from mydb.employees where dept_id =1;
delete from mydb.department where id=1;

 3.3 删除外键约束

需要解除两个表之间的关联关系时，就要删除外键约束，基本语法格式如下：

alter table 表名 drop foreign key 外键名;

若要在删除外键约束后，同时删除系统为外键创建的普通索引，则需要通过手动删除索引的方式完成，具体sql语句如下：

alter table mydb.employees drop foreign key FK_ID;

完成上述操作后，可以使用desc查看已删除的外键字段的key值为空，show create table时表中的外键约束以及普通索引全部已删除。