MySQL之库的操作

1.库的操作
1.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：指定数据库字符集的校验规则（校验规则不区分大小写：utf8_general_ci）

1.2创建一个数据库案例

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

create database db1;

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

create database db2 charset=utf8;

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

create database db3 charset=utf8 collate utf8_general_ci;

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

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

1.3.2查看数据库支持的字符集
字符集主要是用来控制使用的是什么语言，如utf8就可以使用中文。

show charset;

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

show collation;

1.3.4校验规则对数据库的影响
1)不区分大小写
创建一个数据库，校验规则使用utf8_general_ci

create database test1 collate utf8_general_ci;//创建不区分大小写的数据库
mysql> use test1;//使用该数据库
mysql> create table person(name varchar(20));//在test1数据库中建一张person表
mysql> insert into person values('a');//插入4条记录
mysql> insert into person values('A');
mysql> insert into person values('b');
mysql> insert into person values('B');

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

mysql> create database test2 collate utf8_bin;//创建一个区分大小写的test2数据库
mysql> use test2;//使用test2数据库
mysql> create table person(name varchar(20));//在test2中建一张表person
mysql> insert into person values('a');//在person表中插入4行记录
mysql> insert into person values('A');
mysql> insert into person values('b');
mysql> insert into person values('B');

3)进行查询

mysql> use test1
Database changed
mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.01 sec)

mysql> use test2;
Database changed
mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

1.4对数据库的操作
1.4.1查看数据库

show database;

1.4.2显示创建数据库的语句

show create database 数据库名称;

mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

1.4.3修改数据库
对数据库的修改主要是指修改数据库的字符集、校验规则。
例如：将数据库test1的数据字符集修改成gbk.

mysql> alter database test1 charset=gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test1;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+

1.4.4数据库删除

DROP DATABASE [IF EXISTS] db_name;

执行删除之后的结果：数据库内部看不到对应的数据库；对应的数据库中的所有文件夹、数据等全部都会被删除。

1.4.5数据库的备份和恢复
1)备份：断开连接
I.整个数据库，结构，表都导出来：

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

II.导出指定的表：

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

III.备份多个数据库：

mysqldump -P3307 -uroot -p -B 数据库1  数据库2 ......>数据库备份存储文件路径

2)还原：登录数据库;与备份对应
I.source D:/mytest.sql;

1.4.6查看连接情况

show processlist;
+----+------+----------------+-------+---------+------+----------+------------------+
| Id | User | Host           | db    | Command | Time | State    | Info             |
+----+------+----------------+-------+---------+------+----------+------------------+
|  4 | root | localhost:4416 | test2 | Query   |    0 | starting | show processlist |
+----+------+----------------+-------+---------+------+----------+------------------+

通过查看连接情况可以得知有哪些用户连接到我们的MySQL，避免异常登录。