oracle工作日记,Oracle学习日记【4】

最新推荐文章于 2021-11-24 23:22:53 发布

shcxhbc

最新推荐文章于 2021-11-24 23:22:53 发布

阅读量91

点赞数

文章标签： oracle工作日记

1.查询所返回的列数以及列的类型必须匹配，列名可以不同。

2.只有UNION ALL不会去重。其他三个都需要排序后去重，性能比较差。

2、连接查询

2.1、内连接(INNER JOIN)：INNER可省略

语法结构：SELECT 列名 FROM 表1 INNER JOIN 表2 ON 关联条件

SELECT 列名 FROM 表1 , 表2 WHERE 关联条件

栗子：查询部门名称为‘SALES‘的所有员工信息

SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND D.DNAME=‘SALES‘;

SELECT * FROM EMP E , DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME=‘SALES‘;

2.2、外连接(OUTER JOIN)：OUTER可省略

2.2.1 左关联：SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 关联条件

2.2.2 右关联：SELECT 列名 FROM 表1 RIGHT JOIN 表2 ON 关联条件

2.2.3 全外关联：SELECT 列名 FROM 表1 FULL JOIN 表2 ON 关联条件

栗子：查询各部门名称，及对应的经理名称

SELECT D.DNAME, E.ENAME FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO AND E.JOB = ‘MANAGER‘;

注意：WHERE和ON的区别：不管是WHERE 还是ON，Oracle都会把能过滤的条件先过滤掉，再关联。但两者区别在于，如果是内关联，两种结果相同，如果是外关联，结果会不同，ON会保留主表的所有信息，而WHERE可能会过滤掉部分主表信息。

3、子查询和常用函数

3.1 单行子查询：不向外部返回结果，或者只返回一行结果。

3.2 多行子查询：向外部返回零行、一行或者多行结果。

栗子1：查询出销售部(SALES)下面的员工姓名，工作，工资。

SELECT E.ENAME, E.JOB, E.SAL FROM EMP E WHERE E.DEPTNO = (SELECT D.DEPTNO FROM DEPT D WHERE DNAME = ‘SALES‘);

解析：内部查询结果作为外部查询条件

注意：1、如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返还任何结果。

2、在单行子查询中外部查询可以使用=、>、=、<=、<>等比较运算符。

3、内部查询返回的结果必须与外部查询条件中的字段(DEPTNO)匹配。

4、如果内部查询返回多行结果则出现错误。

栗子2：查询EMP表中每个部门的最低工资的员工信息

SELECT E.* FROM EMP E WHERE E.SAL IN (SELECT MIN(A.SAL) FROM EMP A WHERE E.DEPTNO = A.DEPTNO);

4、Oracle中的伪列

4.1、ROWID：ROWID为返回该列的物理地址，使用ROWID可以快速定位表中某一行，可以唯一标识表中的一行

语法结构：删除重复数据，相同数据只保留一条

DELETE FROM EMP E

WHERE ROWID NOT IN

(SELECT MIN(ROWID) FROM EMP E GROUP BY EMPNO);

4.2、ROWNUM：ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推；可以限制查询结果集中返回的行数。

栗子：查询出员工表中前5名员工的姓名，工作，工资

SELECT ROWNUM, E.ENAME, E.JOB, E.SAL FROM EMP E WHERE ROWNUM <= 5;

注意：ROWNUM与ROWID不同，ROWID是插入记录是生成，ROWNUM是查询数据时生成。前者返回行物理地址，后者返回查询结果行的次序。

5、单行函数

定义：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果。

常用单行函数有：

1、字符函数：对字符串操作

2、数字函数：对数字进行计算，返回一个数字

3、转换函数：可以将一种数据类型转换为另外一种数据类型

4、日期函数：对日期和时间进行处理

5.1、字符函数

常用字符函数：

函数

说明

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，默认到结尾。

栗子：

示例

示例结果

SELECT ASCII(‘a‘) FROM DUAL

SELECT CONCAT(‘Hello‘, ‘ world‘) FROM DUAL

Hello world

SELECT INSTR(‘Hello world‘，‘or‘) FROM DUAL

SELECT LENGTH(‘Hello‘) FROM DUAL

SELECT LOWER(‘hElLO‘) FROM DUAL;

hello

SELECT UPPER(‘hello‘) FROM DUAL

HELLO

SELECT LTRIM(‘===HELLO===‘, ‘=‘) FROM DUAL

