SQL基础

【自用笔记】

一、Aggregate Function

AF常和GROUP BY 连用

1、COUNT

• COUNT包括numeric和non-numeric数据（其他几项AF只包括numeric**）
• COUNT() 中间不能加空格
• COUNT()会默认忽略所有null values, COUNT(*)包括所有,且星号在Aggregate Function中只能用在COUNT里

SELECT 
    COUNT(DISTINCT dept_no)
FROM
    dept_emp;

2、SUM

SELECT 
    SUM(salary)
FROM
    salaries;
-----------------------------
SELECT 
    SUM(salary)
FROM
    salaries
WHERE
    from_date > '1997-01-01'

3、MAX/MIN

SELECT 
    MAX(salary)
FROM
    salaries;
-------------------------------
SELECT 
    MIN(salary)
FROM
    salaries;

4、AVG

• AVG不能写成AVERAGE

SELECT 
    AVG(salary)
FROM
    salaries
WHERE
	from_date > '1997-01-01';

ROUND(#)

#是numeric value
ROUND(#, decimal_places)

SELECT 
    ROUND(AVG(salary))
FROM
    salaries;
# 此时结果是整数
----------------------------------------------
SELECT 
    ROUND(AVG(salary),2)
FROM
    salaries
WHERE
    from_date > '1997-01-01';
# 此时结果保留两位小数

IFNULL() and COALESCE()

IFNULL只能包含两个参数

SELECT 
    dept_no,
    IFNULL(dept_name,
            'department name not provided') AS dept_name
FROM
    department_dup;

COALESCE可以包含n个(或1个）
COALESCE(expression_1,expression_2…,expression_N)

SELECT 
    dept_no