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

What are the commonly used commands in mysql? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. How to start and stop mysql service commands

Netstartmysql

Netstopmysql

2. Log in to mysql command

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

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

Mysql >

Note that if you need to connect to another machine, you need to add a parameter-h machine IP address.

3. How to add new users

The command is as follows: grant permissions on database. * to username @ login host identifiedby "password"

For example: we add a user user1 password to password123, you can log in on this machine, and have the authority to query, insert, modify and delete all databases.

First, use the administrator user to connect to mysql and enter the following command:

Grantselect,insert,update,deleteon*.*touser1@localhostIdentifiedby "password123"

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.

Grantselect,insert,update,deleteonmydb.*touser1@localhostidentifiedby ""

4. Operation commands of the database

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

4.1 displays a list of databases.

Showdatabases

4.2 display the data tables in the library:

Usemysql

Showtables

4.3 display the structure of the data table:

Describe table name

4.4 Building and deleting databases:

Createdatabase library name

Dropdatabase library name

4.5 create tables:

Use library name

Createtable table name (field list)

Droptable table name

4.6 clear the records in the table:

Deletefrom table name

4.8 display the records in the table:

Select*from table name

5. How to import and export data

5.1 Import data command

Mysqlimport-uroot-p123456mysql.test

Indicates that the test database is exported to a mysql.test file, which is a text file

For example: mysqldump-uroot-p123456--databasesdbname > mysql.dbname

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

6. Summary of mysql practical commands

Mysql > SHOWDATABASES

Create a database MYSQLDATA

Mysql > CREATEDATABASEMYSQLDATA

Select the database you created

Mysql > USEMYSQLDATA; (if Databasechanged appears by pressing enter key, the operation is successful!)

See what tables exist in the database now

Mysql > SHOWTABLES

Create a database table

Mysql > CREATETABLEMYTABLE (nameVARCHAR (20), sexCHAR (1))

Displays the structure of the table:

Mysql > DESCRIBEMYTABLE

Add a record to the table

Mysql > insertintoMYTABLEvalues ("hyq", "M")

Load data into a database table in text (for example, D:/mysql.txt)

Mysql > LOADDATALOCALINFILE "D:/mysql.txt" INTOTABLEMYTABLE

Import .sql file command (for example, D:/mysql.sql)

Mysql > usedatabase

Mysql > sourced:/mysql.sql

Delete tabl

Mysql > dropTABLEMYTABLE

Clear the table

Mysql > deletefromMYTABLE

Update data in the table

Mysql > updateMYTABLEsetsex= "f" wherename='hyq'

Backup database

Mysqldump-uroot library name > xxx.data

After reading the above, have you mastered the methods of common mysql commands? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.