How to connect to mysql using cmd _ MySQL

How to connect to mysql using cmd

Connection: mysql-h host address-u user name-p user password (note: u and root do not need to add spaces, the same is true for others)
Disconnected: exit (press enter)

Create authorization: grant select on database. * to username @ login host identified by/"password /"
Change password: mysqladmin-u username-p old password new password
Delete authorization: revoke select, insert, update, delete om *. * fromtest2 @ localhost;

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

Database creation: create database name;
Delete database: drop database name;
Database used: use database name;

Create table: create table name (field setting list );
Delete table: drop table name;
Alter table t1 rename t2
Query table: select * from table name;
Clear table: delete from table name;
Backup table: mysqlbinmysqldump-h (ip)-uroot-p (password) databasenametablename> tablename. SQL
Restore table: mysqlbinmysql-h (ip)-uroot-p (password) databasenametablename <tablename. SQL (delete the original table before the operation)

ADD columns: alter table t2 ADD c int unsigned not null AUTO_INCREMENT, ADDINDEX (c );
MODIFY columns: 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
Recover database: mysql/bin/mysql-h (ip)-uroot-p (password) databasename <database. SQL
Copy database: mysql/bin/mysqldump -- all-databases> all-databases. SQL
Fix the 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, start and stop the mysql service
Net stop mysql
Net start mysql

Second, log on to mysql
Syntax: mysql-u user name-p user password
Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press Enter to enter mysql. the mysql prompt is:
Mysql>
Note: If you are connecting to another machine, you need to add a parameter-h machine IP address.

Third, add new users
Format: grant permission on database. * to username @ login host identified by "password"
For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the 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 *. * touser1 @ localhost Identified by "password1 ";
If you want the user to log on to mysql on any machine, change localhost to "% ".
If you do not want user1 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * touser1 @ localhost identified "";

Step 4: operate databases
Log on to mysql and run the following commands at the mysql prompt. each command ends with a semicolon.
1. display the database list.
Show databases;
By default, two databases are available: mysql and test. Mysql inventory contains the mysql system and user permission information. we change the password and add users, in fact, this database is actually operated.
2. display the data tables in the database:
Use mysql;
Show tables;
3. display the data table structure:
Describe table name;
4. create and delete databases:
Create database name;
Drop database name;
5. create a table:
Use database name;
Create 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;

Step 5: 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
For example, mysqldump-u root-p123456 -- databases dbname> mysql. dbname
Export the database dbname to the mysql. dbname file.
2. import data:
Mysqlimport-u root-p123456 <mysql. dbname.
No need to explain it.
3. import text data to the database:
Field data of 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 the current database on the server:
Mysql> show databases;
2. create a database named MYSQLDATA
Mysql> create database mysqldata;
3: Select the database you created
Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)
4: view the tables in the current database
Mysql> show tables;
5. create a database table
Mysql> create table mytable (name VARCHAR (20), sexCHAR (1 ));
6: Display the table structure:
Mysql> describe mytable;
7. add records to the table
Mysql> insert into MYTABLE values ("hyq", "M ");
8: load data into database tables in text mode (for example, D:/mysql.txt)
Mysql> load data local infile "D:/mysql.txt" external table MYTABLE;
9: import the. SQL file command (for example, D:/mysql. SQL)
Mysql> use database;
Mysql> source d:/mysql. SQL;
10: Delete a table
Mysql> drop table mytable;
11: clear the table
Mysql> delete from MYTABLE;
12: update table data
Mysql> update MYTABLE set sex = "f" where name = 'hyq'; 13: Backup database mysqldump-u root database name> xxx. data14:

Example 2: Connect to MYSQL on the remote host
Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: You do not need to add spaces for u and root. The same applies to others)

3. exit MYSQL command: exit

(1) connecting to MYSQL:
Format: mysql-h host address-u user name-p user password

1. Example 1: Connect to MYSQL on the local machine
First, open the DOS window, enter the bin directory under the mysql installation directory, for example, D:/mysql/bin, and then type the command mysql-uroot-p, after you press enter, you are prompted to enter the password. if you have just installed MYSQL, the super user root does not have a password, so you can directly press enter to enter MYSQL. the MYSQL prompt is: mysql>
2. Example 2: Connect to MYSQL on the remote host
Assume that the IP address of the remote host is 10.0.0.1, the user name is root, and the password is 123. Enter the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: You do not need to add spaces for u and root. The same applies to others)
3. exit MYSQL command
Exit (press enter)

(2) change the password:
Format: mysqladmin-u username-p old password new password
1. Example 1: Add a 123 password to the root user. First enter the directory C:/mysql/bin in DOS, and then type the following command:
Mysqladmin-uroot-password123
Note: Because the root account does not have a password at the beginning, the old-p password can be omitted.
2. Example 2: change the password of root to 456.
Mysqladmin-uroot-pab12password 456
(3) add new users :(Note: Unlike the above, the following commands in the MYSQL environment are followed by a semicolon as the command Terminator)
Format: grant select on database. * to username @ login host identified by "password"
Example 1: add a user named "test1" with the password "abc" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:
Grantselect, insert, update, delete on *. * to test1 @ "%" Identified by "abc ";

However, the user added in Example 1 is very dangerous. if someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. for the solution, see example 2.
Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host.
Grantselect, insert, update, delete on mydb. * to test2 @ localhost identifiedby "abc ";
If you do not want test2 to have a password, you can run another command to remove the password.
Grantselect, insert, update, delete on mydb. * to test2 @ localhost identifiedby "";
(4) display commands
1. display the database list:
Show databases;
At the beginning, there were only two databases: mysql and test. The mysql database contains the MYSQL system information. we change the password and add new users to use this database for operations.
2. display the data tables in the database:
Use mysql; // open the database
Show tables;
3. display the data table structure:
Describe table name;
4. database creation:
Create database name;
5. create a table:
Use database name;
Create table name (field setting list );
6. delete databases and tables:
Drop database name;
Drop table name;
7. clear records in the table:
Delete from table name;
8. display records in the table:
Select * from table name;

MySQL import and export command
1. export the entire database
Mysqldump-u username-p database name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
2. export a table
Mysqldump-u user name-p database name table name> exported file name
Mysqldump-u wcnc-p smgp_rj_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 adds a drop table before each create statement.

4. import the database
Common source commands
Go to the mysql database console,
For example, mysql-u root-p
Mysql> use database
Then run the source Command. the following parameter is the script File (for example,. SQL used here)
Mysql> source d: wcnc_db. SQL (note: If it is written as sourced:/wcnc_db. SQL, the syntax is reported.

Using load data to import data in batches can instantly import data, which is very useful!

Load data [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
Into table tbl_name
[FIELDS field operation, set the delimiter for each field

[Terminated by 'string']
[[OPTIONALLY] enclosed by 'char ']
[Escaped by 'char ']
]
[LINES operation, starting from a character to a character

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

[(Col_name_or_user_var,...)] field operation. the written field corresponds to the data.

[SET col_name = expr,...)]

Example: load data infile '/test. file 'Partition table 'test' fields terminated by "/t" (fieldsOne, fieldsTwo );
It means, Load/test. file to the test table, use/t to split the field, and write it into fieldsOne and fieldsTwo. by default, the line break is used as a line break!

BitsCN.com