又是周六了-MySQL特訓

2019-11-14 14:40:55

字體：大中小

來源：轉載

供稿：網友

又是周六，又是磨蹭個一上午~午飯后開始吧

1、MySQL

-----子查詢與連接（三）-----

----使用INSERT...SELECT插入記錄

--數據庫內容的英文版本

由于我的WAMP中的MYSQL控制臺顯示中文無力，在更改過程中心力交瘁，遂改變戰術，把數據改為了全英文的，現分享出來

-- 創建數據表

CREATE TABLE IF NOT EXISTS goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);

-- 寫入記錄

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6Inches Laptop','Laptop','ASUS','3399',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0Inches Laptop','Laptop','Lenovo','4899',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6Inches Gamebook','Gamebook','ThunderGod','8499',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6Inches Laptop','Laptop','ASUS','2799',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5InchesUltrabook','Ultrabook','Lenovo','4999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3Inches Ultrabook','Ultrabook','Lenovo','4299',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3Inches Touch Ultrabook','Ultrabook','Sony','7999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('ipad mini md531CH/A 7.9Inches Tablet','Tablet','Apple','1998',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7Inches Tablet （16G WiFi Vision）','Tablet','Apple','3388',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A with Retina screen 7.9InchesTablet （16G WiFi Vision）','Tablet','Apple','2788',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20Inches All-in-one ','Desktop','Lenovo','3499',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 desktop','Desktop','Dell','2899',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5Inches All-in-one','Desktop','Apple','9188',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP Desktop （i5-3450 4G 500G 2GIndependent graphics card DVD Keyboard&Mouse linux ）','Desktop','Acer','3699',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PAWork station','server/Work station','HP','4288',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 IIserver','server/Work station','Dell','5388',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A Professional Desktop','server/Work station','Apple','28888',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W Headset Display Device','Laptop accessory','Sony','6999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Business Backpage','Laptopaccessory','Sony','99',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4 server 2583i14','server/Work station','IBM','6888',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Xuanlong Laptop Radiator','Laptop accessory','Windgod','',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W Headset Display Device','Laptop accessory','Sony','6999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Business Backpage','Laptop accessory','Sony','99',DEFAULT,DEFAULT);

---INSERT...SELECT

INSERT [INTO] tbl_name [(col_name)] SELECT

使用的時候就這樣，創建一個新的表

CREATE TABLE IF NOT EXISTS cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

然后把原數據表中的東西有選擇性的插入進來

INSERT INTO CATES(CATE_NAME) SELECT GOODS_CATE FROM GOODS GROUP BY GOODS_CATE;

如果需要根據目錄表cates，來更新我們的參數表——多表更新

----多表更新

--多步

UPDATE table_ref SET col_name=... ...

其中，ref是表的參照關系

這里有連接關系，INNER JOIN LEFT JOIN 等

最簡單的是INNER，內連接

mysql> UPDATE GOODS INNER JOIN CATES ON GOODS_CATE=CATE_NAME
-> SET GOODS_CATE=CATE_ID;

（請原諒我不想打小寫的了。。）

解釋一下：更新goods這張表，內連接cates這張表，在什么條件呢——goods_cate=cate_name，設定的值是什么呢，是cates這張表中的cate_id

mysql> SELECT * FROM GOODS/G; 驗證一下

--一步

CREATE...SELECT...

即創建表的同時寫入連接的數據

這里的例子是：對品牌做連接更新

mysql> SELECT BRAND_NAME FROM GOODS GROUP BY BRAND_NAME;

mysql> CREATE TABLE IF NOT EXISTS brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> SELECT BRAND_NAME FROM GOODS GROUP BY BRAND_NAME;

就可以省去INSERT的操作了

但是這時候更新要出錯

mysql> UPDATE GOODS INNER JOIN BRANDS ON BRAND_NAME=BRAND_NAME
-> SET BRAND_NAME=BRAND_ID;
ERROR 1052 (23000): Champ: 'BRAND_NAME' dans field list est ambigu

因為名字一樣，要改字段，或者起別名AS

mysql> UPDATE GOODS AS G INNER JOIN BRANDS AS B ON G.BRAND_NAME=B.BRAND_NAME
-> SET G.BRAND_NAME=BRAND_ID;

雖然實現了外表的更新，但是，比如說goods_cate的類型還是varchar，而我們其實希望主表中，它是int類型的。這時候由于需要修改字段名以及數據類型，用ALTER CHANGE

mysql> ALTER TABLE GOODS
-> CHANGE GOODS_CATE CATE_ID SMALLINT UNSIGNED NOT NULL,
-> CHANGE BRAND_NAME BRAND_ID SMALLINT UNSIGNED NOT NULL;

這種外鍵稱為事實外鍵，比foreign key的物理外鍵還要多

----連接

由于我們實現了多表的存儲，但是由于顯示等需求，要求多個表聯動，需要用到連接

數據表參照ref：tbl_name [[AS] alias] | table_subquery [AS] alias

---INNER JOIN

使用ON來設定連接條件，WHERE一般做進一步的篩選

內連接，僅顯示符合連接條件的記錄

SELECT GOODS_ID,GOODS_NAME,CATE_NAME FROM GOODS INNER JOIN CATES ON GOODS.CATE_ID=CATES.CATE_ID;

