Using MySQL Database SQL statements

1. See which libraries are in the current server database?

SHOW DATABASES; # # #查看有哪些库

2. See which tables are currently used in the library?

Use + name of the library to query

SHOW TABLES; # # #查询库中有哪些表

3. View the underlying structure?

Use + the name of the library to be used

DESCRIBE + table Name # # #查看表结构

4. Create a new library?

CREATE DATABASE + table name # # #创建库

5. Create a new table

CREATE table + table name (field 1 name type, field 2 name type, ...) # # #创建表

6. Delete a table?

DROP table + table name # # #删除表

7. Delete a library?

DROP database + library name # # #删除库

8. Insert data records into the table?

INSERT into table name (Field 1, Field 2,...) Values (the Value of field 1, the value of field 2, ...) ) # # #插入数据记录

9. Query data records?

SELECT Field name 1, field name 2,... From table name [WHERE condition expression] # # #查询记录

10. Modify the data record?

Update table name SET field Name 1 = field value 1WHERE conditional expression # # #修改记录

Example: Change the Zhang San in the table to Wang ER

Update name_db set user_name= ' King II ' where User_name= ' Zhang San ';

11. Delete data records?

Deletefrom table name where conditional expression # # #删除记录

12. Database User Authorization

Grant permission list on Library name. Table name to User name @ Source address [identifiedby ' password ']

Note: Permissions list: Used to list the various database operations authorized to use, separated by commas, such as "select" "Insert" "Update" using "All" to represent all permissions, can authorize any action

Library name. Table Name: The name of the library and table that specifies the authorization action, where you can use the wildcard "*", for example, use "test.*" to indicate that all tables in the test library are "*. *" For all table of the library

User name @ Source address: Used to specify the user name and the client address to allow access, that is, who can connect and from which connection. The source address can be a domain name, an IP address, or a "%" wildcard that represents all the addresses of an area or network segment. such as "%.test.com" "192.168.1.%" and so on

Identified by: Used to set the password string used when the user connects to the database, and if the "identified by" section is omitted when creating a new user, the user password is blank.

Grant statement, which is specifically used to set access rights for a database user when the specified user does not exist, the GRANT statement creates a new user

Example: Grant all on * * to ' test ' @ ' localhost ' identified by ' 123456 '; (Give the test user all the permissions for all the tables in the local library, no user will automatically create a user password of 123456

13. View Permissions?

SHOW GRANTS for User name @ Source Address

Or

Use +mysql this library and select User,host from user; Identify permissions

14. Revoke permissions?

Revoke permissions list on database name. Table name from user name @ Source Address

Example: Revoke all on * * from ' test ' @ ' localhost '; (Revoke permission for test user)

15. Back up the database SQL statement export database?

mysqldump[option] Library name [table name 1][table Name 2] ... >/backup path/backup file # # # #导出指定库中的部分表

mysqldump[option]--databases library name [library Name 2] ... >/backup path/backup file # # # #备份一个或多个完整的库

mysqldump[Options]--all-databases>/Backup path/backup filename # # #备份一个

Note: options include-u,-P are used to specify the user name and password for the database respectively

Example: mysqldump-uroot-p1234567 mysql >/root/mysql.sql (to export the database MySQL to the root directory and named Mysql.sql)

16. Import the database?

mysql[options [library name] [table name]</backup path/backup file name

This article is from the "Hing" blog, make sure to keep this source http://jiaxinwang.blog.51cto.com/12273793/1954053

Using MySQL Database SQL statements