mysql库的操作

• mysql的启动和关闭

service mysqld start;
service mysqld stop;

创建数据库

• 创建名为company1的数据库

create database company1;

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

create database company2 charset =utf8;

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

create database company3 charset=utf8 collate utf8_general_ci;

校验规则

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

create database aaa collate utf8_general_ci;

---

进行验证：   

mysql> create database aaa collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use aaa;
Database changed
mysql> create table person(name varchar(20)); 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person values('a'); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into person values('A'); 
Query OK, 1 row affected (0.00 sec)

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

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

create database bbb collate utf8_bin;

---

进行验证：

mysql> create database bbb collate utf8_bin; 
Query OK, 1 row affected (0.00 sec)

mysql> use bbb;
Database changed
mysql> create table person(name varchar(20)); 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into person values('a'); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into person values('A'); 
Query OK, 1 row affected (0.00 sec)

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

操纵数据库

• 查看数据库

show databases;

---

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bbb                |
| company2           |
| msg                |
| mysql              |
| test               |
+--------------------+
7 rows in set (0.00 sec)

• 显示数据库创建语句

show create database aaa;

---

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

• 数据库删除

drop database company2;

---

mysql> drop database company2;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bbb                |
| msg                |
| mysql              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

• 查看mysql的连接情况

show processlist;

---

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | msg  | Sleep   | 5378 |       | NULL             |
|  4 | root | localhost | bbb  | Query   |    0 | NULL  | show processlist |
|  5 | root | localhost | NULL | Sleep   |   36 |       | NULL             |
+----+------+-----------+------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

• 将 aaa库备份到桌面

[root@localhost Desktop]# mysqldump -u root -p aaa >路径

• 恢复数据库：进入mysql控制台，先删除aaa数据库，然后创建aaa数据库，进行恢复

drop database aaa;删除aaa
create database aaa;创建aaa
use aaa; 使用aaa
source 路径;恢复aaa,找对路径

• 同时备份多个数据库

[root@localhost Desktop]# mysqldump -u root -p密码 -B 数据库名1 数据库名2 … > 数据库存放路径

• 同时恢复多个数据库

mysql > source 备份的文件

---

备份一个数据库时加上-B选项，这样恢复的时候就不必创建一个空的数据库。

---

修改数据库

• 将aaa数据库字符集改成gbk

alter database aaa charset=gbk;

---

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

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

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