=======mysql Introduction and Overview =================
MySQL is a small relational database management system, the developer of the Swedish MySQL AB company, has now
Acquired by Sun to support various operating systems such as FreeBSD, Linux, MAC, windows, etc.
(1), can handle large data with thousands record
(2), Support common SQL statement specification
(3), portable row height, simple and compact installation
(4), good operating efficiency, rich information network support
(5), commissioning, management, optimization simple (relative to other large databases)
One, connect MySQL
Format: mysql-h host address-u user name-P user Password
1, connected to the computer on the MySQL (set up on the C drive).
"Command prompt character" window entry,
Enter the CD C:\mysql\bin and press ENTER to switch the directory to CD C:\mysql\bin
Re-type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, MySQL
The prompt is:mysql>
"Recommended change password, don't use blank password"
C:\mysql\bin Prompt Entry:
Format: Mysqladmin–u username-P Old password password new password
For example: Add a password to root ab12. Type the following command:
Mysqladmin-uroot Password AB12
2. Connect to MySQL on the remote host. Assume the remote host IP is: 18.104.22.168, the user name is root, the password is abcd123. Type the following command:
Mysql-h22.214.171.124-u Root-p 123; (Note: You can not add a space between the root and the other)
3. Exit MySQL command: Exit (enter)
Second, change the password
Format: Mysqladmin-u username-P Old password password new password. For example
1, add a password to root ab12. First enter directory Mysql\bin under DOS, and then type the following command
Mysqladmin-u Root-password AB12
2, then change the root password to djg345.
Mysqladmin-u root-p ab12 password ******
Third, create the database
1, CREATE database name;
2. GRANT select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';
3. SET PASSWORD for
' Database name ' @ ' localhost ' = old_password (' password ');
Execute 3 commands in turn to complete the database creation. Note: The Chinese "password" and "database" are the user's own needs to set.
"Now introduce some common MySQL commands."
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.
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 make the end sign OK.
2. You can use the cursor up and down keys to recall the previous command.
Ii. Common Commands
1. Displays the list of databases in the current database server:
Mysql> SHOW DATABASES;
2. Establish the database:
mysql> CREATE database name;
3. Set up the data sheet:
mysql> use library name;
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));
4. Delete the database:
mysql> DROP database name;
5. Delete Data sheet:
mysql> DROP table name;
6. Empty the records in the table:
Mysql> DELETE from table name;
7. Insert a record into the table:
mysql> INSERT into table name VALUES ("HyQ", "M");
8. Update the data in the table:
mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';
9. Load data into the data table in text mode:
mysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;
10. Import the. sql File command:
mysql> use database name;
Mysql> SOURCE D:/mysql.sql;
11, the command line to modify the root password:
mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';
mysql> FLUSH privileges;
A city build and build tables and instances of inserting data
Drop database if exists school; Delete if Sudu is present
Create DATABASE Sudu; Building a library Sudu
Use school; Open Library Sudu
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 ',
); End of Build table
The following is the Insert field
Insert into teacher values (' ', ' Allen ', ' China info Port 1 ', ' 2005-10-10 ');
Insert into teacher values (' ', ' Jack ', ' China info Port 2 ', ' 2005-12-23 '); If you type the above command at the MySQL prompt, it is not easy to debug.
(1) You can write the above command as-is to a text file, assume Sudu.sql, then copy to c:\\, and enter directory \mysql\bin in DOS, and then type the following command:
Mysql-uroot-p Password < C:\sudu.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).
(2) or enter the command line after using mysql> source C:\sudu.sql; You can also import the Sudu.sql file into the database.
Iv. transferring text data to the database
1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \ n. Example:
3 Rose China info Port 1 1976-10-10
4 Mike China info Port 2 1975-12-23
Suppose you save these two sets of data as speed sudu.txt files, placed in the C packing directory.
2, the data incoming command, load data local infile "C:\sudu.txt" into the table name;
Note: You might want to copy the file to the \mysql\bin directory, and use the using command to hit the library that contains the table.
V. BACKUP DATABASE: (command executed in DOS \mysql\bin directory)
1. Export the entire database
The export file is present in the Mysql\bin directory by default
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. Export with language parameters
Mysqldump-uroot-p--default-character-set=latin1--SET-CHARSET=GBK--skip-opt database_name > Outfile_name.sql
Create MySQL database under command line