1.MySQL存儲引擎

1.1存儲引擎介紹

ＩnnoDB:是ＭySQL的默認存儲引擎，

其表在執行提交和回滾操作時是事務安全的，可以通過創建保存點（savepoint)來實現部分回滾．在系統崩潰後可以直接恢復；外鍵和引用完整性支持，包括級聯刪除和更新；基於行級別的鎖定和多版本化，使得在執行同時包含有檢索和更新操作的組合條件查詢時，可以表現出很好的並發性能；從5.6開始支持全文搜尋和ＦＵＬＬＴＥＸＴ索引；

MyISAM:

– 當保存連續相似的字符串索引值時，它會對鍵進行壓縮，此外，ＭｙＩＳＡＭ還可以壓縮相似的數字索引值，因為這些數值都是按高字節優先的方式來保存的，啟動數字壓縮功能將PACK_KEYS=1;

– 與其他存儲引擎相比，它為AUTO_INCREMENT列提供瞭更多的功能；

– 每個ＭｙＩＳＡＭ表都有一個標志，它會在執行表檢查操作時被設置，ＭｙＩＳＡＭ表還有一個標志，用於表明該表在上次使用後是否被正常關閉，如果伺服器意外宕機或機器崩潰，那麼可用這個標志來判斷表是否需要檢查和修復，如果象進行自動檢查，則需要在啟動伺服器時，將mysiam_recover_options系統變量設置為一個包含有ＦＯＲＣＥ選項的值，

– 支持全文檢索和ＦＵＬＬＴＥＸＴ索引；

– 支持空間數據類型和ＳＰＡＴＩＡＬ索引；

MEMORY存儲引擎

ＭＥＭＯＲＹ存儲引擎會把表存儲在內存中，並且這些表的行長度固定不變；

在某種意義上來講，ＭＥＭＯＲＹ表是臨時性的，當伺服器掉電時，其內容也會消失，也就是說，ＭＥＭＥＯＲＹ表在伺服器重啟之後依然存在，隻是他們的內容為空，不過相對於臨時表來說，ＭＥＭＯＲＹ表對其他客戶端來說是可見的；

ＭＥＭＯＲＹ表的以下幾個特點使他們比其他類型的表更易處理，因此速度也更快；
– 默認情況下，ＭＥＭＯＲＹ表使用的是散列索引，這種索引對＂相等比較＂非常快，對＂范圍比較＂非常慢，因此，散列索引隻適合於相等運算符＂＝＂和＂＜＞＂的操作；不適合於＜及＞操作及ＯＲＤＥＲ　ＢＹ子句裡；
– 為更便於處理，存儲在ＭＥＭＯＲＹ表裡的行使用長度固定不變的格式．這意味著不能使用ＢＬＯＢ和ＴＥＸＴ，ＶＡＲＣＨＡＲ是一種長度可變的類型，但由於在ＭｙＳＱＬ內部，它被當作是一種長度固定不變的ＣＨＡＲ類型，所以可以在ＭＥＭＯＲＹ表裡使用．

ＮＤＢ存儲引擎

ＮＤＢ存儲引擎是ＭｙＳＱＬ的集群存儲引擎，對於這個存儲引擎，ＭｙＳＱＬ伺服器實際上變成瞭一個其他進程的集群客戶端，集群節點會處理彼此間的通信，從而在內存中實現對表的管理，為瞭實現冗餘，這些表會在集群進程之間被復制，內存存儲提供瞭高性能，而集群機制則提供瞭高可用性，因為即使某個節點發生瞭故障，整個系統頁不會崩潰．

ＡＲＣＨＩＶＥ存儲引擎

該引擎提供瞭數據歸檔存儲功能，主要適合於大批量存儲那些＂寫瞭就不會再更改＂的行，因此，它所支持的ＳＱＬ語句很有限，ＩＮＳＥＲＴ和ＳＥＬＥＣＴ可以而ＤＥＬＥＴＥ和ＵＰＤＡＴＥ不可以使用，為瞭節省空間，在存儲時會對行進行壓縮，而在檢索時再對他們進行解壓，ＡＵＣＨＩＶＥ表可以包含一個帶索引的ＡＵＴＯ＿ＩＮＣＲＥＭＥＮＴ列，但其他列不能被索引；

ＢＬＡＣＫＨＯＬＥ存儲引擎

