MySQL入门（9）—— 基于datagrip的SQL组合查询及多表操作

章节所用的数据我已都上传资源，自取练习

一、组合查询

UNION 操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
默认：多个 SELECT 语句会删除重复的数据

（1）格式：

 SELECT 列名, 列名,...
 FROM tables [WHERE 条件] 
 UNION [ ALL | DISTINCT] 
 SELECT 列名, 列名,...
 FROM tables [WHERE 条件];

解析：
DISTINCT：可选，删除结果集中重复的数据。
默认情况下 UNION 操作符已经删除了重复数据，所以DISTINCT 修饰符对结果没什么影响。
ALL： 可选，返回所有结果集，包含重复数据。

（2）案例

假设现在有两张表：customers、customers2，具有相同的表结构，里面各自存放着不同的数据，customers表里存放的是一月份的顾客数据，customers2表里存放的是二月份的顾客数据，现在想查询一二月份所有顾客的数据。

用旧表创建新表

复制表结构及数据到新表
 CREATE TABLE 新表名 SELECT * FROM 旧表名;
 
仅复制表结构，不复制数据
 CREATE TABLE 新表名 SELECT * FROM 旧表名  WHERE 1=2;

仅复制数据
 INSERT INTO 新表名(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表名; 

复制表结构
 CREATE TABLE 新表名 LIKE 旧表名;

（3）分析过程

# 准备数据：先复制一张表 customers2
 CREATE TABLE  customers2  SELECT * FROM customers;
 
# 准备数据：在customers2 中修改几条数据
 UPDATE customers2  SET cust_name = '杨过' WHERE cust_id = 10001 ;
 UPDATE customers2  SET cust_name = '小龙女' WHERE cust_id = 10002;
 UPDATE customers2  SET cust_name = '金庸' WHERE cust_id = 10003 ;
 
# 查询 customers 表中的顾客名称
 SELECT cust_name FROM customers;
 
# 查询 customers2 表中的顾客名称
 SELECT cust_name FROM customers2;
 
# 查询 customers 和 customers2 表中的顾客名称
 SELECT cust_name FROM customers
 UNION
 SELECT cust_name FROM customers2;
 
# 结果变成了8条数据，是因为两张表中有两条重复数据，被自动去重了，如果不想去重，想全部显示
 SELECT cust_name FROM customers
 UNION ALL
 SELECT cust_name FROM customers2;

查看建表语句

（4）注意事项

1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔，因此，如果组合4条SELECT语句，将要使用3个UNION关键字

2、UNION中的每个查询必须包含相同的列、表达式或聚集函数，各个列不需要以相同的次序列出，但出于业务需求，通常会以相同的顺序列出

3、列数据类型必须兼容：类型不必完全相同，但必须是数据库可以隐含地转换的类型，例如，不同的数值类型或不同的日期类型

二、 多表操作

1、外键

现在有两张表“顾客表”和“订单表”，为了表明订单属于哪个顾客，通常情况下，将在订单表上添加一列，用于存放cust_id的信息，此列称为：外键。

外键（foreign key）：
外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

主表（父表）：
对于两个具有关联关系的表而言，相关联字段中的主键所在的那个表即是主表。

从表（子表）：
对于两个具有关联关系的表而言，相关联字段中的外键所在的那个表即是从表。

外键特点：
1、从表外键的值是对主表主键的引用。
2、从表外键类型，必须与主表主键类型一致。

外键的作用：
1、保证数据引用的完整性、一致性。
2、要考虑向从表添加数据时，外键字段必须是主表主键的引用，否则添加失败。
3、要考虑从主表中删除数据时，主键并未被从表引用，否则删除失败。

语法：

1、添加外键

 alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名)  references 主表 (主表的主键);

外键名称用于删除外键约束的，一般建议“_fk”结尾，也可以在建表时添加外键约束

2、删除外键

 alter table 从表 drop foreign key 外键名称

2、表关系

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要顾客表(customers)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来将在单表的基础上，一起学习多表方面的知识。

一对一关系

在实际的开发中不多因，为一对一可以创建成一张表

常见实例：
商品表和商品描述表

两种建表原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。
外键是主键：主表的主键和从表的外键，形成主外键关系。

