數據庫 -- 02

引擎介紹? ? ??

　　1.什么是引擎? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

　　MySQL中的數據用各種不同的技術存儲在文件（或者內存）中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。

　　例如，如果你在研究大量的臨時數據，你也許需要使用內存存儲引擎。內存存儲引擎能夠在內存中存儲所有的表格數據。又或者，你也許需要一個支持事務處理的數據庫(以確保事務處理不成功時數據的回退能力)。

　　這些不同的技術以及配套的相關功能在MySQL中被稱作存儲引擎(也稱作表類型)。

　　MySQL默認配置了許多不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用。你可以選擇適用于服務器、數據庫和表格的存儲引擎，以便在選擇如何存儲你的信息、如何檢索這些信息以及你需要你的數據結合什么性能和功能的時候為你提供最大的靈活性。

　　選擇如何存儲和檢索你的數據的這種靈活性是MySQL為什么如此受歡迎的主要原因。其它數據庫系統(包括大多數商業選擇)僅支持一種類型的數據存儲。

　　遺憾的是，其它類型的數據庫解決方案采取的“一個尺碼滿足一切需求”的方式意味著你要么就犧牲一些性能，要么你就用幾個小時甚至幾天的時間詳細調整你的數據庫。使用MySQL，我們僅需要修改我們使用的存儲引擎就可以了

　　2.MySQL支持哪些存儲引擎??

　　　　MySQL 5.6 支持的存儲引擎包括?InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事務安全表，其他存儲引擎都是非事務安全表。

　　　　　　　　　　　各種存儲引擎的特性

概覽

　　MySQL服務器采用了多層設計和獨立模塊，插件式存儲引擎體系結構，允許將存儲引擎加載到正在運新的MySQL服務器中，圖中的Pluggable Storage Engines部分。采用MySQL服務器體系結構，由于在存儲級別上（也就是Pluggable Storage Engines）提供了一致和簡單的應用模型和API，應用程序編程人員和DBA可不再考慮所有的底層實施細節。因此，盡管不同的存儲引擎具有不同的能力，應用程序是與之分離的。存儲引擎就司職與文件系統打交道了。

并發性：某些應用程序比其他應用程序具有很多的顆粒級鎖定要求（如行級鎖定）。
事務支持：并非所有的應用程序都需要事務，但對的確需要事務的應用程序來說，有著定義良好的需求，如ACID兼容等。
引用完整性：通過DDL定義的外鍵，服務器需要強制保持關聯數據庫的引用完整性。
物理存儲：它包括各種各樣的事項，從表和索引的總的頁大小，到存儲數據所需的格式，到物理磁盤。
索引支持：不同的應用程序傾向于采用不同的索引策略，每種存儲引擎通常有自己的編制索引方法，但某些索引方法（如B-tree索引）對幾乎所有的存儲引擎來說是共同的。
內存高速緩沖：與其他應用程序相比，不同的應用程序對某些內存高速緩沖策略的響應更好，因此，盡管某些內存高速緩沖對所有存儲引擎來說是共同的（如用于用戶連接的高速緩沖，MySQL的高速查詢高速緩沖等），其他高速緩沖策略僅當使用特殊的存儲引擎時才唯一定義。
性能幫助：包括針對并行操作的多I/O線程，線程并發性，數據庫檢查點，成批插入處理等。
其他目標特性：可能包括對地理空間操作的支持，對特定數據處理操作的安全限制等。

何種存儲引擎的特性

　　以上要求會在不同的需求中予以體現，通過單獨一個系統實現是不可能的，以上特點有些本身就是相互矛盾的，魚和熊掌的問題。對以上內容做些選擇，形成的存儲引擎就是一個插件引擎了，某些特定的需求可以使用。如下圖，部分現有的存儲引擎以及基本特點：

