oracle 子查询和常用函数

#子查询

#查询出销售部门下面的员工姓名、工作、工资
#单行子查询
SELECT ENAME,JOB,SAL FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=‘SALES’)

#查询出 Emp 表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资
#any查询
select ename,job,sal from scott.emp
where sal < any (select sal from scott.emp where job = ‘SALESMAN’)
#all查询
SELECT ENAME,JOB,SAL FROM scott.EMP
WHERE SAL>ALL (SELECT SAL FROM scott.EMP WHERE JOB=‘SALESMAN’)

#伪列
#插入数据时生成
SELECT ROWID,ENAME FROM scott.EMP WHERE SAL>2000;
#查询数据时生成
SELECT ROWNUM,ENAME,JOB,SAL FROM scott.EMP WHERE ROWNUM<=5;
#查询出工资最高的前 5 名员工的姓名、工资和工资。T是子查询的别名
SELECT ROWNUM,T.* FROM
(SELECT ENAME,JOB,SAL
FROM scott.EMP ORDER BY SAL DESC)T
WHERE ROWNUM<=5
#查询出表 EMP 中第 5 条到第 10 条之间的记录。内部查询建立了别名R供外部查询使用
SELECT * FROM
(SELECT ROWNUM R,ENAME,JOB,SAL
FROM scott.EMP WHERE ROWNUM<=10)
WHERE R>5

#oracle函数
#单行函数：字符函数、数字函数、转换函数（转换数据类型)、日期函数、数字函数
#聚合函数：可以同时对多行数据进行操作，并返回一个结果，比如SUM(X)

#字符函数
ASCII(x) 返回字符 x 的 ASCII 码。 CONCAT(x,y) 连接字符串 x 和 y。
INSTR(x, str [,start] [,n)
在 x 中查找 str，可以指定从 start 开始，也可以指定从 第 n 次开始。 LENGTH(x) 返回 x 的长度。 LOWER(x) x 转换为小写。 UPPER(x) x 转换为大写。 LTRIM(x[,trim_str]) 把 x 的左边截去 trim_str 字符串，缺省截去空格。 RTRIM(x[,trim_str]) 把 x 的右边截去 trim_str 字符串，缺省截去空格。 TRIM([trim_str FROM] x) 把 x 的两边截去 trim_str 字符串，缺省截去空格。 REPLACE(x,old,new) 在 x 中查找 old，并替换为 new。
SUBSTR(x,start[,length])
返回 x 的字串，从 staart 处开始，截取 length 个字符， 缺省 length，默认到结尾。

#数字函数
ABS(x) x 绝对值 ABS(-3)=3 ACOS(x) x 的反余弦 ACOS(1)=0 COS(x) 余弦 COS(1)=1.57079633 CEIL(x) 大于或等于 x 的最小值 CEIL(5.4)=6 FLOOR(x) 小于或等于 x 的最大值 FLOOR(5.8)=5 LOG(x,y) x 为底 y 的对数 LOG(2,4)=2 MOD(x,y) x 除以 y 的余数 MOD(8,3)=2 POWER(x,y) x 的 y 次幂 POWER(2,3)=8 ROUND(x[,y]) x 在第 y 位四舍五入 ROUND(3.456,2)=3.46 SQRT(x) x 的平方根 SQRT(4)=2 TRUNC(x[,y]) x 在第 y 位截断 TRUNC(3.456,2)=3.45

#日期函数

#转换函数
#TO_CHAR(d|n[,fmt])
SELECT TO_CHAR(SYSDATE,‘YYYY"年"MM"月"DD"日" HH24:MI:SS’) “date”
FROM DUAL;
#TO_DATE(x [,fmt])
#TO_NUMBER(x[,fmt])

#其他单行函数
#NVL(x,value)如果 x 为空，返回 value，否则返回 x。
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM scott.EMP WHERE SAL<2000;
#NVL2(x,value1,value2) 如果 x 非空，返回 value1，否则返回 value2

#聚合函数
#求本月所有员工的基本工资总和
select sum(sal) from scott.emp
#求不同部门的平均工资
select deptno,avg(sal) from scott.emp
group by deptno;

#练习
#1.描述TO_CHAR和TO_DATE函数的用法
TO_CHAR 函数能够把数字和日期转换成固定的字符串格式。TO_DATE 函数能够把 固定格式的字符串转换为日期类型。
#2.描述 EXTRACT 函数的用法
EXTRACT 函数能够获取日期中的某个字段的
#3.你知道有哪些关于日期函数的用法