表相关操作(二)之约束条件、关联关系、复制表
一、约束条件
1、何为约束
除了数据类型以外额外添加的约束
2、约束条件的作用
为了保证数据的合法性，完整性
3、主要的约束条件
NOT NULL #标识该字段不能为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
UNIQUE#标识该字段是唯一的
DEFAULT#为该字段设置默认值，字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
PRIMARY KEY#标识该字段为表的主键，可以唯一的标志记录
AUTO_INCREMENT#标识该字段的值自动增长，一般用于主键后面
FOREIGN KEY(FK)#FK为该表的外键
UNSIGNED # 无符号
ZEROFILL # 使用0填充
4、not null
非空约束，数据不能为空
create table t2(id int not null,name char(10) notnull);
mysql>desc t2;+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+mysql>insert into t2 value(null,null);
ERROR1048 (23000): Column 'id' cannot be null
验证
5、default
创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
mysql> create table t3(id int not null,name char(10) not null,sex char(10) default "male");
mysql>desc t3;+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| sex | char(10) | YES | | male | |
+-------+----------+------+-----+---------+-------+
default验证
6、unique
(1)唯一性约束，该字段的值不能重复。unique其实是一种索引，是一种数据结构，用于提高查询效率
mysql> insert into t4 value(1,'tom');
Query OK,1 row affected (0.07sec)
mysql> insert into t4 value(1,'tony');
ERROR1062 (23000): Duplicate entry '1' for key 'id'
View Code
(2)单列唯一约束
create table t1(id int unique,name char(10));
(3)多列唯一约束(字段应该都是唯一的)
create table t2(id int unique,name char(10),IDcard char(18), unique(id,IDcard));
7、primary key
(1)主键约束，用于唯一标识表中的一条记录，即该记录是唯一的并且不能为空
(2)主键与unique+not null的区别
unique是一种索引，必然存在硬盘上的某个文件中，是物理层面(实实在在存在的数据)
primary key是一种逻辑意义上的数据 (实际上不存在)
(3)主键的作用
区分两条记录
有主键就意味着有索引
可以建立关联关系
(4)单列主键
create table t1(id int primary key,name,char(10));
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 | |
+---------+--------------+------+-----+---------+-------+
View Code
(5)多列联合主键
create table t2(id int,name char(10),primary key(id,name));
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) notnull,
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 | |
+--------------+-------------+------+-----+---------+-------+
View Code
(6)一张表中只有一个主键，无论单列主键还是多列联合主键都是一个主键。
若是想在一张表中同时拥有多个“唯一索引”，可以用 多个“not null unique auto_increment ” + 一个 “ primary key”
8、auto_increment
(1)自动增长，通常搭配主键字段使用，可以自动为你的数据分配主键
在插入数据时，用 null 可以标识自动增长的主键，自动增长只能用于整型
create table t1(id int primary key auto_increment,name char(10));
insert into t1(null,'tom');
mysql> create table t5(id int primary key auto_increment,name char(10),sex char(10) default 'male');
mysql> insert into t5 value(null,'tom','male');
mysql> insert into t5 value(null,'Jack','male');
mysql> select * fromt5;+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | tom | male |
| 2 | Jack | male |
+----+------+------+
View Code
(2)修改自动增长的起始位置
alter table t9 auto_increment = 4;
9、foreign key
(1)外键专门用于为表和表之间，建立物理关联
create table dept(id int primary key,name char(10) notnull);
create table emp(id int primary key,name char(10) not null,foreign key(id) references dept(id));
(2)级联操作
外键加上以后，主表中的数据删除和更新时，都受到限制，解决的方案是为外键添加级联操作。
级联操作就是主表更改，从表就会同步更新或删除。
实现：在创建外键的时候，后面加上on update cascade on delete cascade
在外键、级联之间没有逗号，on update cascade 表示同步更新，on delete cascade 表示同步删除
create table dept(id int primary key,name char(10) notnull);
create table emp(
id int primary key,
name char(10) notnull,
foreign key(id) references dept(id)
on update cascade
on delete cascade
);
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) notnull
);#dpt_id外键，关联父表(department主键id)，同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) notnull,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade
);#先往父表department中插入记录
insert into department values
(1,'欧德博爱技术有限事业部'),
(2,'艾利克斯人力资源部'),
(3,'销售部');#再往子表employee中插入记录
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * fromemployee;+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | egon | 1 |
| 2 | alex1 | 2 |
| 3 | alex2 | 2 |
| 4 | alex3 | 2 |
+----+-------+--------+
#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * fromemployee;+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | egon | 1 |
| 3 | alex2 | 22222 |
| 4 | alex3 | 22222 |
| 5 | alex1 | 22222 |
+----+-------+--------+
View Code
(3)如何找出两张表之间的关系
分析步骤：#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段(通常是id)#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段(通常是id)#3、总结：#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
(4)建立表之间的关系
#一对多或称为多对一
二张表：员工、部门
一对多(或多对一)：一个部门有多个员工
关联方式：foreign key
#1. 先建主表
create table dept(
id int primary key auto_increment,
name char(10)
);#2. 再建从表
create table employee(
id int primary key auto_increment,
name char(10),
dp_id int,
foreign key(dp_id) references dept(id)
on update cascade
on delete cascade
);#3. 先插入主表数据
insert into dapt value(null,'tom');
insert into dapt value(null,'Jack');#4. 在插入从表数据
insert into employee value(null,'张三',1)
insert into employee value(null,'李四',1)
一对多或多对一
#多对多
二张表：老师、学生
多对多：一个老师可以教多个学生，一哥学生可以被多个老师教，双向的一对多，即多对多
关联方式：foreign key+一张新的表
create table teacher(id int primary key auto_increment,name char);
create table student(id int primary key auto_increment,name char);#创建一个新表作为关系表，专门用来存储两张表之间的关系
create table t_s(
t_id int,
s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references student(id),
primary key(t_id,s_id)
);
insert into student value(null,"tom");
insert into teacher value(null,"Jack");
insert into t_s value(1,1);
多对多
#一对一
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
关联方式：foreign key+unique
#一定是student来foreign key表customer，这样就保证了：#1 学生一定是一个客户，#2 客户不一定是学生，但有可能成为一个学生
create table customer(
id int primary key auto_increment,
name varchar(20) notnull,
qq varchar(10) notnull,
phone char(16) notnull
);
create table student(
id int primary key auto_increment,
class_name varchar(20) notnull,
customer_id int unique,#该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;
一对一
二、复制表
复制表结构＋记录 (key不会复制: 主键、外键和索引)
mysql> create table new_service select * fromservice;
只复制表结构
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table t4 like employees;
382
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
