7-[表操作]--完整性约束

1、介绍

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

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

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

 

2、zerofill：使用0填充    UNSIGNED：无符号

 

 

3、not null与default

　　

#==================not null====================
mysql> create table t1(id int); #id字段默认可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空


mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value



#==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;

==================综合练习====================
mysql> create table student(
    -> name varchar(20) not null,
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );
mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(3) unsigned                    | NO   |     | 18         |       |
| sex   | enum('male','female')              | YES  |     | male       |       |
| hobby | set('play','study','read','music') | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('egon');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| egon |  18 | male | play,music |
+------+-----+------+------------+

验证

4、 unique 唯一

 　　（1）单列唯一

设置唯一约束 UNIQUE===============
#方法一：
    create table department(
    id int ,
    name char(10) unique
);
desc department;


insert into department values
(1,'IT'),
(2,'IT');


# 方法2：
create table department2(
    id int,
    name char(10),
    unique(id),
    unique(name)
);

desc department2;

insert into department2 values
(1,'IT'),
(2,'sale');

 

　　（2）联合唯一

# 联合唯一
create table services(
    id int,
    ip char(15),
    port int,
    unique(id),
    unique(ip,port)
);

desc services;

insert into services values
(1,'192.168.0.1',80),
(2,'192.168.0.2',80),
(3,'192.168.0.2',82);

select * from services;

insert into services values(5,'192.168.0.1','80');

5、 primary key

约束：not null unqiue
存储引擎(innodb):对于innodb存储引擎来说，一张表必须有一个主键

 

　　（1）单列主键

单列做主键===============
#方法一：not null+unique
create table department1(
id int not null unique, #主键（只是约束条件形式的），primary key必须满足两个条件
name varchar(20) not null unique,
comment varchar(100)
);

#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

 

 

 　　（2）复合主键

create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)       #复合主键
);    

 

6、 auto_increment：自动增长，被key约束

　　（1）自动增长

#不指定id，则自动增长

#也可以指定id

#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

　　

　　（2） 清空表：delete 、truncate

# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

delete from t20;
delete from t20 where id = 3;
insert into t20(name) values
('xxx');

truncate t20; #应该用它来清空表

 

 　　

　　（3）步长与偏移量

    show variables like 'auto_inc%';

    #步长：
    auto_increment_increment默认为1
    #起始偏移量
    auto_increment_offset默认1

　　

    #设置步长
    set session auto_increment_increment=5;  # 基于会话级别
    set global auto_increment_increment=5;   # 修改全局级别的步长（所有会话都生效）
    

 

    #设置起始偏移量
    set global auto_increment_offset=3;
    强调：起始偏移量<=步长
    mysql> show variables like 'auto_incre%'; #需要退出重新登录

# 验证    
      create table t21(
        id int primary key auto_increment,
        name char(16)
    );
    
    insert into t21(name) values
    ('egon'),
    ('alex'),
    ('wxx'),
    ('yxx');
        

 

2

3

4

5

6

7：