Oracle中的伪列
SELECT * FROM EMP
在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。
伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。
接下来学习两个伪列：ROWID 和 ROWNUM。
1.ROWID —（用来删除重复的数据）
表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。
使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。
由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。
语法结构：删除重复数据，相同数据只保留一条
DELETE FROM 表名1 WHERE ROWID NOT IN (SELECT max(ROWID) FROM 表名1 GROUP BY 能区分唯一数据的字段);—如果不加GROUP BY 只保留表中一条数据
COMMIT;
CREATE TABLE EMP_1 AS SELECT * FROM EMP ;
---------------------------------------------
DELETE FROM EMP_1 A WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM EMP_1 GROUP BY EMPNO);
------复制EMP表中的数据给 EMP_2 ,再删除EMP_2中重复的部门编号、
CREATE TABLE EMP_2 AS SELECT * FROM EMP WHERE 1=1;
SELECT * FROM EMP_2;
DELETE FROM EMP_2 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP_2 A GROUP BY A.DEPTNO);
COMMIT;
2. ROWNUM —(只能用小于号或者小于等于号，不能用大于或者大于等于号,即：rownum < 或者 rownum <=)
在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。
通过 ROWNUM 伪列可以限制查询结果集中返回的行数。
----查询出员工表中前5名员工的姓名，工作，工资。
SELECT
A.ENAME
,A.JOB
,A.SAL
FROM EMP A
WHERE ROWNUM <=5;
--------------------------------------------
ROWNUM 与 ROWID 不同，ROWID 是插入记录时生成，ROWNUM 是查询数据时生成。
ROWID 标识的是行的物理地址。ROWNUM 标识的是查询结果中的行的次序。
----------查询出员工表中工资前5名员工信息。
SELECT
A1.*
FROM(SELECT
A.ENAME
,A.SAL
FROM EMP A
ORDER BY A.SAL DESC
)A1
WHERE ROWNUM <=5;
----------查询出员工表中入职时间最早的5名员工信息。
SELECT
A1.*
FROM (SELECT
A.EMPNO,
A.ENAME,
A.JOB,
A.MGR,
A.HIREDATE,
A.SAL,
A.COMM,
A.DEPTNO
FROM EMP A
ORDER BY A.HIREDATE
)A1
WHERE ROWNUM<6 ;
----------查询出员工表中工资大于平均工资前三名员工姓名、工资。
SELECT
A1.ENAME
,A1.SAL
FROM(SELECT
B.EMPNO,
B.ENAME,
B.JOB,
B.MGR,
B.HIREDATE,
B.SAL,
B.COMM,
B.DEPTNO
FROM EMP B
WHERE B.SAL>(SELECT
AVG(A.SAL)
FROM EMP A
)
ORDER BY B.SAL DESC
)A1
WHERE ROWNUM<=3;
Oracle单行函数
单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，
比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。
常用的单行函数有：
-
字符函数：对字符串操作。
-
数字函数：对数字进行计算，返回一个数字。
-
转换函数：可以将一种数据类型转换为另外一种数据类型。
-
日期函数：对日期和时间进行处理。
- 字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。
函数 说明
ASCII(x) 返回字符x的ASCII码。
-----查询EMP表中员工姓名的ASCII值
SELECT ASCII(A.ENAME) FROM EMP A;
-----查询'A'的ASCII值
SELECT ASCII('A') FROM DUAL;
CONCAT(x,y) 连接字符串x和y。---之前学过'||',但是CONCAT只能两两拼接
-----拼接 '今晚','出去','浪！'-->‘今晚出去浪！’
SELECT
CONCAT(CONCAT('今晚','出去'),'浪！')
FROM DUAL;
------------
SELECT '今晚'||'出去'||'浪！' FROM DUAL;
INSTR(x, str ,start ,n) 在x中查找str，可以指定从start开始，第n次出现。---找位置(常与SUBSTR连用)
---------在'ADFGHFGDSADSFDJGHFGD'中找出‘F’,从1开始 第3次出现的位置
SELECT INSTR('ADFGHFGDSADSFDJGHFGD','F',1,3) FROM DUAL;
---------在'ASDKJDFSALAKDJFKJDKDSH'中找出‘KD’,从倒数第1开始 第2次出现的位置
SELECT INSTR('ASDKJDFSALAKDJFKJDKDSH','KD',-1,2) from DUAL;
LENGTH(x) 返回x的长度。
------查询你自己名字的长度
SELECT LENGTH('张浩宇') FROM DUAL;
LOWER(x) x转换为小写。
-----把'ADFghjdKH'转换成小写
SELECT LOWER('ADFghjdKH') FROM DUAL;
UPPER(x) x转换为大写。
-----把'ADFghjdKH'转换成大写
SELECT UPPER('ADFghjdKH') FROM DUAL;
LTRIM(x,trim_str) 把x的左边截去trim_str字符串，缺省截去空格。
----把'---阿斯蒂芬---'的左边截去'-'字符串
SELECT LTRIM('---阿斯蒂芬---','-') FROM DUAL;
RTRIM(x,trim_str) 把x的右边截去trim_str字符串，缺省截去空格。
----把'---阿斯蒂芬---'的右边截去'-'字符串
SELECT RTRIM('---阿斯蒂芬---','-') FROM DUAL;
TRIM(trim_str FROM x) 把x的两边截去trim_str字符串，缺省截去空格。
----把'---阿斯蒂芬---'的两边截去'-'字符串
SELECT TRIM('-'FROM '---阿斯蒂芬---') FROM DUAL;
REPLACE(x,old,new) 在x中查找old，并替换为new。
-----在'ASDLFKJFASD'中查找'ASD'，并替换为'ABC'。
SELECT REPLACE('ASDLFKJFASD','ASD','ABC') FROM DUAL;
SUBSTR(x,start,length) 返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。
-----在'ASDLFKJKFCASDADF'中从第一位开始，截取'KFC'。
SELECT SUBSTR('ASDLFKJKFCASDADF',INSTR('ASDLFKJKFCASDADF','KFC',1,1),3) FROM DUAL;
SELECT REPLACE('ASDLFKJKFCASDADF','KFC','ABC') FROM DUAL;
示例 示例结果
SELECT ASCII('a') FROM DUAL
SELECT CONCAT('Hello', ' world') FROM DUAL
SELECT INSTR('Hello world'，'or') FROM DUAL
SELECT LENGTH('Hello') FROM DUAL
SELECT LOWER('hElLO') FROM DUAL;
SELECT UPPER('hello') FROM DUAL
SELECT LTRIM('===HELLO===', '=') FROM DUAL
SELECT '=='||LTRIM(' HELLO===') FROM DUAL
SELECT RTRIM('===HELLO===', '=') FROM DUAL
SELECT '='||TRIM(' HELLO ')||'=' FROM DUAL
SELECT TRIM('=' FROM '===HELLO===') FROM DUAL
SELECT REPLACE('ABCDE','CD','AAA') FROM DUAL
SELECT SUBSTR('ABCDE',-2) FROM DUAL
SELECT SUBSTR('ABCDE',2,3) FROM DUAL
DUAL介绍：
dual是一张虚拟表，只有一行一列，用来构成 select 的语法规则。
Oracle的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from 后面使用 DUAL 表
，dual 表在系统中只有一行一列，该表在输出单行函数时为了 select…from 的语法完整性而使用。
—查询员工姓名以’S’结尾的员工信息
SELECT
A.EMPNO,
A.ENAME,
A.JOB,
A.MGR,
A.HIREDATE,
A.SAL,
A.COMM,
A.DEPTNO
FROM EMP A
where substr(a.ename,-1,1)='S';
-------------------------------------------
- *数字函数
----有小数点的时候，小数点的右边代表正数，左边代表负数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。
函数 说明 示例
ABS(x) x绝对值 ABS(-3)=3
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
TRUNC(x,y) x在第y位截断 TRUNC(3.456,2)=3.45
表3 数字函数
说明：
- ROUND(X[,Y])，四舍五入。
在缺省y时，默认y=0；比如：ROUND(3.56)=4。
SELECT ROUND(5.654,1) FROM DUAL;
SELECT TRUNC(3.1635,1) FROM DUAL;
y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。
SELECT ROUND(2435.654,-2) FROM DUAL;
y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。
- TRUNC(x[,y])，直接截取，不四舍五入。
在缺省y时，默认y=0；比如：TRUNC (3.56)=3。
y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。
- 日期函数
日期函数对日期进行运算。常用的日期函数有：
1.ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
—当前日期两个月后的日期
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;
2.LAST_DAY(d)，返回指定日期当月的最后一天。
—查询当前时间的最后一天
select LAST_DAY(SYSDATE) FROM DUAL;
----查询你出生日期的最后一天
SELECT LAST_DAY(TO_DATE('20200105','YYYYMMDD'))FROM DUAL;
----查询你的生日是星期谁？
SELECT to_char(TO_DATE('20200105','YYYYMMDD'),'DAY')FROM DUAL;
----查询当前时间是第几季度
SELECT TO_CHAR(SYSDATE,'mm') FROM DUAL;
3.ROUND(d,fmt)，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
-
如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。
-
如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
-
默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
-
如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
SELECT ROUND(SYSDATE,'DAY') FROM DUAL;
SELECT ROUND(TO_DATE('20210706','YYYYMMDD'),'MM')FROM DUAL;
与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作
，TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。
TRUNC(date[,fmt])：将date截取到fmt指定的形式，如果fmt省略，则截取到最近的日期。
------求你出生日期是星期几
---'19990701'
SELECT
TO_CHAR(TO_DATE('19990701','YYYYMMDD'),'DAY')
FROM DUAL;
------求你出生那年有多少天
—1、先截取到年初
—2、年初加12月
—3、两个年份相减
SELECT
TRUNC(TO_DATE('19990701','YYYYMMDD'),'YYYY')--年初
,ADD_MONTHS(TRUNC(TO_DATE('19990701','YYYYMMDD'),'YYYY'),12)--下年年初
,ADD_MONTHS(TRUNC(TO_DATE('19990701','YYYYMMDD'),'YYYY'),12)- TRUNC(TO_DATE('19990701','YYYYMMDD'),'YYYY')
FROM DUAL;
------求你出生日期月末是几号
SELECT
TO_CHAR(TO_DATE('19990701','YYYYMMDD'),'DAY')---出生日期
,LAST_DAY(TO_DATE('19990701','YYYYMMDD'))
FROM DUAL;
------求你出生日期离年初过了多少天
SELECT
TRUNC(TO_DATE('19990701','YYYYMMDD'),'YYYY')--年初
,TO_DATE('19990701','YYYYMMDD')--出生日期
,TO_DATE('19990701','YYYYMMDD')-TRUNC(TO_DATE('19990701','YYYYMMDD'),'YYYY')
FROM DUAL;
------求你出生日期离年末还有多少天
SELECT
TO_DATE('19990701','YYYYMMDD')--出生日期
,LAST_DAY(TO_DATE('19990701','YYYYMMDD'))--出生日期 最后一天
,ADD_MONTHS(LAST_DAY(TO_DATE('19990701','YYYYMMDD')),5)---年末
,ADD_MONTHS(LAST_DAY(TO_DATE('19990701','YYYYMMDD')),5)-TO_DATE('19990701','YYYYMMDD')
FROM DUAL;
- 转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。
常用的转换函数有：
–1.TO_CHAR(d,fmt)
把日期和数字转换为指定格式的字符串。fmt是格式化字符串，日期的格式化字符串前面已经学习过。
SELECT
TO_CHAR(SYSDATE,'YYYYMMDD')
FROM DUAL;
代码演示：TO_CHAR对日期的处理
SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL
SELECT 1234 FROM DUAL;
代码演示：TO_CHAR对数字的处理
SELECT TO_CHAR(123456) FROM DUAL
2.TO_DATE(x ,fmt)
把一个字符串以fmt格式转换为一个日期类型。
代码演示：TO_DATE函数
SELECT TO_DATE('20170703145533','YYYYMMDD HH24:MI:SS') FROM DUAL
3.TO_NUMBER(x[,fmt])----只能是纯阿拉伯数字才能转换(要想把时间类型的数据转换成数字类型，一定要先TO_CHAR成纯阿拉伯数字的字符串)
把一个字符串以fmt格式转换为一个数字。
-----把系统时间转换成数字类型
SELECT
TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD'))
FROM DUAL;
代码演示：TO_NUMBER函数
SELECT TO_NUMBER(‘123456’) FROM DUAL
5.其他常用函数
NVL（列，默认值） 如果列值为null，则使用默认值表示—指定的默认值一定要跟列的数据类型一致
SELECT
NVL(A.COMM,10000)
FROM EMP A;
NVL2（列，返回值1，返回值2） 如果列值不为null，返回结果1；如果列值为null，返回结果2 —指定的值一定要跟列的数据类型一致
SELECT
NVL2(A.COMM,2000,0)
FROM EMP A;
DECODE（列|值，判断值1，返回值1，判断值2，返回值2，…，默认值）–判断值1:表中具体的数据 ，返回值1：你指定的值
–不是所有数据库都可用 多值判断，如果列值与判断值相同，则显示对应返回值输出，如果没有满足条件，则显示默认值
CLERK --- 办事员
SALESMAN --销售员
PRESIDENT --主席
MANAGER --- 经理
ANALYST --- 分析师
SELECT
DECODE(A.JOB,'CLERK','办事员','SALESMAN','销售员','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师','鬼')
FROM EMP A;
–CASE WHEN 的语法结构：
CASE WHEN 条件1 THEN 返回值1
WHEN 条件2 THEN 返回值2
… ELSE 默认值
END --条件1：列名=具体的数据，返回值1：你指定的值
–所有数据库都支持 用于实现多条件判断，如果都不满足条件，则返回默认值
SELECT
A.ENAME
,CASE WHEN A.JOB='CLERK' THEN '办事员'
WHEN A.JOB='SALESMAN' THEN '销售员'
WHEN A.JOB='PRESIDENT' THEN '主席'
WHEN A.JOB='MANAGER' THEN '经理'
WHEN A.JOB='ANALYST' THEN '分析师'
ELSE ''
END JOB
,A.HIREDATE
,A.SAL
FROM EMP A
- NVL(x,value)
如果x为空，返回 value，否则返回 x。
—对工资是2000元以下的员工，如果没有发奖金，每人奖金100元。
----列出EMP员工的姓名，工资，以及工资评价 --(按工资等级来分，大于3000工资很高，大于2000，工资一般，小于1000，混子)
SELECT
A.ENAME 姓名
,A.SAL 工资
,CASE WHEN A.SAL>3000 THEN '工资很高'
WHEN A.SAL>=1000 THEN '工资一般'
WHEN A.SAL<1000 THEN '混子'
END 工资评价
FROM EMP A;
----EXISTS（子查询） 用于判断子查询是否有数据返回，如果有则成立，否则不成立。
–列出有员工的部门信息
SELECT
A.DEPTNO,
A.DNAME,
A.LOC
FROM DEPT A
WHERE EXISTS (SELECT B.DEPTNO FROM EMP B WHERE B.DEPTNO=A.DEPTNO);
----------------------------------------
SELECT
A.DEPTNO,
A.DNAME,
A.LOC
FROM DEPT A
WHERE A.DEPTNO IN(SELECT B.DEPTNO FROM EMP B);
—查询EMP表中 和10号部门的工作岗位相同,且工资大于1000的员工姓名、工作、薪资–用两种方法来做
SELECT
B.ENAME
,B.JOB
,B.SAL
,B.DEPTNO
FROM EMP B
WHERE B.JOB IN(SELECT
A.JOB
FROM EMP A
WHERE A.DEPTNO=10
)
AND B.SAL>1000
AND B.DEPTNO<>10;
------------------------------
SELECT
B.ENAME
,B.JOB
,B.SAL
,B.DEPTNO
FROM EMP B
WHERE EXISTS(SELECT
A.JOB
FROM EMP A
WHERE A.DEPTNO=10
AND A.JOB=B.JOB
)
AND B.SAL>1000
AND B.DEPTNO<>10;
扫一扫
645
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
