Summarize Mysql related Operations Command _mysql

Test environment: MySQL 5.0.45
"Note: You can view the database version in MySQL by mysql> SELECT version ();"
one, the connection MySQL.
Format: mysql-h host address-u user name-P user Password
1, connected to the MySQL on the computer.
First open the DOS window, and then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have spaces or no spaces, but the password must have no space before, otherwise let you re-enter the password.
If you have just installed MySQL, superuser root is no password, so direct return can enter into MySQL, the MySQL prompt is: mysql>
2, connect to the remote host MySQL. Assume the IP of the remote host is: 110.110.110.110, username is root, password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: There is no space between u and Root, others are the same)
3, exit MySQL command: Exit (enter)
Second, modify the password.
Format: Mysqladmin-u username-P Old password password new password
1, to root add a password ab12. First enter the directory Mysql\bin in DOS, and then type the following command
Mysqladmin-u Root-password AB12
Note: Since Root does not have a password at the beginning, the-p old password can be omitted.
2, and then the root of the password changed to djg345.
Mysqladmin-u root-p ab12 Password djg345
third, add new users.
(Note: Unlike the above, the following are the commands in the MySQL environment, followed by a semicolon as the command Terminator)
Format: Grant Select on database. * To User name @ login host identified by "password"
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 connect to MySQL with root user, and then type the following command:
Grant Select,insert,update,delete on *.* to [email=test1@ '%]test1@ '%[/email] ' identified by ' ABC ';
But the added user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and get your data right, see 2.
2, add a user test2 password for ABC, so that he can only login on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database is the host),
This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [Email=test2@localhost]test2@localhost[/email] identified by "ABC";
If you do not want to test2 the password, you can make another command to eliminate the password.
Grant Select,insert,update,delete on mydb.* to [Email=test2@localhost]test2@localhost[/email] identified by "";
The next article I am MySQL on the database aspects of the operation. Note: You must first log in to MySQL, and the following actions are performed under the MySQL prompt, and each command ends with a semicolon.
first, the Operation skill
1, if you hit the command, when you find that you forgot to add a semicolon, you do not need to repeat the command, as long as a semicolon to enter on it.
That is to say, you can divide a complete command into lines, and then use a semicolon as the closing sign to make it OK.
2, you can use the cursor up and down keys to pull up the previous command.
Second, display command
1. Display the list of databases in the current database server:
Mysql> show DATABASES;
Note: MySQL library has MySQL system information, we change the password and new users, is actually using this library for operation.
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, the establishment of a database:
mysql> CREATE database name;
5, the establishment of data tables:
mysql> use library name;
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));
6, delete the database:
mysql> DROP Database library name;
7, delete the data table:
mysql> DROP table name;
8. Empty the record of the table:
Mysql> DELETE from table name;
9, display the records in the table:
Mysql> SELECT * from table name;
10. Insert records into the table:
mysql> INSERT into table name VALUES ("HyQ", "M");
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 ';
12. Loading data into the data table by means of text:
mysql> LOAD DATA Local INFILE "d:/mysql.txt" to table name;
13, Import. sql File command:
mysql> use database name;
Mysql> SOURCE D:/mysql.sql;
14, the command line to modify the root password:
mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';
mysql> FLUSH privileges;
15, display the use of the database name:
Mysql> SELECT DATABASE ();
16, display the current user:
Mysql> SELECT USER ();
example of 三、一个 and table-building and inserting data
Drop database if exists school; Delete if there is school
Create Database School; Build a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (a) NOT NULL,
Address varchar default ' Shenzhen ',
Year Date
); Build Table End
The following is the Insert field
Insert into teacher values (", ' Allen ', ' Dalian One ', ' 1976-10-10′ ');
Insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′");
If you type the above command at the MySQL prompt, it's not easy to debug.
(1) You can write the above command as is written in a text file, assume the School.sql, and then copy to C:\\, and enter the directory in DOS (Url=file://\\mysql\\bin]\\mysql\\bin[/url), Then type the following command:
Mysql-uroot-p Password < C:\\school.sql
If successful, there is no display on a single line, and if there is an error, there is a hint. (The above command has been debugged and you can use it only if you remove the comment.)
(2) or after entering the command line, use mysql> source c:\\school.sql; You can also import the School.sql file into the database.
Iv. transferring text data to a database
1, the text data should conform to the format: The field data is separated by the TAB key, null value with [Url=file://\\n]\\n[/url] to replace. Example:
3 Rose Dalian II, 1976-10-10
4 Mike Dalian One 1975-12-23
Suppose you save these two sets of data as school.txt files and place them in the C-packing directory.
2, Data incoming command load infile "c:\\school.txt" into table name;
Note: You'd better copy the files to the [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory and use the using command to hit the library where the table resides.
V. BACKUP DATABASE: (command executes in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory)
1. Export the entire database
The export file defaults to the existence of the Mysql\bin directory
Mysqldump-u user name-p database name > exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql
2. Export a table
Mysqldump-u user name-P database name Table name > 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
-D No data –add-drop-table add a drop table before each CREATE statement
4. With language parameter export
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql