Mysql 之数据库、数据表创建、修改操作汇总

1、Mysql 中标识符命名规则

1）、数据库名、表名不得超过30个字符，变量名限制为29个字符，必须只能包含 A–Z, a–z, 0–9, _共63个字符；
2）、数据库名、表名、字段名等对象名中间不要包含空格；
3）、必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（反引号）引起来；
4）、保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

2、Mysql中的数据类型

2.1、数据类型概览

字段种类	类型
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON； 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

2.1、数据类型详细描述

数据类型	描述
INT	从-2^31到2^31-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度。
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30， 默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式'YYYY-MM-DD'
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

3、数据库操作

3.1、创建数据库

# 1、基本方式（都使用默认值）
CREATE DATABASE 数据库名;
# 2、指定字符集创建
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
# 3、判断数据库是否已经存在，不存在则创建数据库（ 推荐）
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集; # 示例：CREATE DATABASE IF NOT EXISTS test character set 'utf8';

3.2、使用数据库

# 1、查看当前所有数据库
show databases;
# 2、查看当前正在使用的数据库
SELECT DATABASE();
# 3、查看指定库下所有的表
SHOW TABLES FROM 数据库名;
# 4、查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G
# 5、使用/切换数据库
USE 数据库名;

3.3、修改数据库

# 1、更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; # 例如：gbk、utf8等
# 2、删除数据库
DROP DATABASE 数据库名;
或者
DROP DATABASE IF EXISTS 数据库名;  # 这种方式常用

4、数据库表操作

4.1、创建数据表

注：

1）、表名，字段名，数据类型，长度必须指定；

2）、在MySQL 8.x版本中，不再推荐为INT类型指定显示长度。

# 1、指定字段名创建
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
# 2、通过子查询创建
CREATE TABLE emp1 AS  [select * ...]

约束条件、默认值说明

约束条件	描述	特点	作用域	示例
NOT NULL	非空约束	1）、所有的类型的值都可以是NULL； 2）、空字符串''不等于NULL，0也不等于NULL。	列	sname varchar(20) not null
UNIQUE	唯一约束	规定某个字段在整个表中是唯一的	单列/多列	1）、字段名 数据类型 unique, 2）、字段名 数据类型 unique key, 3）、[constraint 约束名] unique key(字段名)
PRIMARY KEY	主键(非空且唯一)约束	一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。	列/表级	1）、字段名 数据类型 primary key, 2）、[constraint 约束名] primary key(字段名) #表级模式
FOREIGN KEY	外键约束	限定某个表的某个字段的引用完整性。	单列	[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
DEFAULT	默认值约束	一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默 认值。	列	字段名 数据类型 not null default 默认值,
AUTO_INCREMENT	自增列	1）、一个表最多只能有一个自增长列 2）、自增长列约束的列必须是键列 3）、自增约束的列的数据类型必须是整数类型 4）、如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接 赋值为具体值。	列	1）、字段名 数据类型 unique key auto_increment, 2）、字段名 数据类型 primary key auto_increment,
CHECK	检查约束	检查某个字段的值是否符号xx要求，一般指的是值的范围	列	age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

4.2、查看表结构

# 1、查看具体表结构
desc 表名
# 2、查看建表语句
SHOW CREATE TABLE 表名\G;

4.3、修改表名

# 1、使用rename
RENAME TABLE 旧表名 TO 新表名;
# 2、使用alter
ALTER table 旧表名 RENAME [TO] 新表名; # [TO]可以省略

4.4、修改表字段

注：以下命令均支持批量操作

# 1、新增表字段
ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名],
ADD [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名], ...;
# 2、修改表字段（可以修改列的数据类型，长度、默认值和位置）
ALTER TABLE 表名 MODIFY [COLUMN] 字段名 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名],
MODIFY [COLUMN] 字段名 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名], ...;
# 3、字段重命名
ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型,
CHANGE [COLUMN] 列名1 新列名1 新数据类型,...;
# 4、删除字段
ALTER TABLE 表名 DROP [COLUMN] 字段名,DROP [COLUMN] 字段名1, ...;

4.5、删除 && 清空表

删除表：数据和结构都被删除，所有正在运行的相关事务被提交，所有相关索引被删除。

清空表：删除表中所有的数据，释放表的存储空间。

# 1、删除表
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
# 2、清空表
TRUNCATE TABLE 数据表;

 注：删除表和清空表都无法回滚，因此需要谨慎操作。

5、总结

        本文详细介绍Mysql中数据库、数据库表的具体操作方法，开启 mysql 入门之旅，关于Mysql 高级内容，关注微信公众号获取。 

        本人是一个从小白自学计算机技术，对运维、后端、各种中间件技术、大数据等有一定的学习心得，想获取自学总结资料（pdf版本）或者希望共同学习，关注微信公众号：it自学社团。后台回复相应技术名称/技术点即可获得。（本人学习宗旨：学会了就要免费分享）