MySQL多表操作

（所需的两个表sh_goods与sh_goods_category 的创建在博客中已写过。注：命令窗口中所有符号为英文状态）
一、多表查询
1.联合查询
union是实现联合查询的关键字，all和distinct是联合查询的选项。其中，all表示保留所有的查询结果；distinct是默认值，可以省略，表示去除完全重复的记录。
在sh_goods表中，以联合查询的方式获取category_id为9的商品id、name和price，以及category_id为6的商品id、name和keyword。
select id,name,price from sh_goods where category_id=9 union select id,name,keyword from sh_goods where category_id=6;
（select查询的字段个数必须相同，且联合查询的结果中只保留第一个select语句对应的字段名称，即使union后select查询的字段与第一个select查询的字段表达含义或数据类型不同，MySQL也仅会根据查询字段出现的顺序，对结果进行合并。）
若要对联合查询的记录进行排序等操作，需要使用圆括号“()”包裹每一个select语句，在select语句内或在联合查询的最后添加order by语句。并且若要排序生效，必须在order by后添加limit限定联合查询排序的数量，通常推荐使用大于表记录数的任意值。
例如，以联合查询的方式，对sh_goods表中category_id为3的商品按价格升序排序，其他类型的产品按价格降序排序，查询的商品信息为id、name和price。
(select id,name,price from sh_goods where category_id<>3 order by price desc limit 7)
union (select id,name,price from sh_goods where category_id=3 order by price asc limit 3);

2.连接查询
交叉连接
交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积。例如，商品分类表中3个字段、4条记录，商品表中有5个字段、10条商品信息，那么交叉连接后的笛卡儿积就等于4*10条记录，每条记录中含有3+5个字段。
cross join用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
使用sh_goods_category 表中的每一条记录与sh_goods表中的记录进行连接，最后查询出来的记录数为160（sh_goods_category表中的记录数16乘以sh_goods表中的记录10）。
select c.id cid, c.name cname, g.id gid, g.name gname from sh_goods_category as c cross join sh_goods as g;

内连接
on用于指定内连接的查询条件，在不设置on时，与交叉连接等价，此时可以使用where完成条件的限定，效果与on一样。但由于where是限定已全部查询出来的记录，那么在数据量很大的情况下，此操作会浪费很多性能，所以此处推荐使用on实现内连接的条件匹配。
下面以内连接的方式查询商品表sh_goods和商品分类表sh_goods_category表中对应商品的分类id及name。
select c.id cid, c.name cname, g.id gid, g.name gname from sh_goods g join sh_goods_category c on g.category_id=c.id;

自连接（内连接的一种）
相互连接的表在物理上为同一个表。但逻辑上分为两个表。
例如，要查询“钢笔”所在的分类下有哪些商品，就可以使用自连接查询。
select distinct g1.id,g1.name from sh_goods g1 join sh_goods g2 on g2.name='钢笔’and g2.category_id;
（上述语句中，别名为g1和g2的表在物理上是同一个数据表sh_goods，然后在on的匹配条件中，指定g1表与g2表内商品名为“钢笔”且必须是同分类的记录进行自连接，从而获取sh_goods表中“钢笔”分类下的所有商品。）

左外连接（外连接的一种，或称为“左连接”）
返回连接关键字（left join）左表（主表）中所有的记录，以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配记录时，右表中相关的记录将设为NULL。
select g.id gid,g.name gname,c.id cid,c.name cname from sh_goods g left join sh_goods_category c on g.category_id=c.id and g.score=5;
（左连接查询，即使主表sh_goods中的记录与表sh_goods_category中任何记录都不匹配时，也会再查询结果中保留主表sh_goods中的此条记录，而从表sh_goods_category对应的字段值则为NULL）

右外连接（外连接的一种，或称为“右连接”）
返回连接关键字（right join）右表（主表）中所有的记录，以及左表中符合连接条件的记录。当右表的某行记录在左表中没有匹配记录时，左表中相关的记录将设为NULL。
select g.id gid,g.name gname,c.id cid,c.name cname from sh_goods g right join sh_goods_category c on g.category_id=c.id and g.score=5;

