大廠面試預備篇——《兩萬MySql基礎總結》??建議收藏

?? 一條獨家專欄

?? 搞技術，進大廠，聊人生

📚《大廠面試突擊》——面試10多家中大廠的萬字總結

📚《技術專家修煉》——高薪必備，企業真實場景

📚《leetcode 300題》——每天一道演算法題，進大廠必備

📚《糊涂演算法》——資料結構+演算法全面講解

📚《從實戰學python》——python的各種應用

📚《程式人生》——聽一條聊職場，聊人生

👉更多資料點這里

天下難事，必作于易；天下大事，必作于細， —— 老子

前言

哈嘍，大家好，我是一條，

前幾天有個粉絲來找我，發生了這樣一段有趣的對話：

一條哥 ，請教個問題，我昨天要女神微信被拒絕了怎么辦？

我不是教你搞技術嗎？ 你小子搞物件去了，leetcode題刷了嗎？

要不到微信刷不進去題呀，幫幫忙吧

淡定，急什么，我和你說，要先找到女神的需求，用科技賦能愛情，才能加微信，約吃飯，談戀愛全鏈路打通，

太高深了，聽不懂，你就告訴我怎么做吧

這些互聯網黑話等你畢業就懂了，你現在和我說說女神最近有什么煩惱嗎

女神怎么可能會有煩惱，要啥有啥

給我好好想，你得動腦子呀

哎，還真有一個，她最近資料庫考試考的不太好，不怎么開心

這樣，那就好辦了，我這有一份《mysql萬字秘籍》，你拿回去好好研讀，保你下次班級第一，到時借著發學習資料的名義不就加了微信，然后再約著上自習，完事吃個飯，這就叫全鏈路打通，懂了嗎？

妙啊，秘籍呢，快給我吧

看你那猴急的樣，給我一鍵三連，隨后發你

沒問題，謝謝一條哥！

---

下面就是2萬字秘籍，免費送給大家，老規矩——一鍵三連

?? MySql不區分大小寫，但是很多人愿意對重要陳述句用大寫，用表名等用小寫，便于閱讀

資料庫

選擇資料庫

USE name;

顯示可用資料庫

SHOW DATABASES;

顯示可用表

SHOW TABLES;

獲取表的一個列

SHOW COLUMNS FROM yitiao_coding;

資料型別

數值型別

• 整數型別：tinyint smallint mediumint int bigint
• 浮點型別：float double
• 定點小數型別：decimal

日期型別

year time date datetime timestamp

字串型別

• 文本字串：char varchar tinytext text mediumtext longtext enum set
• 二進制字串：bit binary tinyblob blog mediumblob longblob

表

創建表

CREATE TABLE SalesSummary (

yitiaouct_name VARCHAR(50) NOT NULL ,

total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 ,

avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 ,

total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 );

# 通常設定：
ENGINE=InnoDB default CHARSET=utf8；

插入值

INSERT INTO SalesSummary

(yitiaouct_name, total_sales, avg_unit_price, total_units_sold)

VALUES ('cucumber', 100.25, 90, 2);

查看表

利用 SHOW TABLES 命令顯示表時，臨時表不會出現在結果串列中，如果退出 MySQL 會話，就會執行 SELECT 命令，那么資料庫中將沒有任何資料，甚至臨時表也不存在了，

SELECT * FROM yitiao_coding;

洗掉表

默認情況下，當與資料庫的連接終止時，臨時表就不再存在，不過如果想在資料庫處于連接時就洗掉它們，可以用 DROP TABLE 命令來洗掉，

DROP TABLE SalesSummary;

# 洗掉程度可從強到弱如下排列：

drop table tb;

# drop 是直接將表格洗掉，無法找回，例如洗掉 user 表：

drop table user;

truncate （table） tb;

# truncate 是洗掉表中所有資料，但不能與where一起使用；

delete from tb (where);

# delete 也是洗掉表中資料，但可以與where連用，洗掉特定行；

# 洗掉表中所有資料

delete from user;

