MySQL basic use (2) DCL statements and aggregate functions

【Related learning recommendations: mysql tutorial(Video)】

DCL

DCL is relatively simple and is mainly used to grant or revoke permissions to access the database, as well as commit and rollback database transactions.

Grant/Revoke Permissions

Take granting permissions as an example. After we create a new database, we want to grant specific users access and operation permissions for the database (generally in the production environment, for the sake of security, no (will operate the database through the root user), for this we first need to create a specific user, such as test. Control-level SQL statements such as DCL are generally executed on the command line. We enter the MySQL Docker container and connect to the database. , create a new test user through the CREATE USER statement, and set the password to test:

After the creation is completed, you can see this user in the mysql.user data table:

##Host field is % means test users can connect to the MySQL server from any host.

Or view it through the command line (SQL query statement is used here):

Then we can run the

GRANT statement granttest The user has all operating permissions on the test database:

After authorization, you need to run

flush privileges; Refresh the permissions so that you can see this user in the permission list of the test database:

If we exit the current login state,

test When the user logs in, he can only see the test database because it has no operating permissions for other databases:

To revoke permissions , you need to do it as root, delete this user in the permission list, or complete it through the

REVOKE statement on the command line:

revoke all privideges on test.* from 'test'@'%';
flush privileges;

All permissions are operated here, and specific permissions can also be specified. ：

// 授予权限
grant select on test.* to 'user1'@'localhost';  /*给予查询权限*/
grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/
grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/
grant update on test.* to 'user1'@'localhost'; /*添加权限*/

// 收回权限
revoke select on test.* from 'jack'@'localhost';
revoke insert on test.* from 'jack'@'localhost';
revoke delete on test.* from 'jack'@'localhost';
revoke update on test.* from 'jack'@'localhost';

Transaction submission/rollback

Database transaction (Database Transaction) refers to a series of operations performed as a single logical unit of work (operations related to addition, deletion, modification and query of the database, including a or multiple SQL statements), either completely executed or not executed at all.

For a single SQL statement, the database system automatically executes it as a transaction. This transaction is called

implicit transaction.

To manually execute multiple SQL statements as a transaction, you can use

BEGIN to open a transaction and use COMMIT to submit a transaction. This transaction is called Explicit transactions, if an error or exception occurs during transaction execution, the transaction can be rolled back through the ROLLBACK statement.

We briefly demonstrate the operation of database transactions on the command line:

We start the transaction through the

BEGIN statement, but during execution After multiple statements, the transaction was not submitted through COMMIT. I tested the execution of these SQL statements and entered the "Browse" panel to view it. I found that no new records were inserted:

If you add

ROLLBACK to roll back the transaction after the above SQL sequence, the effect will be the same:

BEGIN；

INSERT INTO post (`title`, `content, `created_at`) VALUES ('这是一篇测试文章2', '测试内容哈哈哈', '2020-05-26 13:00:00');
INSERT INTO post (`title`, `content, `created_at`) VALUES ('这是一篇测试文章3', '测试内容哈哈哈', '2020-05-26 13:30:00');

ROLLBACK;

And if you add the

COMMIT statement at the end , you can submit the modification smoothly:

This is a brief introduction to common SQL statements and visual demonstrations in phpMyAdmin. More Many details need to be explored by yourself in conjunction with online SQL tutorials. This is not the focus of this series of tutorials, so I will not go into details here.

Aggregation functions

In addition to common SQL queries and operation statements, SQL also has some built-in aggregate functions to facilitate simple and convenient statistics of results during data query. Here we introduce several common functions: count, sum, avg, max and min.

COUNT

count The function can be used to count the total number of query results. This function is usually used when performing paging queries. In order to facilitate the direct viewing of the results, we demonstrate in the command line:

In order to improve readability when querying fields, you can specify it through as Field alias, here the post table has a total of three records, so the query result is 3.

SUM

sum can be used to sum a certain field in the statistical query results, so it can only be used for numeric type fields, here we are post A new field views is added to the table, which is used to store the number of views of the corresponding article record. In the post table structure, select to add a field after the content field and click "Execute":

Add the field The name is set to views, and its type is set to UNSIGNED INT, which represents a non-negative integer. At the same time, the default value is set to 0. The corresponding SQL statement can be passed Preview function view:

#Click "Save" to create this field, and you can see it in the table structure:

Since views has a default value, the views value of all records currently is 0:

can be passed " Edit" function to set it to the corresponding simulation value:

Next, we can sum the results through the sum function:

##AVG

avg can be used to count the average value of a field in the query results, and sum The same applies to numeric type fields. For example, we can use it to count the average number of views of all articles:

If it is a number that cannot be divisible, the average will be Accurate to four decimal places.

MAX

max can be used to get the maximum value of a numeric field in the query results. For example, to get the article information with the highest number of views, you can do this:

MySQL command line defaults to Chinese garbled characters. We can set the encoding type to

utf8mb4 through set names utf8mb4;, so that it works normally Chinese and Emoji emoticons are displayed.

In addition, the concept of

subquery is also used here, which is to use the result of one query as the condition of another query. Here we pass the maximum number of views to the parent as the result of the subquery Query is used as the query condition to obtain the corresponding article information.

MIN

Relative to

max, the min function is used to obtain the minimum value of a numeric type field in the query results, such as to obtain the browse The article information with the lowest number can be done like this:

Summary

Okay, we will briefly introduce the basic queries, operations and statistics of the MySQL database. Here, I believe you already have a basic understanding of MySQL and its operations. In the next tutorial, we will introduce to you how to connect to the MySQL database in PHP and perform add, delete, modify and query operations. Regarding some more complex operations, such as paging, grouping, connection query, association relationship, index setting and application, we will discuss it in subsequent tutorials. Demonstrate with specific examples.

This article comes from https://xueyuanjun.com/post/21656

For more related articles, please pay attention to

php mysql Column!

The above is the detailed content of MySQL basic use (2) DCL statements and aggregate functions. For more information, please follow other related articles on the PHP Chinese website!