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"，才推荐进行分库分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
