Common MySQL database operations

1. display the database

 show databases;

2. Select a database

Use Database Name;

3. display tables in the database

show tables;

4. display the data table structure

Describe table name;

 

5. display table records

Select * from Table Name

 

6. Create a database

Create Databse database name;

 

7. Create a table

Create Table Name (field setting list); mysql> Create Table Name (-> ID int auto_increment not null primary key,-> uname char (8 ), -> gender char (2),-> birthday date); query OK, 0 rows affected (0.03 Sec) mysql> show tables; + ------------------ + | tables_in_userdb | + ---------------------- + | Name | + ------------------ + 1 row in SET (0.00 Sec) mysql> describe name; + ---------- + --------- + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ---------- + --------- + ------ + ----- + --------- + -------------- + | id | int (11) | no | pri | null | auto_increment | uname | char (8) | Yes | null | gender | char (2) | Yes | null | birthday | date | Yes | null | + ---------- + --------- + ------ + ----- + --------- + ---------------- + 4 rows in SET (0.00 Sec) note: auto_increment auto-incrementing primary key

 

8. Add records

Insert into name (uname, gender, birthday) values ('zhang san', 'male', '2017-10-01 ');

 

9. Modify records

Update name set Birthday = '2017-01-10 'Where uname = 'zhang san ';

 

10. delete records

Delete from name where uname = 'zhangsan ';

 

11. delete a table

Drop Table Name

 

12. delete a database

Drop database database name;

 

13. Back up the database

Mysqldump-u root-p -- opt Database Name> Backup name; // enter the database directory

 

14. Recovery

Mysql-u root-P database name <backup name; // The database must exist during restoration and can be empty.

 

15. Database authorization

Format: grant select on database. * To username @ login host identified by "password"

Example 1: Add a user user001 with a password of 123456 so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

 mysql> grant select,insert,update,delete on *.* to user001@"%" Identified by "123456";

 

Example 2: Add a user user002 with a password of 123456 so that the user can only log on to localhost or set the specified IP address, you can also query, insert, modify, and delete the database test (localhost refers to the local host, that is, the host where the MySQL database is located)

// In this way, the user knows the password user_2 and cannot directly access the database from the Internet. The user can only operate the test database on the MySQL host.
// First use the root user to connect to MySQL, and then type the following command:

  mysql>grant select,insert,update,delete on test.* to user002@localhost identified by "123456";

 

Note: You can also use the table modification method to process user logon methods:

Database: MySQL
Table: User
Modify: set the value of the host column in the User table to the actual logon entry.