【MySQL】MySQL進階（外來鍵約束、多表查詢、檢視、備份與恢復）

gonghr發表於2021-10-03
MySql

約束

外來鍵約束

  • 外來鍵約束概念

    • 讓表和表之間產生關係，從而保證資料的準確性！

  • 建表時新增外來鍵約束

    • 為什麼要有外來鍵約束
  -- 建立db2資料庫
  CREATE DATABASE db2;
  -- 使用db2資料庫
  USE db2;
  
  -- 建立user使用者表
  CREATE TABLE USER(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	NAME VARCHAR(20) NOT NULL             -- 姓名
  );
  -- 新增使用者資料
  INSERT INTO USER VALUES (NULL,'張三'),(NULL,'李四'),(NULL,'王五');
  
  -- 建立orderlist訂單表
  CREATE TABLE orderlist(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	number VARCHAR(20) NOT NULL,          -- 訂單編號
  	uid INT                               -- 訂單所屬使用者
  );
  -- 新增訂單資料
  INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
  (NULL,'hm003',2),(NULL,'hm004',2),
  (NULL,'hm005',3),(NULL,'hm006',3);
  
  -- 新增一個訂單，但是沒有所屬使用者。這合理嗎？
  INSERT INTO orderlist VALUES (NULL,'hm007',8);
  -- 刪除王五這個使用者，但是訂單表中王五還有很多個訂單呢。這合理嗎？
  DELETE FROM USER WHERE NAME='王五';
  
  -- 所以我們需要新增外來鍵約束，讓兩張表產生關係
  • 外來鍵約束格式
  CONSTRAINT 外來鍵名 FOREIGN KEY (本表外來鍵列名) REFERENCES 主表名(主表主鍵列名)
  • 建立表新增外來鍵約束
  -- 建立user使用者表
  CREATE TABLE USER(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	NAME VARCHAR(20) NOT NULL             -- 姓名
  );
  -- 新增使用者資料
  INSERT INTO USER VALUES (NULL,'張三'),(NULL,'李四'),(NULL,'王五');
  
  -- 建立orderlist訂單表
  CREATE TABLE orderlist(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	number VARCHAR(20) NOT NULL,          -- 訂單編號
  	uid INT,                              -- 訂單所屬使用者
  	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- 新增外來鍵約束
  );
  -- 新增訂單資料
  INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
  (NULL,'hm003',2),(NULL,'hm004',2),
  (NULL,'hm005',3),(NULL,'hm006',3);
  
  -- 新增一個訂單，但是沒有所屬使用者。無法新增
  INSERT INTO orderlist VALUES (NULL,'hm007',8);
  -- 刪除王五這個使用者，但是訂單表中王五還有很多個訂單呢。無法刪除
  DELETE FROM USER WHERE NAME='王五';
  • 刪除外來鍵約束
-- 標準語法
ALTER TABLE 表名 DROP FOREIGN KEY 外來鍵名;

-- 刪除外來鍵
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  • 建表後新增外來鍵約束
-- 標準語法
ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名 FOREIGN KEY (本表外來鍵列名) REFERENCES 主表名(主鍵列名);

-- 新增外來鍵約束
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

外來鍵的級聯更新和級聯刪除(瞭解)

  • 什麼是級聯更新和級聯刪除
    • 當我想把user使用者表中的某個使用者刪掉，我希望該使用者所有的訂單也隨之被刪除
    • 當我想把user使用者表中的某個使用者id修改，我希望訂單表中該使用者所屬的訂單使用者編號也隨之修改
  • 新增級聯更新和級聯刪除
-- 新增外來鍵約束，同時新增級聯更新  標準語法
ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名 FOREIGN KEY (本表外來鍵列名) REFERENCES 主表名(主鍵列名) ON UPDATE CASCADE;

-- 新增外來鍵約束，同時新增級聯刪除  標準語法
ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名 FOREIGN KEY (本表外來鍵列名) REFERENCES 主表名(主鍵列名) ON DELETE CASCADE;

-- 新增外來鍵約束，同時新增級聯更新和級聯刪除  標準語法
ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名 FOREIGN KEY (本表外來鍵列名) REFERENCES 主表名(主鍵列名) ON UPDATE CASCADE ON DELETE CASCADE;


