【MySQL】MySQL库的操作

一、创建数据库

创建数据库的语法如下：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,create_specification] ...]

create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

解释说明：

• SQL中大写的表示关键字，[ ]中代表的是可选项。
• IF NOT EXISTS 表示不存在就创建。
• CHARSET用于指定数据库所采用的编码格式。
• COLLATE用于指定数据库所采用的校验规则。

注意： 如果创建数据库时未指明数据库的编码格式或校验规则，则默认使用MySQL配置文件中对应的编码格式或校验规则。

创建数据库案例

• 采用默认的编码格式和校验规则创建数据库
  创建数据库时将所有的可选项都去掉即可

create database db1;

• 创建指定utf8编码格式创建数据库

create database db2 charset=utf8;

其中SQL中的charset=utf8，也可以写成character set=utf8或character set utf8。

• 指定utf8编码格式和utf8_general_ci校验规则创建数据库

create database db3 charset=utf8 collate utf8_general_ci;

其中SQL中的collate utf8_general_ci，也可以写成collate=utf8_general_ci

字符集和校验规则

---

• 字符集：是一套符号的编码方式的集合，例如我们常见的ASCII字符集、GBK字符集、UTF8字符集等。其中UTF8是一种全球统一的编码方式，所以我们一般采用UTF8字符集。

• 校验规则：是在字符集内用于比较字符的一套规则，一般来说我们按照什么格式字符集存入数据，就按什么字符集对应的校验规则进行读取，这样才能保证数据的正确性。

---

• 查看系统默认的字符集

可以通过查看MySQL系统变量variables中的character_set_database，可以得知系统默认的字符集。如下：

show variables like 'character_set_database';

• 查看系统默认的校验规则

通过查看MySQL系统变量variables中的collation_database，可以得知系统默认的字符集校验规则。如下：

show variables like 'collation_database';

• 查看数据库支持的全部字符集

使用show charset可以查看数据库支持的字符集。如下：

show charset;

如果你细心，且对utf8编码集了解过的话，你可能会产生下面的疑惑。

• 为什么MySQL里面的utf8是3个字节，正常来说utf8是1~4个字节啊？
• 答案是：MySQL 中的 utf8 并不是真正的 UTF-8 编码！utf8mb4 才是真正的UTF-8！
  
  如果你对这里的原因比较感兴趣可以看一看这篇文章：面试官问：为什么 MySQL 中的 utf8 并不是真正的 UTF-8 编码？

• 查看数据库支持的全部的字符集校验规则

使用show collation可以查看数据库支持的字符集校验规则。如下：

show collation;

校验规则对数据库的影响

现在我们已经知道了，为了保证读取的数据和存储的数据是一致的，因此字符集编码格式和校验规则必须是对应的。但实际同一中字符集编码格式一般会有多种可选择的校验规则，比如utf8编码格式的校验规则有utf8_general_ci、utf8_bin、utf8_unicode_ci等校验规则。

使用不同的校验规则操作数据库中的数据可能会得到不同的结果，比如utf8_general_ci校验规则在比对数据时是不区分大小写的，而utf8_bin校验规则在对比数据时则是区分大小写的。

• 不区分大小写
  在刚才我们创建的db3数据库时使用的是utf8_general_ci的校验规则，我们选择使用这个数据库use db3，在该数据库中同样创建一张person表，再不作设置的情况下该person表会将继承当前数据库的编码格式和校验规则。

use db3;
create table person(name varchar(20));

这时向表中插入一些数据。如下：

insert into person values ('A');
insert into person values ('a');
insert into person values ('B');
insert into person values ('b');
insert into person values ('c');
insert into person values ('D');

通过select语句可以查看插入表中的数据。如下：

select * from person;

这时指定查看表中name='A'的记录时会将A和a一并筛选出来，根本原因就是utf8_general_ci校验规则在进行数据比对时是不区分大小写的。如下：

select * from person where name='A';

