First, connect MYSQL.
Format: mysql-h host address-u username-p password
1, connected to the machine on MYSQL.
First, open a DOS window, then enter the directory mysql \ bin, then type the command mysql-u root-p, enter the password after the prompt you lose Note before the user name can have spaces and can be no space, but there is no space before the password Otherwise, you re-enter the password.
If you have just installed MYSQL, there is no super-user root password, so you can directly enter into in the MYSQL, MYSQL prompt is: mysql>
2, connected to a remote host on MYSQL. Assuming the remote host's IP is: 188.8.131.52, user name is root, password is abcd123. Type the following command:
mysql-h184.108.40.206-u root-p 123; (Note: u and can not have spaces between the root, the other is the same)
3, exit MYSQL command: exit (Enter)
Second, change your password.
Format: mysqladmin-u username-p password old password new password
1, to add a root password ab12. First, enter the directory in DOS mysql \ bin, and then type the following command
mysqladmin-u root-password ab12
Note: Since the beginning of root without a password, so-p old password one can be omitted.
2, then the root password to djg345.
mysqladmin-u root-p ab12 password djg345
Third, the addition of new user.
(Note: the above is different because it is MYSQL following command in the environment, so the back with a semicolon as a command terminator)
Format: grant select on database .* to username @ log host identified by "password"
1, add a password for user test1 abc, so that he can log on any host, and all databases have query, insert, update, delete permissions. First of all connected with the root user MYSQL, and then type the following command:
grant select, insert, update, delete on *.* to [email = test1 @ "%] test1"%[/ email]" Identified by "abc";
However, the increase in users is very dangerous, you want to know if someone test1 password, then he can on the internet on any computer to access your mysql database and your data can do whatever they want, and the solution, see 2 .
2, add a user test2 password to abc, so that he can only log on localhost, and can query the database mydb, insert, update, delete operations (localhost means the local host, that is where the MYSQL database that host),
This user test2 password that is used to know, he can not access the database directly from the internet, only through the MYSQL host web pages visited.
grant select, insert, update, delete on mydb .* to [email = test2 @ localhost] test2 @ localhost [/ email] identified by "abc";
If you do not want test2 password, you can resort to a command to eliminate the password.
grant select, insert, update, delete on mydb .* to [email = test2 @ localhost] test2 @ localhost [/ email] identified by "";
I was next in the MYSQL database in operation. Note: You must first log into MYSQL, the following operations are carried out in the MYSQL prompt, and each command with a semicolon.
First, the operating skills
1, if you hit command, enter a semicolon after the discovery forget, you need not re-order to play again, as long as a semicolon enter it.
That means you can put into a complete command lines to play, finished with a semicolon marks the end of it OK.
2, you can use the cursor up and down keys to recall the previous command.
Second, the display command
1, shows the current database server database list:
mysql> SHOW DATABASES;
Note: mysql MYSQL database system information there, we change the password and add users actually use this library to operate.
2, display data in the database table:
mysql> USE database name;
mysql> SHOW TABLES;
3 shows the structure of the data sheet:
mysql> DESCRIBE table;
4, establish a database:
mysql> CREATE DATABASE database name;
5, a data table:
mysql> USE database name;
mysql> CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1));
6, delete the database:
mysql> DROP DATABASE database name;
7, delete the data sheet:
mysql> DROP TABLE table;
8, the empty record in the table:
mysql> DELETE FROM table;
9, show records in the table:
mysql> SELECT * FROM table;
10, to insert a record into the table:
mysql> INSERT INTO table VALUES ("hyq", "M");
11, update data in the table:
mysql-> UPDATE table SET field name 1 = 'a', field name 2 = 'b' WHERE field name 3 = 'c';
12, with the text data into the data sheet:
mysql> LOAD DATA LOCAL INFILE "D: / mysql.txt" INTO TABLE table;
13 import. Sql files command:
mysql> USE database;
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, showing use of the database name:
mysql> SELECT DATABASE ();
16, displays the current user:
mysql> SELECT USER ();
Third, building a database and build a table and insert an instance of the data
drop database if exists school; / / delete if SCHOOL
create database school; / / build libraries SCHOOL
use school; / / open the library SCHOOL
create table teacher / / build table TEACHER
id int (3) auto_increment not null primary key,
name char (10) not null,
address varchar (50) default 'Shenzhen',
); / / Build the 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 are in the mysql prompt, type the above command can be, but is not convenient debugging.
(1) the above command you can write a text file, as is assumed to school.sql, then copy it to c: \ \, and within the state to enter the DOS directory \ \ mysql \ \ bin, and then type the following command:
mysql-uroot-p password <c: \ \ school.sql
If successful, empty out the line without any display; any error, will be prompt. (The above command has to debug, as long as you / / uncomment to use).
(2), or enter the command line using mysql> source c: \ \ school.sql; can also school.sql file into the database.
Fourth, the text data to the database
1, textual data should be consistent with the format: use the tab key between fields separated data, null values with \ \ n instead. Example:
3 rose Dalian II 1976-10-10
4 mike Dalian one 1975-12-23
Suppose you put two sets of data saved as school.txt file in the c root directory.
2, data incoming command load data local infile "c: \ \ school.txt" into table table name;
Note: You'd better copy the file to \ \ mysql \ \ bin directory, and hit the table first with the use command where the library.
Fifth, the backup database: (command in DOS \ \ mysql \ \ bin directory, execute)
1 Export to export the entire database file is there by default mysql \ bin directory
mysqldump-u username-p database name> Export file name
mysqldump-u user_name-p123456 database_name> outfile_name.sql
2 Export a table
mysqldump-u username-p database name watches> Export file name
mysqldump-u user_name-p database_name table_name> outfile_name.sql
3. Exporting a database structure
mysqldump-u user_name-p-d-add-drop-table database_name> outfile_name.sql
-D there is no data-add-drop-table before each create statement to add a drop table
4 with parameters derived language
mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt database_name> outfile_name.sql
Data import and export
Command line to export the database:
1, enter the MySQL directory to the bin folder: cd MySQL bin folder in the directory as I entered the command line: cd C: \ Program Files \ MySQL \ MySQL Server 4.1 \ bin
2, export the database: mysqldump-u username-p database name> Export file name as I enter the command line: mysqldump-u root-p news> news.sql (entered into the MySQL will let you enter the password)
(If you export a single table, then enter the database name and table name to the back)
3, will see the automatically generated file news.sql next to the bin file
Command line to import the database:
1, will be imported. Sql files to bin files, such a path is more convenient
2, with the export of Step 1 above
3, into MySQL: mysql-u username-p
As I entered the command line: mysql-u root-p (input also will let you enter ySQL after the password)
4, MySQL-Front you want to build the new database, then an empty database, called the news as a new target database (mysql> create database news;)
5, enter: mysql> use the target database name as I enter the command line: mysql> use news;
6, import the file: mysql> source into the file name;
As I entered the command line: mysql> source news.sql;
This article comes from CSDN blog, reproduced, please indicate the source: http://blog.csdn.net/cbynietg/archive/2009/06/06/4246779.aspx