ORACLE DQL 数据查询义语言集合

条件查询：Where

 

Where 之后其它运算符：

Like 模糊查询

%匹配多个字符

_匹配单个字符

select * from wip_yanguu where name like '_三';

select * from wip_yanguu where name like '%三';

In() 在某个集合内

select * from wip_yanguu where name in ('张三'，'李四');

select * from wip_yanguu where name in (

select name from wip_yanguu  where name like '%张%'

);

Between ... and ... 在某一个区间（范围）

select * from wip_yanguu where age between 11 and 20 ;

 

Is null  判断为空

select * from wip_yanguu where ate is null;

 

Is not null 判断不为空

select * from wip_yanguu where ate is not null;

 

 

Where过滤条件：

In 结果等于集合中的值；

Not in 结果不等于集合中的值；

 

>Any（） 结果大于集合中的某一个值。

select * from bbb where "id" >any(select id from aaa);

 

>all() 结果大于集合中的所有值。

 

Existe（）集合中有一个值返回为true，否则为false；

select * from bbb where exists(select id from aaa);

 

Not existe（） 结合中没有一个值返回为true，否则为false；

 

在比较中有空值时，将不会出现结果。

解决方法是在查询时就将null值取消。

select * from bbb b,aaa a where b.gender  in

 (select gender from bbb where gender is not null);

查询出b表中id与a变中相等的所有数据。

select * from bbb b

where exists（select * from aaa a where b."id" = a.id）;

关联子查询：

select * from wip_yanguu w where id >

(select id from aaa a where a.classname = w.name);

查询同时满足子查询的条件。

关联子查询：

select * from wip_yanguu w where id >

(select id from aaa a where a.classname = w.name);

查询同时满足子查询的条件。

 

Dual表：oracle中的虚表伪表，主要用来补齐语句通常用来计算。

select 1+1 from dual;  

查询出2的结果

 

别名查询：

使用 as ，可以省略。（可以会出现乱码 如中文），别名不能用特殊符号和关键字，如果有就用双引号。

 

引号： 

单引号：在oracle中通常表示值。

双引号：在oracle 中表示取名字。

 

去除重复数据： distinct  distinct必须放在开头

select distinct age,name from wip_yanguu;

保证每一列数据不会相同，同时比较一列查询出来所用的所用字段。

 

数据可以直接加减乘除（必须是整数）：

select age*10 from wip_yanguu where name = '张三';

 

字符串拼接： || （oracle特有）

select name || age from wip_yanguu

 

字符串函数拼接： concat（参数1，参数2）

select concat('姓名：',name) from wip_yanguu

 

转意字符： escape

Escape 设置转意符号

select * from wip_yanguu where name like '%|%%' escape'|';

select * from wip_yanguu where name like '%#%%' escape'#';

 

Null值排序：

Nulls first（null数据出现在头）；

Nulls last （null数据出现在尾）；

select * from wip_yanguu order by ate nulls first;

 

多字段排序：

先写先排

select * from wip_yanguu order by ate desc,name desc;

降序

   select * from wip_yanguu order by ate desc;

 

函数：函数必须要返回值。

 

单列函数：对某一行中的某个值进行处理

 

Ceil（）取整进1

select ceil(1.1) from dual  结果为2；

 

Floor（）取整不计算小数

select floor(1.7) from dual 结果为1；

 

Round（值，决定小数点后几位，可以为负数为小数点前几位）四舍五入

select round(19.9474,2) from dual; 结果为19.95

select round(19.9474,0) from dual; 结果为20

select round(19.9474,-1) from dual; 结果为20

 

Trunc （值，决定小数点后几位，可以为负数为小数点前几位） 截断后面的值不计算。

select trunc(22.561,1) from dual; 结果为22.5

 

Mod（被除数，除数）取余

select trunc(22.561,1) from dual; 结果为1

 

字符函数: 空格也算字符

substr（字符串，起始索引，截取长度）

起始索引不管是写0还是1都是从第一位字符开始截取。

select substr('你好呀'，0,2) from dual; 结果为’你好’

如果起始索引写负数就是从后面开始截取。

select substr('你好呀'，-2,2) from dual; 结果为‘好呀

Length（字符串）

select length('你好 呀') from dual; 结果为4，空格也算字符

 

 

 

Trim （） 去除左右2边多余的字符

默认为去除空格

select trim( '  你好 呀  ') from dual; 结果为‘你好 呀’

特定去除对应字符

select trim('哦'from '哦哦哦你好 呀哦哦哦') from dual；结果为‘你好 呀’

 

Replace（字符串，被替换字符，替换成字符）替换

   select replace（'你好 呀','你好'，'我帅'） from dual; 结果为‘我帅 呀’

 

Instr （字符串，字符，开始，第几次出现）搜索字符串字符的出现位置

select instr('哦你好呀哦哦'，'哦',1,2) from dual 结果为5

 

日期函数：

Sysdate 今天的时间 精确到秒

Sysdate+3 三天之后

 

Add_months 添加月数

select add_months(sysdate,3) from dual; 三个月之后

 

时间可以直接减获取天数

select ceil(add_months(sysdate,3)-sysdate) from dual;

 

查询出2个时间间隔月数（以小数显示）

select months_between(sysdate,ate) from wip_yanguu where id = 1;

 

转值函数：

字符转换成数字

select to_number('12313') from dual；

 

转字符

select to_char(2123,'$9999.00') from dual;  $2123.00

格式化中9代表一个数字，0表示强制为0

 

日期转字符