HELLO===

SELECT ‘==‘||LTRIM(‘ HELLO===‘) FROM DUAL

==HELLO===

SELECT RTRIM(‘===HELLO===‘, ‘=‘) FROM DUAL

===HELLO

SELECT ‘=‘||TRIM(‘ HELLO ‘)||‘=‘ FROM DUAL

=HELLO=

SELECT TRIM(‘=‘ FROM ‘===HELLO===‘) FROM DUAL

HELLO

SELECT REPLACE(‘ABCDE‘,‘CD‘,‘AAA‘) FROM DUAL

ABAAAE

SELECT SUBSTR(‘ABCDE‘,-2) FROM DUAL

SELECT SUBSTR(‘ABCDE‘,2,3) FROM DUAL

BCD

DUAL是一张虚拟表，只有一行一列，用来构成SELECT语法规则

栗子2：

ASCII(x)：SELECT ASCII(ENAME) FROM EMP WHERE ENAME=‘SMITH‘;

CONCAT(x,y)：SELECT CONCAT(ENAME, JOB) FROM EMP WHERE ENAME = ‘SMITH‘;

INSTR(x, str [,start] [,n])：SELECT INSTR(‘Hello world‘ ， ‘or‘) FROM DUAL;

LENGTH(x)：SELECT LENGTH(ENAME) FROM EMP WHERE ENAME = ‘SMITH‘;

LOWER(x)：SELECT LOWER(ENAME) FROM EMP WHERE ENAME = ‘SMITH‘;

UPPER(x)：SELECT UPPER(ENAME) FROM EMP WHERE ENAME = ‘zhangsan‘;

LTRIM(x[,trim_str])：SELECT * FROM STUDENT WHERE LTRIM(SNAME, ‘张‘) != SNAME;

RTRIM(x[,trim_str])：SELECT * FROM EMP E WHERE RTRIM(E.ENAME,‘S‘)<>E.ENAME;

TRIM([trim_str FROM] x)：SELECT RTRIM(‘===HELLO===‘, ‘=‘) FROM DUAL

REPLACE(x,old,new)：UPDATE EMP SET ENAME = REPLACE(ENAME, ‘S‘, ‘T‘);

SUBSTR(x,start[,length])：SELECT * FROM STUDENT WHERE SUBSTR(SNAME, 1, 1) = ‘张‘;

5.2、数字函数

常用函数：

函数

说明

示例

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

说明：ROUND(X[,Y])，四舍五入；TRUNC(x[,y])，直接截取，不四舍五入。

5.3、日期函数

常用的日期函数有：

5.3.1、ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。

栗子：SELECT add_months(to_date(‘2019/1/1‘,‘yyyy/mm/dd‘),6) FROM dual;

5.3.2、LAST_DAY(d)，返回指定日期当月的最后一天。

栗子：SELECT last_day(to_date(‘2019/1/1‘,‘yyyy/mm/dd‘)) FROM dual;

5.3.3、ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。

栗子：

5.3.3.1、如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。

SELECT ROUND(to_date(‘2019/5/5‘,‘yyyy/mm/dd‘),‘year‘) FROM dual;

5.3.3.2、如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。

SELECT ROUND(to_date(‘2019/1/18‘,‘yyyy/mm/dd‘),‘month‘) FROM dual;

5.3.3.3、默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。

SELECT ROUND(to_date(‘2019/1/5‘,‘yyyy/mm/dd‘),‘ddd‘) FROM dual;

5.3.3.4、如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

SELECT ROUND(to_date(‘2019/1/5‘,‘yyyy/mm/dd‘),‘day‘) FROM dual;

与ROUND对应的函数TRUNC(d[,fmt])对日期的操作，TRUNC与ROUND非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。

栗子：

SELECT TRUNC(to_date(‘2019/1/1‘,‘yyyy/mm/dd‘),‘day‘) FROM dual;

SELECT TRUNC(to_date(‘2019/1/5‘,‘yyyy/mm/dd‘),‘month‘) FROM dual;

SELECT TRUNC(to_date(‘2019/1/5‘,‘yyyy/mm/dd‘),‘year‘) FROM dual;

SELECT TRUNC(to_date(‘2019/1/5‘,‘yyyy/mm/dd‘),’ddd’) FROM dual;

5.4、转换函数

5.4.1、TO_CHAR(d|n[,fmt])：把日期和数字转换为制定格式的字符串。

