MySQL：库表操作

---

库操作

查看

• 查看存在哪些数据库：

show databases;

示例：

• 查看自己当前处于哪一个数据库：

select database();

示例：

此处由于我不处于任何一个数据库中，此处值为NULL

• 查看当前有哪些用户连接到了MySQL：

show processlist;

示例：

---

创建

• 创建一个数据库

语法：

create database [if not exists] 数据库名;

[if not exists]：如果数据库存在，就不创建该数据库。

示例：

此时就创建了blog_db数据库，可以通过show databases查看。

一个数据库，对操作系统而言，本质就是一个目录，在我的ubuntu主机下，数据库存储在了/var/lib/mysql目录下，这个路径会因为MySQL版本以及操作系统而不同。

此时我的/var/lib/mysql目录下，也同步出现了blog_db目录：

---

字符编码集

在MySQL中，数据库有两种字符编码集合：

字符集：字符存储时，采用的编码方式
校验集：字符读取时，采用的编码方式

查看系统支持的字符集：

show charset;

示例：

• 查看系统支持的校验集：

show collation;

示例：

此时系统就列出了支持的各种集合，比如utf8。

可以在创建数据库时，指定使用的字符集与校验集

• 指定字符集：

create database [if not exists] 数据库名 charset=xxx;
create database [if not exists] 数据库名 character set xxx;

以上两种方式，都可以指定字符集为xxx

• 指定校验集：

create database [if not exists] 数据库名 collate xxx;

示例：

执行create database db_1 charset=utf8 collate utf8_general_ci：

该指令创建了数据库db_1，并指定字符集为utf8，校验集为utf8_general_ci。

在数据库对应的目录中，会存在一个文件dp.opt，其内部存储了数据库对应的校验集与字符集：

当然，如果你不指定字符集和校验集，此时会使用系统默认的。

查看系统默认的字符集：

show variables like 'character_set_database';

示例：

即默认的字符集为utf8。

• 查看系统默认的校验集：

show variables like 'collation_database';

示例：

即默认的校验集为utf8_general_ci。

---

删除

• 删除数据库：

drop database [if exists] 数据库名;

示例：

---

修改

修改数据，主要是指修改数据库使用的字符集和校验集。

• 修改字符集：

alter database 数据库名 charset=xxx;

示例：

• 修改校验集：

alter database 数据库名 collate xxx;

示例：

---

备份

• 拷贝数据库指令

mysqldump -P 端口号-u 用户名 -p 密码 -B 数据库名 > 数据库备份存储的文件路径

注意：该指令要在bash中执行，而不是数据库中。

比如备份bit_index数据库：

mysqldump -B bit_index > ~/test/bit_index.sql

上述指令指定了一个名为bit_index 数据库，将其保存在路径~/test/bit_index.sql。

此时在对应路径下，就产生了对应.sql文件：

通过vim查看一下sql内部的数据：

可以发现：.sql文件并不是简单的拷贝一份数据库，而是保留了整个数据库的SQL语句。

• 还原数据库

source 路径.sql;

该指令要在MySQL内部执行，而不是命令行。

---

表操作

接下来学习如何操作一个数据库内部的表。

• 进入/切换 数据库：

use 数据库名;

示例：

如图，我创建了一个名为box_he的数据库，随后执行use box_he进入了该数据库，通过select database()查看当前所处的数据库，可以看到当前确实处于box_he中。

---

创建

• 创建一个表：

create table [if not exists] 表名 (field1 datatype,field2 datatype,field3 datatype
) character set 字符集 collate 校验集 engine 存储引擎;

• [if not exists] ：可省略，如果不存在这个表，才创建
• field：列名
• datatype：该列的数据类型
• character set：指定字符集，若没有指定，与数据库使用的字符集一致
  • 也可写为：charset=字符集
• collate：指定校验集，若没有指定，与数据库使用的校验集一致
• engine：指定存储引擎
  • 也可写为：engine=存储引擎

注意事项：

1. 每一个列后面都有一个逗号，但是最后一个没有
2. 整个语句末尾有分号

示例：

我创建了一个表t1，内部有id列，类型是int；name列，类型是varchar(20)；字符集为utf8；校验集为utf8_general_ci；存储引擎是MyIsam。

此时在/var/lib/mysql下的boe_he目录下，出现了三个文件：t1.frm，t1.MYD，t1.MYI这三个文件共同维护了t1这张表。

可见：数据库中的表的本质，就是多个文件！

而不同存储引擎，对一张表的存储形式不同，比如创建一个同样结构的表，使用InnoDB存储引擎：

路径/var/lib/mysql/box_he下：

可以看到，同样的表结构InnoDB，存储t2只使用了两个文件t2.frm和t2.ibd。关于存储引擎的具体内容会在索引部分讲解。现在只需要了解：不同存储引擎，对表的存储存在差别即可。

---

查看

• 查看当前数据库下有哪些表：

show tables;

示例：

此处的t1，t2也就是刚刚创建的两个表。

• 查看表的结构：

desc 表名;

示例：

各个字段含义如下：

• Field：列名
• Type：列的数据类型
• Null：该列是否允许为空
• Key：索引类型
• Default：默认值
• Extra：扩充

• 查看建表时的语句：

show create table 表名;

示例：

执行语句后，展示了创建表t1时的语句，直接执行该语句，格式比较混乱，一般来说建议将末尾的分号;改为\G：

show create table 表名 \G

这个\G可以将数据以更加直观的方式展示出来：

建表的语句与我们输入的语句略有出入，这是因为MySQL会对输入的语句进行优化，比如说MySQL建议将关键字全部大写，我们就算是以小写的语句创建表格，底层还是会被转化为大写。

不过我个人认为小写的语句更加直观，可以很快分辨出是哪一个单词，而且因为有语句优化，最后存储时还是大写，所以我使用小写。

再比如每一列后面都有一个DEFAULT NULL，表示默认值为NULL，这个也是MySQL的优化，如果我们不指定默认值，就会自动设置默认值为NULL。具体如何指定默认值，会在《MySQL：表的约束》博客中讲解。

---

删除

• 删除一个表：

drop table [if exists] 表名 [, 表名, ...];

• [if exists]：如果表存在，才删除它
• [, 表名, ...]：可以一次性删除多个表，表名之间用逗号,分隔

示例：

当我把t1和t2删除后，此时执行show tables，就显示Empty set即一个空的数据库。

---

修改

由于刚刚把t1和t2都删掉了，此处我重新创建这两个表，方便展示后续操作：

• 修改表名：

alter table 旧表名 rename [to] 新表名;

• rename [to]：此处的to可省略，效果一样

示例：

此处我将t1和t2分别改名为table_1和table_2。

• 插入新的列

alter table 表名 add 列名 类型 comment 描述 after xxx;

• comment：相当于备注，用于表示这一列数据的含义
• after：指定新增的列插入到哪一列后面，如果不指定，默认插入到最后一列

示例：

语句alter table table_1 add age int comment '年龄' after id的作用，就是在id列后面，插入age列，类型是int。

对比前后的desc table_1，确实多出了age列。

• 删除列

alter table 表名 drop 列名;

示例：

以上指令，删除了age列

• 修改列

alter table 表名 change 旧列名 新列名 类型;

示例：

我把id列改为stu_id列，并把类型从int改为了smallint。由于我把数据类型变小了，此时有可能会发生数据精度丢失，一般不建议这样操作。

---