mysql的约束和表关系

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

create table newTable AS select * from oldTable;

create table newPeople  AS select * from day2_test.people;

 

约束

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

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

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

 主键约束

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

设定为主键的字段一般和业务无关列有这些特点：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.(一定要外键字段)任选一张表为从表