Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to connect to mysql database in cmd. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Connection: mysql-h host address-u user name-p user password (note: U and root can not add 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 username-p old password password new password

Delete authorization: revoke select,insert,update,delete om *. * fromtest2@localhost

Show database: show databases

Display data sheet: show tables

Show table structure: describe table name

Create Library: create database Library name

Delete library: drop database library name

Use Library: use Library name

Create table: create table table name (list of field settings)

Delete table: drop table table name

Modify the 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（操作前先把原来表删除） 增加列：ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADDINDEX (c); 修改列：ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b cCHAR(20); 删除列：ALTER TABLE t2 DROP COLUMN c; 备份数据库：mysql\bin\mysqldump -h(ip) -uroot -p(password) databasename>

Database.sql

Restore the database: mysql\ bin\ mysql-h (ip)-uroot-p (password) databasename

< database.sql 复制数据库：mysql\bin\mysqldump --all-databases >

All-databases.sql

Repair the database: mysqlcheck-A-o-uroot-p54safer

Text data import: load data local infile\ "File name\" into table table name

Data import and export: mysql\ bin\ mysqlimport database tables.txt

The first step, the start and stop of mysql service

Net stop mysql

Net start mysql

The second step is to log in to mysql

The syntax is as follows: mysql-u username-p user password

Type the command mysql-uroot-p, enter and prompt you to enter your password, enter 12345, and then enter the mysql. The prompt for mysql is:

Mysql >

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

Third, add new users

Format: grant permission on database. * to user name @ login host identified by "password"

For example, add a user's user1 password to password1, so that it can log in on the local computer, and have the authority 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 be able to log in to mysql on any machine, change localhost to "%".

If you don't want user1 to have a password, you can issue another command to remove the password.

Grant select,insert,update,delete on mydb.* touser1@localhost

Identified by ""

The fourth trick: operate the database

Log in to mysql and run the following commands at the mysql prompt, each ending with a semicolon.

1. Display the list of databases.

Show databases

There are two databases by default: mysql and test. Mysql stores the system and user rights information of mysql. When we change passwords and add new users, we actually operate on this 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 and delete databases:

Create database library name

Drop database library name

5. Create a table:

Use library name

Create table table name (field list)

Drop table table name

6. Clear the records in the table:

Delete from table name

7. Display the records in the table:

Select * from table name

Fifth move, export and import data

1. Export data:

Mysqldump-- opt test > mysql.test

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

For example: mysqldump-u root-p123456-- databases dbname > mysql.dbname

Is to export the database dbname to the file mysql.dbname.

two。 Import data:

Mysqlimport-u root-p123456

< mysql.dbname。 不用解释了吧。 3. 将文本数据导入数据库: 文本数据的字段数据之间用tab键隔开。 use test; load data local infile "文件名" into table 表名; 1:使用SHOW语句找出在服务器上当前存在什么数据库： mysql>

SHOW DATABASES

2:2, create a database MYSQLDATA

Mysql > CREATE DATABASE MYSQLDATA

3: select the database you created

Mysql > USE MYSQLDATA; (if Database changed appears by pressing enter key, 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 (20), sexCHAR (1))

6: display the structure of the table:

Mysql > DESCRIBE MYTABLE

7: add records to the table

Mysql > insert into MYTABLE values ("hyq", "M")

8: load data into database tables in text (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 the table

Mysql > drop TABLE MYTABLE

11: clear the table

Mysql > delete from MYTABLE

12: update data in the table

Mysql > update MYTABLE set sex= "f" where name='hyq';13: backup database mysqldump-u root library name > xxx.data14:

Example 2: connect to MYSQL on a remote host

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

Mysql-h210.110.110.110-uroot-pabcd123

(note: U and root do not have to add spaces, and so do others)

3. Exit the MYSQL command: exit

(1) Connect to MYSQL:

Format: mysql-h host address-u user name-p user password

1. Example 1: connect to the MYSQL on this computer

First, open the DOS window, then go to the bin directory under the mysql installation directory, for example: d:\ mysql\ bin, and then type the command mysql-uroot-p. Enter and prompt you to enter your password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly. The prompt for MYSQL is: mysql >

Example 2: connect to the MYSQL on the remote host

Suppose the IP of the remote host is 10.0.0.1, the user name is root, and the password is 123. Type the following command:

Mysql-h20.0.0.1-uroot-p123

(note: U and root do not have to add spaces, and so do others)

3. Exit the MYSQL command

Exit (enter)

(2) change the password:

Format: mysqladmin-u username-p old password password new password

1. Example 1: add a password to root. First enter the directory C:\ mysql\ bin under 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: change the password of root to 456

Mysqladmin-uroot-pab12password 456

(3) add new users: (note: unlike the above, the following is a command in the MYSQL environment, so it is 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's test1 password to abc, so that he can log in on any host and have the authority 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"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your mysql database on any computer on internet and can do whatever he or she wants with your data.

Example 2, add a user's test2 password to abc, so that he can only log in on localhost, and can 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 even if the user knows the test2 password, he can not access the database directly from the internet, only through the web page on the MYSQL host.

Grantselect,insert,update,delete on mydb.* to test2@localhost identifiedby "abc"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grantselect,insert,update,delete on mydb.* to test2@localhost identifiedby ""

(4) display command

1. Display a list of databases:

Show databases

At the beginning, there were only two databases: mysql and test. The mysql library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library:

Use mysql; / / Open the library

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in 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

two。 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 adds a drop table before each create statement

4. Import database

Common source commands

Go to the mysql database console

Such as mysql-u root-p

Mysql > use database

Then use the source command, followed by a script file (such as .sql used here)

Mysql > sourced: wcnc_db.sql (Note: if written as sourced:\ wcnc_db.sql, the grammar will be reported.

Use load data to import data in bulk, which can import data instantly, which is very useful!

The copy code is as follows: 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 line operation, starting with a character, to a character

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES] Line operation, ignoring a line

[(col_name_or_user_var,...)] Field operation, the fields written correspond to the data

[SET col_name = expr,...)]

Example: load data infile'/ test/test.file' intotable 'test' fields terminated by "\ t" (fieldsOne,fieldsTwo)

This means loading / test/test.file into the table test, using\ t to split the field, and writing it to fieldsOne and fieldsTwo. By default, the newline character is used as a line split!

After reading the above, have you mastered how to connect to the mysql database in cmd? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.