該引擎所創建的表，其寫操作會被忽略，讀操作是返回空內容．

ＣＳＶ存儲引擎

該引擎在存儲數據時，會用逗號進行分割，對於每個表，它會在資料庫目錄裡創建一個.CSV文件，這是一種普通文本文件，其中每個表行占用一個文本行，ＣＳＶ存儲引擎不支持索引．

ＦＥＤＲＥＡＴＥＤ存儲引擎

該引擎提供瞭訪問由其他ＭｙＳＱＬ伺服器進行管理的表的能力，也就是說，ＦＥＤＥＲＡＴＥＤ表的內容實際上位於遠程，當創建ＦＥＤＲＥＡＴＥＤ表時，需要指定一臺運行著其他伺服器的主機，並提供該伺服器上的某個賬戶的用戶名和密碼，需要訪問ＦＥＤＲＥＡＴＥＤ表時，本地伺服器將使用這個賬戶連接那臺遠程伺服器．

ＭＥＲＧＥ存儲引擎

該引擎提供瞭一種把多個ＭｙＩＳＡＭ表合並為一個邏輯單元的手段，查詢一個ＭＥＲＧＥ表時，相當於查詢其所有的成員表，這種做法的好處之一是，可以突破文件系統對單個ＭｙＩＳＡＭ表的最大尺寸所設定的限制，分區表可以替換ＭＥＲＧＥ表，並且不會受限於ＭｙＩＳＡＭ表．

1.1存儲引擎相關屬性：

並發控制

當多個連接對記錄進行修改時保證數據的一致性和完整性;

鎖類型

–>共享鎖：在同一時間內，多個用戶可以讀取同一個資源，數據不發生變化;

–>排他鎖：任何時候隻允許一個用戶寫入數據，阻塞其他的讀取或者寫鎖：

鎖顆粒

–>表鎖：開銷最小的鎖策略

–>行鎖：開銷最大的鎖策略

事務

用於保證資料庫的完整性，事務特性包括原子性，一致性，隔離性，持久性（ACID）

外鍵

用戶保證數據一致性的策略;

索引

是對數據表中一列或者多列的值進行排序的一種結構，（普通索引，唯一索引，全文索引，btree索引，hash索引）

1.2 存儲引擎的區別

	MyISAM	InnoDB	Memory	Archive
存儲限制	256TB	64TB	有	無
事務安全	–	支持	–	–
支持索引	支持	支持	支持	–
鎖顆粒	表鎖	行鎖	表鎖	行鎖
數據壓縮	支持	–	–	支持
支持外鍵	–	支持	支持	–

MyISAM:適用於事務處理不多的情況;
InnoDB:適用於事務處理較多，需要有外鍵支持的情況;

###1.3 存儲引擎的配置

通過修改MySQL配置文件

Default-storage-engine = engine;

通過創建表時命令來實現

CREATE TABLE table_name (……) ENGINE = engine;

通過修改數據表命令

ALTER TABLE table_name ENGINE [=] engine_name;

查看存儲引擎

SHOW ENGINES;

2 MySQL數據類型

2.1 整型

數據類型	存儲范圍	字節
TINYINT	有符號：-2^7～2^8-1，無符號：0～2^8-1	1
SMALLINT	有符號：-2^15~2^15-1,無符號：0～2^16-1	2
MEDIUMINT	有符號：-2^23~2^23-1,無符號：0～2^24-1	3
INT	有符號：-2^31~2^31-1,無符號：0～2^32-1	4
BIGINT	有符號：-2^63~2^63-1,無符號：0～2^64-1	8

2.2 浮點型

數據類型	存儲范圍	有效位	字節
FLOAT[(M,D)]	1.175494351 E-38～3.402823466 E+38	6～7	4
DOUBLE[(M,D)]	2.2250738585072014 E-308 ~1.7976931348623158 E+308	15~16	8

2.3 日期時間型

數據類型	范圍	字節
YEAR	1901～2155	1
TIME	-838：59：59～838：59：59	3
DATE	1000-01-01～9999-12-31	4
DATETIME	1000-01-01 00：00：00～9999-12-31 23：59：59	8
TIMESTAMP	1970-01-01 00：00：00～2037 00:00:00	4

2.4 字符型

