MySQL common commands
- 2020-05-06 11:44:27
- OfStack
Many friends have MySQL installed but don't know how to use it. In this article, we'll learn some of the common MySQL commands from connecting MySQL, changing passwords, adding users, and so on.
1. Connect MySQL.
Format: mysql -h host address -u username -p user password
1. Example 1: connect to MYSQL on the local machine.
First, open the DOS window, then enter the directory mysql\bin, and then enter the command mysql -uroot -p, enter the command mysql -uroot -p, enter the password, if you just installed MySQL, super user root is not password, so directly enter MySQL The prompt for MySQL is: mysql>
2. Example 2: connect to MySQL on the remote host.
Suppose 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:
mysql -h110.110.110.110 -uroot -pabcd123
(note :u and root can be used without Spaces, and so on)
3, exit MySQL command: exit (enter)
2. Change your password.
Format: mysqladmin -u user name -p old password password new password
Example 1: add a password ab12 to root. First enter the directory mysqlbin 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 of -p can be omitted.
2. Example 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 terminated with a semicolon because they are in the MySQL environment.)
grant select on database
Example 1, add a user test1 password abc, so that he can log in on any host, and all databases have the right to query, insert, modify, delete. First use root user to connect MySQL, and then type the following command:
grant select, insert, update, delete on *.* to test1@"%" 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. The solution is shown in example 2.
Case 2, add a user test2 password for abc, let he can login on localhost only, and can be the database mydb query, insert, modify, delete operations (localhost refers to the local host, namely MYSQL database to the host), so that users use know test2 password, he cannot directly access the database from the internet, only through MYSQL web page to access the host.
grant select, insert, update,delete on mydb.* to test2@localhost 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 identified by "";
In the previous post we talked about logging in, adding users, and changing passwords. In the next installment, we'll look at the database aspects of MYSQL. Note: you must first log into MYSQL. The following operations are performed at the prompt of MYSQL, and each command ends with a semicolon.
1. Operating skills
1, if you type the command, return found that you forgot to add a semicolon, you do not have to retype the command, as long as a semicolon return can be. This means that you can type a complete command into a few lines and end it with a semicolon.
2, you can use the cursor up and down key to bring up the previous command. But an older version of MYSQL that I used before didn't support it. I'm using mysql-3.23.27-beta-win.
display command
1. Display database list.
show databases;
We started with two databases: mysql and test. The mysql library is very important because it contains the system information of MySQL , so we change the password and add new users, and we actually operate with this library.
2. Display the data table in the library:
use mysql; // open the library and you will be familiar with FOXBASE
show tables;
3. Structure of display data table:
describe table name;
4. Library construction:
create database library name;
5. Form:
use library name;
create table table name ;
6. Delete libraries and delete tables:
drop database library name;
drop table table name;
7. Empty the record in the table:
delete from table name;
8. Show the records in the table:
select * from table name;
3. An instance of database and table construction and data insertion
drop database if exists school; // delete
if SCHOOL exists create database school; // SCHOOL
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
); // end of construction
// below is the insert field
insert into values (', 'glchengang', 'shenzhen no. 1 middle school', '1976-10-10');
insert teacher values (', 'jack', 'shenzhen no.1 middle school', '1975-12-23');
Note: In the table (1), set ID to the numeric field int(3) with length 3 and have it automatically add one to each record, auto_increment cannot be empty not null and let it be the primary field primary key (2) NAME NAME Set to the character field of length 50 , and the default value is shenzhen. What's the difference between varchar and char ? (4) set YEAR as the date field.
This is fine if you type the above command at the MySQL prompt, but it is not easy to debug. You can write the above command to a text file as school.sql, c:\ , then copy to c:\ , and enter the directory \mysql\bin\ in DOS state, then type the following command:
mysql -uroot p password < c:\school.sql
If successful, empty a 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.
4. Transfer the text data to the database
1. The format that text data should conform to: the field data is separated by tab key, null value is replaced by \n ,
Example:
3 rose shenzhen no.2 middle school 1976-10-10
mike 1975-12-23
2. Data incoming command
load local local infile "file name" into into table table name;
Note: you'd better copy the file to the \mysql\bin directory, and use the use command to type in the library.
5. Backup database: (command is executed in DOS \mysql\bin directory)
mysqldump --opt school > school.bbb
Note: back up school to school.bbb .
Postscript: in fact, MySQL database operation and other SQL database is similar, you had better find a book will SQL book to see. I only introduce some basic here, actually I also understand these, ha ha. The best MYSQL tutorial or "yan zi" translation of the "MYSQL Chinese reference manual" is not only free to download every relevant website, and it is the most authoritative. Unfortunately, it is not in chm format like "PHP4 Chinese manual ", which is not very convenient for finding function commands.
1. Connect MySQL.
Format: mysql -h host address -u username -p user password
1. Example 1: connect to MYSQL on the local machine.
First, open the DOS window, then enter the directory mysql\bin, and then enter the command mysql -uroot -p, enter the command mysql -uroot -p, enter the password, if you just installed MySQL, super user root is not password, so directly enter MySQL The prompt for MySQL is: mysql>
2. Example 2: connect to MySQL on the remote host.
Suppose 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:
mysql -h110.110.110.110 -uroot -pabcd123
(note :u and root can be used without Spaces, and so on)
3, exit MySQL command: exit (enter)
2. Change your password.
Format: mysqladmin -u user name -p old password password new password
Example 1: add a password ab12 to root. First enter the directory mysqlbin 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 of -p can be omitted.
2. Example 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 terminated with a semicolon because they are in the MySQL environment.)
grant select on database
Example 1, add a user test1 password abc, so that he can log in on any host, and all databases have the right to query, insert, modify, delete. First use root user to connect MySQL, and then type the following command:
grant select, insert, update, delete on *.* to test1@"%" 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. The solution is shown in example 2.
Case 2, add a user test2 password for abc, let he can login on localhost only, and can be the database mydb query, insert, modify, delete operations (localhost refers to the local host, namely MYSQL database to the host), so that users use know test2 password, he cannot directly access the database from the internet, only through MYSQL web page to access the host.
grant select, insert, update,delete on mydb.* to test2@localhost 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 identified by "";
In the previous post we talked about logging in, adding users, and changing passwords. In the next installment, we'll look at the database aspects of MYSQL. Note: you must first log into MYSQL. The following operations are performed at the prompt of MYSQL, and each command ends with a semicolon.
1. Operating skills
1, if you type the command, return found that you forgot to add a semicolon, you do not have to retype the command, as long as a semicolon return can be. This means that you can type a complete command into a few lines and end it with a semicolon.
2, you can use the cursor up and down key to bring up the previous command. But an older version of MYSQL that I used before didn't support it. I'm using mysql-3.23.27-beta-win.
display command
1. Display database list.
show databases;
We started with two databases: mysql and test. The mysql library is very important because it contains the system information of MySQL , so we change the password and add new users, and we actually operate with this library.
2. Display the data table in the library:
use mysql; // open the library and you will be familiar with FOXBASE
show tables;
3. Structure of display data table:
describe table name;
4. Library construction:
create database library name;
5. Form:
use library name;
create table table name ;
6. Delete libraries and delete tables:
drop database library name;
drop table table name;
7. Empty the record in the table:
delete from table name;
8. Show the records in the table:
select * from table name;
3. An instance of database and table construction and data insertion
drop database if exists school; // delete
if SCHOOL exists create database school; // SCHOOL
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
); // end of construction
// below is the insert field
insert into values (', 'glchengang', 'shenzhen no. 1 middle school', '1976-10-10');
insert teacher values (', 'jack', 'shenzhen no.1 middle school', '1975-12-23');
Note: In the table (1), set ID to the numeric field int(3) with length 3 and have it automatically add one to each record, auto_increment cannot be empty not null and let it be the primary field primary key (2) NAME NAME Set to the character field of length 50 , and the default value is shenzhen. What's the difference between varchar and char ? (4) set YEAR as the date field.
This is fine if you type the above command at the MySQL prompt, but it is not easy to debug. You can write the above command to a text file as school.sql, c:\ , then copy to c:\ , and enter the directory \mysql\bin\ in DOS state, then type the following command:
mysql -uroot p password < c:\school.sql
If successful, empty a 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.
4. Transfer the text data to the database
1. The format that text data should conform to: the field data is separated by tab key, null value is replaced by \n ,
Example:
3 rose shenzhen no.2 middle school 1976-10-10
mike 1975-12-23
2. Data incoming command
load local local infile "file name" into into table table name;
Note: you'd better copy the file to the \mysql\bin directory, and use the use command to type in the library.
5. Backup database: (command is executed in DOS \mysql\bin directory)
mysqldump --opt school > school.bbb
Note: back up school to school.bbb .
Postscript: in fact, MySQL database operation and other SQL database is similar, you had better find a book will SQL book to see. I only introduce some basic here, actually I also understand these, ha ha. The best MYSQL tutorial or "yan zi" translation of the "MYSQL Chinese reference manual" is not only free to download every relevant website, and it is the most authoritative. Unfortunately, it is not in chm format like "PHP4 Chinese manual ", which is not very convenient for finding function commands.