Shulou(Shulou.com)06/01 Report--

This article mainly introduces the MySQL command analysis, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

Command

Examples

Description

Description

Show

Displays information about databases, tables, columns, and cloud server status

SHOW has many forms that provide information about databases, tables

Columns, or status information about the server.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

Show all databases

Mysql > show grants for oldboy@'localhost'

+-+

| | Grants for oldboy@localhost |

+-+

| | GRANT USAGE ON *. * TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD' * FE28814B4 |

+-+

1 row in set (0.00 sec)

Show user permissions USAGE means no permissions

Mysql > show databases like'% my%'

+-+

| | Database (% my%) |

+-+

| | mysql |

+-+

1 row in set (0.00 sec)

Fuzzy matching

Mysql > show create database oldboy

Show the statement that creates the database

Show CHARACTER SET

Show proofreading rules for gbk

Mysql > show tables

+-+

| | Tables_in_oldboy |

+-+

| | student |

+-+

1 row in set (0.00 sec)

# View the table

Mysql > show create table student\ G

* * 1. Row *

Table: student

Create Table: CREATE TABLE `student` (

`id`int (4) NOT NULL

`name` char (20) NOT NULL

`are`tinyint (2) NOT NULL DEFAULT'0'

`dept` varchar (16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

# View create table statement

Mysql > show index from student\ G

* * 1. Row *

Table: student

Non_unique: 0

Key_name: PRIMARY # Index Type

Seq_in_index: 1

Column_name: id # index column name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

# View the index of the table

Command

Examples

Chinese description

English description

Select

Select user,host from mysql.user

Users who query the user table of the mysql library

Mysql > select database ()

+-+

| | database () |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

View the current database

Mysql > select user ()

+-+

| | user () |

+-+

| | root@localhost |

+-+

1 row in set (0.01 sec)

View current user

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.5.32-log |

+-+

1 row in set (0.00 sec)

View database version

Mysql > select now ()

+-+

| | now () |

+-+

| | 2018-04-10 22:25:11 |

+-+

1 row in set (0.00 sec)

View current time

Mysql > select count (distinct user) from mysql.user

+-+

| | count (distinct user) |

+-+

| | 7 |

+-+

1 row in set (0.01 sec)

Commands for unique values recorded in statistical tables

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

+-+ +

1 row in set (0.00 sec)

View all data in the table

Mysql > select * from test limit 2

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | old111 |

+-+ +

2 rows in set (0.00 sec)

Query the first two rows of the table

Mysql > select * from test limit 2

+-+ +

| | id | name |

+-+ +

| | 3 | kk |

| | 4 | zuma |

+-+ +

2 rows in set (0.00 sec)

Query rows 2 to 3 of the table

Mysql > select * from test where id=1

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

+-+ +

1 row in set (0.00 sec)

Query the data of the table by condition

Mysql > select * from test where id > 2

+-+ +

| | id | name |

+-+ +

| | 3 | kk |

| | 4 | zuma |

+-+ +

2 rows in set (0.00 sec)

Conditional range query table data

Mysql > select * from test where id > 2 and id select id,name from test where id > 1 and id explain select * from test where name='oldboy'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: test

Type: ref

Possible_keys: index_name # is already used, but not used is NULL

Key: index_name # has been used

Key_len: 60

Ref: const

Rows: 1

Extra: Using where; Using index

1 row in set (0.00 sec)

# check whether table fields use indexes

[root@db02 ~] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show full processlist;" | egrep-vi "sleep"

Id User Host db Command Time State Info

7 root localhost NULL Query 0 NULL show full process

Fetch slow query statement

Command

Examples

Chinese description

English description

Desc

Mysql > desc student

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (4) | NO | | NULL |

| | name | char (20) | NO | | NULL |

| | are | tinyint (2) | NO | | 0 | |

| | dept | varchar (16) | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

View table structure

Command

Examples

Chinese description

English description

Update

The UPDATE syntax can update columns in existing table rows with new values. The set clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order specified.

The LIMIT clause is used to give a limit to the number of rows that can be updated.

For the single-table syntax, the UPDATE statement updates columns of

Existing rows in the named table with new values. The SET clause

Indicates which columns to modify and the values they should be given.

Each value can be given as an expression, or the keyword DEFAULT to set

A column explicitly to its default value. The WHERE clause, if given

Specifies the conditions that identify which rows to update. With no

WHERE clause, all rows are updated. If the ORDER BY clause is

Specified, the rows are updated in the order that is specified. The

LIMIT clause places a limit on the number of rows that can be updated

Mysql > update mysql.user SET password=PASSWORD ("oldboy123") WHERE user='root' and host='localhost'

Update (modify) mysql.user (user table of mysql database) SET (which column is specified? Change the password column, what values should be given? PASSWORD value) password=PASSWORD ("oldboy123") (changed to oldboy123,PASSWORD must be added because this is encrypted) WHERE (to whom?) User='root' (for root users) and host='localhost'; (and host localhost modification)

Mysql > update test set name='inct' where id=3

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

# modify the name with an id of 3. Conditions must be added to modify the table.

Mysql > update test set name='inct'

Query OK, 4 rows affected (0.00 sec)

Rows matched: 5 Changed: 4 Warnings: 0

# if all changes are made without conditions, this problem is fatal.

Command

Examples

Chinese description

English description

Alter

Modify statement

Mysql > alter table student change id id int primary key auto_increment

# modify primary key index

Mysql > alter table student drop index index_name

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

# Delete a normal index

Mysql > alter table student add index index_name (name)

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

Modify the general index of a table

Command

Examples

Chinese description

English description

Drop

Mysql > drop user "" @ localhost

Query OK, 0 rows affected (0.00 sec)

Delete user

Mysql > drop database oldboy_gbk

Query OK, 0 rows affected (0.01 sec)

Delete database

Drop table student

Delete the previously built table

Command

Examples

Chinese description

English description

Delete

Delete statement

For the single-table syntax, the DELETE statement deletes rows from

Tbl_name and returns a count of the number of deleted rows.

Mysql > delete from test where id=1

Query OK, 1 row affected (0.00 sec)

# delete the ID specified in the table

Command

Examples

Chinese description

English description

Create

Create statement

Mysql > create database oldboy

Create a database of oldboy

Mysql > create database oldboy_gbk CHARACTER SET gbk COLLATE gbk_chinese_ci

Create a gbk database

Mysql > create user 'oldboy'@'localhost' identified by' oldboy123'

Query OK, 0 rows affected (0.01 sec)

# create a user

Create table student (

Id int (4) not null

Name char (20) not null

Are tinyint (2) NOT NULL default'0'

Dept varchar (16) default NULL

);

Create a tabl

Mysql > create index index_dept on student (dept (8))

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

Create a normal index of the first 8 bytes of a column of a table

Mysql > create index index_name_dept on student (name,dept)

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

Create a federated index of a table

Mysql > create index index_name_dept on student (name (8), dept (10))

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

The first N characters before creating a federated index of a table

Mysql > create unique index uni_ind_name on student (name)

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

# create a unique index of the table

Command

Examples

Chinese description

English description

Grant

Authorize MYSQL user rights and specify special permissions

The GRANT statement grants privileges to MySQL user accounts. GRANT

Also serves to specify other account characteristics such as use of

Secure connections and limits on access to server resources. To use

GRANT, you must have the GRANT OPTION privilege, and you must have the

Privileges that you are granting.

Mysql > grant all on oldboy.* to oldboy@'localhost'

Query OK, 0 rows affected (0.01 sec)

# authorizing oldboy users

Mysql > grant all on oldboy.* to 'oldboy11'@'localhost' identified by' oldboy123'

Query OK, 0 rows affected (0.00 sec)

Create users and passwords and authorize

Command

Examples

Chinese description

English description

Insert

Insert statement

INSERT inserts new rows into an existing table. The INSERT... VALUES

And INSERT... SET forms of the statement insert rows based on

Explicitly specified values. The INSERT... SELECT form inserts rows

Selected from another table or tables. INSERT... SELECT is discussed

Further in [HELP INSERT SELECT].

Mysql > insert into test (id,name) values (1dboy')

Query OK, 1 row affected (0.00 sec)

Insert the contents of id=1 name=oldboy into the test table

Command

Examples

Chinese description

English description

REVOKE

Revoke the authority

The REVOKE statement enables system administrators to revoke privileges

From MySQL accounts. Each account name uses the format described in

Mysql > REVOKE INSERT ON oldboy.* FROM 'oldboy'@'localhost'

Revoke insert permission

Command

Examples

Chinese description

English description

Truncate

Mysql > truncate table test

# emptying table data

Command

Examples

Chinese description

English description

Flush

Refresh statement

The FLUSH statement has several variant forms that clear or reload

Various internal caches, flush tables, or acquire locks. To execute

FLUSH, you must have the RELOAD privilege. Specific flush options might

Require additional privileges, as described later.

Mysql > flush privileges

Command

Examples

Chinese description

English description

Use

Switching database is equivalent to CD

Mysql > use oldboy

Database changed

Switch to oldboy database

Read the above about the MySQL command analysis, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.