未整理1 - 3.SQL多表操作 - 《MySQL》

外键约束
连接查询
子查询
- 1.作为查询条件

外键约束

外键指的是在从表中与主表的主键对应的那个字段
使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
从表外键类型必须与主表主键类型一致否则创建失败
添加数据时, 应该先添加主表中的数据
删除数据时,应该先删除从表中的数据

1.创建外键约束

新建表时添加外键

-- 语法：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
CREATE TABLE table2(
  id INT PRIMARY KEY AUTO_INCREMENT,
  tid INT,
  FOREIGN KEY(tid) REFERENCES table1(id)
);

注：外间约束名称可以省略由系统自动生成

已有表添加外键

-- 语法：ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
ALTER TABLE table2 ADD FOREIGN KEY (tid) REFERENCES table1(id);

2.删除外键约束

删除table2表的外键table4_ibfk_1

-- 语法：ALTER TABLE 从表 DROP FOREIGN KEY 外键约束名称
ALTER TABLE table2 DROP FOREIGN KEY table4_ibfk_1;

3.级联删除操作

如果想实现删除主表数据的同时，也删除掉从表数据，可以使用级联删除操作

关键字：ON DELETE CASCADE

CREATE TABLE table2(
  id INT PRIMARY KEY AUTO_INCREMENT,
  tid INT,
  FOREIGN KEY(tid) REFERENCES table1(id)
  ON DELETE CASCADE
);

连接查询

1.准备数据

CREATE DATABASE db3_2 CHARACTER SET utf8;
-- 分类表 (一方 主表) 
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
-- 商品表 (多方 从表) 
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),
  category_id VARCHAR(32),
   -- 添加外键约束
  FOREIGN KEY (category_id) REFERENCES category (cid)
);
-- 分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电'); 
INSERT INTO category(cid,cname) VALUES('c002','鞋服'); 
INSERT INTO category(cid,cname) VALUES('c003','化妆品'); 
INSERT INTO category(cid,cname) VALUES('c004','汽车');
-- 商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视 机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空 调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰 箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球 鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动 裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T 恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋 衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙 水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大 宝',200,'1','c003');

2.内连接查询

隐式内连接：from后面写多个表名使用where指定连接条件这种连接方式是隐式内连接

-- 语法：SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
SELECT * FROM products,category WHERE category_id = cid;

显式内连接：使用 inner join …on 这种方式, 就是显式内连接

-- 语法：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- INNER 可以省略
SELECT * FROM products p JOIN category c ON p.category_id = c.cid;

内连接: inner join , 只获取两张表中交集部分的数据.

3.外连接查询

左外连接：

-- 语法：SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;

右外连接：

-- 语法：SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分

右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

子查询

一条select 查询语句的结果, 作为另一条 select 语句的一部分
子查询必须放在小括号中
子查询一般作为父查询的查询条件使用

1.作为查询条件

语法格式

SELECT 查询字段 FROM 表 WHERE 字段=(子查询);

示例 ```sql — 1.先查询出最高价格 SELECT MAX(price) FROM products;

— 2.将最高价格作为条件,获取商品信息 SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);


<a name="7516917c"></a>
#### 2.作为一张表
- 
语法格式
```sql
SELECT 查询字段 FROM (子查询)表别名 WHERE 条件;

示例 ```sql — 1.先查询分类表的数据 SELECT * FROM category;

— 2.将上面的查询语句作为一张表使用 SELECT p.pname,p.price,c.cname FROM products p — 子查询作为一张表使用时要起别名才能访问表中字段 INNER JOIN (SELECT * FROM category) c ON p.category_id = c.cid WHERE p.price > 500;


<a name="128c7f95"></a>
#### 3.子查询结果单列
- 
语法格式
```sql
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

示例 ```sql — 1.先查询价格小于2000 的商品的,分类ID SELECT DISTINCT category_id FROM products WHERE price < 2000;

— 2.在根据分类的id信息,查询分类名称 SELECT * FROM category WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000); ```

更新时间：{docsify-updated}