Shulou(Shulou.com)05/31 Report--

This article mainly introduces which commands are commonly used in the mysql command line, which are introduced in great detail and have certain reference value. Friends who are interested must finish reading them.

Mysql Command Line Common commands

The first step, the start and stop of mysql service

Net stop mysql

Net start mysql

The second step is to log in to mysql

The syntax is as follows: mysql-u username-p user password

Type the command mysql-uroot-p, enter and prompt you to enter your password, enter 12345, and then enter the mysql. The prompt for mysql is:

Mysql >

Note that if you are connected to another machine, you need to add a parameter-h machine IP

Third, add new users

Format: grant permission on database. * to user name @ login host identified by "password"

For example, add a user's user1 password to password1, so that it can log in on the local computer, and have the authority to query, insert, modify and delete all databases. 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 be able to log in to mysql on any machine, change localhost to "%".

If you don't want user1 to have a password, you can issue another command to remove the password.

Grant select,insert,update,delete on mydb.* to user1@localhost identified by ""

The fourth trick: operate the database

Log in to mysql and run the following commands at the mysql prompt, each ending with a semicolon.

1. Display the list of databases.

Show databases

There are two databases by default: mysql and test. Mysql stores the system and user rights information of mysql. When we change passwords and add new users, we actually operate on this library.

2. Display the data table in the library:

Use mysql

Show tables

3. Display the structure of the data table:

Describe table name

4. Build and delete databases:

Create database library name

Drop database library name

5. Create a table:

Use library name

Create table table name (field list)

Drop table table name

6. Clear the records in the table:

Delete from table name

7. Display the records in the table:

Select * from table name

Fifth move, export and import data

1. Export data:

Mysqldump-- opt test > mysql.test

Export the database test database to a mysql.test file, which is a text file

For example: mysqldump-u root-p123456-- databases dbname > mysql.dbname

Is to export the database dbname to the file mysql.dbname.

two。 Import data:

Mysqlimport-u root-p123456 < mysql.dbname.

You don't have to explain.

3. Import text data into the database:

The field data of text data is separated by tab.

Use test

Load data local infile File name into table table name

There is also a way to query the range of values from M to N, as follows:

We can use LIMIT--, a clause supported by SELECT in MySQL, to do this.

LIMIT can implement top N query or M to N (a certain segment) record query. The specific syntax is as follows:

SELECT * FROM MYTABLE

ORDER BY AFIELD

LIMIT offset, recnum

Where offset is the number of records starting from item 1, and recnum is the number of records returned. Example:

Select * from mytable

Order by afield

Limit 2, 5

It means five records starting from the third record.

The above is all the contents of the article "what are the commands commonly used on the mysql command line?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.