3 数据类型之（日期、枚举和集合）+约束条件

内容回顾

针对库的基本SQL语句

create database db1;
show create database db1;
show databases;
alter database db1 charset='gbk';
drop database db1;

针对表的基本SQL语句

select database();
use db1;
creata table t1(id int);
show tables;
show create table t1;
desc t1;
alter table t1 rename t11;
drop table t1;

针对记录的基本SQL语句

create table t1(id int,name varchar(32));
insert into t1 values(1,'jason');
insert into t1 values(2,'kevin'),(3,'tony'),(4,'jack');
select * from t1;
select name from t1;
select * from mysql.user;
update t1 set name='jasonNB' where id=1;
delete from t1 where id=2;
delete from t1;

存储引擎

show engines;
​
# 四个
    myisam
    innodb
    memory
    blackhole
# 不同之处
    3个 2个 1个 1个
# 验证不同
    create table t1(id int) engine=myisam;

创建表的完整语法

create table 表名(
    字段名称1 字段类型(数字) 约束条件,
    字段名称2 字段类型(数字) 约束条件,
    字段名称3 字段类型(数字) 约束条件
);
MySQL基本数据类型

1.整型
    tinyint smallint int bigint
    存储范围不一样并且都是自带正负号
        约束条件unsigned
2.浮点型
    float double decimal
    精确度不一样 一般情况下使用float足以 越精确需要的资源越多
3.字符类型
    char varchar
    定长  变长
数字的含义

# 1.数字在数字类型中不是用来限制存储长度 而是用来控制展示长度
    我们在创建数字类型的时候不需要考虑数字
# 2.数字在其他类型中都是用来限制存储长度的
sql_mode

show variables like '%mode%';
​
set session  
set global 
配置文件
​
char_length()
zerofill
'strict_trans_tables'
'pad_char_to_full_length'

 

目录

基本数据类型之日期相关类型

基本数据类型之枚举与集合

约束条件

unsigned
zerofill
not null
default
unique
primary key
auto_increment
外键  # 很重要

 

一、数据类型补充

1、基本数据类型之日期相关类型

date        年月日
datetime    年月日时分秒
time        时分秒
year        年
​
create table user(
    id int comment '编号',
    name varchar(255) comment '姓名',
    reg_time datetime comment '注册时间',
    birthday date comment '生日',
    study_time time comment '学习时间',
    born_year year comment '年份'
);
"""
字段也可以加类似于注释的说明
    comment
"""
# 此处先人工智能模拟 
insert into user values(1,'jason','2000-11-11 11:11:11','2000-11-11','11:11:11','2000');

 

 

 

 

 

 

 

 

2.基本数据类型之枚举与集合类型

（1）.枚举

# 枚举
    多选一
    '''提前定义好数据之后 后续录入只能录定义好的内容之一'''
create table t1(
    id int,
    name char(6),
    gender enum('male','female','others')
);
insert into t1 values(1,'jason','male');  # 正常
insert into t1 values(2,'kevin','男');  # 报错
​

 

 

 

 

 

（2）.集合

# 集合
    多选多(包含多选一)
create table t2(
    id int,
    name char(6),
    hobbies set('basketball','football','doublecolorball')
);
insert into t2 values(1,'jason','basketball');
insert into t2 values(2,'kevin','football,doublecolorball');
insert into t2 values(3,'jkson','pingpongball');

 

 

 

 

 

二、约束条件

# 约束条件相当于是在字段类型之上添加的额外约束(条件)

1.unsigned

  无符号
        id int unsigned

2.zerofill

  0填充
        id int zerofill

 

3.not null

非空(不能为空)
    create table t3(id int,name varchar(16));
    insert into t3(name) values('jason');

     在插入数据的时候还可以指定表字段
    insert into t3(name) values('jason')

create table t4(id int,name varchar(16) not null);
 insert into t4(id) values(1);

 

 

 

 

 

 

 

 

 

4.default

    默认值(用户给了就用用户的 用户不给就是要默认的)
    create table t5(id int,name varchar(16) default '匿名用户');
    insert into t5(id) values(1);
    insert into t5(id,name) values(2,'jason');

 

 

 

 

 

5.unique

   唯一(保证字段(一个、多个)在整个表中没有重复的数据)
    # 单列唯一
        create table t6(id int,name varchar(16) unique);
        insert into t6 values(1,'jason'),(2,'jason');
    # 联合唯一
        create table server(
            id int,
            host varchar(32),  
            port varchar(32),
            unique(host,port)
        );

 单列唯一

 

 

 联合唯一

 

 

 

 6.primary key

   主键
        1.但从约束条件上而言主键相当于not null + unique(非空且唯一)
        2.主键的功能目前简单的理解为能够加快数据的查询速度相当于书的目录
        3.InnoDB存储引擎规定每张表都必须有且只有一个主键
            2.1.表中没有任何的主键和其他约束条件
                InnoDB默认会采用一个隐藏字段作为表的主键
            2.2.表中没有主键但是有非空且唯一的字段
                InnoDB会自动将该字段升级为主键
        结论:每张表都必须要有一个id(sid nid uid)字段并且该字段就是主键
    # 单列主键
        create table t7(
            id int primary key,
            name varchar(32)
        );
        create table t8(
            id int,
            name varchar(32),
            age int not null unique,
            phone bigint not null unique,
            num int not null unique
        );
    # 多列主键(联合主键)
        create table t9(
            id int,
            age int,
            name varchar(16),
            primary key(id,age)
        );

 单列主键

 

 

 

 多列主键

 

 

 