-- 刪除外來鍵約束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

-- 新增外來鍵約束，同時新增級聯更新和級聯刪除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;

-- 將王五使用者的id修改為5    訂單表中的uid也隨之被修改
UPDATE USER SET id=5 WHERE id=3;

-- 將王五使用者刪除     訂單表中該使用者所有訂單也隨之刪除
DELETE FROM USER WHERE id=5;

多表設計

一對一(瞭解)

  • 分析
    • 人和身份證。一個人只有一個身份證，一個身份證只能對應一個人！
  • 實現原則
    • 在任意一個表建立外來鍵，去關聯另外一個表的主鍵
  • SQL演示
-- 建立db5資料庫
CREATE DATABASE db5;
-- 使用db5資料庫
USE db5;

-- 建立person表
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 新增資料
INSERT INTO person VALUES (NULL,'張三'),(NULL,'李四');

-- 建立card表
CREATE TABLE card(
	id INT PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR(50),
	pid INT UNIQUE,
	CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 新增外來鍵
);
-- 新增資料
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);
  • 圖解

image

一對多

  • 分析
    • 使用者和訂單。一個使用者可以有多個訂單！
    • 商品分類和商品。一個分類下可以有多個商品！
  • 實現原則
    • 在多的一方，建立外來鍵約束，來關聯一的一方主鍵
  • SQL演示
/*
	使用者和訂單
*/
-- 建立user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 新增資料
INSERT INTO USER VALUES (NULL,'張三'),(NULL,'李四');

-- 建立orderlist表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR(20),
	uid INT,
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)  -- 新增外來鍵約束
);
-- 新增資料
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);


/*
	商品分類和商品
*/
-- 建立category表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 新增資料
INSERT INTO category VALUES (NULL,'手機數碼'),(NULL,'電腦辦公');

-- 建立product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	cid INT,
	CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- 新增外來鍵約束
);
-- 新增資料
INSERT INTO product VALUES (NULL,'華為P30',1),(NULL,'小米note3',1),
(NULL,'聯想電腦',2),(NULL,'蘋果電腦',2);
  • 圖解

image

多對多

  • 分析
    • 學生和課程。一個學生可以選擇多個課程，一個課程也可以被多個學生選擇！
  • 實現原則
    • 需要藉助第三張表中間表，中間表至少包含兩個列，這兩個列作為中間表的外來鍵，分別關聯兩張表的主鍵
  • SQL演示
-- 建立student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 新增資料
INSERT INTO student VALUES (NULL,'張三'),(NULL,'李四');

-- 建立course表
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 新增資料
INSERT INTO course VALUES (NULL,'語文'),(NULL,'數學');

-- 建立中間表
CREATE TABLE stu_course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sid INT, -- 用於和student表的id進行外來鍵關聯
	cid INT, -- 用於和course表的id進行外來鍵關聯
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 新增外來鍵約束
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 新增外來鍵約束
);
-- 新增資料
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
  • 圖解

image

多表查詢

多表查詢-資料準備

  • SQL語句
-- 建立db6資料庫
CREATE DATABASE db6;
-- 使用db6資料庫
USE db6;

-- 建立user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 使用者id
	NAME VARCHAR(20),			        -- 使用者姓名
	age INT                             -- 使用者年齡
);
-- 新增資料
INSERT INTO USER VALUES (1,'張三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'趙六',26);


-- 訂單表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 訂單id
	number VARCHAR(30),					-- 訂單編號
	uid INT,    -- 外來鍵欄位
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 新增資料
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);


-- 商品分類表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分類id
	NAME VARCHAR(10)                    -- 商品分類名稱
);
-- 新增資料
INSERT INTO category VALUES (1,'手機數碼');
INSERT INTO category VALUES (2,'電腦辦公');
INSERT INTO category VALUES (3,'菸酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');


-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名稱
	cid INT, -- 外來鍵欄位
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 新增資料
INSERT INTO product VALUES (1,'華為手機',1);
INSERT INTO product VALUES (2,'小米手機',1);
INSERT INTO product VALUES (3,'聯想電腦',2);
INSERT INTO product VALUES (4,'蘋果電腦',2);
INSERT INTO product VALUES (5,'中華香菸',3);
INSERT INTO product VALUES (6,'玉溪香菸',3);
INSERT INTO product VALUES (7,'計生用品',NULL);


