MySQL基础之约束条件

发表于 更新于 分类于

内容概要

  • 约束条件
  • 约束条件之外键(重点)
  • 表与表之间建立关系
  • 查询关键字(重点)
1
2
3
4
5
6
7
8
9
select
from
where
group by
having
distinct
order by
limit
regexp

内容详细

约束条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# 在字段类型的基础之上添加额外的约束

1 unsigned ---> 无符号(正负号)

2 zerofill ---> 填充

3 not null ---> 非空
    create table t1(
        id int,
        name varchar(32) not null
    );
    
4 default ---> 默认值
    create table t2(
        id int,
        name char(32),
        gender enum('male','female') default 'male'
    );
    
5 unique ---> 唯一
# 单列唯一：
    create table t3(
        id int,
        name varchar(32) unique
    );
# 多列唯一
    create table t4(
        id int,
        host varchar(32),
        port int,
        unique(host,port)
    );
    
6 primary key ---> 主键
    """
    1 单从限制角度上来看 主键相当于是非空且唯一
        id int primary key == id int not null unique
        create table t5(
            id int primary key                        ------>书写规范
        );
    
    2 innodb存储引擎规定了再创建表的时候必须有且只有一个主键
        2.1 为什么之前创建表的时候没有主键也可以呢？
            当表中没有任何的约束条件字段，为了能够顺利的创建出表，innodb存储引擎会采用内部隐藏的字段作为         主键
            隐藏主键意味着我们无法使用到，正常主键其实可以帮助我们加快数据的查询速度，但因为被隐藏了，所以          其实在速度上并没有变化 ---> 要自己设定主键
            
        2.2 没有主键但是有非空且唯一的字段，那么会自动将该字段升级为主键
            create table t6(
                id int,
                age int not null unique,    <----- 会自动将age升级为主键
                pwd int not null unique
                );
    
    结论：   我们在创建表的时候一般都会创建一个id字段(序号\编号)
         并且我们会将该字段设置为主键
            id int primery key
            ps：(id的名称可以不固定，uid、cid、pid...)
    """
    
7 auto_increment ---> 自增
# 单一主键
    专门用来给key键使用(就理解成是专门给主键用的)
    create table t7(
        id int primary key auto_increment,
        name varchar(32)
    );
# 联合主键(了解)
    create table t8(
        id int,
        nid int,
        name varchar(32),
        primary key(id,nid)...
    );
    
'''
以后的主键字段固定写法：id int primary key atuo_increment
'''

主键的特征

1
2
3
# 1 delete from   ---> 不会影响主键的自增

# 2 truncate(了解) ---> 清空数据并且重置主键 (还不如删表重建)

添加数据的两种方式

1
2
3
1 insert into t1 values(...) # 按照字段顺序一一传值(用得少)

2 insert into t2(name) values(...) # 指名道姓的传值

外键前戏

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
'''员工表'''
id emp_name emp_salary dep_name dep_desc

# 问题
1 表结构不够明确
    - 到底是员工表还是部门表

2 浪费存储空间

3 表拓展性极差

# 拆分
上诉三个问题都能够解决，但员工与部门之间就没有了关联

---> 外键字段
       ---> 用来建立表与表之间的关系的字段

表关系判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
表关系分类
    1 一对多
    2 多对多
    3 一对一
    
表关系判断  ---> '换位思考'

    1 以员工表与部门表为例
        1.1 先站在员工表角度
            问：一名员工能否对应多个部门
            答：否
        2.1 再站在部门表角度
            问：一个部门能否对应多名员工
            答：能
        结论：两边一个能一个否 ---> 表关系就是：'一对多'
    '''
    针对一对多的表关系
        外键字段建立在多的一方 ---> 建在员工表中
    '''
    
    2 以书籍表与作者表为例
        2.1 先站在书籍表角度
            问：一本书能否对应多名作者
            答：能
            
        2.2 再站在作者表角度
            问：一本书能否对应多名作者
            答：能
        结论：两边都能  ---> 表关系就是：'多对多'
    '''
    针对多对多表关系
        外键字段并不会建在任何一张关系表
        而是单独开设一张新表专门存储
    '''
    
    3 以用户表与用户详情表为例
        3.1 先站在用户表角度
            问：一名用户能否对应多个用户详情
            答：否
        3.2 再站在用户详情表角度
            问：一个用户详情能否对应多名用户
            答：否
        结论：两边都否  ---> 表关系就是：'一对一'
        
    '''
    针对一对一表关系
        外键字段建在任何一方都可以
        但是推荐建在查询评率较高的表中   ---> 用户表
    '''

SQL语句实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--------------------------------- 一对多 --------------------------------

# 1 先创建基本字段
# 2 再考虑外键字段
create table emp(
    id int primary key auto_increment,
    emp_name varchar(32),
    emp_salary int,
    dep_id int,
    foregin key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)
);
'''
1 在创建表的是先创建被关联的表(没有外键字段的表)
2 在录入数据的时候先录入被关联表的数据
3 修改或者删除被关联表数据
'''
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

------------------------------------ 多对多 -------------------------------------
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

------------------------------------ 一对一 -------------------------------------
create table userinfo(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    detail_id int unique,
    foreign key(detail_id) reference userinfo_detail(id)
    on update cascade
    on delete cascade
);
create table userinfo_detail(
    id int primary key auto_increment,
    addr varchar(64),
    phone varchar(32)
);

级联更新级联删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)
);
create table dep(
    id int primary key auto_increment,
    emp_name varchar(32),
    emp_salary int
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade # 级联更新
    on delete cascade # 级联删除
);

总结：

1
2
3
七个约束条件：unsigned，zerofill，not null，default，unique，primary key，auto_increment

表关系判断(外键)：一对多 --- 多对多 --- 一对一