MySQL Common commands, slightly modified

(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 will be prompted to enter the password,

If you have just installed MySQL, the Super User Root has no password, so 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 password 123

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-pab12 password 456

(3) operation User: (Note: The following commands in the MySQL environment are followed by a ";" as the command Terminator)

Format: grant select [, insert] [, update] [, delete] 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:
Grant select, 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 ),

In this way, the user knows the password of Test2 and cannot directly access the database from the Internet. He can only access the database through the web page on the MySQL host.
Grant select, insert, update, delete on mydb. * To Test2 @ localhost identified by "ABC ";

If you do not want Test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * To Test2 @ localhost identified "";

Example 3: Add a user with a blank root password so that the user can log on to any host and have all permissions on all databases.

Mysql> grant all privileges on *. * to root @ "% ";
Mysql> flush privileges;
Example 4: delete a user wiki.
Mysql> drop user wiki @ localhost;
Mysql> flush privileges;
(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 database name;

5. Create a table:

Use Database Name;
Create Table Name (field setting list );

6. Delete databases and tables:

Drop database 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;

(5) view version information

# Be sure not to enter the last ";" in the MySQL Command Line

\ S

(6). Export Database files from the database:
1. Export the database mydb to the E: \ mysql \ mydb. SQL file:
Open start> RUN> Enter cmd to enter Command Line Mode
C: \> mysqldump-H localhost-u root-P mydb> E: \ mysql \ mydb. SQL
Enter the password and wait for a moment until the export is successful. You can check whether the export is successful in the target file.
2. Export mytable in mydb to the E: \ mysql \ mytable. SQL file:
C: \> mysqldump-H localhost-u root-P mydb mytable> E: \ mysql \ mytable. SQL
3. Export the database mydb structure to the E: \ mysql \ mydb_stru. SQL file:
C: \> mysqldump-H localhost-u root-P mydb -- add-drop-Table> E: \ mysql \ mydb_stru. SQL

(7). import data from an external file to the database:
Import the SQL statements in the file into the database from E: \ mysql \ mydb2. SQL:
1. Enter MySQL from the command line, and then run the create database mydb2 command to create the database mydb2.
2. To exit MySQL, enter the command exit or quit;
3. Enter the following command in cmd:
C: \> mysql-H localhost-uroot-P mydb2 <E: \ mysql \ mydb2. SQL and then enter the password.

Mysql-H localhost-uroot-P mydb2 indicates a specific database.

(8) Let's talk about how to solve the import file size restriction problem:
By default, MySQL has a limit on the size of the imported file. The maximum size is 2 MB. Therefore, when the file is large, it cannot be imported directly. The following is a solution to this problem:
1. Modify related parameters in PHP. ini:
There are three parameters that affect the size of the MySQL import file:
Memory_limit = 128 M, upload_max_filesize = 2 m, post_max_size = 8 m
Modify upload_max_filesize = 200 M. Modify the size that meets your needs,
You can modify two other memory_limit = 250 m post_max_size = 200 m at the same time.
In this way, the. SQL file below MB can be imported.

More please see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html