# 洗掉指定行

delete from user where username ='Tom';

復制表

可以采用如下步驟來處理這種情況，

使用 SHOW CREATE TABLE 或 CREATE TABLE 陳述句指定源表的結構、索引以及所有的內容，

調整陳述句，將表名改為克隆表的名稱，執行陳述句，這樣就對表進行了克隆，另外，如果想要克隆表的全部內容，也可以使用 INSERT INTO … SELECT 陳述句，

• 步驟1：獲取表的完整結構

• 步驟2：重新命名該表，創建另一個表

• 步驟3：執行完步驟2后，就在資料庫中創建了一個克隆表，如果想從舊表中復制資料，可以使用 INSERT INTO… SELECT 陳述句，

查找資料

查找列

# 從 yitiuao_coding 表中獲取 yitiao 這一列
SELECT yitiuaocoding FROM yitiuao_coding;

查找多個列

SELECT yitiao_coding, yitiao_it, yitiao_name FROM yitiao;

查找所有列

SELECT * FROM yitiao;

查找不同的行

# 重復的行只顯示一次
SELECT DISTINCT yitiao_id FROM yitiao;

限定查找

注意，行0開始數，查找5行是:0,1,2,3,4行，第6行同理從行0開始數

# 只查找5行

SELECT yitiao_id

FROM yitiao

LIMIT 5;

# 查找第6行開始的5行

SELECT yitiao_id

FROM yitiao

LIMIT 6, 5;

# 限定表名的列

SELECT yitiao.yitiao_id

FROM yitiao;

排序查找

排序

# 用yitiao_coding的字母順序排序

SELECT yitiao_coding FROM yitiao

ORDER BY yitiao_coding;
# 先用價格排序，再用名稱排序

SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiao

ORDER BY yitiao_price, yitiao_coding;

降序

無說明默認升序，降序需要用DESC(descrease)加以說明

如果要在多個列上進行降序，需要對每個列都進行DESC說明

# 價格降序

SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiao

ORDER BY yitiao_price DESC;

# 先對價格降序，再用名稱排序（無說明默認升序）

SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiao

ORDER BY yitiao_price DESC, yitiao_coding;

執行順序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

選擇查詢

WHERE子句運算子

= 等于，！= 不等于，< 小于， > 大于， <= 小于等于， >= 大于等于， BETWEEN AND 在指定的兩個值之間

選擇單個值

# 名字等于fuses

SELECT yitiao_id, yitiao_price, yitiao_coding

FROM yitiao

WHERE yitiao_mane = 'fuses';

# 價格大于10

SELECT yitiao_id, yitiao_price, yitiao_coding

FROM yitiao

WHERE yitiao_price > 10;

#供應商ID不是1003

SELECT yitiao_id, yitiao_price, yitiao_coding

FROM yitiao

WHERE yitiao_id != 1003;

范圍值檢查

SELECT yitiao_id,yitiao_coding,yitiao_price

FROM yitiao

WHERE yitiao_price BETWEEN 5 AND 10;

空值檢查

IS NULL：如果列值為 NULL，則該運算子回傳 true，

IS NOT NULL：如果列值不為NULL，則該運算子回傳 true，

該運算子用于兩個值的對比，當兩個值都為 NULL 時（這一點與 = 運算子不同），回傳 true，

包含 NULL 的條件都是比較特殊的，不能在列中使用 = NULL 或 ! = NULL 來尋找 NULL 值，這樣的比對通常都是失敗的，因為不可能得知這樣的比對是否為真，

# NULL表示空值，no value

SELECT yitiao_id,yitiao_coding,yitiao_price

FROM yitiao

WHERE yitiao_price IS NULL;

AND & OR

AND表示同時滿足所有條件

# id=1003，價格小于等于10

SELECT  yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id = 1003 AND yitiao_price <= 10;

OR表示滿足所有的單個條件

# id是1002或者是價格等于10

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id = 1002 OR yitiao_price = 10;

次序計算

MySql優先處理AND, 后處理OR

