Mysql DBA Advanced Operations Learning Note-mysql Database common management application

9.1 Creating a Database

Command syntax: Create database< database name > Note Library name cannot start with a number
In the case of MySQL default character set, the database test is as follows:

A. Creating a database named ZBF

[email protected] 08:3120->create database zbf;Query OK, 1 row affected (0.00 sec)[email protected] 08:3810->show databases like ‘z%‘;+---------------+| Database (z%) |+---------------+| zbf   |+---------------+1 row in set (0.01 sec)

To view the build database statement

[email protected] 08:4634->show create database zbf\G*************************** 1. row ***************************   Database: zbfCreate Database: CREATE DATABASE `zbf` /*!40100 DEFAULT CHARACTER SET latin1 */ 默认是latin字符集1 row in set (0.00 sec)

B. Creating a GBK character Set database named ZBF_GBK

create database zbf_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

C. Create a UTF8 database named Zbf_utf8

[email protected] 09:1523->create database zbf_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;Query OK, 1 row affected (0.00 sec)[email protected] 09:1616->show create database zbf_utf8\G;*************************** 1. row ***************************   Database: zbf_utf8Create Database: CREATE DATABASE `zbf_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */1 row in set (0.00 sec)

D. Creating database commands in different character set formats

create database zbf; 默认数据库配置，相当于创建拉丁字符集数据库。create database zbf_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 创建gbk字符集数据库create database zbf_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 创建utf8字符集数据库

Hint: The inconsistency of character set is the culprit that causes the database Chinese content garbled.

Tip: If a specific character set is specified at compile time, then the database that created the corresponding character set later does not need to be specified. As follows:

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

Then build the library when it is created by default, create database ZBF;

E. How to create a database in the enterprise?

1. Defining character sets according to the developed Environment (recommended UTF8)

2. Specify the character set when compiling. For example:

-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \

Then build the library by default when created, create database ZBF.

3. When compiling a character set or specifying a different character set for the program, how do I fix it?

Specify the character set to create the database

create database zbf; 默认数据库配置，相当于创建拉丁字符集数据库。create database zbf_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;创建gbk字符集数据库create database zbf_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;创建utf8字符集数据库

Database to support the creation of a library's character set, for example

    -DEXTRA_CHARSETS=gbk,gb2312,utf8,asci或    -DEXTRA_CHARSETS=all

9.2 Displaying the database

Command: show databases;

[email protected] 07:0810->show databases;+--------------------+| Database   |+--------------------+| information_schema || mysql  || zbf|| zbf_utf8   |+--------------------+4   rows in set (0.00 sec)[email protected] 07:3122->help showSHOW DATABASES [like_or_where][email protected] 07:3027->show databases like ‘%zb%‘; %为通配符，匹配所有内容+-----------------+| Database (%zb%) |+-----------------+| zbf || zbf_utf8|+-----------------+2   rows in set (0.00 sec)

View current database, empty

[email protected] 07:3537->select database();+------------+| database() |+------------+| NULL   |+------------+1 row in set (0.00 sec)

Go to the database and view it once

[email protected] 07:3638->use zbf;Database changed[email protected] 07:4001->select database();+------------+| database() |+------------+| zbf|+------------+1   row in set (0.00 sec)

9.3 Deleting a database

Command: Drop database< database name >

Example: Delete a database named ZBF

[email protected] 07:4005->show databases;+--------------------+| Database   |+--------------------+| information_schema || mysql  || zbf|| zbf_utf8   |+--------------------+4   rows in set (0.00 sec)[email protected] 07:4620->drop database zbf;Query OK, 0 rows affected (0.00 sec)[email protected] 07:4635->show databases;+--------------------+| Database   |+--------------------+| information_schema || mysql  || zbf_utf8   |+--------------------+2   rows in set (0.00 sec)

Don't want to see help often

[email protected] 07:5010->help drop databaseName: ‘DROP DATABASE‘Description:Syntax:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

9.4 Connecting the database

Command: Use < database name > equivalent to the CD switch directory under Linux command, use is to switch the database
For example:

[email protected] 08:0746->use zbf;Database changed[email protected] 08:0802->select database();+------------+| database() |+------------+| zbf|+------------+1   row in set (0.00 sec)

9.5 Viewing the currently connected database

[email protected] 08:0802->select database();相当于linux下的pwd+------------+| database() |+------------+| zbf|+------------+1 row in set (0.00 sec)

View version

[email protected] 08:0938->select version();+------------+| version()  |+------------+| 5.1.72-log |+------------+1 row in set (0.00 sec)

View the current user

[email protected] 08:1316->select user();+------------------+| user()   |+------------------+| [email protected] |+------------------+1 row in set (0.00 sec)

View the current time

[email protected] 08:1327->select now();+---------------------+| now()   |+---------------------+| 2018-01-18 20:14:43 |+---------------------+1   row in set (0.01 sec)

9.6 Viewing the table information contained in the current database

Switch to the database to view

[email protected] 08:1630->show tables;Empty set (0.00 sec) 空表，新库还没有建表[email protected] 08:1742->show tables like ‘user‘;Empty set (0.00 sec)[email protected] 08:2016->show tables from zbf; 查询指定数据库的表Empty set (0.00 sec)[email protected] 08:2030->show tables in zbf;Empty set (0.00 sec)

9.7 Delete the MySQL system surplus account

Syntax: Drop user "user" @ "host Domain" <= Note that quotation marks can be single or double quotes, but cannot be added.

mysql> select user,host from mysql.user;+------+-----------+| user | host  |+------+-----------+| root | 127.0.0.1 || root | ::1   ||  | localhost || root | localhost ||  | mysql || root | mysql |+------+-----------+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   || root | localhost ||  | mysql || root | mysql |+------+-----------+5 rows in set (0.00 sec)

Note: If the drop is not deleted (typically due to special characters or uppercase), it can be removed in the following way.

Delete from mysql.user where user=’root’and host=’mysql’;Flush privileges;

2018/1/26 23:51:40

Mysql DBA Advanced Operations Learning Note-mysql Database common management application