1，完整约束

image.png

注意：unsigned ,zerofill 一般适用于整数类型的值

2，类型测试

1 unsigned

image.png

2 zerofill

image.png

3 ,掌握约束类型。default-默认值，not null-非空

创表
create table student.student4(
id int not null,
name varchar(50) not null,
sex enum ('m','f') default 'm' not null,
age int unsigned default 18 not null,
hobby set('music','disc','dance','book') default 'book,dance' );

插入数据
mysql> insert into student4 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;

mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)
查询数据
mysql> select * from student4;

4.掌握主键约束特性。

创表
mysql> create table student6(
id int primary key not null auto_increment,
name varchar(50) not null,
sex enum('male','female') default 'male',
age int default 18
);
插入数据
mysql> insert into student6 values (1,'alice','female',22);

mysql> insert into student6(name,sex,age) values
('jack','male',19),
('tom','male',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
查询
select * from student6；

5.掌握唯一约束

创表
CREATE TABLE company.department1 (
dept_id INT,
dept_name VARCHAR(30) UNIQUE,
comment VARCHAR(50));
插入正确数据
insert into department1 values (1,'zhangsan','yyy');
insert into department1 values (1,NULL,'yyy');
insert into department1 values (1,NULL,'yyy');
插入错误数据
insert into department1 values (1,'zhangsan','yyy');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'dept_name'
查询
select * from company.department1;

6.掌握外键约束 FOREIGN KEY

创表
mysql> create table employees(
name varchar(50) not null,
mail varchar(20),
primary key(name)
)engine=innodb;

mysql> create table payroll(
id int not null auto_increment,
name varchar(50) not null,
payroll float(10,2) not null,
primary key(id),
foreign key(name) references employees(name) on update cascade on delete cascade
)engine=innodb;

查看表结构
desc employees；
show create table payroll\G

插入测试数据
insert into employees values ('zhangsan','zhangsan@126.com');
insert into payroll values (1,'zhangsan',20000.23);
select * from employees;
select * from payroll;

父表更新，子表会如何？
update employees set name='zhangsansss' where name='zhangsan';
select * from payroll;

父表删除，子表会如何
delete from employees where name='zhangsansss';
注意：复合组件与上面操作类似（多设几个主键和外键）

7.设置字段值增 AUTO_INCREMENT

1、被auto_increment修饰的列为标识列，可以自动增长。一张表中只能有一个标识列。

2、auto_increment只能用来修饰数值类型的列，并且该类型的列列只能为key类型的列，key类型的列有：primary key 、unique、key、foreign key
3、简单的说，主键可以没有auto_increment属性，但是一张表只能有一个auto_increment属性