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

What are the common commands of MySQL under the Windows system, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail, people with this need can come to learn, I hope you can get something.

1. Connect MYSQL

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

1. Connect to the MYSQL on this machine.

First open the DOS window, then enter the directory mysql/bin, and then type the command mysql-u root-p. Enter and prompt you to enter the password. Note that there can be spaces or no spaces before the user name, but there must be no spaces before the password, otherwise you will be asked to re-enter the password.

If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing enter. The prompt for MYSQL is: mysql >

2. Connect to the MYSQL on the 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-u root-p 123; (note: there can be no spaces between u and root, and so do others)

3. Exit MYSQL command: exit (enter)

Second, change the password

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

1. Add a password ab12 to root. First enter the directory mysql/bin under DOS, and then type the following command

Mysqladmin-u root-password ab12

Note: since root does not have a password at the beginning, the-p old password can be omitted.

2. Change the password of root to djg345.

Mysqladmin-u root-p ab12 password djg345

Third, 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"

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 connect to MYSQL with the root user, and then type the following command:

Grant select,insert,update,delete on *. * to "Identified by" abc "

But the addition of users is very dangerous. If someone knows the password of test1, he can log in to your mysql database on any computer on internet and do whatever he or she wants with your data. See 2 for the solution.

2. Add a user's test2 password to abc, so that he can only log in on localhost and query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located).

In this way, even if the user knows the password of test2, he cannot access the database directly from the internet and can only access it through the web page on the MYSQL host.

Grant select,insert,update,delete on mydb.* to identified by "abc"

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

Grant select,insert,update,delete on mydb.* to identified by ""

*

First, operation skills

1. If you enter the order and find that you forgot to add the semicolon, you don't have to type the command again, just hit the semicolon and enter.

In other words, you can type a complete command into several lines, and then use a semicolon as a closing mark to OK.

You can use the cursor up and down keys to call up the previous command.

Second, display commands

1. Display the list of databases in the current server:

> SHOW DATABASES

Note: the mysql library contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data tables in the database:

Mysql > USE library name

Mysql > SHOW TABLES

3. Display the structure of the data table:

Mysql > DESCRIBE table name

4. Set up the database:

Mysql > CREATE DATABASE library name

5. Establish a data sheet:

Mysql > USE library name

Mysql > CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1))

6. Delete the database:

Mysql > DROP DATABASE library name

7. Delete the data table:

Mysql > DROP TABLE table name

8. Empty the records in the table:

Mysql > DELETE FROM table name

9. Display the records in the table:

Mysql > SELECT * FROM table name

10. Insert a record into the table:

Mysql > INSERT INTO table name VALUES ("hyq", "M")

11. Update data in the table:

Mysql- > UPDATE table name SET field name 1 roomrooma name, field name 2 roomb' WHERE field name 3roomroomc'

12. Load the data into the data table in text:

Mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE table name

13. Import .sql file command:

Mysql > USE database name

Mysql > SOURCE d:/mysql.sql

14. Change the root password on the command line:

Mysql > UPDATE mysql.user SET password=PASSWORD ('new password') WHERE User='root'

Mysql > FLUSH PRIVILEGES

15. Display the database name of use:

Mysql > SELECT DATABASE ()

16. Display the current user:

Mysql > SELECT USER ()

An example of building a database and table as well as inserting data

Drop database if exists school; / / delete if SCHOOL exists

Create database school; / / build library SCHOOL

Use school; / / Open the library SCHOOL

Create table teacher / / create table TEACHER

(

Id int (3) auto_increment not null primary key

Name char (10) not null

Address varchar (50) default 'Shenzhen'

Year date

); / / end of table creation

/ / the following are insert fields

Insert into teacher values (", 'allen',' Dalian No.1 Middle School', '1976-10-10')

Insert into teacher values (", 'jack',' Dalian No.2 Middle School', '1975-12-23')

It's OK if you type the above command at the mysql prompt, but it's not easy to debug.

(1) you can write the above command to a text file as it is, assuming it is school.sql, then copy it to CMU _ file:////mysql//bin]//mysql//bin[/url] /, enter the directory [url= file:////mysql//bin]//mysql//bin[/url]] in the DOS state, and type the following command:

Mysql-uroot-p password

You can also import the school.sql file into the database by source source UniUnix. SQL;.

Transfer the text data to the database

1. The format that the text data should conform to: the field data is separated by tab key, and the null value is replaced by [url= file:////n]//n[/url]. Example:

3 rose Dalian No.2 Middle School 1976-10-10

4 mike Dalian No.1 Middle School 1975-12-23

Suppose you save these two sets of data as school.txt files and put them in the root directory of disk c.

2. Data input command load data local infile "c://school.txt" into table table name

Note: you'd better copy the files to the [url= file:////mysql//bin]//mysql//bin[/url]] directory, and type the library where the table is located with the use command.

5. Backup the database: (the command is executed in the [url= file:////mysql//bin]//mysql//bin[/url] directory] of DOS)

1. Export the entire database

The export file is stored in the mysql/bin directory by default

Mysqldump-u user name-p database name > exported file name

Mysqldump-u user_name-p123456 database_name > outfile_name.sql

two。 Export a table

Mysqldump-u user name-p database name table name > exported file name

Mysqldump-u user_name-p database_name table_name > outfile_name.sql

3. Export a database structure

Mysqldump-u user_name-p-d-add-drop-table database_name > outfile_name.sql

-d No data-add-drop-table adds a drop table before each create statement

4. Export with language parameters

Mysqldump-uroot-p-default-character-set=latin1-set-charset=gbk-skip-opt database_name > outfile_name.sql

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.