mysql的约束和表关系

根据查询的结果，复制出一个新表

create table newTable AS select * from oldTable;

create table newPeople  AS select * from day2_test.people;

约束

引入：如果某一列如id列，有重复的数据，无法准确定位，有的列有空值null;

解决：需要给列约束，让数据合法

约束其实就是一种限制，用于修饰表中的列，通过这种限制来保证数据的准确性，有效性和完整性

约束名称	关键字	作用
主键约束	primary key	唯一+非空
唯一约束	unique	唯一
非空约束	not null	非空
默认值约束	default 默认的值	指定默认值
外键约束	foreign key	多表之间的关系约束

 主键约束

主键概述：设定表中某一字段，那么该字段所在列的数据能够唯一的标识表中每一行数据（作用）

设定为主键的字段一般和业务无关列有这些特点：1：唯一，2：非空，3：与业务无关

 主键的定义和删除语法

        1在建表的时候指定

create table 表名 (id int primary key,其他字段...);

        2在已有表中指定（原来表中没有主键） 

 alter table 表名 add primary key(字段名);

         3删除主键约束

alter table 表名 drop primary key;

-- 在已有表中添加索引
alter table people add primary key (id);

-- 创建的时候添加
create table student2
(
    id int primary key,
    name varchar(10)
);
-- 删除主键约束
alter table student2 drop primary key;

 练习

create table student3
(
    id int primary key,
    name varchar(10),
    age int
);
-- 测试主键
insert into student3 values(1,'hhh',18);
insert into student3 values(1,'aaa',20);-- err
insert into student3 values(null,'ccc',56);-- err

-- 删除主键
alter table student3 drop primary key ;

-- 添加主键
alter table student3 add primary key (id);

主键列下数据不能重复

索引我们把主键列交给mysql（主键列的插入的数据，交给mysql管理）

主键自增：
主键如果让我们自己添加可能有重复的主键导致数据添加失败。所以我们每次插入新的记录时，数据库都是自己生成主键的值

        主键添加语法：

字段名 字段类型 primary key auto_increment;

注意，需要让主键自增，主键必须是整数类型

create table student4
(
    id int primary key auto_increment,-- 主键自动增加
    name varchar(10)
);

insert into student4(name) values('aaa');
insert into student4(name) values('bbb');
insert into student4(name) values('ccc');
insert into student4 (id, name) values (null,'ddd');

delete from student4 where id=3;-- 把第三行删除

insert into student4(name) values('eee');-- id=5,可见把id=3删除后，之后生成的id不会重复

insert into student4(id, name) values(100,'fff');--  使用100覆盖mysql管理的主键值
insert into student4(name) values('ggg'); -- id=101

select * from student4;

        主键自增的注意事项

1.主键列下的数据由mysql维护（从1开始，每次增加1）

2.mysql维护过的主键值，不能重复使用

3.当程序员干预主键，就会拿插入的主键值，覆盖mysql管理的主键值 

        delete和truncate 删表对id的影响

1.delete删除表中的数据，不重复auto_increment的值

2.truncate摧毁表，重建表，auto_increment的值重置为1 

 唯一约束

一个表只能有一个主键 

作用

被唯一约束的字段，本列数据不允许出现重复的数据，null除外，null可以重复

 语法

1.create table 表名 (id int unique,字段名...);

2.alter table 表名 add unique(字段名)；

create table student5
(
    id int primary key auto_increment,
    name varchar(10)
);

alter table student5 add unique (name);
-- 插入相同的name值时，会插入失败，但是id(主键值)还是会自增

 非空约束

 作用：

被非空约束的字段，本列数据不允许出现null数据。插入数据若为null就会报错

语法

1.create table 表名 (id int not null);

2.alter table 表名 modify 字段名 类型 not null;

create table student6
(
    id int primary key auto_increment,
    name varchar(10) unique not null
);