• 区分大小写

操作与前面类似

create database db4 charset=utf8 collate=utf8_bin;
show databases;
use db4;
create table person(name varchar(20));

这时向表中插入一些数据。如下：

insert into person values ('A');
insert into person values ('a');
insert into person values ('B');
insert into person values ('b');
insert into person values ('c');
insert into person values ('D');

这时指定查看表中name='A'的记录时会将A和a区分出来，根本原因就是utf8_bin校验规则在进行数据比对时是不区分大小写的。如下：

select * from person where name='A';

二、操纵数据库

1、查看数据库

使用show databases可以查看系统中所有的数据库。如下：

show databases;

2、查看当前正在使用的数据库

select database();

• database()是一个MySQL函数

3、使用数据库

use 数据库名;

4、显示创建语句

使用show create database 数据库名可以查看对应数据库的在创建时的语句。如下：

show create database db3;

说明一下：

• MySQL建议SQL中的关键字使用大写，但不是必须的。
• 数据库的名字加上反引号，是为了防止使用的数据库名与关键字冲突。
• /*!40100 DEFAULT CHARACTER SET utf8 */不是注释，它表示当前MySQL版本如果大于4.10，则执行后面的SQL语句。

5、数据库删除

删除数据库的SQL语法如下：

DROP DATABASE [IF EXISTS] db_name;

• IF EXISTS，表示存在就删除

执行删除之后的结果:

• show databases;看不到对应的数据库了。
• 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。

注意：不要随意删除数据库

drop database if exists db4;
show databases;

6、数据库的修改

修改数据库的SQL语法如下：

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

• 对数据库的修改主要指的是修改数据库的「字符集」或「校验规则」。

例如我们可以进入数据库存储目录/var/lib/mysql查看db3的「字符集」和「校验规则」。

现在我们进行对db3一些修改，字符集改为gbk，并将数据库的校验规则改为gbk_chinese_ci。

alter database db3 charset=gbk collate=gbk_chinese_ci;

此时，我们再去查看db3下的db.opt配置文件：

7、备份和恢复

在我们安装数据库mysql时，同时会给我们安装一个mysqldump的工具，通过这个工具我们可以进行数据库的备份。

• 数据库备份

这个工具的使用语法是：

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

我们打开这个文件：

vim back.sql

可以看到，文件中的内容实际就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句。如下：

• 数据库恢复

恢复的命令是：

source 数据库备份存储的文件路径

我们先把数据库db3给删除。

进行恢复：

我们可以看到数据库和里面的表都恢复了。

• 表的备份
  使用如下命令即可对指定表进行备份：

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

• 表的恢复

这时我们可以创建一个空的数据库并在该数据库中执行如下命令即可对表进行恢复。

create database temp;
show databases;
use temp;

source /root/MySQL/back2.sql

当备份文件中的SQL语句执行完毕后，该数据库下就恢复出了person表，并且表当中的数据也都恢复出来了。

select database();
show tables;

总结：如果备份一个数据库时，没有带上-B参数(备份数据库)， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

8、查看连接情况

使用show processlistSQL语句即可查看当前连接MySQL的用户。比如：

show processlist;

说明一下：

• Id列：一个标识，可以在MySQL中通过kill id杀死指定id的线程。
• User列：显示当前用户，如果不是root，这个命令就只显示你权限范围内的SQL语句。
• Host列：显示这个语句是从哪个IP的哪个端口上发出的，可用来追踪出问题语句的用户。
• db列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为NULL。
• Command列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
• Time列：表示该线程处于当前状态的时间，单位是秒。
• State列：显示使用当前连接的SQL语句的状态。
• Info列：一般记录的是线程执行的语句，默认只显示前100个字符，如果要看全部信息，需要使用show full processlist。

show processlist可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登录的，那么很有可能你的数据库被人入侵了，以后如果发现自己的数据库比较慢时，可以用这个SQL来查看数据库连接情况。