A daily summary of common functions in MySQL

“This is the 24th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

preface

Many friends have learned database related operations, SQL as access and processing database standard computer language, there are a lot of functions convenient for us to call. Recently in view of the SQL function for a summary, especially to share with you.

SQL function

SQL functions are mainly divided into two categories, one is Aggregate function based on calculation, the other is Scalar function based on data processing.

Among them, the Aggregate function based on calculation mainly refers to the functions worth calculation in the database, including average value function, maximum and minimum value function, calculation sum, calculation total number of rows and so on. The Scalar function-based functions are mainly for data processing, such as transfer to upper case, transfer to lower case, data length, formatting data, string interception and other related functions. The following is an example.

This time the function will be demonstrated based on the information in the following table.

CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(255) DEFAULT NULL COMMENT 'Trade Name'.`price` decimal(10.2) DEFAULT NULL COMMENT 'price'.`discount` decimal(10.2) DEFAULT NULL COMMENT 'discount'.`freight` decimal(10.2) DEFAULT NULL COMMENT 'shipping costs'.`goods_num` int(11) DEFAULT NULL COMMENT 'Quantity of goods'.`class_type` varchar(100) DEFAULT NULL COMMENT 'Types of goods, sporting goods, clothing'.`create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='Commodity Information Sheet';
Copy the code

The screenshot of test data is as follows:

The Aggregate function

MAX()

The MAX() function returns the maximum value in the column that satisfies the condition, where NULL is not evaluated.

MAX() SELECT MAX(column name) FROM table name WHERE query condition;

An example of the MAX() function queries for the highest price in the list of goods classified as “stylistics”. Query SQL as follows:

SELECT MAX(price) FROM goods WHERE class_type="Stationery";
Copy the code

The maximum price in the query table classified as “stationery” is 10 yuan.

MIN()

The MIN() function returns the minimum value in the column that meets the criteria, where null is not evaluated.

MIN() SELECT MIN(column name) FROM table name WHERE query condition;

An example of the MIN() function queries for the lowest price in an item table classified as “clothing.” Query SQL as follows:

SELECT MIN(price) FROM goods WHERE class_type="Dress";
Copy the code

The MIN() function is executed and the lowest price classified as “clothing” in the query table is 30 yuan.

SUM()

The SUM() function computes the SUM of a specified column.

SELECT SUM FROM table_name WHERE table_name = 1 and table_name = 1;

An example of the SUM() function calculates the SUM of unit prices for all items in a table of goods; Calculate the total value of goods classified as “recreational goods” in the commodity table. SQL is as follows:

SELECT SUM(price) FROM goods; SELECT SUM(price) FROM goods; SELECT SUM(price*goods_num) FROM goods WHERE class_type="Stationery";
Copy the code

The SUM of the SUM() function is 323 yuan

Calculate the total value of goods classified as “stationery” in the commodity table 225.00 yuan.

AVG()

The AVG() function returns the average value of the specified column that meets the criteria.

SELECT AVG(AVG, AVG, AVG, AVG, AVG, AVG, AVG, AVG, AVG);

An example of the AVG() function queries the average selling price of an item in the goods table under category “stationery”.

SELECT AVG(price) FROM goods WHERE class_type="Stationery";
Copy the code

Result of the AVG() function The average price of the goods in the category of “stationery” in the query commodity table is 4.5 yuan.

COUNT()

The COUNT() function queries the total number of rows that meet the criteria.

SELECT COUNT(*) FROM table WHERE COUNT(*);

An example of the COUNT() function queries the number of categories of all items for sale in an item table.

SELECT COUNT(*) FROM goods;
Copy the code

The COUNT() function results in 8 items for sale.

FIRST()

The FIRST() function returns the value of the FIRST record in the specified field.

SELECT FIRST(*) FROM table_name WHERE table_name ORDER BY table_name;

An example of the FIRST() function

SELECT FIRST(price) FROM goods WHERE class_type="Dress" ORDER BY id DESC;
Copy the code

LAST()

LAST() returns the value of the LAST record in the specified field.

SELECT LAST(*) FROM table_name WHERE table_name ORDER BY table_name;

Example of the LAST() function

SELECT LAST(price) FROM goods WHERE class_type="Dress" ORDER BY id DESC;
Copy the code

The Scalar function

UCASE()

The UCASE() function uppercases the value of a specified column that meets a condition.

SELECT UCASE(column name) FROM table name WHERE condition;

The example UCASE() function capitalizes the name of an item in the list of items under category “stylistic”

SELECT UCASE(goods_name) FROM goods WHERE class_type="Stationery";
Copy the code

The result of the UCASE() functionChange the name of the commodity in category “stationery” to uppercase and the execution result is as follows:

LCASE()

The LCASE() function converts the value of a specified column to lowercase.

SELECT LCASE(column name) FROM table name WHERE condition;

The example of the LCASE() function converts the name of an item in an item list under category “stylistic” to lowercase

SELECT LCASE(goods_name) FROM goods WHERE class_type="Stationery";
Copy the code

The result of executing the LCASE() functionThe result is as follows:

LENGTH()

The LENGTH() function outputs the LENGTH of a specified column. Note that some databases use LEN() as a function.

SELECT LENGTH(column name) FROM table name WHERE condition;

The example of the LENGTH() function queries the item name and item name value LENGTH of the item list under the category “stylistic”.

SELECT goods_name,LENGTH(goods_name) FROM goods WHERE class_type="Stationery";
Copy the code

The query result of the LENGTH() function is as follows:

ROUND()

The ROUND() function is used to ROUND a specified number of decimal places to a specified number of numeric fields

ROUND() SELECT ROUND(name, decimal number) FROM table WHERE condition;

The example of the ROUND() function retrieves the name and price of an item in the goods table under the category “clothing”, leaving the price in three decimal places.

SELECT goods_name,ROUND(price,3) FROM goods WHERE class_type="Dress";
Copy the code

The ROUND() function retrieves the name and price of the item in the category “clothing” in the table, leaving the price three decimal places. The result is as follows:

FORMAT()

Functions of the FORMAT() function The FORMAT() function formats the specified columns that meet the criteria.

SELECT FORMAT(column name, FORMAT) FROM table name WHERE condition;

An example of the FORMAT() function uses DATE_FORMAT to FORMAT the creation time in an item table in year-month-day FORMAT.

SELECT goods_name,price,DATE_FORMAT(create_time,'%Y-%c-%d') FROM goods
Copy the code

The result of the FORMAT() function uses DATE_FORMAT to FORMAT the creation time in the merchandise table in year-month-day FORMAT. The results are as follows:

NOW()

The NOW() function returns the current system time

SELECT NOW();

An example of the NOW() function gets the time of the current database.

SELECT NOW();
Copy the code

The result of executing the NOW() function

SUBSTR()

The SUBSTR() function intercepts and displays the data that meets the requirement of adding specified columns according to certain rules. Some databases use MID() function.

SUBSTR() SELECT SUBSTR(column name, start position 1 by default, return number of characters can be null) FROM table name WHERE condition;

An example of the SUBSTR() function displays the first two characters of the item name of the category “stylistic” in the list of goods.

SELECT goods_name,SUBSTR(goods_name,1.2) FROM goods WHERE class_type="Stationery";
Copy the code

The SUBSTR() function keeps the first two characters of the name of the item in the category of “stylistic articles”. The output is as follows:

conclusion

Thank you for your reading. I hope you like it. If it is helpful to you, welcome to like it. If there are shortcomings, welcome comments and corrections. See you next time.

About the author: [Little Ajie] a love tinkering with the program ape, JAVA developers and enthusiasts. Public number [Java full stack architect] maintainer, welcome to pay attention to reading communication.