Summarize MYSQL related operational commands
- 2020-05-07 20:34:54
- OfStack
Test environment: mysql 5.0.45
[note: mysql can be accessed in mysql > SELECT VERSION (); To view the database version
1. Connect MYSQL.
Format: mysql-h host address -u username -p user password
1. Connect to MYSQL on the local machine.
First open the DOS window, then enter the mysql\bin directory, then enter the command mysql-u root-p, and enter the password.
If you have just installed MYSQL, the superuser root has no password, so you can enter MYSQL directly by hitting enter. The prompt for MYSQL is mysql >
2. Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:
110.110.110-u root-p 123; (note: no Spaces are allowed between u and root, and the others are the same)
3. Exit MYSQL command: exit (enter)
2. Change your password.
Format: mysqladmin-u username -p old password password new password
1. Give root the password ab12. First enter the directory mysql\bin under DOS and then type the following command
mysqladmin -u root -password ab12
Note: since root did not have a password at the beginning, the old password 1 item of -p can be omitted.
2. Change the password of root to djg345.
mysqladmin -u root -p ab12 password djg345
3. Add new users.
(note: unlike above, the following commands are followed by a semicolon as the end of the command because they are in the MYSQL environment)
Format: grant select on database.* to username @ login host identified by "password"
1. Add one user test1 password to abc, so that he can log in on any host and have the right to query, insert, modify and delete all databases. First connect to MYSQL with the root user, and then type the following command:
grant select insert, update, delete on *. * to [email = test1 @ %] test1 @ % [/ email] "Identified by" abc ";
If someone knows the password for test1, they can log into your mysql database from any computer on internet and do whatever they want with your data.
2. Add one user whose test2 password is abc, so that he can only log in on localhost and query, insert, modify and delete the database mydb (localhost refers to the local host, namely the host where MYSQL database is located).
In this way, even if the user knows the password of test2, he cannot access the database directly from internet, but only through web page on the MYSQL host.
grant select, insert update, delete on mydb. * to test2 @ localhost [email = test2 @ localhost] [/ email] identified by "abc";
If you don't want test2 to have a password, you can type another command to cancel the password.
grant select, insert update, delete on mydb. * to test2 @ localhost [email = test2 @ localhost] [/ email] identified by "";
In the next installment, I'm going to talk about database operations in MYSQL. Note: you must first log into MYSQL. The following operations are done at the prompt of MYSQL and each command ends with a semicolon.
1. Operating skills
1. If you type a command and return it and find that you have forgotten to put a semicolon, you do not have to type the command again.
This means that you can type a complete command into a few lines and end it with a semicolon on OK.
2, you can use the cursor up and down key to bring up the previous command.
2. Display commands
1. Display the database list in the current database server:
mysql > SHOW DATABASES;
Note: the mysql library contains the system information of MYSQL. We changed the password and added new users, and we are actually operating with this library.
2. Display the data table in the database:
mysql > USE library name;
mysql > SHOW TABLES;
3. Structure of display data table:
mysql > DESCRIBE table name;
4. Database establishment:
mysql > Name of CREATE DATABASE library;
5. Data table:
mysql > USE library name;
mysql > CREATE TABLE table name (field name VARCHAR(20), field name CHAR(1));
6. Delete the database:
mysql > DROP DATABASE library name;
7. Delete data table:
mysql > DROP TABLE table name;
8. Empty the records in the table:
mysql > DELETE FROM table name;
9. Show 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. Load the data into the data table by text:
mysql > LOAD DATA LOCAL INFILE "D:/mysql txt" INTO TABLE
13. Import.sql file command:
mysql > USE database name;
mysql > SOURCE d:/mysql.sql;
14. Modify root password at the command line:
mysql > UPDATE mysql.user SET password=PASSWORD(' new password ') WHERE User='root';
mysql > FLUSH PRIVILEGES;
15. Database name of use:
mysql > SELECT DATABASE();
16. Display the current user:
mysql > SELECT USER();
3. 1 instance of database and table construction and data insertion
drop database if exists school; // delete if SCHOOL exists
create database school; // set up SCHOOL library
use school; // open 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
); // the table is finished
// below are the insert fields
insert into teacher values (", 'allen', 'dalian 1', '1976-10-10');
insert into teacher values (", 'jack', 'dalian 2', '1975-12-23');
This is fine if you type the above command at the mysql prompt, but it's not easy to debug.
(1) you can write the above command to a text file, assuming school.sql, then copy it to c:\\ and enter the directory in DOS state [url=file://\\mysql\\bin]\\mysql\\bin[/url], then type the following command:
mysql-uroot-p password < c:\\school.sql
If successful, blank 1 line without any display; If there is an error, there will be a prompt. The above command has been debugged, you just need to remove the comments of // to use it.
(2) or use mysql after entering the command line > source c: \ \ school sql; You can also import the school.sql file into the database.
4. Transfer the text data to the database
1, text data should conform to the format: field data is separated by tab key, null value is replaced by [url=file://\\n]\\n[/url].
3 rose dalian 2 middle school 1976-10-10
4 mike dalian 1 1975-12-23
Suppose you save these two sets of data as school.txt, in the c root directory.
2. Data is passed into load data local infile "c:\\school.txt" into table table name;
Note: you'd better copy the file to the [url=file://\\mysql\\bin]\\mysql\\bin[/url] directory, and type the list with the use command first.
5. Backup database: (command executed in DOS [url=file://\\mysql\\bin]\\mysql\\bin[/url] directory)
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 1 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
Export a database structure
mysqldump-user_name-p-d > outfile_name.sql
Add 1 drop table before each create statement
4. Export with language parameters
mysqldump-uroot-p > outfile_name.sql
[note: mysql can be accessed in mysql > SELECT VERSION (); To view the database version
1. Connect MYSQL.
Format: mysql-h host address -u username -p user password
1. Connect to MYSQL on the local machine.
First open the DOS window, then enter the mysql\bin directory, then enter the command mysql-u root-p, and enter the password.
If you have just installed MYSQL, the superuser root has no password, so you can enter MYSQL directly by hitting enter. The prompt for MYSQL is mysql >
2. Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:
110.110.110-u root-p 123; (note: no Spaces are allowed between u and root, and the others are the same)
3. Exit MYSQL command: exit (enter)
2. Change your password.
Format: mysqladmin-u username -p old password password new password
1. Give root the password ab12. First enter the directory mysql\bin under DOS and then type the following command
mysqladmin -u root -password ab12
Note: since root did not have a password at the beginning, the old password 1 item of -p can be omitted.
2. Change the password of root to djg345.
mysqladmin -u root -p ab12 password djg345
3. Add new users.
(note: unlike above, the following commands are followed by a semicolon as the end of the command because they are in the MYSQL environment)
Format: grant select on database.* to username @ login host identified by "password"
1. Add one user test1 password to abc, so that he can log in on any host and have the right to query, insert, modify and delete all databases. First connect to MYSQL with the root user, and then type the following command:
grant select insert, update, delete on *. * to [email = test1 @ %] test1 @ % [/ email] "Identified by" abc ";
If someone knows the password for test1, they can log into your mysql database from any computer on internet and do whatever they want with your data.
2. Add one user whose test2 password is abc, so that he can only log in on localhost and query, insert, modify and delete the database mydb (localhost refers to the local host, namely the host where MYSQL database is located).
In this way, even if the user knows the password of test2, he cannot access the database directly from internet, but only through web page on the MYSQL host.
grant select, insert update, delete on mydb. * to test2 @ localhost [email = test2 @ localhost] [/ email] identified by "abc";
If you don't want test2 to have a password, you can type another command to cancel the password.
grant select, insert update, delete on mydb. * to test2 @ localhost [email = test2 @ localhost] [/ email] identified by "";
In the next installment, I'm going to talk about database operations in MYSQL. Note: you must first log into MYSQL. The following operations are done at the prompt of MYSQL and each command ends with a semicolon.
1. Operating skills
1. If you type a command and return it and find that you have forgotten to put a semicolon, you do not have to type the command again.
This means that you can type a complete command into a few lines and end it with a semicolon on OK.
2, you can use the cursor up and down key to bring up the previous command.
2. Display commands
1. Display the database list in the current database server:
mysql > SHOW DATABASES;
Note: the mysql library contains the system information of MYSQL. We changed the password and added new users, and we are actually operating with this library.
2. Display the data table in the database:
mysql > USE library name;
mysql > SHOW TABLES;
3. Structure of display data table:
mysql > DESCRIBE table name;
4. Database establishment:
mysql > Name of CREATE DATABASE library;
5. Data table:
mysql > USE library name;
mysql > CREATE TABLE table name (field name VARCHAR(20), field name CHAR(1));
6. Delete the database:
mysql > DROP DATABASE library name;
7. Delete data table:
mysql > DROP TABLE table name;
8. Empty the records in the table:
mysql > DELETE FROM table name;
9. Show 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. Load the data into the data table by text:
mysql > LOAD DATA LOCAL INFILE "D:/mysql txt" INTO TABLE
13. Import.sql file command:
mysql > USE database name;
mysql > SOURCE d:/mysql.sql;
14. Modify root password at the command line:
mysql > UPDATE mysql.user SET password=PASSWORD(' new password ') WHERE User='root';
mysql > FLUSH PRIVILEGES;
15. Database name of use:
mysql > SELECT DATABASE();
16. Display the current user:
mysql > SELECT USER();
3. 1 instance of database and table construction and data insertion
drop database if exists school; // delete if SCHOOL exists
create database school; // set up SCHOOL library
use school; // open 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
); // the table is finished
// below are the insert fields
insert into teacher values (", 'allen', 'dalian 1', '1976-10-10');
insert into teacher values (", 'jack', 'dalian 2', '1975-12-23');
This is fine if you type the above command at the mysql prompt, but it's not easy to debug.
(1) you can write the above command to a text file, assuming school.sql, then copy it to c:\\ and enter the directory in DOS state [url=file://\\mysql\\bin]\\mysql\\bin[/url], then type the following command:
mysql-uroot-p password < c:\\school.sql
If successful, blank 1 line without any display; If there is an error, there will be a prompt. The above command has been debugged, you just need to remove the comments of // to use it.
(2) or use mysql after entering the command line > source c: \ \ school sql; You can also import the school.sql file into the database.
4. Transfer the text data to the database
1, text data should conform to the format: field data is separated by tab key, null value is replaced by [url=file://\\n]\\n[/url].
3 rose dalian 2 middle school 1976-10-10
4 mike dalian 1 1975-12-23
Suppose you save these two sets of data as school.txt, in the c root directory.
2. Data is passed into load data local infile "c:\\school.txt" into table table name;
Note: you'd better copy the file to the [url=file://\\mysql\\bin]\\mysql\\bin[/url] directory, and type the list with the use command first.
5. Backup database: (command executed in DOS [url=file://\\mysql\\bin]\\mysql\\bin[/url] directory)
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 1 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
Export a database structure
mysqldump-user_name-p-d > outfile_name.sql
Add 1 drop table before each create statement
4. Export with language parameters
mysqldump-uroot-p > outfile_name.sql