Oracle相关语法

Oracle相关语法

Oracle相关语法目录

• 1、<> 与 !=
• 2、count()
• 3、exists 与 not exists
• 4、from 多表与联表查询
• 5、distinct关键字
• 6、oracle的并集（union、union all）、交集（intersect）、差集（minus）
• 7、decode关键字
• 8、rownum关键字
• 9、connect by [prior (字段)]关键字
• 10、dual伪表
• 11、nvl函数
• 12、trunc函数
• 13、instr函数
• 14、sysdate关键字
• 15、trim、ltrim与rtrim函数
• 16、round函数
• 17、grouping sets函数
• 18、case函数
• 19、ground by
• 20、replace函数
• 21、add_months()函数
• 22、row_number函数
• 23、database link(dblink)
• 24、更多
• •    1、to_char、to_date、to_timestamp与to_number关键字
  •    2、左连接（left join）、右连接（right join）、全连接（full join）、内连接（inner join）与（+）
  •    3、rollup函数

1、<> 与 !=

   （1）语义相同

   （2）<> 为标准语法，可移植性较高，可移植至其他平台；
      != 非标准语法，可移植性较低。

   （3）建议开发应用中使用 <>

2、count()

   count(*)、count(1)、count(列名)：均是统计表格中的行数

   区别 ：count(*) 所查出结果包含了值为null的行；
       count(列名) 所查出结果不包含值为null的行；
       count(distinct 列名)所查出结果不包含值为null或重复的行：如：count(distinct userId)

3、exists 与 not exists

   （1）exists ：当返回集不为空时是true，否则是false；
     not exists : 当返回集不为空时是false，否则是true。

select * from sysuserwhere userName='cqb'and exists(select * from sysuserwhere userPassword="12345678")-- 此时，只要exists中的查询结果有一条或一条以上的数据时，执行前面的语句。-- exists中的查询结果没有数据时，则不运行前面的语句。

select * from sysuserwhere userName='cqb'and not exists(select * from sysuserwhere userPassword="12345678")-- 此时，只要not exists中的查询结果没有数据时，执行前面的语句。-- not exists中的查询结果有一条或一条以上的数据时，则不运行前面的语句。

   （2）exists 与 in、not exists 与 not in的区别：
      exists与not exists只关注返回集是否为空，而in与not in更关注于返回集的内容。

4、from 多表与联表查询

sysuser表：

userId	userName	userPassword	deptId
1	cqb	12345678	1
2	ywp	87654321	2
3	lyb	liyanbo	5

dept表：

deptId	deptName
1	研发部
2	产品部
3	市场部
4	销售部

   （1）from 多表：表示多表的各行相拼接
例：

select * from sysuser s, dept dwhere s.deptId = d.deptId-- 一行对多行，并将两表合并输出；共2行，但有6列

结果为：

userId	userName	userPassword	deptId	deptId(1)	deptName
1	cqb	12345678	1	1	研发部
2	ywp	87654321	2	2	产品部

   （2）联表查询：
例：

select * from sysuser s where s.deptId in (select deptIdfrom dept dwhere d.deptId=s.deptId)-- where d.deptId=s.deptId 也是一行对多行，但只有单表输出；共3行，并且只有4列

结果为：

userId	userName	userPassword	deptId
1	cqb	12345678	1
2	ywp	87654321	2

5、distinct关键字

   distinct关键字：表示去重

select distinct(userName)from sysuser--或者select distinct userNamefrom sysuser--上述语句当userName重复时会过滤掉。

select distinct(userName, userPassword)from sysuser--或者select distinct userName, userPasswordfrom sysuser--上述语句只有当userName与userPassword同时重复时才过滤，单个字段重复不过滤。

6、oracle的并集（union、union all）、交集（intersect）、差集（minus）

   三者都是oracle中连接两个查询语句的关键字。

   （1）并集（union与union all）：两者的所有记录行
     1）union：相交部分去重并集
     2）union all：相交部分不去重并集

   （2）交集（intersect）：去重的相交记录行，可用于查询区间（小，大）
     例：

select *from sysuser uwhere u.userId >= 1intersectselect *from sysuser uwhere u.userId <= 2

   （3）差集（minus）：以第一个查询语句为主，先查出第一个查询语句的结果；再看有没有在第二个查询语句结果中的，有则去掉，无则留下。

