数据查询中函数的使用

STU表字段及数据

SNO        SNAME      SG       SAGE SDEPT
---------- ---------- -- ---------- --------------------
0007       赵九       男         19 NULL
0008       钱十       女         20
0001       张三       男         18 计算机
0002       李四       男         19 艺术
0003       王五       男         20 外语
0004       刘六       男         18 大数据
0005       王八       男         17 工商
0006       李七       男         19 信息管理

1.使用count函数统计表中的记录数

统计STU中所有19岁的学生人数

SELECT Count(SAGE) AS 人数
FROM test.stu
WHERE SAGE = 19

对于带有WHERE子句的PL/SQL查询语句，使用COUNT(字段名)和COUNT(*)的效果是一样的。

注意：要求统计记录不重复时，需要使用DISTINCT关键字，关键字DISTINCT应写在COUNT()函数内，并就其中不重复的列进行统计。

emp表创建及数据插入

CREATE TABLE TEST.emp (
  empno NUMBER NOT NULL,
  ename VARCHAR2(50) DEFAULT NULL,
  job VARCHAR2(50) DEFAULT NULL,
  mgr NUMBER DEFAULT NULL,
  sal NUMBER(7,2) DEFAULT NULL,
  comm NUMBER(7,2) DEFAULT NULL,
  deptno NUMBER DEFAULT NULL,
  PRIMARY KEY (empno)
) 
----------------------------------------
INSERT INTO test.emp VALUES (7369, 'SMITH', 'CLERK', 7902, 800.00, null, 20);
INSERT INTO test.emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698,  1600.00, 300.00, 30);
INSERT INTO test.emp VALUES (7521, 'WARD', 'SALESMAN', 7698, 1250.00, 500.00, 30);
INSERT INTO test.emp VALUES (7566, 'JONES', 'MANAGER', 7839, 2975.00, null, 20);
INSERT INTO test.emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698,  1250.00, 1400.00, 30);
INSERT INTO test.emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, 2850.00, null, 30);
INSERT INTO test.emp VALUES (7782, 'CLARK', 'MANAGER', 7839, 2450.00, null, 10);
INSERT INTO test.emp VALUES (7788, 'SCOTT', 'ANALYST', 7566,  3000.00, null, 20);
INSERT INTO test.emp VALUES (7839, 'KING', 'PRESIDENT', null,  5000.00, null, 10);
INSERT INTO test.emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, 1500.00, 0.00, 30);
INSERT INTO test.emp VALUES (7876, 'ADAMS', 'CLERK',7788, 1100.00, null, 20);
INSERT INTO test.emp VALUES (7900, 'JAMES', 'CLERK', 7698, 950.00, null, 30);
INSERT INTO test.emp VALUES (7902, 'FORD', 'ANALYST', 7566, 3000.00, null, 20);
INSERT INTO test.emp VALUES (7934, 'MILLER', 'CLERK', 7782, 1300.00, null, 10);

2.使用SUM函数求和

对员工表emp的sal字段进行算术求和

SELECT SUM(sal) AS 总工资
FROM test.emp

注意：SUM函数只能对数值数据类型的列求和，同时也支持DISTINCT关键字

3.使用AVG函数求平均值

求员工表emp中员工的平均工资

SELECT AVG(sal) AS 平均工资
FROM test.emp

注意：与SUM函数一样，AVG函数也只允许对数值型数据进行操作

4.返回最大值

在STU表中找出所有男生中年龄最大的是多少

SELECT MAX(sage)
FROM test.stu
WHERE sgentle='男'

注意：MAX函数可用于非数值的数据中，例如：select max(sname) from test.stu; 系统返回的是姓名sname字段的字符串最大值

5.返回最小值

在STU表中找出所有男生中年龄最小的是多少

SELECT MIN(sage)
FROM test.stu
WHERE sgentle='男'

MIN函数的使用方法和注意事项与MAX函数相同。

6.查找并替换字符串

在学生表STU中，查找SDEPT列中所有出现了“计算机”字符串的记录，并将其全部替换为“COMPUTER”

SELECT replace(SDEPT,'计算机','COMPUTER')
FROM test.STU

注意：在SELECT语句中使用REPLACE函数并不会改变数据表中的字段值，只是改变其显示在控制端的值STU表中SDEPT列中“计算机”字符串是不会被修改的。

7.获取字符的ASCII码

使用ASCII函数将“A”、“Z”、“0”、“ ”等4个字符的ASCII码输出

SELECT ASCII('A') A,ASCII('Z') Z,ASCII('0') ZERO,ASCII(' ') SPACE
FROM DUAL

注意：DUAL表是一个1行1列的表，其结构已固定，用户不能向该表进行插入删除等操作。

8.返回字符串长度

在学生表STU中，输出学号为”0001“的学生姓名字段及其长度

SELECT sname,LENGTH(sname)
FROM test.stu
WHERE sno='0001'

LENGTH函数后的括号内可以使用数据表的列名，也可以直接加字符串，用SELECT语句和DUAL表输出。

注意：LENGTH函数不仅可以统计字符串的长度，还能对数值统计位数。

9.使用ROUND函数确定精度

计算学生表STU中所有学生的平均年龄，并设置其平均年龄精确到小数点后3位

SELECT ROUND(AVG(SAGE),3) 平均年龄
FROM test.STU

实际输出时采用四舍五入来实现。

注意：当ROUND函数中取的位数为负数时，表示对小数点前的数字（即整数）进行四舍五入操作。

10.NVL函数的使用

为emp员工表中的员工发奖金，对工资SAL是1000元以下的员工，如果没有发奖金，没人发奖金100元

SELECT EMPNO,ENAME,SAL,NVL(COMM,100)
FROM test.emp
WHERE SAL < 1000

NVL函数的用法：

NVL(x,value)

该函数的功能是如果x为空，返回value，否则返回x。

NVL函数的衍生函数：NVL2函数

NVL2(x,value1,value2)

该函数的功能是如果x非空，返回value1，否则返回value2。

注意：如果NVL函数中的参数x和value的结果都为NULL值，那么NVL函数最终的返回值为NULL。