- 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)