# 先滿足yitiao_id = 1003 AND yitiao_price = 10

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id = 1002 OR yitiao_id = 1003 AND yitiao_price = 10;

# 要先處理OR，應該加（）

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE (yitiao_id = 1002 OR yitiao_id = 1003) AND yitiao_price = 10;

IN指定條件范圍

# 查找1002，1003供應商并用名稱排序

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id IN (1002, 1003)

ORDER BY yitiao_coding;

NOT & NOT IN

NOT表在WHERE子句中用來否定后跟的條件，NOT IN可以用來取反

# 查找不是1002，1003供應商并用名稱排序

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id NOT IN (1002, 1003)

ORDER BY yitiao_coding;

模糊查詢

LIKE子句+通配符 %

%表示任何字串出現任意次數，區分大小寫

_表示任何字串出現單次，指一個字符，其他功能和%一樣

# 查找以jet起頭的產品名字（和JET起頭不匹配）

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding LIKE 'jet%';
# 查找產品名字中任意位置有care字符

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding LIKE '%care%';

# 查找產品名字中以s開頭e結尾的字符，長度不限

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding LIKE 's%e';

正則運算式

正則運算式是用來匹配文本的特殊的串（字符集合）

基本字符匹配

# 查找列yitiao_coding**包含文本1000**的所有行,和LIKE類似

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding **REGEXP '1000'**

ORDER BY yitiao_coding;
# . 是正則運算式中一個特殊的字符，表示匹配任意一個字符，查找列yitiao_coding包含文本.000的所有行（比如1000，2000，3000），和LIKE不同

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding REGEXP '.000'

ORDER BY yitiao_coding;

OR的匹配

# 查找列yitiao_coding包含文本1000，2000的行

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding REGEXP '1000|2000'

ORDER BY yitiao_coding;

匹配幾個字符之一

# 匹配1TON, 2TON, 3TON的組合方式, [123]TON = [1|2|3]TON**，如果輸1|2|3 TON是指1，2，3 TON，不是1TON, 2TON, 3TON.

SELECT yitiao-name, yitiao_price

FROM yitiao

WHERE yitiao_coding REGEXP '[1|2|3]TON'

ORDER BY yitiao_coding;

匹配范圍

[1-3]是一個范圍，[a-z]匹配任意字母字符

匹配特殊字符

用\為前導來匹配，如\.

# 匹配帶.的特殊字符

SELECT yitiao-name, yitiao_price

FROM yitiao

WHERE yitiao_coding REGEXP '\\.'

ORDER BY yitiao_coding;

空白元字符

\f 換頁， \n 換行， \r 回車，\t 制表，\v 縱向制表

重復元字符

0個或多個字符，+ 1個或多個字符，？0個或1個字符，{n} 指定n個字符，{n,} 指定不少于 n個字符，{n,m}指定n-m個字符

定位元字符

^xx 以xx開頭，xx$ 以xx結束，[[:<:f]] 詞的開始，[[:>:]] 詞的結尾

#尋找以 'st' 開頭的名稱，查詢如下：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

#尋找以 'ok' 結尾的名稱，查詢如下：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

文本數值時間計算處理

Concat() 拼接

將值聯結到一起構成單個值

# 字串拼接

select concat(str1,str2...)

# 把兩列合并為一列

select concat(vender, country) as yitiao

from test;

使用別名

AS賦予聯結后的單個詞一個別名

SELECT Contact(vend_name, '(',vend_country,')') **AS** vend_title

FROM vendors

ORDER BY vend_name;

文本字串處理

Left() 回傳左邊的字串， Right() 回傳右邊的字串，Length() 回傳字串的長度，Locate() 找出串的一個子串，SubString() 回傳子串的字符，Soundex() 回傳字串的SOUNDEX值(類似發音的字串)，RTrim洗掉右邊多余的空格，LTrim洗掉左邊多余的空格， Upper() 轉為大寫，Lower() 轉為小寫，

洗掉右邊空格

