MySQL Command line usage guide

First, connect MYSQL. format: mysql-h host address -u user name-p user password 1.example 1: Connect to MYSQL on this machine . first open Dos window, then go to directory mysqlbin mysql-uroot-p , enter after you are prompted to lose the password, if you just installed mysql , Super user root is no password, so directly enter into the mysql mysql mysql > 2.example 2: Connect to MYSQL on the remote host . Assume that the IP of the remote host is: . 110.110.110, user name is root, password is abcd123. Type the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note : You and root can be used without spaces, others are the same) 3. Exit MYSQL command: exit (enter)

Second, change the password. format:mysqladmin-u username- p old password password new password 1, example 1: Add a password to root ab12. First Enter directory mysqlbinunder DOS, and then type the following command (password do not add a command character )mysqladmin-uroot Password ab12 Note: Because root does not have a password at the beginning, the- p old password can be omitted. 2, example 2: Then change the root password to djg345. mysqladmin-uroot-pab12 Password djg345

Third, add new users. (Note: Unlike the above, the following is because it is a command in a MYSQL environment, so it is followed by a semicolon as a command terminator) Format:grant Select on database . * to user name @ Login Host Identified by " password " Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First , use the root user to connect to MYSQL, and then type the following command: Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC"; But example 1 increases the user is very dangerous, you want to like someone to know test1 Password, then he can in the Internet Log on to your mysql database on any computer and do whatever you can with your data, see Example 2 for a workaround . Example2, add a usertest2Password isABC,so that he can only be inlocalhostlog on, and you can access the databaseMyDBoperations for querying, inserting, modifying, deleting (localhostrefers to the local host, i.e.MYSQLThe host where the database resides) so that the user knowstest2the password, and he couldn't get it fromInternetaccess the database directly, only through theMYSQLon the hostWebpage to access the. Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC"; If you do not want to test2 have a password, you can call another command to erase the password. Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

in the previous article we talked about login, add user, password change and other issues. In the next section, we'll look at MYSQL 's operations on the database. Note: You must first log in to MySQL , the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.

One, operation tips 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, then use a semicolon to end the sign ok 2 You can use the cursor up and down keys to bring up previous commands. But previously I used a mysql older versions are not supported. I am now using mysql-3.23.27-beta-win

second, show the command 1. Display the list of databases. show databases; just started with two databases:MySQL and test. MySQL Library is very important it has The MySQL system information, we change the password and the new user, is actually using this library to operate.

2.Display the data table in the library: Use mysql;//Open the library, learn FOXBASE must not be unfamiliar with it show tables;3, display the structure of the data table: describe table name ; 4, build the library: CREATE DATABASE name ; 5, build the table: use library name; CREATE table table name ( field Settings list ); 6. Deleting the library and deleting the table : drop Database name ; drop table name; 7. Empty the records in the table: Delete from table name ; 8.display the records in the table: SELECT * from table name ;

a city build and build tables and instances of inserting data drop database if exists school;//delete if school is presentCREATE database School;// Build Library School Use School;// Open Library School CREATE TABLE teacher// Create tables teacher ( ID int (3) auto_increment NOT NULL primary key,name Char (TEN) is not NULL,address varchar (+) Default ' Shenzhen ', Year date); End of Build table // Below is the Insert field INSERT into teacher values (' ', ' Glchengang ', ' Shenzhen one ', ' 1976-10-10 '); INSERT into teacher values (' ', ' Jack ', ' Shenzhen one ', ' 1975-12-23 ');

Note: In the table under construction

(1) set the ID to a numeric field of length 3 : Int (3) and let it automatically add one to each record : auto_increment cannot be empty: not null and let him be the main field primary key

(2) set NAME to a character field of length ten

(3) the ADDRESS is set to a character field of length, and the default value is Shenzhen. What is the difference between varchar and char , only to wait for a later article to say.

(4) set year as the Date field. It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file that is assumed to be school.sql, then copied to c \ , and the DOS State into the directory \mysql\bin, and then type the following command: mysql-uroot-p Password < c:\school.sql If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove// comment).

MySQL Command line usage guide