二、子查询
子查询的划分方式有很多种，最常见的是以功能和位置进行划分。按子查询的功能可分为标量子查询、列子查询、行子查询和表子查询；按子查询出现的位置可分为where子查询和from子查询。表子查询属于from子查询。

1.标量子查询
子查询返回的结果是一个数据，即一行一列。
下面利用标量子查询的方式，从sh_goods_category表中获取商品名为“智能手机”的商品分类名称。
select name from sh_goods_category where id=(select category_id from sh_goods where name=‘智能手机’);

2.列子查询
子查询返回结果是一个字段符合条件的所有数据，即一行多列。列子查询利用比较运算函数“in（）”或“not in（）”，判断指定条件是否在子查询语句返回的结果集中，然后根据比较结果完成相关需求的操作。
下面利用列子查询的方式，从sh_goods_category表中获取已添加了商品的商品分类。
select name from sh_goods_category where id in (select distinct category_id from sh_goods);

3.行子查询
当子查询的结果是一条包含多个字段记录（一行多列）时，称为行子查询。
利用行子查询的方式，从sh_goods表中获取价格最高，且评分最低的商品信息（id、name、price、score、content）。
select id,name,price,score,content from sh_goods where (price,score)=(select max(price),min(score)from sh_goods);

4.表子查询
子查询的返回结果用于from数据源，它是一个符合二维表结构的数据，可以是一行一列，一列多行，一行多列或多行多列。
from后的数据源都是表名。因此，当数据源是子查询时必须为其设置别名，同时也是为了将查询结果作为一个表使用，可以进行条件判断、分组、排序yji限量等操作。
下面利用表子查询的方式，从sh_goods表中获取每个商品分类下价格最高的商品信息（id、name、price、category_id）。
select a.id,a.name,a.price,a.category_id from sh_goods a,
(select category_id,max(price) max_price from sh_goods group by category_id)b
where a.category_id=b.category_id and a.price=b.max_price;

三、子查询关键字
1.带exists关键字的子查询
带exists关键字的子查询返回结果只有0和1两个值。其中，0代表不成立，1代表成立。
例如，在sh_goods_category表中存在名称为“厨具”的分类时，将sh_goods表中id等于5的商品修改为电饭煲，价格修改为599，分类修改为“厨具”对应的id。
update sh_goods set name=‘电饭煲’,price=599,
category_id=(select id from sh_goods_category where name=‘厨具’)
where exists(select id from sh_goods_category where name=‘厨具’)
and id=5;
（在上述语句中，sh_goods_category表中不存在厨具时，子查询无结果，则exists（）的返回结果为0.此时update语句的更新条件不满足，不会更新sh_goods表中对应的语句。）

2.带any关键字的子查询
表示给定的判断条件，只要符合any子查询结果的任意一个，就返回1，否则返回0.
从sh_goods_category表中获取分类下商品价格小于500的商品分类名称。
select name from sh_goods_category where id=any(select distinct category_id from sh_goods where price<500);
（若将“=”换成“<>”，就会获取sh_goods_category表中全部的商品分类名称。）

3.带all关键字的子查询
表示给定的判断条件只有全部符合all子查询的结果时。才返回1，否则返回0.
例如，获取sh_goods表中分类id为3下的商品价格比分类为8下的商品价格都低的商品信息（id、name、price、keyword）。
select id,name,price,keyword from sh_goods where category_id=3 and price<all(select distinct price from sh_goods where category_id=8);
（带any、some或all关键字的子查询。不能使用“<=>”比较运算符。另外，若子查询结果与条件匹配时有NULL，那么此条记录不参与匹配。）

四、外键约束
外键指的是在一个表中引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束，从而额保证数据的一致性和完整性。其中，被引用的表称为主表，引用外键的表称为从表。