InnoDBMySql 5.6 版本默認的存儲引擎。InnoDB 是一個事務安全的存儲引擎，它具備提交、回滾以及崩潰恢復的功能以保護用戶數據。InnoDB 的行級別鎖定以及 Oracle 風格的一致性無鎖讀提升了它的多用戶并發數以及性能。InnoDB 將用戶數據存儲在聚集索引中以減少基于主鍵的普通查詢所帶來的 I/O 開銷。為了保證數據的完整性，InnoDB 還支持外鍵約束。MyISAMMyISAM既不支持事務、也不支持外鍵、其優勢是訪問速度快，但是表級別的鎖定限制了它在讀寫負載方面的性能，因此它經常應用于只讀或者以讀為主的數據場景。Memory在內存中存儲所有數據，應用于對非關鍵數據由快速查找的場景。Memory類型的表訪問數據非常快，因為它的數據是存放在內存中的，并且默認使用HASH索引，但是一旦服務關閉，表中的數據就會丟失BLACKHOLE黑洞存儲引擎，類似于 Unix 的 /dev/null，Archive 只接收但卻并不保存數據。對這種引擎的表的查詢常常返回一個空集。這種表可以應用于 DML 語句需要發送到從服務器，但主服務器并不會保留這種數據的備份的主從配置中。CSV它的表真的是以逗號分隔的文本文件。CSV 表允許你以 CSV 格式導入導出數據，以相同的讀和寫的格式和腳本和應用交互數據。由于 CSV 表沒有索引，你最好是在普通操作中將數據放在 InnoDB 表里，只有在導入或導出階段使用一下 CSV 表。NDB(又名 NDBCLUSTER)——這種集群數據引擎尤其適合于需要最高程度的正常運行時間和可用性的應用。注意：NDB 存儲引擎在標準 MySql 5.6 版本里并不被支持。目前能夠支持MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同樣基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正處于研發階段。Merge允許 MySql DBA 或開發者將一系列相同的 MyISAM 表進行分組，并把它們作為一個對象進行引用。適用于超大規模數據場景，如數據倉庫。Federated提供了從多個物理機上聯接不同的 MySql 服務器來創建一個邏輯數據庫的能力。適用于分布式或者數據市場的場景。Example這種存儲引擎用以保存闡明如何開始寫新的存儲引擎的 MySql 源碼的例子。它主要針對于有興趣的開發人員。這種存儲引擎就是一個啥事也不做的 "存根"。你可以使用這種引擎創建表，但是你無法向其保存任何數據，也無法從它們檢索任何索引。

各種搜索引擎介紹

?常用存儲引擎及使用場景

InnoDB

用于事務處理應用程序，支持外鍵和行級鎖。如果應用對事物的完整性有比較高的要求，在并發條件下要求數據的一致性，數據操作除了插入和查詢之外，還包括很多更新和刪除操作，那么InnoDB存儲引擎是比較合適的。InnoDB除了有效的降低由刪除和更新導致的鎖定，還可以確保事務的完整提交和回滾，對于類似計費系統或者財務系統等對數據準確要求性比較高的系統都是合適的選擇。

MyISAM

如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發性要求不高，那么可以選擇這個存儲引擎。

Memory

將所有的數據保存在內存中，在需要快速定位記錄和其他類似數據的環境下，可以提供極快的訪問。Memory的缺陷是對表的大小有限制，雖然數據庫因為異常終止的話數據可以正常恢復，但是一旦數據庫關閉，存儲在內存中的數據都會丟失。

?存儲引擎在 MySQL 中的使用

1.存儲引擎相關的 sql 語句

查看當前的默認存儲引擎:mysql> show variables like "default_storage_engine";查詢當前數據庫支持的存儲引擎mysql> show engines \G;

2.指定存儲引擎建表

　　1.在建表時指定

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;也可以使用alter table語句，修改一個已經存在的表的存儲引擎。mysql> alter table ai engine = innodb;

　　2.在配置文件中指定

#my.ini文件
[mysqld]
default-storage-engine=INNODB

MySQL 的工作流程

MySQL架構總共四層，在上圖中以虛線作為劃分。?
　　首先，最上層的服務并不是MySQL獨有的，大多數給予網絡的客戶端/服務器的工具或者服務都有類似的架構。比如：連接處理、授權認證、安全等。?
　　第二層的架構包括大多數的MySQL的核心服務。包括：查詢解析、分析、優化、緩存以及所有的內置函數（例如：日期、時間、數學和加密函數）。同時，所有的跨存儲引擎的功能都在這一層實現：存儲過程、觸發器、視圖等。

　　第三層包含了存儲引擎。存儲引擎負責MySQL中數據的存儲和提取。服務器通過API和存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明化。存儲引擎API包含十幾個底層函數，用于執行“開始一個事務”等操作。但存儲引擎一般不會去解析SQL（InnoDB會解析外鍵定義，因為其本身沒有實現該功能），不同存儲引擎之間也不會相互通信，而只是簡單的響應上層的服務器請求。

　　第四層包含了文件系統，所有的表結構和數據以及用戶操作的日志最終還是以文件的形式存儲在硬盤上。

表介紹

表就相當于文件，表中的一條記錄就相當于文件的一行內容，不同的是，表中的一條記錄有對應的標題，稱為表的字段

