mysql dba管理_Mysql DBA 高级运维学习之路-mysql数据库常用管理应用

1.创建数据库

命令语法：create database 注意库名不能数字开头

(1)创建linzhongniao库

mysql> create database linzhongniao;

Query OK, 1 row affected (0.00 sec)

mysql> show databases like "l%";

+---------------+

| Database (l%) |

+---------------+

| linzhongniao |

+---------------+

1 row in set (0.00 sec)

(2)查看建库语句

可以查看到刚才的建库语句和数据库的默认字符集信息

mysql> show create database linzhongniao\G

*************************** 1. row ***************************

Database: linzhongniao

Create Database: CREATE DATABASE `linzhongniao` /*!40100 DEFAULT CHARACTER SET utf8 */ 默认字符集是latin1，在安装的时候可以指定字符集

1 row in set (0.00 sec)

(3)创建字符集为latin的库

mysql> create database linzhongniao_latin default character set latin1;

Query OK, 1 row affected (0.00 sec)

mysql> show create database linzhongniao_latin\G

*************************** 1. row ***************************

Database: linzhongniao_latin

Create Database: CREATE DATABASE `linzhongniao_latin` /*!40100 DEFAULT CHARACTER SET latin1 */

1 row in set (0.00 sec)

(4)创建一个名为linzhongniao_gbk的gbk数据库

mysql> create database linzhongniao_gbk default character set gbk;

Query OK, 1 row affected (0.00 sec)

mysql> show create database linzhongniao_gbk\G

*************************** 1. row ***************************

Database: linzhongniao_gbk

Create Database: CREATE DATABASE `linzhongniao_gbk` /*!40100 DEFAULT CHARACTER SET gbk */

1 row in set (0.00 sec)

总结：创建不同字符集格式的数据库命令

create database linzhongniao; 默认数据库配置，相当于创建拉丁字符集数据库。

create database linzhongniao_gbk DEFAULT CHARACTER SET gbk; 创建gbk字符集数据库

create database linzhongniao_utf8 DEFAULT CHARACTER SET utf8; 创建utf8字符集数据库

create database linzhongniao_latin default character set latin1; 创建latin字符集数据库

提示：

1、字符集的不一致是导致数据库中文内容乱码的罪魁祸首，根据开发的环境确定字符集(建议UTF8。

2、如果编译安装mysql的时候可以指定特定的字符集，以后创建对应字符集的数据库就不需要指定了。如下：

-DDEFAULT_CHARSET=utf8 \ #指定默认字符集

-DDEFAULT_COLLATION=utf8_general_ci \

3、编译的时候没有指定字符集或者指定了和程序不同的字符集，如何解决？

指定字符集创建数据库即可，数据库要支持创建库所需要的字符集，例如

-DEXTRA_CHARSETS=gbk,gb2312,utf8,asci或

-DEXTRA_CHARSETS=all

2.查看数据库信息

命令：show databases;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| linzhongniao |

| linzhongniao_gbk |

| linzhongniao_latin |

| mysql |

| performance_schema |

| test |

+--------------------+

7 rows in set (0.00 sec)

mysql> show databases like '%li%';

+--------------------+

| Database (%li%)|

+--------------------+

| linzhongniao |

| linzhongniao_gbk |

| linzhongniao_latin |

+--------------------+

3 rows in set (0.00 sec)

3.连接数据库

命令：use 相当于linux下的cd切换目录的命令，use是切换数据库

例如：

mysql> use linzhongniao

Database changed

4.查看当前使用库

mysql>select database();相当于linux下的pwd

+------------+

| database() |

+------------+

| linzhongniao|

+------------+

1 row in set (0.00 sec)

5.删除数据库

命令：drop database

例如：删除名为linzhongniao的数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| linzhongniao |

| linzhongniao_gbk |

| linzhongniao_latin |

| mysql |

| performance_schema |

| test |

+--------------------+

7 rows in set (0.00 sec)

mysql> drop database linzhongniao_latin;

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| linzhongniao |

| linzhongniao_gbk |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.00 sec)

不会要经常看帮助

system@ceshi 07:5010->help drop database

Name: 'DROP DATABASE'

Description:

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

6.查看当前数据库的版本

mysql> select version();

+------------+

| version() |

+------------+

| 5.5.32-log |

+------------+

1 row in set (0.00 sec)

7.查看当前登录数据库的用户

mysql> select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

8.查看当前的时间

mysql>select now();

+---------------------+

| now() |

+---------------------+

| 2018-01-18 20:14:43 |

+---------------------+

1 row in set (0.01 sec)

9.查看当前数据库包含的表信息

切换到指定库里面去查看

mysql>show tables;

Empty set (0.00 sec) 空表，新库还没有建表

mysql>show tables like 'user';

Empty set (0.00 sec)

mysql>show tables from linzhongniao; 查询指定数据库的表

Empty set (0.00 sec)

mysql>show tables in linzhongniao;

Empty set (0.00 sec)

10.删除mysql库多余账号

语法：drop user “user”@”主机域”<=注意引号可以是单引号或双引号，但是不能不加。

mysql> select user,host from mysql.user;

+------+--------------+

| user | host |

+------+--------------+

| root | 127.0.0.1|

| root | ::1 |

| | linzhongniao |

| root | linzhongniao |

| | localhost|

| root | localhost|

+------+--------------+

6 rows in set (0.00 sec)

mysql> drop user ''@'localhost'; 没有的部分就用两个单引号代替即可

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

+------+--------------+

| user | host |

+------+--------------+

| root | 127.0.0.1|

| root | ::1 |

| | linzhongniao |

| root | linzhongniao |

| root | localhost|

+------+--------------+

5 rows in set (0.00 sec)

注意：如果drop删除不了(一般是特殊字符或大写导致的)，可以用下面方式删除。

mysql> delete from mysql.user where user=' ' and host='localhost';

Query OK, 1 row affected (0.00 sec)

mysql> select user,host from mysql.user;

+------+--------------+

| user | host |

+------+--------------+

| root | 127.0.0.1|

| root | ::1 |

| | linzhongniao |

| root | linzhongniao |

| root | localhost|

+------+--------------+

5 rows in set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)