mysql字段约束操作,数据库之完整性约束

总结:

数据类型

一、介绍

字段常用约束如下表:

     1,not null 不能插入空,不设置可空

1. 数字(默认都是由符号,宽度表示的是显示宽度,与存储无关).tinyint 括号里指定宽度

七位2进制数最大数就是2**7 -1=127 最小是-128

验证:

create tabel t1(id tinyint)

create tabe t2(id int)

浮点数

float

double

decimal

测试

create table t2(salary float)

float(6,2) 第一参数,代表宽度. 第二个参数代表小数点后面的位数.

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

约束 名称
primary key 主键
unique key 唯一约束
null 空约束
default 默认值
auto_increment 自增长约束
comment 注释
foreign key 外键约束

      2,unique  单列唯一

2.字符串类型

char 与 varchar

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

主键(primary key)

能唯一标识记录的字段,可以作为主键。一个表只能有一个主键。主键具有唯一性。声明字段时,用 primary key 标识。也可以在字段列表之后声明,在字段列表后声明主键时主键可以由多个字段组成,声明主键的字段值不能为null。
ex:

--创建一张名为lesson的课表设置主键是lesson_id
create table if not exists lesson(
lesson_id int not null primary key auto_increment,
name varchar(40) not null unique key comment '课名称'
)
--另一种写法方式
create temporary table if not exists lesson(
  id int not null,
  name varchar(40) not null comment '课程名称',
  primary key(id),
  unique key(name)
)

    create table department(name char(10) unique);         创建方式一

char 类型:定长,浪费空间,存取速度快

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

UNSIGNED 无符号
ZEROFILL 使用0填充

唯一约束(unique key)

使得字段的值不能重复。具有唯一性。

    create table department( unique(name));                    创建方式二

不够就用空格补,
字符长度范围: 0 ~ 255

create table t6(name char(4));

insert into t6('aigen')#存不进去

insert into t6('能不能存')  #能存进去


####varchar:变长,精准,节省空间,存取速度慢
字符长度范围:0 ~ 65535



**char 和 varchar 区别:  char(5), 存不够5个,用空格补齐.     varhar(5), 你是几个它就存几个.**

char_length : 查看的是字符的长度
'abc'和'你好啊' 都是三个字符.

但是前面是3个字节,后面 char(5)的length话,是11个字节.

length : 查看字节.


一个英文字符算一个 bytes

一个中文字符算3个 bytes

insert into t7 values('abc','abc ')

select * from t6 where y= 'abc   '

说明:

空约束(null)

null不是数据类型,是列的一个属性。表示当前列是否可以为空。null表示什么都没有。null意思是允许列为空。not null是不允许列为空。列的默认值为null。
ex:

--创建一张表名为student(name,age)
create temporary table if not exists student(
 name varchar(20) null,
 age int not null default 18
)
--向学生表中插入一条数据,此时表示将name字段的值设为null, 取决于该字段是否允许为null
replace into student set name=null,age=18;

 

3.日期类型

create table student(
id int,
name char(5),
born_date date,#'2017-09-06',
born_year year, #'2017',
res_time datetime, #'2017-09-06 10:53:23'
class_time time  #'10:53:23'
);


insert into student values(1,'ff',now(),now(),now(),now());

insert into student values(2,'ff2',,now(),now(),now());
1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值(无符号) 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

默认值(default)

字段的默认值。
ex:

--创建一张班级表,名为clazz包含班级名,班级人数(人数默认30人)
create temporary table clazz(
 name varchar(20) not null comment '班级名称',
 student_num int not null default 30
);
-- 表示强制使用默认
insert into clazz values ('一年级二班', default);

--为班级表增加开班时间字段
alter table if exists clazz add column start_time timestamp default current_timestamp;

    联合唯一: unique(name)    unique(id)  两个都不同才可插入

4.枚举和集合

enum 枚举: 规定一个范围,可有多个值,但是为该字段传值时,只能取规定范围内的一个值.

set 集合: 规定一个范围,但是为该字段传值时,能取规定范围中的一个值或多个值.

create table person(
id int primary key auto_increment,
name char(10),
sex enum('male','female'),
hobbies set('music','read','swimming','learning English')
);

insert into person (name,sex enum,hobbies set) values('ff','male','read,swimming');

