MySQL 数据库的结构操作之数据库与表

MySQL 数据库的结构操作之数据库与表 DDL

[TOC]

## 1. 数据库操作指令
### 1.1. 登录数据库
```SQL
mysql -h localhost -u root -proot
```

>  -p 后面的 root 是密码，需注意 -p 和密码之间没有空格。如果出现空格，系统将不会把后面的字符串当成密码来对待

#### 1.1.1. 登录指定密码的加密规则

创建账户时可以为账户的密码指定密码的加密规则，如果创建时指定了非默认的加密规则，那么登录时需要带上指定对应的加密规则的参数

```SQL
-- 创建username账户时，指定了密码的加密规则为mysql_native_password，因此登录需要带上规则参数
mysql -u username -p --default-auth=mysql_native_password
```

> MySQL8用户密码加密规则默认是caching_sha2_password，而之前的版本是mysql_native_password，因为特殊场景需要在MySQL8中使用mysql_native_password规则时需注意带加密规则这个参数

### 1.2. 查看数据库
+ 查看所有数据库

```SQL
show databases;
```

+ 查看指定数据库
查看指定数据库其实就是模糊查询数据库名称，模糊查询会用到两个替代符号，`%`是多个字符匹配，`_`是单个字符匹配只能替代一个字符，如果希望`_`不是替代字符而是真实字符需要使用`\_`这种方式对`_`进行转义，如果模糊查询没有找到则返回`Empty set`提示

```SQL
-- 两端模糊，%可替换多个字符，查询结果为study
show databases like '%tu%';
-- _只能替换一个字符，查询结果为study
show databases like '_tu__';
-- 对_转义查询，结果是匹配不到的返回Empty set提示
show databases like 'stu\_%';
```

+ 查看数据库的创建语句
从显示的结果中发现输出的语句并不是当时创建数据库的语句，这是因为数据库在执行SQL语句之前会优化SQL，系统保存的结果是优化的结果

```SQL
-- 显示创建数据库的SQL脚本语句
show create database study;
-- 结果：
-- CREATE DATABASE `study` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
```
> 注意：如果数据库名称是关键字，那么查询时都需要使用反引号（ESC下面的键在英文状态下输出）包裹数据库名称

### 1.3. 创建库
__命名规则__：数据库名称可由字母，下划线，数字组成，但不能以数字开头，数据名称不可用关键字或保留字，如果非要使用关键字做为数据库名称，需要在关键字加上反引号（ESC下面的键在英文状态下输出），数据库名称建议不要使用中文作为数据库名称，如果非要使用创建方式见下面的【创建中文名称的数据库】
__库选项__：创建数据库也有库选项参数，库选项参数用来约束数据库，数据库选项分别为`字符集`和`校对集`

+ 快速创建

```SQL
-- 创建一个名称为study的数据库
create database study
```

+ 字符集约束创建
字符集的两种方式 charset/character set

```SQL
-- 创建UTF8约束的数据库
create database study charset UTF8
-- 创建GBK约束的数据库
create database person character set GBK
```

