数据类型
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)