MySQL建库建表与管理

建库建表与管理

引入扩展：存储一条数据的过程。

创建数据库。
确认字段。
创建数据表。
插入数据。

那我们先从创建库开始吧😊

1. 创建数据库与管理数据库

创建数据库时 - 注意事项：

需用当前登录的用户具备 "创建数据库的权限"。
如果在创建数据库时，未指定使用的字符集，使用默认的字符集（utf8mb4）。

1. 创建数据库

方式1：普通创建数据库（一些默认）

CREATE DATABASE 数据库名; 
# 
CREATE DATABASE supconit_db;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARSET =  字符集;
# 
CREATE DATABASE supconit_db_char CHARSET = utf8mb4;

方式3：判断式创建（如果数据库不存在，创建，反之不创建）

CREATE DATABASE IF NOT EXISTS 数据库名 CHARSET = 字符集;
#
CREATE DATABASE IF NOT EXISTS supconit_db CHARSET =utf8mb4;

查看数据库（创建信息）语句

SHOW CREATE DATABASE 数据库名;
#
SHOW CREATE DATABASE supconit_db;
查看所有的数据库
SHOW DATABASES;
查看当前正在使用的数据库
SELECT DATABASE();

2. 切换，修改和删除数据库

使用/切换数据库

USE 数据库名;
# 
USE mysql;

修改数据库 - 更改数据库字符集

ALTER DATABASE 数据库名 CHARSET=字符集;
#
ALTER DATABASE supconit_db CHARSET=utf8mb3;

删除数据库

# ? 怎么删除呢?
# 提示：这是一个 DDL 语句

扩展 - 查看数据库下所有的表

SHOW TABLES FROM 数据库名;
# 
SHOW TABLES FROM mysql;

2. 创建表

创建表时 - 注意事项：

需用当前登录的用户具备 "创建表的权限"。
如果在创建表时，未指定使用的字符集，默认会使用表所在的数据库的字符集。

前言知识：

查看表结构

DESC 表名;

查看创建表的信息

SHOW CREATE TABLE 表名;

基于数据字典查看表的详细信息

SELECT
	*
FROM
	information_schema. COLUMNS
WHERE
	TABLE_SCHEMA = '库名称'
AND TABLE_NAME = '表名称'

1. 创建表

创建表 - 语法

CREATE TABLE [IF NOT EXISTS ] 数据库名.表名
(
	column_lists 数据类型 [各种约束] COMMENT '注释'
	[PRIMARY KEY (主键字段1,主键字段2,...) ] #可以设置多个主键字段
	[CONSTRAINT 外键约束名称 FOREIGN KEY (外键列) REFERENCES 主表名(表主键)...] #基本不使用
    [ADD INDEX 索引 (索引字段)]
    [CONSTRAINT 约束名称 约束类型 (约束字段)]
)
[ENGINE = 存储引擎] #不指定，默认为：INNODB
[AUTO_INCREMENT = 自增初始值] #不指定，默认为 0
[DEFAULT CHARSET = 字符集]; #默认会使用表所在的数据库字符集

创建表 - 实例

CREATE TABLE
IF NOT EXISTS supconit_db.supconit (
	id INT PRIMARY KEY auto_increment COMMENT '主键',
	c_name VARCHAR (20) NOT NULL COMMENT '非空的名字',
	age TINYINT UNSIGNED COMMENT '无符号的年龄',
	c_code VARCHAR (30) NOT NULL UNIQUE COMMENT '唯一且不为空的身份账号'
) ENGINE = INNODB auto_increment = 10 DEFAULT CHARSET = utf8mb4;

# 一些默认
CREATE TABLE
IF NOT EXISTS supconit_db.supconit_table (
	id INT PRIMARY KEY auto_increment COMMENT '主键',
	c_name VARCHAR (20) NOT NULL COMMENT '非空的名字',
	age TINYINT UNSIGNED COMMENT '无符号的年龄',
	c_code VARCHAR (30) NOT NULL UNIQUE COMMENT '唯一且不为空的身份账号'
);

# 多主键
CREATE TABLE
IF NOT EXISTS supconit_db.supconit_primarys(
	id INT COMMENT '主键',
	c_name VARCHAR (20) NOT NULL COMMENT '非空的名字',
	age TINYINT UNSIGNED COMMENT '无符号的年龄',
	c_code VARCHAR (30) COMMENT '唯一且不为空的身份账号',
	PRIMARY KEY(id,c_code)
);

说明：

创建表时指定外键的方式，在实际开发中不会使用，这里不多做介绍。

创建表时指定索引的方式，后续会在索引章节做详细说明。

2. 克隆表

克隆表结构

CREATE TABLE 新表名 LIKE 被克隆的表名;
#
CREATE TABLE supconit_db.supconit_like LIKE supconit;

基于现有的表，创建新的表，同时导入数据

注意：这种方式创建表时可能不会将表中的一些约束复制过来的。例如：主键约束，外键约束，索引等...

CREATE TABLE 新表名 AS SELECT * FROM 被复制的表名;
# 
CREATE TABLE supconit_as_select AS SELECT * FROM supconit;

扩展：

# 指定字段克隆
CREATE TABLE 表名(字段,字段1,字段2,...) AS SELECT
	字段,
	字段1,
	字段2 ,...
FROM
	现有表;

CREATE TABLE 表名 AS SELECT
	字段,
	字段1,
	字段2 ,...
FROM
	现有表A
JOIN 现有表B ON 连接条件;

3. 表重命名

方式1： RENAME TABLE

RENAME TABLE 旧表名 TO 新表名;
#
RENAME TABLE supconit TO supconit_rename;

方式2： ALTER TABLE

