03 库的操作

目录

1. 创建
2. 查看
3. 修改
4. 删除
5. 备份和恢复
6. 查看连接情况

---

1. 创建

语法

CRATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] …]

create_specification:
 CHARACTER SET charset_name
 CPLLATE collation_name

说明：
大写的标识关键字
[]是可选项
CHARACTER SET：指定数据库采用的字符集
COLLATE：指定数据库字符集的校验规则
create_specification：采用的编码规则

创建数据库时有两个编码集，分为数据库编码集和校验集，编码集市数据库存储数据时使用的，椒盐基支持数据库比较使用的编码，本质是一种读取数据库中采用的编码格式
数据库无论任何操作，都必须保证操作和存储是编码一致的

案例

• 先进入数据库存放文件夹

cd var/lib.mysql

• 创建名为db1的数据库

create database db1;

说明：创建数据库没有指定字符集和校验规则时，系统默认字符集：utf8，校验规则是：utf8_general_ci

创建数据库就是在上面的文件夹里创建文件夹

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

create database db2 charset=utf8;
create database db2 character set utf-8; //另一种写法

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

create database db3 chareset=utf8 collate utf8_general_ci;

• IF NOT EXISTS 选项，表示不存在的话创建
• 创建一个db3，带这个选项

create database if not exists db3;

如果数据库已经存在，这个操作不会执行，可以避免因对象已存在导致的错误。

字符集和校验规则

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

show variables like ‘character_set_database’;
show variables like ‘collation_database’;

查看默认所有椒盐基

show variables like ‘collation_%’;

查看数据库支持的字符集

show charset;

字符集主要控制用什么语言，比如utf8就可以使用中文

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

show collation;

校验规则对数据库影响

数据库的字符集和校验影响着创建的表的规则，字符集和校验集，存储和读取必须一致
在数据库中有配置文件db.opt查看

cat db.opt;

• 不区分大小写

创建一个数据库，校验规则用utf8_general_ci[不区分大小写]，下面的操作是创建数据库，创建person表，插入四条数据

create database test1 collate utf8_general_ci;
use test1;
create table person (name varchar(20));
insert into person values (‘a’);
insert into person values (‘A’);
insert into person values (‘b’);
insert into person values (‘B’);

• 区分大小写

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

create database test2 collate utf8_bin;
use test2;
create table person (name varchar(20));
insert into person values (‘a’);
insert into person values (‘A’);
insert into person values (‘b’);
insert into person values (‘B’);

• 查询

不区分大小写的查询及结果

use test1;
select * from person where name=‘a’;

区分大小写的查询及结果

use test2;
select * from person where name=‘a’;

对于不区分大小写的，A也看做是a，所以查询到两个

• 结果排序

不区分大小写排序结果

use test1;
select * from person order by name;

区分大小写排序结果

use test2;
select * from person order by name;

区分大小写，就会以ascii码排序，大写比小写的值小

2. 查看

查看所有数据库

show databases;

显示创建语句

show create database 数据库名;

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

3. 修改

说明：对数据库的修改主要指修改数据库的字符集，校验规则

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]…]

alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

实例

将test2数据库集改为gbk

alter database test2 charset=gbk;

当修改了字符集后，校验集会自动和它匹配

4. 删除

DROP DATABASE [IF EXISTS] db_name;

执行完删除之后的结果：

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

注意，不要随意删除数据库，会造成很多上层的工程需要修改

5. 备份和恢复

备份

备份有最直接的方法，将数据库文件拷贝，但是这样遇到版本不同的sql可能会出现错误，mysql提供了备份的方法

语法，需要退出数据库在命名行输入：

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

示例，将test2库备份（退出连接）

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

打开备份文件，里面都是数据库操作的语句，建库，建表，导入数据的语句都在里面

还原

mysql> source 数据库路径;

注意

如果备份的不是整个数据库，而是其中的表，怎么做

mysqldump -u root -p 数据库名 表名1 表名2 > test2.sql

同时备份多个数据库

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

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

mysqldump -P3306 -u root -p mytest > mytest.sql

这时恢复需要先创建数据库

6. 查看连接情况

语法：

show processlist；

可以告诉我们哪些用户连接了，可以查出非正常连接，如果数据库比较慢时，可以用这个指令看看连接情况