【MySQL】数据库的基本操作

1. 创建数据库

1.1 创建数据库的语句

创建数据库的基本语句如下：

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

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

1.2 创建一个数据库

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

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

如果没有对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。

1.3 查看字符串与校验规则

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

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

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

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

使用show charset语句可以查看数据库支持的字符集。

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

1.4 校验规则对数据库的影响

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

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

比如我们村数据的时候是按照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表将继承当前数据库的编码格式和校验规则。

向表中插入一些数据。

此时查看表中 name=‘a’ 的记录时会将 a 和 A 都筛选出来，根本原因就是utf8_general_ci校验规则在进行数据比对时是不区分大小写的。

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

创建数据库时指定数据库的校验规则为utf8_bin，数据库的编码格式默认为utf8。
在该数据库中同样创建和刚才一样的person表，该person表会将继承当前数据库的编码格式和校验规则。

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

进行数据筛选。可以看到utf8_bin是区分大小写的。

2. 删除数据库

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

增删数据库的本质

增删数据库的本质就是在 /var/lib/mysql 路径下创建和删除目录。但是在这个路径下手动地进行创建和删除目录是极度不合理的，我们决对不能这么干，只能在mysql中进行创建和删除。

3. 查看数据库

查看数据库

使用show databases语句可以查看系统中所有数据库。

显示创建语句

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

说明：

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

确认自己当前在哪个数据库

使用select database() 语句可确认自己当前在哪个数据库里。

4. 修改数据库

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

比如将数据库的字符集该为gbk，并将数据库的规则改为gbk_bin。

5. 备份与恢复

5.1 数据库的备份与恢复

数据库的备份

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


然后在student表中和teacher表中2分别插入两条记录。

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

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

数据库恢复

使用source相关语句可将指定数据库恢复。

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

这时让MySQL服务器执行下面语句即可对数据库进行恢复。

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

5.2 表的备份与恢复

表的备份

使用下面的语句可对指定表进行备份。

如果也要备份刚才创建的student表和teacher表，可以执行以下语句，并将备份后产生的文件存放到当前目录下，这样就会将历史上的与这两个表相关的语句放到文件当中。

表的恢复

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

在数据库中使用source相关语句对指定表进行恢复。

为了演示表的恢复，我们先将刚才的数据库删除。

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


查看恢复的表的数据。

6. 查看数据库的连接情况

这个语句可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登录的，很有可能你的数据库被人入侵了。

如果以后发现自己的数据库访问起来比较慢，可以用这个命令来查看一些数据库的连接情况。