第三篇：字段类型和约束条件

创建表完整语法

约束条件：可有可无

宽度：限制某些数据类型的存储大小

create table 表名(
    字段名1 字段类型(宽度) 约束条件,
    字段名2 字段类型(宽度) 约束条件
);

初始约束条件： not null

create table teacher(
    id int not null,  # 约束插入记录时id不能为空
    name varchar(16),
    age int
);
 
 
 
insert into teacher values(null, 'tank', 17);
insert into teacher values(1, 'tank', 17);

字段类型

1、确认表结构

2、字段与字段类型

整形

tinyint: 默认范围 -128, 127

create table t5(
    id tinyint,
    name varchar(16)
);
 
 
insert into t5 values(-128, 'tank'), (127, 'jason');
insert into t5 values(-129, 'tank');
insert into t5 values(128, 'jason');
insert into t5 values(12, 'sean');

int：默认范围是(-2147483648， 2147483647)

应用场景：id号、年龄...

create table t6(
    id int
);
 
# 如果指定子段类型的时候没有给出宽度，int类型会默认为11，也会默认展示宽度
insert into t6 values(-2147483649);
insert into t6 values(2147483648);
insert into t6 values(100);
 
create table t7(
    id int(3)
);
 
# 若插入超过设定宽度，则正常显示
insert into t7 values(123456);
 
# 若插入不足够设定的宽度，则以空格补全
insert into t7 values(1);

浮点型

应用场景：工资、身高、体重....

• float
• double
• decimal

三种方式示例

# 范围255是最大长度（包括.小数）, 30代表是小数的位数
create table t8(x float(255, 30));
create table t9(x double(255, 30));
create table t10(x decimal(65, 30));
 
 
# 插入数据
# 三种浮点型: 区别在于精确度不一样
insert into t8 values(1.111111111111111111111111111111);
insert into t9 values(1.1111111111111111111111111111);
insert into t10 values(1.1111111111111111111111111111);

字符类型

char定长字符

• 优点
  • 存取速度快（不用读取长度）
• 缺点
  • 浪费空间

char: 手机号、身份证号、银行卡号等...

# 创建表，限制字符宽度为4
create table t11(
    id int,
    name char(4)
);
 
 
# 此时正常插入4个字符
insert into t11 values(1, 'tank');
 
# 不足4个字符，会以空格补全，任然占用4个字符
insert into t11 values(1, 't');  # t+三个空格

varchar不定长字符

存入几个字符的数据，会在前面加上1bytes，因此最终大小就是实际数据的字符大小加上1bytes。

• 优点
  • 节省空间
• 缺点
  • 查取速度慢（需要先读取1bytes确定数据长度，再去读取数据）

create table t12(id int, name varchar(4));
insert into t12 values(1, 'egon');  # 1bytes + egon
insert into t12 values(2, 'tank');  # 1bytes + tank
insert into t12 values(3, 'sean');  # 1bytes + sean
 
insert into t12 values(4, 't');  # 1bytes + t
1bytes + egon  、 1bytes + tank、 1bytes + sean

日期类型

• date: 2019-12-11
• datetime: 2019-12-11 11:11:11
• time: 11:11:11
• year: 2019
• timestamp: 时间戳，修改数据的时候，时间戳会变为当前修改操作的时间

create table student(
    id int,
    name varchar(4),
    birth date,
    register datetime,
    work_time year,
    t_time time,
    update_time timestamp
);
 
 
insert into student values(1, '张全蛋', '2019-11-11', '2019-11-11 11:11:11','2019', '11:11:11', null);
insert into student values(2, 'HCY', '1000-11-11', '1980-11-11 11:11:11','2019', '11:11:11', null);

枚举与集合

enum：多选一

enumerate全称

创建表的时候，给字段多个值，插入数据的时候，该字段的数据必须是创建表的时候给字段值的范围内，且只能选择其中的一个，不能多选。

create table t13(
    id int,
    name varchar(4),
    gender enum('male', 'female', 'others')
);
 
 
# insert into 表名(字段名) values(字段名对应的值);
insert into t13(id, name, gender) values(1, 'tank', 'male');
 
# 严格模式下，选择枚举以外的值会报错
insert into t13(id, name, gender) values(2, 'gd', '人Y');

set：多选一 或 多选多

