MySQL Common commands

Today, I'd like to share with you a list of common commands that MySQL connects to specific operations. Maybe some people think there are a lot of visual tools now, there is no need to learn the specific commands, but I do not think so, there is no denying that the tools do make our work more convenient and fast, but if you really want to learn these things, these commands can not be bypassed. In short, mastering these commands is very important to a programmer. Needless to say, let's take a look at all the commands I've ordered for you!

Want to operate MySQL first to open the MySQL service,win+r--> cmd--> net start MySQL (stop is to change start to stop) so the MySQL service is turned on. Here's a list of things you can do with MySQL.

First, connect MySQL.

1. Connect to MySQL on this machine.

First of all the DOS window to jump to the Mysql/bin directory, this time we need to create a password to the default account root, Mysqladmin-u root-password 123456. Re-type the command mysql-uroot-p123456, enter after the prompt you to lose the password. Note that you can have a space before the user name, but there must be no space before the password, or let you re-enter the password.

2. Connect to MySQL on the remote host. Assume that the remote host IP is: 192.168.1.2, the user name is root, the password is 123456. Type the following command:

Mysql-h192.168.1.2-u root-p123456

3. Exit MySQL command: Exit (enter)

Second, change the password.
Format: Mysqladmin-u username-P Old password password new password
Change the root password to 654321.

Mysqladmin-u root-p123456 Password 654321 (there is also no space after-p)

third, add new users. (Note: Unlike above, the following are commands in the MySQL environment, so you must first log in to MySQL, and each command ends with a semicolon.) ）
Format: Grant Select on database. * To User name @ login host identified by "password"

Add a user mytest password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First connect to MySQL with the root user, and then type the following command:

Mysql> Grant Select,insert,update,delete on * * [email protected] "%" identified by "abcdef";

But the increase of the user is very dangerous, you want to like someone knows mytest password, then he can be on any computer on the Internet to log on to your MySQL database, your data can do whatever you like, just make the following changes to solve the problem:

Mysql> Grant Select,insert,update,delete on * * to [e-mail protected] identified by "abcdef";

If you do not want to mytest have a password, you can call another command to erase the password.

Mysql> Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

Operation Skills
1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it.
In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK.

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

Operations on databases and data tables
1. Displays the list of databases in the current database server:
Mysql> SHOW DATABASES;
Note: MySQL library has the MySQL system information, we change the password and new users, is actually using 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. Establish the database:
mysql> CREATE database name;
5. Set up the data sheet:
mysql> use library name;
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));
6. Delete the database:
mysql> DROP database name;
7. Delete Data sheet:
mysql> DROP 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 ("Data 1", "Data 2");
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 ';

Basic operation introduced almost, orders are placed in this, the most important or more practice, light see useless, use more time natural. The so-called practice practice, but also remember the first high school "sell oil Weng" bar, the inside selling oil Weng said that sentence: Weng Yue: "Without him, but hand ripe." "Nothing, but skillful skill."

MySQL Common commands