-- 中間表
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中間表id
	uid INT, -- 外來鍵欄位。需要和使用者表的主鍵產生關聯
	pid INT, -- 外來鍵欄位。需要和商品表的主鍵產生關聯
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 新增資料
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);
  • 架構器圖解

image

多表查詢-笛卡爾積查詢(瞭解)

  • 有兩張表，獲取這兩個表的所有組合情況
  • 要完成多表查詢，需要消除這些沒有用的資料
  • 多表查詢格式
SELECT
	列名列表
FROM
	表名列表
WHERE
	條件...
  • 笛卡爾積查詢
-- 標準語法
SELECT 列名 FROM 表名1,表名2,...;

-- 查詢user表和orderlist表
SELECT * FROM USER,orderlist;

多表查詢-內連線查詢

  • 查詢原理
    • 內連線查詢的是兩張表有交集的部分資料(有主外來鍵關聯的資料)
  • 顯式內連線
-- 標準語法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 條件;

-- 查詢使用者資訊和對應的訂單資訊
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;

-- 查詢使用者資訊和對應的訂單資訊，起別名
SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;

-- 查詢使用者姓名，年齡。和訂單編號
SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年齡
	o.`number`	-- 訂單編號
FROM
	USER u          -- 使用者表
JOIN
	orderlist o     -- 訂單表
ON 
	u.`id` = o.`uid`;
  • 隱式內連線
-- 標準語法
SELECT 列名 FROM 表名1,表名2 WHERE 條件;

-- 查詢使用者姓名，年齡。和訂單編號
SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年齡
	o.`number`	-- 訂單編號
FROM
	USER u,		-- 使用者表
	orderlist o     -- 訂單表
WHERE
	u.`id`=o.`uid`;

多表查詢-外連線查詢

  • 左外連線

    • 查詢原理
      • 查詢左表的全部資料，和左右兩張表有交集部分的資料
    • 基本演示
  -- 標準語法
  SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 條件;
  
  -- 查詢所有使用者資訊，以及使用者對應的訂單資訊
  SELECT
  	u.`name`,	-- 姓名
  	u.`age`,	-- 年齡
  	o.`number`	-- 訂單編號
  FROM
  	USER u          -- 使用者表
  LEFT OUTER JOIN
  	orderlist o     -- 訂單表
  ON
  	u.`id`=o.`uid`;

  • 右外連線

    • 查詢原理
      • 查詢右表的全部資料，和左右兩張表有交集部分的資料
    • 基本演示
  -- 基本語法
  SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 條件;
  
  -- 查詢所有訂單資訊，以及訂單所屬的使用者資訊
  SELECT
  	u.`name`,	-- 姓名
  	u.`age`,	-- 年齡
  	o.`number`	-- 訂單編號
  FROM
  	USER u          -- 使用者表
  RIGHT OUTER JOIN
  	orderlist o     -- 訂單表
  ON
  	u.`id`=o.`uid`;

多表查詢-子查詢

  • 子查詢介紹

    • 查詢語句中巢狀了查詢語句。我們就將巢狀查詢稱為子查詢！

  • 子查詢-結果是單行單列的

    • 可以作為條件，使用運算子進行判斷！
    • 基本演示
  -- 標準語法
  SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函式(列名) FROM 表名 [WHERE 條件]);
  
  -- 查詢年齡最高的使用者姓名
  SELECT MAX(age) FROM USER;              -- 查詢出最高年齡
  SELECT NAME,age FROM USER WHERE age=26; -- 根據查詢出來的最高年齡，查詢姓名和年齡
  SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);

  • 子查詢-結果是多行單列的

    • 可以作為條件，使用運算子in或not in進行判斷！
    • 基本演示
  -- 標準語法
  SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 條件]); 
  
  -- 查詢張三和李四的訂單資訊
  SELECT id FROM USER WHERE NAME='張三' OR NAME='李四';   -- 查詢張三和李四使用者的id
  SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; -- 根據id查詢訂單
  SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='張三' OR NAME='李四');

  • 子查詢-結果是多行多列的

    • 可以作為一張虛擬表參與查詢！
    • 基本演示
  -- 標準語法
  SELECT 列名 FROM 表名 [別名],(SELECT 列名 FROM 表名 [WHERE 條件]) [別名] [WHERE 條件];
  
  -- 查詢訂單表中id大於4的訂單資訊和所屬使用者資訊
  SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

