数据类型（Data Type）

数据类型
MySQL里有哪些数据类型？
MySQL支持几种类别的SQL数据类型：数值类型，日期和时间类型，字符串（字符和字节）类型，空间类型和JSON数据类型。

创建一个表格

root@nongda 11:30 mysql>create table student_inform(
    -> id int(6) primary key,
    -> name varchar(20),
    -> sex char(1),
    -> birth_date date,
    -> grade decimal(5,2),
    -> address varchar(20),
    -> class varchar(20),
    -> major varchar(20),
    -> college varchar(20),
    -> marry char(1)
    -> );
	Query OK, 0 rows affected (0.01 sec)

数值类型

int，tinyint，smallint，mediumint，bigint，bit，float，double，decimal

日期和时间类型

datetime,date,timestamp,time,year

root@sanchuang 15:31 mysql>create table t1(name int,brithday date,check_sc timestamp);
Query OK, 0 rows affected (0.01 sec)
root@sanchuang 15:36 mysql>insert into t1(name,brithday,check_sc) values(5,'1984-10-01',now());
Query OK, 1 row affected (0.00 sec)        -------》now()   获得当前时间的函数
root@sanchuang 15:36 mysql>select * from t1;
+------+------------+---------------------+
| name | brithday   | check_sc            |
+------+------------+---------------------+
|    5 | 1984-10-01 | 2020-12-03 15:36:28 |
+------+------------+---------------------+
1 row in set (0.00 sec)

字符串类型

char，varchar，blob，text，enum，set，binary，varbinary

char和varchar的差别？

char  固定长度的字符串类型    character 字符   --》在存储的时候，不够固定长度，就在前面填充空格，达到固定长度
varchar  可变长的字符串类型  variable character

root@sanchuang 15:50 mysql>create table t1(name char(30));
Query OK, 0 rows affected (0.01 sec)
root@sanchuang 15:50 mysql>insert into t1(name) values('xdd');
Query OK, 1 row affected (0.00 sec)
root@sanchuang 15:51 mysql>insert into t1(name) values('左爷');
Query OK, 1 row affected (0.00 sec)
root@sanchuang 15:53 mysql>select name,length(name),char_length(name) f
rom t1;
+--------+--------------+-------------------+              
| name   | length(name) | char_length(name) |         
+--------+--------------+-------------------+
| xdd    |            3 |                 3 |
| 左爷   |            6 |                 2 |
+--------+--------------+-------------------+
2 rows in set (0.00 sec)

length(name) 统计字符串的存储的字节数
char_length(name) 统计的是字符的个数

枚举类型

ENUM

root@nongda 17:47 mysql>create table t1(
    -> id tinyint,
    -> sex enum('man','woman'),
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

root@nongda 17:49 mysql>insert into t1(id,name,sex) values(1,'lhc','man'),(2,'tyl','man'),(3,'pzy','woman');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@nongda 17:50 mysql>desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(4)          | YES  |     | NULL    |       |
| sex   | enum('man','woman') | YES  |     | NULL    |       |
| name  | varchar(10)         | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

root@nongda 17:50 mysql>select *,length(name) from t1;
+------+-------+------+--------------+
| id   | sex   | name | length(name) |
+------+-------+------+--------------+
|    1 | man   | lhc  |            3 |
|    2 | man   | tyl  |            3 |
|    3 | woman | pzy  |            3 |
+------+-------+------+--------------+
3 rows in set (0.00 sec)

set类型

root@nongda 17:51 mysql>create table t2(mamu 
    -> set('a','b','c','d')
    -> );
Query OK, 0 rows affected (0.01 sec)

root@nongda 18:00 mysql>insert into t2(mamu) values(
    -> 'a,b')
    -> ,('a,c'),
    -> ('a,d'),
    -> ('b,c'),
    -> ('b,d'),
    -> ('c,d')
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@nongda 18:02 mysql>select * from t2;
+------+
| mamu |
+------+
| a,b  |
| a,c  |
| a,d  |
| b,c  |
| b,d  |
| c,d  |
+------+
6 rows in set (0.00 sec)