MySQL库的操作(2)

前言

  有了相应的数据库基础认识后，接下来我们就来认识一下库的相关操作吧！

一、创建数据库

创建数据库

创建数据库的SQL如下：

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

说明一下：

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

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

创建数据库案例

采用默认的编码格式和校验规则创建数据库

  创建数据库时不指明数据库的编码格式和校验规则即可。如下：

  注意： 如果没有对MySQL的配置文件进行过修改，则默认的编码格式是utf8，默认的校验规则是utf8_general_ci。

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

  创建数据库时通过charset指明数据库的编码格式即可。如下：

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

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

  创建数据库时通过charset和collate分别指明数据库的编码格式和校验规则即可。如下：

  注意： SQL中的collate=utf8_general_ci，也可以写成collate utf8_general_ci。

二、字符集和校验规则

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

查看系统默认的字符集

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

  注意： 如果是在指定数据库下使用该SQL，则查看的是该数据库对应的字符集。

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

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

  注意： 如果是在指定数据库下使用该SQL，则查看的是该数据库对应的字符集校验规则。

查看数据库支持的字符集

查看数据库支持的字符集

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

  说明一下： 字符集主要是控制用什么语言，比如utf8就可以使用中文。

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

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

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

校验规则对数据库的影响

字符集编码格式和字符集校验规则的区别

• 字符集编码格式指的是在存储数据时各个字符的底层编码，用于指定数据的存储格式。
• 字符集校验规则是在字符集内用于比较字符的一套规则，用于对数据进行比对。

  比如我们存储数据的时候是按照utf8的格式进行存储的，那么将来在对数据做比对时也必须按照utf8的格式进行比对，因为“存数据”和“取数据”的方式必须保持一致。

  注意： 在对数据库当中的数据进行增删查改时，不可避免的需要进行数据的比对，因为在对数据做增删查改之前，都需要先通过比对的方式找到目标数据。

校验规则对数据库的影响

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

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

操作数据库时采用 utf8_general_ci 校验规则

  创建数据库时指定数据库的校验规则为utf8_general_ci，数据库的编码格式默认为utf8。如下：

  在该数据库中创建 person表 ，该 person表 会将继承当前数据库的编码格式和校验规则。如下：

  这时向表中插入刚才相同的数据。如下：

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

  这时指定查看表中 name=‘alice’ 的记录时会将 alice 和 Alice 都筛选出来

  所以说 utf8_general_ci 校验规则在进行数据比对时是不区分大小写的
  创建数据库时指定数据库的校验规则为utf8_bin，数据库的编码格式默认为utf8。如下：

  在该数据库中同样创建和刚才一样的person表，该person表会将继承当前数据库的编码格式和校验规则。如下：

  这时向表中插入刚才相同的数据。如下：

  通过select语句可以看到表中的数据与之前相同。如下：

  但这时指定查看表中 name=‘alice’ 的记录时只会将 alice 筛选出来，根本原因就是 utf8_bin 校验规则在进行数据比对时是区分大小写的。如下：

三、操纵数据库

查看数据库

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

显示创建语句

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

说明一下：

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

修改数据库

  修改数据库的语句如下：

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

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

  比如将数据库的字符集改为 gbk ，并将数据库的校验规则改为 gbk_bin 。如下：

删除数据库

  删除数据库的SQL如下：

DROP DATABASE [IF EXISTS] db_name;

  删除数据库后该数据库对应的文件夹就被删除了。比如：

  并且删除数据库后，该数据库下的所有表也都会被级联删除，因此不要随意删除数据库。

删库跑路！

备份与恢复

数据库的备份与恢复

  使用如下命令即可对指定数据库进行备份：

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

  为了演示数据库备份，下面我们创建一个数据库，并在该数据库中创建两个表。如下：

  在student表中插入两条记录。如下：

  在teacher表中也插入两条记录。如下：

  这时在命令行中执行如下命令即可将该数据库进行备份，并指定将备份后产生的文件存放在当前目录下。如下：

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

数据库恢复

  使用以下指令就能对数据库进行恢复：

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

  为了演示数据库恢复，我们先将刚才创建的数据库删除。如下：

  这时让MySQL服务器执行如下命令即可对数据库进行恢复。如下：

  实际恢复数据库的时候就是按顺序执行数据库备份文件中的SQL语句，执行完毕后数据库也就恢复出来了。如下：

  同时该数据库下的两张表，以及表当中的数据也都恢复出来了。如下：

表的备份和恢复

  使用如下命令可以对表进行备份

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

  比如说我们现在想要备份 student 和 teacher 表

  就可以在命令行中执行如下命令，并指定将备份后产生的文件存放在当前目录下。如下：

  这时历史上与 student 和 teacher 表相关的SQL语句，就会被保存到备份文件当中。如下：

表恢复

  表恢复之前需要先选中一个数据库，表明需要将表恢复到哪一个数据库中，为了防止恢复出来的表与该数据库中已有的表的表名重复，一般在恢复表时会选择创建一个空的数据库，然后在该数据库中进行表的恢复。

  在数据库中使用如下命令即可对指定表进行恢复：

source 表备份存储的文件路径

  为了演示表恢复，我们先将刚才的数据库删除，且再创建一个空的数据库并在该数据库中执行如下命令即可对表进行恢复。如下：

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

查看连接情况

  使用 show processlist SQL 即可查看当前连接 MySQL 的用户。比如：

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

总结

  第二篇，应该还是不难的，或者说有了之前铺垫的基础，MySQL的学习相较于 C++、Linux、数据结构比起来还是蛮轻松的！