MYSQL common commands and practical tips

1. Connect MYSQL.  

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

1. Example 1: connect to MYSQL on the local machine.  

First open the DOS window, then enter the directory   mysqlbin, and then enter the command mysql  -uroot  -p, enter the password, if you just installed MYSQL, super user root is not password, so directly enter MYSQL, MYSQL prompt is: mysql>  

Example 2: connect to 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   -h110.110.110.110   -uroot   -pabcd123  

(note :u and root can be used without Spaces, and so on)  

3. Exit MYSQL command:   exit   (enter)  

Second, change the password.  

Format: mysqladmin  -u user name  -p old password   password   new password  

Example 1: give root the password ab12. First enter the directory mysqlbin under DOS, and then type the following command  

mysqladmin   -uroot   -password   ab12  

Note: since root did not have a password at the beginning, one entry of the -p old password can be omitted.  

2. Example 2: change the password of root to djg345.  

mysqladmin   -uroot   -pab12   password   djg345  

Third, add new users. (note: unlike above, the following commands are terminated with a semicolon because they are in the MYSQL environment.)  

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

Example 1, add a user test1 password to abc, so that he can log in on any host, and all databases have the right to query, insert, modify, delete. First connect to MYSQL for root users, and then type the following command:  

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

If someone knows the password for test1, they can log into your mysql database from any computer on internet and do whatever they want with your data. The solution is shown in example 2.  

Case 2, add a user test2 password for abc, let he can login on localhost only, and can be conducted on database mydb query, insert, modify, delete operations (localhost refers to the local host, namely MYSQL database to the host), so that users use know test2 password, he cannot directly access the database from the internet, only through web MYSQL hosts to access the page.  

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 cancel the password.  

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

In the previous post we talked about logging in, adding users, and changing passwords. In the next installment, we'll look at the database aspects of MYSQL. Note: you must first log into MYSQL. The following operations are performed at the prompt of MYSQL, and each command ends with a semicolon.  

One, operating skills  

1, if you type the command, return found that you forgot to add a semicolon, you do not have to retype the command, as long as a semicolon return can be. This means that you can type a complete command into a few lines and end it with a semicolon on OK.  

2, you can use the cursor up and down key to bring up the previous command. But an older version of MYSQL that I used before didn't support it. I'm using mysql-3.23.27-beta-win.  

Display command  

1. Display database list.  

show   databases;  

We started with two databases: mysql and test. The mysql library is very important because it contains the system information of MYSQL. We changed the password and added new users, and we actually used this library to operate.  

2. Display the data table in the library:  

use   mysql;   / / open the library and you will be familiar with FOXBASE.  

show   tables;  

3. Structure of display data table:  

describe   table name;  

4. Library construction:  

create   database   library name;  

5. Tabulations:  

use   library name;  

create   table     (list of field Settings);  

6. Delete libraries and delete tables:  

drop   database   library name;  

drop   table   table name;  

7. Empty the records in the table:  

delete   from   table name;  

8. Show the records in the table:  

select   *   from   table name;  

3. An instance  
of database and table construction and data insertion
drop   database     exists   school;   // delete  
if SCHOOL exists
create   database   school;   // set up SCHOOL  

use   school;   // open SCHOOL  

create   table   teacher   // create a 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 building  

// below is the insert field  

insert   into teacher   values (', 'glchengang', 'shenzhen no.1 middle school', '1976-10-10');  

insert     teacher   values (', 'jack', 'shenzhen no. 1 middle school', '1975-12-23');  

Note: in the table (1), set ID to the numeric field of length 3: int (3) and have it automatically add one for each record: auto_increment cannot be null: not   null (2) and make NAME to the character field of length 10 (3) and ADDRESS to the character field of length 50, and the default value is shenzhen. What's the difference between varchar and char? I'll have to wait for a future article. (4) set YEAR as the date field.  

This is fine if you type the above command at the mysql prompt, but it is not easy to debug. You can write the above command to a text file as school.sql, then copy it to c:\\, enter directory \\mysql\\bin in DOS state, and type the following command:  

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

If successful, empty a line without any display; If there is an error, there will be a prompt. The above command has been debugged, you just need to remove the comments of // to use it.  

Transfer the text data to the database  

1. The format of the text data: the field data is separated by tab key, and the null value is replaced by \\n.  

Example:  

  rose     1976-10-10  

4   mike     1975-12-23  

2, data incoming command   load       infile   \" file name \"   into   table   table name;  

Note: you'd better copy the file to the directory \\mysql\\bin, and type the library with the use command first.  

Backup database :(command to be executed in DOS \\mysql\\bin directory)  

mysqldump   --opt   school > school.bbb  

Note: backup database school to school.bbb file. Es554en.bbb is a text file with any file name.