Common MySQL operations

Do you have a headache for obtaining common MySQL operations? If this is the case, the following articles will give you corresponding solutions. The following articles mainly introduce common MySQL operations, and the following are specific descriptions of related content.

Note: Each Command in MySQL must end with a semicolon.

1. display the database

 
 

  
  
mysql> show databases;   
  
  
+----------+   
  
  
| Database |   
  
  
+----------+   
  
  
| mysql|   
  
  
| test|   
  
  
+----------+   
  
  
2 rows in set (0.04 sec)  
 
 

Mysql has just been installed with two databases: mysql and test. The mysql database is very important. It contains MySQL system information. We change the password and add new users. In fact, we use the relevant tables in this database for operations.

2. Common MySQL operations; displays tables in the database

 
 

  
  
Mysql> use mysql; open the database. to operate on each database, open the database, similar to foxpro)
  
  
Database changed
  
  
Mysql> show tables;
  
  
+ ----------------- +
  
  
| Tables_in_mysql |
  
  
+ ----------------- +
  
  
| Columns_priv |
  
  
| Db |
  
  
| Func |
  
  
| Host |
  
  
| Tables_priv |
  
  
| User |
  
  
+ ----------------- +
  
  
6 rows in set (0.01 sec)
 
 

3. display the data table structure:

 
 

  
  
Describe table name;
 
 

4. display the records in the table:

 
 

  
  
Select * from table name;
 
 

For example, the user table records in the mysql database are displayed. All users who can operate on MySQL users are in this table.

 
 

  
  
Select * from user; 
 
 

5. database creation:

 
 

  
  
Create database name;
 
 

For example, create a database named aaa

 
 

  
  
mysql> create databases aaa; 
 
 

6. Create a table:

 
 

  
  
Use Database Name;
  
  
Create table Name (field setting list );
 
 

For example, create a table name in the newly created aaa database. The table has four fields: id (serial number, auto-increment), xm name), xb gender, and csny year and month.

 
 

  
  
use aaa;  
  
  
mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date); 
 
 

You can use the describe command to view the created table structure.

 
 

  
  
mysql> describe name;   
  
  
 
  
  
+-------+---------+------+-----+---------+----------------+   
  
  
| Field | Type| Null | Key | Default | Extra　|   
  
  
+-------+---------+------+-----+---------+----------------+   
  
  
| id| int(3)　|　| PRI | NULL| auto_increment |   
  
  
| xm| char(8) | YES　|| NULL||   
  
  
| xb| char(2) | YES　|| NULL||   
  
  
| csny　| date| YES　|| NULL||   
  
  
+-------+---------+------+-----+---------+----------------+  
 
 
 

7. Common MySQL operations: Add records

For example, add several related records.

Mysql> insert into name values ('', 'zhang san', 'mal', '2017-10-01 ');

Mysql> insert into name values ('', 'baiyun ', 'female', '2017-05-20 ');

The select command can be used to verify the result.

 
 

  
  
Mysql> select * from name;
  
  
+ ---- + ------ + ------------ +
  
  
| Id | xm | xb | csny |
  
  
+ ---- + ------ + ------------ +
  
  
| 1 | Zhang San | male |
  
  
| 2 | Baiyun | female | 1972-05-20 |
  
  
+ ---- + ------ + ------------ +
 
 

8. Modify records

For example, change the date of birth of John

 
 

  
  
Mysql> update name set csny = '2017-01-10 'where xm = 'zhang san ';
 
 

9. delete records

For example, delete the records of Michael Jacob.

 
 

  
  
Mysql> delete from name where xm = 'zhang san ';
 
 

10. Delete databases and tables

 
 

  
  
Drop database name;
  
  
Drop table name;
 
 

9. Add MySQL users

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

Example 1: Add a user user_1 with a password of 123 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 user_1@"%" Identified by "123"; 
 
 

In example 1, the added user is very dangerous. If you know the user_1 password, then he can log on to your MySQL database on any computer on the Internet and do whatever he wants. For the solution, see Example 2.

Example 2: Add a user_2 password of 123 so that the user can only log on to localhost, you can also query, insert, modify, and delete the database aaa. localhost refers to the local host, that is, the host where the MySQL database is located. In this way, the user knows the password of user_2, he cannot directly access the database from the Internet, and can only operate the aaa database through the MYSQL host.

 
 

  
  
mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123";  
 
 

If a new user cannot log on to MySQL, run the following command during logon:

 
 

  
  
Mysql-u user_1-p-h 192.168.113.50-h is followed by the IP address of the host to be logged on)
 
 

10. Common MySQL operations: backup and recovery

1. Backup

For example, back up the aaa library created in the previous example to the back_aaa file.

[Root @ test1 root] # cd/home/data/mysql (go to the database directory, this example library has been transferred from val/lib/mysql to/home/data/mysql, see section 7 above)

 
 

  
  
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa 
 
 

2. Recovery

 
 

  
  
[root@test mysql]# mysql -u root -p ccc < back_aaa