| 函數(shù)名 | 描述 |
|---|---|
| COUNT() | 返回參數(shù)字段的數(shù)量，不統(tǒng)計(jì)為NULL的記錄 |
| SUM() | 返回參數(shù)字段之和 |
| AVG() | 返回參數(shù)字段的平均值 |
| MAX() | 返回參數(shù)字段的最大值 |
| MIN() | 返回參數(shù)字段的最小值 |
| GROUP_CONCAT() | 返回符合條件的參數(shù)字段值的連接字符串 |
| JSON_ARRAYAGG() | 將符合條件的參數(shù)字段值做為單個JSON數(shù)組返回柠衍，MySQL5.7.22新增 |
| JSON_OBJECTAGG() | 將符合條件的參數(shù)字段值做為單個JSON對象返回蛉艾，MySQL5.7.22新增 |
COUNT()嘶摊、SUM()、AVG()、MAX()捞挥、MIN()和GROUP_CONCAT()函數(shù)中可以在參數(shù)前添加DISTINCT希柿，表示對不重復(fù)的記錄進(jìn)行相關(guān)操作诊沪。
COUNT()的參數(shù)設(shè)置為“*”時，表示統(tǒng)計(jì)符合條件的所有記錄（包含NULL）曾撤。
1.準(zhǔn)備
CREATE DATABASE mahaiwuji;
USE mahaiwuji;
CREATE TABLE goods (
id INT,
name VARCHAR (32),
price INT
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO goods VALUES (1,'書',10);
INSERT INTO goods VALUES (2,'鍵盤',11);
INSERT INTO goods VALUES (3,'鼠標(biāo)',15);
INSERT INTO goods VALUES (4,'手機(jī)',20);
INSERT INTO goods VALUES (5,NULL,15);
2.COUNT()
SELECT COUNT(id) FROM goods; -- 5
SELECT COUNT(name) FROM goods; -- 4,NULL不統(tǒng)計(jì)
SELECT COUNT(price) FROM goods; -- 5
SELECT COUNT(DISTINCT price) FROM goods; -- 4,因?yàn)橛?個15,不重復(fù)的數(shù)量為4
SELECT COUNT(*) FROM goods; -- 5
3.SUM()
SELECT SUM(price) FROM goods; -- 71
SELECT SUM(DISTINCT price) FROM goods; -- 56,因?yàn)橛?個15,只算了一個
4.AVG()
SELECT AVG(price) FROM goods; -- 14.2
SELECT AVG(DISTINCT price) FROM goods; -- 14,因?yàn)橛?個15,只算了一個
5.MAX()
SELECT MAX(price) FROM goods; -- 20
SELECT MAX(DISTINCT price) FROM goods; -- 20
6.MIN()
SELECT MIN(price) FROM goods; -- 10
SELECT MIN(DISTINCT price) FROM goods; -- 10
7.GROUP_CONCAT()
-- 書,鍵盤,鼠標(biāo),手機(jī)
SELECT GROUP_CONCAT(name) FROM goods;
8.JSON_ARRAYAGG()
-- ["書", "鍵盤", "鼠標(biāo)", "手機(jī)", null]
SELECT JSON_ARRAYAGG(name) FROM goods;
9.JSON_OBJECTAGG()
-- {"1": "書", "2": "鍵盤", "3": "鼠標(biāo)", "4": "手機(jī)", "5": null}
SELECT JSON_OBJECTAGG(id,name) FROM goods;
-- error端姚，因?yàn)閕d為5的name是NULL,JSON中鍵不能為NULL
SELECT JSON_OBJECTAGG(name,price) FROM goods;
