Shulou(Shulou.com)06/01 Report--

In this paper, an example is given to show that MySQL uses set functions for query operations. Share with you for your reference, the details are as follows:

COUNT function

SELECT COUNT (*) AS cust_num from customers;SELECT COUNT (c_email) AS email_num FROM customers;SELECT o_num, COUNT (f_id) FROM orderitems GROUP BY o_num

SUM function

SELECT SUM (quantity) AS items_total FROM orderitems WHERE o_num = 30005 * select o_num, SUM (quantity) AS items_total FROM orderitems GROUP BY o_num

AVG function

SELECT AVG (f_price) AS avg_price FROM fruits WHERE s_id = 103 bot select AVG (f_price) AS avg_price FROM fruits group by s_id

MAX function

SELECT MAX (f_price) AS max_price FROM fruits;SELECT s_id, MAX (f_price) AS max_price FROM fruits GROUP BY select MAX (f_name) from fruits

MIN function

SELECT MIN (f_price) AS min_price FROM fruits;SELECT s_id, MIN (f_price) AS min_price FROM fruits GROUP BY s_id

[example .34] query the total number of rows in the customers table

SELECT COUNT (*) AS cust_num from customers

[example .35] query the total number of customers with e-mail in the customers table and enter the following statement:

SELECT COUNT (c_email) AS email_numFROM customers

[example. 36] in the orderitems table, use the COUNT () function to count the types of fruits ordered in different order numbers

SELECT o_num, COUNT (f_id) FROM orderitems GROUP BY o_num

[example. 37] query the total amount of fruits purchased under order 30005 in the orderitems table and enter the following statement:

SELECT SUM (quantity) AS items_totalFROM orderitemsWHERE o_num = 30005

[example .38] in the orderitems table, use the SUM () function to calculate the total amount of fruits ordered in different order numbers

SELECT o_num, SUM (quantity) AS items_totalFROM orderitemsGROUP BY o_num

[example. 39] in the fruits table, query the average fruit price of the suppliers of s_id=103. The SQL statement is as follows:

SELECT AVG (f_price) AS avg_priceFROM fruitsWHERE s_id = 103

[example. 40] in the fruits table, query the average fruit price of each supplier. The SQL statement is as follows:

SELECT f_price (AVG) AS avg_priceFROM fruitsGROUP BY s_id

[example. 41] look for the most expensive fruit on the market in the fruits table, and the SQL statement is as follows:

Mysql > SELECT MAX (f_price) AS max_price FROM fruits

[example 7.42] look for the highest-priced fruits from different suppliers in the fruits table

SELECT s_id, MAX (f_price) AS max_priceFROM fruitsGROUP BY s_id

[example .43] look for the maximum value of f_name in the fruits table, and the SQL statement is as follows

SELECT MAX (f_name) from fruits

[example .44] look for the cheapest fruit on the market in the fruits table, and the SQL statement is as follows:

Mysql > SELECT MIN (f_price) AS min_price FROM fruits

[example .45] look for the cheapest fruits provided by different suppliers in the fruits table

SELECT s_id, MIN (f_price) AS min_priceFROM fruitsGROUP BY s_id

More readers who are interested in MySQL-related content can check out this site's special topics: "A Summary of MySQL Common functions", "A Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.