一对多关系

常见实例：
客户和订单，分类和商品，部门和员工，省份和城市

一对多建表原则：
在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键

多对多关系

常见实例：
商品和订单，学生和课程，用户和角色

多对多关系建表原则：
需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

查看表关系

选择所要查看的数据库，右击选择【图】，选择第一个【显示可视化】

即可查看缩略图：（有外键的话图上会显示）

3、实操

（1）练习1

 # 添加外键
 ALTER TABLE orders ADD CONSTRAINT orders_customers_fk FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
 # 删除外键
 ALTER TABLE orders DROP FOREIGN KEY orders_customers_fk;
 
 # 先向主表添加数据
 INSERT INTO customers (cust_id,cust_name) VALUES (666,'王老五');-- 成功
 # 再向从表添加数据
 INSERT INTO orders(order_date, cust_id) VALUES (now(),666);-- 成功
 
 # 先向从表添加数据（从表的外键信息必须在主表中有对应）
 INSERT INTO orders (order_date,cust_id) VALUES (now(),111);-- 失败
 # 主表删除数据（从表还在使用外键，无法从主表中直接删除）
 DELETE FROM customers WHERE cust_id=666;-- 失败
 # 正常删除操作步骤
 DELETE FROM orders WHERE cust_id=666;    #先删除从表中正在使用的
 DELETE FROM customers WHERE cust_id=666; #再删除主表中的

总结来说， 添加内容是先主表后从表，删除内容是先从表后主表。

（2）练习2

1、添加外键

 ALTER TABLE orderitems  ADD CONSTRAINT orderiterms_orders_fk FOREIGN KEY (order_num) REFERENCES orders (order_num);
 ALTER TABLE orderitems ADD CONSTRAINT orderiterms_products_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id);

2、向订单表插入数据

 INSERT INTO orders (order_num, order_date, cust_id) VALUES (101,now(),666);

3、向商品表插入数据

 INSERT INTO products (prod_id,prod_name,vend_id,prod_price) VALUES (101,'大刀',1002,100);

4、向中间表插入一条数据（数据存在）

 INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (101,1,101,1,100);

5、向中间表插入一条数据（数据不存在） 执行失败

 INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (101,1,102,1,100);-- 产品表不存在102

 INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (102,1,101,1,100);-- 订单表不存在102

6、删除商品表 执行失败

DELETE FROM products WHERE prod_id=101;

（2）实战1：省和市：一对多关系

方案1：多张表，一对多

# 创建省份表
 create table province(
   pid int PRIMARY KEY,
   pname varchar(32), -- 省份名称
   description varchar(100) -- 描述
 );
 
# 创建城市表
 create table city (
   cid int PRIMARY KEY,
   cname varchar(32), -- 城市名称
   description varchar(100), -- 描述
   province_id int,
   CONSTRAINT city_province_fk foreign key(province_id) references province(pid)
 );

方案2：一张表，自关联一对多

 create table area (
   id int PRIMARY key AUTO_INCREMENT,
   `name` varchar(32),
   description varchar(100),
   parent_id int,
   CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)
 );
 
 INSERT into area values(null, '辽宁省', '这是一个省份', null);
 INSERT into area values(null, '大连市', '这是一个城市', 1);
 INSERT into area values(null, '沈阳市', '这是一个城市', 1);
 INSERT into area values(null, '河北省', '这是一个省份', null);
 INSERT into area values(null, '石家庄市', '这是一个城市', 4);
 INSERT into area values(null, '保定市', '这是一个城市', 4);

（3）实战2：用户和角色：多对多关系

 -- 用户表
 create table `user` (
   uid varchar(32) PRIMARY KEY,
   username varchar(32),
   `password` varchar(32)
 );
 
 -- 角色表
 create table role (
   rid varchar(32) PRIMARY KEY,
   rname varchar(32)
 );
 
 -- 中间表
 create table user_role(
   user_id varchar(32),
   role_id varchar(32),
   CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
   CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES `user`(uid),
   CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)
 );

特别提醒：现在这种创建外键的方式已经不提倡，甚至被禁止了，因为在维护数据时，限制条件太多，效率较低。关联关系通过SQL语句来实现。