MySQL Library Base Command summary

1 log in to MySQL database

[Email protected] ~]# mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 9

Server Version:5.6.34-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

2 Viewing the currently logged on User:

mysql> Select User ();

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

| User () |

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

| [Email protected] |

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

1 row in Set (0.20 sec)

Mysql>

3 Create a database haha and view the complete statement of the built library

mysql> CREATE database haha; haha

Query OK, 1 row Affected (0.00 sec)

Mysql> Show CREATE database haha; View vigorous's Databases

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

| Database | Create Database |

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

| haha | CREATE DATABASE ' haha '/*!40100 DEFAULT CHARACTER SET UTF8 */| |

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

1 row in Set (0.00 sec)

Mysql> show databases; view all databases

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

| Database |

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

| Information_schema |

| haha |

| MySQL |

| Oldboy |

| Oldgril |

| Performance_schema |

| Xinpan |

| Xu |

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

8 rows in Set (0.00 sec)

Mysql>

4 Create user hehe so that it can manage the database haha

mysql> Create user [email protected] ' localhost ' identified by ' oldboy123 '

Create local User hehe authorization database password

Query OK, 0 rows affected (0.06 sec)

Mysql> Grant all on haha.* to hehe@ ' localhost '; haha for database, hehe for user

Query OK, 0 rows Affected (0.00 sec)

5 View what permissions the user hehe has created

Mysql> Show grants for [email protected] ' localhost '; see what permissions the user hehe created

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

| Grants for [email protected] |

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

| GRANT USAGE on *. hehe ' @ ' localhost ' identified by PASSWORD ' *fe28814b4a8b3309dac6ed7d3237aded6da1e515 ' |

| GRANT all Privileges "Oldboy". * to ' hehe ' @ ' localhost ' |

| GRANT all Privileges "hehe". * to ' hehe ' @ ' localhost ' |

| GRANT all privileges "haha". * to ' hehe ' @ ' localhost ' |

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

4 rows in Set (0.03 sec)

6 See which users are in the current database.

Mysql>

Mysql> select User,host from Mysql.user;

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

| user | Host |

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

| Keke | %          |

| Oldboy | %          |

| Xinjia | %          |

| MHA | 10.0.0.% |

| Rep | 10.0.0.% |

| Root | 127.0.0.1 |

| BBS | 172.16.1.% |

| WordPress | 172.16.1.% |

| Root | db02--52 |

| haha | localhost |

| hehe | localhost |

| Oldboy | localhost |

| Root | localhost |

| System | localhost |

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

Rows in Set (0.00 sec)

Mysql>

6 Entering the Oldboy database

Mysql> Use Oldboy

Database changed

Mysql>

7 View your current database

mysql> Select Database ();

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

| Database () |

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

| Oldboy |

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

1 row in Set (0.00 sec)

Mysql>

8 Create a table Xiaoke field ID and name varchar (16)

Mysql> CREATE TABLE Xiaoke (ID int (4) not NULL, name varchar (+) not NULL);

Query OK, 0 rows affected (0.63 sec)

Mysql>

9 View SQL statements for building table structures and table structures

Mysql> desc Xiaoke View the structure of a table

;

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

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

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

| ID | Int (4) |     NO | |       NULL | |

| name | varchar (16) |     NO | |       NULL | |

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

2 rows in Set (0.03 sec)

View SQL statements for table structure

Mysql> Show full columns from xiaoke; View SQL statements for table structure

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

| Field | Type | Collation | Null | Key | Default | Extra | privileges | Comment |

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

| ID | Int (4) | NULL |     NO | |       NULL | |         select,insert,update,references | |

| name | varchar (16) | Utf8_general_ci |     NO | |       NULL | |         select,insert,update,references | |

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

2 rows in Set (0.00 sec)

Mysql>

10 inserting a data "1,aini"

mysql> INSERT INTO Test (Id,name) VALUES (1, ' Aini ');

Query OK, 1 row affected (0.02 sec)

Mysql> SELECT * from test;

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

| ID | Age | name |

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

| 1 | NULL | Oldboy |

| 1 | NULL | Oldboy |

| 1 | NULL | Aini |

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

3 Rows in Set (0.02 sec)

Mysql>

11 BULK Insert 2 rows of data "2,xiaoxu" "3,xiaoxin"

mysql> INSERT INTO Test (Id,name) VALUES (2, ' Xaioke '), (3, ' xiaoxin ');

Query OK, 2 rows Affected (0.00 sec)

Records:2 duplicates:0 warnings:0

Mysql> SELECT * from test;

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

| ID | Age | name |

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

| 1 | NULL | Oldboy |

| 1 | NULL | Oldboy |

| 1 | NULL | Aini |

| 2 | NULL | Xaioke |

| 3 | NULL | Xiaoxin |

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

5 rows in Set (0.00 sec)

12 querying records with the name Oldboy

Mysql> SELECT * from test where name= ' Oldboy ';

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

| ID | Age | name |

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

| 1 | NULL | Oldboy |

| 1 | NULL | Oldboy |

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

2 rows in Set (0.00 sec)

Mysql>

13 change the name of database ID equal to 1 oldboy to Oldgril

Mysql> SELECT * FROM test where name= ' Oldboy ';

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

| ID | Age | name |

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

| 1 | NULL | Oldboy |

| 1 | NULL | Oldboy |

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

2 rows in Set (0.00 sec)

mysql> updata Test set name= ' Oldgirl ' where id= ' 1 ';

Mysql> select * from test;

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

| ID | Age | name |

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

| 1 | NULL | Oldgirl |

| 1 | NULL | Oldgirl |

| 1 | NULL | Oldgirl |

| 2 | NULL | Xaioke |

| 3 | NULL | Xiaoxin |

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

5 rows in Set (0.00 sec)

Mysql>

To be continued .....

This article is from "Xiao Xin" blog, please be sure to keep this source http://12218064.blog.51cto.com/12208064/1924715

MySQL Library Base Command summary