Mysql修饰符_MySQL

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 |

+----+-------+---------+---------+