@[toc]

1. 數(shù)據(jù)類型

1.數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。

這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER太防、SMALLINT另患、DECIMAL和NUMERIC)莫秆，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)极舔。

關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞链瓦。

MySQL支持的整數(shù)類型有TINYINT姆怪、MEDIUMINT和BIGINT。下面的表顯示了需要的每個(gè)整數(shù)類型的存儲(chǔ)和范圍澡绩。

對(duì)于小數(shù)的表示稽揭，MYSQL分為兩種方式：浮點(diǎn)數(shù)和定點(diǎn)數(shù)。浮點(diǎn)數(shù)包括float(單精度)和double(雙精度),而定點(diǎn)數(shù)只有decimal一種肥卡，在mysql中以字符串的形式存放溪掀，比浮點(diǎn)數(shù)更精確，適合用來(lái)表示貨幣等精度高的數(shù)據(jù)步鉴。

BIT數(shù)據(jù)類型保存位字段值揪胃，并且支持MyISAM璃哟、MEMORY、InnoDB和BDB表喊递。

2. 日期時(shí)間類型

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE哈肖、TIMESTAMP吻育、TIME和YEAR。

每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值淤井，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值布疼。

TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述币狠。

3. 字符串類型

字符串類型指CHAR贱案、VARCHAR、BINARY止吐、VARBINARY宝踪、BLOB侨糟、TEXT、ENUM和SET瘩燥。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型秕重。

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同厉膀。它們的最大長(zhǎng)度和是否尾部空格被保留等方面也不同溶耘。在存儲(chǔ)或檢索過(guò)程中不進(jìn)行大小寫轉(zhuǎn)換。

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

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR汰具，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串卓鹿。也就是說(shuō)菱魔，它們包含字節(jié)字符串而不是字符字符串。這說(shuō)明它們沒(méi)有字符集吟孙，并且排序和比較基于列值字節(jié)的數(shù)值值澜倦。

BLOB 是一個(gè)二進(jìn)制大對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)杰妓。有 4 種 BLOB 類型：TINYBLOB藻治、BLOB、MEDIUMBLOB 和 LONGBLOB巷挥。它們區(qū)別在于可容納存儲(chǔ)范圍不同桩卵。

有 4 種 TEXT 類型：TINYTEXT、TEXT倍宾、MEDIUMTEXT 和 LONGTEXT雏节。對(duì)應(yīng)的這 4 種 BLOB 類型，可存儲(chǔ)的最大長(zhǎng)度不同高职，可根據(jù)實(shí)際情況選擇钩乍。

4. ENUM和SET類型

ENUM中文名稱叫枚舉類型，它的值范圍需要在創(chuàng)建表時(shí)通過(guò)枚舉方式顯示怔锌。ENUM只允許從值集合中選取單個(gè)值寥粹，而不能一次取多個(gè)值。

SET和ENUM非常相似埃元，也是一個(gè)字符串對(duì)象涝涤，里面可以包含0-64個(gè)成員。根據(jù)成員的不同岛杀，存儲(chǔ)上也有所不同妄痪。set類型可以允許值集合中任意選擇1或多個(gè)元素進(jìn)行組合。對(duì)超出范圍的內(nèi)容將不允許注入楞件，而對(duì)重復(fù)的值將進(jìn)行自動(dòng)去重衫生。

2. 常見(jiàn)約束

1. 非空（not null）

NOT NULL ：非空約束，指定某列不能為空墓阀；

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

2. 唯一 （unique）

唯一約束斯撮，指定某列或者幾列組合不能重復(fù)

3. 主鍵 （primary key）

主鍵為了保證表中的每一條數(shù)據(jù)的該字段都是表格中的唯一值经伙。換言之，它是用來(lái)獨(dú)一無(wú)二地確認(rèn)一個(gè)表格中的每一行數(shù)據(jù)勿锅。
主鍵可以包含一個(gè)字段或多個(gè)字段帕膜。當(dāng)主鍵包含多個(gè)欄位時(shí)，稱為組合鍵 (Composite Key),也可以叫聯(lián)合主鍵溢十。
主鍵可以在建置新表格時(shí)設(shè)定 (運(yùn)用 CREATE TABLE 語(yǔ)句)垮刹，或是以改變現(xiàn)有的表格架構(gòu)方式設(shè)定 (運(yùn)用 ALTER TABLE)。
主鍵必須唯一张弛，主鍵值非空荒典；可以是單一字段，也可以是多字段組合吞鸭。

• 單字段主鍵

============單列做主鍵===============
#方法一：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)

#方法二：在某一個(gè)字段后用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)

#方法三：在所有字段后單獨(dú)定義primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id); #創(chuàng)建主鍵并為其命名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)

# 方法四：給已經(jīng)建成的表添加主鍵約束
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    |       |
+---------+--------------+------+-----+---------+-------+
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: 0

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| 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)

單字段主鍵

• 多字段主鍵

==================多列做主鍵================
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    |       |
+--------------+-------------+------+-----+---------+-------+
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: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

多字段主鍵

4. 自增 AUTO_INCREMENT

約束字段為自動(dòng)增長(zhǎng)寺董，被約束的字段必須同時(shí)被key約束

#不指定id，則自動(dòng)增長(zhǎng)
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 |
+----+------+--------+


#對(duì)于自增的字段瞒大，在用delete刪除后螃征，再插入值，該字段仍按照刪除前的位置繼續(xù)增長(zhǎng)
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 |
+----+------+------+

#應(yīng)該用truncate清空表透敌，比起delete一條一條地刪除記錄盯滚，truncate是直接清空表，在刪除大表時(shí)用它
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)

設(shè)置auto_increment

5. 外鍵 （foreign key）

多表 ：

假設(shè)我們要描述所有公司的員工酗电，需要描述的屬性有這些 ： 工號(hào) 姓名 部門

公司有3個(gè)部門魄藕，但是有1個(gè)億的員工，那意味著部門這個(gè)字段需要重復(fù)存儲(chǔ)撵术，部門名字越長(zhǎng)背率，越浪費(fèi)

解決方法： 我們完全可以定義一個(gè)部門表 然后讓員工信息表關(guān)聯(lián)該表，如何關(guān)聯(lián)，即foreign key

• 創(chuàng)造外鍵的條件

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    |       |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

# 創(chuàng)建外鍵不成功
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 

# 設(shè)置dep_id非空寝姿，仍然不能成功創(chuàng)建外鍵
mysql> alter table departments modify dep_id int(4) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | NO   |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
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

# 當(dāng)設(shè)置字段為unique唯一字段時(shí)交排，設(shè)置該字段為外鍵成功
mysql> alter table departments modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  | UNI | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
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)

創(chuàng)造外鍵的條件

• 外鍵操作示例

#表類型必須是innodb存儲(chǔ)引擎，且被關(guān)聯(lián)的字段饵筑，即references指定的另外一個(gè)表的字段埃篓，必須保證唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外鍵，關(guān)聯(lián)父表（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  # 級(jí)連刪除
on update cascade # 級(jí)連更新
)engine=innodb;


#先往父表department中插入記錄
insert into department values
(1,'教質(zhì)部'),
(2,'技術(shù)部'),
(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中對(duì)應(yīng)的記錄跟著刪
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 |
+----+-----------+--------+
rows in set (0.00 sec)


#更新父表department玄帕，子表employee中對(duì)應(yīng)的記錄跟著改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬銀    |      2 |
+----+-----------+--------+
rows in set (0.00 sec)

外鍵操作示例