【MySQL】库和表的操作

一. 库的操作

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [[DEFAULT] CHARSET=charset_name] [[DEFAULT] COLLATE=collation_name];

• SQL 中大写的表示关键字, [ ]中代表的是可选项;
• CHARSET(charset): 用于指定数据库所采用的编码格式; 若未指定, 则使用配置文件中的默认格式;
• COLLATE(collate): 用于指定数据库所采用的校验规则; 若未指定, 则使用配置文件中的默认规则;

在 SQL 中不区分关键字的大小写;

使用例:

创建名为 t1 的数据库;

create database t1;

创建使用 utf8mb4 编码格式的数据库 t2;

create database t2 charset=utf8mb4;

以下三种写法也可以

create database t2 charset utf8mb4;
create database t2 character set=utf8mb4;
create database t2 character set utf8mb4;

创建使用 utf8mb4 编码格式和 utf8mb4_0900_ai_ci 校验规则的数据库 t3;

create database t3 charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

校验规则的另一种写法也可以

create database t3 charset=utf8mb4 collate utf8mb4_0900_ai_ci;

2. 删除数据库

DROP DATABASE [IF EXISTS] db_name;

注: 删除数据库 会删除数据库的下级文件及目录, 级联删除, 不要轻易删除数据库;

3. 查看数据库

显示当前服务器的所有数据库

SHOW DATABASES;

显示当前所在的数据库

SELECT DATABASE();

显示指定数据库的属性

SHOW CREATE DATABASE da_name;

• 数据库名的反引号是为了防止数据库名与关键字冲突;
• /*!40100 default… */ 不是注释, 表示当前使用 MySQL 版本若大于4.01版本, 执行这里的 SQL 语句;

显示数据库的连接情况

SHOW PROCESSLIST;

4. 修改数据库

ALTER DATABASE db_name [[DEFAULT] CHARSET=character_name] [[DEFAULT] COLLATE=collation_name];

数据库的修改主要是数据库的字符集和校验规则, 但数据库的修改不会影响数据库中已有表的字符集和校验规则;

数据库的更名方法推荐备份数据, 新建数据库(更名), 导入数据的方法, 并且轻易不要改名;

5. 数据库的备份与恢复

使用 MySQL 提供的备份工具 mysqldump 进行备份;

mysqldump -u 用户 -p -P 端口 -B 待备份的数据库 > 目标路径(含目标文件)

• -B 表示备份时, 将创建数据库的语句进行备份, 恢复时将会自行创建数据库; 若不加 -B 选项, 恢复时先 use 选择数据库, 然后才能 source;

若当前数据库正在被使用, 需要先断开连接;

查看备份文件, 其中包含了数据库的配置信息和有效的 SQL 语句;

恢复则在 MySQL 中使用 SQL 语句;

SOURCE 路径; 

6 . 字符集和校验规则

查看系统默认字符集及校验规则;

show variables like 'character_set_database';
show variables like 'collation_database';

查看数据库支持的字符集和校验规则;

show charset;
show collation;

校验规则对数据库的影响

创建 d1 数据库, 使用 utf8mb4_0900_ai_ci 校验规则, 不区分字母的大小写;

在 d1 数据库中创建 t1 表, 插入大写和小写的数据, 并查询小写数据, 结果大写和小写字母全部打印;

创建 d2 数据库, 使用 utf8mb4_bin 校验规则, 区分字母的大小写;

在 d2 数据库中创建 t2 表, 插入大写和小写的数据, 并查询小写数据, 只有小写字母打印;

二. 表的操作

1. 创建表

CREATE TABLE [IF NOT EXISTS] table_name(field1 datatype1 [COMMENT '注释信息'],field2 datatype2 [COMMENT '注释信息'],field3 datatype3  [COMMENT '注释信息']
)[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];

• field: 表示列名
• datatype: 表示列的类型
• CHARSET: 字符集, 若未指定则使用当前数据库的字符集;
• COLLATE: 校验规则, 若未指定则使用当前数据库的校验规则;
• ENGINE: 存储引擎, 若未指定则使用默认的存储引擎;

2. 删除表

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;
// temporary 关键字暂时忽略即可;

3. 查看表

显示数据库所有的表;

SHOW TABLES;

显示指定表创建的详细信息

SHOW CREATE TABLE table_name

也可以格式化输出内容

SHOW CREATE TABLE table_name \G

显示指定表的结构;

DESC tablename

• Field: 表示列名;
• Type: 表示类型;
• Null: 表示是否可为空;
• Key: 表示是否存在主键/外键/唯一键约束;
• Default: 表示是否有默认值;
• Extra: 表示额外信息;

4. 修改表

新增字段

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);

• column: 表示列名;
• datatype: 表示类型;
• [DEFAULT expr]: 表示该列的各种属性, 例如约束, 默认值, 是否可为空等, 支持同时新增多个字段;

新增的字段默认是在最后一个字段之后的;

当新增单个字段时, 支持插入在指定字段后, 和 “( )” 冲突;

删除字段

ALTER TABLE tablename DROP (column), [DROP ...];

修改指定字段属性

ALTER TABLE tablename MODIFY column datatype [DEFAULT expr], [MODIFY ...];

• 需确保修改的字段存在, 并且要保证修改后的字段类型与表中已存在的数据类型相匹配;
• 当修改多个字段时, 需叠加多条 MODIFY column datatype [DEFAULT expr] 语句, 通过 “,” 分隔;
• 在修改字段后, 原字段的所有信息都会被覆盖, 包括注释;

修改 ch 的类型为 varchar;

修改列名

ALTER TABLE tablename CHANGE oldcolumn newcolumn datatype [DEFAULT expr];

需要注意的地方和修改指定字段属性相同;

修改表名

ALTER TABLE oldname RENAME [TO] newname;

修改字段名和表名是危险操作, 会影响上层使用, 不要轻易更改;