MySQL--数据库的操作

目录

MySQL--数据库的操作

创建数据库

创建数据库案例

字符集和校验规则

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

查看数据库支持的字符集

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

校验规则对数据库的影响

校验规则对数据库的影响

操纵数据库

查看所有数据库

显示创建语句

修改数据库

数据库删除

数据库的备份和恢复

注意事项

查看连接情况：

MySQL库的操作

创建数据库

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

说明一下：

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

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

创建数据库案例

• 创建名为 db1 的数据库

create database db1;

当我们创建数据库没有指定字符集和校验规则时，系统使用MySQL配置文件里的，其中默认字符集：utf8，校验规则是：utf8_ general_ ci

• 创建一个使用utf8字符集的 db2 数据库

create database db1 charset=utf8;//注这里校验规则还是数据库配置文件里的
create database db1 character set utf8;

• 创建一个使用utf字符集，并带校对规则的 db3 数据库。

create database db1 charset=utf8 collate utf8_general_ci;

字符集和校验规则

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

show variables like 'character_set_database';//查看字符集
show variables like 'collation_database';    //查看校验集

查看数据库支持的字符集

show charset;

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

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

show collation;

校验规则对数据库的影响

这里我们首先要知道字符集编码格式与校验集规则的是什么？区别是什么？

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

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

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

校验规则对数据库的影响

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

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

这里我们直接演示当采用不同校验规则时会有什么影响：一个数据库的校验集采用utf8_general_ci,另一个数据库的校验集采用utf8_bin.(第一个不区分大小写，第二个区分大小写)。

在这两个数据库创建一个person表，只有一个name属性，类型未varchar(20),同时插入数据（A a  B b C c）.

这里我们在这两个数据库里对person表查找name=a。

这里我们可以发现在user1数据库中查找是区分大小写，而user2数据库中查找是不区分大小写的。

这里我们可以发现在使用不同检验集是，会导致查找的结果不同。

操纵数据库

查看所有数据库

show databases;

显示创建语句

show create database 数据库名;

这里我们拿上面的user1为例：

• MySQL 建议我们关键字使用大写，但是不是必须的。
• 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
• /*!40100 default.... */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

修改数据库

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

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

举例：将user1数据库的字符集改成gbk

数据库删除

DROP DATABASE [IF EXISTS] db_ name;

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

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

这里我们将数据库user2删除：

数据库的备份和恢复

备份语法：

mysqldump -P 端口号（这里默认3306） -u 应户名 -p 密码 -B 数据库名 > 数据库备份存储的文件路径

回复语法：

 source 备份数据库路径;

这里我们把user1数据库备份还原一下文件名叫user1.sql：

备份

然后再打开user1.sql查看里面的内容：

这里我们可以发现里面记录了我们对于数据库的各种操作，如：创建数据库，添加表等。

然后我们再把数据库user1删除：

还原

source > /MySQL/user1.sql;

这里数据库user1被恢复回来了，同时person表也恢复了

注意事项

同时备份多个数据库：

mysqldump -P 端口号 -u 用户名 -p 密码 数据库名1 数据库名2 ...> 备份路径;

mysqldump -P 端口号 -u 用户名 -p 密码 数据库名 表名1 表名2 > 备份路径;

source 表备份存储的文件路径

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。

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