數據類型	存儲范圍
CHAR（M）	M個字節，0<=M<=255
VARCHAR(M)	L+1個字節，其中L<=M,0<=M<=2^16
TINYTEXT	L+1個字節，L<=2^8
TEXT	L+2個字節，L<=2^16
MEDIUMTEXT	L+3個字節，L<=2^24
LONGTEXT	L+4個字節，L<=2^32

3 資料庫操作

3.1 創建資料庫

CREATE DATABASE database_name;#創建資料庫 GRANT ALL ON test.* to user(s); #為指定用戶（或所有用戶）提升權限

3.2 使用資料庫

USE database_name;

3.3 刪除資料庫

DROP DATABASE test;

3.4 創建表

CREATE TABLE [IF NOT EXISTS] table_name (column name,datatype);

3.5 查看數據表列表

SHOW TABLES [FROM db_name];

3.6 查看數據表結構

SHOW COLUMNS FROM tbl_name;

4. 數據表的基本操作

4.1 插入記錄

INSERT [INTO] tbl_name [(col_name,…)] VALUES (val,…);

4.2 記錄查找

SELECT expr,… FROM tbl_name;

5.約束的基本介紹

5.1 非空約束（NOT NULL）

5.2 主鍵約束（PRIMARY KEY）

每張數據表隻能存在一個主鍵

5.3 唯一約束（UNIQUE KEY）

保證數據的唯一性
一張表可以有多個唯一約束
允許為空

5.4 默認約束（DEFAULT）

當輸入數據時沒有明確賦值，自動賦予默認值

#舉例
CREATE TABLE t2( 
    id INT, 
    name VARCHAR(20), 
    sex ENUM('1','2','3') DEFAULT'3');

5.5 外鍵約束（FOREIGN KEY）

保持數據一致性，完整性 實現一對一或一對多關系 外鍵約束要求
父表與子表使用InnoDB存儲引擎，且不能使用臨時表
外鍵列和參照列具有相似的數據結構
外鍵列和參照列必須創建索引

5.6 外鍵約束的基本操作

CASCADE
自動刪除或者更新子表中匹配的行

SET NULL
從父表刪除或更新行，並設置子表中的外鍵列為NULL

RESTRICT
拒絕對父表的刪除或更新操作

NO ACTION
標準SQL的關鍵字，與RESTRICT相同

//舉例
CREATE TABLE t1(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20));
CREATE TABLE t2(
    id INT AUTO_INCREMENT PRIMARY KEY,
    type INT)
    FOREIGN KEY (type) REFERENCES t1(id ON DELETE CASCADE));```

##5.7 添加/刪除列
- **添加單列**

ALTER TABLE tbl_name ADD[COLUMN] col_name column_defination [FIRST|AFTER column_name];“`

添加多列

ALTER TABLE tbl_name ADD[COLUMN](colunmn_name, column_defination,...);```

- **刪除列**

ALTER TABLE tbl_name DROP[COLUMN] column_name,DROP[COLUMN] column_name;“`

5.8 添加/刪除約束

5.8.1 添加/刪除主鍵約束

添加主鍵約束

ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]] PRIMARY KEY[index_type](index_col_name,...);

刪除主鍵約束

ALTER TABLE tbl_name DROP PRIMARY KEY;

5.8.2 添加/刪除唯一約束

添加唯一約束

ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]] UNIQUE [INDEX|KEY][index_name] [index_type](index_col_name,...);

刪除唯一約束

ALTER TABLE tbl_name DROP{INDEX|KEY} index_name;

5.8.3 添加/刪除外鍵約束

添加外鍵約束

ALTER TABLE tbl_name ADD[CONSTRAINT [symbol]] FOREIGN KEY [index_type](index_col_name,...) reference_definition ;

刪除外鍵約束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

5.8.4 添加/刪除默認約束

添加默認約束

ALTER TABLE tbl_name ALTER[COLUMN] column_name SET DEFAULT literal;

刪除默認約束

ALTER TABLE tbl_name ALTER[COLUMN] col_name DROP DEFAULT;

5.9 列設定和更名數據表

5.9.1 修改列設定/列名稱

修改列設定

ALTER TABLE tbl_name MODIFY[COLUMN] col_name column_definition [FIRST|AFTER col_name];```
- **修改列名稱**

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];“`

5.9.2 修改數據表

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;```

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]…;“`