SELECT RTim(vend_name), RTrim(vend_country)

FROM vendors

ORDER BY vend_name;

Upper()轉為大寫，Lower()轉為小寫

SELECT vend_name, Upper(vend_name) AS vend_name_upcase

FROM vendors

ORDER BY vend_name;

日期和時間處理函式

Year() 回傳一個時間的年份，Month() 回傳一個時間的月份，Date() 回傳一個時間的日期，Day() 回傳一個時間的天數，Hour() 回傳一個時間的小時，Minute() 回傳一個時間的分鐘，Second() 回傳一個時間的秒鐘，Now() 回傳當前日期和時間，Time() 回傳一個日期的時間，AddDate() 增加一個日期，AddTime() 增加一個時間

查找一個特定日期的訂單

SELECT yitiao_id, yitiao_coding

FROM yitiao

WHERE Date(order_date) = '2020-09-02'

ORDER BY yitiao_coding;

查找特定的時間段

SELECT yitiao_id, yitiao_coding

FROM yitiao

WHERE Date(order_date) **BETWEEN** '2021-09-01' **AND** '2021-09-03';

# 查找特定年份和月份

SELECT yitiao_id, yitiao_coding

FROM yitiao

WHERE Year(order_date) = 2021 **AND** Month(order_date) = 9;

算數計算

# 查找訂單號是2005的產品數量和價格

SELECT yitiao_id, yitiao_qty, yitiao_price

FROM yitiao

WHERE order_num = 2005;

# 上述 查找后計算總價值（數量*價格）

SELECT yitiao_id, yitiao_qty, yitiao_price, **yitiao_qty\*yitiao_price AS expanded_price**

FROM yitiao

WHERE order_num = 2005;

數值處理函式

Abs() 回傳一個數的絕對值，Sqrt() 回傳一個數的平方根， Rand() 回傳一個亂數，Pi() 回傳圓周率Exp()，回傳一個數的指數值，Mod() 回傳除操作的余數，Cos() 回傳一個角度的余弦值 Sin() 回傳一個角度的正弦值，Tan() 回傳一個角度的正切值

資料處理

AVG() 平均值

# 求產品平均值

SELECT AVG(yitiao_price) AS avg_price

FROM yitiao;

# AVG(DISTINCT), 相同價格只出現一次，計算平均值

SELECT AVG(DISTINCT yitiao_price) AS avg_price

FROM yitiao;

COUNT() 計數

COUNT(*) AS cust_num, 對所有列進行計數的，但是只回傳cust_num里的計數結果

COUNT(*)對所有進行計數，COUNT(column)對除掉NULL的列進行計數

# 求客戶數量

SELECT COUNT(*) AS num_cust

FROM customers;

# 求有郵箱的客戶數量

SELECT COUNT(cust_email) AS num_cust

FROM customers;

最值

# MAX() 最大值

SELECT MAX(yitiao_price) AS max_price

FROM yitiao;

# MIN() 最小值

SELECT MIN(yitiao_price) AS min_price

FROM yitiao;

SUM() 求和

# 訂單中物品為2005的所有數量

SELECT SUM(qty) AS total_items

FROM orderitems

WHERE item_name = 2005;

# 訂單中物品為2005的全部金額

SELECT SUM(qty*item_price) AS total_amount

FROM orderitems

WHERE item_name = 2005;

SELECT 可以包含多個聚集函式

SELECT COUNT(*) AS item_num

MIN(yitiao_price) AS min_price

MAX(yitiao_price) AS max_price

AVG(yitiao_price) AS avg_price

FROM yitiao;

分組

創建分組

# 不同供應商包含的產品計數并分組

SELECT yitiao_id, COUNT(*) AS yitiao_num

FROM yitiao

GROUP BY yitiao_id;

過濾分組，

WHERE針對特定值（每個值，原值），HAVING針對分組過濾后的值**

# 查找買了2次以上的客戶,此處不能用WHERE

SELECT cust_id, COUT(*) AS order_num

FROM orders

GROUP BY cust_id