ALTER TABLE 旧表名 RENAME TO 新表名;
#
ALTER TABLE supconit_rename RENAME TO supconit;

4. 删除表

注意：默认情况下，表一旦删除是无法回滚的，请谨慎操作！

# ? 怎么删除呢?
# 提示：这是一个 DDL 语句

5. 清空表

TRUNCATE TABLE 表名;
# 
TRUNCATE TABLE supconit;

DDL 命令，该方式相比 DML 语句清空表，要快的多！

3. 修改表-列

1. 添加列

添加列 - 语法

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束] COMMENT '注释' [FIRST|AFTER]

说明：

first 关键字用于将 "添加的列" 放置到表中的第一列。

after 关键字用于指定 "添加的列" 放置到那个现有列后面。

在不指定 first 和 after 的情况下，MySQL 默认将 "添加的列" 放置到表中的最后一列。

添加列 - 实例

# 默认在表的最后一列
ALTER TABLE supconit ADD COLUMN start_time datetime COMMENT '出生日期';

添加列到指定位置

# 将添加的列放置到表中的第一列
ALTER TABLE supconit ADD COLUMN id_ext INT NOT NULL COMMENT '扩展id' FIRST;

# 将添加的列放置到 age 列之后
ALTER TABLE supconit ADD COLUMN void_age TINYINT UNSIGNED COMMENT '虚年龄' AFTER age;

添加列 - 多个字段

# 语法：用逗号分割
ALTER TABLE 表名   
  ADD COLUMN 列名 数据类型 [各种约束] [FIRST|AFTER 现有列],
  ADD COLUMN 列名 数据类型 [各种约束] [FIRST|AFTER 现有列];

# 实例
ALTER TABLE supconit_table 
 ADD COLUMN id_ext INT NOT NULL COMMENT 'id扩展' FIRST,
 ADD COLUMN void_age TINYINT UNSIGNED DEFAULT 0 COMMENT '虚年龄' AFTER age,
 ADD COLUMN start_time datetime COMMENT '出生日期';

2. 修改列

你如果修改了列的 "数据类型"，那请你一定注意原来的 "数据类型" 和你要修改的 "数据类型" 是否存在冲突~。在修改大数据表的列时，这个修改会很慢。这里更加推荐添加虚拟列来解决问题。

ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型 [各种约束];
#
ALTER TABLE supconit_table MODIFY COLUMN void_age INT UNSIGNED;

修改列的约束

ALTER TABLE supconit_table MODIFY COLUMN void_age TINYINT UNSIGNED DEFAULT 0 ;

修改列的位置

ALTER TABLE supconit_table MODIFY COLUMN id_ext INT NOT NULL AFTER id;

注意：每次在修改列时，你都必须重新指定该列的 "数据类型" 呢！

3. 列重命名

说明：列重命名其本质也是对列的一次修改。

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 数据类型 [各种约束];
#
ALTER TABLE supconit_table CHANGE COLUMN id_ext id_ext_new INT NOT NULL;

列重命名时修改列的约束

ALTER TABLE supconit_table CHANGE COLUMN id_ext_new id_ext_new INT DEFAULT 0;

列重命名时修改列的位置

ALTER TABLE supconit_table CHANGE COLUMN void_age void_age_new TINYINT UNSIGNED AFTER c_code;

注意：每次在给列重命名时，你都必须重新指定该列的 "数据类型" 呢！

4. 删除列

ALTER TABLE 表名 DROP COLUMN 列名;
# 
ALTER TABLE supconit_table DROP COLUMN id_ext_new;

4. MySQL 8.0 新特性 DDL 的原子性

DDL：一旦操作成功，就不可回滚。
DML：默认情况下，一旦执行，也是不可回滚的。但是，可以修改默认提交配置，实现 DML 的回滚。
- COMMIT：提交数据。一旦执行 commit ，则数据就被永远的保存了，意味着数据库不可回滚。
- ROLLBACK：回滚数据。一旦执行 rollback，则可以实现数据回滚。回滚到最近一次的 commit 之后。
```
#关闭默认执行提交
set autocommit = false;
```
MySQL 8.0：如果在删除多张的表的时候，其中的一张表删除失败，报错了。那么在该删除语句中的所有表则都不会删除。保证了 DDL 的原子性。
MySQL 5.0：如果在删除多张的表的时候，其中的一张表删除失败，报错了。那么在该删除语句中的删除成功的表，则真的都删除了。没有保证原子性。

5. 内容扩展

阿里-删除表规范

truncate table 比 delete 速度快。但 truncate 无事务且不触发 "触发器"，有可能造成事故，故而不建议在开发代码中使用。

说明：truncate table 在功能上和不带 where 语句的 delete 语句相同。

阿里-MySQL 列

强制规范：
表名，列名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库列名修改代价很大，所以列名称需要慎重考虑。
# 正例：user_name,user_config,level3_name
# 返例：UseName,userConfig,level_3_name
禁用保留字，如 desc，table，from，select等.... 请参考 MySQL 官方保留字。

表必备三个字段：id，gmt_create（创建时间），gmt_modified（修改时间）

唯一索引名为 uk 列名（unique key），普通索引名则为 idx 列名（index）。

小数禁止使用 float 和 double，必须使用 decimal。

varchar 是可变字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
推荐规范：

表的命名最好是遵循 "业务名称"。

库名与 "应用名称" 尽量一致。

列允许适当冗余，以提高查询性能，但必须考虑 "数据一致性"。冗余字段应遵循：

不是频繁修改的字段。

不是唯一索引的字段。

不是 varchar 超长字段，更不能是 text 字段。

单表 "行数超过 500 万行" 或者 "单表容量超过 2GB"，才推荐进行分库分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。