SQL语句基本数据类型及约束条件

基本数据类型之日期相关

# 格式
​
date        年月日
datetime    年月日时分秒
time        时分秒
year        年
​
create table t1(id int comment "编号",
                name varchar(32) comment "姓名",
                data1 date comment "数据1",
                data2 datetime comment "数据2",
                data3 time comment "数据3",
                data4 year comment "数据4");
​
mysql> insert into t1 values(1,"jason","2021-11-10","2021-11-10 19:54:28","19:54:28","2021");
Query OK, 1 row affected (0.08 sec)
​
mysql> select * from t1;
+------+-------+------------+---------------------+----------+-------+
| id   | name  | data1      | data2               | data3    | data4 |
+------+-------+------------+---------------------+----------+-------+
|    1 | jason | 2021-11-10 | 2021-11-10 19:54:28 | 19:54:28 |  2021 |
+------+-------+------------+---------------------+----------+-------+
1 row in set (0.00 sec)
# 注：comment无实际意义，类似于注释的说明

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

# 枚举
    多选一  关键字"enum"
    
 "提前定义好的数据后期录入只能按照之前输入的内容，比如说性别，提前规定好后，输入其他数据会报错"
create table t2(
                id int,
                name varchar(8),
                gender enum("male","female","others")
               );
insert into t2 values(1,"jason","male"); # 正常
insert into t1 values(2,'kevin','男');    # 报错
​
​
# 集合
    多选  关键字"set"
    
    create table t3(id int,
                    name char(8),
                    hobbies set("read","music","basketball","swimming"));
    insert into t3 values(1,"jason","music,dance");     # 报错
    insert into t3 values(2,"jason1","music,swimming");  # 正常

约束条件

# 创建表格的完整语句
create table 表名(
    字段名称1 字段类型(数字) 约束条件,
    字段名称2 字段类型(数字) 约束条件,
    字段名称3 字段类型(数字) 约束条件
);
​
"约束条件相当于是在字段类型之上添加的额外约束(条件)"
​
1.unsigned  # 无符号
    id int unsigned  #去除正负号
2.zero fill     用0填充
    id int（5） zerofill  # 当输入的id不足5位数是00001
3.not null  # 非空
    如果添加not null，不输入任何东西会报错
    mysql> create table t4(id int not null,name varchar(24) not null);
    
    mysql> insert into t4(id) values(1);
        ERROR 1364 (HY000): Field 'id' doesn't have a default value    # 报错
    mysql>  insert into t4(name) values("jason");
        ERROR 1364 (HY000): Field 'name' doesn't have a default value   # 报错
    mysql> insert into t4 values(1,"jason");    
        Query OK, 1 row affected (1.83 sec)     # 正确
​
4.default   # 默认值
    默认值(用户给了就用用户的 用户不给就是要默认的)
    
    mysql> create table t5(id int,name varchar(12) default "匿名用户");
        Query OK, 0 rows affected (0.35 sec)
​
    mysql> insert into t5(id) values(1);
        Query OK, 1 row affected (1.80 sec)
​
    mysql> select * from t5;
        +------+--------------+
        | id   | name         |
        +------+--------------+
        |    1 | 匿名用户     |
        +------+--------------+
        1 row in set (0.00 sec)
        
    mysql> insert into t5 values(1,"jason");
        Query OK, 1 row affected (1.77 sec)
​
    mysql> select * from t5;
        +------+--------------+
        | id   | name         |
        +------+--------------+
        |    1 | 匿名用户     |
        |    1 | jason        |
        +------+--------------+
        2 rows in set (0.00 sec)
    
5.unique    # 唯一
    唯一(保证字段(一个、多个)在整个表中没有重复的数据)
    
    # 单列唯一
    mysql> create table t6(id int unique,name varchar(12));
        Query OK, 0 rows affected (1.98 sec)
​
    mysql> insert into t6 values(1,"jason");
        Query OK, 1 row affected (0.08 sec)     # 正常录入
​
    mysql> insert into t6 values(1,"jason1");
        ERROR 1062 (23000): Duplicate entry '1' for key 'id'    # id一样报错
            
    # 联合唯一（组合）
​
  只要不是ip和port都一样，就不会报错
​
    mysql> create table server(id int,host varchar(32),ip varchar(32),port varchar(32),unique(ip,port));
        Query OK, 0 rows affected (0.37 sec)
​
    mysql> insert into server values(1,"localhost","192.168.15.1","3306");
        Query OK, 1 row affected (0.09 sec)
​
    mysql> insert into server values(1,"localhost","192.168.15.1","3307");
        Query OK, 1 row affected (0.06 sec)
​
        mysql> select * from server;
        +------+-----------+--------------+------+
        | id   | host      | ip           | port |
        +------+-----------+--------------+------+
        |    1 | localhost | 192.168.15.1 | 3306 |
        |    1 | localhost | 192.168.15.1 | 3307 |
        +------+-----------+--------------+------+
        2 rows in set (0.00 sec)
        
