Common Management language of MYSQL Database in Linux

MYSQL Database Management Language in Linux

1 Login to database

mysql -u (user) -p

enter the password

2 View database

show databases ;

3 Use of databases

use library name;

4 View tables in the library

show tables ;

5 View table structure

describe table name;

6 Creating and deleting databases

Create database name;

Drop database name;

7 Creating tables

Create table name (field 1 type, field 2 type,…, primary key);

8 Deletion table

Drop table library name. table name

9 Insert data

Insert into table names (field 1, field 2,…) values (value1, value2,…)

10 View Data Records

Select field 1, field 2,… from table name where conditional expression

11 Modify data records

Update table name set field 1= value 1, field 2= value 2… where conditional expression

12 Delete data records

Delete from table name where conditional expression

13 database security

Change password:

mysql -u root -p'old password' password 'new password'

Enter MySQL Database

In MySQL database server, guest users who access database from local machine are added by default (user and password are empty), which needs to be deleted for database security.

14 Database user authorization

① Grant authority

Grant permission list on library name. table name to username @ source address [identified by 'password']

precautions

Permission list: used to list various database operations authorized to be used, separated by commas, such as "select, insert, update" using all to indicate the permissions used

Name of the library. Table Name: The name of the library and table used to specify the authorized operation, using the wildcard "", for example, using "auth. "The objects representing authorized operations are all tables in the auth library

User name @ source address: used to specify the user name and allowed access to the client address, source address can be domain name, IP address, can also use the "%" wildcard, indicating all addresses in a certain area or segment, such as "%. taobao.com""192.168.1.% "etc.

IDENTIFIED BY: Used to set the password string used when connecting users to the database. When creating a new user, if omitted, the user password is blank.

verification

② View authorization

show grants for username @ source address

③ Revocation of authorization

revoke permission list on database name. Data table from username @ source address