数据类型【MySQL】

建立表

mysql> create table if not exists  user1(-> id int ,-> name varchar (20) comment '用户名' ,-> password  char (32)  comment '用户名的密码' ,-> birthday  date comment '用户的生日'-> )character set utf8 collate utf8_general_ci engine MyIsam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists user2(-> name varchar (20) comment '用户名' ,-> birthday date comment'用户的生日'-> )character set utf8 collate utf8_general_ci engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

查看表

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user1             |
| user2             |
+-------------------+
2 rows in set (0.00 sec)

查看具体的表中的详细信息

mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

查看当时创建user1表中的语法细节

mysql> show create table user1  \G
*************************** 1. row ***************************Table: user1
Create Table: CREATE TABLE `user1` (`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL COMMENT '用户名',`password` char(32) DEFAULT NULL COMMENT '用户名的密码',`birthday` date DEFAULT NULL COMMENT '用户的生日'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除表

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user1             |
| user2             |
+-------------------+
2 rows in set (0.00 sec)mysql> drop table user2 ;
Query OK, 0 rows affected (0.01 sec)

修改表的名字

alter table user1 rename to user

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user1             |
| user2             |
+-------------------+
2 rows in set (0.00 sec)mysql> alter table user1 rename to user ;

向表中插入信息

mysql> insert  into user values (1 , '张三', '12345','2010-10-1');
Query OK, 1 row affected (0.00 sec)mysql> insert  into user values (2 , '李四', '54321','2015-10-1');
Query OK, 1 row affected (0.00 sec)mysql> select * from user ;
+------+--------+----------+------------+
| id   | name   | password | birthday   |
+------+--------+----------+------------+
|    1 | 张三   | 12345    | 2010-10-01 |
|    2 | 李四   | 54321    | 2015-10-01 |
+------+--------+----------+------------+
2 rows in set (0.00 sec)

新增一列，对原来数据无影响

mysql> select * from user ;
+------+--------+----------+------------+
| id   | name   | password | birthday   |
+------+--------+----------+------------+
|    1 | 张三   | 12345    | 2010-10-01 |
|    2 | 李四   | 54321    | 2015-10-01 |
+------+--------+----------+------------+
2 rows in set (0.00 sec)mysql> alter table user add image_path varchar(128) comment '这个是用户的头像路径' after birthday;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql>  select * from user ;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 12345    | 2010-10-01 | NULL       |
|    2 | 李四   | 54321    | 2015-10-01 | NULL       |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)

alter table user modify name varchar (60)，修改指定列中的属性, 将name改为60

这种修改是覆盖式修改

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> alter table user modify name varchar (60) ;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> desc user ;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| password   | char(32)     | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

删除一列

mysql> desc user ;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| password   | char(32)     | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)mysql> select * from user ;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 12345    | 2010-10-01 | NULL       |
|    2 | 李四   | 54321    | 2015-10-01 | NULL       |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)mysql> alter table user drop password;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> desc user ;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除表的数据
delete from stu where name=‘九品’;

mysql> select * from stu;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 妖捕   |
|  3 | 九品   |
+----+--------+
3 rows in set (0.00 sec)
mysql> delete from stu where name='九品';
Query OK, 1 row affected (0.00 sec)
mysql> select *from stu;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 妖捕   |
+----+--------+
2 rows in set (0.00 sec)

修改表的名字

to可以省略不写

mysql> alter table user rename to User;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| User              |
+-------------------+
1 row in set (0.00 sec)

修改表中列的名称

ysql> alter table user change name xingming varchar (60) DEFAULT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user              |
+-------------------+
1 row in set (0.00 sec)mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)

数据类型

数值类型:

BIT(M) 位类型：M指定位数，默认值为1，范围为1-64
BOOL 布尔类型：使用1表示真，使用0表示假
TINYINT [UNSIGNED] 占用1字节，默认为有符号
SMALLINT [UNSIGNED] 占用2字节，默认为有符号
MEDIUMINT [UNSIGNED] 占用3字节，默认为有符号
INT [UNSIGNED] 占用4字节，默认为有符号
BIGINT [UNSIGNED] 占用8字节，默认为有符号
FLOAT[(M,D)] [UNSIGNED] M指定显示长度，D指定小数位数，占用4字节
DOUBLE[(M,D)] [UNSIGNED] M指定显示长度，D指定小数位数，占用8字节
DECIMAL(M,D) [UNSIGNED] M指定显示长度，D指定小数位数，每4个字节表示9个数字，小数点占用1字节

文本、二进制类型:

CHAR(L)	固定长度字符串：L指定字符串长度，最大为255
VARCHAR(L)	可变长度字符串：L指定字符串长度上限，最多占用65535字节
BLOB	用于存储二进制数据
TEXT	用于存储大文本数据

时间日期:

DATE / DATETIME	日期类型：YYYY-MM-DD格式 / YYYY-MM-DD HH:MM:SS格式
TIMESTAMP	时间戳：以YYYY-MM-DD HH:MM:SS格式进行显示

字符串类型:

ENUM 枚举类型：ENUM类型的取值范围需要在定义字段时进行指定，设置字段值时只允许从成员中选取单个值，其所需的存储空间由定义ENUM类型时指定的成员个数决定
SET 集合类型：SET类型的取值范围需要在定义字段时进行指定，设置字段值时可以从成员中选取一个或多个值，其所需的存储空间由定义SET类型时指定的成员个数决定

注意:MySQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

float

mysql> create table if not exists t6(-> id bigint ,-> salary float (4,2) unsigned -> );
Query OK, 0 rows affected (0.01 sec)

decima

mysql> create table if not exists t7 (-> f1 float (10, 8) ,-> f2 decimal(4,2)-> );
Query OK, 0 rows affected (0.01 sec)

char

创建一个表，表当中包含一个char(2)的name列

mysql> create table  if not exists t8 (-> id int ,-> name char (2)-> );
Query OK, 0 rows affected (0.01 sec)

由于char(2)中最多可存储2个字符，只要插入的字符个数不超过2个都是能够成功插入的

如果插入的字符个数超过了2个，那么在插入数据时就会产生报错

mysql> insert into t8 (id ,name ) values (1,'a');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 (id ,name ) values (1,'b');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 (id ,name ) values (1,'ab');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t8 (id ,name ) values (1,'abc');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | b    |
|    1 | ab   |
+------+------+
3 rows in set (0.00 sec)

字符并不只是指一个英文字母，一个汉字也是一个字符，因此只要插入的汉字个数不超过2个也是可以插入的

varchar

创建一个表，表当中包含一个varchar(6)的name列

mysql> create table if not exists t9 (-> id int ,-> name varchar (6)-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t9 (id ,name ) values (123,'sasaa');
Query OK, 1 row affected (0.00 sec)mysql> insert into t9 (id ,name ) values (123,'sasaadd');
ERROR 1406 (22001): Data too long for column 'name' at row 1

由于varchar(6)中最多可存储6个字符，只要插入的字符个数不超过6都是能够成功插入的

如果插入的字符个数超过了6个，那么在插入数据时就会产生报错

varchar类型可指定的字符个数上限

varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。

而varchar类型可指定的字符个数上限，与表的编码格式有关：

对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 3 = 21844
对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 2 = 32766

char&&varchar

char类型可存储字符上限为255，varchar类型可存储字符上限与表的编码格式有关。
char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。
varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。

如何选取char和varchar类型？

char和varchar的优缺点如下：

char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）。
varchar类型的数据是变长的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）。
如果要存储的数据是定长的，那就使用char类型进行存储，比如身份证号码、手机号、md5等。如果要存储的数据是变长的，那就使用varchar类型进行存储，比如名字、地址等。

时间日期类型

• date：日期格式为YYYY-MM-DD，占用三字节。
• datetime：时间日期格式为YYYY-MM-DD HH:MM:SS，占用八字节。
• timestamp：时间戳，格式为YYYY-MM-DD HH:MM:SS，占用四字节。

创建一个表，表当中包含date、datetime和timestamp三种时间日期类型的列

mysql> create table  if not exists t11 (t1 date, t2 datetime , t3 timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t11 ;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | date      | YES  |     | NULL              |                             |
| t2    | datetime  | YES  |     | NULL              |                             |
| t3    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

timestamp类型的t3列是不允许为空的，它的默认值为CURRENT_TIMESTAMP

如果插入数据时不插入t3列，那么就会自动插入当前的时间戳

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1                |
| t11               |
| t2                |
| t3                |
| t4                |
| t5                |
| t6                |
| t7                |
| t8                |
| t9                |
+-------------------+
10 rows in set (0.00 sec)mysql> desc t11 ;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | date      | YES  |     | NULL              |                             |
| t2    | datetime  | YES  |     | NULL              |                             |
| t3    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)mysql> insert into t11 (t1,t2) values ('2010-10-01','1949-10-01 08:00:00' );
Query OK, 1 row affected (0.00 sec)mysql> select * from t11;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2010-10-01 | 1949-10-01 08:00:00 | 2024-10-23 22:39:32 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

将t1更新 ，timestamp也会更新

mysql> select * from t11;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2010-10-01 | 1949-10-01 08:00:00 | 2024-10-23 22:39:32 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)mysql> update t11 set t1='1999-01-01';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t11;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1999-01-01 | 1949-10-01 08:00:00 | 2024-10-23 22:42:38 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

enum和set

enum和set类型的区别：

• 在定义enum字段时需要提供若干个选项的值，在设置enum字段值时只允许选取其中的一个值。
• 在定义set字段时需要提供若干个选项的值，在设置set字段值时可以选取其中的一个或多个值。

例如：

人的性别只能从男和女中进行二选一，定义成enum类型，人的爱好可能存在多个，定义成set

mysql> create table if not exists votes  ( username varchar (30), gender enum('nan' ,'nu') , hobby set('daima','yumaoqiu','zuqiu') )  ;
Query OK, 0 rows affected (0.01 sec)mysql> desc votes;
+----------+---------------------------------+------+-----+---------+-------+
| Field    | Type                            | Null | Key | Default | Extra |
+----------+---------------------------------+------+-----+---------+-------+
| username | varchar(30)                     | YES  |     | NULL    |       |
| gender   | enum('nan','nu')                | YES  |     | NULL    |       |
| hobby    | set('daima','yumaoqiu','zuqiu') | YES  |     | NULL    |       |
+----------+---------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into votes values ('lisi','ddsd' , 'daima' ) ;
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> desc votes;
+----------+---------------------------------+------+-----+---------+-------+
| Field    | Type                            | Null | Key | Default | Extra |
+----------+---------------------------------+------+-----+---------+-------+
| username | varchar(30)                     | YES  |     | NULL    |       |
| gender   | enum('nan','nu')                | YES  |     | NULL    |       |
| hobby    | set('代码','羽毛球','足球') | YES  |     | NULL    |       |
+----------+---------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into votes values ('caocao','nan','足球,羽毛球') ;
Query OK, 1 row affected (0.01 sec)

enum和set可以通过数字的方式进行设置

enum : 数字代表下标，下标从1开始，依次增加

​ 例如：

​ nan：1

​ nu : 2

set： 数字代表位图 ，比特位从低位向高位，比特位的位置代表哪一个爱好

代码、 羽毛球 、足球对应三个比特位 ，代码就是001 ， 羽毛球就是010，足球就是100

代码、羽毛球和足球就是111

enum和set查找

select * from votes where hobby=‘代码’;查找爱好中只有代码

mysql> select  * from votes where gender='nan';
+----------+--------+----------------+
| username | gender | hobby          |
+----------+--------+----------------+
| zhangfei | nan    | 代码          |
| caocao   | nan    | 代码,yumaoqiu |
| 曹操     | nan    |                |
+----------+--------+----------------+
3 rows in set (0.00 sec)
mysql> select  * from votes where hobby='代码';
+----------+--------+-------+
| username | gender | hobby |
+----------+--------+-------+
| zhangfei | nan    | 代码 |
+----------+--------+-------+
1 row in set (0.00 sec)

​

查找爱好中有代码，

mysql> select *from votes;
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 曹操     | 男     | 羽毛球,足球      |
| 曹操     | 男     | 羽毛球,足球      |
| 曹操     | 男     | 羽毛球           |
| 刘备     | 男     | 羽毛球           |
| 刘备     | 男     | 代码             |
+----------+--------+------------------+
5 rows in set (0.00 sec)mysql> select * from  votes where find_in_set('羽毛球' ,hobby);
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 曹操     | 男     | 羽毛球,足球      |
| 曹操     | 男     | 羽毛球,足球      |
| 曹操     | 男     | 羽毛球           |
| 刘备     | 男     | 羽毛球           |
+----------+--------+------------------+
4 rows in set (0.00 sec)

mysql> select * from  votes where find_in_set('羽毛球' ,hobby) and find_in_set('足球', hobby);
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 曹操     | 男     | 羽毛球,足球      |
| 曹操     | 男     | 羽毛球,足球      |
+----------+--------+------------------+
2 rows in set (0.00 sec)