mysql怎么獲取數(shù)據(jù)表字段enum類型的默認(rèn)值
公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來(lái)驚喜。創(chuàng)新互聯(lián)推出鳳臺(tái)免費(fèi)做網(wǎng)站回饋大家。
本節(jié)主要內(nèi)容：
MySQL數(shù)據(jù)類型之枚舉類型ENUM
MySQL數(shù)據(jù)庫(kù)提供針對(duì)字符串存儲(chǔ)的一種特殊數(shù)據(jù)類型：枚舉類型ENUM，這種數(shù)據(jù)類型可以給予我們更多提高性能、降低存儲(chǔ)容量和降低程序代碼理解的技巧，前面介紹了首先介紹了四種數(shù)據(jù)類型的特性總結(jié)，其后又分別介紹了布爾類型BOOL或稱布爾類型BOOLEAN，以及后續(xù)會(huì)再單獨(dú)介紹集合類型SET。
本文詳細(xì)介紹集合類型enum測(cè)試過(guò)程與總結(jié)，加深對(duì)mysql數(shù)據(jù)庫(kù)集合類型enum的理解記憶。
n 枚舉類型ENUM
a).數(shù)據(jù)庫(kù)表mysqlops_enum結(jié)構(gòu)
執(zhí)行數(shù)據(jù)庫(kù)表mysqlops_enum創(chuàng)建的SQL語(yǔ)句：
復(fù)制代碼代碼示例:
root@localhost : test 11:22:29 CREATE TABLE Mysqlops_enum(ID INT NOT NULL AUTO_INCREMENT,
- Job_type ENUM('DBA','SA','Coding Engineer','JavaScript','NA','QA','','other') NOT NULL,
- Work_City ENUM('shanghai','beijing','hangzhou','shenzhen','guangzhou','other') NOT NULL DEFAULT 'shanghai',
- PRIMARY KEY(ID)
- )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)
執(zhí)行查詢數(shù)據(jù)庫(kù)表mysqlops_enum結(jié)構(gòu)的SQL語(yǔ)句：
復(fù)制代碼代碼示例:
root@localhost : test 11:23:31 SHOW CREATE TABLE Mysqlops_enum\G
*************************** 1. row ***************************
Table: Mysqlops_enum
Create Table: CREATE TABLE `Mysqlops_enum` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Job_type` enum('DBA','SA','Coding Engineer','JavaScript','NA','QA','','other') NOT NULL,
`Work_City` enum('shanghai','beijing','hangzhou','shenzhen','guangzhou','other') NOT NULL DEFAULT 'shanghai',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
小結(jié)：
為方便測(cè)試枚舉類型，如何處理字段定義的默認(rèn)值、是否允許為NULL和空值的情況，我們定義了2個(gè)枚舉類型的字段名，經(jīng)過(guò)對(duì)比創(chuàng)建與查詢數(shù)據(jù)庫(kù)中表的結(jié)構(gòu)信息，沒(méi)有發(fā)現(xiàn)MySQL數(shù)據(jù)庫(kù)默認(rèn)修改任何信息。
b). 寫入不同類型的測(cè)試數(shù)據(jù)
寫入一條符合枚舉類型定義的記錄值：
復(fù)制代碼代碼示例:
root@localhost : test 11:22:35 INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(1,'QA','shanghai');
Query OK, 1 row affected (0.00 sec)
測(cè)試第二個(gè)枚舉類型字Work_City是否允許為空記錄值：
復(fù)制代碼代碼示例:
root@localhost : test 11:22:42 INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(2,'NA','');
Query OK, 1 row affected, 1 warning (0.00 sec)
root@localhost : test 11:22:48 SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'Work_City' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
測(cè)試第二個(gè)枚舉類型字段Work_City是否允許存儲(chǔ)NULL值：
復(fù)制代碼代碼示例:
root@localhost : test 11:22:53 INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(3,'Other',NULL);
ERROR 1048 (23000): Column 'Work_City' cannot be null
測(cè)試第一個(gè)枚舉類型字段Job_type是否可以存儲(chǔ)空白值：
復(fù)制代碼代碼示例:
root@localhost : test 11:22:59 INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(4,'','hangzhou');
Query OK, 1 row affected (0.00 sec)
測(cè)試第二個(gè)枚舉類型字段Job_City如何處理沒(méi)有在定義中描述的值域第一個(gè)枚舉類型字段Work_Type的默認(rèn)值沒(méi)指定情況下，會(huì)默認(rèn)填寫那個(gè)值：
復(fù)制代碼代碼示例:
root@localhost : test 11:23:06 INSERT INTO Mysqlops_enum(ID,Work_City) VALUES(5,'ningbo');
Query OK, 1 row affected, 1 warning (0.00 sec)
root@localhost : test 11:23:13 SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'Work_City' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
測(cè)試第二個(gè)枚舉類型字段未插入數(shù)據(jù)的情況下，是否能使用上字段定義中指定的默認(rèn)值：
復(fù)制代碼代碼示例:
root@localhost : test 11:23:17 INSERT INTO Mysqlops_enum(ID,Job_type) VALUES(6,'DBA');
Query OK, 1 row affected (0.00 sec)
MySQL 數(shù)據(jù)類型細(xì)分下來(lái)，大概有以下幾類：
數(shù)值，典型代表為 tinyint,int,bigint
浮點(diǎn)/定點(diǎn)，典型代表為 float,double,decimal 以及相關(guān)的同義詞
字符串，典型代表為 char,varchar
時(shí)間日期，典型代表為 date,datetime,time,timestamp
二進(jìn)制，典型代表為 binary,varbinary
位類型
枚舉類型
集合類型
以下內(nèi)容，我們?cè)诹硪黄恼陆榻B
大對(duì)象，比如 text,blob
json 文檔類型
一、數(shù)值類型（不是數(shù)據(jù)類型，別看錯(cuò)了）如果用來(lái)存放整數(shù)，根據(jù)范圍的不同，選擇不同的類型。
以上是幾個(gè)整數(shù)選型的例子。整數(shù)的應(yīng)用范圍最廣泛，可以用來(lái)存儲(chǔ)數(shù)字，也可以用來(lái)存儲(chǔ)時(shí)間戳，還可以用來(lái)存儲(chǔ)其他類型轉(zhuǎn)換為數(shù)字后的編碼，如 IPv4 等。示例 1用 int32 來(lái)存放 IPv4 地址，比單純用字符串節(jié)省空間。表 x1，字段 ipaddr，利用函數(shù) inet_aton，檢索的話用函數(shù) inet_ntoa。
查看磁盤空間占用，t3 占用最大，t1 占用最小。所以說(shuō)如果整數(shù)存儲(chǔ)范圍有固定上限，并且未來(lái)也沒(méi)有必要擴(kuò)容的話，建議選擇最小的類型，當(dāng)然了對(duì)其他類型也適用。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl總用量 3.0G3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd
二、浮點(diǎn)數(shù) / 定點(diǎn)數(shù)先說(shuō)?浮點(diǎn)數(shù)，float 和 double 都代表浮點(diǎn)數(shù)，區(qū)別簡(jiǎn)單記就是 float 默認(rèn)占 4 Byte。float(p) 中的 p 代表整數(shù)位最小精度。如果 p 24 則直接轉(zhuǎn)換為 double，占 8 Byte。p 最大值為 53，但最大值存在計(jì)算不精確的問(wèn)題。再說(shuō)?定點(diǎn)數(shù)，包括 decimal 以及同義詞 numeric，定點(diǎn)數(shù)的整數(shù)位和小數(shù)位分別存儲(chǔ)，有效精度最大不能超過(guò) 65。所以區(qū)別于 float 的在于精確存儲(chǔ)，必須需要精確存儲(chǔ)或者精確計(jì)算的最好定義為 decimal 即可。示例 3創(chuàng)建一張表 y1，分別給字段 f1,f2,f3 不同的類型。mysql-(ytt/3305)-create table y1(f1 float,f2 double,f3 decimal(10,2));Query OK, 0 rows affected (0.03 sec)
三、字符類型字符類型和整形一樣，用途也很廣。用來(lái)存儲(chǔ)字符、字符串、MySQL 所有未知的類型?？梢院?jiǎn)單說(shuō)是萬(wàn)能類型！
char(10) 代表最大支持 10 個(gè)字符存儲(chǔ)，varhar(10) 雖然和 char(10) 可存儲(chǔ)的字符數(shù)一樣多，不同的是 varchar 類型存儲(chǔ)的是實(shí)際大小，char 存儲(chǔ)的理論固定大小。具體的字節(jié)數(shù)和字符集相關(guān)。示例 4例如下面表 t4 ，兩個(gè)字段 c1,c2，分別為 char 和 varchar。mysql-(ytt/3305)-create table t4 (c1 char(20),c2 varchar(20));Query OK, 0 rows affected (0.02 sec)
所以在 char 和 varchar 選型上，要注意看是否合適的取值范圍。比如固定長(zhǎng)度的值，肯定要選擇 char；不確定的值，則選擇 varchar。
四、日期類型日期類型包含了 date,time,datetime,timestamp，以及 year。year 占 1 Byte，date 占 3 Byte?！?/p>
time,timestamp,datetime 在不包含小數(shù)位時(shí)分別占用 3 Byte,4 Byte,8 Byte；小數(shù)位部分另外計(jì)算磁盤占用，見下面表格。
請(qǐng)點(diǎn)擊輸入圖片描述
注意：timestamp 代表的時(shí)間戳是一個(gè) int32 存儲(chǔ)的整數(shù)，取值范圍為 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'；datetime 取值范圍為 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。?
綜上所述，日期這塊類型的選擇遵循以下原則：
1. 如果時(shí)間有可能超過(guò)時(shí)間戳范圍，優(yōu)先選擇 datetime。2. 如果需要單獨(dú)獲取年份值，比如按照年來(lái)分區(qū)，按照年來(lái)檢索等，最好在表中添加一個(gè) year 類型來(lái)參與。3. 如果需要單獨(dú)獲取日期或者時(shí)間，最好是單獨(dú)存放，而不是簡(jiǎn)單的用 datetime 或者 timestamp。后面檢索時(shí)，再加函數(shù)過(guò)濾，以免后期增加 SQL 編寫帶來(lái)額外消耗。
4. 如果有保存毫秒類似的需求，最好是用時(shí)間類型自己的特性，不要直接用字符類型來(lái)代替。MySQL 內(nèi)部的類型轉(zhuǎn)換對(duì)資源額外的消耗也是需要考慮的。
示例 5
建立表 t5，對(duì)這些可能需要的字段全部分離開，這樣以后寫 SQL 語(yǔ)句的時(shí)候就很容易了。
當(dāng)然了，這種情形占用額外的磁盤空間。如果想在易用性與空間占用量大這兩點(diǎn)來(lái)折中，可以用 MySQL 的虛擬列來(lái)實(shí)時(shí)計(jì)算。比如假設(shè) c5 字段不存在，想要得到 c5 的結(jié)果。mysql-(ytt/3305)-alter table t5 drop c5, add c5 year generated always as (year(c1)) virtual;Query OK, 1 row affected (2.46 sec)Records: 1 ?Duplicates: 0 ?Warnings: 0
五、二進(jìn)制類型
binary 和 varbinary 對(duì)應(yīng)了 char 和 varchar 的二進(jìn)制存儲(chǔ)，相關(guān)的特性都一樣。不同的有以下幾點(diǎn)：
binary(10)/varbinary(10) 代表的不是字符個(gè)數(shù)，而是字節(jié)數(shù)。
行結(jié)束符不一樣。char 的行結(jié)束符是 \0，binary 的行結(jié)束符是 0x00。
由于是二進(jìn)制存儲(chǔ)，所以字符編碼以及排序規(guī)則這類就直接無(wú)效了。
示例 6
來(lái)看這個(gè) binary 存取的簡(jiǎn)單示例，還是之前的變量 @a。
切記！這里要提前計(jì)算好 @a 占用的字節(jié)數(shù)，以防存儲(chǔ)溢出。
六、位類型
bit 為 MySQL 里存儲(chǔ)比特位的類型，最大支持 64 比特位， 直接以二進(jìn)制方式存儲(chǔ)，一般用來(lái)存儲(chǔ)狀態(tài)類的信息。比如，性別，真假等。具有以下特性：
1. 對(duì)于 bit(8) 如果單純存放 1 位，左邊以 0 填充 00000001。2. 查詢時(shí)可以直接十進(jìn)制來(lái)過(guò)濾數(shù)據(jù)。3. 如果此字段加上索引，MySQL 不會(huì)自己做類型轉(zhuǎn)換，只能用二進(jìn)制來(lái)過(guò)濾。
示例 7
創(chuàng)建表 c1, 字段性別定義一個(gè)比特位。mysql-(ytt/3305)-create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec)
mysql-(ytt/3305)-select cast(gender as unsigned) ?'f1' from c1;+------+| f1 ? |+------+| ? ?0 || ? ?1 |+------+2 rows in set (0.00 sec)
過(guò)濾數(shù)據(jù)也一樣，二進(jìn)制或者直接十進(jìn)制都行。mysql-(ytt/3305)-select conv(gender,16,10) as gender \???- from c1 where gender = b'1';?+--------+| gender |+--------+| 1??????|+--------+1 row in set (0.00 sec)????mysql-(ytt/3305)-select conv(gender,16,10) as gender \????- from c1 where gender = '1';+--------+| gender |+--------+| 1??????|+--------+1 row in set (0.00 sec)
其實(shí)這樣的場(chǎng)景，也可以定義為 char(0)，這也是類似于 bit 非常優(yōu)化的一種用法。
mysql-(ytt/3305)-create table c2(gender char(0));Query OK, 0 rows affected (0.03 sec)
那現(xiàn)在我給表 c1 簡(jiǎn)單的造點(diǎn)測(cè)試數(shù)據(jù)。
mysql-(ytt/3305)-select count(*) from c1;+----------+| count(*) |+----------+| 33554432 |+----------+1 row in set (1.37 sec)
把 c1 的數(shù)據(jù)全部插入 c2。
mysql-(ytt/3305)-insert into c2 select if(gender = 0,'',null) from c1;Query OK, 33554432 rows affected (2 min 18.80 sec)Records: 33554432 ?Duplicates: 0 ?Warnings: 0
兩張表的磁盤占用差不多。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl總用量 1.9G4085684 933M -rw-r----- 1 mysql mysql 932M 12月 11 10:16 c1.ibd4082686 917M -rw-r----- 1 mysql mysql 916M 12月 11 10:22 c2.ibd
檢索方式稍微有些不同，不過(guò)效率也差不多。所以說(shuō)，字符類型不愧為萬(wàn)能類型。
七、枚舉類型
枚舉類型，也即 enum。適合提前規(guī)劃好了所有已經(jīng)知道的值，且未來(lái)最好不要加新值的情形。枚舉類型有以下特性：
1. 最大占用 2 Byte。2. 最大支持 65535 個(gè)不同元素。3. MySQL 后臺(tái)存儲(chǔ)以下標(biāo)的方式，也就是 tinyint 或者 smallint 的方式，下標(biāo)從 1 開始。4. 排序時(shí)按照下標(biāo)排序，而不是按照里面元素的數(shù)據(jù)類型。所以這點(diǎn)要格外注意。
示例 8
創(chuàng)建表 t7。mysql-(ytt/3305)-create table t7(c1 enum('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));Query OK, 0 rows affected (0.03 sec)
八、集合類型
集合類型 SET 和枚舉類似，也是得提前知道有多少個(gè)元素。SET 有以下特點(diǎn)：
1. 最大占用 8 Byte，int64。2. 內(nèi)部以二進(jìn)制位的方式存儲(chǔ)，對(duì)應(yīng)的下標(biāo)如果以十進(jìn)制來(lái)看，就分別為 1,2,4,8，...，pow(2,63)。3. 最大支持 64 個(gè)不同的元素，重復(fù)元素的插入，取出來(lái)直接去重。4. 元素之間可以組合插入，比如下標(biāo)為 1 和 2 的可以一起插入，直接插入 3 即可。
示例 9
定義表 c7 字段 c1 為 set 類型，包含了 8 個(gè)值，也就是下表最大為 pow(2,7)。
mysql-(ytt/3305)-create table c7(c1 set('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));Query OK, 0 rows affected (0.02 sec)
插入 1 到 128 的所有組合。
mysql-(ytt/3305)-INSERT INTO c7WITH RECURSIVE ytt_number (cnt) AS ( ? ? ? ?SELECT 1 AS cnt ? ? ? ?UNION ALL ? ? ? ?SELECT cnt + 1 ? ? ? ?FROM ytt_number ? ? ? ?WHERE cnt pow(2, 7) ? ?)SELECT *FROM ytt_number;Query OK, 128 rows affected (0.01 sec)Records: 128 ?Duplicates: 0 ?Warnings: 0
九、數(shù)據(jù)類型在存儲(chǔ)函數(shù)中的用法
函數(shù)里除了顯式聲明的變量外，默認(rèn) session 變量的數(shù)據(jù)類型很弱，隨著給定值的不同隨意轉(zhuǎn)換。
示例 10
定義一個(gè)函數(shù)，返回兩個(gè)給定參數(shù)的乘積。定義里有兩個(gè)變量，一個(gè)是 v_tmp 顯式定義為 int64，另外一個(gè) @vresult 隨著給定值的類型隨意變換類型。
簡(jiǎn)單調(diào)用下。
mysql-(ytt/3305)-select ytt_sample_data_type(1111,222) 'result';+--------------------------+| result ? ? ? ? ? ? ? ? ? |+--------------------------+| The result is: '246642'. |+--------------------------+1 row in set (0.00 sec)
總結(jié)
本篇把 MySQL 基本的數(shù)據(jù)類型做了簡(jiǎn)單的介紹，并且用了一些容易理解的示例來(lái)梳理這些類型。我們?cè)趯?shí)際場(chǎng)景中，建議選擇適合最合適的類型，不建議所有數(shù)據(jù)類型簡(jiǎn)單的最大化原則。比如能用 varchar(100)，不用 varchar(1000)。
根據(jù)用戶定義的枚舉值與分片節(jié)點(diǎn)映射文件，直接定位目標(biāo)分片。
用戶在rule.xml中配置枚舉值文件路徑和分片索引是字符串還是數(shù)字，DBLE在啟動(dòng)時(shí)會(huì)將枚舉值文件加載到內(nèi)存中，形成一個(gè)映射表
在DBLE的運(yùn)行過(guò)程中，用戶訪問(wèn)使用這個(gè)算法的表時(shí)，WHERE子句中的分片索引值會(huì)被提取出來(lái)，直接查映射表得到分片編號(hào)
與MyCat的類似分片算法對(duì)比
中間件
DBLE
MyCat
分片算法種類 ? ?enum 分區(qū)算法 ? ?分片枚舉 ?
兩種中間件的枚舉分片算法使用上無(wú)差別。
開發(fā)注意點(diǎn)
【分片索引】1. 整型數(shù)字（可以為負(fù)數(shù)）或字符串（（不含=和換行符）
【分片索引】2. 枚舉值之間不能重復(fù)
Male=0Male=1
或者
123=1123=2
會(huì)導(dǎo)致分片策略加載出錯(cuò)
【分片索引】3. 不同枚舉值可以映射到同一個(gè)分片上
Mr=0Mrs=1Miss=1Ms=1123=0
運(yùn)維注意點(diǎn)
【擴(kuò)容】1. 增加枚舉值無(wú)需數(shù)據(jù)再平衡
【擴(kuò)容】2. 增加一個(gè)枚舉值的分片數(shù)量數(shù)時(shí)，需要對(duì)局部數(shù)據(jù)進(jìn)行遷移
【縮容】1. 減少枚舉值需要數(shù)據(jù)再平衡
【縮容】2. 減少一個(gè)枚舉值的分片數(shù)量數(shù)時(shí)，需要對(duì)局部數(shù)據(jù)進(jìn)行遷移
配置注意點(diǎn)
【配置項(xiàng)】1. 在 rule.xml 中，可配置項(xiàng)為?property name="defaultNode" 、property name="mapFile" 和 property name="type"
【配置項(xiàng)】2. 在 rule.xml 中配置?property name="defaultNode"?標(biāo)簽，非必須配置項(xiàng)，不配置該項(xiàng)的話，用戶的分片索引值沒(méi)落在 mapFile 定義的范圍時(shí)，DBLE 會(huì)報(bào)錯(cuò)；若需要配置，必須為非負(fù)整數(shù)，用戶的分片索引值沒(méi)落在 mapFile 定義的范圍時(shí)，DBLE 會(huì)路由至這個(gè)值的 MySQL 分片
【配置項(xiàng)】3. 在 rule.xml 中配置 property name="mapFile"?標(biāo)簽，范圍映射文件的路徑：若在映射文件在 DBLE_HOME/conf 或其中，則可以使用相對(duì)路徑的形式配置，例如，映射文件是 DBLE_HOME/conf/map/table_map.txt 時(shí)，配置值就可以簡(jiǎn)寫為 map/table_map.txt；映射文件在 DBLE_HOME/conf 目錄以外時(shí)，需要使用絕對(duì)路徑，但這種做法需要考慮用戶權(quán)限等問(wèn)題，因此不建議把映射文件放在 DBLE_HOME/conf 外。
【配置項(xiàng)】4. 編輯 mapFile 所配置的文件
記錄格式為：枚舉值=分片編號(hào)
枚舉值可以是整型數(shù)字，或任意字符（除了=和換行符），分片編號(hào)必須是非負(fù)整型數(shù)字，記錄之間以換行分隔，一行僅能有一條記錄，枚舉值不能夠是“DEFAULT_NODE”這個(gè)字符串，允許以“//”和“#”在行首來(lái)注釋該行
【配置項(xiàng)】5. 在 rule.xml 中配置 property name="type"?標(biāo)簽；type 必須為整型；取值為 0 時(shí)，mapFile 的枚舉值必須為整型；取值為非 0 時(shí)，mapFile 的枚舉值可以是任意字符（除了=和換行符）
SQL 基礎(chǔ)應(yīng)用及information_schema
1.SQL（結(jié)構(gòu)化查詢語(yǔ)句）介紹
SQL標(biāo)準(zhǔn)：SQL 92? SQL99
5.7版本后啟用SQL_Mode 嚴(yán)格模式
2.SQL作用
SQL 用來(lái)管理和操作MySQL內(nèi)部的對(duì)象
SQL對(duì)象：
庫(kù)：庫(kù)名，庫(kù)屬性
表：表名，表屬性，列名，記錄，數(shù)據(jù)類型，列屬性和約束
3.SQL語(yǔ)句的類型
DDL：數(shù)據(jù)定義語(yǔ)言? ? data definition language
DCL：數(shù)據(jù)控制語(yǔ)言? ? data control language
DML：數(shù)據(jù)操作語(yǔ)言? ? data manipulation language
DQL：數(shù)據(jù)查詢語(yǔ)言? ? data query language
4.數(shù)據(jù)類型
4.1 作用：
控制數(shù)據(jù)的規(guī)范性，讓數(shù)據(jù)有具體含義，在列上進(jìn)行控制
4.2.種類
4.2.1 字符串
char(32)
定長(zhǎng)長(zhǎng)度為32的字符串。存儲(chǔ)數(shù)據(jù)時(shí)，一次性提供32字符長(zhǎng)度的存儲(chǔ)空間，存不滿，用空格填充。
varchar(32)：
可變長(zhǎng)度的字符串類型。存數(shù)據(jù)時(shí)，首先進(jìn)行字符串長(zhǎng)度判斷，按需分配存儲(chǔ)空間
會(huì)單獨(dú)占用一個(gè)字節(jié)來(lái)記錄此次的字符長(zhǎng)度
超過(guò)255之后，需要兩個(gè)字節(jié)長(zhǎng)度記錄字符長(zhǎng)度。
面試題：
1. char 和varchar的區(qū)別？?
（1） 255? 65535
（2） 定長(zhǎng)（固定存儲(chǔ)空間）? 變長(zhǎng)（按需）
2. char和varchar 如何選擇？
（1） char類型，固定長(zhǎng)度的字符串列，比如手機(jī)號(hào)，身份證號(hào)，銀行卡號(hào)，性別等
（2） varchar類型，不確定長(zhǎng)度的字符串，可以使用。
3. enum 枚舉類型
enum('bj','sh','sz','cq','hb',......)
數(shù)據(jù)行較多時(shí)，會(huì)影響到索引的應(yīng)用
注意：數(shù)字類禁止使用enum類型
4.2.2 數(shù)字
1. tinyint
2. int
4.2.3 時(shí)間
1. timestamp
2. datetime
4.2.4 二進(jìn)制
5. 表屬性
存儲(chǔ)引擎 ：engine =? InnoDB
字符集? ：charset = utf8mb4
utf8? ? 中文? 三個(gè)字節(jié)長(zhǎng)度
utf8mb4 中文? 四個(gè)字節(jié)長(zhǎng)度? ? 才是真正的utf8
支持emoji字符
排序規(guī)則（校對(duì)規(guī)則） collation
針對(duì)英文字符串大小寫問(wèn)題
6. 列的屬性和約束
6.1 主鍵： primary key (PK)
說(shuō)明：
唯一
非空
數(shù)字列，整數(shù)列，無(wú)關(guān)列，自增的.
聚集索引列？
是一種約束，也是一種索引類型，在一張表中只能有一個(gè)主鍵。
6.2 非空： Not NULL
說(shuō)明：
我們建議，對(duì)于普通列來(lái)講，盡量設(shè)置not null
默認(rèn)值 default ： 數(shù)字列的默認(rèn)值使用0 ,字符串類型，設(shè)置為一個(gè)nil null
6.3 唯一：unique
不能重復(fù)
6.4 自增 auto_increment
針對(duì)數(shù)字列，自動(dòng)生成順序值
6.5 無(wú)符號(hào) unsigned
針對(duì)數(shù)字列?
6.6 注釋 comment
7. SQL語(yǔ)句應(yīng)用
7.1 DDL：數(shù)據(jù)定義語(yǔ)言
7.1.1 庫(kù)?
（1）建庫(kù)
mysql create database oldguo charset utf8mb4;
mysql show databases;
mysql show create database oldguo;
（2）改庫(kù)
mysql alter database oldguo1 charset utf8mb4;
（3）刪庫(kù)
mysql drop database oldguo1;
7.1.2 表
（0）建表建庫(kù)規(guī)范：
1、庫(kù)名和表名是小寫字母
為啥？
開發(fā)和生產(chǎn)平臺(tái)可能會(huì)出現(xiàn)問(wèn)題。
2、不能以數(shù)字開頭
3、不支持-? 支持_
4、內(nèi)部函數(shù)名不能使用
5、名字和業(yè)務(wù)功能有關(guān)（his,jf,yz,oss,erp,crm...）
（1）建表
create table oldguo (
ID int not null primary key AUTO_INCREMENT comment '學(xué)號(hào)',
name varchar(255) not null comment '姓名',
age tinyint unsigned not null default 0 comment '年齡',
gender enum('m','f','n') NOT null default 'n' comment '性別'
)charset=utf8mb4 engine=innodb;
（2）改表
1. 改表結(jié)構(gòu)
-- 例子：
-- 在上表中添加一個(gè)手機(jī)號(hào)列15801332370.（重點(diǎn)*****）
-- alter table oldguo add telnum char(11) not null unique comment '手機(jī)號(hào)';
-- 練習(xí)：
-- 添加一個(gè)狀態(tài)列
ALTER TABLE oldguo ADD state TINYINT? UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態(tài)列';
-- 查看列的信息
DESC? oldguo;
-- 刪除state列(不代表生產(chǎn)操作)
ALTER TABLE oldguo DROP state;
-- online-DDL : pt-osc (自己研究下***)
-- 在name后添加 qq 列 varchar(255)
ALTER TABLE oldguo ADD qq VARCHAR(255) NOT NULL UNIQUE? COMMENT 'qq' AFTER NAME;
-- 練習(xí) 在name 之前添加wechat列
ALTER TABLE oldguo ADD wechat VARCHAR(255) NOT NULL UNIQUE COMMENT '微信' AFTER ID;
-- 在首列上添加 學(xué)號(hào)列：sid(linux58_00001)
ALTER TABLE oldguo ADD sid VARCHAR(255) NOT NULL UNIQUE COMMENT '學(xué)生號(hào)' FIRST;
-- 修改name數(shù)據(jù)類型的屬性
ALTER TABLE oldguo? MODIFY NAME VARCHAR(128)? NOT NULL ;
DESC oldguo;
-- 將gender 改為 gg 數(shù)據(jù)類型改為 CHAR 類型
ALTER TABLE oldguo? CHANGE gender gg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC oldguo;
7.2 DML 數(shù)據(jù)操作語(yǔ)言
7.2.1 INSERT
--- 最簡(jiǎn)單的方法插入數(shù)據(jù)
DESC oldguo;
INSERT INTO oldguo VALUES(1,'oldguo','22654481',18);
--- 最規(guī)范的方法插入數(shù)據(jù)(重點(diǎn)記憶)
INSERT INTO oldguo(NAME,qq,age) VALUES ('oldboy','74110',49);
--- 查看表數(shù)據(jù)（不代表生產(chǎn)操作）
SELECT * FROM oldguo;
7.2.2 UPDATE (注意謹(jǐn)慎操作?。。?！)
UPDATE oldguo SET qq='123456' WHERE id=5 ;
7.2.3? DELETE (注意謹(jǐn)慎操作！?。?！)
DELETE FROM oldguo WHERE id=5;
7.2.4 生產(chǎn)需求：將一個(gè)大表全部數(shù)據(jù)清空
DELETE FROM oldguo;
TRUNCATE TABLE oldguo;
DELETE 和 TRUNCATE 區(qū)別
1. DELETE 邏輯逐行刪除，不會(huì)降低自增長(zhǎng)的起始值。
效率很低，碎片較多，會(huì)影響到性能
2. TRUNCATE ，屬于物理刪除，將表段中的區(qū)進(jìn)行清空，不會(huì)產(chǎn)生碎片。性能較高。
7.2.5 生產(chǎn)需求：使用update替代delete，進(jìn)行偽刪除
1. 添加狀態(tài)列state (0代表存在，1代表刪除)
ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;
2. 使用update模擬delete
DELETE FROM oldguo WHERE id=6;
替換為
UPDATE oldguo SET state=1 WHERE id=6;
SELECT * FROM oldguo ;
3. 業(yè)務(wù)語(yǔ)句修改
SELECT * FROM oldguo ;
改為
SELECT * FROM oldguo WHERE state=0;
重慶分公司
重慶分公司
