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 astest. 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 newtestuser through theCREATE USERstatement, and set the password totest:

After the creation is completed, you can see this user in themysql.userdata table:

##Hostfield is%meanstestusers can connect to the MySQL server from any host.

GRANTstatement granttestThe user has all operating permissions on thetestdatabase:

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

testWhen the user logs in, he can only see thetestdatabase because it has no operating permissions for other databases:

REVOKEstatement on the command line:

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

// 授予权限 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';

implicit transaction.

BEGINto open a transaction and useCOMMITto submit a transaction. This transaction is calledExplicit transactions, if an error or exception occurs during transaction execution, the transaction can be rolled back through theROLLBACKstatement.

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

ROLLBACKto 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;

COMMITstatement at the end , you can submit the modification smoothly:

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,maxandmin.

COUNT

countThe 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 throughasField alias, here theposttable has a total of three records, so the query result is3.

SUM

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

Add the field The name is set toviews, and its type is set toUNSIGNED INT, which represents a non-negative integer. At the same time, the default value is set to0. 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:

Sinceviewshas a default value, theviewsvalue 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 thesumfunction:

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

maxcan 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:

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

subqueryis 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.

max, theminfunction 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:

For more related articles, please pay attention to

php mysqlColumn!

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!