7、decode关键字

   （1）decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)：
     该函数的含义如下：
       if (条件=值1) then
         return (翻译值1);
       else if (条件=值2) then
         return (翻译值2);
       …
       else if (条件=值n) then
         return (翻译值n);
       else
         return (缺省值);
       engif

   （2）decode(字段或字段的运算，值1，返回值2，返回值3）：
     这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3。
​       当然值 1，值 2，值 3也可以是表达式。

8、rownum关键字

​   即为数据库操作客户端数据表中的行（不包括表头）。

9、connect by [prior (字段)]关键字

   用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。

   connect by进阶

   （1）start with：指定起始节点的条件

   （2）connect by prior (字段)：可以查询使用了connect by后的根节点对应字段

     例：表free

id	freeId	name
1	0	cqb
2	1	ywp
3	1	lyb
4	2	zb

SELECT ID, freeId, name, CONNECT_BY_ROOT(name) rootNameFROM freeSTART WITH freeId = 0  CONNECT BY PRIOR id = freeId;

     查询结果为：

id	freeId	name	rootName
1	0	cqb	cqb
2	1	ywp	cqb
4	2	zb	cqb
3	1	lyb	cqb

10、dual伪表

   详细笔记

   dual表是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

   （1）查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;

   （2）用来调用系统函数；

   （3）得到序列的下一个值或当前值，用下面语句

select val from dual;		--获得序列your_sequence的下一个值select your_sequence.currval from dual;		--获得序列your_sequence的当前值

   （4）可以用做计算器 ，如：select 7*9 from dual;

11、nvl函数

   nvl(值1, 值2)：表示返回一个非null值；

   例：

		nvl(org_name,'总计');  -- 当org_name为null时，返回“总计”；否则返回org_name

12、trunc函数

   trunc()：类似截取函数，按指定的格式截取输入的数据。

   （1）处理日期：trunc(date[, fmt]); --date表示日期，fmt表示所需要的格式

   例：

	select trunc(sysdate) from dual;		-- 2017/2/13,返回当前时间select trunc(sysdate,'yy') from dual;	-- 2017/1/1,返回当年第一天select trunc(sysdate,'mm') from dual;	-- 2017/2/1,返回当月的第一天

   （2）处理number型数字：trunc(number[, decimals]); --number表示数字，decimals表示所需要的小数点后几位 （可以是负数）

13、instr函数

   类似字符串查找函数，返回查找到的下标+1。

   （1）instr(String1, String2)：从String1中查找出String2的位置；默认返回第一个查找到的位置。

   （2）instr(String1, String2[, start_position [, nth_appearance]])：从String1的第start_position位开始查找，找出第nth_appearance次出现String2的位置。

14、sysdate关键字

   表示系统时间，可对其进行加减法操作。

15、trim、ltrim与rtrim函数

   （1）去空格：[ l/r ]trim(String1)
     1）trim函数：去除String1两端空格
     2）ltrim函数：去除String1开头的空格
     3）rtrim函数：去除String1结尾的空格

   （2）去除指定字符：

   1）trim函数：trim(position String2 from String1)，position可以是 ***leading（前）/trailing（后）/both（两端）***；同时，String2只能是单个字符，否则会报错。
   2）ltrim函数：ltrim(String1, String2)，去除String1开头的String2，String2可以是多字符。
   3）rtrim函数：rtrim(String1, String2)，去除String1结尾的String2，String2可以是多字符。

16、round函数

   格式：round(number[, decimals]); --表示从number中截取小数点后decimals位，采用四舍五入的方法。

17、grouping sets函数

   格式：grouping sets(A, B)
     例：

select A, B from table group by grouping sets(A, B)

     等价于：

select A, null as B from table group by A  union all  
select null as A, B from table group by Bunion all
select A, B from test group by A,B

18、case函数

select            case                   		-- 如果when sex='1' then '男' 		-- sex='1'，则返回值'男'when sex='2' then '女' 		-- sex='2'，则返回值'女'  else 0                 		-- 其他的返回'其他’end                    		-- 结束
from   sys_user            		-- 整体理解： 在sys_user表中如sex='1'，则返回'男',如sex='2'，则返回'女' 否则返回'其他’select sex         case                   		-- 如果when '1' then '男' 			-- sex='1'，则返回值'男'when '2' then '女' 			-- sex='2'，则返回值'女'  else 0                 		-- 其他的返回'其他’end                    		-- 结束
from   sys_user            		-- 整体理解： 在sys_user表中如sex='1'，则返回'男',如sex='2'，则返回'女' 否则返回'其他’

19、ground by

   当SELECT 后 既有 表结构本身的字段，又有需要使用聚合函数（COUNT(),SUM(),MAX(),MIN(),AVG()等）的字段，就要用到group by分组，查询的限定条件里有需要用聚合函数计算的字段时也需要用分组，比如：

 select avg(grade) from cs

   查询选课表（cs）中学生的总成绩(grade)；这里就不用分组；
     又如：

select sno,sum(grade) from cs group by sno

   查询选课表里每个学生的总成绩，这里就要用分组，分组的依照字段必须是select 后没有被计算过的原始字段；

20、replace函数

   作用：用另一个值来替代串中的某个值

   格式：replace(searchString, searchChar[, replaceChar])
     解读：含有replaceChar参数时，指用replaceChar字符替换searchString串中的searchChar字符
       没有replaceChar参数时，指将searchString串中的searchChar字符去掉

  具体例子如下：

	select replace('0123456789', '0', 'a') from dual;		-- a123456789select replace('0123456789', '0', '') from dual;		-- 123456789select replace('0123456789', '0') from dual;			-- 123456789

21、add_months()函数

   作用：主要是对日期进行操作的函数

   格式：add_months(date, int)

   具体例子如下：

	add_months(to_date('2019-01-01', 'yyyy-MM-dd'), 2)		-- 得到的是 '2019-03-01'add_months(to_date('2019-01-01', 'yyyy-MM-dd'), -2)		-- 得到的是 '2018-11-01'-- 注：add_months的第一个参数必须要是date日期格式

22、row_number函数

23、database link(dblink)

   概念：oracle可以通过创建dblink实现跨库访问

24、更多

   1、to_char、to_date、to_timestamp与to_number关键字

     详细笔记

   2、左连接（left join）、右连接（right join）、全连接（full join）、内连接（inner join）与（+）

     详细笔记1

     详细笔记2

     注： 前四个连接与on搭配使用，不可与 where 搭配

   3、rollup函数

     详细笔记