1、 Connect MYSQL.
Format: mysql - h host address - u user name - p user password
1. Connect to MYSQL on the local machine.
First open the DOS window, then enter the directory mysql bin, and then type the command mysql - u root - p, and press Enter to prompt you to enter the password Note that there may or may not be a space before the user name, but there must be no space before the password, or you will be asked to re-enter the password
If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing Enter. The prompt of MYSQL is: mysql>
2. Connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:
mysql -h110.110.110.110 -u root -p 123; (Note: no space can be added between u and root, and the same applies to others)
3. Exit the MYSQL command: exit
2、 Change password.
Format: mysqladmin - u username - p old password password new password
1. Add the password ab12 to root. First enter the directory mysql bin under 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. Then change the root password to djg345.
mysqladmin -u root -p ab12 password djg345
3、 Add new users.
(Note: Different from the above, the following commands are in the MYSQL environment, so they are followed by a semicolon as the command terminator)
Format: grant select on data base . * to user name @ login host identified by "password"
1. Add a user whose test1 password is abc, so that he can log in on any host and have the right to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:
grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;
But the added users are very dangerous. If someone knows the password of test1, he can log in to your MySQL database on any computer on the Internet and do whatever he wants with your data. See 2 for the solution.
2. Add a user whose test2 password is abc, so that he can only log in on the localhost, and can query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located),
In this way, even if the user knows the password of test2, he cannot directly access the database from the Internet, and can only access it through the web page on the MYSQL host.
grant select,insert,update,delete on mydb.* to test2@localhost identified by “abc”;
If you don't want a password for test2, you can type another command to erase the password.
grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;
The next chapter is about the database operation in MYSQL. Note: You must first log in to MYSQL. The following operations are performed at the prompt of MYSQL, and each command ends with a semicolon.
1、 Operating skills
1. If you forget to add a semicolon after entering the command, you don't need to type the command again, just enter the command with a semicolon.
That is to say, you can divide a complete command into several lines to type, and then use a semicolon as the end sign.
2. You can use the up and down keys of the cursor to call up previous commands.
2、 Show Commands
1. Display the current database The server List of databases in:
mysql> SHOW DATABASES;
Note: MySQL system information is available in the MySQL database. We change passwords and add new users by using this database.
2. Display the data table in the database:
MySQL>USE database name;
mysql> SHOW TABLES;
3. Display the structure of the data table:
MySQL>DESCRIBE table name;
4. Establish database:
MySQL>CREATE DATABASE database name;
5. Create data table:
MySQL>USE database name;
Mysql>CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1));
6. Delete database:
MySQL>DROP DATABASE database name;
7. To delete a data table:
MySQL>DROP TABLE table name;
8. Clear the records in the table:
MySQL>DELETE FROM table name;
9. Show records in the table:
Mysql>SELECT * FROM table name;
10. To insert a record into a 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. Load data into the data table in text mode:
Mysql>LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE table name;
13. Import. sql file command:
MySQL>USE database name;
mysql> SOURCE d:/mysql.sql;
14. Command line to modify root password:
Mysql>UPDATE mysql. user SET password=PASSWORD ('new password ') WHERE User='root';
mysql> FLUSH PRIVILEGES;
15. Display the database name of use:
mysql> SELECT DATABASE();
16. Display the current user:
mysql> SELECT USER();
3、 An instance of database and table creation and data insertion
drop database if exists school; //Delete if SCHOOL exists
create database school; //Create library SCHOOL
use school; //Open library SCHOOL
Create table teacher//Create table TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
Address varchar (50) default 'Shenzhen',
year date
）;//End of table creation
//The following fields are inserted
Insert into teacher values (", 'allen', 'Dalian No. 1 Middle School', '1976-10-10');
Insert into teacher values (", 'jack', 'Dalian No.2 Middle School', '1975-12-23');
You can also type the above command at the mysql prompt, but it is not convenient for debugging.
(1) You can write the above command to a text file as it is, assume it is school.sql, and then copy it to c: , enter the directory mysql bin in DOS status, and then type the following command:
Mysql - uroot - p password<c: school.sql
If successful, a blank line will not be displayed; If there is any error, there will be a prompt. (The above command has been debugged. You can use it by removing the//comment.).
(2) Or enter the command line and use mysql>source c: school. sql; You can also import the school.sql file into the database.
4、 Transfer text data to the database
1. The format that the text data should conform to: the field data should be separated by the tab key, and the null value should be replaced by n Example:
3 rose Dalian No. 2 Middle School 1976-10-10
4 mike Dalian No. 1 Middle School 1975-12-23
Suppose you save these two sets of data as a school.txt file in the root directory of disk C.
2. Load data local infile "c: school. txt" into table name;
Note: You'd better copy the file to the mysql bin directory, and use the use command to print the database where the table is located.
5、 Backup database: (The command is executed in the DOS mysql bin directory)
1. Export the entire database
The export file is stored 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 adds 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
