SQL语句GROUP BY、HAVING、EXISTS、SQL函数（Null判断、日期相关、计算数值和字符串操作 ）

目录

GROUP BY

HAVING

EXISTS

SQL函数

Null判断函数

日期数据类型及函数

计算数值和字符串操作函数 

AVG(平均值)

COUNT(数据条数)

FIRST/LAST(第一条数据)

MAX/MIN(最大值)

SUM(列总和)

UCASE/ LCASE (转换大小写)

MID(截取字符串)

LEN(字符值的长度)

ROUND(数值四舍五入)

FORMAT(数据格式转化)

FORMAT详细介绍

---

GROUP BY

根据某一列或多列进行分组（列值相同的为一组），每组返回产生一条数据（默认为第一条数据）。

Websites表

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

access_log 表

+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+

返回 access_log 各个 site_id 的访问量

统计有记录的网站的记录数量

HAVING

用于搭配函数筛选内容，因为WHERE 关键字无法与函数一起使用。

注意HAVING需要写在GROUP BY后面。

查找总访问量大于 200 的网站（见GROUP BY中的表）

EXISTS

用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

查找存在总访问量(count 字段)大于 200 的网站名和链接（见GROUP BY中的表）。

SQL函数

数据库中的内置函数。

Null判断函数

下面表示当UnitsOnOrder列中有值为null时返回0

SQL Server / MS Access

ISNULL(UnitsOnOrder,0)

Oracle

NVL(UnitsOnOrder,0)

MySQL

--两种都行
IFNULL(UnitsOnOrder,0)
COALESCE(UnitsOnOrder,0)

日期数据类型及函数

SQL Server 和 MySQL 中的 Date 函数 | 菜鸟教程

计算数值和字符串操作函数 

AVG(平均值)

对某一列取平均值

SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);

注意不能直接写count>AVG(count)，where关键字不能与函数一起使用

COUNT(数据条数)

返回符合条件的指定列的值的数目（NULL 不计入）

SELECT COUNT(count) AS nums FROM access_log

返回access_log表中列字段count不为null的总条数。

Count(*)表示返回所有条数（包括null）。

COUNT(DISTINCT count)表示列字段count不为null且去重后的总条数。

计算指定的字符为一条数据

count(case when job ='SALESMAN' then '1' end)
count(case job = 'MANAGER' then 2 end )

表示当job字段值为SALESMAN时为1条，为MANAGER时为2条

FIRST/LAST(第一条数据)

FIRST(column_name)返回指定的列中第一个（最后一个）记录的值。

只有 MS Access 支持 FIRST/LAST() 函数。可以用SELECT TOP或LIMIT加上ORDER BY来代替

MAX/MIN(最大值)

 MAX(column)返回指定列的最大/最小值。

SUM(列总和)

SUM(column_name)返回某一列数值的总和。

UCASE/ LCASE (转换大小写)

UCASE/ LCASE (column_name)把字段的值转换为大/小写。

SQL Server使用UPPER/LOWER(column_name)。

MID(截取字符串)

MID(column_name,start,length?)从某个文本字段提取字符，start为起始值，最小为1，length为可选表示返回的字符数，省略时返回剩余文本。

注意在MySql 中使用，Oracle用substr，MS SQL Server用SubString关键字代替mid。

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

LEN(字符值的长度)

LEN(column_name)返回该列字段中值的长度。

ROUND(数值四舍五入)

ROUND(column_name,decimals?)对某列数值字段进行指定小数位数的四舍五入，decimals为可选表示返回小数的位数，省略时只返回整数。

FORMAT(数据格式转化)

FORMAT(column_name,format)对某列字段值进行格式化。通常用在时间相关上。

下面会把Now函数产生的时分秒过滤掉，只显示年月日，中间用-连接。

SELECT DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;

FORMAT详细介绍

SQL FORMAT 函数 - SQL教程 - 一点教程

SQL FORMAT() 函数的使用 - 墨天轮