Frequently Used commands for MySQL database operations

I. connect to MySQL and some basic commands 1. connection to MySQL format: mysql-H host address-u user name-P user password for example: connect to MySQL on the local machine: mysql-uroot-p123456 to connect to the MySQL database on the remote host: mysql-h10.10.1.23-uroot-p1234562. exit MySQL Command exit + press Enter 3. change Password: Format: mysqladmin-u username-P old password-Password new password example: Add a password 123456 to root. mysqladmin-uroot-p123-Password 1234564. add a new user: Add a semicolon to the command in the MySQL environment. format: grant select on database. * To username @ login host identified by "password"

For example, you can add a user named "test1" with the password "ABC" to log on to any host and have the query, insert, modify, and delete permissions on all databases. First, use the root user to connect to MySQL, and then type the following command:

    grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your MySQL database on any computer on the Internet and do whatever he wants for your data. For the solution, see Example 2.

Example 2: Add a user named "Test2" with the password "ABC" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the password of Test2, he cannot access the database directly from the Internet, but can only access the database through the web pages on the MySQL host.

    grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"; 

If you do not want Test2 to have a password, you can run another command to remove the password.

   grant select,insert,update,delete on mydb.* to test2@localhost identified by ""; 

Ii. MySQL display command

1. display the Database List: Show databases;

At the beginning, there were only two databases: MySQL and test. The MySQL database contains the MySQL system information. We change the password and add new users to use this database for operations.

2. display the data tables in the database:

Use MySQL; // open the database

    show tables; 

3. display the data table structure:

Describe table name;

4. database creation:

Create Database database name;

5. Create a table:

Use Database Name;

Create Table Name (field setting list );

6. Delete databases and tables:

Drop database database name;

Drop table name;

7. Clear records in the table:

Delete from table name;

8. Display records in the table:

Select * from table name;

9. view the show status; 10. isamchk is used for repair, check, and optimization. database file with the ISM suffix; 3: Back up the database and import the database. for example, to back up an existing MySQL database named Linux, run the mysqldump command.

The command format is as follows:

[Root @ linuxsir01 root] #/opt/MySQL/bin/mysqldump-uroot-P Linux>/root/Linux. SQL
Enter Password: Enter the Database Password here

Through the above command, we need to understand two things: first, back up the database as a database administrator; second, the backup destination is/root, and the backup file name is Linux. SQL. In fact, the backup location and file name are determined based on your own situation. The file name can be retrieved by yourself, or the path can be arranged by yourself;

For example, if I want to back up a Linux database to/home/beinan and the database name is linuxsir031130. SQL, enter the following command.
[Root @ linuxsir01 root] #/opt/MySQL/bin/mysqldump-uroot-P Linux>/home/beinan/linuxsir031130. SQL
Enter Password: Enter the database password of the database administrator root.

In this way, we can find the backup file linuxsir031130. SQL for the database named Linux in MySQL under the/home/beinan directory.

To sum up, we must learn to make changes when learning. :):)

5] How to import the backup database to the database?

First, we need to perform the preceding operations, such as adding a Database Administrator (if you have not added a MySQL database administrator) and creating a database.

For example, to back up linuxsir031130. SQL IN THE/home/beinan directory and import it to a database named Linux, perform the following operations;

[Root @ linuxsir01 root] #/opt/MySQL/bin/MySQL-uroot-P Linux Enter Password: enter the password here