MySQL库的操作

1. 创建数据库

创建数据库：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,create_specification] ...];
create_specification:
[DEFAULT] CHARACTER SET charset_name; 
[DEFAULT] COLLATE collation_name;

说明：

• 大写的表示关键字。
• []：是可选项。
• CHARACTER SET：指定数据库采用的字符集。
• COLLATE：指定数据库字符集的校验规则。

例如：

• 创建一个名为database1的数据库

create database database1;

在MySQL的数据目录里也可以看到了

删除一个名为database1的数据库

drop database database1;

所以说

• 创建数据库的本质就是在/var/lib/mysql下创建一个目录
• 删除数据库的本质就是删除目录。

2. 字符集和校验规则

2.1 认识编码集

创建数据库的时候，有两个编码集：

1. 数据库编码集——数据库未来存储数据所采用的编码集。
2. 数据库校验集——支持数据库进行比较使用的编码，本质也是一种读取数据库中数据采用的编码格式。

数据库无论对数据做任何操作，都必须保证操作和编码必须是编码一致的！

说明：当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是utf8_ general_ ci。（这是我们配置后的结果，如果在配置文件里配置的是别的字符集，那么默认字符集自然也就会改变了）

• 创建一个使用utf8字符集的database2数据库（两种方法）。

create database database2 charset=utf8;
create database database3 character set utf8;

• 创建一个使用utf字符集，并带校对规则的database3数据库（两种方法）。

create database database3 charset=utf8 collate utf8_general_ci;
create database database3 collate utf8_general_ci;

2.2 查看字符集以及校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

• 查看默认字符集：

• 查看默认校验集：

show variables like 'collation_%';

• 查看MySQL下的所有编码集：

• 查看数据库支持的所有字符集：

show charset;

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

show collation;

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

• 不区分大小写

创建一个数据库，校验规则使用utf8_ general_ ci[不区分大小写]

create database test1 collate utf8_general_ci;

使用这个数据库，也就是打开数据库，也就Linux下cd进入到目录里一样。

use test1;

在数据库里创建一个表

create table if not exists person (name varchar(20));

向表里插入一些字符

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

查看表里的数据

select * from person;

筛选表里字符为‘a’的数据

select * from person where name='a';

可以看到大小的写字母a都被筛选出来了，不区分大小写得证。

• 区分大小写

创建一个数据库，校验规则使用utf8_ bin[区分大小写]

create database test2 collate utf8_bin;

使用这个数据库，然后在数据库里创建一个表，这里用个不一样的方式：

create table if not exists person (name varchar(20));

也就是类型可以放到后面。

其余操作和表test1相似，这里直接来看结果。

可以看到只有小写的字母’a’被筛选出来了。

• 结果排序

对test1进行排序，不区分大小写排序以及结果：

select * from person order by name;

对test2进行排序，区分大小写以及排序结果：

3. 操纵数据库

3.1 查看数据库和显示创建语句

show databases;

show create database 数据库名;

如：

说明：

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

3.2 删除数据库

删除数据库

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果：

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

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

3.3 修改数据库

语法：

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

说明：

对数据库的修改主要指的是修改数据库的字符集，校验规则。一般来说不要轻易修改库的名字，因为上层依赖库都是依赖库的名字，一旦被修改会使上层用户找不到该库。

比如：

mysql> alter database test2 charset=gbk collate gbk_chinese_ci;

4. 备份和恢复数据库

4.1 备份数据库

语法，在命令行中输入：

mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

示例：

mysqldump -P3306 -u root -p -B test1 > test1.sql

打开这个备份文件看看：

其实把我们整个创建数据库，建表，导入数据的语句都装载进这个文件中了。

3.4 查看连接情况

语法：

show processlist;

如：

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

4.2 还原数据库

在MySQL里输入

source 你备份文件所在的路径

比如：

source /root/MySQL/test1.sql;

实验一下，删除后：

备份后：

成功恢复！

4.3 注意事项

• 如果备份的不是整个数据库，而是其中的一张表，那我们应该怎么做？

mysqldump -u root -p 数据库名 表名1 表名2 > 数据库备份存储的文件路径

• 同时备份多个数据库

mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

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