CMD connection Mysql method of detailed _mysql

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

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

Display database: show databases;
Display datasheet: show tables;
Display table structure: describe table name;

Creating a library: Create database name;
Delete Library: drop database library name;
Using library: Use library name;

Creating Table: Create table table name (field set list);
Delete table: drop table 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 (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 \ "filename \" into table name;
Data Import Export: Mysql\bin\mysqlimport database Tables.txt

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"
such as, add a user user1 password for password1, Make it possible to log on on this computer and have permission to query, insert, modify, and delete all databases. First, 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 the 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: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 (), 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: Load data into a database table (for example, d:/mysql.txt) in text mode
mysql> LOAD DATA Local INFILE "D:/mysql.txt" intotable 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 '; 13: Back Up the database mysqldump-u root library name &GT;XXX.DATA14:

Example 2: Connecting to MySQL on a remote host
Assume the IP of the remote host is: 110.110.110.110, username is root, password is abcd123. Type the following command:

Mysql-h110.110.110.110-uroot-pabcd123

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

3, exit MySQL command: Exit

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

1, Example 1: Connect to MySQL on this computer
First in the Open DOS window, and then into the MySQL installation directory under the bin directory, such as: D:\mysql\bin, and then type the command mysql-uroot-p, enter after the prompts you to lose the password, if you have just installed a good MySQL, superuser root is no password, So you can enter the direct return to MySQL, the MySQL prompt is:mysql>
2, Example 2: Connect to the remote host MySQL
Suppose the IP of the remote host is: 10.0.0.1, the username is root, and the password is 123. Type the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: U and root can be without spaces, others are the same)
3, quit the MySQL command
Exit (Enter)

(ii) Change Password:
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root 123. First enter the directory C:\mysql\bin in DOS, and then type the following command:
Mysqladmin-uroot-password123
Note: Since Root does not have a password at the beginning, the-p old password can be omitted.
2, Example 2: And then change the root password to 456
Mysqladmin-uroot-pab12password 456
(iii) Increased number of new users: (Note: Unlike the above, the following are the commands in the MySQL environment, followed by a semicolon as the 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 connect the root user to MySQL, and then type the following command:
Grantselect,insert,update,delete on *.* to test1@ "%" identified by "ABC";

But for example 1 the increased user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and can do whatever it wants with your data, as shown in Example 2.
Example 2, add a user test2 password for ABC, so that he can only log on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database of the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.
Grantselect,insert,update,delete on mydb.* to Test2@localhost Identifiedby "ABC";
If you do not want to test2 the password, you can make another command to eliminate the password.
Grantselect,insert,update,delete on mydb.* to Test2@localhost identifiedby "";
(iv) Display commands
1. Display Database list:
show databases;
Only two databases were initially: MySQL and test. MySQL Library is very important it has MySQL system information, we change the password and new users, 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 library name;
5, the establishment of the table:
Use library name;
CREATE TABLE table name (field set list);
6, delete the database and delete the table:
drop Database library name;
The drop table table name;
7. Empty the record of the table:
Delete from table name;
8, display the records in the table:
SELECT * from table name;

MySQL Import and 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 Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source D:wcnc_db.sql (Note: If written as Sourced:\wcnc_db.sql, it will report the grammar

The use of load data to bulk import, this practice can be instantaneous import data, very useful!

Copy Code code as follows:

LOAD DATA [Low_priority | CONCURRENT] [local] INFILE ' File_name.txt '
[REPLACE | IGNORE]
Into TABLE tbl_name
[FIELDS field action, set separator for each field

[Terminated by ' string ']
[[optionally] enclosed by ' char ']
[Escaped by ' char ']
]
[LINES line operation, starting from 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 actions, 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);
it means, load/test/test.file into table test, use the \ t split field, write to Fieldsone and Fieldstwo, and default to line breaks as a row!