库操作

1.创建数据库

create database if not exists zx charset=utf8 collate utf8_general_ci;

• if not exists ：可写可不写
• charset：指定数据库采用的字符集
• collation：指定数据库字符集的校验规则

2.字符集和校验规则

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

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set, 1 warning (0.35 sec)

mysql> show variables like 'collation_database';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

utf8_general_ci :不区分大小写
utf8_bin:区分大小写

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

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

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

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

不区分大小写排序以及结果：

mysql> use test1; 
mysql> select * from person order by name; 
+------+
| name | 
+------+ 
| a |
| A |
| b |
| B |
+------+

区分大小写排序以及结果：

mysql> use test1; 
mysql> select * from person order by name; 
+------+
| name | 
+------+ 
| A |
| B |
| a |
| b |
+------+

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

show charset;

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

 show collation;

3.操作数据库

3.1查看数据库

show databases;

3.2显示创建语句

mysql> show create database mytest;

3.3修改数据库（指的是修改数据库的字符集）

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

mysql> show create database zx;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| zx       | CREATE DATABASE `zx` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

3.4数据库的删除

mysql> drop database if exists zx;
Query OK, 1 row affected (0.29 sec)

3.5数据库的备份和恢复
3.5.1备份整个数据库

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

3.5.2备份数据库中的一张表

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

3.5.3备份数据库中的多张表

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

3.5.4还原

mysql> source D:/mysql-5.7.22/mytest.sql;

3.6查看连接情况

mysql> show processlist;
+----+------+----------------+------+---------+------+----------+------------------+
| Id | User | Host           | db   | Command | Time | State    | Info             |
+----+------+----------------+------+---------+------+----------+------------------+
| 12 | root | localhost:9680 | NULL | Query   |    0 | starting | show processlist |
+----+------+----------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

可以告诉我们有哪些用户连接到我们的MySQL;