多表查詢練習

  • 查詢使用者的編號、姓名、年齡。訂單編號
/*
分析：
	使用者的編號、姓名、年齡  user表     訂單編號 orderlist表
	條件：user.id = orderlist.uid
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者姓名
	t1.`age`,	-- 使用者年齡
	t2.`number`	-- 訂單編號
FROM
	USER t1,       -- 使用者表
	orderlist t2   -- 訂單表
WHERE
	t1.`id` = t2.`uid`;
  • 查詢所有的使用者。使用者的編號、姓名、年齡。訂單編號
/*
分析：
	使用者的編號、姓名、年齡 user表     訂單編號 orderlist表
	條件：user.id = orderlist.uid
	查詢所有使用者，使用左外連線
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者姓名
	t1.`age`,	-- 使用者年齡
	t2.`number`	-- 訂單編號
FROM
	USER t1        -- 使用者表
LEFT OUTER JOIN
	orderlist t2   -- 訂單表
ON
	t1.`id` = t2.`uid`;
  • 查詢所有的訂單。使用者的編號、姓名、年齡。訂單編號
/*
分析：
	使用者的編號、姓名、年齡 user表     訂單編號 orderlist表
	條件：user.id = orderlist.uid
	查詢所有訂單，使用右外連線
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者姓名
	t1.`age`,	-- 使用者年齡
	t2.`number`	-- 訂單編號
FROM
	USER t1         -- 使用者表
RIGHT OUTER JOIN
	orderlist t2    -- 訂單表
ON
	t1.`id` = t2.`uid`;
  • 查詢使用者年齡大於23歲的資訊。顯示使用者的編號、姓名、年齡。訂單編號
/*
分析：
	使用者的編號、姓名、年齡 user表     訂單編號 orderlist表
	條件：user.age > 23 AND user.id = orderlist.uid
*/
/*
select
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者姓名
	t1.`age`,	-- 使用者年齡
	t2.`number`	-- 訂單編號
from
	user t1,     -- 使用者表
	orderlist t2 -- 訂單表
where
	t1.`age` > 23
	and
	t1.`id` = t2.`uid`;
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者姓名
	t1.`age`,	-- 使用者年齡
	t2.`number`	-- 訂單編號
FROM
	USER t1       -- 使用者表
LEFT OUTER JOIN
	orderlist t2  -- 訂單表
ON
	t1.`id` = t2.`uid`
WHERE
	t1.`age` > 23;
  • 查詢張三和李四使用者的資訊。顯示使用者的編號、姓名、年齡。訂單編號
/*
分析：
	使用者的編號、姓名、年齡 user表     訂單編號 orderlist表
	條件：user.id = orderlist.uid AND user.name IN ('張三','李四');
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者姓名
	t1.`age`,	-- 使用者年齡
	t2.`number`	-- 訂單編號
FROM
	USER t1,        -- 使用者表
	orderlist t2    -- 訂單表
WHERE
	t1.`id` = t2.`uid`
	AND
	-- (t1.`name` = '張三' OR t1.`name` = '李四');
	t1.`name` IN ('張三','李四');
  • 查詢商品分類的編號、分類名稱。分類下的商品名稱
/*
分析：
	商品分類的編號、分類名稱 category表     分類下的商品名稱 product表
	條件：category.id = product.cid
*/
SELECT
	t1.`id`,	-- 分類編號
	t1.`name`,	-- 分類名稱
	t2.`name`	-- 商品名稱
FROM
	category t1,	-- 商品分類表
	product t2	    -- 商品表
WHERE
	t1.`id` = t2.`cid`;
  • 查詢所有的商品分類。商品分類的編號、分類名稱。分類下的商品名稱
/*
分析：
	商品分類的編號、分類名稱 category表     分類下的商品名稱 product表
	條件：category.id = product.cid
	查詢所有的商品分類，使用左外連線
*/
SELECT
	t1.`id`,	-- 分類編號
	t1.`name`,	-- 分類名稱
	t2.`name`	-- 商品名稱
FROM
	category t1	-- 商品分類表