创建表的时候，给字段多个值，插入数据的时候，该字段的数据必须是创建表的时候给字段值的范围内，可以多选一，也可以选择多个。

create table t14(
    id int,
    name varchar(4),
    gender enum('male', 'female', 'others'),
    hobbies set('read', 'sing', '生蚝', 'HSNM', '架子鼓')
);
 
# 多选一
insert into t14 values(1, '大鸡J', 'others', 'HSNM');
# 多选多
insert into t14 values(2, 'tank', 'male', 'read,架子鼓,sing,生蚝');
 
# 多选多的顺序可不一
insert into t14 values(2, 'tank', 'male', 'read,架子鼓,sing,生蚝');
 

约束条件

not null + unique

null：空，限制该字段可以为空

not null非空，限制该字段的值不能为空

unique唯一，限制该字段的值不能重复

not null 和 unique加起来的效果就相当于主键的效果

# 非空
create table user1(
    id int not null,
    name varchar(4)
);
 
insert into user1(id, name) values(null, 'tank');
insert into user1(id, name) values(1, 'tank');
 
 
# unique 将某个字段设置为唯一的值
create table user2(
    id int not null unique,
    name varchar(4)
);
 
insert into user2(id, name) values(1, 'tank'), (2, 'sean');

primary key + auto_increment

primary key，主键，就是表中的索引，可以通过索引快速查找到某些数据，提高查询速度。

主键具有非空和唯一的特性

# 将id设置为主键，非空切唯一
create table user3(
    id int primary key,
    name varchar(4)
);
 
 
insert into user3(id, name) values(1, 'tank');
insert into user3(id, name) values(2, 'tank');

auto_increment：自增长

# 将id设置为自增
create table user4(
    id int primary key auto_increment,
    name varchar(4)
);
 
 
# 自增默认从0开始
insert into user4(name) values('tank');
insert into user4(name) values('sean');
insert into user4(name) values('egon');
insert into user4(name) values('大鸡哥');
 
# 若想自增从指定值开始，可插入第一条数据时先指定id的值;
insert into user4(id, name) values(10, 'tank');
insert into user4(name) values('sean');  # 11
insert into user4(name) values('egon');  # 12
insert into user4(name) values('大鸡哥');  # 13

当表中有自增长时，清空表中所有的数据，需要使用truncate，如果使用delete，并不会将自增长的字段恢复默认初始值0，而truncate不仅可以清空表中的数据，还可以将自增长字段重置为0。

create table user8(
    id int primary key auto_increment,
    name varchar(4)
);
 
insert into user8(name) values('tank');
insert into user8(name) values('大大大'), ('egon');
 
# 使用delete清空表中的数据
delete from user8;
 
# 使用truncate清空表中的数据，并且会将ID字段的自增长的值重置为0
truncate table user8;

foreign key

语法：foreign key(当前表中建立关系的外键字段) references 被关联表名(外键字段)
作用：标识该字段为该表的外键，用来建立两张表之间的关系

将所有数据存放在一张表中的弊端：

• 结构不清晰
• 浪费空间
• 可扩展性差

如何解决？
将一张表进行拆分，利用外键产生联系。

表与表之间的关系

一对多

凡是单向多对一的表关系，称之为一对多的外键关系

单向的意思：例如多个人可以在同一个部门，但是多个部门却不能有同一个人（同一个人存在多个部门）。

# 使用外键创建表的时候，必须先建立被关联的表，再建立关联表
-- 被关联表（dep表）
create table dep(
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(255)
);

-- 关联表（emp）
create table emp(
    id int primary key auto_increment,
    name varchar(16),
    age int,
    gender enum('male', 'female', 'others') default 'male',
    dep_id int not null,
    foreign key(dep_id) references dep(id)
);

# 插入数据时，必须先插入被关联表（dep）的数据，再插入关联表（emp）的数据
-- 被关联表（dep）
insert into dep(dep_name, dep_desc)
values('nb_外交部', '国际形象大使部门'),
('sb_教学部', '造程序员部门!!!!'),
('技术部', '技术有限部门');

-- 关联表（emp）
insert into emp(name, age, gender, dep_id)
values('tank', 17, 'male', 1),
('jason', 70, 'male', 2),
('sean', 50, 'male', 2),
('egon', 88, 'male', 2),
('owen', 95, 'female', 3);

