MySQL库的操作

目录

一、创建数据库

二、字符集&&校验规则

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

2.2 查看数据库支持的字符集

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

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

三、数据库操作

3.1 查看数据库

3.2 显示创建语句

3.3 修改数据库

3.4 删除数据库

3.5 数据库备份与恢复

3.6 表备份与恢复

3.7 查看连接情况

---

一、创建数据库

 创建数据库

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

二、字符集&&校验规则

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

查看系统默认的字符集

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

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

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

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

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

2.2 查看数据库支持的字符集

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

注意： 字符集可以控制用什么语言，如utf8就可使用中文

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

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

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

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

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

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

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

校验规则对数据库的影响

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

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

数据库采用utf8_general_ci校验规则

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

在该数据库中创建一个简单的user表，由于创建未指定表的编码格式和校验规则，因此user表将继承当前数据库的编码格式和校验规则

向表中插入一些数据

通过select语句查看插入表中的数据

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

数据库采用utf8_bin校验规则

创建数据库时指定数据库的校验规则为utf8_bin，数据库的编码格式默认为utf8

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

向表中插入与之前相同的数据

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

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

三、数据库操作

3.1 查看数据库

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

3.2 显示创建语句

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

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

3.3 修改数据库

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

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

举例：将数据库的字符集改为gbk，并将数据库的校验规则改为gbk_bin

3.4 删除数据库

DROP DATABASE [IF EXISTS] db_name;

删除数据库后，该数据库下的所有表都会被级联删除，因此不要随意删除数据库，会导致数据丢失

3.5 数据库备份与恢复

数据备份

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

下面演示数据库备份，创建一个数据库，并在该数据库中创建两个表 

在student和teacher表中分别插入两条记录

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

打开back.sql文件即可看到，文件中的内容就是对于该数据库执行的各种SQL，包括创建数据库、创建表、插入数据等

数据恢复

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

为了演示数据库恢复，先将db6数据库删除

MySQL服务器执行如下SQL语句即可对数据库进行恢复

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

同时该数据库下的两张表，以及表中的数据也都恢复了

3.6 表备份与恢复

表备份 

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

假设在db6数据库中仅想备份student表，这时就可以在命令行中执行如下命令，并指定将备份后产生的文件存放在当前目录下

这时历史上与student表相关的SQL语句，就会被保存到备份文件中

表恢复

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

source 表备份存储的文件路径

执行表恢复SQL语句

当执行完毕后，该数据库下就恢复出了student表，并且表中的数据也恢复了

3.7 查看连接情况

使用 show processlist 即可查看当前连接MySQL的用户

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

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