Mysql Common Command line action statement _mysql

First recruit, the start and stop of MySQL service

net stop MySQL
net start MySQL

Second recruit, login MySQL

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

Type the command mysql-uroot-p, enter the password to prompt you, enter 12345, and then enter into MySQL, MySQL prompt is:
Mysql>

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

Third recruit, add new user

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

For example, add a user user1 password for password1, so that it can be logged on the computer, and all databases have query, insert, modify, delete permissions. First of all

Use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on *.* to User1@localhost identified by "Password1";

If you want the user to be able to log on to MySQL on any machine, change the 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 User1@localhost identified by "";

Action Database

Log in to MySQL, and then run the following commands under the MySQL prompt, with each command ending with a semicolon.
1. Display the list of databases.

show databases;
There are two databases by default: MySQL and test. MySQL inventory with MySQL system and user rights information, we change the password and new users, is actually 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, the establishment and deletion of the library:

Create database library name;
drop Database library name;
5, the establishment of the table:

Use library name;
CREATE TABLE table name (field list);
The drop table table name;

6, clear the table record:

Delete from table name;
7, display the records in the table:

SELECT * from table name;

V. Recruit, export, and import 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.
You don't have to explain.

3. Import text data into the database:

The field data for the text data is separated by the TAB key.
Use test;
Load data local infile "filename" into table name;

1: Use the show statement to find out what database is currently 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 when the database changed the 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: Load data into a database table (for example, d:/mysql.txt) in text mode

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

9: Import. sql file command (e.g. D:/mysql.sql)

Mysql>use database;
Mysql>source D:/mysql.sql;

10: Delete Table

Mysql>drop TABLE MYTABLE;

11: Empty the table

Mysql>delete from MYTABLE;

12: Update the data in the table

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