数据类型与约束条件

目录

• 数据类型与约束条件
  • 一、存储引擎
  • 二、创建表完整的语法
  • 三、基本字段类型
  • 四、约束条件
  • 五、设置严格模式

数据类型与约束条件

一、存储引擎

存储引擎：是用于根据不同的机制处理不同的数据

[https://images2015.cnblogs.com/blog/1036857/201707/1036857-20170720175637911-1251772910.png]

• 查看mysql中所有引擎

  • show engines;

• myisam：5.5以前老版本使用的存储引擎

  • mysql> create table t1(id int)engine=myisam;

• blackhole：类似于队列中的数据，存进去消失

  • mysql> create table t2(id int)engine=blackhole;

• innodb：默认使用存储引擎

  • mysql> create table t3(id int)engine=innodb;

• memory：通电数据存在，断电丢失

  • mysql> create table t4(id int)engine=memory;

二、创建表完整的语法

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

• 约束条件：可有可无

1. 宽度指的是对存储数据的限制

   1. 没有安全模式的数据库版本，能够存放数据但只能存进去规定的宽度
   2. 最新数据库的版本直接报错提示无法储存：Data too long for column 'name'

2. 约束条件：null与not null

   1. create table t1(id int not full, name char); 插入记录时id不能为空

• 注意
  • 创建表的字段名不能重复 (id int, id int)
  • 最后一个字段不能在末尾加逗号(id int, id int,)
  • 字段名必须要有字段类型与宽度
• 总结：类型与约束条件区别
  • 类型：限制字段必须以什么样的数据类型存储
  • 约束条件：约束条件是在类型之外添加一种额外的限制

三、基本字段类型

• 整形

  • 应用场景：id号、年龄...
  • tinyint：默认范围(-128,127)
    • mysql> create table t6(id tinyint, name varchar(16));
  • int：默认范围(-2147483648， 2147483647)
    • 只要字段超过这个范围，则会自动存入这个范围的 最大或最小值
    • int(8)不是限制长度，而是控制数据的位数，8位以内以空格补全，超出则正常显示
    • 默认宽度11
  • bigint

• 浮点型

  • 应用场景：工资、身高、体重
  • float
    • create table t7(x float(255, 30));
  • double
    • create table t8(x double(255, 30));
  • decimal
    • create table t9(x decimal(65, 30));
  • 范围255是最大长度（包括小数），30 代表的是小数的位数
  • 三种浮点型区别在于：精确度不一样

• 字符串类型

  • char(16)：定长字符
    • char应用场景：手机号、身份证号、银行卡号
    • 插入16个字符
      • create table t10(id int, name char(16));
      • 优点：存取速度快
      • 缺点：浪费空间
  • varchar(16)：不定长字符
    • 存入几个字符，就是几个字符的大小，每个字符前都要+1bytes
    • 插入16个字符
    • create table t11(id int, name varchar(16));
    • 优点：节省空间

• 日期类型

  • date：2019-12-11
  • datetime：2019-12-11 11:11:11
  • time：11:11:11
  • year：2019
  • timestamp：时间戳

  mysql> create table student(id int, name varchar(16), birth date, register datetime, work_
  tme year, t_time time, update_time timestamp);
  Query OK, 0 rows affected (0.52 sec)
  
  mysql> insert into student values(1, '张全蛋', '1990-11-11', '2019-12-11 11:11:11', '2019'
  , '11:11:11', null);
  Query OK, 1 row affected (0.03 sec)
  

  注意：插入时间数据时，转成str类型

• 枚举与集合类型

  • 分类

    • enmu枚举：可以多选一

    mysql> create table user1(id int, name varchar(16), gender enum('male','female','other'));
    Query OK, 0 rows affected (0.58 sec)
    # 严格模式下，选择枚举以外的值会报错
    mysql> insert into user1 values(1, 'sean', '人妖');
    Query OK, 1 row affected, 1 warning (0.12 sec)
    
    mysql> insert into user1 values(2, 'tank', 'female');
    Query OK, 1 row affected (0.35 sec)
    
    mysql> select * from user1;
    +------+------+--------+
    | id   | name | gender |
    +------+------+--------+
    |    1 | sean |        |
    |    2 | tank | female |
    +------+------+--------+
    2 rows in set (0.00 sec)
    

    • set 集合：可 多选一 或 多选多

    mysql> create table user2 (id int, name varchar(16), gender enum('male','female'), hobbies
     set('read','sing','HSNM'));
    Query OK, 0 rows affected (1.37 sec)
    
    # 多选多 
    mysql> insert into user2 values(1, 'sean', 'male', 'read,sing,HSNM');
    Query OK, 1 row affected (0.59 sec)
    
    mysql> select * from user2;
    +------+------+--------+----------------+
    | id   | name | gender | hobbies        |
    +------+------+--------+----------------+
    |    1 | sean | male   | read,sing,HSNM |
    +------+------+--------+----------------+
    1 row in set (0.00 sec)
    
    

    注意：在写多选时，必须连着写，不可以有空格

四、约束条件

约束条件就是：对于数据库表插入数据时加以约束限制

• not null：不可设置为空

  • create table user3(id int not null, name varchar(16));

  mysql> create table user3(id int not null, name varchar(16));
  Query OK, 0 rows affected (0.75 sec)
  # 不可设置为null，否则就报错
  mysql> insert into user3 values(null, 'yang');
  ERROR 1048 (23000): Column 'id' cannot be null
  
  mysql> insert into user3 values(1, 'yang');
  Query OK, 1 row affected (0.35 sec)
  

• not null + default ：设置不为空的默认值

  mysql> create table user5(id int, name varchar(16) not null, gender enum('male','female','
  other') default 'male');
  Query OK, 0 rows affected (0.61 sec)
  
  mysql> insert into user5(id, name) values(1, 'yang');
  Query OK, 1 row affected (0.38 sec)
  
  mysql> select * from user5;
  +------+------+--------+
  | id   | name | gender |
  +------+------+--------+
  |    1 | yang | male   |
  +------+------+--------+
  1 row in set (0.00 sec)
  

• unique：将某个资源设置为唯一的值

  • not null + unique

  mysql> create table user4(id int not null unique, name varchar(16));
  Query OK, 0 rows affected (0.31 sec)
  # id 一样的话报错
  mysql> insert into user4(id, name) values(1, 'yang'),(1, 'tank');
  ERROR 1062 (23000): Duplicate entry '1' for key 'id'
  # 正常
  mysql> insert into user4(id, name) values(1, 'yang'),(2, 'tank');
  Query OK, 2 rows affected (0.07 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  mysql> select * from user4;
  +----+------+
  | id | name |
  +----+------+
  |  1 | yang |
  |  2 | tank |
  +----+------+
  2 rows in set (0.00 sec)
  

  • 联合唯一
    • create table user6(id int, name varchar(16), unique(id, name));

• primary key + auto_increment：主键+自增

  # 结束角度：primary key == not null + unique
  mysql> create table user6(id int primary key);
  Query OK, 0 rows affected (0.33 sec)
  
  mysql> insert into user6 values(1);
  Query OK, 1 row affected (0.12 sec)
  # 唯一
  mysql> insert into user6 values(1);
  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  # 不能为空
  mysql> insert into user6 values(null);
  ERROR 1048 (23000): Column 'id' cannot be null
  # 注意：primary key 除了约束之外，它还是innodb引擎组织数据的一句，提升查询效率
  """
  		1.一张表必须只有一个主键，如果没有设置主键，会从上到下搜索，直到遇到"第一个"非空且唯一的字段自动设置为主键
  """
  mysql> create table user7(id int, name varchar(16), age int not null unique)engine=innodb;
  
  Query OK, 0 rows affected (0.58 sec)
  
  mysql> desc user7;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | int(11)     | YES  |     | NULL    |       |
  | name  | varchar(16) | YES  |     | NULL    |       |
  | age   | int(11)     | NO   | PRI | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  3 rows in set (0.01 sec)
  """
  		2.如果包里没有指定任何可以设置主键的字段，那innodb会采用自己默认的一个隐藏关键字作为主键，隐藏意味着查询你的时候通过这个加速查询
  		索引：类似于书的目录，没有主键就相当于一页一页翻着查询
  		
  		3.一张表中通常都应该有一个id字段，并且通常将id字段作为主键
  """
  # 联合主键：多个字段联合起来为一个主键，本质上还是一个主键
  mysql> create table user8(id int, name varchar(16), primary key(id, name));
  # 主键id作为数据的编号，应该设置为自动递增
  

• auto_increment：设置自增

  mysql> create table user9(id int primary key auto_increment, name varchar(16));
  Query OK, 0 rows affected (0.54 sec)
  # 自增默认从0开始
  mysql> insert into user9(name) values('yang');
  Query OK, 1 row affected (0.38 sec)
  
  mysql> insert into user9(name) values('tank');
  Query OK, 1 row affected (0.12 sec)
  
  mysql> insert into user9(name) values('sean');
  Query OK, 1 row affected (0.05 sec)
  
  mysql> select * from user9;
  +----+------+
  | id | name |
  +----+------+
  |  1 | yang |
  |  2 | tank |
  |  3 | sean |
  +----+------+
  3 rows in set (0.00 sec)
  

  mysql> insert into user9 values(10, 'jason');
  Query OK, 1 row affected (0.12 sec)
  
  mysql> insert into user9(name) values('jason1');
  Query OK, 1 row affected (0.04 sec)
  
  mysql> insert into user9(name) values('jason2');
  Query OK, 1 row affected (0.06 sec)
  # 若想自增从指定值开始，可插入第一条数据时先指定id的值;
  mysql> select * from user9;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | yang   |
  |  2 | tank   |
  |  3 | sean   |
  | 10 | jason  |
  | 11 | jason1 |
  | 12 | jason2 |
  +----+--------+
  6 rows in set (0.00 sec)
  

  注意: auto_increment通常加在主键上，并且只能设置给primary key字段。

• unsigned

  • 无符号

    mysql> create table user10(id int unsigned);
    Query OK, 0 rows affected (0.54 sec)
    # 报错
    mysql> insert into user10 values(-100);
    # 正常
    mysql> insert into user10 values(100);
    

  • 有符号，就使用平时的int 或 bigint

• zerofill

  • 使用0填充空格

    mysql> create table user11(id int zerofill);
    Query OK, 0 rows affected (0.29 sec)
    
    mysql> insert into user11 values(111);
    Query OK, 1 row affected (0.11 sec)
    
    mysql> select * from user11;
    +------------+
    | id         |
    +------------+
    | 0000000111 |
    +------------+
    1 row in set (0.00 sec)
    
    

• 删除记录

  • delete删除表的记录或者指定记录，但是id 不会重置
  • truncate可以删除表记录，而且id会重置为0

总结：

- primary key (pk)
    标识该字段为该表的主键，主键可以是唯一的表示记录
    
- foreign key (fk)
    标识该字段为该表的外键

- not null
    标识该字段不能为空

- unique key (uk)
    标识该字段的值是唯一的

- auto_increment
    标识该字段的值自动增长 （整数类型，并且为主键）

- default
    为该字段设置默认值

- unsigned
    无正负符号

- zerofill
    使用0填充空格

五、设置严格模式

# 查看数据库配置中变量名包含mode的配置参数:
show variables like "%mode%";

# 修改安全模式:
set session;  # 局部有效，只在你当前操作的窗口有效
set global session;  # 全局有效，永久有效

# 修改完之后退出当前客户端重新登录即可
set global sql_mode = 'STRICT_TRANS_TABLES';