LEFT OUTER JOIN
	product t2	-- 商品表
ON
	t1.`id` = t2.`cid`;
  • 查詢所有的商品資訊。商品分類的編號、分類名稱。分類下的商品名稱
/*
分析：
	商品分類的編號、分類名稱 category表     分類下的商品名稱 product表
	條件：category.id = product.cid
	查詢所有的商品資訊，使用右外連線
*/
SELECT
	t1.`id`,	-- 分類編號
	t1.`name`,	-- 分類名稱
	t2.`name`	-- 商品名稱
FROM
	category t1	-- 商品分類表
RIGHT OUTER JOIN
	product t2	-- 商品表
ON
	t1.`id` = t2.`cid`;
  • 查詢所有的使用者和所有的商品。顯示使用者的編號、姓名、年齡。商品名稱
/*
分析：
	使用者的編號、姓名、年齡 user表   商品名稱 product表   中間表 us_pro
	條件：us_pro.uid = user.id AND us_pro.pid = product.id
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者名稱稱
	t1.`age`,	-- 使用者年齡
	t2.`name`	-- 商品名稱
FROM
	USER t1,	-- 使用者表
	product t2,	-- 商品表
	us_pro t3	-- 中間表
WHERE
	t3.`uid` = t1.`id`
	AND
	t3.`pid` = t2.`id`;
  • 查詢張三和李四這兩個使用者可以看到的商品。顯示使用者的編號、姓名、年齡。商品名稱
/*
分析：
	使用者的編號、姓名、年齡 user表   商品名稱 product表   中間表 us_pro
	條件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('張三','李四')
*/
SELECT
	t1.`id`,	-- 使用者編號
	t1.`name`,	-- 使用者名稱稱
	t1.`age`,	-- 使用者年齡
	t2.`name`	-- 商品名稱
FROM
	USER t1,	-- 使用者表
	product t2,	-- 商品表
	us_pro t3	-- 中間表