HAVING COUNT(*) >= 2;

# 價格為10以上，具有2個以上產品的供應商

SELECT yitiao_id, COUNT(*) AS yitiao_num

FROM vendors

WHERE yitiao_price >= 10

GROUP BY yitiao_id

HAVING COUNT(*) >= 2;

語法順序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

子查詢

利用子查詢過濾

在幾個關系表中，檢索TNT2物品的客戶資訊，但是沒有直接一個表體現這個資訊，一步一步過濾，方式如下：

# 查找包含物品TNT2的所有訂單編號

SELECT order_num

FROM orderitems

WHERE yitiao_id = 'TNT2';

輸出結果（2005，2007）

# 查找該訂單編號的所有客戶ID

SELECT cust_id

FROM orders

WHERE oder_num IN (2005,2007);

輸出結果（1001，1004）

# 查找該客戶ID的所有客戶資訊

SELECT cust_info

FROM customers

WHERE cust_id IN (1001, 1004);

輸出結果

利用子查詢，方式如下：

SELECT cust_info

FROM customers

WHERE cust_id **IN** (

SELECT cust_id

FROM orders

WHERE oder_num **IN**(

SELECT order_num

FROM orderitems

WHERE yitiao_id = 'TNT2'));

聯結表

創建聯結 JOIN

# 從2個關系表中匯出資料

SELECT yitiao_price, yitiao_coding, vend_name

FROM yitiao, vendors

**WHERE yitiao.yitiao_id = vendors.yitiao_id**

ORDER BY vend_name, yitiao_coding;

# INNOR JOIN...ON內部聯結-上述的第二種寫法

SELECT yitiao_price, yitiao_coding, vend_name

FROM yitiao

**INNER JOIN vendors ON yitiao.yitiao_id = vendors.yitiao_id;**

聯結多個表

SELECT yitiao_price, yitiao_coding, vend_name, order_num

FROM yitiao, vendors, orders

WHERE yitiao.yitiao_id = vendors.yitiao_id

AND yitiao.yitiao_id = orders.yitiao_id

AND order_num = 2005； # AND起過濾作用

自聯結

# 先找到物品ID是TNT2的供應商，再找到此供應商ID下的其他物品，把1個表別名成2個表，p1輸出物品名字和ID，p2用作關聯TNT2的語法和結果輸出

SELECT p1.yitiao_id, p1.yitiao_coding

FROM yitiao AS p1, yitiao AS p2

**WHERE p1.yitiao_id = p2.yitiao_id**

**AND p2.yitiao_id = 'TNT2';**

自然聯結

關系表中有一樣的列，通過表別名和篩選，使每個列只回傳一次

# *通配符只對c表使用，其他表的重復列沒有被查找出來

SELECT c*, o.order_item, o.order_date, oi.yitiao_id, oi.yitiao_qty, oi.yitiao_price

FROM customers AS c, orders AS o, orderitems AS oi

WHERE c.cust_id = o.cust_id

AND p.yitiao_id = oi.yitiao_id

AND yitiao_id = 'TNT2';

外部聯結

# 為了查找所有客戶的下單數量，包括沒有訂單的客戶, LEFT OUTER JOIN...ON**表示從左邊的表(customers)中選擇所有行

SELECT customers.cust_id, orders.order_num

FROM customers

LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

使用帶聚集函式的聯結

# 內部聯結，表之間相等的行聯結

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_num

FROM customers INNER JOIN orders

ON customers_cust.id = orders.cust_id

GROUP BY customers_cust.id;

# 外部聯結，表之間有不相關聯的行聯結

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_num

FROM customers LEFT OUTER JOIN orders

ON customers_cust.id = orders.cust_id

GROUP BY customers_cust.id;

更新資料

更新查詢Update

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

# 把李四的地址改為上海（默認Beijing)

Update employee set city = 'Shanghai' where id = 2;

更改表結構

# 在表employee增加一列addr

Alter table employee **add** colunm addr varchar(40);

更新表名