6.操作數據表和基本查詢

6.1 插入記錄——INSERT

INSERT [INTO] tbl_name [(col_name,...)]`{VALUES|VALUE} ({expr|DEFAULT},...),(...);```

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},…“`

6.2 更新和刪除記錄

6.2.1 更新表記錄

UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition];```

##6.2.2刪除表記錄

DELETE FROM tbl_name [WHERE where_condition];“`

6.3 查詢表達式

SELECT select_expr [select_epxr...] FROM tbl_name
[WHERE where_condition]
//查詢結果分組
[GROUP BY {col_name|position}[ASC|DESC],...]
//分組條件
[HAVING where_condition]
//查詢結果排序
[ORDER BY {col_name|expr|position}[ASC|DESC],...]
//查詢結果限制數量
//註意:limit如果限制某一范圍，其順序號是從0開始，然後再加上增幅;
[LIMIT {[offset],row_count|row_count OFFSET offset}];```

## 6.4 子查詢
子查詢關鍵字
    |---|ANY|SOME|ALL|
    |----|----|----|----|
    |>,>=|最小值|最小值|最大值|
    |<,<=|最大值|最大值|最小值|
    |<>,!=|----|----|任意值|

另外，IN等同於ANY, NOT IN 等同於ALL

## 6.5 插入查詢結果
- **將查詢結果寫入數據表**

```INSERT [INTO] tbl_name [(col_name，...)] SELECT ... ```

如將t1表中的查詢結果插入至t2表中
```INSERT t2(name) SELECT cate FROM t1 GROUP BY cate;```

暫時性表的創建
　　臨時表會在客戶端會話結束後自動刪除，但最好還是在用完之後顯性的刪除以節省內存；
　　臨時表隻對創建該表的客戶端可見；
　　臨時表的名字可以與某個已有的永久表的名字相同，此時永久表會隱藏起來，如果刪除的話也會先刪除臨時表，而永久表會顯現出來；
　　使用臨時表需註意：
　　 1. 如果客戶端程式在與伺服器的連接意外斷開時自動連接，那麼在重新連接之後，斷開前的臨時表將不復存在，如果之前用臨時表來＂隱藏＂某個與之同名的永久表，那麼這個永久表將成為現在使用的表；
　　 ２. 由於臨時表隻在創建它們的會話裡是可見的，因此他們與連接池機制一起使用時沒有什麼用處；
　　 ３．如果使用連接池或永久性連接，那麼當應用程式終止時，臨時表與ＭｙＳＱＬ伺服器之間的連接不一定被關閉，那些機制可能會保持處於連接狀態，以供其他客戶端使用．這表示當應用程式終止後不能假設臨時表會自動消失．

根據其他表或查詢結果來創建表
ＭｙＳＱＬ有兩種方法來創建表副本

     - ＣＲＥＡＴＥ　ＴＡＢＬＥ　．．．　ＬＩＫＥ會根據原有表創建一個新表，該表是原有表的一個空副本，它會把原有表的結構絲毫不差的復制過來，而且會保留各列的所有屬性，索引結構也照樣會被復制，不過，由於這個表是空的，因此還需要一條數據填充語句（ＩＮＳＥＲＴ　ＩＮＴＯ　．．．　ＳＥＬＥＣＴ），請註意，該語句不能根據原有表的列子集創建出新表，也不能使用除原有表以外的任何其他表裡的列．
     CREATE TABLE new_tbl_name LIKE tbl_name;
     INSERT INTO new_tbl_name SELECT * FROM tbl_name;
     - ＣＲＥＡＴＥ　ＴＡＢＬＥ　．．．　ＳＥＬＥＣＴ，默認情況下，這條語句不會復制所有的列屬性，如ＡＵＴＯ＿ＩＮＣＲＥＭＥＮＴ，也不會把原有表裡任何索引復制過去，因為結果集不會自己索引．不過該語句可完成創建表並填充數據這兩個操作．
      CREATE TABLE new_tbl_name SELECT * FROM tbl_name;
      由於該條語句的不足在於不會把原有數據的所有特征全部復制到新表的結構中去，因此在某些場合可以采用在語句的ＳＥＬＥＣＴ部分使用ＣＡＳＴ（）函數的方式，在新表裡強制使用某些特定的屬性，如：
       CREATE  TABLE mytbl SELECT 
         CAST(1 AS UNSIGEND) AS i,
         CAST(CURTIME() AS TIME) AS t,
         CAST(PI() AS DECIMAL(10,5)) AS d;
         允許強制轉換的類型包括：ＢＩＮＡＲＹ，ＣＨＡＲ，ＤＡＴＥ，ＤＡＴＥＴＩＭＥ，ＴＩＭＥ，ＳＩＧＮＥＤ，ＳＩＧＮＥＤ　ＩＮＴＥＧＥＲ，ＵＮＳＩＧＮＥＤ　ＩＮＴＥＧＥＲ　和　ＤＥＣＩＭＡＬ．