WHERE
	(t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`)
	AND
	-- (t1.`name` = '張三' or t1.`name` = '李四');
	t1.`name` IN ('張三','李四');

多表查詢-自關聯查詢

  • 自關聯查詢介紹
    • 同一張表中有資料關聯。可以多次查詢這同一個表！
  • 自關聯查詢演示
-- 建立員工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 新增資料
INSERT INTO employee VALUES (1001,'孫悟空',1005,9000.00),
(1002,'豬八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龍',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林沖',1009,8100.00),
(1009,'宋江',NULL,16000.00);

-- 查詢所有員工的姓名及其直接上級的姓名，沒有上級的員工也需要查詢
/*
分析：
	員工姓名 employee表        直接上級姓名 employee表
	條件：employee.mgr = employee.id
	查詢左表的全部資料，和左右兩張表交集部分資料，使用左外連線
*/
SELECT
	t1.name,	-- 員工姓名
	t1.mgr,		-- 上級編號
	t2.id,		-- 員工編號
	t2.name     -- 員工姓名
FROM
	employee t1  -- 員工表
LEFT OUTER JOIN
	employee t2  -- 員工表
ON
	t1.mgr = t2.id;

檢視

檢視的概念

  • 檢視是一種虛擬存在的資料表
  • 這個虛擬的表並不在資料庫中實際存在
  • 作用是將一些比較複雜的查詢語句的結果，封裝到一個虛擬表中。後期再有相同複雜查詢時，直接查詢這張虛擬表即可
  • 說白了，檢視就是將一條SELECT查詢語句的結果封裝到了一個虛擬表中，所以我們在建立檢視的時候，工作重心就要放在這條SELECT查詢語句上

檢視的好處

  • 簡單
    • 對於使用檢視的使用者不需要關心表的結構、關聯條件和篩選條件。因為這張虛擬表中儲存的就是已經過濾好條件的結果集
  • 安全
    • 檢視可以設定許可權 , 致使訪問檢視的使用者只能訪問他們被允許查詢的結果集
  • 資料獨立
    • 一旦檢視的結構確定了，可以遮蔽表結構變化對使用者的影響，源表增加列對檢視沒有影響；源表修改列名，則可以通過修改檢視來解決，不會造成對訪問者的影響

檢視資料準備

-- 建立db7資料庫
CREATE DATABASE db7;

-- 使用db7資料庫
USE db7;

-- 建立country表
CREATE TABLE country(
	id INT PRIMARY KEY AUTO_INCREMENT,
	country_name VARCHAR(30)
);
-- 新增資料
INSERT INTO country VALUES (NULL,'中國'),(NULL,'美國'),(NULL,'俄羅斯');

-- 建立city表
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,
	city_name VARCHAR(30),
	cid INT, -- 外來鍵列。關聯country表的主鍵列id
	CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id)
);
-- 新增資料
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'紐約',2),(NULL,'莫斯科',3);

檢視的建立

  • 建立檢視語法
-- 標準語法
CREATE VIEW 檢視名稱 [(列名列表)] AS 查詢語句;
  • 普通多表查詢，查詢城市和所屬國家
-- 普通多表查詢，查詢城市和所屬國家
SELECT
	t1.*,
	t2.country_name
FROM
	city t1,
	country t2
WHERE
	t1.cid = t2.id;
	
-- 經常需要查詢這樣的資料，就可以建立一個檢視
  • 建立檢視基本演示
-- 建立一個檢視。將查詢出來的結果儲存到這張虛擬表中
CREATE
VIEW
	city_country
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
  • 建立檢視並指定列名基本演示
-- 建立一個檢視，指定列名。將查詢出來的結果儲存到這張虛擬表中
CREATE
VIEW
	city_country2 (city_id,city_name,cid,country_name) 
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

檢視的查詢

  • 查詢檢視語法
-- 標準語法
SELECT * FROM 檢視名稱;
  • 查詢檢視基本演示
-- 查詢檢視。查詢這張虛擬表，就等效於查詢城市和所屬國家
SELECT * FROM city_country;

-- 查詢指定列名的檢視
SELECT * FROM city_country2;

-- 查詢所有資料表，檢視也會查詢出來
SHOW TABLES;
  • 查詢檢視建立語法
-- 標準語法
SHOW CREATE VIEW 檢視名稱;
  • 查詢檢視建立語句基本演示
SHOW CREATE VIEW city_country;

檢視的修改

  • 修改檢視表中的資料
-- 標準語法
UPDATE 檢視名稱 SET 列名=值 WHERE 條件;

-- 修改檢視表中的城市名稱北京為北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';

-- 查詢檢視
SELECT * FROM city_country;

-- 查詢city表,北京也修改為了北京市
SELECT * FROM city;

-- 注意：檢視表資料修改，會自動修改源表中的資料
  • 修改檢視表結構
-- 標準語法
ALTER VIEW 檢視名稱 [(列名列表)] AS 查詢語句;

-- 查詢檢視2
SELECT * FROM city_country2;

-- 修改檢視2的列名city_id為id
ALTER
VIEW
	city_country2 (id,city_name,cid,country_name)
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

檢視的刪除

  • 刪除檢視
-- 標準語法
DROP VIEW [IF EXISTS] 檢視名稱;

-- 刪除檢視
DROP VIEW city_country;

-- 刪除檢視2，如果存在則刪除
DROP VIEW IF EXISTS city_country2;

檢視的總結

  • 檢視是一種虛擬存在的資料表
  • 這個虛擬的表並不在資料庫中實際存在
  • 說白了，檢視就是將一條SELECT查詢語句的結果封裝到了一個虛擬表中，所以我們在建立檢視的時候，工作重心就要放在這條SELECT查詢語句上
  • 檢視的好處
    • 簡單
    • 安全
    • 資料獨立

備份與還原

命令列方式

  • 備份

    • 使用SecureCRT工具連線到Linux系統，輸入：mysqldump -u root -p 資料庫名稱 > 檔案儲存路徑
mysqldump -u root -p db1 > /root/mysql/db1.sql
  • 進入檔案儲存路徑，檢視檔案是否存在
cd mysql
ls

  • 恢復

    • 登入mysql資料庫
mysql -u root -p
  • 刪除已經備份的資料庫
drop database db1
  • 重新建立名稱相同的資料庫
create database db1;
  • 使用該資料庫
use db1;
  • 匯入檔案執行：source 備份檔案路徑;
source /root/mysql/db1.sql;

圖形化介面方式

  • 備份

image

image

image

  • 恢復

image

image

image

image

相關文章