select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual;

HH24 24小时显示。

d 表示一个星期的第几天。

dd 一个月的第几天。

ddd 一年的第几天。

Day 中文星期几。

Dy 英文星期几。

 

字符转日期

select to_date('2017-10-8','yyyy-mm-dd') from dual;

时间戳转日期

 

select Systimestamp+0 FROM DUAL;

Select Cast(Systimestamp As Date) From dual;

To_date 使用失败？？？

日期转时间戳

Select to_timestamp('2006-01-01 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff') From dual;

 

nvl()函数：nvl(参数1,参数2)

如果字段名为参数1的值为null，就返回参数2；

select nvl(ate,sysdate) from wip_yanguu；

Nvl2()函数：nvl2（参数1，参数2，参数3）

如果字段名为参数1的值不为空返回参数2，为空就返回参数3.

select nvl2(ate，'yes','no') from wip_yanguu;

 

Nullif（）函数：nullif（参数1，参数2）

如果参数1的值= 参数2的值，那么就返回一个null，不然就返回参数1的值。

select nullif(11,age) from wip_yanguu;

 

Coalesce（）函数：coalesce（参数1，参数2.....）

返回第一个不为null的值。

select coalesce(null,null,3,5) from dual; ---3

 

多例函数：对某一列的所有进行处理。也被称为聚合函数。

Max（）：最大值。

Min（）：最小值。

Count（）：计数。

Sum（）：总数。

Avg（）：平均。

这些函数都直接忽略null值。

 

函数必须要有返回值。

 

条件表达式：

 when，then ，else  相当于 （if else）

select 

       case name -- 选择字段

          when '张三' then '三娃' --如果值等于‘张三’就替换为‘三娃’

            when '李四' then '四娃' --如果值等于 ’李四‘ 机替换为’四娃‘

              else

                '瓜娃' --如果条件都不满足就等于’瓜娃‘

                end as "名字" 

               from  wip_yanguu;

 

不是等于条件

CASE

   WHEN A.BEYOND_TIME>0THEN 2

    WHEN A.BEYOND_TIME<=0 and  A.BEYOND_TIME>=-1 THEN 1

          else

                0 --如果条件都不满足就等于0

                end as "BEYOND_TIME"

Decode（）函数，decode（参数1，值1，改变值1，值2，改变值2，.....，都不满足的值）

参数1为字段名字，如果参数1的值等于函数对应设置的值就会改变为对应设置的值，如果都没对应上可以在最后设置一个值去改变，如果不设置这位null值。

select decode(name,'张三'，'三娃'，'李四'，'四娃',name) from wip_yanguu；

分组表达式：

Group by

分组必须分组select后所以的值。

 

Where和haring 的区别

Where 后面不能接聚合函数，可以接单例函数。

Haring 是在group by之后执行的，可以接聚合函数。

 

多表查询：

笛卡尔积：实际上就是两张表的乘积，在实际中没有太大的意义。

select * from bbb b,aaa a --现在两张的乘积，3条数据乘以2条数据 会得到6条数据。

内连接：

隐式内连接：

Where 表1.字段 = 表2.字段 属于关联

等值内连接

select * from bbb b,aaa a where b."id" = a.id  --并积

不等式内连接

select * from bbb b,aaa a where b."id" <> a.id

其他的条件的内连接方法

select * from bbb b,aaa a where b."id" between a.id and a.id;

select * from bbb b,aaa a where b."id" in a.id ;

显示内连接：

select * from bbb b inner join aaa a on b."id" = a.id;  

Inner可以省略。

 

外连接：

左外连接:

Left outer join 显示左表中的所有记录。

select * from bbb b left outer join aaa a on b."id" = a.id; 

右外链接：

Right outer join 显示右表的所有记录。

select * from bbb b right outer join aaa a on b."id" = a.id; 

Outer可以省略不写。

 

Oracle中特有的外链接方法：(+) 实际上是如果没有对应的记录就加上空值。

select * from bbb b,aaa a where b."id" = a.id(+);

 

子查询：查询语句中嵌套查询语句；

单列查询：

select * from bbb where "id" = (select ID from aaa where classname = '你好');

多例查询：

select * from bbb where "id" in (select ID from aaa);

 

select * from bbb where "id"  not in (select id from aaa);

 

 

Rownum：为例，系统自动生成的结果必需要有1，生成数据的行号。

（oracle中特有，起始值是1）

  是查询完了之后在添加的行数，查询出来的结果必须要有用起始值1.

分页：

select * from 

(select rownum as r,a.* from wip_yanguu a) where r between 2 and 4; 

这样就可以查询出来不需要以纯在的1的列数。

 

 

Rowid：伪例，每一行记录所存放的真实物理地址。（每一条数据唯一）

select rowid ,w.* from wip_yanguu w

 

集合运算

并集：奖两个查询结果进行合并

or ：where 中使用

select * from yang_user where "name" = '李四' or "name" = '张三'  

Union ：去除重复的，并且排序

select "name" , age from yang_user

union 

select "name" , age from yang_user2;

Union all: 不会去除重复

select "name" , age from yang_user

union all

select "name" , age from yang_user2;

交集：将两个查询结果交差的结果 intersect

select "name" , age from yang_user

intersect

select "name" , age from yang_user2;

差集：将两个查询结果交差的结果 minus

select "name" , age from yang_user

minus

select "name" , age from yang_user2;

 

集合运算字段类型要一列，两表对应按照顺序写，列的数量要一致，如果不能一致，用 null 值表示。