# 前言
數據庫，后端開發者必學，而且現在以MySQL居多。家下來我們將系統化MySQL一些高級用法。打算先講很多人關注的使用方式（增刪改查以及其優化），然后就講數據庫和表的操作（很多我們學習忽略的地方），接著就是引擎還有更高級的查詢等等
# 操作數據語句優化認識：
通常情況下，當訪問某張表的時候，讀取者首先必須獲取該表的鎖，如果有寫入操作到達，那么寫入者一直等待讀取者完成操作（查詢開始之后就不能中斷，因此不允許讀取者完成之前進行寫操作）。當讀取者完成對表的操作的時候，鎖就會被解除。如果寫入者正在等待的時候，另一個讀取操作到達了，該讀取操作也會被阻塞（block），因為默認的調度策略是寫入者優先于讀取者。當第一個讀取者完成操作并解放鎖后，寫入者開始操作，并且直到該寫入者完成操作，第二個讀取者才開始操作。
通過**LOCK TABLES**和**UNLOCK TABLES**語句可以顯式地獲取或釋放鎖，但是在通常情況下，服務器的鎖管理器會自動地在需要的時候獲取鎖，在不再需要的時候釋放鎖。獲取的鎖的類型依賴于客戶端是寫入還是讀取操作。
對某張表進行寫入操作的客戶端必須擁有獨占的（排他的）訪問權的鎖。操作在進行的過程中，該數據表處于不一致的（inconsistent）狀態，因為數據記錄在刪除、添加或修改的時候，數據表上的索引也可能需要更新以相互匹配。允許兩個客戶端同時寫入一張數據表是不利的，因為這樣的操作會很快使數據表中的信息成為一堆無用的垃圾。同時允許客戶端讀取變化之中的數據表也不正確，因為正在讀取的位置中的數據可能正在變化（修改），讀取的結果可能并不是真實的。因此對某張表執行讀取操作的客戶端也必須獲取一個鎖，防止在讀取的過程中，其它的客戶端寫入或改變表。但是這個鎖不需要獨占的訪問權。因為讀取操作不會改變數據，因此沒有理由讓某個讀取者阻止其它的讀取者訪問這張表。所以讀取鎖可允許其它的客戶端在同一時刻讀取這張表。
雖然通過鎖機制，可以實現多線程同時對某個表進行操作，但當某個線程作更新操作時，首先要獲得獨占的訪問權。在更新的過程中，所有其它想要訪問這個表的線程必須要等到其更新完成為止。此時就會導致鎖競爭的問題，從而導致用戶等待時間的延長。
因此：要提高MySQL的更新/插入效率，應首先考慮降低鎖的競爭，減少寫操作的等待時間。 （在后面才討論表設計的優化）
* * * * *
~~~
CREATE TABLE `score` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`change_type` VARCHAR(255) NULL DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`score` INT(11) NULL DEFAULT NULL,
`user_id` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';
CREATE TABLE `gag` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gag_time` DATE NULL DEFAULT NULL,
`user_id` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';
~~~
* * * * *
# INSERT語句
**INSERT [INTO]** 表名 [(字段列表)] **VALUES** (值列表)[, (值列表), …]
**注意：**
如果要插入的值列表包含所有字段并且順序一致，則可以省略字段列表。
可同時插入多條數據記錄！
**例子：**
~~~
INSERT INTO score (change_type,score,user_id) VALUES ('吃飯',10,1);
~~~
**優化：**
（1）當我們需要批量插入數據的時候，這樣的語句卻會出現性能問題。例如說，如果有需要插入100000條數據，那么就需要有100000條insert語句，每一句都需要提交到關系引擎那里去解析，優化，然后才能夠到達存儲引擎做真的插入工作。上述所說的同時插入多條就是一種優化。（經測試，大概10條同時插入是最高效的）
**例子：**
~~~
INSERT INTO score (change_type,score,user_id) VALUES ('吃飯',10,1),('喝茶',10,1),('喝茶',10,1);
~~~
（2）將進程/線程數控制在2倍于CPU數目相對合適
（3）采用順序主鍵策略（例如自增主鍵，或者修改業務邏輯，讓插入的記錄盡可能順序主鍵）
**DELETE語句：**
DELETE FROM 表名[ 刪除條件子句]
沒有條件子句，則會刪除全部（***慎重***）
**例子：**
~~~
DELETE FROM gag; 刪除全部
DELETE FROM gag WHERE id=1;
~~~
**補充：**
Mysql中truncate table和delete語句都可以刪除表里面所有數據，但是在一些情況下有些不同！
**例子：**
`truncate table gag;`
（1）truncate table速度要更快一些，但truncate刪除后不記錄mysql日志，不可以恢復數據。
（2）如果沒有外鍵關聯，innodb執行truncate是先drop table(原始表),再創建一個跟原始表一樣空表,速度要遠遠快于delete逐條刪除行記錄。
（3）表有外鍵關聯，truncate table刪除表數據為逐行刪除，
如果外鍵指定級聯刪除(delete cascade)，關聯的子表也會會被刪除所有表數據。
如果外鍵未指定級聯(cascde),truncate table逐行刪除數據，
如果是父行關聯子表行數據，將會報錯。
（4）auto_increment計數器在truncate table后會重置為0.與是否有外鍵關聯沒有關系。
**注意：**
一個大的 DELETE 或 INSERT 操作，要非常小心，因為這兩個操作是會鎖表的，表一鎖住，其他操作就進不來了。因此，我們要交給DBA去拆分，重整數據庫策略，比如限制處理1000條。
MySQL官方手冊得知刪除數據的速度和創建的索引數量是成正比的。所以在超大型數據庫中，刪除時處理好索引關系非常重要。
**推薦的折中方法：**
在刪除數據之前刪除這幾個索引，然后刪除其中無用數據，刪除完成后重新創建索引。
**UPDATE語句**
`UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新條件]`
例子：
~~~
UPDATE score SET change_type='洗澡' WHERE id=2;
~~~
**優化：**
更新多條記錄
~~~
Update score
SET change_type = CASE id
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END
WHERE id IN (1,2,3)
~~~
更新多條記錄的多個值
~~~
Update score
SET change_type = CASE id
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END ,
score = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
END
WHERE id IN (1,2,3)
~~~
(1). 盡量不要修改主鍵字段。
(2). 當修改VARCHAR型字段時，盡量使用相同長度內容的值代替。
(3). 盡量最小化對于含有UPDATE觸發器的表的UPDATE操作。
(4). 避免UPDATE將要復制到其他數據庫的列。
(5). 避免UPDATE建有很多索引的列。
(6). 避免UPDATE在WHERE子句條件中的列。
# 庫與表基本操作
## 庫的基本操作
（1）查看所有數據庫以及使用數據庫：
~~~
show databases;
use table;
~~~
（2）查看當前數據庫
~~~
select database();
~~~
（3）顯示當前時間、用戶名、數據庫版本
~~~
select now(), user(), version();
~~~
（4）創建庫
~~~
create database[ if not exists] 數據庫名 數據庫選項
數據庫選項：
CHARACTER SET charset_name
COLLATE collation_name
~~~
（5）查看當前庫信息
~~~
show create database 數據庫名
~~~
（6）修改庫的選項信息
~~~
alter database 庫名 選項信息
~~~
（7）刪除庫
~~~
drop database[ if exists] 數據庫名
同時刪除該數據庫相關的目錄及其目錄內容
~~~
## 表的基本操作：
（1）創建表：
~~~
create table[ if not exists] 表名 ( 表的結構定義 )[ 表選項]
每個字段必須有數據類型
最后一個字段后不能有逗號
對于字段的定義：
字段名 數據類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
~~~
（2）表的選項：
1. 刪除列
~~~
ALTER TABLE 【表名字】 DROP 【列名稱】
~~~
2. 增加列
~~~
ALTER TABLE 【表名字】 ADD 【列名稱】 INT NOT NULL COMMENT ‘注釋說明’
~~~
3. 修改列的類型信息
~~~
ALTER TABLE 【表名字】 CHANGE 【列名稱】【新列名稱（這里可以用和原來列同名即可）】 BIGINT NOT NULL COMMENT ‘注釋說明’
~~~
4. 重命名列
~~~
ALTER TABLE 【表名字】 CHANGE 【列名稱】【新列名稱】 BIGINT NOT NULL COMMENT ‘注釋說明’
~~~
5. 重命名表
~~~
ALTER TABLE 【表名字】 RENAME 【表新名字】
~~~
6. 刪除表中主鍵
~~~
Alter TABLE 【表名字】 drop primary key
~~~
7. 添加主鍵
~~~
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
~~~
8. 添加索引
~~~
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
~~~
9. 添加唯一限制條件索引
~~~
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
~~~
10. 刪除索引
~~~
Alter table tablename drop index emp_name;
~~~
（3）查看所有表
~~~
SHOW TABLES；
~~~
（4）查看表結構：
~~~
SHOW CREATE TABLE 表名 （信息更詳細）
DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
~~~
（5）修改表：對表進行重命名
~~~
RENAME TABLE 原表名 TO 新表名
RENAME TABLE 原表名 TO 庫名.表名 （可將表移動到另一個數據庫）
~~~
（6）刪除表
~~~
DROP TABLE[ IF EXISTS] 表名
~~~
（7）清空表數據
~~~
TRUNCATE [TABLE] 表名
~~~
（8）復制表結構
~~~
CREATE TABLE 表名 [AS] SELECT * FROM 要復制的表名
~~~
