MySQL数据类型
常见信息种类
信息种类
数值型∶体重、身高、成绩、工资
字符型:姓名、工作单位、通信住址·
枚举型:兴趣爱好、性别、专业·
日期时间型:出生日期、注册时间
字符类型
定长char
定长:char(字符个数)
-最大字符个数255
-不够指定字符个数时在右边用空格补全
-字符个数超出时，无法写入数据。
mysql> create table db1.t1(
name char(5),
homedir char(50)
);
mysql> insert into db1.t1 values( "bob” " USA” );
变长varchar
·变长: varchar(字符个数)
一按数据实际大小分配存储空间-字符个数超出时，无法写入数据。
·大文本类型: text/blob
-字符数大于65535存储时使用
mysql> create table db1.t2(name char(5),
email varchar(30));
mysql>insert into db1.t2 values( "lucy" " lucy@feng.cn” );
- 按照 图-1 所示建表。
![]()
- 1）新建db1库，并切换到db1库
mysql> create database db1;
Query OK, 1 row affected (0.02 sec)mysql> use db1
Database changed - 2）新建t3表
mysql> create table db1.t3(
-> name char(5),
-> mail varchar(10),
-> homeaddr varchar(50)
-> );
Query OK, 0 rows affected (0.04 sec) - 3) 查看a3表结构
mysql> desc db1.t3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | char(5) | YES | | NULL | |
| mail | varchar(10) | YES | | NULL | |
| homeaddr | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
数值类型
整数型
·只能存储整数
浮点型
一格式1∶字段名类型;
一格式2∶字段名类型(总宽度,小数位数)
- 按照 图-2 所示建表。
![]()
- 1）切换到db1库
mysql> use db1;
Database changed - 2）新建t2表
mysql> create table db1.t2(
-> stu_num int(11),
-> name char(5),
-> age tinyint(4),
-> pay float,
-> money float(5,2) //float(5,2)规定显示的值不会超过5位数，小数点后面带有2位数字。
-> );
Query OK, 0 rows affected (0.01 sec) - 3) 查看t2表结构
mysql> desc db1.t2; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | stu_num | int(11) | YES | | NULL | | | name | char(5) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | pay | float | YES | | NULL | | | money | float(5,2) | YES | | NULL | | +---------+------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>
日期时间类型
类型
.日期时间datetime
-范围:1000-01-01 00:00:00~9999-12-31 23:59:59-格式:yyyymmddhhmmss
·日期时间timestamp
-范围:1970-01-01 00:00:00~2038-01-19 00:00:00-格式:yyyymmddhhmmss
·日期date
-范围:0001-01-01 ~9999-12-31-格式: yyyymmdd
.年year
-范围:1901~2155-格式:yyyy
时间time
-格式:HH:MM:SS
·关于日期时间字段
-当未给timestamp字段赋值时，自动以当前系统时间赋值，而datetime值为NULL(空)
year类型
–要求使用4位数赋值-当使用2位数赋值时:- 01~69视为 2001~2069- 70~99视为1970~1999
- 按照图-3所示建表
![]()
- 1）建表
mysql> create table db1.t4(
-> name char(10),
-> your_star year,
-> up_time time,
-> birthday date,
-> party datetime
-> );
Query OK, 0 rows affected (0.01 sec) - 2) 查看表结构
mysql> desc db1.t4;
+------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | your_start | year(4) | YES | | NULL | | | up_time | time | YES | | NULL | | | birthday | date | YES | | NULL | | | party | datetime | YES | | NULL | | +------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
- 3）插入记录
mysql> insert into db1.t4 values("bob",1990,083000,20191120,20190828200000);
Query OK, 1 row affected (0.01 sec)mysql> insert into db1.t4 values("tom",1991,090000,20191120,now());
Query OK, 1 row affected (0.01 sec) - 4）查看表记录
mysql> select * from db1.t4;
+------+-----------+----------+------------+---------------------+
| name | your_star | up_time | birthday | party |
+------+-----------+----------+------------+---------------------+
| bob | 1990 | 08:30:00 | 2019-11-20 | 2019-08-28 20:00:00 |
| tom | 1991 | 09:00:00 | 2019-11-20 | 2022-02-15 14:34:11 |
+------+-----------+----------+------------+---------------------+
2 rows in set (0.00 sec)
时间函数
. MySQL服务内置命令
-可以使用时间函数给字段赋值
-
1）使用now()查看当前的日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-02-15 13:11:34 |
+---------------------+
1 row in set (0.00 sec) - 2）使用curdate()获得当前的日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2022-02-15 |
+------------+
1 row in set (0.00 sec) - 3）使用curtime()获得当前的时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 13:13:52 |
+-----------+
1 row in set (0.00 sec) - 4）分别获取当前日期时间中的年份、月份、日
mysql> select year(now()),month(now()),day(now());
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
| 2022 | 2 | 15 |
+-------------+--------------+------------+
1 row in set (0.00 sec) - 5）获取系统日期
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2022-02-15 |
+-------------+
1 row in set (0.00 sec)
枚举类型
enum
· enum单选∶
——格式:字段名 enum(值1，值2,值N)-仅能选择一个值
——字段值必须在列表里选择
mysql> create table db1.t5(
namechar(5)，
sexenum( "boy"," girl” , “no”)
);
mysql> insert into db1.t5 values( "bob", "man”);
set
- set 多选:
——格式:字段名set(值1，值2，值N)-选择一个或多个值
——字段值必须在列表里选择
mysql> create table db1.t6(
name char(5),
likesset( "eat", "game” , “music" , "money" )
);
mysql> insert into db1.t6 values( "jerry" , "eat,film” );
sexenum( "boy"," girl” , “no”)
);
mysql> insert into db1.t5 values( "bob", "man”);
- 按照图-4所示建表
![]()
- 1）建表
mysql> mysql> create table db1.t5 ( -> name char(5), -> likes set("eat","game","film","music"), -> sex enum("boy","girl","no") -> ); Query OK, 0 rows affected (0.04 sec) Mysql> - 2）查看表结构
mysql> desc db1.t5; +-------+----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------------------+------+-----+---------+-------+ | name | char(5) | YES | | NULL | | | likes | set('eat','game','film','music') | YES | | NULL | | | sex | enum('boy','girl','no') | YES | | NULL | | +-------+----------------------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) - 3）插入表记录
mysql> mysql> insert into db1.t5 values ("bob","eat,film,game","boy"); Query OK, 1 row affected (0.03 sec) mysql> - 4）查看表记录
mysql> select * from db1.t5; +------+---------------+------+ | name | likes | sex | +------+---------------+------+ | bob | eat,game,film | boy | +------+---------------+------+ 1 rows in set (0.00 sec)
浙公网安备 33010602011771号