bitscn.com
mysql修饰符
not null
有not null 来修饰的话，往表里添加默认值的时候：
数值类型的话，添加的是0
字符串类型的话，添加的是空
如果数据类型是时间戳类型，添加的默认值是当前时间
枚举类型，添加的是第一个预先定义的值
验证举例：
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 |
+---+---+---------------------+---+
----------------------------------------------------------------------------------------------------------------------------
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,anna@163.com);
> insert into user values(nana,anna@163.com);
error 1062 (23000): duplicate entry 'anna@163.com' for key 1
unique字段null是允许的
mysql> insert into user values();
mysql> insert into user values();
mysql> select * from user;
+------+--------------+
| name | email |
+------+--------------+
| anna | anna@163.com |
| 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重复，但不能同时重复
-------------------------------------------------------------------------
外键
建立外键：
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 |
+----+-------+---------+---------+
bitscn.com