Rename Table 表名 to 新表名；

Alter選擇

洗掉、添加列或對其重新定位

# 從表中洗掉 i這一列

ALTER TABLE testalter_tbl DROP i; # 如果表中只有一列，則 DROP 子句不起作用

# 下面我們再把 i 這一列恢復到 testalter_tbl 中，使用 ADD 并指定列定義：

ALTER TABLE testalter_tbl ADD i INT;

# 要想把列放到一個特定位置，可以使用兩種方法，第一種方法是使用 FIRST，讓指定列成為第一列；第二種則采用 # AFTER 后跟給定列名的方式，指示新列應該放到給定列名的后面，

ALTER TABLE testalter_tbl DROP i; 
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i; 
ALTER TABLE testalter_tbl ADD i INT AFTER c;

# 識別符號 FIRST 和 AFTER 只能和 ADD 子句一起使用，這也意味著，如果要重新定位一列，就必須先用 DROP # 洗掉它，然后再用 ADD 將它添加到新的位置，

更新列名和資料型別

更改資料型別，把列 c 從 CHAR(1) 變為 CHAR(10)：

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

# CHANGE 的語法稍有不同，必須把所要改變的列名放到 CHANGE 關鍵字的后面然后指定新的列定義

ALTER TABLE testalter_tbl CHANGE 原列名 新列名 列定義;

# 如果想利用 CHANGE 將 j 從 BIGINT 轉為 INT，并且不改變列名，則陳述句如下：

ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 對 Null 及默認值屬性的作用

在利用 MODIFY 或 CHANGE 修改列時，還可以指定該列是否能有 NULL 值，以及它的默認值，如果我們不這樣處理，MySQL 會自動為這些屬性指定相關值，

# NOT NULL 列默認值為100：

MODIFY j BIGINT NOT NULL DEFAULT 100;

# 使用 ALTER 命令可以改變任何列的默認值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

# 使用 DROP 子句與 ALTER 命令，可以去除任何列中的默認限制

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

改變表型別

結合使用 TYPE 子句與 ALTER 命令，可以使用表型別

ALTER TABLE testalter_tbl TYPE = MYISAM;

對表進行重命名

使用 ALTER TABLE 陳述句的 RENAME 選項可以對表進行重命名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Index索引

簡單而唯一的索引

可以為表創建唯一索引，唯一索引要求任意兩行的索引值不能相同

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

# 可以使用一或多個列來創建索引

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

# 降序在列中索引數值，可以在列名后添加保留字 DESC(Descending)

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

添加與洗掉 INDEX 的ALTER 命令

為表添加索引，可以采用4種陳述句

# 該陳述句添加一個主鍵，意味著索引值必須是唯一的，不能為 NULL

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

# 該陳述句為必須唯一的值（除了 NULL 值之外，NULL 值可以多次出現）

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

# 陳述句為可能多次出現的值創建一般索引

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

# 陳述句創建專用于文本搜索的 FULLTEXT 索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

為現有表添加索引

ALTER TABLE testalter_tbl ADD INDEX (c);

# 可以使用 DROP 子句以及 ALTER 命令洗掉索引

ALTER TABLE testalter_tbl DROP INDEX (c);

利用 ALTER 命令來添加與洗掉主鍵

添加主鍵也采用類似方式，但要保證主鍵一定在列上，是 NOT NULL

# 在現有表中添加主鍵，先使列為 NOT NULL，然后再將其作為主鍵

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

# 洗掉一個主鍵

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

序列

使用 AUTO_INCREMENT 列

# 先創建一個表，然后插入一些行，不需要提供記錄ID，因為這是由 MySQL 自動增加的

CREATE TABLE insect (

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id),

name VARCHAR(30) NOT NULL, # type of insect

date DATE NOT NULL, # date collected

origin VARCHAR(30) NOT NULL # where collected );

# 插入值

INSERT INTO insect (id,name,date,origin)

VALUES (NULL,'housefly','2001-09-10','kitchen'),

(NULL,'millipede','2001-09-10','driveway'),

