Preface

DDL

DDL, Chinese is the data definition language. The characteristics of DDL are to create (create), alter (modify), drop (delete) and other operations on the objects inside the database. It is responsible for managing the basic data of the database, and does not involve the operation and change of the contents in the table.

DCL

DCL is a data control language in Chinese. The characteristics of DDL are grant (user authorization), revoke (permission recovery), commit (COMMIT), rollback (rollback) for the objects inside the database.

DML

DML, Chinese is the data operation language. The characteristics of DML are to insert (add), delete (delete), update (modify), select (query) the objects inside the database, mainly for the data in the database table.

1. Management database of DDL statements

1.1. Create database

create database test;  #Create default character set database
create database test_gbk character set gbk collate gbk_chinese_ci;  #Creating a gbk character set database
create database test_utf8 character set utf8 collate utf8_general_ci;  #Create utf8 character set database
show create database test\G  #View database creation statement

1.2. Display database

show databases;  #View all databases
show databases like 'test';  #Database matching test string
show databases like 'test%';  #%Is a wildcard to match all databases starting with test

1.3. Switch database

use test;  #Switch to test library
select database();  #View the library name of the current administrator

1.4. View the table information contained in the database

1.4.1. Switch to database to view table information

use test;
show tables;

1.4.2. View the table information outside the library

show tables from test;  #View the tables contained in the specified library test
show tables in test_gbk;
show tables from mysql like 'db%';  #Matches the table that contains the beginning of the specified character

1.5. Delete database

drop database test_gbk;  #Delete the test GBK database
show databases like 'test_gbk';

2. Manage users of DDL & & DCL statements

2.1. View the user list of the current database

Viewing the database user list is part of the responsibility of DML.

select user,host from mysql.user;  #Here, the select keyword represents query, which is one of the keywords of DML statement. User and host are the fields of MySQL table to be searched. from represents where to search. mysql.user is the user table in MySQL library. The standard user of the database consists of "user" @ "host name". The two together are the unique identification of the database user.

2.2. Create database user

create user test1@localhost identified by '123456';
select user,host from mysql.user;  #View all database users
create user test2@'192.168.1.%' identified by '123456';
select user,host from mysql.user where user='test2;  #View specified database users
show grants for test2@'192.168.1.%';
#USAGE indicates connection permission

2.3. Delete database user

drop user 'test1'@'localhost';  #Delete database user command
select user,host from mysql.user where user='test1';  #Check database users after deletion
flush privileges;  #After processing the user, it will take effect on the database. If the database changes, execute this command

delete from mysql.user where user='test2' and host='192.168.1.%';
flush privileges;

2.4. Authorized database users

2.4.1. Create a test3 user with all permissions to the test database. It is allowed to log in to the management database from the localhost host. The password is 123456.

grant all privileges on test.* to 'test3'@'localhost' identified by '123456';
select user,host from mysql.user where user='test3';
show grants for 'test3'@'localhost';
#ALL PRIVILEGES is the authorized authority

2.4.2. Authorize the system user authority of the same status as root.

show grants for root@localhost;  #View root's permissions
grant all on *.* to 'system'@'localhost' identified by '123456' with grant option;
grant proxy on ''@'' to 'system'@'localhost' with grant option;  #Allow creation of proxy users
show grants for system@localhost;

2.5. Authorized authority list

show grants for 'test3'localhost';
#ALL PRIVILEGES is the authorized authority
revoke select on test.* from 'test3'@'localhost';
show grants for 'test'@'localhost';
#Permission ALL is split into more detailed permissions

Permission list of ALL PRIVILEGES of MySQL

2.6. Explanation of grant authorization in the enterprise

2.6.1. Explanation of authorization of main database users in the enterprise

In the enterprise production environment, if users connect to the database in the form of web, they should not grant all permission as much as possible, and the best way is to split authorization, for example, grant the keyword permission of DML statements suitable for web use, such as select, insert, update, delete, etc.

grant select,insert,update,delete on oldboy.* to test3@'172.16.1.%' identified by '123456';

#Note: the following three red lines should not be crossed easily when authorizing user permissions.
1,Permission cannot be used all，To apply select,insert,update,delete Etc.
2,Library cannot be used"*.*"，And Application"oldboy.*"The format is specific to the library.
3,Host not available%，Application Intranet IP Segment, i.e'192.168.0.%'Format.

Code for PHP program language to connect MySQL:

<?php
  //$link_id=mysql_connect('Database hostname ',' user ',' password ');
    $link_id=mysql_connect('172.16.1.7')
    if($link_id){
        echo "mysql successful by oldboy !";
    }else{
        echo mysql_error();
    }
?>