Collection of mysql common commands

1. Connect to MYSQL.

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

1. Connect to MYSQL on this machine.

First open the DOS window, then enter the directory mysqlbin, and then type the command mysql -u root -p. After pressing Enter, you will be prompted to enter the password. Note that the user name can have spaces or no spaces in front of it, but there must be no spaces in front of the password, otherwise it will be You re-enter your password.

If you have just installed MYSQL, the super user root does not have a password, so just press Enter to enter MYSQL. The MYSQL prompt is: mysql>

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

mysql -h110.110.110.110 -u root -p 123; (Note: There is no need to add a space between u and root, and the same applies to others)

3. Exit the MYSQL command: exit (Enter )

2. Change the password.

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

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

mysqladmin -u root -password ab12

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

2. Change the root password to djg345.

mysqladmin -u root -p ab12 password djg345

3. Add new users.
(Note: Different from the above, the following are commands in the MYSQL environment, so they are followed by a semicolon as the command terminator)

Format: grant select on database.* to username@login host identified by "password ”

1. Add a user test1 with the password abc, so that he can log in on any host and have query, insert, modify, and delete permissions on all databases. First connect to MYSQL as the root user, and then type the following commands:

grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;

But adding more users is very dangerous. If you want someone to know the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever he wants with your data. See solution 2.

2. Add a user test2 with the password 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) ),

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

grant select,insert,update,delete on mydb.* to test2@localhost identified by “abc”;

If you don’t want test2 to have a password, you can type another command to delete the password.

grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;

The next part is about database operations in MYSQL. Note: You must first log in to MYSQL. The following operations are performed at the MYSQL prompt, and each command ends with a semicolon.

1. Operation skills

1. If you forget to add a semicolon after pressing Enter when typing a command, you don’t need to type the command again, just type a semicolon and press Enter.

That is to say, you can divide a complete command into several lines and type it with a semicolon as the end mark.

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

2. Display command

1. Display the database list in the current database server:

mysql> SHOW DATABASES;

Note: There is MYSQL system information in the mysql library. When we change the password and add a new user, it is actually Use this library to operate.

2. Display the data table in the database:

mysql> USE library name;
mysql> SHOW TABLES;

3. Display the structure of the data table:

mysql> DESCRIBE table name;

4. Create the database:

mysql> CREATE DATABASE library name;

5. Create a data table:

mysql> USE library name;
mysql> CREATE TABLE table name (field name VARCHAR(20), field name CHAR(1));

6. Delete the database:

mysql> DROP DATABASE database name;

7. Delete the data table:

mysql> DROP TABLE table name;

8. Clear the records in the table:

mysql> DELETE FROM table name;

9. Display records in the table:

mysql> SELECT * FROM table name;

10. Insert records into the table:

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

11. Update the data in the table:

mysql-> UPDATE table name SET field name 1='a', field name 2='b' WHERE field name 3='c';

12. Use text mode to Load data into the data table:

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 from 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();

3. An example of creating a database, creating a table and inserting data

drop database if school exists; //If SCHOOL exists, delete it

create database school; //Create library SCHOOL

use school; //Open 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 the inserted fields
insert into teacher values(",'allen','Dalian Yizhong',' 1976-10-10′);
insert into teacher values(”,'jack','Dalian No. 2 Middle School','1975-12-23′);

You can also type the above command at the mysql prompt, But it is not convenient for debugging.

(1) You can write the above command as it is into a text file, assuming it is school.sql, then copy it to c:\, enter the directory \mysql\bin in DOS state, and then type the following command:

mysql -uroot -p password < c:\school.sql

If successful, leave a blank line and nothing will be displayed; if there is an error, there will be a prompt. (The above command has been debugged, you only need to remove the // comment to use it).

(2) Or use mysql> source c:\school.sql; after entering the command line to import the school.sql file into the database.

4. Transfer text data to the database

1. The format that text data should conform to: field data are separated by tab keys, and null values ​​are replaced by \n. 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 place them in the root directory of drive c.

2. Data input command load data local infile “c:\school.txt” into table table name;

Note: You’d better copy the file to the \mysql\bin directory and use the use command to open it first The library where the table is located.

5. Back up the database: (The command is executed in the \mysql\bin directory of DOS)

1. Export the entire database

The export file is stored in the mysqlbin directory by default

mysqldump -u username -p database name> Exported file name

mysqldump -u user_name -p123456 database_name > outfile_name.sql

2. Export a table

mysqldump -u username -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 Add 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

1. Back up the database
mysqldump -uroot -p test_db > test_db.sql
2. Restore the database
mysql -uroot -p test_db < test_db.sql
3. Create permissions
grant all privileges on test_db.* to test_db@ 'localhost' identified by '123456';
Compatible with the mode before mysql4.1:
update mysql.user set password=old_password('123456') where user='test_db';
4. Forgot password
In "my.cnf" Or add "skip-grant-tables" to the "mysqld" configuration section of the "my.ini" file, and then restart mysql to log in and change the root password.