Common MySQL command lines in Linux

The most comprehensive tutorial: MySQL 5.1 Reference Manual

Below are some common commands.

Note that all MySQL commands after entering MySQL end with ";"; otherwise, MySQL remains in the waiting state.

First,MySQLLogin and exit

Format: shell> mysql-H Machine IP address-u user name-P Password

Enter the mysql-u root-p command, press enter and prompt you to enter the password, and then press enter to enter MySQL. (If there is no password, press-P and then press Enter.) The default MySQL user name is root, and the password is blank.

(This is a bit strange. My system is Ubuntu 11.04 and MySQL version is 5.1.54-1ubuntu4 (MySQL-server). When logging on, although there is no password, you must write-P, otherwise, it cannot be entered.-P can be omitted without a password, unlike in some documents .)

Note: if it is connected to another machine, you need to add a parameter:-H Machine IP address, not local.

After successful connection, you can enter quit (or \ q) at any time at the mysql> prompt to exit (Special: After the quit command, you do not need to add ";").

In UNIX, you can also press control-D to disconnect the server.

MySQL prompt: mysql>

Second, add new users

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

For example, add a user user1 with the password password1 so that it can log on to the local machine andAll databasesYou have the query, insert, modify, and delete permissions.

First, use the root user to connect to MySQL, and then type the following command:

Grant select, insert, update, delete on *. * To user1 @ localhost identified by "password1 ";

If you want the user to log on to MySQL on any machine, change localhost to "% ".

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

Grant select, insert, update, delete on mydb. * To user1 @ localhost identified "";

Step 3: operate databases(Very useful)

1. display the Database List.

Show databases;

By default, two databases are available: MySQL and test. MySQL inventory contains the MySQL system and user permission information. We change the password and add users, in fact, this database is actually operated.

2. display the data tables in the database:

Use MySQL;

Show tables;

Here, use is followed by the database name, and show is followed by the table name.

3. display the data table structure:

Describe table name;

4. Create and delete databases:

Create Database database name;

Drop database database name;

5. Create a table:

Use Database Name;

Create Table Name (Field List );

Drop table name;

6. Clear the table records:

Delete from table name;

7. display the records in the table:

Select * from table name;

Step 4: export and import data

1. Export data:

Mysqldump -- opt test> mysql. Test
Export the database test database to the mysql. Test file, which is a text file
For example, mysqldump-u root-p123456 -- databases dbname> MySQL. dbname

Export the database dbname to the mysql. dbname file.

2. import data:

Mysqlimport-u root-p123456 <MySQL. dbname.

No need to explain it.

3. Import text data to the database:

Field data of text data is separated by the tab key.
Use test;
Load data local infile "file name" into Table table name;