MySQL创建数据表语法、字段类型

目录

• 一、创建表的完整语法
• 二、字段类型
  • 0. mode 严格模式
  • 1.整型
    • （1）int[(m)][unsigned][zerofill]
    • （2）smallint[(m)][unsigned] [zerofill]
    • （3）tinyint[(m)][unsigned] [zerofill]
    • （4）bigint[(m)][unsigned][zerofill]
  • 2.浮点型
    • （1）FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
    • （2）DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
    • （3）decimal[(M,D)] [UNSIGNED] [ZEROFILL]
    • 例子：三者的核心区别在精确度不同
  • 3.字符类型
    • （1）char(m)
    • （2）varchar(m)
    • （3）char_length()获取字段存储的字符长度
    • （4）char 与varchat 对比
  • 附.数字的含义
  • 5.枚举与集合
    • （1）枚举：enum()多选一
    • （2）集合：多选多
  • 6.日期类型

一、创建表的完整语法

表的完整语法

create table 表名(
    字段名(列名) 字段类型 约束条件,
    字段名(列名) 字段类型 约束条件,  
    字段名(列名) 字段类型 约束条件
)default charset=utf8;
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个，空格隔开即可
4.最后一行结尾不能加逗号

二、字段类型

0. mode 严格模式

5.7版本为严格模式，在严格模式下如果数据不符合规范，mysql会直接报错而不是擅自修改数据

# 报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1

（1）临时修改mode

# 在当前客户端有效
set session sql_mode='strict_trans_tables';
 
#  在当前服务端有效 
set global sql_mode='strict_trans_tables'; 

（2）永久修改mode

直接修改配置文件
  在[mysql]d下添加 
    sql_mode='strict_trans_tables'

1.整型

整数类型	字节
tinyint	1 bytes = 8 bit
smallint	2 bytes
int	4 bytes
bigint	8 bytes

（1）int[(m)][unsigned][zerofill]

int				表示有符号，取值范围：-2147483648 ～ 2147483647
int unsigned	表示无符号，取值范围：0 ～ 4294967295
int(5)zerofill	仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

（2）smallint[(m)][unsigned] [zerofill]

有符号，取值范围：-32768 ～ 32767.
  符号需要占1位
无符号，取值范围：0 ～ 65535

（3）tinyint[(m)][unsigned] [zerofill]

有符号，取值范围：-128 ～ 127.
  符号需要占1位
无符号，取值范围：0 ～ 255

（4）bigint[(m)][unsigned][zerofill]

有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0  ～  18446744073709551615

• 验证整型是否携带正负号

create table t5(id tinyint);
insert into t5 values(-129),(128);
 
# 在非严格模式下，输入超出范围的整数不会报错，而是会将

• 可以取消正负号 （约束条件 unsigned 可以取消正负号）

create table t5(id tinyint unsigned);

2.浮点型

（1）FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

（2）DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

（3）decimal[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

例子：三者的核心区别在精确度不同

float < double < decimal

正常业务使用float就可以了

高精度数据，可以使用decimal

float(50,20) 
  表示 总共存储20位数，小数点后面占10位  
double(50,20)  
  表示 总共存储20位数，小数点后面占10位  
decimal(50,20)  
  表示 总共存储20位数，小数点后面占10位  
 
# 创建三种不同浮点型的表
create table t7(id float(50,20));
create table t8(id double(50,20));
create table t9(id decimal(50,20));
 
# 插入数据
insert into t7 values(2.01234567890123456789);
insert into t8 values(2.01234567890123456789);
insert into t9 values(2.01234567890123456789);
 
mysql> select * from t7;
+------------------------+
| id                     |
+------------------------+
| 2.01234579086303700000 |
+------------------------+
1 row in set (0.00 sec)
 
mysql> select * from t8;
+------------------------+
| id                     |
+------------------------+
| 2.01234567890123460000 | 
+------------------------+
1 rows in set (0.00 sec)
 
mysql> select * from t9;
+------------------------+
| id                     |
+------------------------+
| 2.01234567890123456789 |
+------------------------+
1 row in set (0.00 sec)
 
 
# 则可以看出在表示小数的精度上 float  < double < decimal

3.字符类型

（1）char(m)

• 一般适用于：固定长度的内容

• 定长字符串，m代表字符串的长度，最多可容纳255个字符。

• 定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。

char(4) 最多存储四个字符，超出就报错，不够四个空格填充至四个

# 非严格模式下自动斩断
    如果在配置文件中加入如下配置，
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。
 
#  char默认底层存储是固定的长度,不够则用空格补齐
注意：默认底层存储是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。 如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
查看模式sql-mode，执行命令：show variables  like 'sql_mode';
 

（2）varchar(m)

• 变长字符串，m代表字符串的长度，最多可容纳65535个字节。
• 变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。

# 非严格模式下自动斩断
    如果在配置文件中加入如下配置，
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。

例子

mysql> desc t10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | char(4)     | YES  |     | NULL    |       |
| hobby | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
 
mysql> insert into t10 values(1,'duoo','drinkhothotwater');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from t10;
+------+------+------------------+
| id   | name | hobby            |
+------+------+------------------+
|    1 | duoo | drinkhothotwater |
+------+------+------------------+
1 row in set (0.00 sec)

（3）char_length()获取字段存储的字符长度

char_length()

mysql> select char_length(hobby) from t10;
+--------------------+
| char_length(hobby) |
+--------------------+
|                 16 |
+--------------------+
1 row in set (0.01 sec)

（4）char 与varchat 对比

char
  优势：整存整取 速度快
  劣势：浪费存储空间  
  
varchar
  优势：节省存储空间
  劣势：存取数据的速度较char慢
  
  ps:varchar在存储数据的时候需要存储数据的长度，取数据的时候需要解析报头去知道数据的长度
  
# char 与 varchar 的使用需要结合应用场景

附.数字的含义

数字在很多地方都是用来表示限制存储数据的长度，但是在整形中数字不是用来限制存储长度

# 如
create table t13(id int(5) zerofill);  
int 括号内填写的数字，是用来控制展示的长度的，与 zerofill结合表示，需要展示 5位，不满 5位用 0补充

insert into t13 values(123),(1234567);
# 查询t13中的数字
mysql> select * from t13;
+---------+
| id      |
+---------+
|   00123 |
| 1234567 |
+---------+
2 rows in set (0.00 sec)

5.枚举与集合

（1）枚举：enum()多选一

通过enum()来在多个选择中选1个输入，不符合则报错

create table tb2(
    id int,
    name varchar(16), 
    email varchat(32),     
    gender enum('male','female','others')
);
 

（2）集合：多选多

通过set()来在多个选择中选择可以选多个

create table tb2(
    id int,
    name varchar(16), 
    email varchat(32),     
    hobby set('drink','movie','chat')
); 
# 可以自由在set中选择输入，并且可以选多个

6.日期类型

日期类型	表示
datetime	年月日时分秒
date	年月日
time	时分秒
year	年