欢迎点击上方蓝字关注我
〇、MySQL数据类型
1. 数值数据类型
数值数据类型存储数值。
MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。
整数
长度int(n)与zerofill
int(n)只影响显示字符的宽度，不限制数值的合法范围。int(3)依然可以存储123456789这么大的数值。
若设置了zerofill属性，当int(3)存储12时，会在前面补0，补足3位。即012；当int(5)存储12时，会在前面补三个0，补足5位。即00012
有符号或无符号
所有数值数据类型(除BIT和BOOLEAN外)都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字，这样做将允许你存储两倍大小的值。
小数
DECIMAL最常用的用法就是用来存储货币，例如DECIMAL(8, 2)。DECIMAL还可以用于存储比BIGINT还大的整数以及精确的小数。
2. 串数据类型
从 MySQL4.1 版本开始，char(n)和varchar(n)中的n指字符长度，不再表示之前版本的字节长度。也就是说在不同字符集下，char类型列的内部存储可能不是定长数据。
CHAR
CHAR 是定长字符串，会直接根据定义字符串时指定的长度分配足够的空间。
CHAR 适合存储所有值长度相同的字符串或很短的字符串。
VARCHAR
VARCHAR 的最大长度是65535个字节，而varchar(n)中的n指字符长度，因此，n的最大值是由当前字段的字符集决定的。当字符集是utf8时，n的最大值为 21845。当字符集是utf8mb4时，n的最大值为 16383。(但是实际上MySQL要求一个行的定义长度不能超过65535个字节，因此，除非表中只有这一个字段，否则n的值达不到上述的最大值)。
VARCHAR 使用1-2个额外字节记录字符串长度，列长度小于等于255个字符时，使用1个字节记录，否则使用2个字节。
最佳实践
对于经常变更的数据， CHAR 比 VARCHAR 更好，CHAR 的磁盘空间利用率更高，不容易产生碎片。
当列中数据的长度相同时，选择 CHAR；当列中数据长度参差不齐时，选择 VARCHAR。
对于非常短的列，CHAR 比 VARCHAR 在存储上更有效率。
只分配真正需要的空间，更长的列会消耗更多的内存。
尽量避免使用 BLOB/TEXT 类型，查询时会使用临时表，导致严重的性能开销。如果一定要用，建议单独建表存储该字段。
3. 二进制数据类型
4. 日期和时间类型
最佳实践
尽量使用 TIMESTAMP，比 DATETIME 的空间利用率高。
一、创建数据表
CREATE TABLE
使用CREATE TABLE 创建表，必须给出下列信息：
表的名字，在关键字 CREATE TABLE 之后给出；
表中字段的名字和定义，用逗号分隔。
以下为创建MySQL数据表的SQL通用语法：
column1 datatype [NULL|NOT NULL] [DEFAULT ], column2 datatype, );CREATE TABLE table_name (
实例
创建用户表
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `username` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '密码', `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态', `created_at` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `unq_email` (`email`) USING BTREE, KEY `idx_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT '用户表';CREATE TABLE IF NOT EXISTS `user` (
实例解析
如果数据库中不存在user表时，创建该表。存储引擎为InnoDB，默认字符集为utf8
ENGINE 设置存储引擎，CHARSET 设置编码。
... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT '用户表'CREATE TABLE IF NOT EXISTS `user` (
创建名为id的字段，整型，非负数，不能为空，自增。
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
`id`int(10) UNSIGNEDNOTNULLAUTO_INCREMENT
创建名为username的字段，字符串类型，最大长度为190个字符，字符集为utf8mb4，不能为空
`username`varchar(190)CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNOTNULL,
将字段id设为主键，使用BTREE索引
PRIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键，列间以逗号分隔。
PRIMARYKEY(`id`)USINGBTREE,
为字段email添加唯一索引，索引名称为unq_email
设置了唯一索引的字段不能出现重复的值，但是如果字段可以为null，则允许出现多个null值。
UNIQUEKEY `unq_email` (`email`)USINGBTREE
为字段username添加普通索引，索引名称为idx_username
KEY `idx_username` (`username`)
二、查看数据表
1. 查看数据库中的所有数据表
SHOW TABLES用于查看数据库中的所有数据表。
+----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.07 sec)mysql> SHOW TABLES;
2. 查看数据表的建表SQL语句
SHOW CREATE TABLE用于查看指定数据表的建表SQL语句
语法：
SHOWCREATETABLEtable_name
查看user表的建表语句
*************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '密码', `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态', `created_at` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `unq_email` (`email`) USING BTREE, KEY `idx_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='用户表' 1 row in set (0.00 sec)mysql> SHOW CREATE TABLE `user`\G
3. 查看数据表结构
DESCRIBE 和 DESC
DESCRIBE可用于查看表结构，DESC是DESCRIBE的缩写。
语法：
DESCRIBEtable_name
查看user表的表结构
+------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)mysql> DESCRIBE `user`;
+------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)mysql> DESC `user`;
EXPLAIN
EXPLAIN也可以用于查看表结构。
语法：
EXPLAIN table_name
DESCRIBE和EXPLAIN语句是同义词，实际上在平时使用过程中DESCRIBE多用于获取表结构的信息，而EXPLAIN多用于获取SQL语句的执行计划。
查看user表的表结构
+------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.03 sec)mysql> EXPLAIN `user`;
三、修改数据表
1. 重命名数据表
语法
RENAMETABLEold_nameTO new_name;
将user表重命名为consumer，再改回user
Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | consumer | +----------------+ 1 row in set (0.06 sec) mysql> RENAME TABLE `consumer` TO `user`; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.05 sec)mysql> RENAME TABLE `user` TO `consumer`;
2. 增加字段
语法
ALTERTABLEtable_nameADDcolumn_name column_type
给user表添加一个字段intro
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC `user`; +------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | intro| varchar(255) | YES || NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 7 rows in set (0.06 sec)mysql> ALTER TABLE `user` ADD `intro` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '简介' AFTER `email`;
3. 修改字段
修改字段名和属性
语法
ALTERTABLEtable_name CHANGE old_name new_name column_type;
将user表的intro字段名改为about
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC `user`; +------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | about| varchar(255) | YES || NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 7 rows in set (0.07 sec)mysql> ALTER TABLE `user` CHANGE `intro` `about` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '简介';
修改字段属性
语法
ALTERTABLEtable_name MODIFY column_name column_type;
将user表的about字段字符串最大长度改为200个字符
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC `user`; +------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | about| varchar(200) | YES || NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 7 rows in set (0.09 sec)mysql> ALTER TABLE `user` MODIFY `about` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '简介';
4. 删除字段
语法
ALTERTABLEtable_nameDROPCOLUMNcolumn_name;
从user表中删除about字段
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC `user`; +------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.08 sec)mysql> ALTER TABLE `user` DROP COLUMN `about`;
5. 添加索引
语法
ALTERTABLE`user`ADD[ KEY|UNIQUEKEY|PRIMARYKEY] idx_name (column_name);
为user表中的created_at字段添加普通索引，索引名为idx_created_at
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC `user`; +------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES | UNI | NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO | MUL | NULL || +------------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.07 sec)mysql> ALTER TABLE `user` ADD KEY `idx_created_at` (`created_at`);
6. 删除索引
语法
ALTERTABLE`user`DROPKEY idx_name;
删除user表中的created_at和email两个字段的索引，索引名为idx_created_at和unq_email
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `user` DROP KEY `unq_email`; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC `user`; +------------+---------------------+------+-----+---------+----------------+ | Field| Type| Null | Key | Default | Extra| +------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(190) | NO | MUL | NULL || | email| varchar(255) | YES || NULL || | password | varchar(255) | YES || NULL || | status| tinyint(3) unsigned | NO || 0 || | created_at | int(11) unsigned | NO || NULL || +------------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.06 sec)mysql> ALTER TABLE `user` DROP KEY `idx_created_at`;
四、删除数据表
可以使用DROP TABLE命令删除一个或者多个数据表。
在使用DROP TABLE删除数据表时，要删除的数据表必须存在，否则会报错。1. 删除一个数据表
DROPTABLEtablename
2. 批量删除数据表
DROPTABLEtablename1,tablename2,tablename3
删除user表
Query OK, 0 rows affected (0.01 sec) mysql> DESC `user`; 1146 - Table 'test.user' doesn't exist mysql> SHOW TABLES; Empty setmysql> DROP TABLE `user`;
点个在看升职加薪
