SQL语言的惯用函数_SQL

SQL语言的常用函数

本人心情：今天忽雨忽晴，一个周末又这么要过去了，总是感觉时间过得很快，不管是上班时间还是下班后的时间，总感觉时间不够用。最近博客也越来越少，有些知识用简单的文字去描述是很的抽象的，所以有时候也没什么可以写，以下是我这周抽空学的SQL语言常用函数。希望对大家有所帮助。

SQL语言的常用函数非常多。平时也许我们没经常用到，但是要用时，却不知如何使用。在这里，我举例几个常用的SQL常用函数；

字符函数

事例一：检索商品表，把商品价格后面加一个“元”字（商品价格比如为20）

分析：这题可以使用字符函数拼接concat或是“||”这个拼接符去完成上面的需求，

select concat(price,"元") from es;或 select price||"元" from es;

事例二：检索用户表，如果用户名为空，则用“路人甲”来代替

分析：这个例子，可以看出，如果用户表中的用户名是空的，在表中只会显示空，而需求是把空的用户名换成“路人甲”，那我们可以使用NVL函数进行判断转换

select NVL(name,"路人甲") from emp;//当name有值时，就显示()中第一个内容，如果是空的，就会显示第二条“路人甲”的内容

数字函数

TRUNC函数：TRUNC(COLUMN|EXPRESSION,N);

说明：TRUNC函数是截取函数，从N中来决定截取第一个参数的多少。

1.此函数可以只有一个参数，那么此时第二个函数N将默认为0；

2.N可以是正数，也可以是负数，正整数时表示截取小数点后面的位数，负整数时表示截取小数点前的位数

事例：我们在dual表中创建如下语句（DUAL表为虚拟表，在Oracle中dual表只包含一条记录，并且任何用户都可以读取，此表常被我们用来构造我们的语法结构）

1.SELECT TRUNC(45.926) FROM dual; 结果为：45（第二个函数N没有，就默认为0，）2.SELECT TRUNC(45.926,2) FROM dual:结果为：45.92（N为正整数2，因此第一个参数位数要向小数点右边截取2，只留下2位前的数据）3.SELECT TRUNC(45.926,-1) FROM dual;结果为：40 (N为负整数-1，因此第一个参数位数要向小数点左边截取1位，因此为40)

ROUND函数：ROUND(COLUMN|EXPRESSION,N);

说明：ROUND函数与前面的TRUNC函数的截取方式是一样的，只是他截取时会四舍五入算法，我们也称ROUND函数为四舍五入函数

1.SELECT TRUNC(45.926) FROM dual; 结果为：46（第二个函数N没有，就默认为0，小数点右边的为9，则要四舍五入加1）2.SELECT TRUNC(45.926,2) FROM dual:结果为：45.93（N为正整数2，因此第一个参数位数要向小数点右边截取2，只留下2位前的数据，第三位数为6，则要四舍五入2变3）3.SELECT TRUNC(45.926,-1) FROM dual;结果为：50 (N为负整数-1，因此第一个参数位数要向小数点左边截取1位，45中的第一位数是5，则要四舍五入，因此为50，)

事例：检索商品表，在检索结果中对商品价格进行四舍五入处理（精确到小数点后两位）

SELECT ROUND(p.price,2) AS "四舍五入后的价格" FROM es p;//AS为给前面的字段加一个别名

另外，TRUNC和ROUND函数不只是可以对NUMBER类型进行操作，也可以对DATE类型的数据进行操作。因为用得少，因此在这就不在描述，从上面的说明与事例中，就可以了解他们的作用，和区别

日期函数

SYSDATE函数：SELECT SYSDATE FROM dual;

说明：SYSDATE函数是获取当前系统时间，

事例：对商品表进行数据插入操作，并且要添加插入当前时间

INSERT INTO es VALUES(100,2,'苹果',SYSDATE,700);//其中SYSDATE为时间字段，插入了当前系统时间

EXTRACT函数：SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;

说明：EXTRACT函数是获取部分时间，比如年，月，日等

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;//<span style="font-family: Arial, Helvetica, sans-serif;">获取当前系统时间的年</span>

SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;//<span style="font-family: Arial, Helvetica, sans-serif;">获取当前系统时间的月</span>

SELECT EXTRACT(DAY FROM SYSDATE) FROM dual;//<span style="font-family: Arial, Helvetica, sans-serif;">获取当前系统时间的日</span>

事例：检索商品表，对检索结果中的上架时间使用年-月-日格式显示

SELECT EXTRACT(YEAR FROM p.datee) || '-'|| EXTRACT(MONTH FROM p.datee) || '-' || EXTRACT (DAY FROM p.datee) FROM es p

说明：上面我使用了前面讲的拼接符||，把年月日分别取出，然后使用拼接显示p.datee为商品表中的日期字段

转换函数

转换函数：主要用于操作多表数据类型，它的主要作用就是把数据从一种数据类型转换成另外一种数据类型，比如：

TO_CHAR：主要是对数字，日期进行转换成字符类型，常用的还是日期转成字符串格式

语法：TO_CHAR(date) 或 TO_CHAR(date,'format_model') 第二个参数为格式，

事例：检索商品表，对检索结果中的上架时间使用年-月-日格式显示

SELECT TO_CHAR(p.datee,'YYYY-MM-DD') FROM es p

TO_DATE()：主要是时间字符符转换成时间类型函数

语法：TO_DATE(date,'format_model')

事例：对商品上架日期进行比较检索，检索2012年3月份上架的商品

SELECT * FROM es p WHERE TO_DATE('2012-2-29','YYYY-MM-DD') < p.datee AND p.datee <= TO_DATE('2012-3-31','YYYY-MM-DD')

聚合函数

分组:GROUP BY column

1、AVG(n) 求取记录集中的平均值。
例如：求 tem p 表中 col2 字段数据的平均值
SELECT AVG(col2)FROM temp;
例如：根据 col1 分组，分别求每组 col2 字段数据的平均值
SELECT col1, AVG(col2)FROM temp GROUP BY col1;
2、SUM(n) 求取记录集中的总和
例如：求 tem p 表中 col2 字段数据的总和
SELECT SUM(col2)FROM temp;
例如：根据 col1 分组，分别对每组 col2 字段数据求和
SELECT col1, SUM(col2)FROM temp GROUP BY col1;
3、 CO UNT(n) 对记录集中的指定列记数
例如：查询 te m p 表的总记录数
SELECT COUNT(*)FROM temp;
例如：根据 col1 分组，分别求每组数据的记录数
SELECT col1, COUNT(*)FROM temp GROUP BY col1;
4、 M A X ( n ) 求取记录集中指定列的最大值
例如：求 tem p 表中 col2 字段数据的最大值
SELECT MAX(col2)FROM temp;
例如：根据 col1 分组，分别求每组 col2 字段数据的最大值
SELECT col1, MAX(col2)FROM temp GROUP BY col1;
5、 M I N ( n ) 求取记录集中指定列的最小值
例如：求 tem p 表中 col2 字段数据的最小值
SELECT MIN(col2)FROM temp;
例如：根据 col1 分组，分别求每组 col2 字段数据的最小值
SELECT col1, MIN(col2)FROM temp GROUP BY col1;

聚合函数与GROUP BY 分组一起结合起来用，这样更有意义。我记得我的博客中有写过分组，大家可以自行查看博客中数据库类型中的博客。