day 7-14 数据库完整性约束

一. 介绍

　　约束条件与数据类型的宽度一样，都是可选参数

　　作用：用于保证数据的完整性和一致性
　　主要分为：

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字段的值不为空且唯一,一张表中可以做单列主键和复合主键(多列主键),但是一张表中只能有一个主键.

create table t25(id int primary key auto_increment,name char(6),age int);
insert into t25(name,age) values("jack",18);
select * from t25;
+----+------+------+
| id   | name | age  |
+----+------+------+
|  1   | jack   |   18 |
+----+------+------+

#id作为自增长的主键

create table t24(
    name char(10) not null,
    host char(15),
    port int,
    primary key(host,port)
);
insert into t24 values("mysql","172.168.0.253",3306);
insert into t24 values("ftp","172.168.0.253",21);
insert into t24 values("mysql","172.168.0.253",3306);    #插入相同的会报错
#用法和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即可.

开始建表,注意:一定要先建立被关联的外键表(部门表).在建立(员工表),被关联的字段必须是唯一的,要先给被关联的表插入记录.

create table dep(id int primary key auto_increment,name char(15));     
insert into dep(name) values("HR"),("IT"),("SALE");
mysql> SELECT * FROM DEP;
+----+------+
| id | name |
+----+------+
|  1 | HR   |
|  2 | IT   |
|  3 | SALE |
+----+------+
create table emp(id int primary key auto_increment,name char(6) not null,
sex enum("male","female"),depart_id int);

insert into emp(name,sex,depart_id) values
    ("jack","male",2),
    ("lucy","female",1),
    ("lili","female",3),
    ("hanmeimei","female",1),
    ("lilei","male",2),
    ("erya","female",3);

select * from emp;
+----+--------+--------+-----------+
| id | name   | sex    | depart_id |
+----+--------+--------+-----------+
|  1 | jack   | male       |         2 |
|  2 | lucy   | female    |         1 |
|  3 | lili      | female    |         3 |
|  4 | hanmei | female |         1 |
|  5 | lilei    | male      |         2 |
|  6 | erya  | female    |         3 |
+----+--------+--------+-----------+

show create table empG;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(6) NOT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `depart_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `depart_id` (`depart_id`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`depart_id`) REFERENCES `dep` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

上面建立的表,有一个坏处,如果部门的id发生了变化,员工表中的部门id并不会有所改变.如果部门解散了.员工信息也不会删除.

drop table emp;
create table emp(id int primary key auto_increment,
name char(6) not null,
sex enum("male","female"),
depart_id int,foreign key (depart_id) references dep(id) 
on delete cascade         
on update cascade);     #加上这两个语句,在部门ID发生变化时,员工表中的部门id也会跟着辩护
update table dep id=101 where id=1;   #修改部门1的id
select * from emp;

+----+--------+--------+-----------+
| id | name   | sex    | depart_id |
+----+--------+--------+-----------+
|  1 | jack   | male   |              2 |
|  2 | lucy   | female |          101 |
|  3 | lili      | female |             3 |
|  4 | hanmei | female |       101 |
|  5 | lilei     | male   |             2 |
|  6 | erya   | female |             3 |
+----+--------+--------+-----------+