外鍵約束和多表查詢
一、外鍵是什么
圖解
知識點
外鍵: 多個表之間的關聯欄位
特點1: 從表外鍵的值是對主表主鍵的參考，
特點2: 從表外鍵型別，必須與主表主鍵型別一致，
主從表: 外鍵欄位所在的表是從表,依賴欄位對應的表是主表
多表關系: 一對一 一對多 多對多
一對多關系: 主表是一方 從表是多方
外鍵約束
外鍵約束: FOREIGN KEY
外鍵約束作用:
保證了資料的準確性: 限制了從表在插入資料的時候,不能插入主表不存在的資料
保證了資料的完整性: 限制了主表在洗掉資料的時候,不能洗掉從表已經參考的資料
如果添加外鍵約束:
在建從表時候添加(建議): constraint [外鍵名稱] foreign key(外鍵欄位名) references 主表(主鍵)
# 拓展存盤引擎
# 查看所有存盤引擎
show ENGINES;
# 查看默認存盤引擎
show variables like '%default_storage_engine%';
# 注意: innodb支持外鍵而myisam不支持外鍵
# 如果要使用外鍵: 你的mysql存盤引擎是myisam需要修改成innodb
#資料準備
# 分類表
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY, # 分類id
cname VARCHAR(100) #分類名稱
);
# 商品表
CREATE TABLE products
(
pid VARCHAR(100) PRIMARY KEY , # 商品id
pname VARCHAR(40) ,# 商品名稱
price DOUBLE ,# 商品價格
category_id VARCHAR(32),# 分類id
CONSTRAINT FOREIGN KEY(category_id) REFERENCES category(cid) # 添加外鍵約束
);
# 查看表建表陳述句
show create table category;
show create table products;
# 插入測驗資料
#1 向分類表中添加資料
INSERT INTO category (cid ,cname) VALUES('c001','服裝');
#2 向商品表添加普通資料,沒有外鍵資料，默認為null
INSERT INTO products (pid,pname) VALUES('p001','商品名稱');
#3 向商品表添加普通資料，含有外鍵資訊(category表中存在這條資料)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名稱2','c001');
# 演示外鍵約束的限制作用
# 限制從表插入資料的時候不能插入主表不存在的資料,否則報錯
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名稱2','c999'); # 報錯
# 限制主表不能刪洗掉從表已經參考的資料,否則報錯
DELETE FROM category WHERE cid = 'c001';# 報錯
多表查詢
圖解
資料準備
# 創建hero表
CREATE TABLE hero(
hid INT PRIMARY KEY,# 英雄id
hname VARCHAR(255),# 英雄名稱
kongfu_id INT # 對應功夫id
);
# 創建kongfu表
CREATE TABLE kongfu
(
kid INT PRIMARY KEY, # 功夫id
kname VARCHAR(255) # 功夫名
);
# 插入hero資料
INSERT INTO hero VALUES(1, '鳩摩智', 9),(3, '喬峰', 1),(4, '虛竹', 4),(5, '段譽', 12);
# 插入kongfu資料
INSERT INTO kongfu VALUES(1, '降龍十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');
交叉連接
交叉連接關鍵字: cross join
注意: 交叉連接會產生笛卡爾積(離散數學里面學過)
格式: select 欄位名 from 左表 cross join 右表 ; 注意:以后一般不用
內連接
知識點
內連接關鍵字:表1 [inner] join 表2 on 條件
顯式內連接格式:select 欄位名 from 左表 inner join 右表 on 左右關聯條件;
隱式內連接格式:select 欄位名 from 左表,右表 where 左右關聯條件;
示例
# 需求: 查找英雄中有對應功夫的資訊
# 顯式格式: select 欄位名 from 左表 inner join 右表 on 左右表關聯條件
SELECT * FROM hero inner join kongfu on kongfu_id = kid;
# 隱式格式: select 欄位名 from 左表 , 右表 where 左右表關聯條件
SELECT * FROM hero,kongfu WHERE kongfu_id = kid;
外連接
知識點
左外連接關鍵字:表1 left [outer] join 表2 on 條件
右外連接關鍵字:表1 right [outer] join 表2 on 條件
注意:outer可以省略
左外連接格式: select 欄位名 from 左表 left outer join 右表 on 左右表關聯條件 ;
右外連接格式: select 欄位名 from 左表 right outer join 右表 on 左右表關聯條件 ;
示例
# 需求: 查找所有英雄對應功夫資訊,即使沒有功夫也要展示資訊
# 左外連接格式: select 欄位名 from 左表 left outer join 右表 on 左右表關聯條件 ;
# 左連接效果: 以左表為主,左表資料都展示,右表只展示和左表關聯上的資料,其他內容null補全
select hname,kname from hero left outer join kongfu on hero.kongfu_id=kongfu.kid;
select hname,kname from hero left join kongfu on hero.kongfu_id=kongfu.kid;
# 右外連接格式: select 欄位名 from 左表 right outer join 右表 on 左右表關聯條件 ;
select hname,kname from kongfu right outer join hero on hero.kongfu_id=kongfu.kid;
select hname,kname from kongfu right join hero on hero.kongfu_id=kongfu.kid;
內外連接練習
準備資料
# 創建分類表
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), #是否上架標記為：1表示上架、0表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk 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','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',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','花花公子',440,'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,'0','c003');
示例
# 1.查詢哪些分類的商品已經上架,要求展示分類名稱
# 注意: 如果表名稱較長,可以使用別名,as關鍵字可以省略
select distinct cname from category c join products p on c.cid = p.category_id where flag = '1';
# 2.查詢所有分類商品的個數,要求展示分類名稱
# 注意: 可以利用聚合函式(欄位名)的忽略null的特點
select cname,count(category_id) from category c left join products p ON c.cid = p.category_id GROUP BY cname;
子查詢
知識點
子查詢核心思路: 一個select陳述句的結果作為另一個select陳述句的部分(表或者條件)
子查詢作為表: select 欄位名 from (子查詢陳述句) as 別名;
子查詢作為條件: select 欄位名 from 表名 where ... (子查詢陳述句);
示例
# 一個陳述句作為另外一個陳述句的一部分(表或者條件)
# 演示子查詢作為條件
# 1.查詢哪些分類的商品已經上架,要求展示分類名稱
SELECT DISTINCT
category_id
FROM
products
WHERE
flag = '1'; #先找已經上架的商品的id
SELECT
cname
FROM
category
WHERE
cid IN (SELECT DISTINCT category_id FROM products WHERE flag = '1');
#將上條查詢作為條件，進行子查詢
# 2.查詢“化妝品”分類上架商品詳情
SELECT
cid
FROM
category
WHERE
cname = '化妝品'; #先查詢化妝品的商品id是什么
SELECT *
FROM
products
WHERE
flag = '1'
AND category_id = (SELECT cid FROM category WHERE cname = '化妝品');
#將上條查詢作為條件，進行子查詢
# 3.查詢“化妝品”和“家電”兩個分類上架商品詳情
SELECT
cid
FROM
category
WHERE
cname IN ('化妝品', '家電');#先查詢化妝品和家電的商品id是什么
SELECT *
FROM
products
WHERE
flag = '1'
AND category_id IN (SELECT cid FROM category WHERE cname IN ('化妝品', '家電'));
#將上條查詢作為條件，進行子查詢
# 演示子查詢作為表
# 1.查詢“化妝品”分類上架商品詳情，要求包含分類名稱
# 顯式內連接
SELECT *
FROM
category
WHERE
cname = '化妝品'; #查詢化妝品分類下的商品資訊，作為表
SELECT *
FROM
products p
JOIN (SELECT * FROM category WHERE cname = '化妝品') t1 ON p.category_id = t1.cid
WHERE
flag = '1'; #將上表與商品表連接起來，之后進行查詢
自連接
知識點
自連接作為一種特例，可以將一個表與它自身進行連接，稱為自連接，
語法: 自連接語法和內外連接的語法一樣，只不過換成了只在同一張表上面操作
特點: 特殊的地方就是左表和右表是同一張表,只是起了不同的別名
示例
# 假設現在有一個區域表areas，里面是我國區域階級，如下圖所示北京市下屬有幾個區，每個區的pid是其上級區域
# 分析: 省市區三級都在一個表中,那么就可以使用自連接
# 需求1: 查詢河北省所有的城市
# 自連接方式 思路: 通過起別名把一個表(區域表)變成兩個表(城市表,省級表)使用
#自連接，將表復制為兩個表，一個取名city，一個起名province，進行關聯，查找
SELECT *
FROM
areas city
JOIN areas province ON city.pid = province.id
WHERE
province.title = '河北省';
#查邯鄲市下的區縣
SELECT *
FROM
areas district
JOIN areas city on district.pid = city.id
where city.title='邯鄲市';
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/552292.html
標籤：其他
下一篇：返回列表