1.avg()函数

-- avg()函数忽略列值为null的行
SELECT AVG(id) as age FROM person WHERE age = 15;

2.count()函数

count(*) -- 对表中行的数目进行计数，不管表列中包含的是空值(null)还是非空值
count(column) -- 对特定列中具有值的行进行计数,忽略null值

3.max()函数

-- max（）函数忽略列值为null的行
SELECT max(id) as id FROM person;

4.min()函数

-- min（）函数忽略列值为null的行
SELECT min(id) as id FROM person;

5.case when()函数

-- 分情况更新语句
UPDATE Salaries
SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
WHEN salary < 10000 THEN salary * 1.2
ELSE salary END;
-- 查询转换
SELECT CASE pref_name
      WHEN '长沙' THEN '湖南' 
      WHEN '衡阳' THEN '湖南'
      WHEN '海口' THEN '海南' 
      WHEN '三亚' THEN '海南'
    ELSE '其他' END AS district,
    SUM(population) 
FROM PopTbl
GROUP BY district;

6.exists关键字

-- 慢（in查询较慢）
SELECT * 
  FROM Class_A
WHERE id IN (SELECT id 
               FROM  CLASS_B);

-- 快（表示存在）
SELECT *
  FROM Class_A A 
 WHERE EXISTS
(SELECT *  
   FROM Class_B  B
  WHERE A.id = B.id);

7.UNION 和 UNION ALL

 UNION ALL -- 全连接
 UNION -- 相同只留一条

8.FIND_IN_SET()函数

-- 函数返回指定字符串在以逗号分隔的字符串列表中的位置。常用在部门的子部门或者子部门的上级部门等场景下使用
-- 如果needle包含逗号(，)，该函数将无法正常工作
FIND_IN_SET(needle,haystack);
-- 效率和in相比，in更好
-- 分情况使用，能用in，则用in
INSERT INTO `test` VALUES (1, 'name', 'daodao,www.111cn.net,xiaoqin');
INSERT INTO `test` VALUES (2, 'name2', 'xiaohu,daodao,xiaoqin');
INSERT INTO `test` VALUES (3, 'name3', 'xiaoqin,daodao,www.111cn.net');

-- 不能使用
select id, list, name from test where 'daodao' IN ('libk', 'zyfon', 'daodao');
-- list是变量，使用find_in_set()
-- ('libk', 'zyfon', 'daodao')为常量
select id, list, name from test where find_in_set('daodao',list);

9.LOCATE()函数

-- 返回字符串str中子字符串substr的第一个出现位置, 起始位置在pos	
LOCATE(substr,str,pos)
-- 返回字符串 str中子字符串substr的第一个出现位置
SELECT LOCATE('fa',sex) FROM `user` 
SELECT * FROM `user` WHERE LOCATE('5',PASSWORD,4) AND LOCATE('5',user_name)
-- 结果：如若substr 不在str中，则返回值为0

10.CASE WHEN 使用场景

-- 第一、等值转换
SELECT NAME AS '名字', (
    CASE sex
    WHEN 0 THEN
    '女'
    WHEN 1 THEN
    '男'
    ELSE '不详'
    END )
FROM USER
-- 第二、范围转换
SELECT id AS 学号,
         name AS 姓名,
        (case
    WHEN score >= 90
    WHEN '优秀'
    WHEN score >= 70 THEN
    '良好'
    WHEN score >= 60 THEN
    '及格'
    ELSE '不及格' END) AS 学习情况
FROM student 
-- 第三、列转行操作
SELECT name as '姓名'
,MAX(CASE course WHEN '语文' THEN score ELSE 0 END) AS '语文'
,MAX(CASE course WHEN '数学' THEN score ELSE 0 END) AS '数学'
,MAX(CASE course WHEN '英语' THEN score ELSE 0 END) AS '英语'
FROM student GROUP BY name

11.group_concat()函数

-- group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
-- 以id分组，把price字段的值在同一行打印出来，逗号分隔(默认)
select id, group_concat(price) from goods group by id; 
-- 以id分组，把price字段的值在一行打印出来，分号分隔
select id,group_concat(price separator ';') from goods group by id;  
-- 以id分组，把去除重复冗余的price字段的值打印在一行，逗号分隔
select id,group_concat(distinct price) from goods group by id;  
-- 以id分组，把price字段的值去重打印在一行，逗号分隔，按照price倒序排列
select id,group_concat(DISTINCT price order by price desc) from goods group by id;  

12.group_concat()函数与FIND_IN_SET()函数连用

SELECT GROUP_CONCAT(zbxmid) FROM zcy_zbxmcd
SELECT * FROM zcy_zbxm t1
JOIN (SELECT GROUP_CONCAT(zbxmid) as id FROM zcy_zbxmcd ) t2 ON FIND_IN_SET(t1.id,t2.id)
ORDER BY t1.id
-- 可以使用in代替