MySQL Common syntax

1.1. Open

Service MySQL Start

1.2. Close

Service MySQL Stop

1.3. Restart

Service MySQL Restart

2.1. Password plaintext

Syntax: mysql-u user name-p user Password

Example: mysql-uroot-p123456

2.2. Password redaction

Syntax: Mysql-u user name-p+ carriage return, then enter the password

Example: Mysql-uroot-p

　　

3. Change Password 3.1.SET password command (login required)

Syntax:mysql> set password for user name @localhost = password (' new password ');

Example: mysql>set password for [email protected] = password (' 123456 ');

3.2. Update the user table in the MySQL library with the SQL statement (login required)

mysql> use MySQL;

mysql> Update user set Password=password (' 123 ') where user= ' root ' and host= ' localhost ';

mysql> flush Privileges;

Mysqladmin command, clear text (no login required)

Syntax: mysqladmin-u user name-p old password password new password

Example: mysqladmin-uroot-p123 password 123456

3.4.mysqladmin command, ciphertext (no login required)

Syntax: mysqladmin-u user name-p old password password+ Enter, then enter two times new password

Example: mysqladmin-uroot-p123 password

　　

4.1. Display the list of databases

show databases;

4.2. Create and delete databases

Create database name;

drop database name;

4.3. Display the data table in the library

Use MySQL;

Show tables;

4.4. Display the data table structure

Describe data table name;

5. Import and Export database (no login required) 5.1. Export all databases

Syntax: mysqldump-u user name-p password--all-databases > export path

Example: mysqldump-uroot-p123456--all-databases >/tmp/all.sql

5.2. Export the specified database

Syntax: mysqldump-u user name-p password--databases database name > Export path

Example: mysqldump-uroot-p123456--databases testdatebase >/tmp/testdatebase.sql

5.3. Export the structure of the specified database (no data)

Syntax: mysqldump-u user name-p password--no-data--databases database name > Export path

Example: mysqldump-uroot-p123456--no-data--databases testdatebase >/tmp/testdatebase_no_data.sql

5.4. Export the specified table in the specified database

Syntax: mysqldump-u user name-p password--databases database name--tables data table name > Export path

Example: mysqldump-uroot-p123456--databases testdatebase--tables t_user >/tmp/testdatebase_user.sql

5.5. Export the structure of the specified table in the specified database (no data)

Syntax: mysqldump-u user name-p password--no-data--databases database name--tables data table name > Export path

Example: mysqldump-uroot-p123456--no-data--databases testdatebase--tables t_user >/tmp/testdatebase_user_no_data.sql

5.6. Export the specified table in the specified database and set the query criteria

Syntax: mysqldump-u user name-p password--databases database name--tables data table name--where= ' condition ' > Export path

Example: mysqldump-uroot-p123456--databases testdatebase--tables t_user--where= ' user_no=1 ' >/tmp/mysql_user_no.sql

5.7. Backing up a database across servers

Import all the data from the DB1 database in the HOST1 server into the DB2 database in Host2, and the DB2 database must be present or the error will be

(Tested, in the mysql5.5 version, DB2 exists, the actual generated database name is consistent with DB1; plus-c parameter enables compression delivery)

Syntax: mysqldump--host=host1-u user name-p password--databases db1 |mysql--host=host2-u user name-p password--databases DB2

Example: mysqldump--host=192.168.1.101-uroot-p123456-c--databases testdatebase |mysql--host=192.168.3.102-uroot- p123456--database Testdatebase

5.8. Importing a database from a SQL file

Syntax: mysql-u user name-p password < import path
Example: mysql-uroot-p123456 </tmp/testdatebase.sql

MySQL Common syntax