栗子：SELECT TO_CHAR(SYSDATE, ‘YYYYMMDD‘) FROM DUAL

SELECT TO_CHAR(123456) FROM DUAL

5.4.2、TO_DATE(x [,fmt])：把一个字符串以fmt格式转换为一个日期类型。

栗子：SELECT TO_DATE(‘20170703145533‘,‘YYYYMMDD HH24:MI:SS‘) FROM DUAL

5.4.3、TO_NUMBER(x[,fmt])：把一个字符串以fmt格式转换为一个数字。

栗子：SELECT TO_NUMBER(‘123456‘) FROM DUAL

5.5、其他常用函数

NVL(列，默认值)

如果列值为null，则使用默认值表示

NVL2(列，返回值1，返回值2)

如果列值不为null，返回结果1；

如果列值为null，返回结果2

DECODE(列|值，判断值1，返回值1，判断值2，返回值2，...，默认值)

--不是所有数据库都可用

多值判断，如果列值与判断值相同，则显示对应返回值输出，如果没有满足条件，则显示默认值

CASE WHEN 条件1 THEN 返回值1 [WHEN 条件2 THEN 返回值2 ...] ELSE 默认值 END

--所有数据库都支持

用于实现多条件判断，如果都不满足条件，则返回默认值

EXISTS(子查询)

用于判断子查询是否有数据返回，如果有则成立，否则不成立。

栗子1：列出EMP员工的姓名，以及工作(中文)——DECODE

SELECT E.ENAME, DECODE(E.JOB, ‘CLERK‘, ‘业务员‘, ‘SALESMAN‘, ‘销售员‘, ‘MANAGER‘, ‘经理‘, ‘ANALYST‘, ‘分析员‘, ‘PRESIDENT‘, ‘总裁‘) FROM EMP E

CASE WHEN ：功能与DECODE相似，DECODE只用于多值判断，CASE WHEN适用于多条件判断。

语法1：当参数的值为判断值1，则返回返回值1……当参数的值匹配不到时，则返回默认值

CASE 参数

WHEN 判断值1 THEN 返回值1

WHEN 判断值2 THEN 返回值2

……

ELSE 默认值 END

语法2：当条件成立，则返回对应的返回值，没有条件成立则返回默认值

CASE

WHEN 条件1 THEN 返回值1

WHEN 条件2 THEN 返回值2

……

ELSE 默认值 END

栗子2：列出EMP员工的姓名，以及工作(中文)——CASE WHEN

SELECT E.ENAME,

CASE E.JOB WHEN ‘CLERK‘ THEN ‘业务员‘

WHEN ‘SALESMAN‘ THEN ‘销售员‘

WHEN ‘MANAGER‘ THEN ‘经理‘

WHEN ‘ANALYST‘ THEN ‘分析员‘

WHEN ‘PRESIDENT‘ THEN ‘总裁‘

END

FROM EMP E

栗子3：列出EMP员工的姓名，工资，以及工资评价——CASE WHEN

SELECT E.ENAME, E.SAL,

CASE WHEN E.SAL > 3000 THEN ‘工资很高‘

WHEN E.SAL > 1000 THEN ‘工资一般‘

ELSE ‘工资很低‘

END

FROM EMP E

EXISTS(查询结果集)：查询结果集有记录则成立，否则不成立。

栗子：列出有员工的部门信息

SELECT * FROM DEPT D WHERE EXISTS (SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO)

注意：

1、EXISTS、IN、关联必然可以相互转换。

2、同理NOT EXISTS、NOT IN、外关联+从表IS NULL也能相互转换

3、EXISTS、IN方法不会发散，但关联性能最好

Oracle学习日记【4】

标签：san abs 使用输入 lse upper instr 支持方法

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉本文系统来源：https://www.cnblogs.com/wenxing9415/p/10850344.html

shcxhbc

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle工作日记,Oracle学习日记【4】

1.查询所返回的列数以及列的类型必须匹配，列名可以不同。2.只有UNION ALL不会去重。其他三个都需要排序后去重，性能比较差。2、连接查询2.1、内连接(INNER JOIN)：INNER可省略语法结构：SELECT 列名 FROM 表1 INNER JOIN 表2 ON 关联条件SELECT 列名 FROM 表1 , 表2 WHERE 关联条件栗子：查询部门名称为‘SALES‘的所有员工信息...
复制链接

扫一扫

oracle工作日记,Oracle学习日记【4】

“相关推荐”对你有帮助么？