使用分區表 — 分區表的兩個好處為： – 表存儲可以分佈在多個設備上，可以通過Ｉ／Ｏ並行機制來縮短訪問時間． – 優化器可以把本地化搜尋限定在某個特定的分區，或者並行搜尋多個分區分區函數的分區依據： – 根據范圍來分區，適用這種方式的情形是，行包含的是一些可劃分為多個離散區間的值域，如日期，收入水平或重量． – 根據列表來分區，適用這種方式的情形是，每個分區都分別對應於某些明確的列表值，如郵政編碼，電話號碼區號，按地理區域劃分出的各實體的編號． – 根據散列值來分區，適用這種方式的情形是，根據從行鍵計算出的散列值，把行分佈到各個分區． **分區函數必須具有確定性，這樣，在分配行時，才會把相同的輸入值分配到同一個分區，有些函數不適合這個規則，如ＲＡＮＤ（）和ＮＯＷ（）** 索引 — MySQL提供瞭多種靈活的索引創建方法 – 可以對單個列或多個列建立索引，多列索引也被稱作復合索引； – 索引可以隻包含唯一值，也可以包含重復值； – 可以為同一個表創建多個索引，幫助優化對表的不同類型的查詢． – 對於除ＥＮＵＭ和ＳＥＴ以外的字符串類型，可以利用列的前綴創建索引，這種方法的優點在於對列的前綴而不是整個列進行索引，可以讓索引變的更小，訪問速度更快．ＭｙＳＱＬ中支持索引的引擎包括InnoDB,MyISAM,MEMORY. ＭｙＳＱＬ可以創建多種類型的索引： – 唯一索引 – 常規索引（非唯一性） – ＦＵＬＬＴＥＸＴ索引，這種索引適用於ＭyISAM和5.6.4版本以上的InnoDB – SPATIAL索引，這種索引隻適用於包含空間值的ＭｙＩＳＡＭ表． – ＨＡＳＨ索引，這是ＭＥＭＯＲＹ表的默認索引類型，不過可以通過創建ＢＴＲＥＥ索引來改寫它．添加索引的方式：

ALTER TABLE tbl_name ADD INDEX index_name(index_columns);
CREATE INDEX index_name ON tbl_name(index_columns);

刪除索引的方法

DROP INDEX index_name ON tbl_name;
DROP INDEX PRIMARY ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;

更改表結構 — 1. 更改列的數據類型如果在mytbl表裡,列i的數據類型為SMALLINT UNSIGNED，如果要把它改為MEDIUMINT UNSIGNED ，則：

ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;

為什麼在使用CHANGE語句時要把i寫兩遍呢，因為ＣＨＡＮＧＥ語句在修改數據類型的同時可以將該列重命名．

如果要修改列的字符集：

ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;

修改存儲引擎時應註意不同引擎之間功能是否兼容：

 - 包含有ＢＬＯＢ列的表不能轉換成MEMORY引擎；
 - 包含有外鍵的InnoDB表不能轉換為其他引擎；
 - 如果希望某個表的內容在伺服器重新啟動後依然存在，那麼不能使用ＭＥＭＯＲＹ引擎；

 重新命名表

ALTER TABLE tbl_name RENAME TO new_tbl_name;
RENAME TABLE tbl1_name TO new_tbl1_name,tbl2_name TO new_tbl2_name,…;

獲取資料庫元數據 == ＳＨＯＷ　命令 — 使用通配符：

SHOW COLUMNS FROM student LIKE ‘s%’;

表示在student表中打印s開頭的所有列

使用標識符：

SHOW COLUMNS FROM student WHERE Key = PRI;

表示在student表中打印主鍵列

“`

列