Mysql修飾符
not null
有not null 來修飾的話，往表裡添加默認值的時候：
數值類型的話，添加的是0
字符串類型的話，添加的是空
如果數據類型是時間戳類型，添加的默認值是當前時間
枚舉類型，添加的是第一個預先定義的值
www.2cto.com
驗證舉例：
mysql> create table t6(a int not null,b char(10) not null,c timestamp not null,d enum('y','n') not null);
mysql> insert into t6 values(); // 添加默認值（就是values後面為空，什麼都不寫）
mysql> select * from t6;
+---+---+---------------------+---+
| a | b | c | d |
+---+---+---------------------+---+
| 0 | | 2009-12-28 15:42:17 | y |
+---+---+---------------------+---+
----------------------------------------------------------------------------------------------------------------------------
www.2cto.com
default
default修飾符為字段指定一個默認值
例子：
> create table t2(id int,name varchar(10),dep varchar(10) default "HR");
> insert into t2 set id=1,name="Lili";
> insert into t2 set id=2,name="Anna";
> insert into t2 set id=3,name="Hebe",dep="MIS";
> select * from t2;
+------+------+------+
| id | name | dep |
+------+------+------+
| 1 | Lili | HR |
| 2 | Anna | HR |
| 3 | Hebe | MIS |
+------+------+------+
auto_increment
auto_increment修飾符只適用於INT字段，表明MySQL應該自動為該字段生成一個數（每次在前一個值得基礎上加1）。
MySQL的表只能有一個auto_increment 字段，而且這個字段必須被定義為鍵。
> create table t3 (id int not null auto_increment primary key,name varchar(10) not null);
> insert into t3(name) values("Anna");
> insert into t3(name) values("Nana");
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | Anna |
| 2 | Nana |
+----+------+
-----------------------------------------------------------------------------------
unique
UNI
表示記錄不能重復
例子：
> create table user(name char(10),email varchar(20) unique);
> insert into user values("Anna","[email protected]");
> insert into user values("Nana","[email protected]");
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 1
unique字段null是允許的
mysql> insert into user values();
mysql> insert into user values();
mysql> select * from user;
+------+--------------+
| name | email |
+------+--------------+
| Anna | [email protected] |
| NULL | NULL |
| NULL | NULL |
+------+--------------+
刪除unique
> alter table user drop index email;
設置unique屬性的兩種方法：
> create table t10(name char(10),email varchar(20) unique);
> create table t11(id int,name char(10),unique(id));
------------------------------------------------------------------------------------------------------------
索引
MUL
為了加快搜索速度，減少查詢時間， MySQL允許我們為一個表的特定字段設置索引
索引的缺點：它占據一定的磁盤空間，而且它影響INSERT，UPDATE和DELETE執行的時間
添加索引
> create table sales(name char(4),price float(4,2),date date,index name_index(name)); // 給字段name添加索引，索引的名稱是name_index; 如果沒有定義索引名稱，則使用字段名稱做為索引的名稱
查看索引
> show index from sales;
為多個字段添加索引
> create table sales2(name char(4),price float(4,2),date date,index name_index(name),index (price));
> create table sales3(name char(4),price float(4,2),date date,index (name,price));
使用create index命令向已存在的表添加索引
> create index id_index on xueke (id);
刪除索引
> drop index id_index on xueke;
------------------------------------------------------------------
主鍵
primary key
設置主鍵的幾種方法：
> create table pri(id tinyint not null unique,name char(10)); // not null unique
> create table pri(id tinyint primary key,name char(10));
> create table pri(id tinyint,name char(10),primary key(id));
組合主鍵
> create table firewall(host varchar(20),port smallint(4),access enum('deny','allow'),primary key(host,port));
> insert into firewall values('192.168.10.1',21,'deny');
> insert into firewall values('192.168.10.1',80,'deny');
> insert into firewall values('192.168.10.2',80,'deny');
> insert into firewall values('192.168.10.2',80,'deny');
ERROR 1062 (23000): Duplicate entry '192.168.10.2-80' for key 1
mysql> select * from firewall;
+--------------+------+--------+
| host | port | access |
+--------------+------+--------+
| 192.168.10.1 | 21 | deny |
| 192.168.10.1 | 80 | deny |
| 192.168.10.2 | 80 | deny |
+--------------+------+--------+
在這種情況下，允許host或者port重復，但不能同時重復
-------------------------------------------------------------------------
外鍵
www.2cto.com
建立外鍵：
foreign key...references...
> create table score (id int,name char(10) primary key,math int,phy int) type=innodb;
> create table addr (id int primary key,aname char(10) not null,addr char(255),tel int,index (aname),foreign key (aname) references score (name)) type=innodb;
約束：
1. 關系中的所有表必須是InnoDB表
2. 參照的表和字段（必須是primary key,否則會出MySQL - errno:150的錯誤）是存在 的
3. 組成外鍵的字段被索引
4. 在外鍵關系中，字段的數據類型必須相似，這對於大小和符號都必須匹配的整數類型及其重要
mysql> insert into score values(1,"Anna",70,80),(1,"John",80,90),(3,"Lisa",50,60);
mysql> select * from score;
+------+------+------+------+
| id | name | math | phy |
+------+------+------+------+
| 1 | Anna | 70 | 80 |
| 1 | John | 80 | 90 |
| 3 | Lisa | 50 | 60 |
+------+------+------+------+
一旦建立一個外鍵，MySQL只允許向addr.aname字段輸入那些也存在於score.name字段的值
mysql> insert into addr values(1,"Anna","addr...",4334543);
mysql> insert into addr values(2,"Lucy","addr...",8774366);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1/addr`, CONSTRAINT `addr_ibfk_1` FOREIGN KEY (`aname`) REFERENCES `score` (`name`))
mysql> select * from addr;
+----+-------+---------+---------+
| id | aname | addr | tel |
+----+-------+---------+---------+
| 1 | Anna | addr... | 4334543 |
+----+-------+---------+---------+
刪除外鍵：
1. 刪除有外鍵的表
> drop table addr;
Query OK, 0 rows affected (0.00 sec)
2. 從表中刪除外鍵
語法：
alter table 表名 drop foreign key 外鍵名;
show create table 表名;
實驗結果 Mysql會報錯：
> alter table addr drop foreign key aname;
ERROR 1025 (HY000): Error on rename of './db1/addr' to './db1/#sql2-5258-7' (errno: 152)
on delete cascade子句
cascade刪除包含與已刪除鍵值有參照關系的所有記錄
建表
> create table score (id int,name char(10) primary key,math int) type=innodb;
> create table addr (id int primary key,aname char(10),addr char(255),tel int,index (aname),foreign key (aname) references score (name) on delete cascade) type=innodb;
插入記錄
> insert into score values(1,"Nana",50),(2,"Hebe",70);
> insert into addr values(1,"Nana","addr...",6668787),(2,"Hebe","addr...",8989666);
> select * from score;
+------+------+------+
| id | name | math |
+------+------+------+
| 1 | Nana | 50 |
| 2 | Hebe | 70 |
+------+------+------+
> select * from addr;
+----+-------+---------+---------+
| id | aname | addr | tel |
+----+-------+---------+---------+
| 1 | Nana | addr... | 6668787 |
| 2 | Hebe | addr... | 8989666 |
+----+-------+---------+---------+
刪除score表中Hebe的記錄，addr表中Hebe的記錄自動刪除
> delete from score where name="Hebe";
> select * from score;
+------+------+------+
| id | name | math |
+------+------+------+
| 1 | Nana | 50 |
+------+------+------+
> select * from addr;
+----+-------+---------+---------+
| id | aname | addr | tel |
+----+-------+---------+---------+
| 1 | Nana | addr... | 6668787 |
+----+-------+---------+---------+