6.primary key# 主键（一般把id当做主键）
​
1.单从约束条件上而言主键相当于not null + unique(非空且唯一)
     2.主键的功能目前可以简单的理解为能够加快数据的查询速度 # (相当于书的目录)
     3.InnoDB存储引擎规定每张表都必须有且只有一个主键
        3.1.表中没有任何的主键和其他约束条件
            InnoDB默认会采用一个隐藏字段作为表的主键
    3.2.表中没有主键但是有非空且唯一的字段
            InnoDB会自动将该字段升级为主键
                
    "结论:每张表都必须要有一个id(sid nid uid)字段并且该字段就是主键"
    
    # 单列主键
    mysql> create table t7(id int primary key,name varchar(30));
    Query OK, 0 rows affected (0.40 sec)
​
    mysql> insert into t7 values(1,"jason");
    Query OK, 1 row affected (1.77 sec)
​
    mysql> select * from t7;
        +----+-------+
        | id | name  |
        +----+-------+
        |  1 | jason |
        +----+-------+
        1 row in set (0.00 sec)
​
    mysql> desc t7;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | NO   | PRI | NULL    |       |
        | name  | varchar(30) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        2 rows in set (0.02 sec)
    # 多列主键(联合主键)
    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删除操作的影响（比如id为主键，你把id为3的删除，再添加一行的话会从4开始）
​
truncate既可以清空表数据也会重置主键值

外键

# 前戏
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.一对一
​
"""查找表关系:换位思考"""
书籍表与出版社表
    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)
);

多对多

以书籍表与作者表为例
    1.先站在书籍表的基础之上
        问:一本书能否对应多个作者
        答:可以
    2.在站在作者表的基础之上
        问:一个作者能否对应多本书
         答:可以
    结论:两个都可以那么表关系就是"多对多"
    
        外键字段建在第三张关系表中
    
mysql> create table auth(id int primary key auto_increment ,name varchar(24));
    Query OK, 0 rows affected (0.58 sec)
​
mysql> desc auth;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(24) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    2 rows in set (0.01 sec)
​
mysql> create table publish(id int primary key auto_increment ,title varchar(24));
    Query OK, 0 rows affected (1.64 sec)
​
mysql> desc publish;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | title | varchar(24) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    2 rows in set (0.02 sec)
​
mysql> create table auth_pub(id int primary key auto_increment,auth_id int,foreign key(auth_id) references publish(id) on update cascade on delete cascade,pub_id int,foreign key(pub_id) references auth(id) on update cascade on delete cascade);
    Query OK, 0 rows affected (0.28 sec)
​
mysql> desc auth_pub;
    +---------+---------+------+-----+---------+----------------+
    | Field   | Type    | Null | Key | Default | Extra          |
    +---------+---------+------+-----+---------+----------------+
    | id      | int(11) | NO   | PRI | NULL    | auto_increment |
    | auth_id | int(11) | YES  | MUL | NULL    |                |
    | pub_id  | int(11) | YES  | MUL | NULL    |                |
    +---------+---------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
​
mysql> insert into auth(name) values("张三"),("李四"),("王五"),("刘柳");
    Query OK, 4 rows affected (1.42 sec)
    Records: 4  Duplicates: 0  Warnings: 0
​
mysql> select * from auth;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | 张三   |
    |  2 | 李四   |
    |  3 | 王五   |
    |  4 | 刘柳   |
    +----+--------+
    4 rows in set (0.00 sec)
​
mysql> insert into publish(title) values("Python开发"),("Linux运维"),("数据分析"),("java"),("c++");
    Query OK, 5 rows affected (0.39 sec)
    Records: 5  Duplicates: 0  Warnings: 0
​
mysql> select * from publish;
    +----+--------------+
    | id | title        |
    +----+--------------+
    |  1 | Python开发   |
    |  2 | Linux运维    |
    |  3 | 数据分析     |
    |  4 | java         |
    |  5 | c++          |
    +----+--------------+
    5 rows in set (0.00 sec)
    
mysql> insert into auth_pub(auth_id,pub_id) values(1,1),(1,3),(2,2),(2,1),(3,4),(3,2),(4,4),(5,1),(5,2),(5,3);
    Query OK, 10 rows affected (1.98 sec)
    Records: 10  Duplicates: 0  Warnings: 0
​
mysql> select * from auth_pub;
    +----+---------+--------+
    | id | auth_id | pub_id |
    +----+---------+--------+
    |  8 |       1 |      1 |
    |  9 |       1 |      3 |
    | 10 |       2 |      2 |
    | 11 |       2 |      1 |
    | 12 |       3 |      4 |
    | 13 |       3 |      2 |
    | 14 |       4 |      4 |
    | 15 |       5 |      1 |
    | 16 |       5 |      2 |
    | 17 |       5 |      3 |
    +----+---------+--------+
    10 rows in set (0.00 sec)