還記得我們之前寫過的‘員工信息表作業’么？存儲這員工信息的文件是這樣的：

id,name,age,sex,phone,job
1,Hahage,83,female,13651054608,IT
2,Xixijie,26,male,13304320533,Tearcher
3,Hehemei,25,male,13332353222,IT
4,Oodi,40,male,13332353333,IT

?如果把上面這個文件改成一張表,應該是下面這個樣子

id	name	age	sex	phone	job
1	Alex	83	female	13651054608	IT
2	Egon	26	male	13304320533	Teacher
3	nezha	25	male	13332353222	IT
4	boss_jin	40	male	13332353333

id,name,age,sex,phone,job稱為字段，其余的，一行內容稱為一條記錄?

創建表

#語法：
create table 表名(
字段名1 類型[(寬度) 約束條件],
字段名2 類型[(寬度) 約束條件],
字段名3 類型[(寬度) 約束條件]
);#注意：
1. 在同一張表中，字段名是不能相同
2. 寬度和約束條件可選
3. 字段名和類型是必須的

mysql> create database staff;
Query OK, 1 row affected (0.00 sec)mysql> use staff;
Database changed
mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));
Query OK, 0 rows affected (0.02 sec)mysql> show tables;
+-----------------+
| Tables_in_staff |
+-----------------+
| staff_info      |
+-----------------+
1 row in set (0.00 sec)mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> select id,name,sex from staff_info;
Empty set (0.00 sec)mysql> select * from staff_info;
Empty set (0.00 sec)

建表

mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT');
Query OK, 1 row affected (0.00 sec)mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher');
Query OK, 1 row affected (0.00 sec)mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from staff_info;
+------+----------+------+--------+-------------+---------+
| id   | name     | age  | sex    | phone       | job     |
+------+----------+------+--------+-------------+---------+
|    1 | Alex     |   83 | female | 13651054608 | IT      |
|    2 | Egon     |   26 | male   | 13304320533 | Teacher |
|    3 | nezha    |   25 | male   | 13332353222 | IT      |
|    4 | boss_jin |   40 | male   | 13332353333 | IT      |
+------+----------+------+--------+-------------+---------+
4 rows in set (0.00 sec)

?查看表結構

查看表結構有兩種方式：

describe [tablename];這種方法和desc [tablename];效果相同；可以查看當前的表結構

雖然desc命令可以查看表的定義，但是其輸出的信息還不夠全面，為了得到更全面的表定義信息，有時候就需要查看創建表的SQL語句，使用show create table語法。除了可以看到表定義之外，還可以看到engine（存儲引擎）和charset（字符集）等信息。（\G選項的含義是是的記錄能夠豎向排列，以便更好的顯示內容較長的記錄。）