二、not null 和default  

自增长约束(auto_increment)

自增长必须为索引(主键或unique),一张表中只能存在一个字段为自动增长。默认从1开始自增长。可以通过表属性 auto_increment = 初始值进行设置或 alter table tbl auto_increment = 初始值来更改自增长的初始值;

              组合唯一:unique(name,id)   有一个不同即可插入

约束

是否可空,null表示空,非字符串
not null - 不可空
null - 可空

注释(comment)

表、列都可以设置注释(comment),注释就是对表、列的意思的进行解释
ex:

create temporary table if not exists teacher(
name varchar(45) not null comment '教师姓名'
)engine=innodb comment='教师信息表'  charset utf8 collate utf8_general_ci;

   3,主键   primary key = not null unique

1.not null和 default...

create table student( id int primary key auto_increment, name char(5), sex enum('male','female') not null default 'femela' );

insert into student (name) values('egon');

 

外键约束(foreign key)

格式: foreign key(外键字段) references 主表(关联字段) [restrict|cascade|set null| no action | set default]

用于限制主表与从表数据完整性。作用:保持数据一致性,完整性,主要目的是控制存储在外键表(从表)中的数据。
ex:

-- 将表t1的t1_id外键关联到表t2的id字段。 每个外键都有一个名字,可以通过 constraint 指定存在外键的表,称之为从表(子表),外键指向的表,称之为主表(父表)。
alter table t1 add constraint t1_t2_fk foreign key (t1_id) references t2(id);

MySQL中,可以对InnoDB引擎使用外键约束:
语法:foreign key (外键字段) references 主表名 (关联字段) [主表记录删除动作] [主表记录更新动作]
此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下,可以设置为null.前提是该外键列,没有not null。可以不指定主表记录更改或更新时的动作,那么此时主表的操作被拒绝。如果指定了 on update 或 on delete:在删除或更新时,有如下几个操作可以选择:
cascade,级联操作。主表数据被更新(主键值更新),从表也被更新(外键值更新)。主表记录被删除,从表相关记录也被删除。
set null,设置为null。主表数据被更新(主键值更新),从表的外键被设置为null。主表记录被删除,从表相关记录外键被设置成null。但注意,要求该外键列,没有not null属性约束。
restrict,拒绝父表删除和更新。
set default 设置为默认值,主表数据被更新(主键值更新),从表的外键被设置为默认值。主表记录被删除,从表相关记录外键被设置成默认值。但注意,要求该外键列设置default属性约束。

ps:注意,外键只被InnoDB存储引擎所支持。其他引擎是不支持的。

    作用:唯一标识,查询优化.

2.unique 唯一

主键就是不为空且唯一.

default默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table tb1(
id int not null defalut  2 ,
num int not null
)

     复合主键:  有一个不同即可.

2.1单列唯一

create table teacher( id int not null unique, name char(10));

insert into teacher values(1,'go');

insert table teacher values(1,'alex');

 

    注意    :   多列主键有问题

2.2多列唯一

