Slime: Basic command used by MySQL database

This article by show according to Lin Feng to provide friendship sponsorship, starting in the mud row World .

1 , the format of the connected database

Mysql-h ip-u user name-p password;

1.1 connecting to a remote database

Mysql-h 192.168.1.214-uroot-p123456

Can also be written as:

Mysql-h 192.168.1.214-u root-p 123456

1.2 connecting to a local database

mysql-uroot-p123456

Can also be written as:

Mysql-u root-p 123456

2 , view database version

mysql> status;

3 , view database

show databases;

4 , view Database the user in

Select User from user;

5 , change user password

5.1 Change Local Root User Password

Format: Mysqladmin-u username-P Old password password new password

mysqladmin-uroot-p123456 Password Ilanni

Note: The newly installed MySQL database, because Root does not have a password at the beginning, so the first time to modify the root user password, the-p old password can be omitted. As follows:

Mysqladmin-uroot Password 123456

5.2 Change Remote Root User Password

Mysqladmin-h 192.168.1.214-uroot-p123456 Password Ilanni

5.3 change normal user password

Format: Update user set Password=password (' New_password ') where user= ' username ';

Update user set Password=password (' 123456 ') where user= ' Ailanni ';

FLUSH privileges;--Immediate effect, no restart required

6 , create a database

Create Database Ailanni;

You can now view the database that you have created on the OS as follows:

PS aux |grep mysqld see where MySQL is running and where the data files are stored

Ls/usr/local/mysql/data

7 , using the database

Use Ailanni;

8 , delete database

Drop database Ailanni;

The database folder on the OS is deleted at the same time, as follows:

Ls/usr/local/mysql/data

9 , creating a table for the database

CREATE TABLE ilannitable (ID int (4));

Ten , view the table for the database

Show tables;

One , displaying the structure of a database table

Describe ilannitable;

A , inserting data into the table

INSERT into ilannitable values (1);

INSERT into ilannitable values (2);

- , delete the inserted data

Delete from ilannitable where id= ' 1 ';

- , clear the table

Delete from ilannitable;

the , delete a table

drop table ilannitable;

- , create users

Format: Create user [email protected] identified by ' password ';

Create user ' Ailanni ' @ '% ' identified by ' Ailanni ';

- , delete users

Drop user Alianni;

- , user authorization

Format: Grant privileges on database.table to [e-mail protected] identified by ' password ';

privileges represents permissions.

Grant all on ailanni.* to ' ailanni ' @ '% ' of ' identified by ' Ailanni ';

FLUSH privileges;

Privileges Permissions Classification:

All : be allowed to do anything ( same as root ) . This is usually used more, the actual production environment is a user to a database.

Usage: Allow login only – nothing else is allowed.

Alter: Modifies an existing data table (for example, add/Remove Columns) and index.

Create: Create a new database or data table.

Delete: Deletes the record for the table.

Drop: Deletes a data table or database.

Index: Establish or delete the indexes.

Insert: Adds a table record.

Select: Displays/searches the table's records.

Update: Modifies a record that already exists in the table.

Note When authorizing a user, do not authorize this:

Grant all on * * to ' ailanni ' @ ' percent ' identified by ' Ailanni ';

Because this is equivalent to the Ailanni this user to grant the highest privileges, with the ability to manage all databases, equivalent to the root user permissions. This is very unsafe.

+ , view user permissions

Show grants for ' ailanni ' @ '% ';

- , user rights Delete

Format: Revoke privilege on database.table from [email protected] identified by ' password ';

Revoke all on ailanni.* from ' ailanni ' @ '% ' identified by ' Ailanni ';

+ , database backup

Format: Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql;

Mysqldump-h 192.168.1.214-uroot-p123456 Ailanni >ailanni.sql

A , Database recovery

Mysql-hhostname-uusername-ppassword databasename< Backupfile.sql;

MySQL database recovery is exactly the same as backup.

at , about database user and database deletion order

When you delete a database or a user, you must first delete the permission. Then the database and the user are deleted, otherwise unpredictable errors will occur.

Slime: Basic command used by MySQL database