MySQL Common commands

1. Start and stop of MySQL service

net stop mysqlnet start MySQL

2. Log in to MySQL

Mysql-u User name-p user Password

Type the command mysql-u root-p, enter the password after entering, and then enter into MySQL, MySQL prompt is:mysql>

Note that if you are connecting to another machine, you need to add a parameter-H machine IP

3. Add new users

" Password "

Add a user user1 password to Password1, so that it can log on to the computer, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Select " Password1 ";

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

4. Display Database list

show databases;

The default is two databases: MySQL and test. MySQL inventory in the MySQL system and user rights information, we change the password and the new user, is actually to operate the library.

5. Display the data table in the library

Use mysql;show tables;

6, display the structure of the data table

describe table name;

7. Building the library and deleting the library

Create database name; drop database name;

8. Construction and deletion of tables

Use library name, create table table name (field list);d ROP table name;

Create a data table:

CREATE TABLE MyTable (name VARCHAR), Sex CHAR (1));

9. Add a record to the table

INSERT into test1 values ("hyq","M");

10. Clear the record in the table

Delete from table name;

11. Update the data in the table

Update test1 set sex="F" where Name='hyq';

12. Display the records in the table

Select * from table name;

13. Export Data

Mysqldump--opt test1 > mysql.test;

Export the database test1 to the Mysql.test file, which is a text file;

Mysqldump-u root-p--databases test1 > Mysql.dbname

is to export the database test1 to the file mysql.dbname;

14. Import data

123456 < Mysql.dbname

15. Import the text data into the database (the field data of the text data is separated by the TAB key)

" E:\123.txt " into table test1;

Import the data from the e-disk 123.txt file into the Test1 data table of the database Test1 (established)

16. Import. sql files (for example, D:/mysql.sql)

Use test1;source D:/mysql.sql;

17. Backing Up the database

Mysqldump-u Root Library name >xxx.data;

18. Quit MySQL

Exit

MySQL Common commands