完整性约束
1. not null 与 default
-
-
如果即设置了not null,又指定default,可以插入空值,会走default
(1)默认值可以为空
mysql> create table t1(id int);# id字段默认可以为空 Query OK, 0 rows affected (0.05 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ row in set (0.03 sec) mysql> insert into t1 values(); #给t1表插一个空的值 Query OK, 1 row affected (0.00 sec) #查询结果如下 mysql> select * from t1; +------+ | id | +------+ | NULL | +------+ row in set (0.00 sec) 默认值可以为空
(2)设置not null后 ,插入值不能为空
#给 num设置不能为空 mysql> create table t2(num int not null); Query OK, 0 rows affected (0.47 sec) mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | num | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) #此时报错,显示不能插空值 mysql> insert into t2 values(); ERROR 1364 (HY000): Field 'num' doesn't have a default value
(3) 如果即设置了not null,又指定default,可以插入空值,值为default,当插入值不是空值,结果是自己的值
#创建 t3表此时设置not null,并且default为 4 mysql> create table t3(num int not null default 4); Query OK, 0 rows affected (0.46 sec) mysql> desc t3; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | num | int(11) | NO | | 4 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) #当插入数据为空时,此时是默认值生效 mysql> insert into t3 values(); Query OK, 1 row affected (0.11 sec) #但是也可以插入值但是此时却是给的值而不是默认值 mysql> insert into t3 values(2); Query OK, 1 row affected (0.11 sec) mysql> select * from db2.t3; +-----+ | num | +-----+ | 4 | | 2 | +-----+ 2 rows in set (0.00 sec)
2.
(1)单列唯一
# 创建t4表,给name设置单列唯一 mysql> create table t4(id int not null,name char(20) unique); Query OK, 0 rows affected (0.47 sec) mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | char(20) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into t4 values(1,'alex'); Query OK, 1 row affected (0.35 sec) mysql> insert into t4 values(1,'alex'); #有于给name设置了单列为一,因此插入相同的值会报错 ERROR 1062 (23000): Duplicate entry 'alex' for key 'name' #当将值改为与第一次不同时候,发现此时插入成功 mysql> insert into t4 values(1,'alex2'); Query OK, 1 row affected (0.34 sec) mysql> select * from db2.t4; +----+-------+ | id | name | +----+-------+ | 1 | alex | | 1 | alex2 | +----+-------+ 2 rows in set (0.00 sec)
两种书写方式:
create table t4( id int not null, name char(20) unique ); create table t4( id int not null, name char(20), unique(name) ); insert into t4(id,name) values(1,'alex'); insert into t4(id,name) values(1,'wusir');
(2)多列唯一:只要有一列是相同的就不能插入.
#书写
create table t5( id int, name char(20), unique(id), unique(name) );
#创建文件t5 ,此时将id 和name都设置 unique mysql> create table t5( -> id int, -> name char(20), -> unique(id), -> unique(name) -> ); Query OK, 0 rows affected (0.58 sec) #插入一组值 mysql> insert into t5 values(2,'alex'); ERROR 1062 (23000): Duplicate entry 'alex' for key 'name' #插入有相同内容的值时候报错 mysql> insert into t5 values(1,'alex2'); ERROR 1062 (23000): Duplicate entry '1' for key 'id' #当所有内容都不同时才能插入 mysql> insert into t5 values(2,'alex2'); Query OK, 1 row affected (0.10 sec) mysql> desc t5; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(20) | YES | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> select * from db2.t5; +------+-------+ | id | name | +------+-------+ | 1 | alex | | 2 | alex2 | +------+-------+ 2 rows in set (0.00 sec)
(3)联合唯一:多列相同时不能插入
**适用场景 学生选课
#书写 create table t6( id int, name char(20), unique(id,name) );
#创建t6表 ,设置联合唯一 mysql> create table t6( -> id int, -> name char(20), -> unique(id,name) -> ); Query OK, 0 rows affected (0.47 sec) mysql> desc t6; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into t6 values(1,"alex"); Query OK, 1 row affected (0.35 sec) #当插入相同的两列记录会报错 mysql> insert into t6 values(1,"alex"); ERROR 1062 (23000): Duplicate entry '1-alex' for key 'id' mysql> insert into t6 values(2,"alex"); Query OK, 1 row affected (0.11 sec) #但是当两列记录中的一项内容不同就可以插入 mysql> insert into t6 values(1,"wusir"); Query OK, 1 row affected (0.10 sec) mysql> select *from t6; +------+-------+ | id | name | +------+-------+ | 1 | alex | | 1 | wusir | | 2 | alex | +------+-------+ 3 rows in set (0.01 sec)
3. 主键primary key (not null + unique 的结果)
(1)单列主键 不能为空 ,并且是唯一
#书写 # primary key 索引(针对于大量数据) 查询速度要快 create table t7( id int primary key, name varchar(10) unique ); #相同结果的写法 create table t8( id int not null unique, name varchar(10) unique );
#创建t7表 此时为id设置主键,并且 name唯一 mysql> create table t7( -> id int primary key, -> name varchar(10) unique -> ); Query OK, 0 rows affected (0.51 sec) mysql> desc t7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) #当插入空值时候回报错 mysql> insert into t7 values(); ERROR 1364 (HY000): Field 'id' doesn't have a default value mysql> insert into t7(id,name) values(1,"wusir"); Query OK, 1 row affected (0.37 sec) #id唯一且不能为空 mysql> insert into t7(id,name) values(1,"wusir"); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into t7(id,name) values(1,"wusir2"); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into t7(id,name) values(2,"wusir2"); Query OK, 1 row affected (0.35 sec) mysql> select *from db2.t7; +----+--------+ | id | name | +----+--------+ | 1 | wusir | | 2 | wusir2 | +----+--------+ 2 rows in set (0.00 sec)
(2)联合主键
#书写方式 create table t9( id int, name varchar(10), primary key(id,name) );
#创建了t9表,此时可以设置空值 mysql> create table t9( -> id int, -> name varchar(10), -> primary key(id,name) -> ); Query OK, 0 rows affected (0.30 sec) mysql> desc t9; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | varchar(10) | NO | PRI | | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into t9 values(); Query OK, 1 row affected (0.11 sec) mysql> insert into t9(id name) values(1,"alex"); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name) values(1,"alex")' at line 1 mysql> insert into t9(id, name) values(1,"alex"); Query OK, 1 row affected (0.10 sec) mysql> insert into t9(id, name) values(1,"alex2"); Query OK, 1 row affected (0.11 sec) mysql> select *from db2.t9; +----+-------+ | id | name | +----+-------+ | 0 | | | 1 | alex | | 1 | alex2 | +----+-------+ 3 rows in set (0.00 sec)
**学生选课可以为空
4.auto_increment (自增长)
#书写方式 create table student( id int primary key auto_increment, name varchar(20) not null, sex enum('male','female') default 'male', ip varchar(20) unique ); insert into student(name,sex,ip) values ('alex','female','127.0.0.5'),('wusir','male','173.45.32.1');
mysql> create table student( -> id int primary key auto_increment, -> name varchar(20) not null, -> sex enum('male','female') default 'male', -> ip varchar(20) unique -> ); Query OK, 0 rows affected (0.23 sec) mysql> insert into student(name,sex,ip) values ('alex','female','127.0.0.5'),('wusir','male','173.45.32.1'); Query OK, 2 rows affected (0.37 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | YES | | male | | | ip | varchar(20) | YES | UNI | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> select *from student; +----+-------+--------+-------------+ | id | name | sex | ip | +----+-------+--------+-------------+ | 1 | alex | female | 127.0.0.5 | | 2 | wusir | male | 173.45.32.1 | +----+-------+--------+-------------+ 2 rows in set (0.00 sec)
涉及到删除时:
#当删除第二条记录时候,会发现此时的结果任然保留id为2 时的序号 mysql> delete from student where id =2; Query OK, 1 row affected (0.49 sec) mysql> select *from student; +----+-------+--------+------------+ | id | name | sex | ip | +----+-------+--------+------------+ | 1 | alex | female | 127.0.0.5 | | 3 | alex1 | male | 127.1.0.12 | +----+-------+--------+------------+ 2 rows in set (0.00 sec) #在插入记录时候,会在后续排列 mysql> insert into student(name,sex,ip) values ('alex3','male','127.1.5.72'); Query OK, 1 row affected (0.11 sec) mysql> select *from student; +----+-------+--------+------------+ | id | name | sex | ip | +----+-------+--------+------------+ | 1 | alex | female | 127.0.0.5 | | 3 | alex1 | male | 127.1.0.12 | | 4 | alex3 | male | 127.1.5.72 | +----+-------+--------+------------+ 3 rows in set (0.00 sec) #但是可以指定id进行插入 mysql> insert into student(id,name,sex,ip) values (2,'wusir','male','127.3.5.9'); Query OK, 1 row affected (0.11 sec) mysql> mysql> Ctrl-C -- exit! select *from student; +----+-------+--------+------------+ | id | name | sex | ip | +----+-------+--------+------------+ | 1 | alex | female | 127.0.0.5 | | 2 | wusir | male | 127.3.5.9 | | 3 | alex1 | male | 127.1.0.12 | | 4 | alex3 | male | 127.1.5.72 | +----+-------+--------+------------+ 4 rows in set (0.00 sec) #清空记录 mysql> delete from student; Query OK, 4 rows affected (0.36 sec) mysql> delete from student; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_db2 | +---------------+ | student | | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t9 | +---------------+ 9 rows in set (0.11 sec) #但是会保留自增长的序号,后续插入 mysql> insert into student(name,sex,ip) values ('alex3','male','127.1.5.72'); Query OK, 1 row affected (0.36 sec) mysql> select *from student; +----+-------+------+------------+ | id | name | sex | ip | +----+-------+------+------------+ | 5 | alex3 | male | 127.1.5.72 | +----+-------+------+------------+ 1 row in set (0.00 sec) #但是使用truncate时候,不会保留增长的序号 mysql> truncate table student; Query OK, 0 rows affected (0.21 sec) mysql> show tables; +---------------+ | Tables_in_db2 | +---------------+ | student | | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t9 | +---------------+ 9 rows in set (0.00 sec) mysql> select *from student; Empty set (0.00 sec) mysql> insert into student(name,sex,ip) values ('alex3','male','127.1.5.72'); Query OK, 1 row affected (0.35 sec) #再次插入时候从一开始 mysql> select *from student; +----+-------+------+------------+ | id | name | sex | ip | +----+-------+------+------------+ | 1 | alex3 | male | 127.1.5.72 | +----+-------+------+------------+ 1 row in set (0.00 sec)
delete from t1; #如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大,删除速度比上一条快,且直接从零开始。
5.
# 先创建主表 mysql> create table dep( -> id int primary key auto_increment, -> name char(10) unique, -> dep_desc varchar(50) not null -> ); Query OK, 0 rows affected (0.47 sec) #创建从表 mysql> create table emp( -> eid int primary key auto_increment, -> name char(10) not null, -> age int not null, -> dep_id int, -> school_id int, -> constraint fk_dep foreign key(dep_id) references dep(id) ); Query OK, 0 rows affected (0.55 sec) #主表插入数据 mysql> insert into dep(name,dep_desc) values('校长部','校长管理有限部门'),('公关部','公关管理有限部门'),('IT部门','IT技 术有限部门'),('财务部','管钱很多部门'); Query OK, 4 rows affected (0.11 sec) Records: 4 Duplicates: 0 Warnings: 0 #从表插入数据 mysql> insert into emp(name,age,dep_id) -> values -> ('alex',18,1), -> ('wusir',30,2), -> ('吴老板',20,3), -> ('马老板',18,4), -> ('邱老板',20,2), -> ('女神',16,3); Query OK, 6 rows affected (0.06 sec) Records: 6 Duplicates: 0 Warnings: 0 #将从表中对应主表的id为2 的项删除 mysql> delete from emp where dep_id =2; Query OK, 2 rows affected (0.37 sec) mysql> select *from emp; +-----+-----------+-----+--------+-----------+ | eid | name | age | dep_id | school_id | +-----+-----------+-----+--------+-----------+ | 1 | alex | 18 | 1 | NULL | | 3 | 吴老板 | 20 | 3 | NULL | | 4 | 马老板 | 18 | 4 | NULL | | 6 | 女神 | 16 | 3 | NULL | +-----+-----------+-----+--------+-----------+ 4 rows in set (0.00 sec) #此时可以将主表中id为2的部门删除 mysql> delete from dep where id=2; Query OK, 1 row affected (0.35 sec) mysql> select *from dep; +----+-----------+--------------------------+ | id | name | dep_desc | +----+-----------+--------------------------+ | 1 | 校长部 | 校长管理有限部门 | | 3 | IT部门 | IT技术有限部门 | | 4 | 财务部 | 管钱很多部门 | +----+-----------+--------------------------+ 3 rows in set (0.00 sec) #但是若果直接删除,会报错,因为此时该id对应的存在内容 mysql> delete from dep where id=3; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db2`.`emp`, CONSTRAINT `fk_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
所以要同步删除和同步更新
#当在创建从表时添加 on delete cascade on update cascade 同步更新同步删除 mysql> select *from dep; +----+-----------+--------------------------+ | id | name | dep_desc | +----+-----------+--------------------------+ | 1 | 校长部 | 校长管理有限部门 | | 2 | 公关部 | 公关管理有限部门 | | 3 | IT部门 | IT技术有限部门 | | 4 | 财务部 | 管钱很多部门 | +----+-----------+--------------------------+ 4 rows in set (0.00 sec) #直接删除部门可以,而对应的部门内的人也删除 mysql> delete from dep where id=2; Query OK, 1 row affected (0.35 sec) mysql> select *from dep; +----+-----------+--------------------------+ | id | name | dep_desc | +----+-----------+--------------------------+ | 1 | 校长部 | 校长管理有限部门 | | 3 | IT部门 | IT技术有限部门 | | 4 | 财务部 | 管钱很多部门 | +----+-----------+--------------------------+ 3 rows in set (0.00 sec) mysql> select *from emp; +-----+-----------+-----+--------+-----------+ | eid | name | age | dep_id | school_id | +-----+-----------+-----+--------+-----------+ | 1 | alex | 18 | 1 | NULL | | 3 | 吴老板 | 20 | 3 | NULL | | 4 | 马老板 | 18 | 4 | NULL | | 6 | 女神 | 16 | 3 | NULL | +-----+-----------+-----+--------+-----------+ 4 rows in set (0.00 sec)