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

The following mainly brings you the main SQL statements of the MySQL database. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit the main SQL statements of the MySQL database. All right, don't talk too much nonsense, let's just read the following.

1. Check what libraries are available in the current CVM database?

SHOW DATABASES; # check which libraries are available

two。 What tables are there to see which libraries are currently in use?

USE + the name of the library to be queried

SHOW TABLES; # which tables are in the query library

3. Check the target structure?

USE + the name of the library to use

DESCRIBE + Table name # View table structure

4. Create a new library?

CREATE DATABASE + Table name # create a library

5. Create a new table

CREATE TABLE + table name (field 1 name type, field 2 name type,...) # create a table

6. Delete a table?

DROP TABLE + Table name # Delete the table

7. Delete a library?

DROP DATABASE + Library name # Delete the library

8. Insert data records into the table?

INSERT INTO table name (field 1, field 2, etc.) VALUES (value of field 1, value of field 2,...) # insert data record

9. Query data records?

The SELECT field name is 1, and the field name is 2. FROM table name [WHERE conditional expression] # query record

10. Modify data records?

UPDATE table name SET field name 1 = field value 1 WHERE conditional expression # modify record

Ex.: change Zhang San in the table to Wang er

The second of update name_db set user_name=' 'where user_name=' Zhang San'

11. Delete data records?

DELETE FROM Table name WHERE conditional expression # Delete record

twelve。 Database user authorization

GRANT permission list ON library name. Table name TO username @ source address [IDENTIFIED BY 'password']

Note: permission list: used to list various database operations authorized to use, separated by commas. For example, "select", "insert", "update" use "all" to indicate all permissions, and can authorize any operation.

Name of the library. Table name: the name of the library and table used to specify the authorized operation, in which the wildcard "*" can be used, for example, "test.*" is used to indicate that the object of the authorized operation is all tables in the test library "*. *" means all tables in all libraries.

User name @ source address: used to specify the user name and the client address that is allowed to access, that is, who can connect and where to connect. The source address can be a domain name, an IP address, or you can use the "%" wildcard to represent all addresses for an area or network segment. Such as "% .test.com"192.168.1%", etc.

IDENTIFIED BY: used to set the password string that users use to connect to the database. When creating a new user, if the "IDENTIFIED BY" part is omitted, the user password will be empty.

GRANT statement, which is specifically used to set access rights for database users. When the specified user does not exist, the GRANT statement will create a new user.

For example: grant all on *. * to 'test'@'localhost' identified by' 123456users; (give test users full access to all tables of local libraries. Without this user, the user password will be automatically created as 123456

13. View permissions?

SHOW GRANTS FOR user name @ Source address

Or

USE + mysql this library, and then select user,host from user; finds out the permissions

14. Revoke privileges?

REVOKE permission list ON database name. Table name FROM user name @ Source address

Example: revoke all on *. * from 'test'@'localhost'; (revoke the rights of the test user)

15. Backup database SQL statement export database?

Mysqldump [option] library name [table name 1] [table name 2]. > / backup path / backup file name # Export some tables in the specified library

Mysqldump [option]-- databases library name [library name 2]. > / backup path / backup file name # back up one or more complete libraries

Mysqldump [options]-- all-databases > / backup path / backup file name # back up one

Note: options include-u and-p to specify the user name and password of the database, respectively.

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

16. Import the database?

Mysql [option] [Library name] [Table name] < / backup path / backup File name

For the above on the MySQL database of the main SQL statements, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.