1.字符类型
- 定长:char(字符数)
- 最大长度255字符
- 不够指定字符的时候在右边有空格补齐
- 超出指定字符数时无法写入数据
- 变长:varchar(字符数)
- 按数据实际大小分配存储空间
- 超出指定字符数时无法写入数据
- 大文本类型:
- 字符数大于65535的时候使用
2.日期时间类型
2.1 日期
- 日期时间DATETIME
- 占用8个字节
- 范围:1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
- 日期时间TIMESTAMP
- 占用4个字节
- 范围:1970-01-01 00:00:00.000000 ~ 2038-01-19 03:14:07.999999
- 日期DATE
- 占用4字节
日期时间类型 | 解释 |
---|---|
DATETIME | 占用8个字节;范围:1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 |
TIMESTAMP | 占用4个字节;范围:1970-01-01 00:00:00.000000 ~ 2038-01-19 03:14:07.999999 |
DATE | 占用4个字节;范围:0001-01-01 ~ 9999-12-31 |
YEAR | 占用1个字节;范围:0001-01-01 ~ 9999-12-31 |
TIME | 占用3个字节;格式:HH:MM:SS |
当未给TIMESTAMP字段赋值时,自动以当前系统时间赋值,而DATETIME字段默认赋值为 NULL
year年份的处理
- 默认用4位数字表示
- 当只用2位数字赋值时,0169视为20012069,而7099视为19701999
示例
mysql> create table student_info(
-> name varchar(8),
-> starty year,
-> birth date,
-> ttime1 time,
-> ttime2 time
-> );
mysql> desc student_info;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| name | varchar(8) | YES | | NULL | |
| starty | year(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| ttime1 | time | YES | | NULL | |
| ttime2 | time | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
mysql> insert into student_info values('tom',2021,20210924,0830,0830);
mysql> select * from student_info;
+------+--------+------------+----------+----------+
| name | starty | birth | ttime1 | ttime2 |
+------+--------+------------+----------+----------+
| tom | 2021 | 2021-09-24 | 00:08:30 | 00:08:30 |
+------+--------+------------+----------+----------+
2.2 时间函数
类型 | 用途 |
---|---|
now() | 获取日期和时间 |
year() | 获取年 |
sleep(N) | 休眠N秒 |
curdate() | 获取当前的系统日期 |
curtime() | 获取当前的系统时刻 |
month() | 获取指定时间中的月份 |
date() | 获取指定时间中的日期 |
time() | 获取指定时间中的时刻 |
-
无需库表,可直接调用
mysql> select now(),sysdate(),curdate(); +---------------------+---------------------+------------+ | now() | sysdate() | curdate() | +---------------------+---------------------+------------+ | 2021-09-01 23:31:25 | 2021-09-01 23:31:25 | 2021-09-01 | +---------------------+---------------------+------------+
mysql> select date(now()),time(now()); +-------------+-------------+ | date(now()) | time(now()) | +-------------+-------------+ | 2021-09-01 | 23:33:15 | +-------------+-------------+
3.枚举类型
-
从给定值集合中选择单个值,ENUM
- 格式:enum(值1,值2,值N)
-
从给定值集合中选择一个或多个值,SET
- 格式:set(值1,值2,值N)
mysql> create table cc_tmp2( -> name varchar(8), -> sex enum('boy','girl'), -> interest set('basketball','football','music','volleyball') -> ); mysql> desc cc_tmp2; +----------+---------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------------------------------------+------+-----+---------+-------+ | name | varchar(8) | YES | | NULL | | | sex | enum('boy','girl') | YES | | NULL | | | interest | set('basketball','football','music','volleyball') | YES | | NULL | | +----------+---------------------------------------------------+------+-----+---------+-------+ mysql> insert into cc_tmp2 values('hejing','boy','music,volleyball'); mysql> select * from cc_tmp2; +--------+------+------------------+ | name | sex | interest | +--------+------+------------------+ | hejing | boy | music,volleyball | +--------+------+------------------+