CMD connection mysql operation command

Connection: Mysql-h host address-u user name-P user Password (note: U and root can be used without spaces, others are the same)
Disconnect: Exit (Enter)

Create authorization: Grant SELECT on database. * To User name @ login host identified by \ "Password \"
Change Password: mysqladmin-u user name----old password password new password
Delete authorization: Revoke select,insert,update,delete om * * [email protected];

Display database: show databases;
Display Data sheet: show tables;
Display table structure: describe table name;

Create Library: Name of creation database;
Delete Library: drop database name;
Usage Library: Use library name;

Creating Tables: Create table table names (field settings list);
Delete tables: drop table name;
Modify table: ALTER TABLE t1 rename T2
Query table: SELECT * from table name;
Empty table: Delete from table name;
Backup table: Mysqlbinmysqldump-h (IP)-uroot-p (password) databasenametablename > Tablename.sql
Recovery table: Mysqlbinmysql-h (IP)-uroot-p (password) Databasenametablename < Tablename.sql (first delete the original table before operation)

Add column: ALTER TABLE T2 ADD C INT UNSIGNED not NULL Auto_increment,addindex (c);
Modify column: ALTER TABLE T2 MODIFY A TINYINT not NULL, change B CChar (20);
Delete column: ALTER TABLE T2 DROP column C;

Backup database: Mysql\bin\mysqldump-h (IP)-uroot-p (password) databasename> Database.sql
Recovery database: Mysql\bin\mysql-h (IP)-uroot-p (password) databasename< Database.sql
Copy database: Mysql\bin\mysqldump--all-databases >all-databases.sql
Repair Database: Mysqlcheck-a-o-uroot-p54safer

Text data import: Load data local infile \ "file name \" into table name;
Data import and Export: Mysql\bin\mysqlimport database Tables.txt

First trick, 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---User password
Type the command mysql-uroot-p, enter the password after you enter the code, type 12345, 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

Third recruit, add new user
Format: Grant permissions on the database. * To username @ Login host identified by "password"
For example, add a user user1 password to Password1, so that it can log on the computer, and all the databases have the 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[email 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 use a command to remove the password.
Grant Select,insert,update,delete on mydb.* to[email protected] identified by";

Four strokes: Manipulating the 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, build the table:
Use library name;
CREATE TABLE table name (field list);
drop table name;
6. Clear the Table records:
Delete from table name;
7. Display the records in the table:
SELECT * from table name;

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 of the text data is separated by the TAB key.
Use test;
Load data local infile "file name" into table name;
1: Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;
2:2. Create a database Mysqldata
mysql> CREATE DATABASE Mysqldata;
3: Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) )
4: See what tables exist in the current database
Mysql> SHOW TABLES;
5: Create a database table
Mysql> CREATE TABLE MYTABLE (name VARCHAR), Sexchar (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" intotable 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: Back Up the database mysqldump-u root library name &GT;XXX.DATA14:

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)

3. Exit MySQL command: Exit

(a) connect MySQL:
Format: mysql-h host address-u user name-P user Password

1. Example 1: Connect to MySQL on this machine
First open the DOS window, and then into the MySQL installation directory under the bin directory, for example: D:\mysql\bin, and then type the command mysql-uroot-p, enter after the prompt to lose the password, if just installed MySQL, superuser root is no password, So the direct return to enter the MySQL, MySQL, the prompt is:mysql>
2. Example 2: Connect to MySQL on a remote host
Assume that the remote host IP is: 10.0.0.1, the user name is root, the password is 123. Type the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: You and root can be used without spaces, others are the same)
3. Quit MySQL Command
Exit (Enter)

(b) Change the password:
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root 123. First enter directory C:\mysql\bin under DOS, and then type the following command:
Mysqladmin-uroot-password123
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2, Example 2: Then change the root password to 456
Mysqladmin-uroot-pab12password 456
(iii) Increased number of new users: (Note: Unlike above, the following are the commands in the MySQL environment, so they are followed by a semicolon as a command terminator)
Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grantselect,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";

But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2.
Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grantselect,insert,update,delete on mydb.* to [email protected] identifiedby "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grantselect,insert,update,delete on mydb.* to [email protected] identifiedby "";
(iv) Display of commands
1. Display the database list:
show databases;
Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.
2. Display the data table in the library:
use MySQL;//Open Library
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * from table name;

MySQL Import Export command
1. Export the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC >wcnc.sql
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC Users>wcnc_users.sql

3. Export a database structure
Mysqldump-u Wcnc-p-D--add-drop-table smgp_apps_wcnc>d:wcnc_db.sql
-D No data--add-drop-table add a drop table before each CREATE statement

4. Import the database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:wcnc_db.sql (Note: If you write Sourced:\wcnc_db.sql, you will report the grammar

Bulk importing data using load data, which can import data instantaneously, is very useful!

Copy CodeThe code is as follows:
LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE ' file_name.txt '
[REPLACE | IGNORE]
Into TABLE tbl_name
[Fields Field operation, setting the delimiter for each field

[TERMINATED by ' string ']
[[optionally] enclosed by ' char ']
[Escaped by ' char ']
]
[LINES line operation, starting with a character, to a character

[Starting by ' string ']
[TERMINATED by ' string ']
]
[IGNORE number LINES] row operation, ignoring a row

[(Col_name_or_user_var,...)] Field operations, fields written to correspond to data

[SET col_name = expr,...)]

Example:load Data infile '/test/test.file ' intotable ' test ' fields terminated by "\ T" (fieldsone,fieldstwo);
load/test/test.file into table test, use the \ t partition field, write to Fieldsone and Fieldstwo, and divide by default as a line break!

CMD connection mysql operation command