SQL查漏补缺学习笔记（三）函数部分

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

有用的 Aggregate 函数：

• AVG() - 返回平均值
• COUNT() - 返回行数，NULL不计入内。
• FIRST() - 返回第一个记录的值，只有 MS Access 支持 FIRST() 函数。
• LAST() - 返回最后一个记录的值，只有 MS Access 支持 LAST() 函数。
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

有用的 Scalar 函数：

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符，MySql 中使用
• SubString(字段，1，end) - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的值的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入
• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

1.count()函数：

-- 查询所有记录的条数
select count(*) from access_log;

-- 查询websites 表中 alexa列中不为空的记录的条数
select count(alexa) from websites;

-- 查询websites表中 country列中不重复的记录条数
select count(distinct country) from websites;

2.first()函数：

SQL Server 语法

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;

3.group by 语句：GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

 4.HAVING子句：

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

5.exists语句：（如果想查找不存在的记录，则直接在exists前面加上not即可）

SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

6.MID() 函数用于从文本字段中提取字符。

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

Oracle 中没有 MID 函数，有 substr 函数有类似功能:

SELECT substr(name,1,4) AS ShortTitle
FROM Websites;

7.ROUND() 函数用于把数值字段舍入为指定的小数位数。

mysql> select ROUND(1.298, 1);
        -> 1.3
mysql> select ROUND(1.298, 0);
        -> 1

8.now()函数：返回当前系统的日期和时间。

SELECT name, url, Now() AS date
FROM Websites;

9.SQL FORMAT() 用于对字段的显示进行格式化。

下面的 SQL 语句从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

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