(NULL,'grasshopper','2001-09-10','front yard');

對已有序列進行重新編號

如果一定要對 AUTO_INCREMENT 列進行重新排序，那么正確的方式是將該列從表中洗掉，然后再添加它，下面這個范例中就用了這個技巧，在 insect 表中對 id 值重新排序，

ALTER TABLE insect DROP id;

ALTER TABLE insect

ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,

ADD PRIMARY KEY (id);

以特定值作為序列初始值

MySQL 默認以 1 作為序列初始值，但你也可以在創建表時指定其他的數字，以 100 作為序列初始值

CREATE TABLE insect (

id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,

PRIMARY KEY (id),

name VARCHAR(30) NOT NULL, # type of insect

date DATE NOT NULL, # date collected

origin VARCHAR(30) NOT NULL # where collected );

重復處理

防止表中出現重復記錄

可以在表中正確的欄位內使用 PRIMARY KEY 或 UNIQUE 索引來終止重復記錄，比如下面這張表，由于沒有這樣的索引或主鍵，因此 first_name與last_name 就被重復記錄了下來，

CREATE TABLE person_tbl (

first_name CHAR(20),

last_name CHAR(20),

sex CHAR(10) );

為了防止表中出現同樣姓名的值，為其添加一個 PRIMARY KEY，同時要注意將索引列宣告為 NOT NULL，這是因為 PRIMARY KEY 不允許出現空值

CREATE TABLE person_tbl (

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10),

PRIMARY KEY (last_name, first_name) );

不要使用 INSERT ，使用 INSERT IGNORE，如果該記錄與現存的某個記錄重復，IGNORE 關鍵字就會讓 MySQL 默默地將其摒棄，不會產生任何錯誤，

# 下面這個范例不會產生任何錯誤，不會插入會產生重復的記錄，

INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ( 'Jay', 'Thomas');

# 使用 **REPLACE** 而不是 INSERT，如果是一個重復記錄，新的記錄將會替換舊有記錄，

REPLACE INTO person_tbl (last_name, first_name) VALUES ( 'Ajay', 'Kumar');

強制唯一性的另一種辦法是為表添加 UNIQUE 索引而不是主鍵，

CREATE TABLE person_tbl (

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10)

UNIQUE (last_name, first_name) );

確認重復記錄，并計算重復記錄數

# 下面是計算表中姓名記錄重復的查詢：

SELECT COUNT(*) as repetitions, last_name, first_name

FROM person_tbl GROUP BY last_name, first_name

HAVING repetitions > 1;

該查詢回傳表 person_tbl 中所有的重復記錄，一般來說，要想確認重復記錄，需要采取以下步驟：

• 確定可能產生重復記錄的列，
• 在列選擇串列中顯示所有列，利用 COUNT(*) ，
• 利用 GROUP BY 子句列出列，
• 加入 HAVING 子句排除唯一值，需要讓組計數大于1，

從查詢結果中消除重復記錄

使用DISTINCT(獨的) 和 SELECT 陳述句來查找表中的重復記錄，

SELECT DISTINCT last_name, first_name

FROM person_tbl

ORDER BY last_name;

另一種辦法是添加 GROUP BY 子句，命名選擇的列，消除重復記錄并只選擇指定列中的唯一值組合，

SELECT last_name, first_name

FROM person_tbl

GROUP BY (last_name, first_name);

使用表替換去除重復記錄

下面這種技巧也可以消除表中存在的所有重復記錄，

CREATE TABLE tmp

SELECT last_name, first_name, sex

FROM person_tbl;

GROUP BY (last_name, first_name);

DROP TABLE person_tbl;

ALTER TABLE tmp RENAME TO person_tbl;

為表加入 INDEX 或 PRIMARY KEY ，即使該表已經存在，你也可以利用這種技巧消除重復記錄，這種做法將來也依然保險，

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

---

?今天是堅持刷題更文的第49/100天

?各位的點贊、關注、收藏、評論、訂閱就是一條創作的最大動力