1.创建表
创建表结构
mysql>create table t1(->id int,-> name varchar(11),->age int,-> sex enum('male','female'),-> phone char(11),-> job char(10));
Query OK, 0 rows affected (0.02 sec)
查看表结构
mysql>desc t1; 是否允许为空 是否有默认值+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
| job | char(10) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
查看存储引擎/默认编码等其他信息
mysql> mysql>show create table t1 \G;*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
`job` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)
ERROR:
No query specified
插入数据
mysql> insert into t1 values(1,'wanglan',18,'male','13888888888',,'IT');
Query OK,1 row affected (0.01sec)
mysql> select * fromt1;+------+---------+------+------+-------------+------+
| id | name | age | sex | phone | job |
+------+---------+------+------+-------------+------+
| 1 | wanglan | 18 | male | 13888888888 | IT |
+------+---------+------+------+-------------+------+
1 row in set (0.00 sec)
2.1.数字类型
类型大小范围(有符号)范围(无符号)unsigned约束用途
TINYINT
1 字节
(-128,127)
(0,255)
小整数值
SMALLINT
2 字节
(-32 768,32 767)
(0,65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608,8 388 607)
(0,16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648,2 147 483 647)
(0,4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)
(0,18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节
float(255,30)
(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)
0,(1.175 494 351 E-38,3.402 823 466 E+38)
单精度
浮点数值
DOUBLE
8 字节
double(255,30)
(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL
对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2
double(65,30)
依赖于M和D的值
依赖于M和D的值
小数值
#创建表一个是默认宽度的int,一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02sec)#像t1中插入数据1,1
mysql> insert into t1 values (1,1);
Query OK,1 row affected (0.01sec)#可以看出结果上并没有异常
mysql> select * fromt1;+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00sec)#那么当我们插入了比宽度更大的值,会不会发生报错呢?
mysql> insert into t1 values (111111,111111);
Query OK,1 row affected (0.00sec)#答案是否定的,id2仍然显示了正确的数值,没有受到宽度限制的影响
mysql> select * fromt1;+------------+--------+
| id1 | id2 |
+------------+--------+
| 0000000001 | 00001 |
| 0000111111 | 111111 |
+------------+--------+
2 rows in set (0.00sec)#修改id1字段 给字段添加一个unsigned表示无符号
mysql>alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>desc t1;+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1 | int(10) unsigned | YES | | NULL | |
| id2 | int(5) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01sec)#当给id1添加的数据大于214748364时,可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK,1 row affected (0.00sec)#当给id2添加的数据大于214748364时,会报错
mysql> insert into t1 values (2147483647,2147483648);
ERROR1264 (22003): Out of range value for column 'id2' at row 1
int整数示例
#创建表的三个字段分别为float,double和decimal参数表示一共显示5位,小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02sec)#向表中插入1.23,结果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK,1 row affected (0.00sec)
mysql> select * fromt2;+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00sec)#向表中插入1.234,会发现4都被截断了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK,1 row affected, 1 warning (0.00sec)
mysql> select * fromt2;+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00sec)#向表中插入1.235发现数据虽然被截断,但是遵循了四舍五入的规则
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK,1 row affected, 1 warning (0.00sec)
mysql> select * fromt2;+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
3 rows in set (0.00sec)#建新表去掉参数约束
mysql>create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02sec)#分别插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK,1 row affected, 1 warning (0.00sec)#发现decimal默认值是(10,0)的整数
mysql> select * fromt3;+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.234 | 1.234 | 1 |
+-------+-------+------+
1 row in set (0.00sec)#当对小数位没有约束的时候,输入超长的小数,会发现float和double的区别
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK,1 row affected, 1 warning (0.00sec)
mysql> select * fromt3;+---------+--------------------+------+
| id1 | id2 | id3 |
+---------+--------------------+------+
| 1.234 | 1.234 | 1 |
| 1.23556 | 1.2355555555555555 | 1 |
+---------+--------------------+------+
2 rows in set (0.00 sec)
int 小数示例
设置的int后面的长度只是约束了显示宽度,所以输入的数字的长度可以是范围内任意的数字而不必担心宽度的设置,但是如果放入了超出范围的数字,那么只能写入当期范围内能描述的最大值
2.2.集合和枚举类型
ENUM中文名称叫枚举类型,它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值,而不能一次取多个值。
SET和ENUM非常相似,也是一个字符串对象,里面可以包含0-64个成员。根据成员的不同,存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入,而对重复的值将进行自动去重。
类型
大小
用途
ENUM
对1-255个成员的枚举需要1个字节存储;
对于255-65535个成员,需要2个字节存储;
最多允许65535个成员。
单选:选择性别
SET
1-8个成员的集合,占1个字节
9-16个成员的集合,占2个字节
17-24个成员的集合,占3个字节
25-32个成员的集合,占4个字节
33-64个成员的集合,占8个字节
多选:兴趣爱
mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01sec)#选择enum('female','male')中的一项作为gender的值,可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK,1 row affected (0.00sec)#不能同时插入'male,female'两个值,也不能插入不属于'male,female'的值
mysql> insert into t10 values ('nezha','male,female');
ERROR1265 (01000): Data truncated for column 'gender' at row 1mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
Query OK, 0 rows affected (0.01sec)#可以任意选择set('抽烟','喝酒','烫头','翻车')中的项,并自带去重功能
mysql> insert into t11 values ('yuan','烫头,喝酒,烫头');
Query OK,1 row affected (0.01sec)
mysql> select * fromt11;+------+---------------+
| name | hobby |
+------+---------------+
| yuan | 喝酒,烫头 |
+------+---------------+
1 row in set (0.00sec)#不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项,
mysql> insert into t11 values ('alex','烫头,翻车,看妹子');
ERROR1265 (01000): Data truncated for column 'hobby' at row 1
set/enum
2.3.时间类型
类型大小
(字节)范围格式用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
年月日
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时分秒
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
年月日时分秒
TIMESTAMP
4
1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS
混合日期和时间值,时间戳
mysql>create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02sec)
mysql>desc t4;+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01sec)
mysql>insert into t4 values (now(),now(),now());
Query OK,1 row affected, 1 warning (0.01sec)
mysql> select * fromt4;+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
1 row in set (0.00sec)
mysql>insert into t4 values (null,null,null);
Query OK,1 row affected (0.01sec)
mysql> select * fromt4;+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL | NULL | NULL |
+------------+----------+---------------------+
2 rows in set (0.00 sec)
date/time/datetime
mysql>create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02sec)
mysql>desc t5;+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00sec)#插入数据null,会自动插入当前时间的时间
mysql>insert into t5 values (null);
Query OK,1 row affected (0.00sec)
mysql> select * fromt5;+---------------------+
| id1 |
+---------------------+
| 2018-09-21 14:56:50 |
+---------------------+
1 row in set (0.00sec)#添加一列 默认值是'0000-00-00 00:00:00'
mysql>alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>show create table t5 \G;*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE `t5` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT'0000-00-00 00:00:00') ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)
ERROR:
No query specified#手动修改新的列默认值为当前时间
mysql>alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>show create table t5 \G;*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE `t5` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)
ERROR:
No query specified
mysql>insert into t5 values (null,null);
Query OK,1 row affected (0.01sec)
mysql> select * fromt5;+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
timestamp
mysql>create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02sec)
mysql>desc t6;+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.01sec)
mysql> insert into t6 values (19700101080001);
Query OK,1 row affected (0.00sec)
mysql> select * fromt6;+---------------------+
| t1 |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00sec)#timestamp时间的下限是19700101080001
mysql> insert into t6 values (19700101080000);
ERROR1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK,1 row affected (0.00sec)#timestamp时间的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1mysql>
timestamp示例
mysql>create table t7 (y year);
Query OK, 0 rows affected (0.02sec)
mysql> insert into t7 values (2018);
Query OK,1 row affected (0.00sec)
mysql> select * fromt7;+------+
| y |
+------+
| 2018 |
+------+
1 row in set (0.00 sec)
year
mysql>create table t8 (dt datetime);
Query OK, 0 rows affected (0.01sec)
mysql> insert into t8 values ('2018-9-26 12:20:10');
Query OK,1 row affected (0.01sec)
mysql> insert into t8 values ('2018/9/26 12+20+10');
Query OK,1 row affected (0.00sec)
mysql> insert into t8 values ('20180926122010');
Query OK,1 row affected (0.00sec)
mysql> insert into t8 values (20180926122010);
Query OK,1 row affected (0.00sec)
mysql> select * fromt8;+---------------------+
| dt |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
4 rows in set (0.00 sec)
datetime
2.4 字符串类型
类型大小用途
CHAR
0-255字节
定长字符串
VARCHAR
0-65535 字节
变长字符串
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LONGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
CHAR列的长度固定为创建表是声明的长度,范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535)。
mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01sec)
mysql> insert into t9 values ('ab','ab');
Query OK,1 row affected (0.00sec)#在检索的时候char数据类型会去掉空格
mysql> select * fromt9;+------+------+
| v | c |
+------+------+
| ab | ab |
+------+------+
1 row in set (0.00sec)#来看看对查询结果计算的长度
mysql> select length(v),length(c) fromt9;+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.00sec)#给结果拼上一个加号会更清楚
mysql> select concat(v,'+'),concat(c,'+') fromt9;+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab + | ab+ |
+---------------+---------------+
1 row in set (0.00sec)#当存储的长度超出定义的长度,会截断
mysql> insert into t9 values ('abcd','abcd');
Query OK,1 row affected, 1 warning (0.01sec)
mysql> select * fromt9;+------+------+
| v | c |
+------+------+
| ab | ab |
| abcd | abcd |
+------+------+
2 rows in set (0.00 sec)
char/varchar
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。