翻譯一下：選取goods表中的goods_id,goods_name,cate_name字段，但是顯示的時候，需要內連接cates表（因為我們不想顯示的是類型的數字表示），條件是當兩者的cate_id字段相等；這里由于cate_id沒有給別名，所以寫成表名.cate_id

會發現，這個內連接的顯示，只顯示了on條件的結果

---外連接

外連接又分為左外和右外

LEFT JOIN顯示左表的全部記錄和右表符合鏈接條件的記錄，例子如下

注意最后一個記錄的類名是null

RIGHT JOIN是這樣的

----多表連接

mysql> SELECT GOODS_ID,GOODS_NAME,CATE_NAME,BRAND_NAME,GOODS_PRICE FROM GOODS AS G
-> INNER JOIN CATES AS C ON G.CATE_ID=C.CATE_ID
-> INNER JOIN BRANDS AS B ON G.BRAND_ID=B.BRAND_ID;

每連接一個表就需要一個JOIN，注意起別名給數據表！

此時的顯示雖然跟一開始一樣，但實現不同，意義大不一樣

----無限級分類表設計

一般實際中會出現無限級數據表，不斷往下分

舉個例子

CREATE TABLE goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT goods_types(type_name,parent_id) VALUES('Home Electronics',DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES('PC、Office',DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES('Appliances',1);
INSERT goods_types(type_name,parent_id) VALUES('Life Appliances',1);
INSERT goods_types(type_name,parent_id) VALUES('Tablet TV',3);
INSERT goods_types(type_name,parent_id) VALUES('Air Conditioner',3);
INSERT goods_types(type_name,parent_id) VALUES('Electrical Fan',4);
INSERT goods_types(type_name,parent_id) VALUES('Water Dispenser',4);
INSERT goods_types(type_name,parent_id) VALUES('Complete Machine',2);
INSERT goods_types(type_name,parent_id) VALUES('Accessory',2);
INSERT goods_types(type_name,parent_id) VALUES('Laptop',9);
INSERT goods_types(type_name,parent_id) VALUES('Ultrabook',9);
INSERT goods_types(type_name,parent_id) VALUES('Gamebook',9);
INSERT goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT goods_types(type_name,parent_id) VALUES('Host Computer',10);

mysql> SELECT * FROM GOODS_TYPES;
+---------+------------------+-----------+
| type_id | type_name | parent_id |
+---------+------------------+-----------+
| 1 | Home Electronics | 0 |
| 2 | PC?Office | 0 |
| 3 | Appliances | 1 |
| 4 | Life Appliances | 1 |
| 5 | Tablet TV | 3 |
| 6 | Air Conditioner | 3 |
| 7 | Electrical Fan | 4 |
| 8 | Water Dispenser | 4 |
| 9 | Complete Machine | 2 |
| 10 | Accessory | 2 |
| 11 | Laptop | 9 |
| 12 | Ultrabook | 9 |
| 13 | Gamebook | 9 |
| 14 | CPU | 10 |
| 15 | Host Computer | 10 |
+---------+------------------+-----------+

注意parent_id，是指改節點的級別，比如0，表示home electronics沒有父節點，是個頂級節點

查找的實現，是通過自身連接來完成的

---自身連接

舉個例子，顯示所有類別的父類是什么

這里，自身的連接，可以想象右邊有一張一模一樣的表（左邊也可以，有些東西稍微變一下就行）

翻譯一下，就是，可以先從條件說起，當子表s中的parent_id=父表中的type_id時，選取子表的type_id,type_name,以及父表的type_name顯示，這里的別名可以在語句的末尾處顯示

要把這個邏輯關系先說說/想想清楚，再翻譯為sql語句

反過來，查找所有類別的子類

先考慮邏輯關系：當父表的type_id=子表的parent_id時，取 P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME ——再寫出語句

再復雜一點，且留給你們自己翻譯

mysql> SELECT P.TYPE_ID,P.TYPE_NAME,COUNT(S.TYPE_NAME) CHILD_COUNT FROM GOODS_TYPES AS P LEFT JOIN GOODS_TYPES AS S
-> ON P.TYPE_ID=S.PARENT_ID GROUP BY P.TYPE_NAME ORDER BY P.TYPE_ID;
+---------+------------------+-------------+
| TYPE_ID | TYPE_NAME | CHILD_COUNT |
+---------+------------------+-------------+
| 1 | Home Electronics | 2 |
| 2 | PC?Office | 2 |
| 3 | Appliances | 2 |
| 4 | Life Appliances | 2 |
| 5 | Tablet TV | 0 |
| 6 | Air Conditioner | 0 |
| 7 | Electrical Fan | 0 |
| 8 | Water Dispenser | 0 |
| 9 | Complete Machine | 3 |
| 10 | Accessory | 2 |
| 11 | Laptop | 0 |
| 12 | Ultrabook | 0 |
| 13 | Gamebook | 0 |
| 14 | CPU | 0 |
| 15 | Host Computer | 0 |
+---------+------------------+-------------+

----多表刪除

基本思路也是通過一張表模擬多張表的操作

懶了，直接復制看吧，需要了再去做

-- INSERT ... SELECT實現復制

INSERT goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM goods WHERE goods_id IN (19,20);

-- 查找重復記錄

SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2;

-- 刪除重復記錄

DELETE t1 FROM goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

上一篇：離職了，一切從頭開始

下一篇：Yii2的深入學習--yii/base/Object類