一介绍
约束条件与数据类型的宽度一样, 都是可选参数
作用: 用于保证数据的完整性和一致性
主要分为:
PRIMARY KEY (PK) 标识该字段为该表的主键, 可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型, 而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用 0 填充
说明:
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...)
二 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,egon);
- insert into t2 values(1,alex);
- # 上面创建表的时候把 id 设置了唯一约束那么在插入 id=1, 就会出错了
多列唯一
-----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;
四 primary key (主键约束)
primary key 字段的值不为空且唯一
一个表中可以:
单列做主键
多列做主键(复合主键)
但一个表内只能有一个主键 primary key
============ 单列做主键 ===============
- # 方法一: not null+unique
- create table department1(
- id int not null unique, #主键
- name varchar(20) not null unique,
- comment varchar(100)
- );
- mysql> desc department1;
- +---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | NO | UNI | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+
- rows in set (0.01 sec)
- # 方法二: 在某一个字段后用 primary key
- create table department2(
- id int primary key, #主键
- name varchar(20),
- comment varchar(100)
- );
- mysql> desc department2;
- +---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+
- rows in set (0.00 sec)
- # 方法三: 在所有字段后单独定义 primary key
- create table department3(
- id int,
- name varchar(20),
- comment varchar(100),
- constraint pk_name primary key(id); #创建主键并为其命名 pk_name
- mysql> desc department3;
- +---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+
- rows in set (0.01 sec)
单列主键
============ 单列做主键 ===============
- #方法一: not null+unique
- create table department1(
- id int not null unique, #主键
- name varchar(20) not null unique,
- comment varchar(100)
- );
- mysql> desc department1;
- +---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | NO | UNI | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+
- rows in set (0.01 sec)
- #方法二: 在某一个字段后用 primary key
- create table department2(
- id int primary key, #主键
- name varchar(20),
- comment varchar(100)
- );
- mysql> desc department2;
- +---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+
- rows in set (0.00 sec)
- #方法三: 在所有字段后单独定义 primary key
- create table department3(
- id int,
- name varchar(20),
- comment varchar(100),
- constraint pk_name primary key(id); #创建主键并为其命名 pk_name
- mysql> desc department3;
- +---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+
- rows in set (0.01 sec)
单列主键
================== 多列做主键 ================
- create table service(
- ip varchar(15),
- port char(5),
- service_name varchar(10) not null,
- primary key(ip,port)
- );
- mysql> desc service;
- +--------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------------+-------------+------+-----+---------+-------+
- | ip | varchar(15) | NO | PRI | NULL | |
- | port | char(5) | NO | PRI | NULL | |
- | service_name | varchar(10) | NO | | NULL | |
- +--------------+-------------+------+-----+---------+-------+
- rows in set (0.00 sec)
- mysql> insert into service values
- -> (172.16.45.10,3306,mysqld),
- -> (172.16.45.11,3306,mariadb)
- -> ;
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- mysql> insert into service values (172.16.45.10,3306,nginx);
- ERROR 1062 (23000): Duplicate entry 172.16.45.10-3306 for key PRIMARY
多列主键
================== 多列做主键 ================
- create table service(
- ip varchar(15),
- port char(5),
- service_name varchar(10) not null,
- primary key(ip,port)
- );
- mysql> desc service;
- +--------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------------+-------------+------+-----+---------+-------+
- | ip | varchar(15) | NO | PRI | NULL | |
- | port | char(5) | NO | PRI | NULL | |
- | service_name | varchar(10) | NO | | NULL | |
- +--------------+-------------+------+-----+---------+-------+
- rows in set (0.00 sec)
- mysql> insert into service values
- -> (172.16.45.10,3306,mysqld),
- -> (172.16.45.11,3306,mariadb)
- -> ;
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- mysql> insert into service values (172.16.45.10,3306,nginx);
- 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 (外键约束)
员工信息表有三个字段: 工号 姓名 部门
公司有 3 个部门, 但是有 1 个亿的员工, 那意味着部门这个字段需要重复存储, 部门名字越长, 越浪费
解决方法:
我们完全可以定义一个部门表
然后让员工信息表关联该表, 如何关联, 即 foreign key
如下图简单的表示了一下员工表与部门表的关系, 即员工表的 (dep_id) 要关联部门表的 id 字段
多对一(一个表多条记录的某一字段关联另一张表的唯一一个字段): 员工有部门, 部门又有好多信息, 所以
分开建了一张部门表, 部门表的 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;
如果部门解散了, 员工也就走吧, 就是部门表没了,
员工表也就没有了
运行结果如下图:
查看创建的表
修改 id=301
查看被关联表和关联表
来源: http://www.bubuko.com/infodetail-2501175.html