mysql> describe staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> show create table staff_info\G;
*************************** 1. row ***************************Table: staff_info
Create Table: CREATE TABLE `staff_info` (`id` int(11) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`age` int(3) DEFAULT NULL,`sex` enum('male','female') DEFAULT NULL,`phone` bigint(11) DEFAULT NULL,`job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)ERROR: 
No query specified

?mysql 中的數據類型

MySQL支持所有標準SQL數值數據類型。

這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類型(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

MySQL支持的整數類型有TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數類型的存儲和范圍。

對于小數的表示，MYSQL分為兩種方式：浮點數和定點數。浮點數包括float(單精度)和double(雙精度),而定點數只有decimal一種，在mysql中以字符串的形式存放，比浮點數更精確，適合用來表示貨幣等精度高的數據。

BIT數據類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。?

類型	大小	范圍（有符號）	范圍（無符號）unsigned約束	用途
TINYINT	1 字節	(-128，127)	(0，255)	小整數值
SMALLINT	2 字節	(-32 768，32 767)	(0，65 535)	大整數值
MEDIUMINT	3 字節	(-8 388 608，8 388 607)	(0，16 777 215)	大整數值
INT或INTEGER	4 字節	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數值
BIGINT	8 字節	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數值
FLOAT	4 字節 float(255,30)	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度浮點數值
DOUBLE	8 字節 double(255,30)	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度浮點數值
DECIMAL	對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 double(65,30)	依賴于M和D的值	依賴于M和D的值	小數值

# 創建表一個是默認寬度的int，一個是指定寬度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)# 像t1中插入數據1，1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)# 可以看出結果上并沒有異常
mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)# 那么當我們插入了比寬度更大的值，會不會發生報錯呢？
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)# 答案是否定的，id2仍然顯示了正確的數值，沒有受到寬度限制的影響
mysql> select * from t1;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000001 |  00001 |
| 0000111111 | 111111 |
+------------+--------+
2 rows in set (0.00 sec)# 修改id1字段 給字段添加一個unsigned表示無符號
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)# 當給id1添加的數據大于214748364時，可以順利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)# 當給id2添加的數據大于214748364時，會報錯
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

int 整數示例

# 創建表的三個字段分別為float，double和decimal參數表示一共顯示5位，小數部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)# 向表中插入1.23，結果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)# 向表中插入1.234，會發現4都被截斷了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)# 向表中插入1.235發現數據雖然被截斷，但是遵循了四舍五入的規則
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
3 rows in set (0.00 sec)# 建新表去掉參數約束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)# 分別插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)# 發現decimal默認值是(10,0)的整數
mysql> select * from t3;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
1 row in set (0.00 sec)# 當對小數位沒有約束的時候，輸入超長的小數，會發現float和double的區別
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t3;
+---------+--------------------+------+
| id1     | id2                | id3  |
+---------+--------------------+------+
|   1.234 |              1.234 |    1 |
| 1.23556 | 1.2355555555555555 |    1 |
+---------+--------------------+------+
2 rows in set (0.00 sec)

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值范圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP類型有專有的自動更新特性，將在后面描述。

大小 (字節)	范圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	時分秒
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	年月日時分秒
TIMESTAMP	4	1970-01-01 00:00:00/2038 結束時間是第?2147483647?秒，北京時間?2038-1-19 11:14:07，格林尼治時間 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和時間值，時間戳

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

data/time/datetime示例

mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)# 插入數據null，會自動插入當前時間的時間
mysql> insert into t5 values (null);
Query OK, 1 row affected (0.00 sec)mysql> select * from t5;
+---------------------+
| id1                 |
+---------------------+
| 2018-09-21 14:56:50 |
+---------------------+
1 row in set (0.00 sec)#添加一列 默認值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t5 \G;
*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE `t5` (`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)ERROR: 
No query specified# 手動修改新的列默認值為當前時間
mysql> alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t5 \G;
*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE `t5` (`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)ERROR: 
No query specifiedmysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

timestamp示例

mysql> create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.01 sec)mysql> insert into t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)mysql> select * from t6;
+---------------------+
| t1                  |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
# timestamp時間的下限是19700101080001
mysql> insert into t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp時間的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql>

timestamp示例2

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)mysql> select * from t7;
+------+
| y    |
+------+
| 2018 |
+------+
1 row in set (0.00 sec)

year 示例

mysql> create table t8 (dt datetime);
Query OK, 0 rows affected (0.01 sec)mysql> insert into t8 values ('2018-9-26 12:20:10');
Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values ('2018/9/26 12+20+10');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 values ('20180926122010');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 values (20180926122010);
Query OK, 1 row affected (0.00 sec)mysql> select * from t8;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
4 rows in set (0.00 sec)

datetime示例

字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類型如何工作以及如何在查詢中使用這些類型。

類型	大小	用途
CHAR	0-255字節	定長字符串
VARCHAR	0-65535 字節	變長字符串
TINYBLOB	0-255字節	不超過 255 個字符的二進制字符串
TINYTEXT	0-255字節	短文本字符串
BLOB	0-65 535字節	二進制形式的長文本數據
TEXT	0-65 535字節	長文本數據
MEDIUMBLOB	0-16 777 215字節	二進制形式的中等長度文本數據
MEDIUMTEXT	0-16 777 215字節	中等長度文本數據
LONGBLOB	0-4 294 967 295字節	二進制形式的極大文本數據
LONGTEXT	0-4 294 967 295字節	極大文本數據

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

CHAR列的長度固定為創建表是聲明的長度,范圍(0-255);而VARCHAR的值是可變長字符串范圍(0-65535)。

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)mysql> insert into t9 values ('ab  ','ab  ');
Query OK, 1 row affected (0.00 sec)# 在檢索的時候char數據類型會去掉空格
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)# 來看看對查詢結果計算的長度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)# 給結果拼上一個加號會更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
1 row in set (0.00 sec)# 當存儲的長度超出定義的長度，會截斷
mysql> insert into t9 values ('abcd  ','abcd  ');
Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
| abcd | abcd |
+------+------+
2 rows in set (0.00 sec)

char varchar 示例

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進制字符串而不要非二進制字符串。也就是說，它們包含字節字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節的數值值。

BLOB 是一個二進制大對象，可以容納可變數量的數據。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在于可容納存儲范圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據實際情況選擇。

ENUM 和 SET類型

ENUM中文名稱叫枚舉類型，它的值范圍需要在創建表時通過枚舉方式顯示。ENUM只允許從值集合中選取單個值，而不能一次取多個值。

SET和ENUM非常相似，也是一個字符串對象，里面可以包含0-64個成員。根據成員的不同，存儲上也有所不同。set類型可以允許值集合中任意選擇1或多個元素進行組合。對超出范圍的內容將不允許注入，而對重復的值將進行自動去重。

類型

大小

用途

ENUM

對1-255個成員的枚舉需要1個字節存儲;

對于255-65535個成員，需要2個字節存儲;

最多允許65535個成員。

單選：選擇性別

SET

1-8個成員的集合，占1個字節

9-16個成員的集合，占2個字節

17-24個成員的集合，占3個字節

25-32個成員的集合，占4個字節

33-64個成員的集合，占8個字節

多選：興趣愛好

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)# 選擇enum('female','male')中的一項作為gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)# 不能同時插入'male,female'兩個值，也不能插入不屬于'male,female'的值
mysql> insert into t10 values ('nezha','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1mysql> create table t11 (name char(20),hobby set('抽煙','喝酒','燙頭','翻車'));
Query OK, 0 rows affected (0.01 sec)# 可以任意選擇set('抽煙','喝酒','燙頭','翻車')中的項，并自帶去重功能
mysql> insert into t11 values ('yuan','燙頭,喝酒,燙頭');
Query OK, 1 row affected (0.01 sec)mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| yuan | 喝酒,燙頭     |
+------+---------------+
1 row in set (0.00 sec)# 不能選擇不屬于set('抽煙','喝酒','燙頭','翻車')中的項，
mysql> insert into t11 values ('alex','燙頭,翻車,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

set enum 示例

?表的完整性約束

概覽

為了防止不符合規范的數據進入數據庫，在用戶對數據進行插入、修改、刪除等操作時，DBMS自動按照一定的約束條件對數據進行監測，使不符合規范的數據不能進入數據庫，以確保數據庫中存儲的數據正確、有效、相容。?

　　約束條件與數據類型的寬度一樣，都是可選參數，主要分為以下幾種：

# NOT NULL ：非空約束，指定某列不能為空； 
# UNIQUE : 唯一約束，指定某列或者幾列組合不能重復
# PRIMARY KEY ：主鍵，指定該列的值可以唯一地標識該列記錄
# FOREIGN KEY ：外鍵，指定該行記錄從屬于主表中的一條記錄，主要用于參照完整性

?NOT NULL

是否可空，null表示空，非字符串
not null - 不可空
null - 可空?

mysql> create table t12 (id int not null);
Query OK, 0 rows affected (0.02 sec)mysql> select * from t12;
Empty set (0.00 sec)mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)#不能向id列插入空元素。 
mysql> insert into t12 values (null);
ERROR 1048 (23000): Column 'id' cannot be nullmysql> insert into t12 values (1);
Query OK, 1 row affected (0.01 sec)

not null

?default?

我們約束某一列不為空，如果這一列中經常有重復的內容，就需要我們頻繁的插入，這樣會給我們的操作帶來新的負擔，于是就出現了默認值的概念。

默認值，創建列時可以指定默認值，當插入數據時如果未主動設置，則自動添加默認值

mysql> create table t13 (id1 int not null,id2 int not null default 222);
Query OK, 0 rows affected (0.01 sec)mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 222     |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)# 只向id1字段添加值，會發現id2字段會使用默認值填充
mysql> insert into t13 (id1) values (111);
Query OK, 1 row affected (0.00 sec)mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
+-----+-----+
1 row in set (0.00 sec)# id1字段不能為空，所以不能單獨向id2字段填充值；
mysql> insert into t13 (id2) values (223);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value# 向id1，id2中分別填充數據，id2的填充數據會覆蓋默認值
mysql> insert into t13 (id1,id2) values (112,223);
Query OK, 1 row affected (0.00 sec)mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
| 112 | 223 |
+-----+-----+
2 rows in set (0.00 sec)

not null + default 示例

設置嚴格模式：不支持對not null字段插入null值不支持對自增長字段插入”值不支持text字段有默認值直接在mysql中生效(重啟失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

not null 不生效

?unique

唯一約束,指定某列或者幾列組合不能重復

方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
unique(name)
);mysql> insert into department1 values(1,'IT','技術');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技術');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

unique 示例

mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

not null 和 unique 的組合

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #聯合唯一
);mysql> insert into service values-> (1,'nginx','192.168.0.10',80),-> (2,'haproxy','192.168.0.20',80),-> (3,'mysql','192.168.0.30',3306)-> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

聯合唯一

?primary key?

主鍵為了保證表中的每一條數據的該字段都是表格中的唯一值。換言之，它是用來獨一無二地確認一個表格中的每一行數據。?
主鍵可以包含一個字段或多個字段。當主鍵包含多個欄位時，稱為組合鍵 (Composite?Key),也可以叫聯合主鍵。
主鍵可以在建置新表格時設定 (運用 CREATE TABLE 語句)，或是以改變現有的表格架構方式設定 (運用 ALTER TABLE)。
主鍵必須唯一，主鍵值非空；可以是單一字段，也可以是多字段組合。

1.單字段主鍵

============單列做主鍵===============
#方法一：not null+unique
create table department1(
id int not null unique, #主鍵
name varchar(20) not null unique,
comment varchar(100)
);mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)#方法二：在某一個字段后用primary key
create table department2(
id int primary key, #主鍵
name varchar(20),
comment varchar(100)
);mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)#方法三：在所有字段后單獨定義primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id); #創建主鍵并為其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)# 方法四：給已經建成的表添加主鍵約束
mysql> create table department4(-> id int,-> name varchar(20),-> comment varchar(100));
Query OK, 0 rows affected (0.01 sec)mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> alter table department4 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

單字段主鍵

?2.多字段主鍵

==================多列做主鍵================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into service values-> ('172.16.45.10','3306','mysqld'),-> ('172.16.45.11','3306','mariadb')-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

多字段主鍵

?auto_increment

約束字段為自動增長，被約束的字段必須同時被key約束

#不指定id，則自動增長
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values-> ('egon'),-> ('alex')-> ;mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+#對于自增的字段，在用delete刪除后，再插入值，該字段仍按照刪除前的位置繼續增長
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)mysql> select * from student;
Empty set (0.00 sec)mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+#應該用truncate清空表，比起delete一條一條地刪除記錄，truncate是直接清空表，在刪除大表時用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
row in set (0.00 sec)

設置 auto_increment

?了解知識

#在創建完表后，修改自增字段的起始值
mysql> create table student(-> id int primary key auto_increment,-> name varchar(20),-> sex enum('male','female') default 'male'-> );mysql> alter table student auto_increment=3;mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  3 | egon | male |
+----+------+------+
row in set (0.00 sec)mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8#也可以創建表時指定auto_increment的初始值，注意初始值的設置為表選項，應該放到括號外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;#設置步長
sqlserver：自增步長基于表級別create table t1（id int。。。）engine=innodb,auto_increment=2 步長=2 default charset=utf8mysql自增的步長：show session variables like 'auto_inc%';#基于會話級別set session auth_increment_increment=2 #修改會話級別的步長#基于全局級別的set global auth_increment_increment=2 #修改全局級別的步長（所有會話都生效）#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻譯：如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值會被忽略 ，這相當于第一步步子就邁大了，扯著了蛋
比如：設置auto_increment_offset=3，auto_increment_increment=2mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'auto_incre%'; #需要退出重新登錄
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+步長:auto_increment_increment,起始偏移量:auto_increment_offset

offset偏移量

foreikey

多表：

假設我們要描述所有公司的員工，需要描述的屬性有這些：?工號姓名部門

公司有3個部門，但是有1個億的員工，那意味著部門這個字段需要重復存儲，部門名字越長，越浪費

解決方法：我們完全可以定義一個部門表然后讓員工信息表關聯該表，如何關聯，即foreign key

mysql> create table departments (dep_id int(4),dep_name varchar(11));
Query OK, 0 rows affected (0.02 sec)mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)# 創建外鍵不成功
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key # 設置dep_id非空，仍然不能成功創建外鍵
mysql> alter table departments modify dep_id int(4) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | NO   |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint# 當設置字段為unique唯一字段時，設置該字段為外鍵成功
mysql> alter table departments modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  | UNI | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
Query OK, 0 rows affected (0.02 sec)

創建外鍵的條件

#表類型必須是innodb存儲引擎，且被關聯的字段，即references指定的另外一個表的字段，必須保證唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;#dpt_id外鍵，關聯父表（department主鍵id），同步更新，同步刪除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id)
references department(id)
on delete cascade  # 級連刪除
on update cascade # 級連更新
)engine=innodb;#先往父表department中插入記錄
insert into department values
(1,'教質部'),
(2,'技術部'),
(3,'人力資源部');#再往子表employee中插入記錄
insert into employee values
(1,'yuan',1),
(2,'nezha',2),
(3,'egon',2),
(4,'alex',2),
(5,'wusir',3),
(6,'李沁洋',3),
(7,'皮卡丘',3),
(8,'程咬金',3),
(9,'程咬銀',3)
;#刪父表department，子表employee中對應的記錄跟著刪
mysql> delete from department where id=2;
Query OK, 1 row affected (0.00 sec)mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      3 |
|  6 | 李沁洋    |      3 |
|  7 | 皮卡丘    |      3 |
|  8 | 程咬金    |      3 |
|  9 | 程咬銀    |      3 |
+----+-----------+--------+
6 rows in set (0.00 sec)#更新父表department，子表employee中對應的記錄跟著改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬銀    |      2 |
+----+-----------+--------+
6 rows in set (0.00 sec)

外鍵操作示例

  . cascade方式
在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄 . set null方式
在父表上update/delete記錄時，將子表上匹配記錄的列設為null
要注意子表的外鍵列不能為not null  . No action方式
如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作  . Restrict方式
同no action, 都是立即檢查外鍵約束. Set default方式
父表有變更時,子表將外鍵列設置成一個默認的值 但Innodb不能識別

on delete(了解)

?修改表結構

語法：
1. 修改表名ALTER TABLE 表名 RENAME 新表名;2. 增加字段ALTER TABLE 表名ADD 字段名  數據類型 [完整性約束條件…],ADD 字段名  數據類型 [完整性約束條件…];3. 刪除字段ALTER TABLE 表名 DROP 字段名;4. 修改字段ALTER TABLE 表名 MODIFY  字段名 數據類型 [完整性約束條件…];ALTER TABLE 表名 CHANGE 舊字段名 新字段名 舊數據類型 [完整性約束條件…];ALTER TABLE 表名 CHANGE 舊字段名 新字段名 新數據類型 [完整性約束條件…];5.修改字段排列順序/在增加的時候指定字段位置ALTER TABLE 表名ADD 字段名  數據類型 [完整性約束條件…]  FIRST;ALTER TABLE 表名ADD 字段名  數據類型 [完整性約束條件…]  AFTER 字段名;ALTER TABLE 表名CHANGE 字段名  舊字段名 新字段名 新數據類型 [完整性約束條件…]  FIRST;ALTER TABLE 表名MODIFY 字段名  數據類型 [完整性約束條件…]  AFTER 字段名;

create table t(id int unique,name char(10) not null);#去掉null約束
alter table t modify name char(10) null;
# 添加null約束
alter table t modify name char(10) not null;# 去掉unique約束
alter table t drop index id;
# 添加unique約束
alter table t modify id int unique;alter處理null和unique約束

alter 操作非空和唯一(了解)

1、首先創建一個數據表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
); 
2、如果發現主鍵設置錯了,應該是id是主鍵,但如今表里已經有好多數據了,不能刪除表再重建了，僅僅能在這基礎上改動表結構。
先刪除主鍵
alter table table_test drop primary key;
然后再增加主鍵
alter table table_test add primary key(id);
注:在增加主鍵之前,必須先把反復的id刪除掉。

alter 操作主鍵(了解)

創建press表
CREATE TABLE `press` (`id` int(11) NOT NULL,`name` char(10) DEFAULT NULL,PRIMARY KEY (`id`)
) ；創建book表
CREATE TABLE `book` (`id` int(11) DEFAULT NULL,`bk_name` char(12) DEFAULT NULL,`press_id` int(11) NOT NULL,KEY `press_id` (`press_id`)
) ；為book表添加外鍵
alter table book add constraint fk_id foreign key(press_id) references press(id);刪除外鍵
alter table book drop foreign key fk_id;

為表添加外鍵(了解)

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)# 表重命名
mysql> alter table staff_info rename staff;
Query OK, 0 rows affected (0.00 sec)mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)# 刪除sex列
mysql> alter table staff drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| phone | bigint(11)  | YES  |     | NULL    |       |
| job   | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)# 添加列
mysql> alter table staff add sex enum('male','female');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0# 修改id的寬度
mysql> alter table staff modify id int(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)# 創建自增id主鍵
mysql> alter table staff modify id int(4) primary key auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(4)                | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| age   | int(3)                | YES  |     | NULL    |                |
| phone | bigint(11)            | YES  |     | NULL    |                |
| job   | varchar(11)           | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)# 刪除主鍵，可以看到刪除一個自增主鍵會報錯
mysql> alter table staff drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key# 需要先去掉主鍵的自增約束，然后再刪除主鍵約束
mysql> alter table staff modify id int(11);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | 0       |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0# 添加聯合主鍵
mysql> alter table staff add primary key (sname,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0# 刪除主鍵
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0# 創建主鍵id
mysql> alter table staff add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | 0       |       |
| sname | varchar(20)           | NO   |     |         |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | NO   |     | 0       |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)# 為主鍵添加自增屬性
mysql> alter table staff modify id int(4) auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(4)                | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)           | NO   |     |         |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| age   | int(3)                | NO   |     | 0       |                |
| phone | bigint(11)            | YES  |     | NULL    |                |
| job   | varchar(11)           | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

示例

?刪除表

DROP TABLE 表名;

?多表結構的創建與分析

如何找出兩張表之間的關系

分析步驟：
#1、先站在左表的角度去找
是否左表的多條記錄可以對應右表的一條記錄，如果是，則證明左表的一個字段foreign key 右表一個字段（通常是id）#2、再站在右表的角度去找
是否右表的多條記錄可以對應左表的一條記錄，如果是，則證明右表的一個字段foreign key 左表一個字段（通常是id）#3、總結：
#多對一：
如果只有步驟1成立，則是左表多對一右表
如果只有步驟2成立，則是右表多對一左表#多對多
如果步驟1和2同時成立，則證明這兩張表時一個雙向的多對一，即多對多,需要定義一個這兩張表的關系表來專門存放二者的關系#一對一:
如果1和2都不成立，而是左表的一條記錄唯一對應右表的一條記錄，反之亦然。這種情況很簡單，就是在左表foreign key右表的基礎上，將左表的外鍵字段設置成unique即可

建立表之間的關系

#一對多或稱為多對一
三張表：出版社，作者信息，書一對多（或多對一）：一個出版社可以出版多本書關聯方式：foreign key

=====================多對一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);insert into press(name) values
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識產權沒有用出版社')
;insert into book(name,press_id) values
('九陽神功',1),
('九陰真經',2),
('九陰白骨爪',2),
('獨孤九劍',3),
('降龍十巴掌',2),
('葵花寶典',3)
;

sql 示例

班級和學生
一個班級可以對應多個學生，但一個學生只能對應一個班級主機和機房
一個機房可以有多臺主機，但是一個主機只能屬于一個機房

其他示例

#多對多
三張表：出版社，作者信息，書多對多：一個作者可以寫多本書，一本書也可以有多個作者，雙向的一對多，即多對多關聯方式：foreign key+一張新的表

=====================多對多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);#這張表就存放作者表與書表的關系，即查詢二者的關系查這表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);#插入四個作者，id依次排開
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每個作者與自己的代表作如下
egon: 
九陽神功
九陰真經
九陰白骨爪
獨孤九劍
降龍十巴掌
葵花寶典
alex: 
九陽神功
葵花寶典
yuanhao:
獨孤九劍
降龍十巴掌
葵花寶典
wpq:
九陽神功insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

sql示例

服務和機器
一個服務可能被部署到多臺機器上，一臺機器上也可以部署多個服務學生和課程
一個學生可以選擇多門課程，一門課程也可以被多個學生選擇

其他示例

#一對一
兩張表：學生表和客戶表一對一：一個學生是一個客戶關聯方式：foreign key+unique

create table customer(-> id int primary key auto_increment,-> name varchar(20) not null,-> qq varchar(10) not null,-> phone char(16) not null-> );create table student(-> id int primary key auto_increment,-> class_name varchar(20) not null,-> customer_id int unique, #該字段一定要是唯一的-> foreign key(customer_id) references customer(id) #外鍵的字段一定要保證unique-> on delete cascade-> on update cascade-> );#增加客戶
mysql> insert into customer(name,qq,phone) values-> ('韓蕾','31811231',13811341220),-> ('楊瀾','123123123',15213146809),-> ('翁惠天','283818181',1867141331),-> ('楊宗河','283818181',1851143312),-> ('袁承明','888818181',1861243314),-> ('袁清','112312312',18811431230)mysql> #增加學生
mysql> insert into student(class_name,customer_id) values-> ('脫產1班',3),-> ('周末1期',4),-> ('周末1期',5)-> ;

sql 示例

例一：一個用戶只有一個博客用戶表：id  name1    egon2    alex3    wupeiqi博客表   fk+uniqueid url name_id1  xxxx   12  yyyy   33  zzz    2例二：一個管理員唯一對應一個用戶用戶表：id user  password1  egon    xxxx2  alex    yyyy管理員表：fk+uniqueid user_id password1   1      xxxxx2   2      yyyyy

其他示例