# 如果插入的数据中，不在被关联表字段的值中，就会报错
insert into emp(name, age, gender, dep_id) values('大饼', 100, 'others', 999);

对于上面没有使用级联创建的关联表，删除关联表的记录，才能修改和删除被关联表中的记录，这样很不方便，为此我们在使用外键建立关联表的时候，需要使用级联更新和级联删除。
级联更新：on update cascade
级联删除：on delete cascade

# 创建表
-- 被关联表（dep2）
create table dep2(
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(255)
);

-- 关联表（emp2）
create table emp2(
    id int primary key auto_increment,
    name varchar(16),
    age int,
    gender enum('male', 'female', 'others') default 'male',
    dep_id int not null,
    foreign key(dep_id) references dep2(id)
    on update cascade  # 此处使用级联更新
    on delete cascade   # 此处使用级联删除
);

# 插入数据
-- dep2
insert into dep2(dep_name, dep_desc) values('nb_外交部', '国际形象大使部门'),
('sb_教学部', '造程序员部门!!!!'),
('技术部', '技术有限部门');

-- emp2
insert into emp2(name, age, gender, dep_id)
values('tank', 17, 'male', 1),
('jason', 70, 'male', 2),
('sean', 50, 'male', 2),
('egon', 88, 'male', 2),
('owen', 95, 'female', 3);

# 更新数据
-- 更新纪录
update dep2 set id=200 where id=1;

-- 删除记录
delete from dep2 where id=200;

注意：mysql中没有多对一，只有一对多。

多对多

利用第三张表，为两张表建立多对多外键关系。

-- book
create table book(
        id int primary key auto_increment,
        title varchar(20),
        price int,
        book_content varchar(255)
);

--  author:
  create table author(
          id int primary key auto_increment,
          name varchar(16),
          age int
  );

-- book2author:(第三张表)
create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

# 插入数据
-- book
insert into book(title, price, book_content) values
('红楼梦', 199, '讲述朦胧时光的小故事'),
('python从入门到断气', 2000, '学习如何一夜秃头'),
('三体', 200, '跟着大佬进入宇宙奇幻世界');

-- author
insert into author(name, age) values
('egon', 68),
('jason', 88);

-- book2author：
insert into book2author(book_id, author_id) values
(1, 1),
(1, 2),
(2, 2),
(3, 1);

# 报错，插入的数据必须存在于第三张表中
insert into book2author(book_id, author_id) values (4, 4);

# 更新或删除记录
-- 更新
update book set price=6666 where id=1;
update book set id=4 where id=1;

-- 删除
delete from book where id=4;

一对一

两张表之间的关系一一对应，将一张数据量比较大的表，拆分成两张表。

foreign key应该建在 使用频率较高的一方

# 创建表
-- 被关联表
create table customer(
    id int primary key auto_increment,
    name varchar(16),
    media varchar(32)
);

-- 关联表
create table student(
    id int primary key auto_increment,
    addr varchar(255),
    phone char(11),
    id_card char(18),

    # 外键必须设置为唯一的
    customer_id int unique,
    foreign key(customer_id) references customer(id)
    on update cascade
    on delete cascade
);

-- 插入数据
insert into customer(name, media) values
('hcy', 'facebook'),
('zsb1', 'ig'),
('zsb2', 'vk'),
('hb', '探探');


insert into student(addr, phone, id_card, customer_id) values
('上海', '15214546711', '440888888888888888', 1),
('北京', '18888888888', '440777777777777777', 2);

default

为该字段设置默认值

# not null + default
create table student(
    id int,
  name varchar(16) not null,
  gender enum('male', 'female', 'others') default 'male'
);
 
insert into student(id, name) values(1, 'tank');
insert into student(id, name, gender) values(2, 'jason', 'female');
 

unsigned

无符号模式，限制该字段插入的值中不能带有正负符号

create table user5(
    id int unsigned
);
 
 
# 报错
insert into user5 values(-100);
 
# 无符号，正常插入值
insert into user5 values(0);
insert into user5 values(100);

zerofill

当字段的长度没有达到限定的宽度时，使用0代替空格填充

create table user8(
    id int primary key auto_increment,
    name varchar(4)
);
 
 
insert into user7 values(100);