alter table student5 modify name varchar(10) not null ;

 非空+唯一约束和主键约束的区别

1.主键约束在一张表只能存在一个，非空加唯一约束没有限制，可以存在多个

2.主键约束有自动增长，非空+唯一约束没有自动增长

默认值约束

作用

被默认值约束的字段（列），相当于给字端添加默认值，插入数据时如果字段没有被赋值，则使用默认值

语法

默认值约束需要使用的关键字:default

1.创建表时指定

create table 表名

(

字段名 字段类型 default 默认值，

其他字段

); 

2.给已有表指定字段添加默认值

alter table 表名 modify 字段名 字段类型 default 默认值；

create table student7
(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    address varchar(10) default '天津'
);
-- 插入null值，不代表没有给数据
insert into student7 (id, name, address) values (null,'aaa',null);

-- 没有给值
insert into student7 (name) values ('bbb');

 

表关系

 一对一

一对多

多对多

一对多表关系

例子：一个用户可以有多个订单，一个订单只能对应一个用于

我们约定，"一方"（用户表）叫主表或1表，"多方"（订单表）叫从表或多表 

设计表时，我们通常在多表中添加一个字段，用于存储主表主键的值，这个字段叫外键字段。

外键约束的作用：
1.建立表和表之间的关系

2.约束外键下的数据和主表数据下的数据保持一致（一致性，完整性）

多对多表关系

例子：老师和学生之间的关系

会创建第三张中间表（有两个字段，分别是学生和老师的主键），由中间表承担外键字段（降低多对多的复杂关系，变成两个一对多）

一是老师表和学生表，多是中间表

这个中间表中会有多个相同学生主键，和多个相同老师主键

所以一个学生对应多个相同的学生主键，一个学生主键对应一个学生

所以是一对多

 问题一：怎么建立表和表之间的关系

答案：外键字段+主键字段

问题二:外键字段应该创建在哪个表

外键字段创建，需要考虑多表关系

一对多:外键字段创建在多表上

多对多：创建第三张表，外键字段创建在中间表上

一对一：1.两张表合并为一张表

                2.(一定要外键字段)任选一张表为从表

外键约束

概述：外键就是从表中设定一个字段，用来保存主表的主键值，外键约束了从表的外键值只能是主表的主键值

外键值定义语法

方式一：创建表时创建外键

create table 表名

(

其他字段,

外键字段名 int,

constraint 外键名 foreign key （当前外键字段名）references 主表名(主表主键名)

);

方式二：修改字段名为外键约束

alter table 表名 add constraint 外键名 foreign key（当前表外键字段名）references 主表名
 

 外键删除语法

alter table 表名 drop foreign key 外键名称;

create table department
(
    id int primary key auto_increment,
    name varchar(10)
);

insert into department(name) values ('研发部'),('宣传部');

create table employee
(
    id int primary key auto_increment,
    name varchar(10),
    age int,
    dep_id int,   -- 外键字段（和department表中的id列有关联）
    -- 添加外键约束
    constraint FK_employee_dept_id foreign key(dep_id) references department(id)
);

insert into employee(name,age,dep_id) values ('aa',18,1),('bb',19,1);
insert into employee(name,age,dep_id) values ('bb',20,3); -- err,主表的主键中没有3

 注意事项

1：添加数据时：
先添加主表中的数据，再添加从表中的数据

2：删除数据时：

先删除从表中的数据，再删除主表中的数据

3：修改数据时：
如果主表中的主键被从表引用了，不能修改此主键的值

外键的级联：
级联操作就是在修改或者删除主键时可以同时对表的外键进行删除修改

语法

 on update cascade（同步更新）

on delete cascade（同步删除）

alter table employee drop foreign key FK_employee_dept_id;
-- 给外键添加外键约束，级联更新，级联删除
alter table employee add constraint FK_employee_dept_id foreign key (dep_id) references department(id)
on delete cascade
on update cascade;