一. 介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY 标示该字段为表的主键,可以唯一的标示记录
FOREIGN KEY 标示该字段为该表的外键
NOT NULL 标示该字段不能为空
UNIQUE KEY 标示该字段的值是唯一的
AUTO_INCREMENT 标示该字段是自增长类型(数据类型为整数且是主键)
DEFAULT 为对应的字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
使用方法及说明
#创建一个班级,班级的id是主键且是自增长类型的,班级的名称不能为空 create table class( id int(4) primary key auto_increment , name char(20) not null ); insert into class (name) values("中学三年二班"); #插入一条记录 # 创建一个学生表,id为主键,自增长,姓名不能为空,性别默认男,班级编号不能为空,班级编号的外键是class表中的id,分数是无符号类型.(0-255) create table student( id int primary key auto_increment, name char(6) not null, sex enum("男","女") default "男", class_id int(6) not null, foreign key(class_id) references class(id), score int(3) unsigned ); insert into student (name,sex,class_id,score)values("jack","男",1,75);
外键语法:
foreign key(要引用外键的字段) references 被引用的表名(被引用的表字段);
注意:
如果一个字段未设置not null,插入数据的时候,如果不给该字段传值,则默认值为NULL,如果设NOT NULL,则该字段不能为空,否则报错
二. not null 和default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
default默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(id int not null defalut 2 ,num int not null)
三. unique唯一性约束
单列唯一,设置某一个字段的唯一性.
-----1.单列唯一--------- create table t2( id int not null unique, name char(10) ); insert into t2 values(1,'jack'); insert into t2 values(1,'lucy'); #上面创建表的时候把id设置了唯一约束。那么在插入id=1，就会出错了
联合唯一,设置多个字段组成在一起是唯一的.
create table t20(id int not null unique auto_increment,host char(15),port int,unique(host,port)); #设置host和port字段联合唯一 insert into t23(host,port) values("172.16.0.253",1990); insert into t23(host,port) values("172.16.0.253",1991); insert into t23(host,port) values("172.16.0.252",1990); # 2个字段合在一起是唯一的.
四. primary key(主键约束)
primary key字段的值不为空且唯一,一张表中可以做单列主键和复合主键(多列主键),但是一张表中只能有一个主键.
1 create table t25(id int primary key auto_increment,name char(6),age int); 2 insert into t25(name,age) values("jack",18); 3 select * from t25; 4 +----+------+------+ 5 | id | name | age | 6 +----+------+------+ 7 | 1 | jack | 18 | 8 +----+------+------+ 9 10 #id作为自增长的主键
1 create table t24( 2 name char(10) not null, 3 host char(15), 4 port int, 5 primary key(host,port) 6 ); 7 insert into t24 values("mysql","172.168.0.253",3306); 8 insert into t24 values("ftp","172.168.0.253",21); 9 insert into t24 values("mysql","172.168.0.253",3306); #插入相同的会报错 10 #用法和unique的联合唯一差不多
五. auto_increment(自增长约束)
auto_increment有2个属性:
1.auto_increment_increment 步长
2.auto_increment_offset 起始偏移量
---------------------未设置步长和起始偏移量------------------------------ create table t26(id int primary key auto_increment,name char(6)); insert into t26(name) values("IT"),("HR"),("SALER"); SELECT * FROM T26; +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | HR | | 3 | SALER | +----+-------+ --------------------------设置起始步长为100--------------------------------------- create table t27(id int primary key auto_increment,name char(6))auto_increment=100; insert into t27(name) values("IT"),("HR"),("SALER"); SELECT * FROM T27; +-----+-------+ | id | name | +-----+-------+ | 100 | IT | | 101 | HR | | 102 | SALER | +-----+-------+ ------------------------------设置步长和起始偏移量------------------------------------ set session auto_increment_increment =1000; 还是在27这张表插入一个新的值 insert into t27 (name) values("HHH"); #HHH的编号已经是1001了 select * from t27; +------+-------+ | id | name | +------+-------+ | 100 | IT | | 101 | HR | | 102 | SALER | | 1001 | HHH | +------+-------+ #设置偏移量 set session auto_increment_offset =2; insert into t27(name) values ("RRR"),("PPP"),("QQQ"); select * from t27; +------+-------+ | id | name | +------+-------+ | 100 | IT | | 101 | HR | | 102 | SALER | | 1001 | HHH | | 2002 | RRR | | 3002 | PPP | | 4002 | QQQ | +------+-------+ #每增加一个值,步长都会增加1000,偏移量是2
注意:offset的值不能大于increment的值,否则无效.
六. 外键约束(foreign key)
员工表里有三个字段:姓名,性别,部门.现在公司有员工20000人,3个部门.这意味着部门名称会有大量的重复数据.这时候可以使用外键来解决这个问题.先建立一个部门表.再建立员工表,把员工表中的部门ID设置为外键,来自部门表中的部门ID即可.
开始建表,注意:一定要先建立被关联的外键表(部门表).在建立(员工表),被关联的字段必须是唯一的,要先给被关联的表插入记录.
1 create table dep(id int primary key auto_increment,name char(15)); 2 insert into dep(name) values("HR"),("IT"),("SALE"); 3 mysql> SELECT * FROM DEP; 4 +----+------+ 5 | id | name | 6 +----+------+ 7 | 1 | HR | 8 | 2 | IT | 9 | 3 | SALE | 10 +----+------+ 11 create table emp(id int primary key auto_increment,name char(6) not null, 12 sex enum("male","female"),depart_id int); 13 14 insert into emp(name,sex,depart_id) values 15 ("jack","male",2), 16 ("lucy","female",1), 17 ("lili","female",3), 18 ("hanmeimei","female",1), 19 ("lilei","male",2), 20 ("erya","female",3); 21 22 select * from emp; 23 +----+--------+--------+-----------+ 24 | id | name | sex | depart_id | 25 +----+--------+--------+-----------+ 26 | 1 | jack | male | 2 | 27 | 2 | lucy | female | 1 | 28 | 3 | lili | female | 3 | 29 | 4 | hanmei | female | 1 | 30 | 5 | lilei | male | 2 | 31 | 6 | erya | female | 3 | 32 +----+--------+--------+-----------+ 33 34 show create table empG; 35 *************************** 1. row *************************** 36 Table: emp 37 Create Table: CREATE TABLE `emp` ( 38 `id` int(11) NOT NULL AUTO_INCREMENT, 39 `name` char(6) NOT NULL, 40 `sex` enum('male','female') DEFAULT NULL, 41 `depart_id` int(11) DEFAULT NULL, 42 PRIMARY KEY (`id`), 43 KEY `depart_id` (`depart_id`), 44 CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`depart_id`) REFERENCES `dep` (`id`) 45 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 46 1 row in set (0.00 sec)
上面建立的表,有一个坏处,如果部门的id发生了变化,员工表中的部门id并不会有所改变.如果部门解散了.员工信息也不会删除.
1 drop table emp; 2 create table emp(id int primary key auto_increment, 3 name char(6) not null, 4 sex enum("male","female"), 5 depart_id int,foreign key (depart_id) references dep(id) 6 on delete cascade 7 on update cascade); #加上这两个语句,在部门ID发生变化时,员工表中的部门id也会跟着辩护 8 update table dep id=101 where id=1; #修改部门1的id 9 select * from emp; 10 11 +----+--------+--------+-----------+ 12 | id | name | sex | depart_id | 13 +----+--------+--------+-----------+ 14 | 1 | jack | male | 2 | 15 | 2 | lucy | female | 101 | 16 | 3 | lili | female | 3 | 17 | 4 | hanmei | female | 101 | 18 | 5 | lilei | male | 2 | 19 | 6 | erya | female | 3 | 20 +----+--------+--------+-----------+