【二】SQL的基本函数 2.1 关系型数据库SQL命令类别 数据操纵语言:DML: select; insert; delete; update; merge. 数据定义语言:DDL: create; alter; drop; truncate; rename; comment. 事务控制语言:TCL: commit; rollback; savepoint. 数据控制语言:DCL: grant; revoke. 2.2 单行函数与多行函数 单行函数:指一行数据输入,返回一个值得函数。所以查询一个表时,对选择的每一行数据都会返回一个结果。 SQL>select empno,lower(ename) from emp; 多行函数:指多行数据输入,返回一个值得函数。所以对表的群组进行操作,并且每组返回一个结果。(典型的是聚合函数) SQL>select sum(sal) from emp; 2.3 单行函数的几种类型 2.3.1 字符型函数 lower('SQL Course')----->sql course 返回小写 upper('sql course')----->SQL COURSE 返回大学 initcap('SQL course')-----> Sql Course 每个单字返回首字母大写 concat('good','string')---->good string 拼接 只能拼接2个字符串 substr('String',1,3)---->Str 从第1位开始截取3位数, 演变:只有两个参数的 substr('String',3) 正数第三位起始,得到后面所有字符 substr('String',-2) 倒数第二位,起始,得到最后所有字符 instr('t#i#m#r#a#n#','#') --->找第一个#字符在那个绝对位置,得到的数值 Instr参数经常作为substr的第二个参数值 演变:Instr参数可有四个之多 如select instr('aunfukk','u',-1,1) from dual; 倒数第一个u是哪个位置,结果返回5 length('String')---->6 长度,得到的是数值 length参数又经常作为substr的第三个参数 lpad('first',10,'#39;)左填充 rpad(676768,10,'*')右填充 replace('JACK and JUE','J','BL')---->BLACK and BLUE trim('m' from 'mmtimranm')---->timran 两头截,这里的‘m’是截取集,仅能有一个字符 trim( ' timran ')---->timran 作用是两头去空字符 处理字符串时,利用字符型函数的嵌套组合是非常有效的: create table customers(cust_name varchar2(20)); insert into customers values('Lex De Hann'); insert into customers values('Renske Ladwig'); insert into customers values('Jose Manuel Urman'); insert into customers values('Joson Malin'); select * from customers; CUST_NAME -------------------- Lex De Hann Renske Ladwig Jose Manuel Urman Joson Malin 一共四条记录,客户有两个名的,也有三个名的,现在想列出仅有三个名的客户,且第一个名字用*号略去 答案之一: SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name,' ',1,2)<>0; CUST NAME ------------------------------------------------------------------------------------------------------------------------ *** De Hann **** Manuel Urman 分析: 先用INSTR(cust_name,' ')找出第一个空格的位置, 然后,SUBSTR(cust_name,INSTR(cust_name,' '))从第一个空格开始往后截取字符串到末尾,结果是第一个空格以后所有的字符, 最后,LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')用LPAD左填充到cust_name原来的长度,不足的部分用*填充,也就是将第一个空格前的位置,用*填充。 where后过滤是否有三个名字,INSTR(cust_name, ' ',1,2)从第一个位置,从左往右,查找第二次出现的空格,如果返回非0值,则说明有第二个空格,则有第三个名字。 2.3.2 数值型函数 round 对指定的值做四舍五入,round(p,s) s为正数时,表示小数点后要保留的位数,s也可以为负数,但意义不大。 round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9 round(45.923, 0),结果,46 round(45.923, -1),结果,50 trunc 对指定的值取整 trunc(p,s) trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9 trunc(45.923),结果,45 trunc(45.923, -1),结果, 40 mod 返回除法后的余数 SQL> select mod(100,12) from dual; 2.3.3 日期型函数 因为日期在oracle里是以数字形式存储的,所以可对它进行加减运算,计算是以天为单位。 缺省格式:DD-MON-RR. 可以表示日期范围:(公元前)4712 至(公元)9999 时间格式 SQL> select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:MI:SS') FROM dual; SQL> select sysdate+2 from dual; 当前时间+2day SQL> select sysdate+2/24 from dual; 当前时间+2hour SQL> select sysdate+2/1440 from dual; 当前时间+2分钟 SQL> select (sysdate-hiredate)/7 week from emp; 两个date类型差,结果是以天为整数位的实数。 ①MONTHS_BETWEEN 计算两个日期之间的月数 SQL>select months_between('1994-04-01','1992-04-01') mm from dual; 查找emp表中参加工作时间>30年的员工 SQL>select * from emp where months_between(sysdate,hiredate)/12>32; 很容易认为单行函数返回的数据类型与函数类型一致,对于数值函数类型而言的确如此,但字符和日期函数可以返回任何数据类型的值。比如instr函数是字符型的,months_between函数是日期型的,但它们返回的都是数值。 ②ADD_MONTHS 给日期增加月份 SQL>select hiredate,add_months(hiredate,4) from emp; ③LAST_DAY 日期当前月份的最后一天 SQL>select hiredate,last_day(hiredate) from emp; ④NEXT_DAY NEXT_DAY的第2个参数可以是数字1-7,分别表示周日--周六(考点) 比如要取下一个星期六,则应该是: SQL>select next_day(sysdate,7) FROM DUAL; ⑤ROUND(p,s),TRUNC(p,s)在日期中的应用,如何舍入要看具体情况,s是MONTH按30天计,应该是15舍16入,s是YEAR则按6舍7入计算。 SQL>SELECT empno, hiredate,round(hiredate,'MONTH') AS round,trunc(hiredate,'MONTH') AS trunc FROM emp; SQL>SELECT empno, hiredate, round(hiredate,'YEAR') AS round,trunc(hiredate,'YEAR') AS trunc FROM emp; 2.3.4 几个有用的函数和表达式 1)DECODE函数和CASE表达式: 实现sql语句中的条件判断语句,具有类似高级语言中的if-then语句的功能。 decode函数源自oracle, case表达式源自sql标准,实现功能类似,decode语法更简单些。 decode函数用法: SQL> SELECT job, sal, decode(job, 'ANALYST', SAL*1.1, 'CLERK', SAL*1.15,'MANAGER', SAL*1.20, SAL) SALARY FROM emp decode函数的另几种常见用法: SQL>select ename,job,decode(job,'MANAGER','中层干部') leader from emp; SQL> select ename,job,comm,decode (comm,null,'nonsale','sale') saleman from emp; 注:单一列处理,共四个参数:含义是:comm 如果为null就取'nonsale,否则取'sale' SQL> select sal,sign(sal-1500) from emp; SQL> select ename,decode (sign(sal-1500), 1, 'NORMAL','LOW') as "LEV" from emp; 注:sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1,含义是:工资大于1500,返回1,真取'NORMAL',假取'LOW' CASE表达式第一种用法: SQL> SELECT job, sal, case job when 'ANALYST' then SAL*1.10 when 'CLERK' then SAL*1.15 when 'MANAGER' then SAL*1.20 else sal end SALARY FROM emp CASE表达式第二种用法: SQL> SELECT job, sal, case when job='ANALYST' then SAL*1.1 when job='CLERK' then SAL*1.15 when job='MANAGER' then SAL*1.20 else sal end SALARY FROM emp 以上三种写法结果都是一样的 CASE第二种语法比第一种语法增加了搜索功能。形式上第一种when后跟定值,而第二种还可以使用表达式和比较符。 看一个例子 SQL> SELECT ename,sal,case when sal>=3000 then '高级' when sal>=2000 then '中级' else '低级' end 级别 FROM emp 再看一个例子:使用了复杂的表达式 SQL> SELECT AVG(CASE WHEN sal BETWEEN 500 AND 1000 AND JOB='CLERK' THEN sal ELSE null END) "CLERK_SAL" from emp; 比较; SQL> select avg(sal) from emp where job='CLERK'; 2)DISTINCT(去重)限定词的用法: distinct貌似多行函数,严格来说它不是函数而是select子句中的一个选项。 SQL> select distinct job from emp; 消除表行重复值。 SQL> select distinct job,deptno from emp; 重复值是后面的字段组合起来考虑的 SQL> select distinct * from emp; 消除重复记录 3)sys_context 获取环境上下文的函数(多用于应用环境) scott远程登录 SQL>select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual; -------------------------------------------------------------------------------- 192.168.0.136 SQL> select sys_context('userenv','sid') from dual; SYS_CONTEXT('USERENV','SID') -------------------------------------------------------------------------------- 129 SQL> select sys_context('userenv','terminal') from dual; SYS_CONTEXT('USERENV','TERMINAL') -------------------------------------------------------------------------------- TIMRAN-222C75E5 the end !!! @jackman 共筑美好!