MySQL Common commands

1.mysql Service start-up and stop

net stop MySQL

net start MySQL

2. Log in to MySQL

Mysql-u User name-p user Password

such as: Mysql-uroot-p return, enter the password such as (123456) carriage return, success is shown as follows:

Mysql>

If you are connecting to another machine, you need to add a parameter-H machine IP

3. Add new users

Grant permissions on the database. * to User name @ login host identified by "password"

such as: Add a user user1 password is 123456, so that it can log on the computer, and all the database has query, insert, modify, delete permissions.

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

4. Operational database

Remember to end with a semicolon.

1) Show Database list

show databases;

The default is 2 databases: MySQL and test.

MySQL inventory in the MySQL system and user rights information, we change the password and the new user is the library to operate.

2) Display the data table in the library

Use MySQL;

Show tables;

3) Display the structure of the data table

describe table name;

4) Building and deleting libraries

Create database name;

drop database name;

5) Build Table

Use library name;

CREATE TABLE table name (field list);

drop table name;

6) Clear the record in the table

Delete from table name;

7) display the records in the table

SELECT * from table name;

5. Exporting and importing data

1) Export Data

Mysqldump--opt Test > Mysql.test

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

Such as:

Mysqldump-u root-p123456--databases dbname > Mysql.dbname

Export the database dbname to a file mysql.dbname

2) Import data

Mysqlimport-u root-p123456 < Mysql.dbname

3) Import 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;

For example:

1: Use the show statement to find out what database currently exists on the server:

Mysql> SHOW DATABASES;

2: Create a database Mysqldata

mysql> CREATE DATABASE Mysqldata;

3: Select the database you created

mysql> use Mysqldata; (press ENTER to appear database changed operation success!)

4: See what tables exist in the current database

Mysql> SHOW TABLES;

5: Create a database table

Mysql> CREATE TABLE MYTABLE (name VARCHAR), sex CHAR (1));

6: Show the structure of the table:

Mysql> DESCRIBE MYTABLE;

7: Add a record to the table

mysql> INSERT INTO MYTABLE values ("HyQ", "M");

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

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;

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

Mysql>use database;

Mysql>source D:/mysql.sql;

10: Delete Table

Mysql>drop TABLE MYTABLE;

11: Clear the table

Mysql>delete from MYTABLE;

12: Update data in table

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

13: Backing Up the database

Mysqldump-u Root Library name >xxx.data

14: Example 2: Connecting to MySQL on a remote host

Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: You and root can be used without spaces, others are the same)

15: Exit MySQL command: Exit (enter)

MySQL Common commands