with … as … select
- 在写SQL时,对两个已经order by 的语句进行union时失败,后来发现在Oracle sql中,要求order by是select语句的最后一个语句,而且一个select语句中只允许出现一个order by语句,而且order by必须位于整个select语句的最后。
- union操作实际上做了两部分动作:结果集合并 + 排序,
- union all只进行结果集简单合并,不做排序,效率比union高
- 因此使用with … as … select
with
s1 as (select * from table1 t1 order by t1. c1 ),
s2 as ( select * from table1 t2 order by t2.c1 )
select * from s1 union all (此处可以换为 union ) select * from s2
参考这个:https://www.icode9.com/content-2-602860.html
- 对with … as …select的说明:
其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了
with as 相当于虚拟视图
参考这个:https://www.cnblogs.com/mingforyou/p/8295239.html
case when
- CASE WHEN条件表达式函数:类似JAVA中的IF ELSE语句
CASE WHEN SCORE = 'A' THEN '优'
WHEN SCORE = 'B' THEN '良'
WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
参考这篇:https://blog.csdn.net/rongtaoup/article/details/82183743
group by ,where,having
group by ,where和having可以同时使用,但要注意SQL的执行顺序,where>group by>having>select,不能在where后面添加分组后才有的查询
https://deepinout.com/sql/sql-dql/sql-select-group-by.html
exists
sql 递归?
sql执行顺序
一条完整的sql的执行顺序是:
- FROM子句组装数据(包括通过ON进行连接);
- WHERE子句进行条件筛选;
- GROUP BY分组 ;
- 使用聚集函数进行计算;
- HAVING筛选分组;
- 计算所有的表达式;
- SELECT 的字段;
- ORDER BY排序;
- LIMIT筛选
sql的两个顺序:
书写顺序
select … from … where … group by … having … order by …
执行顺序
from>where>group by>having>select的字段>distinct>order by>limit
sql执行原理分析:https://deepinout.com/sql/sql-dql/sql-select-statement-execution-order.html
distinct说明
DISTINCT需要放到所有列名的前面,如果写成SELECT name, DISTINCT attack_range FROM heros
会报错。
DISTINCT其实是对后面所有列名的组合进行去重
not in 失效问题oracle
原因:not in 包含的查询中存在null,需要把null去掉
DBLink :主要用来跨数据库连接查询
https://help.aliyun.com/document_detail/99941.html
使用:
创建DBLink
CREATE PUBLIC DATABASE link dblink CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘服务ip/serviceName’;
查询 当前库 的dblink
select * from dba_db_links;
删除当前数据库的dblink
DROP PUBLIC DATABASE LINK 名称(dblink名称);
然后注意跨库联表的时候 再跨库的表名后面加 @dblink名称
update IM_USER_INF@dblink A set A.user_ORGID =(select B.ORGID from ORG_EMPLOYEE B where B.EMPID=A.EMPID)
条件查询空值判断
where 关键字后面加 manager is not null 或者 manager is null
order by 中文排序和空值排序
https://deepinout.com/sql/sql-dql/sql-select-order-by.html
常用函数
nvl()
它是一个空值转换函数,nvl(表达式1,表达式2),如果表达式1的值为空,nvl返回值为表达式2的值,否则返回表达式1的值。
表达式的值可以是数字型,字符型和日期型,DNA必须数据类型一致。