+ 校对集：校对集依赖字符集，参考：[补全](https://blog.csdn.net/J080624/article/details/71310545)

```SQL
create database student collate _bin
```

+ 默认字符集

```SQL
create database if notexists student default charset utf8mb4;
```

+ 创建中文名称的数据库

```SQL
-- 第一步要设置当前中文字符集
set names gbk
-- 第二步创建中文数据库
create database 中国 charset UTF8
```

+ 创建数据库时判断是否已存在

```SQL
create database if not exists study;
```

> 验证已创建数据的方式：1. 使用`show databases`命令查询创建的数据库是否存在，2. 如果是win10下安装的Mysql，默认C:\ProgramData\MySQL\MySQL Server 8.0\Data中会创建一个和数据名称一样的文件夹，如果数据库是中文的话，这是文件夹的名称会是编码过的，例如中国会显示为【@4e2d@56fd】，所以不建议使用中文作为数据库名称

> 注意：上述语法中的database,也可以替换成 schema。如:create schema study;MySQL8版本中，默认字符集为utf8mb4。

### 1.4. 更新数据库
数据库名称不可以修改，数据的修改仅限库选项`字符集`和`校对集`，校对集依赖字符集

```SQL
-- 修改字符集，这里修改字符集校对集也会跟着被修改
alter database study charset UTF8
-- 修改校对集
alter database study collate _bin
```

### 1.5. 删除数据库
删除数据库为结构操作，使用DDL中的`drop`

```SQL
drop database test
```
> 删除命令执行后发生了什么：1. 数据库内部看不到删除的库了，2. 操作系统物理磁盘上之前为数据库创建的文件夹也被删除了

### 1.6. 进入数据库
对数据进行操作之前先要切换到要操作的数据库目录下

```SQL
-- study是表名
use study;
```

### 1.7. 查看当前选择的库
查看当前在那个数据库下操作，确认操作是在正确的数据库下进行的

```SQL
select database();
```

## 2. 表操作指令
### 2.1. 创建表
__重名检查__：执行创建表的命令之前，数据库会对的表名称进行重名检查，如果已经存在不予再创建
__库选项__：创建表也有库选项参数，库选项参数用来约束数据表，表选项分别为`字符集`和`校对集`以及`存储引擎`

+ 字符集
保证表中数据存储的字符集，以防出现内容乱码情况，字符集的两种方式 charset/character set

```SQL
create table student(
   id int,
   name varchar(20),
   gender varchar(2),
   age int
) charset utf8;
```

+ 校对集：collate校对集依赖字符集（待补全）
+ 存储引擎：engine引擎有innodb和myisam（待补全）

> PS：没有进入到数据库中，需要在创建表的名称前面添加库名称：create table study.student
创建命令执行后发生了什么：1. 数据库中创建了对应的表，2. 在磁盘上对应的数据库文件夹下创建了表结构文件，后缀为.ibd

### 2.2. 复制表
如果是跨库复制，记得要带库名(例如库.dbo.表)

#### 2.2.1. 复制表结构
__语法：__create table 表 like 表

**语法说明： **使用like仅复制表的列结构，不复制数据，此复制还能复制 primary key 和 auto_increment

```SQL
-- 将study2库的my_info2表，复制到study库，表名为my_info
create table study2.dbo.my_info like study2.dbo.my_info2;
desc my_info
-- 对比之下表结构一致，
```

> PS 上面复制表的方式只会复制表结构，不会复制表数据

#### 2.2.2. 复制表结构和数据

__语法：__create table 表 select * from 表

**语法说明： **使用这种方法可以复制的表的结构和表的数据，但 auto_increment 和 primary key 不会复制，类型也可能会变化例如varchar(100)可能会变成char(100)

```SQL
-- 将study2库的my_info2表和数据，复制到study库
create table study2.dbo.my_info select * from study2.dbo.my_info2;
```

### 2.3. 查看表

+ 查看所有表

```SQL
show tables;
```

+ 查看指定表
查看指定表其实就是模糊查询表名称，模糊查询会用到两个替代符号，`%`是多个字符匹配，`_`是单个字符匹配只能替代一个字符，如果希望`_`不是替代字符而是真实字符需要使用`\_`这种方式对`_`进行转义，如果模糊查询没有找到则返回`Empty set`提示

```SQL
-- 两端模糊，%可替换多个字符，查询结果为student
show tables like '%tu%';
-- _只能替换一个字符，查询结果为student
show tables like '_tu__';
-- 对_转义查询，结果是匹配不到的返回Empty set提示
show tables like 'stu\_%';
```

+ 查看表的创建语句
从显示的结果中发现输出的语句并不是当时创建表的语句，这是因为数据库在执行SQL语句之前会优化SQL，系统保存的结果是优化的结果

```SQL
-- 显示创建表的SQL脚本语句
show create table student;
-- 下面的\g等同于;
show create table student\g
-- 下面的\G是将查到的结构旋转90°变成纵向展示（默认是横向展示）;
show create table student\G
```

### 2.4. 查看表结构
查询表的字段描述信息，有三种查询方式`desc`/`describe`/`show columns from`查询结果都是一样的

```SQL
desc student;
describe student;
show columns from student;
```

__查询结果：__

| Field  | Type        | Null | Key | Default | Extra |
|--------|-------------|------|-----|---------|-------|
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    | &nbsp;|

### 2.5. 修改表信息
修改表信息包括修改表名称和表选项，表选项包括字符集，校对集和存储引擎

+ 修改表名

```SQL
-- student：原表名，my_student：新的表名
rename table student to my_student;
```

+ 修改字符集

```SQL
-- 基础修改字符集
alter table student charset utf8;
-- 只修改表的字符集，不影响到列的字符集就在编码前面加关键字default
alter table student default character set utf8;
-- convert to 可以修改整个表的全局编码，包括列的编码
alter table student convert to character set utf8;
```
> 同修改数据的字符集一样`charset`等同于`character set`，两种方式可以任意使用

+ 修改校对集：待补充
+ 修改存储引擎：待补充

### 2.6. 删除表
#### 2.6.1. 简单删除

__语法：__drop table 表1,表2

```SQL
drop table my_student1,my_student2
```

> 可以一次性同时删除多张表
删除命令执行后发生了什么：1. 数据库中没有了对应的表，2. 在磁盘上对应的数据库文件夹下创建的表关联文件也被删除了

#### 2.6.2. 判断删除
__语法：__drop table if exists 表1，判断表是不是存在，使用这个可以避免表不存在时执行报错问题

```SQL
drop table if exists my_student1
```

### 2.7. 新增表列字段
__新增列的语法：__alter table 表名 add 字段名 数据类型 [列属性] [位置]
> 列属性和位置都是可选参数，字段名和数据类型是必选参数

```SQL
-- 新增一列password并设置默认值为123456
alter table my_student add column password varchar(50) not null default ('123456');
-- 新增一列id并设置为主键和自增长，column关键字可以省略不写
alter table my_student add id int primary key auto_increment；
```

### 2.8. 修改表列字段
__修改列信息的语法：__alter table 表名 modify 字段名 数据类型 [列属性] [位置]
> 修改主要有数据类型、列属性和位置，使用方式和新增基本一致，只要在字段后面添加即可

```SQL
-- 修改数据类型，将原来的varchar(20)修改为varchar(50)
alter table my_student modify name varchar(50)
-- 修改字段位置，将birthday字段由date类型修改为datetime类型，并移动到name字段之后
alter table my_student modify birthday datetime after name;
-- 将id列修改为主键并自增长
alter table my_student modify id int primary key auto_increment;
-- 修改name字段的编码格式
alter table my_student modify name varchar(20) charset utf8mb4 not null
-- 修改reportTime字段的默认时间，需要注意使用()包裹CURRENT_DATE
alter table my_schoolReport modify reportTime date not null default (CURRENT_DATE) comment '成绩日期';
```
__修改列名称的语法：__alter table 表名 change 原字段名 新字段名 数据类型 [列属性] [位置]
> 变更字段名称语句也可以对数据类型、列属性和位置信息进行修改，使用方式和modify一样

```SQL
-- 将gender字段修改为sex并修改新的数据类型为char(2)
alter table my_student change gender sex char(2)；
```
> 注意：① 使用修改语句时，如果原有的属性在修改语句后没有了设置，执行之后会导致原属性设置丢失，因此修改时要注意原来的设置都要放在修改语句中，② 位置属性需要放置在类型之后，如果既要修改位置属性和其它字段属性建议分成两条语句执行，放一条语句中执行可能会导致部分属性不生效

### 2.9. 删除表列字段
__语法：__alter table 表名 drop 字段名

```SQL
-- 删除my_student表中的sex列
alter table my_student drop sex;
```
> 如果表中已存在数据，删除字段会清空该字段的所有数据，不可逆