Shulou(Shulou.com)05/31 Report--

This article mainly explains "practical command arrangement in MySQL". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "practical command arrangement in MySQL" together.

MySQL utility commands

(a) Connect MYSQL:

Format: mysql -h host address-u user name-p user password

Example 1: Connect to MYSQL on the local machine

First open the DOS window, and then enter the bin directory under the mysql installation directory, for example: D: mysql bin, and then type the command mysql -uroot -p, after the carriage prompt you enter the password, if you just installed MYSQL, super user root is no password, so enter directly into MYSQL, MYSQL prompt is: mysql>

Example 2: Connecting to MYSQL on a remote host

Assume that the IP of the remote host is 10.0.0.1, the username is root, and the password is 123. Type the following command:

mysql -h20.0.0.1 -uroot -p123

(Note: u and root can be added without spaces, and the same is true for others)

3. Exit MYSQL command

exit (carriage return)

(ii) Change password:

Format: mysqladmin -u username-p old password new password

Example 1: Add a password 123 to root. First go to the directory C:mysqlbin under DOS and type the following command:

mysqladmin -uroot -password 123

Note: Since root does not have a password at the beginning, the-p old password can be omitted.

Example 2: Change the password of root to 456

mysqladmin -uroot -pab12 password 456

3. Another way:

mysql -u root -p

use mysql

update user set password=password("new password") where user="root";

flush privileges;

(3) Add new users: (Note: Different from the above, the following commands are commands in MYSQL environment, so they are followed by a semicolon as the command terminator)

Format: grant select on database.* to username @ login host identified by "password"

Example 1: Add a user test1 password to abc, so that he can log in on any host and have query, insert, modify and delete permissions on all databases. First connect to MYSQL as root and type the following command:

grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

But example 1 added users is very dangerous, you think if someone knows test1 password, then he can log in to your mysql database on any computer on the internet and do whatever he wants with your data, see example 2 for solution.

Example 2. Add a password of user test2 as abc, so that he can only log in on localhost, and can query, insert, modify, and delete database mydb (localhost refers to the local host, that is, the host where 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, but can only access it through the web page on MYSQL 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 erase the password.

grant select,insert,update,delete on mydb.* to test2@localhost identified by "";

(iv) Display commands

1. Display database list:

show databases;

We started with two databases: mysql and test. mysql library is very important it has MYSQL system information, we change passwords and new users, in fact, is to use this library for operation.

2. Display the data tables in the library:

use mysql; //open library

show tables;

3. Display the structure of the data table:

describe table name;

4. Library establishment:

create database name;

5. Table:

use library name;

create table name (field setting list);

6. Delete database and table:

drop database name;

drop table name;

7. Clear the records in the table:

delete from table name;

8. Records in the display table:

select * from table name;

Thank you for reading, the above is the "MySQL practical command finishing" content, after the study of this article, I believe that everyone has a deeper understanding of MySQL practical command finishing this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.