1.添加外键约束
目前只有InnoDB存储引擎支持外键约束。且建立外键关系的两个数据表的相关字段数据类型必须相似，也就是要求字段的数据类型可以相互转换。
（1）create table时添加外键约束
为从表创建外键约束时，首先要保证数据库中已存在主表，否则程序会报“不能添加外键约束”的错误。
下面为employees表中的dept_id字段添加外键约束，与主表department中的主键id相关联。同时，利用on delete指定从表此关联字段含有数据时，拒绝主表department执行删除操作，利用on update设置主表department执行更新操作时，从表employees中的相关字段也执行更新操作。
在mydb数据库下创建主表
create table mydb.department(
id int unsigned primary key auto_increment comment’部门编号’,
name varchar(50)not null comment’部门名称’)default charset=utf8;
在mydb数据库下创建从表，添加外键约束
create table mydb.employees(
id int unsigned primary key auto_increment comment’员工编号’,
name varchar(120) 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;
（2）alter table时添加外键约束
对于已创建的数据表，则可以通过alter table的方式添加外键约束。
例如，mydb数据库中已有两个数据表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;

2.查看外键约束
desc mydb.employees dept_id;
由执行后操作结果可知，添加了外键约束的dept_id字段的Key（索引）值为MULL，表示非唯一性索引（MULTIPLE KEY），值可以重复。
另外也可以使用show create table查看employees表的详细结构。

3.关联表操作
（1）添加数据
一个具有外键约束的从表在插入数据时，外键字段的值会受主表数据的约束，保证从表插入的数据必须符合约束规范的要求。如从表外键字段不能插入主表中不存在的数据。
例如，主表department中未添加数据时，向employees表中插入一条记录（名称为Tom的用户所属部门编号为3）.
insert into mydb.employees(name,dept_id)values(‘Tom’,3);

从以上结果可知，从表外键字段插入的值必须选取主表中相关联字段已经存在的数据，否则就会报错。
下面为department表添加一条id值为3，分类名为“研发部”的记录，然后再利用上述语句为employees表添加数据。
insert into mydb.department(id,name)values(3,‘研发部’);
insert into mydb.employees(name,dept_id)values(‘Tom’,3);

2.更新数据
对于建立外键约束的关联数据表来说，若对主表进行更新操作，从表将按照其建立外键约束时设置的on update参数自动执行相应的操作。
例如，当参数设置为cascade时，如果主表发生更新，则从表也会对相应的字段进行更新。
下面对具有外键约束关系的employees（从表）和department（主表）进行操作。
update mydb.department set id=1 where name=‘研发部’;
select name,dept_id from mydb.employees;

3.删除数据
对于已建立外键约束的关联数据表来说，若要对主表执行删除操作，从表将按照其建立外键约束时设置的on delete参数自动执行相应的操作。例如，当参数设置为restrict时，如果主表进行删除操作，同时从表中的外键字段有关联记录，就会阻止主表的删除操作。
下面对具有外键约束关系的employees（从表）和department（主表）进行操作。
delete from mydb.employees where dept_id=1;
delete from mydb.department where id=1;
（关联表在删除操作时使用district严格模式，主表中每条记录的删除，都要保证从表中没有没有相关记录的对应数据，这会对开发造成很大的不便。因此，对于添加外键约束的on delete一般都使用set null模式，即删除主表记录时，将从表中对应的记录设置为null，同时要保证从表中对应的外键字段允许为空，否则不允许设置该模式。）

4.删除外键约束
需要删除两个表之间的关联关系时，就要删除外键约束。
解除员工表employees与部门表department之间的外键约束为例进行演示。
alter table mydb.employees drop foreign key fk_id;
在删除employees表的外键约束后，下面利用desc查询employees表中删除了外键约束的字段信息。
desc mydb.employees dept_id;
从上述执行结果可知，在删除dept_id的外键约束后，dept_id的Key值依然为MULL，这是由于删除外键约束时并不会自动删除系统创建的普通索引，此时，可以通过show create table查看。
show create table mydb.employees\G
若要在删除外键约束后，同时删除系统为外键创建的普通索引，则需要通过手动删除索引的方式完成。
alter table mydb.employees drop key fk_id;
完成上述操作后，可再次利用desc查看已删除的外键字段的Key值为空，show create table 时表中的外键约束以及普通索引全部已删除。