create table service( id int primary key autoincrement, name char(10), host char(15), port int, /#constraint hostport unique(host,port) );

三、unique约束(唯一性约束)

  4,auto_increment(自增长)

2.3偏移量: autoincremenoffset

create table dep( id int primary key autoincrement, name char(10) )autoincrement=10;

insert into dep(name) values ('it'),('hr');

单列唯一

**         # 步长auto_increment_increment,默认为1
         # 起始的偏移量auto_increment_offset, 默认是1

2.4步长 autoincrementincrement

create table dep( id int primary key auto_increment, name char(10) );

insert into dep(name) values ('it'),('hr');

set session autoincrementincrement=10;#会话级,只对当前回话有效.

set global autoincrementincrement=2;#全局,对所有的会话都有效.

autoincrementoffset#偏移量(也就是从几开始) autoincrementincrement#步长

set session autoincrementoffset=5; set session autoincrementincrement=2;

!!!如果偏移量的值大于步长的值,则偏移量的值会被忽略!

set session autoincrementoffset=2;

set session autoincrementincrement=3;

-----1.单列唯一---------
create table t2(
id int not null unique,
name char(10)
);
insert into t2 values(1,'egon');
insert into t2 values(1,'alex');
#上面创建表的时候把id设置了唯一约束。那么在插入id=1,就会出错了

         # 设置步长 为会话设置,只在本次连接中有效
          set session auto_increment_increment=5;

2.5show variables like '%auto_in%'; #查看变量

primary key字段的值不为空且唯一... 一个表中可以,一般都加在 id 字段上. ```

多列唯一

         #全局设置步长 都有效。
         set global auto_increment_increment=5;

3.foreign key(附练习) #外键

-----2.多列唯一---------
#255.255.255.255
create table server(
id int primary key auto_increment,
name char(10),
host char(15), #主机ip
port int, #端口
constraint host_port unique(host,port) #constraint host_port这个只是用来设置唯一约束的名字的,也可以不设置默认就有了
);
insert into server(name,host,port) values('ftp','192.168.20.11',8080);
insert into server(name,host,port) values('https','192.168.20.11',8081); #ip和端口合起来唯一
select * from server;

        # 设置起始偏移量
         set global  auto_increment_offset=3;**

关联

四、primary key (主键约束)

清空表区分delete和truncate的区别:

关联 自己的字段 references(关联) 要关联的地方(字段) dep(id);

foreign key (dep_id) references dep(id)

如果想指定名字 constraint fk_depid_id

先建被关联的表,先建被关联的表,要保证被关联的那个表示不为空且唯一的表. 关联: 多的关联那个1, 多本书关联一个出版社,那么被关联的就是出版社.

create table dep(

id int,

name varchar(50),

comment varchar(100)

);

然后建需要关联的表: create into emp_info(

id int primary key auto_increment,

name varchar(20),

dep_id int,

foreign key(dep_id) references dep(id)

on delete cascade

on update cascade

);

delete from dep where id=2;

update dep set id = 301 where id=3;

以上就是一条记录对应另一个表的多条记录.

先给被关联的表插入初始化记录

一个作者可以属于多个出版社

一本书只能属于一个出版社

书(两本书的信息)

出版社(1设,2设)

多本书属于一个出版社,那么就是多对1, 1就是需要被关联的那个表

书这张表 foreign key了出版社那张表的 id 字段.

primary key字段的值不为空且唯一

delete from t1; #如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。

出版社是需要被关联的那个1.
create table press(
id int primary key auto_increment,
name varchar(20) not null
);


create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade


insert into press(name) values('人出版社'),('民出版社');

insert into book(name,press_id) values ('pyjy',1),('ons',2),('go',2),('python',2),('java',3);



一个作者可以写多本书,一本书可以有多个作者,他俩相互对应

create table author(
id int primary key  auto_increment,
name varchar(20)
);


create table book2author(
id int not null unique auto_increment,
book_id int not null,
author_id int not null,
foreign key(book_id) references book(id)
on delete cascade
on update cascade,
foreign key(author_id) references author(id)
on delete cascade
on update cascade,

primary key (book_id,author_id)
);

insert into author(name) values('ff'),('jm'),('by');

insert into book2author(book_id,author_id) values(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1);


每个作者与自己的代表作如下
1 ff: 
      1 九阳神功
      2 九阴真经
      3 九阴白骨爪
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典

2 jm: 
      1 九阳神功
      6 葵花宝典

3 by:
      4 独孤6剑
      5 降龙7巴掌
      6 玫瑰花花宝典

一个表中可以:

truncate table t1;数据量大,删除速度比上一条快,且直接从零开始。

单列做主键
多列做主键(复合主键)

 

但一个表内只能有一个主键primary key

  5,foreign key:

图片 1图片 2

          constraint   fk_dep   foreign   key(dep_id)   references   dep(id)

 1 ============单列做主键===============
 2 #方法一:not null unique
 3 create table department1(
 4 id int not null unique, #主键
 5 name varchar(20) not null unique,
 6 comment varchar(100)
 7 );
 8 
 9 mysql> desc department1;
10  --------- -------------- ------ ----- --------- ------- 
11 | Field   | Type         | Null | Key | Default | Extra |
12  --------- -------------- ------ ----- --------- ------- 
13 | id      | int(11)      | NO   | PRI | NULL    |       |
14 | name    | varchar(20)  | NO   | UNI | NULL    |       |
15 | comment | varchar(100) | YES  |     | NULL    |       |
16  --------- -------------- ------ ----- --------- ------- 
17 rows in set (0.01 sec)
18 
19 #方法二:在某一个字段后用primary key
20 create table department2(
21 id int primary key, #主键
22 name varchar(20),
23 comment varchar(100)
24 );
25 
26 mysql> desc department2;
27  --------- -------------- ------ ----- --------- ------- 
28 | Field   | Type         | Null | Key | Default | Extra |
29  --------- -------------- ------ ----- --------- ------- 
30 | id      | int(11)      | NO   | PRI | NULL    |       |
31 | name    | varchar(20)  | YES  |     | NULL    |       |
32 | comment | varchar(100) | YES  |     | NULL    |       |
33  --------- -------------- ------ ----- --------- ------- 
34 rows in set (0.00 sec)
35 
36 #方法三:在所有字段后单独定义primary key
37 create table department3(
38 id int,
39 name varchar(20),
40 comment varchar(100),
41 constraint pk_name primary key(id); #创建主键并为其命名pk_name
42 
43 mysql> desc department3;
44  --------- -------------- ------ ----- --------- ------- 
45 | Field   | Type         | Null | Key | Default | Extra |
46  --------- -------------- ------ ----- --------- ------- 
47 | id      | int(11)      | NO   | PRI | NULL    |       |
48 | name    | varchar(20)  | YES  |     | NULL    |       |
49 | comment | varchar(100) | YES  |     | NULL    |       |
50  --------- -------------- ------ ----- --------- ------- 
51 rows in set (0.01 sec)
52 
53 单列主键

                               名字                               从表          关联             主表

单列主键

 

图片 3图片 4

      on delete cascade #同步删除
      on update cascade #同步更新

 1 ==================多列做主键================
 2 create table service(
 3 ip varchar(15),
 4 port char(5),
 5 service_name varchar(10) not null,
 6 primary key(ip,port)
 7 );
 8 
 9 
10 mysql> desc service;
11  -------------- ------------- ------ ----- --------- ------- 
12 | Field        | Type        | Null | Key | Default | Extra |
13  -------------- ------------- ------ ----- --------- ------- 
14 | ip           | varchar(15) | NO   | PRI | NULL    |       |
15 | port         | char(5)     | NO   | PRI | NULL    |       |
16 | service_name | varchar(10) | NO   |     | NULL    |       |
17  -------------- ------------- ------ ----- --------- ------- 
18 rows in set (0.00 sec)
19 
20 mysql> insert into service values
21     -> ('172.16.45.10','3306','mysqld'),
22     -> ('172.16.45.11','3306','mariadb')
23     -> ;
24 Query OK, 2 rows affected (0.00 sec)
25 Records: 2  Duplicates: 0  Warnings: 0
26 
27 mysql> insert into service values ('172.16.45.10','3306','nginx');
28 ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

  加上可直接删除,不用先删除从表,再删主表;

多列主键

   直接删除主表,从表中的数据也会删除.

五、auto_increment (自增约束)

 

步长increment与起始偏移量offset:auto_increment_increment,auto_increment_offset

 

 

 

3.--------偏移量:auto_increment_offset---------
==============没有设置偏移量的时候
create table dep(
id int primary key auto_increment,
name char(10)
);
insert into dep(name) values('IT'),('HR'),('EFO');
select * from dep;

================设置自增的时候以10开头
create table dep1(
id int primary key auto_increment,
name char(10)
)auto_increment = 10;
insert into dep1(name) values('IT'),('HR'),('EFO');
select * from dep1;

===============auto_increment_increment:自增步长
create table dep3(
id int primary key auto_increment,
 name char(10)
);
会话:通过客户端连到服务端(一次链接称为一次会话)
set session auto_increment_increment = 2; #会话级,只对当前会话有效
set global auto_increment_increment=2; #全局,对所有的会话都有效
insert into dep3(name) values('IT'),('HR'),('SALE'),('Boss');

-----------查看变量----------
show variables like '%auto_in%';#查看变量。只要包含auto_in就都查出来了

=========auto_increment_offset:偏移量 auto_increment_increment:步长===========
注意:如果auto_increment_offset的值大于auto_increment_increment的值,
则auto_increment_offset的值会被忽略

set session auto_increment_offset=2;
set session auto_increment_increment=3;
show variables like '%auto_in%';

create table dep4(
id int primary key auto_increment,
name char(10)
);
insert into dep4(name) values('IT'),('HR'),('SALE'),('Boss');

 

 六、foreign key (外键约束)

 

员工信息表有三个字段:工号  姓名  部门

          not null 与 default

公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费

  • unique
  • primary
  • auto_increment
  • foreign key

解决方法:

 

我们完全可以定义一个部门表

 

然后让员工信息表关联该表,如何关联,即foreign key

一、介绍

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

作用:用于保证数据的完整性和一致性

主要分为:

图片 5

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

UNSIGNED #无符号
ZEROFILL #使用0填充

图片 6

说明:

图片 7

#1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
#2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
sex enum('male','female') not null default 'male'

#必须为正值(无符号) 不允许为空 默认是20
age int unsigned NOT NULL default 20 
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

图片 8

如下图简单的表示了一下员工表与部门表的关系,即员工表的(dep_id)要关联部门表的id字段

 

图片 9

二、not null 与default

是否可空,null表示空,非字符串
not null - 不可空
null - 可空

默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

create table tb1(
    nid int not null defalut 2,
    num int not null

);

 

验证1:

 

图片 10图片 11

图片 12

mysql> create table t11(id int);# id字段默认可以为空
Query OK, 0 rows affected (0.05 sec)

mysql> desc t11;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | YES   |          | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.03 sec)
mysql> insert into t11 values(); #给t11表插一个空的值
Query OK, 1 row affected (0.00 sec)

#查询结果如下
mysql> select * from t11;
 ------ 
| id   |
 ------ 
| NULL |
 ------ 
1 row in set (0.00 sec)

图片 13

默认值可以为空

 

 

验证2:

图片 14图片 15

mysql> create table t12(id int not null);#设置字段id不为空
Query OK, 0 rows affected (0.03 sec)

mysql> desc t12;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | NO   |     | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.01 sec)

mysql> insert into t12 values();#不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

设置not null,插入值时不能为空

 

验证3:

图片 16图片 17

# 第一种情况
mysql> create table t13(id int default 1);
Query OK, 0 rows affected (0.03 sec)

mysql> desc t13;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | YES  |           | 1              |              |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.01 sec)

mysql> insert into t13 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;
 ------ 
| id   |
 ------ 
|    1  |
 ------ 
1 row in set (0.00 sec)


# 第二种情况
mysql> create table t14(id int not null default 2);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t14;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | NO      |         | 2               |             |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.01 sec)

mysql> select * from t14;
 ---- 
| id |
 ---- 
|  2 |
 ---- 
1 row in set (0.00 sec)

设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值

 

小练习:

创建学生表student2,设置每个字段的约束条件。

图片 18

mysql> create table student2(
    -> id int not null,
    -> name varchar(50) not null,
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> fav set('smoke','drink','tangtou') default 'drink,tangtou'
    -> );
Query OK, 0 rows affected (0.01 sec)

# 只插入了not null约束条件的字段对应的值
mysql> insert into student2(id,name) values(1,'mjj');
Query OK, 1 row affected (0.00 sec)

# 查询结果如下
mysql> select * from student2;
 ---- ------ ----- ------ --------------- 
| id | name | age | sex  | fav           |
 ---- ------ ----- ------ --------------- 
|  1 | mjj  |  18 | male | drink,tangtou |
 ---- ------ ----- ------ --------------- 
1 row in set (0.00 sec)

图片 19

 

多对一(一个表多条记录的某一字段关联另一张表的唯一一个字段):员工有部门,部门又有好多信息,所以
分开建了一张部门表,部门表的id 和员工表里面
的dep_id相关联。(dep_id要关联部门表的id字段
(注意:1.先建被关联的表,
        2.被关联的字段必须唯一
        3.先给被关联的表插入记录
 )


先建张部门表(被关联表)
create table dep(
id int not null unique,
#id int primary key auto_increment,
name varchar(50),
comment varchar(100)
);

再建张员工表(关联表)
create table emp_info(
id int primary key auto_increment,
name varchar(20),
dep_id int,
constraint FK_depid_id foreign key(dep_id) references dep(id) #references :关联
on delete cascade  #关联的表删了,被关联的表也删了
on update cascade  #关联的表修改了,被关联的表也修改了
);
#先给被关联的表初始化记录
insert into dep values
(1,'欧德博爱技术有限事业部','说的好...'),
(2,'艾利克斯人力资源部','招不到人'),
(3,'销售部','卖不出东西');


insert into emp_info values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3);

#修改
update dep set id =301 where id = 2;
select * from dep;
delect * from em_info;


如果部门解散了,员工也就走吧,就是部门表没了,
员工表也就没有了。

3、unique

中文翻译:不同的。在mysql中称为单列唯一

 

举例说明:创建公司部门表(每个公司都有唯一的一个部门)。

图片 20图片 21

mysql> create table department(
    -> id int,
    -> name char(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into department values(1,'IT'),(2,'IT');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from department;
 ------ ------ 
| id   | name |
 ------ ------ 
|    1 | IT   |
|    2 | IT   |
 ------ ------ 
2 rows in set (0.00 sec)
# 发现: 同时插入两个IT部门也是可以的,但这是不合理的,所以我们要设置name字段为unique 解决这种不合理的现象。

验证之前重复插入记录的操作是可行的,但是不符合场景

 

接下来,使用约束条件unique,来对公司部门的字段进行设置。

图片 22

#第一种创建unique的方式
#例子1:
create table department(
    id int,
    name char(10) unique
);
mysql> insert into department values(1,'it'),(2,'it');
ERROR 1062 (23000): Duplicate entry 'it' for key 'name'

#例子2:
create table department(
    id int unique,
    name char(10) unique
);
insert into department values(1,'it'),(2,'sale');

#第二种创建unique的方式
create table department(
    id int,
    name char(10) ,
    unique(id),
    unique(name)
);
insert into department values(1,'it'),(2,'sale');

图片 23

 

联合唯一:

图片 24

# 创建services表
mysql> create table services(
    -> id int,
    -> ip char(15),
    -> port int,
    -> unique(id),
    -> unique(ip,port)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc services;
 ------- ---------- ------ ----- --------- ------- 
| Field | Type      | Null | Key | Default | Extra |
 ------- ---------- ------ ----- --------- ------- 
| id        | int(11)   | YES   | UNI  | NULL       |             |
| ip        | char(15) | YES   | MUL  | NULL       |             |
| port    | int(11) | YES   |          | NULL       |             |
 ------- ---------- ------ ----- --------- ------- 
3 rows in set (0.01 sec)

#联合唯一,只要两列记录,有一列不同,既符合联合唯一的约束
mysql> insert into services values
    -> (1,'192,168,11,23',80),
    -> (2,'192,168,11,23',81),
    -> (3,'192,168,11,25',80);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from services;
 ------ --------------- ------ 
| id   | ip            | port |
 ------ --------------- ------ 
|    1 | 192,168,11,23 |   80 |
|    2 | 192,168,11,23 |   81 |
|    3 | 192,168,11,25 |   80 |
 ------ --------------- ------ 
3 rows in set (0.00 sec)

mysql> insert into services values (4,'192,168,11,23',80);
ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

图片 25

 

 

4.primary key

一个表中可以:

单列做主键
多列做主键(复合主键)

约束:等价于 not null unique,字段的值不为空且唯一

存储引擎默认是(innodb):对于innodb存储引擎来说,一张表必须有一个主键。

单列主键

图片 26

# 创建t14表,为id字段设置主键,唯一的不同的记录
create table t14(
    id int primary key,
    name char(16)
);

insert into t14 values
(1,'xiaoma'),
(2,'xiaohong');

mysql> insert into t14 values(2,'wxxx');
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'


#   not null   unique的化学反应,相当于给id设置primary key
create table t15(
    id int not null unique,
    name char(16)
);
mysql> create table t15(
    -> id int not null unique,
    -> name char(16)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t15;
 ------- ---------- ------ ----- --------- ------- 
| Field | Type         | Null | Key | Default | Extra |
 ------- ---------- ------ ----- --------- ------- 
| id        | int(11)  | NO     | PRI | NULL       |             |
| name   | char(16) | YES  |         | NULL       |             |
 ------- ---------- ------ ----- --------- ------- 
2 rows in set (0.02 sec)

图片 27

 

复合主键

图片 28图片 29

图片 30

create table t16(
    ip char(15),
    port int,
    primary key(ip,port)
);

insert into t16 values
('1.1.1.2',80),
('1.1.1.2',81);

图片 31

验证复合主键的使用

 

 运行结果如下图:

5.auto_increment

约束:约束的字段为自动增长,约束的字段必须同时被key约束

 

(重点)验证:

图片 32图片 33

# 创建student
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql>  desc student;
 ------- ----------------------- ------ ----- --------- ---------------- 
| Field | Type                  | Null | Key | Default | Extra          |
 ------- ----------------------- ------ ----- --------- ---------------- 
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
 ------- ----------------------- ------ ----- --------- ---------------- 
3 rows in set (0.17 sec)

#插入记录
mysql>  insert into student(name) values ('老白'),('小白');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
 ---- -------- ------ 
| id | name   | sex  |
 ---- -------- ------ 
|  1 | 老白   | male |
|  2 | 小白   | male |
 ---- -------- ------ 
2 rows in set (0.00 sec)

不指定id,则自动增长

 

图片 34图片 35

mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
 ---- -------- -------- 
| id | name   | sex    |
 ---- -------- -------- 
|  1 | 老白   | male   |
|  2 | 小白   | male   |
|  4 | asb    | female |
|  7 | wsb    | female |
 ---- -------- -------- 
4 rows in set (0.00 sec)

# 再次插入一条不指定id的记录,会在之前的最后一条记录继续增长
mysql>  insert into student(name) values ('大白');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
 ---- -------- -------- 
| id | name   | sex    |
 ---- -------- -------- 
|  1 | 老白   | male   |
|  2 | 小白   | male   |
|  4 | asb    | female |
|  7 | wsb    | female |
|  8 | 大白   | male   |
 ---- -------- -------- 
5 rows in set (0.00 sec)

也可以指定id

 

图片 36图片 37

mysql> delete from student;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  9 | ysb  | male |
 ---- ------ ------ 
1 row in set (0.00 sec)

#应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.03 sec)

mysql>  insert into student(name) values('xiaobai');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
 ---- --------- ------ 
| id | name    | sex  |
 ---- --------- ------ 
|  1 | xiaobai | male |
 ---- --------- ------ 
1 row in set (0.00 sec)

mysql>

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

 

了解:

图片 38图片 39

查看可用的 开头auto_inc的词
mysql> show variables like 'auto_inc%';
 -------------------------- ------- 
| Variable_name            | Value |
 -------------------------- ------- 
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
 -------------------------- ------- 
2 rows in set (0.02 sec)
# 步长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;

#强调:If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译:如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略 

# 设置完起始偏移量和步长之后,再次执行show variables like'auto_inc%';
发现跟之前一样,必须先exit,再登录才有效。

mysql> show variables like'auto_inc%';
 -------------------------- ------- 
| Variable_name            | Value |
 -------------------------- ------- 
| auto_increment_increment | 5     |
| auto_increment_offset    | 3     |
 -------------------------- ------- 
2 rows in set (0.00 sec)

#因为之前有一条记录id=1
mysql> select * from student;
 ---- --------- ------ 
| id | name    | sex  |
 ---- --------- ------ 
|  1 | xiaobai | male |
 ---- --------- ------ 
1 row in set (0.00 sec)
# 下次插入的时候,从起始位置3开始,每次插入记录id 5
mysql> insert into student(name) values('ma1'),('ma2'),('ma3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
 ---- --------- ------ 
| id | name    | sex  |
 ---- --------- ------ 
|  1 | xiaobai | male |
|  3 | ma1     | male |
|  8 | ma2     | male |
| 13 | ma3     | male |
 ---- --------- ------ 

auto_increment_increment和 auto_increment_offset

 

清空表区分delete和truncate的区别:

delete from t1; #如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大,删除速度比上一条快,且直接从零开始。

 

图片 40

6.foreign key

 

一 快速理解foreign key

之前创建表的时候都是在一张表中添加记录,比如如下表:

图片 41

 

公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费。

这个时候,

解决方法:

我们完全可以定义一个部门表

然后让员工信息表关联该表,如何关联,即foreign key

我们可以将上表改为如下结构:

图片 42

 

此时有两张表,一张是employee表,简称emp表(关联表,也就从表)。一张是department表,简称dep表(被关联表,也叫主表)。

 

创建两张表操作:

图片 43图片 44

#1.创建表时先创建被关联表,再创建关联表
# 先创建被关联表(dep表)
create table dep(
    id int primary key,
    name varchar(20) not null,
    descripe varchar(20) not null
);

#再创建关联表(emp表)
create table emp(
    id int primary key,
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id) 
);

#2.插入记录时,先往被关联表中插入记录,再往关联表中插入记录

insert into dep values
(1,'IT','IT技术有限部门'),
(2,'销售部','销售部门'),
(3,'财务部','花钱太多部门');

insert into emp values
(1,'zhangsan',18,1),
(2,'lisi',19,1),
(3,'egon',20,2),
(4,'yuanhao',40,3),
(5,'alex',18,2);

3.删除表
#按道理来说,删除了部门表中的某个部门,员工表的有关联的记录相继删除。
mysql> delete from dep where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

#但是先删除员工表的记录之后,再删除当前部门就没有任何问题

mysql> delete from emp where dep =3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
 ---- ---------- ----- -------- 
| id | name     | age | dep_id |
 ---- ---------- ----- -------- 
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  18 |      1 |
|  3 | egon     |  20 |      2 |
|  5 | alex     |  18 |      2 |
 ---- ---------- ----- -------- 
4 rows in set (0.00 sec)

mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dep;
 ---- ----------- ---------------------- 
| id | name      | descripe             |
 ---- ----------- ---------------------- 
|  1 | IT        | IT技术有限部门       |
|  2 | 销售部    | 销售部门             |
 ---- ----------- ---------------------- 
2 rows in set (0.00 sec)

View Code

 

上面的删除表记录的操作比较繁琐,按道理讲,裁掉一个部门,该部门的员工也会被裁掉。其实呢,在建表的时候还有个很重要的内容,叫同步删除,同步更新

接下来将刚建好的两张表全部删除,先删除关联表(emp),再删除被关联表(dep)

接下来:
重复上面的操作建表
注意:在关联表中加入
on delete cascade #同步删除
on update cascade #同步更新

修改emp表:

图片 45

create table emp(
    id int primary key,
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id) 
    on delete cascade #同步删除
    on update cascade #同步更新
);

图片 46

接下来的操作,就复合我们正常的生活中的情况了。

图片 47

#再去删被关联表(dep)的记录,关联表(emp)中的记录也跟着删除
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dep;
 ---- ----------- ---------------------- 
| id | name      | descripe             |
 ---- ----------- ---------------------- 
|  1 | IT        | IT技术有限部门       |
|  2 | 销售部    | 销售部门             |
 ---- ----------- ---------------------- 
2 rows in set (0.00 sec)

mysql> select * from emp;
 ---- ---------- ----- -------- 
| id | name     | age | dep_id |
 ---- ---------- ----- -------- 
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  19 |      1 |
|  3 | egon     |  20 |      2 |
|  5 | alex     |  18 |      2 |
 ---- ---------- ----- -------- 
4 rows in set (0.00 sec)

#再去更改被关联表(dep)的记录,关联表(emp)中的记录也跟着更改

mysql> update dep set id=222 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 赶紧去查看一下两张表是否都被删除了,是否都被更改了
mysql> select * from dep;
 ----- ----------- ---------------------- 
| id  | name      | descripe             |
 ----- ----------- ---------------------- 
|   1 | IT        | IT技术有限部门       |
| 222 | 销售部    | 销售部门             |
 ----- ----------- ---------------------- 
2 rows in set (0.00 sec)

mysql> select * from emp;
 ---- ---------- ----- -------- 
| id | name     | age | dep_id |
 ---- ---------- ----- -------- 
|  1 | zhangsan |  18 |      1 |
|  2 | lisi     |  19 |      1 |
|  3 | egon     |  20 |    222 |
|  5 | alex     |  18 |    222 |
 ---- ---------- ----- -------- 
4 rows in set (0.00 sec)

图片 48

 

 查看创建的表

图片 49

 修改id=301

图片 50

查看被关联表和关联表

图片 51

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:mysql字段约束操作,数据库之完整性约束

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。