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

Source: Internet
Author: User
Tags dba create database

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.