7.auto_increment

    自增(专门配合主键一起使用的 让主键能够自增)
    create table t10(
        id int primary key auto_increment,
        name varchar(32)
    );
    
"""
在插入数据的时候还可以指定表字段
    insert into t3(name) values('jason')
"""

 

 

 

 

 

 

 

主键自增特性

主键的自增是不会收到delete from删除操作的影响
truncate既可以清空表数据也会重置主键值
truncate t10; #清空充值t10表

 

 

 

 

 

 

 

 

 

8.外键

# 前戏

1.定义一张员工表
    id  name    age     dep_name    dep_desc
    1   jason   18      外交部     搞外交
    2   kevin   28      教学部     教书育人
    3   tony    38      教学部     教书育人
    4   oscar   48      安保部     保家卫国
    5   jackson 58      财务部     发工资
"""
上述表不合理之处
    1.表内部数据混乱(可忽略)
    2.反复的录入重复数据(可忽略)
    3.修改数据太过繁琐  浪费磁盘空间(可忽略)
    4.极大地影响了操作数据的效率
"""
2.将上述表拆分成两张表
    id  name    age 
    1   jason   18
    2   kevin   28
    3   tony    38
    4   oscar   48
    5   jackson 58
    id  dep_name    dep_desc
    1   外交部     搞外交
    2   教学部     教书育人
    3   安保部     保家卫国
    4   财务部     发工资
"""拆分完之后解决了上述四个缺陷"""

 

 

 

 

 

 

 

​
外键

  用来记录表与表之间的关系

 

三、如何查找表关系

1.多对一
2.多对多
3.一对一
4.没有关系

 

​
"""查找表关系:换位思考"""
1.多对一

书籍表与出版社表
    1.先站在书籍表
        问:一本书能够对应多个出版社
        答:不可以
    2.再站在出版社表
        问:一个出版社能否对应多本书
        答:可以
    结论:一个可以一个不可以 表关系为"多对一"
        那么外键字段建在"多"的一方
# 针对具有表关系的SQL建议先写普通字段 最后再考虑外键字段
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    pub_id int,
    foreign key(pub_id) references publish(id)
);
create table publish(
    id int primary key auto_increment,
    name varchar(32)
);

 

 

 

 

 

 

 

外键约束

1.在创建表的时候需要先创建被关联表(没有外键的表)
2.在写入数据的时候也需要先写入被关联表(没有外键的表)
3.被关联表里面的数据无法直接删除和修改关联字段的操作
​
# 级联更新 级联删除

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade 
    on delete cascade
);
create table dep(
    id int primary key auto_increment,
    name varchar(32)
);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 级联更新，级联删除

 

 

 

 

 

 

 

 

 

2.多对多

以书籍表与作者表为例
    1.先站在书籍表的基础之上
        问:一本书能否对应多个作者
        答:可以
    2.在站在作者表的基础之上
        问:一个作者能否对应多本书
         答:可以
    结论:两个都可以那么表关系就是"多对多"
        外键字段建在第三张关系表中
    
​
# 先写普通字段之后在考虑外键字段
'''错误的创建方式'''
create table book1(
    id int primary key auto_increment,
    title varchar(32),
    author_id int,
    foreign key(author_id) references author1(id) 
    on update cascade 
    on delete cascade
);
create table author1(
    id int primary key auto_increment,
    name varchar(32),
    book_id int,
    foreign key(book_id) references book1(id) 
    on update cascade 
    on delete cascade
);
'''正确的创建方式'''
create table book1(
    id int primary key auto_increment,
    title varchar(32)
);
create table author1(
    id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    foreign key(author_id) references author1(id) 
    on update cascade 
    on delete cascade,
    book_id int,
    foreign key(book_id) references book1(id) 
    on update cascade 
    on delete cascade
);
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.一对一和没有关系

场景    
客户表与学生表
    QQ用户表
以用户表与用户详情表为例
    1.先站在用户表的基础之上
        问:一个用户能否对应多个用户详情
        答:不可以
    2.在站在用户详情表基础之上
        问:一个用户详情能否对应多个用户
         答:不可以
    结论:换位思考之后两边都不可以 那么表关系有两种
            1.没有关系(用膝盖都能判断出来)
            2.一对一关系
          外键字段建在哪里?
            理论上建在任何一方都可以但是推荐建在查询频率较高的表中（想象QQ加好友界面，不点开详情只能看到基本的昵称，QQ号，这个是查询频率较高的（user表），要想看更多信息需要点开详情看）

create table user(
    id int primary key auto_increment,
    name varchar(32),
    detail_id int unique,
    foreign key(detail_id) references user_detail(id)
    on update cascade
    on delete cascade
);
#用户详情表
create table user_detail(
    id int primary key auto_increment,
    addr varchar(32),
    phone bigint
);