MySQL command set

One, the connectionMYSQL.
Format:mysql-hHost Address- uUser name -PUser Password
1, connect to this computerMYSQL.
First OpenDOSwindow, then enter the directoryMysql\bin, and then type the commandmysql-u root-p, you are prompted to lose the password after enter.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.
If you have just installedMYSQL, super usersRootThere is no password, so the direct return to enter theMYSQLin,MYSQLthe prompt is:mysql>
2, connect to a remote host on theMYSQL. Assume that the remote hostIPare:110.110.110.110, the user name isRoot,Password isabcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123;(Note: Uwith theRootthere can be no space between, the other is the same)
3, ExitMYSQLcommand:Exit(carriage return)
Second, change the password.
Format:mysqladmin-uUser name- POld PasswordPasswordNew Password
1, giveRootAdd a passwordAB12. First inDOSEnter the directoryMysql\bin, and then type the following command
Mysqladmin-u Root-password AB12
Note: Because at the beginningRootno password, so- Pthe old password can be omitted.
2, and then theRootthe password is changed todjg345.
Mysqladmin-u root-p ab12 Password djg345
Third, add new users.
(Note: Unlike above, the following is because it isMYSQLcommands in the environment, so a semicolon is followed as a command terminator)
Format:Grant Select onDatabase. * toUser name@Login Hostidentified by "Password"
1, add a usertest1Password isABCso that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First UseRootuser to connect inMYSQL, and then type the following command:
Grant Select,insert,update,delete on * * to [[email protected] '%][email protected] '%[/email] ' identified by ' ABC ';
But the increased user is very dangerous and you want to be like someone who knowstest1password, then he will be able toInternetlog on to any computer on yourMySQLdatabase and to your data can do whatever you see in the solution2.
2, 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),
This allows the user to use the knowntest2the 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]][email protected][/email] identified by "ABC";
If you don't want totest2There is a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "";
The next article I wasMYSQLfor database-related operations. Note: You must first log in toMYSQL, the following actions are in theMYSQLprompt, and each command ends with a semicolon.
First, the Operation skill
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 end the sign.OK.
2, you can use the cursor up and down keys to bring up previous commands.
Second, show the command
1, displays a list of databases in the current database server:
Mysql> SHOW DATABASES;
Attention:MySQLThere's a library inside .MYSQLSystem Information, we changed the password and the new user, is actually using this library to operate.
2, display the data tables in the database:
Mysql> uselibrary name;
Mysql> SHOW TABLES;
3, display the structure of the data table:

Mysql> DESCRIBETable name;
4, Database creation:
Mysql> CREATE DATABASELibrary name;
5, set up the data table:
Mysql> useLibrary name;
Mysql> CREATE TABLETable name (Field nameVARCHAR (+),Field nameCHAR (1));
6, delete the database:
Mysql> DROP DATABASELibrary name;
7, delete data tables:
Mysql> DROP TABLEtable name;
8, empty the records in the table:
Mysql> DELETE fromTable name;
9, display the records in the table:
Mysql> SELECT * FROMTable name;
10, inserting records into the table:
Mysql> INSERT intoTable nameVALUES ("HyQ", "M");
11, update the data in the table:
Mysql-> UPDATETable nameSETField name1= ' A ',Field name2= ' B ' WHEREField name3= ' C ';
12, and load the data into the data table in text mode:
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLETable name;
13, Import. SQLFile command:
Mysql> useDatabase name;
Mysql> SOURCE D:/mysql.sql;
14, command-line modificationRootPassword:
mysql> UPDATE mysql.user SET Password=password ('New Password') WHERE user= ' root ';
mysql> FLUSH privileges;
15, display Usethe database name:
Mysql> SELECT DATABASE ();
16, display the currentUser:
Mysql> SELECT USER ();
A city build and build tables and instances of inserting data
Drop database if exists school; //If there isSCHOOLthe Delete
Create Database School; //Building a librarySCHOOL
Use school; //Open LibrarySCHOOL
CREATE TABLE teacher//Create a tableTEACHER
(
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
//The following is the Insert field
Insert into teacher values (", ' Allen ', 'Dalian Yi Zhong', ' 1976-10-10′ ');
Insert into teacher values (", ' Jack ', 'Dalian Second Middle', ' 1975-12-23′ ');
If you are inMySQLthe prompt to type the above command is also possible, but not easy to debug.
（1you can write the above command as it is in a text file, assumingSchool.sql, and then copy toc:\\under, and inDOSStatus Entry Directory[Url=file://\\mysql\\bin]\\mysql\\bin[/url], and then type the following command:
Mysql-uroot-pPassword< 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 just//annotations are removed and can be used).
（2) or go to the command line and usemysql> source C:\\school.sql;You can also addSchool.sqlfile into the database.
Iv. transferring text data to the database
1, the format in which the text data should conform: between field dataTabkey to separate,NULLvalue with[Url=file://\\n]\\n[/url]to replace.Example:
3 RoseDalian Second Middle1976-10-10
4 MikeDalian Yi Zhong1975-12-23
Let's say you save these two sets of data asSchool.txtfiles, placed inCthe packing directory.
2, data incoming commandLoad Data local infile "c:\\school.txt" into tableTable name;
Note: You might want to copy the file to[Url=file://\\mysql\\bin]\\mysql\\bin[/url]directory, and first use the Usecommand to hit the library where the table is located.
V. Backing up the database: (Command inDOSof the[Url=file://\\mysql\\bin]\\mysql\\bin[/url]directory execution)
1.Export the entire database
Export file is present by defaultMysql\bindirectory under
Mysqldump-uUser name- PDatabase name>the exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql
2.Export a table
Mysqldump-uUser name- PDatabase name Table name>the 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
-DNo Data–add-drop-tablein eachCreatestatement before adding adrop table
4.export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

MySQL command set