SQL高级查询技术一

15
使用数学函数
• 数学函数可以用于执行各种数据计算。许多编程语言都提供了大 量的数学函数，这也是编程语言最早的功能之一。 • Oracle Database 10g系统也提供了大量的数学函数，这些函数大 大增强了Oracle Database 10g系统的科学计算能力。 • Oracle Database 10g系统提供了几乎所有常用的数学函数。Oracle Database 10g系统中可用的数学函数如表6-7所示。
26
interval
• --当前时间减去7分钟的时间 select sysdate,sysdate - interval '7' MINUTE from dual --当前时间减去7小时的时间 select sysdate - interval '7' hour from dual --当前时间减去7天的时间 select sysdate - interval '7' day from dual --当前时间减去7月的时间 select sysdate,sysdate - interval '7' month from dual --当前时间减去7年的时间 select sysdate,sysdate - interval '7' year from dual --时间间隔乘以一个数字 select sysdate,sysdate - 8 *interval '2' hour from dual
EMPNO ENAME DEPTNO --------- ---------- --------7698 BLAKE 30
11
使用字符处理函数
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERE SUBSTR(job,1,5) = 'SALES';
使用TRUNC函数
• 显示45.923到小数点后两位,个位,十位
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM SYS.DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- --------------45.92 45 40
SQL高级查询技术一
1
目录
• 函数
– 字符函数 – 数字函数 – 日期函数 – 转换函数 – 其他函数
• 连接查询
– 内连接 – 外连接 – 自连接
• 子查询
– 单行子查询 – 多行子查询 – 多列子查询
2
SQL 函数
输入
arg 1 arg 2 函数
输出
函数执行任务 结果
arg n
两种SQL 函数
函数
单行函数
多行函数
单行函数
– – – – – – 操作数据项 接受参数并返回一个值 对每一返回行起作用 每一行返回一个结果 可修改数据类型 可使用嵌套
function_name (column|expression, [arg1, arg2,...])
单行函数
Character
General 单行函数
使用日期
–Oracle以内部数字格式存储日期:世纪,年,月,日, 小时,分钟,秒 –缺省的日期格式是 DD-MON-YY –SYSDATE 是返回日期和时间的函数 –DUAL是用来查看SYSDATE的虚表
日期的运算
– 从日期中加或减一个数值,以得当一个日期结果值 – 两个日期相减以便得到他们相差多少天 – 往一天中加小时时需除以24
Leabharlann Baidu
16
17
使用ROUND函数
• 显示45.923到小数点后两位,个位,十位
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM SYS.DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- -------------- ----------------45.92 46 50
常用日期函数
• MONTHS_BETWEEN函数 – MONTHS_BETWEEN（d1,d2)，返回日期d1和日期d2之间相差的月数。如果d1 小于d2则返回负数；的如果d1和d2天数相同或都是月底则返回整数；否则Oracle 以每月31天为准来计算结果的小数部分 – SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('0101-1995','MM-DD-YYYY') ) "Months" FROM DUAL ADD_MONTHS函数 – ADD_MONTHS（d,n），该函数用于返回特定日期时间d之后（之前）的n个月所 对应的日期时间 – SELECT TO_CHAR(ADD_MONTHS(hiredate,1), 'YYYY-MM-DD') "Nextmon" FROM emp NEXT_DAY函数 – NEXT_DAY(d,char)该函数用于返回指定日期后的第一个工作日(由char指定)所对 应的日期 – Select next_day(sysdate,'星期一') from dual; LAST_DAY函数 – LAST_DAY(d)用于返回特定日期所在月份的最后一天 – Select last_day(sysdate) from dual;
24
•
•
•
常用日期函数
• ROUND函数 – Round(d[,fmt])用于返回日期时间的四舍五入结果。如果fmt指定年度，则7月1日为分界线， 如果fmt指定月，则16日为分界线，如果fmt指定天，则中午12：00为分界线 – select round(to_date('2008-06-05','YYYY-MM-DD'),'day') from dual TRUNC函数 – Trunc(d[,fmt])用于截取日期时间数据
•
• Extract函数 – Extract([year,month,day,hour,minute,second] from d)从时间日期中 获得所需要的特定数据（例如取得年份、月份等） – SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL – SELECT ename, empno, hiredate FROM emp WHERE EXTRACT(YEAR FROM hiredate) > 1981 ORDER BY hiredate
– select chr(56) from dual
• Translate函数： • translate（char,from_string,to_string） 用于将字符串char的字符按照from_string和to_string的对应关系进行转换
– Select translate(„2KRW229‟,‟0123456789KRW‟,‟9999999999XXX‟) from dual
使用日期运算
SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;
ENAME ---------KING CLARK MILLER
WEEKS --------830.93709 853.93709 821.36566
• 问题:
Translate和Replace的区别？
13
练 习1
• 某公司印了一批充值卡，卡的密码是随机生成的，现在出 现这个问题：
卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清 楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成 “零”，把所有的“i”都改成“1”；
请编写一条SQL语句实现以上要求； • 数据库表名：Card；密码字段名：CardPwd； • 用两种方式实现
其他字符串函数
• Replace函数：
– select replace('itpub oracle','oracle','training') from dual
• ASCII函数：
– select ascii(„a‟) ”a” ,ascii(„A‟) “A” from dual;
• CHR函数：
14
练习2
• • • • • • --1、查询部门30中的所有雇员 --2、列出所有“办事员”的姓名、编号和部门 --3、找出提成高于薪金的雇员 --4、找出提成高于60%薪金的雇员 --5、找出部门10中所有经理和部门20中的所有办事员的详细资料 --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事 员但其薪金>=2000的所有雇员的详细资料 • --7、找出收取提成的雇员的不同工作 • --8、找出不收取提成或收取的提成低于100的雇员
ENAME ---------MARTIN ALLEN TURNER WARD CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A') ------------------- ------------- ---------------MARTINSALESMAN 6 2 ALLENSALESMAN 5 1 TURNERSALESMAN 6 0 WARDSALESMAN 4 2
7
字符函数
字符函数
大小写转换函数
LOWER UPPER INITCAP
字符处理函数
CONCAT SUBSTR LENGTH INSTR LPAD|RPAD TRIM REPLACE
9
使用大小写转换函数
• 显示员工号,名字,部门号
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake'; no rows selected SQL> SELECT 2 FROM 3 WHERE empno, ename, deptno emp LOWER(ename) = 'blake';
25
使用Trunc计算日期(了解)
• 取上周4的日期值 select trunc(sysdate, 'D') - 3 from dual; trunc(sysdate, 'D'), 截取到本周的第一天(0:00) trunc(sysdate, 'MM'), 截取到本月的第一天(0:00) trunc(sysdate, 'DD'), 截取到本日的0:00 trunc(sysdate,'yyyy')得到今年的第一天的0:00.
Number
Conversion
Date
使用字符函数
• 字符函数是对字符表达式进行处理的函数。字符函数的输入是字 符表达式(包括表中的列名称)，其输出是经过处理的字符表达式。
• 例如，lower()函数可以将字符表达式转换为小写字母，substring() 函数将输出某个字符串的子串。 • 在Oracle Database 10g系统中，可以使用的字符函数如表6-6所示。 实际上，这些字符函数在许多编程语言中都可以找到。
使用MOD函数
• 计算工资除以奖金后的余数
SQL> SELECT 2 FROM 3 WHERE ename, sal, comm, MOD(sal, comm) emp job = 'SALESMAN';
ENAME SAL COMM MOD(SAL,COMM) ---------- --------- --------- ------------MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250
• --9、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪 金升序 --10、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名(用两种方式实现)
• • • •
--11、显示只有首字母大写的所有雇员的姓名 --12、显示正好为6个字符的雇员姓名 --13、显示不带有'R'的雇员姓名 (用两种方式实现) --14、显示所有雇员的姓名的前三个字符 --15、显示所有雇员的姓名，用a替换所有'A‘ (用两种方式实现)