MySQL Common commands

First, the start and stop of MySQL service

net stop MySQL

net start MySQL

Second, login to MySQL

The syntax is as follows: Mysql-u user name-p user Password

Type the command mysql-uroot-p, enter the password after entering, enter 12345, and then enter into the MySQL, MySQL prompt is:

Mysql>

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

Third, add new users

Format: Grant permissions on database. * To User name @ login host identified by "password"

For example, 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:

Grant Select,insert,update,delete on * * to [e-mail protected] identified by "Password1";

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

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

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

Iv. Operational Database

Log in to MySQL, and then run the following command at the prompt of MySQL, with each command ending with a semicolon.

1. Display the list of databases.

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.

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 the library and delete the library:

Create database name;

drop database name;

5. Set the default character encoding:

Set names = ' encoded format '

6, the establishment of the table to delete the table:

Use library name;

CREATE TABLE table name (field list);

drop table name;

7. Clear the Table records:

Delete from table name;

8. Display the records in the table:

SELECT * from table name;

9. Add a record to the table

mysql> INSERT INTO MYTABLE values ("xxxx", "yyyy");

10. Update the data in the table

Mysql>update MYTABLE set sex= "F" where name= ' xxxxxx ';

V. Exporting and importing data

1. Export Data:

Mysqldump--opt Test > Mysql.test

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

such as: Mysqldump-u root-p123456--databases dbname > Mysql.dbname

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

2. Import data:

Mysqlimport-u root-p123456 < Mysql.dbname.

can also use Mysql>source d:/mysql.sql;

3. Import the text data into the database:

The field data of the text data is separated by the TAB key.

Use test;

Load data local infile "file name" into table name;

Vi. exit MySQL command:

Exit (Enter)

This article is from the "4699096" blog, please be sure to keep this source http://4709096.blog.51cto.com/4699096/1631416

MySQL Common commands