【MySQL】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

Tags:

VirMach 便宜 VPS