mysql 高级操作

-- --------------------------------------------------
-- mysql 函数

-- SOUNDEX() 转换为语音值,可以将一个字符串转换为描述其语音表示的字母数字模式
SELECT *
FROM USER
WHERE SOUNDEX(username) = SOUNDEX('rot');

-- NOW()返回当前系统时间
SELECT NOW();

-- AVG() 返回某列的平均值
SELECT AVG(DISTINCT prod_price) AS avg_prod_price
FROM products


-- -------------------------------------
-- ------------------------------------
-- ORDER BY 排序

-- 先按prod_price降序，再按prod_name 升序
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;


-- GROUP BY 分组
-- GROUP BY 可以按一列或多列进行分组
-- GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。

SELECT prod_id, prod_name, COUNT(vend_id) AS vend_id
FROM products GROUP BY vend_id;

-- 分组后排序

SELECT prod_id, prod_name, COUNT(vend_id) AS vend_id
FROM products GROUP BY vend_id
ORDER BY prod_id DESC;


-- --------------------------------------------------
/*
HAVING 用于对汇总的 GROUP BY 结果进行过滤。
HAVING 要求存在一个 GROUP BY 子句。
WHERE 和 HAVING 可以在相同的查询中。
HAVING vs WHERE
WHERE 和 HAVING 都是用于过滤。
HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录
*/
SELECT prod_id, prod_name, COUNT(vend_id) AS num
FROM products
WHERE prod_name IS NOT NULL
GROUP BY vend_id
HAVING COUNT(vend_id) >= 2;