@[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表喊递。
類型 | 大小 | 范圍(有符號(hào)) | 范圍(無(wú)符號(hào))unsigned約束 | 用途 |
---|---|---|---|---|
tinyint | 1字節(jié) | (-128随闪,127) | (0,255) | 小整數(shù)值 |
smallint | 2 字節(jié) | (-32 768骚勘,32 767) | (0铐伴,65 535) | 大整數(shù)值 |
mediumint | 3字節(jié) | (-8 388 608,8 388 607) | (0俏讹,16 777 215) | 大整數(shù)值 |
int或integer | 4字節(jié) | (-2 147 483 648当宴,2 147 483 647) | (0,4 294 967 295) | 大整數(shù)值 |
bigint | 8字節(jié) | (-9 233 372 036 854 775 808泽疆,9 223 372 036 854 775 807) | (0户矢,18 446 744 073 709 551 615) | 極大整數(shù)值 |
float | 4字節(jié) | (-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) | 單精度,浮點(diǎn) |
double | 8 字節(jié) 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) | 雙精度,浮點(diǎn)數(shù)值 |
2. 日期時(shí)間類型
表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE哈肖、TIMESTAMP吻育、TIME和YEAR。
每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值淤井,當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值布疼。
TIMESTAMP類型有專有的自動(dòng)更新特性,將在后面描述币狠。
類型 | 大小(字節(jié)) | 范圍 | 格式 | 用途 |
---|---|---|---|---|
date | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 年月日 |
time | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 時(shí)分秒 |
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 | 年月日時(shí)分秒 |
timestamp | 4 | 1970-01-01 00:00:00/2038 結(jié)束時(shí)間是第 2147483647 秒游两,北京時(shí)間 2038-1-19 11:14:07,格林尼治時(shí)間 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和時(shí)間值漩绵,時(shí)間戳 |
3. 字符串類型
字符串類型指CHAR贱案、VARCHAR、BINARY止吐、VARBINARY宝踪、BLOB侨糟、TEXT、ENUM和SET瘩燥。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型秕重。
類型 | 大小(字節(jié)) | 用途 |
---|---|---|
char | 0-255字節(jié) | 定長(zhǎng)字符串 |
varchar | 0-65535 字節(jié) | 變長(zhǎng)字符串 |
tinyblob | 0-255字節(jié) | 不超過(guò) 255 個(gè)字符的二進(jìn)制字符串 |
tinytext | 0-255字節(jié) | 短文本字符串 |
blob | 0-65 535字節(jié) | 二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù) |
text | 0-65 535字節(jié) | 長(zhǎng)文本數(shù)據(jù) |
mediumblob | 0-16 777 215字節(jié) | 二進(jìn)制形式的中等長(zhǎng)度文本數(shù)據(jù) |
mediumtext | 0-16 777 215字節(jié) | 中等長(zhǎng)度文本數(shù)據(jù) |
longblob | 0-4 294 967 295字節(jié) | 二進(jìn)制形式的極大文本數(shù)據(jù) |
longtext | 0-4 294 967 295字節(jié) | 極大文本數(shù)據(jù) |
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)去重衫生。
類型 | 大小 | 用途 |
---|---|---|
ENUM | 對(duì)1-255個(gè)成員的枚舉需要1個(gè)字節(jié)存儲(chǔ);對(duì)于255-65535個(gè)成員裳瘪,需要2個(gè)字節(jié)存儲(chǔ);最多允許65535個(gè)成員。 | 單選:選擇性別 |
SET | 1-8個(gè)成員的集合罪针,占1個(gè)字節(jié);9-16個(gè)成員的集合彭羹,占2個(gè)字節(jié);17-24個(gè)成員的集合,占3個(gè)字節(jié);25;32個(gè)成員的集合泪酱,占4個(gè)字節(jié);33-64個(gè)成員的集合派殷,占8個(gè)字節(jié) | 多選:興趣愛(ài)好 |
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)
外鍵操作示例