2019/5/24
SQL语句分类
1.DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
创建、删除、修改：库、表结构
- DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
增、删、改：表记录
- DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
- DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。本质上属于DML的一部分。
SQL基础查询语句
--查询某张表某列所有数据：
select * from dept;
--having,用于过滤分组,可以使用聚合函数
select swjg_dm , sum(se) se
from sb_zsxx
group by swjg_dm
having sum(se)>500000
--内关联(ISO)：
select e.emp_id,e.emp_name,d.dept_name
from emp e
inner join dept d on e.dept_id=d.dept_id
--内关联(oracle)：
select e.emp_id,e.emp_name,d.dept_name
from emp e,dept d
where e.dept_id=d.dept_id
--左外联(ISO)：
select e.emp_id,e.emp_name,d.dept_name
from emp e
left join dept d on e.dept_id=d.dept_id
--左外联(oracle)：
select e.emp_id,e.emp_name,d.dept_name
from emp e,dept d
where e.dept_id =d.dept_id(+)
--全外联(ISO)：
select e.emp_id,e.emp_name,d.dept_name
from emp e
full outer join dept d on e.dept_id=d.dept_id
--select子查询
select swjg_dm,
(select sum(se)
from sb_zsxx zs, dm_swjg dm
where zs.swjg_dm = dm.swjg_dm
and dm.jbdm like swjg.jbdm || '%') se
from dm_swjg swjg
where sj_swjg_dm = '22103000000'
SQL常用函数
--查询不从复值，倒序：distinct()内为去重字段， order by 后为排序字段，desc倒序，asc正序。
select distinct(id),name from T3 order by tid desc;
--绝对值：abs(n)内为n的绝对值
select abs (id) from emp;
--取整函数：ceil(n) 取大于等于数值n的最小整数
select ceil（-2.1）value from emp;//结果-2
--取整函数：floor()返回小于或等于数值表达式的最大正数
select floor (-2.1) value from emp;//结果-3
--四舍五入：round（n,x）四舍五入，n为查询数，x为指定小数位数
select round (1.235,3) value from emp ;//结果1.24
--取整函数：trunc()类似截取函数，按指定的格式截取输入的数据。
1.【trunc(for dates)】TRUNC()函数处理日期
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,返回当月的第一天
select trunc(sysdate,'d') from dual;--2017/2/12,返回当前星期的第一天，即星期天
select trunc(sysdate,'dd') from dual;--2017/2/13,返回当前日期,今天是2017/2/13
select trunc(sysdate ,'HH24') from dual;--2017/2/13 15:00:00,返回本小时的开始时间
select trunc(sysdate ,'MI') from dual;--2017/2/13 15:13:00,返回本分钟的开始时间
2.【TRUNC(for number)】TRUNC()函数处理number型数字
select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(123.567) from dual;--123,默认截去小数点后面的部分;
--取平方根：sqrt()
select sqrt() value from emp;
--取随机数： dbms_random (minvalue,maxvalue )
select dbms_random.value (2,7) value from emp;
--取符号：sign()
select sign (-3) value from emp;
select sign (2) value from emp;
--求字符序号:ascii()
select ascii(a) value from dual;
--求序号字符:chr()
select chr(97) value from dual;
--链接：concat()
select concat("11","22") value from dual;(1122)
--首字母大写：InitCAP()
select INITCAP(abc def ghi) value from dual;(Abc Def Ghi)
--取集合的最大值:greatest(value)
select greatest(-1,3,5,7,9) value from dual; --(9)
--取集合的最小值:least(value)
select least(-1,3,5,7,9) value from dual; --(-1)
--截取字段
substr(F_xada,1,length(F_xada)-4)缩减字段
--NVL条件
NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。
NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。
select nvl(null,10) value from dual;(10)
SQL常用查询技巧
--rownum小于某个数时可以直接作为查询条件（注意oracle不支持select top）
select * from student where rownum <3;
--区间查询
select * from (select rownum rn, student.* from student) where rn >3 and rn<6;
--排序+区间查询1
select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn<9 and p.rn>6;
--排序+区间查询2
select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t where rownum<9 )p where p.rn>6;//效率远高于方式一
--在查询的字段后追加符号
select t.id||'%', t.rowid from table1 t
--并一列
select SS a from a1
union
select SS b from b1
--处理字段Null值：nvl(空值，代替值)
select nvl(null,10) value from dual; --(10)
--获取当前时间，并赋值查询条件：
select
F_PROVINCE,F_CITY,F_COUNTY,F_PROVINCEID,F_CITYID,F_COUNTYID,F_ADRESS,F_TITLE,F_DATAFROM,F_SJLY,F_FSSJ,F_RAINFALL
from
W_CSLQFSXX t,
(select trunc(sysdate, 'yyyy') as bn from dual) bn
where
to_char(F_DATATIME,'yyyy') = to_char(bn.bn,'yyyy')
特殊用法1：case when
select 与 case结合使用最大的好处有两点，一是在显示查询结果时可以灵活的组织格式，二是有效避免了多次对同一个表或几个表的访问。查询可用于筛选select 后的查询内容，也可用于where内做查询条件，用在group内做分组条件。在Case函数中，可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS，可以进行子查询，从而 实现更多的功能。
语句1: 普通用法（case ### when ## then ## else ## end）
select
sum( case u.sex when 1 then 1 else 0 end) 男性,
sum( case u.sex when 2 then 1 else 0 end) 女性,
sum( case when u.sex<>1 and u.sex<>2 then 1 else 0 end) 性别为空
from users u;
语句2: GROUP BY CASE WHEN 用法
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
语句3: 两个表数据是否一致的检查有两个表
tbl_A,tbl_B，两个表中都有keyCol列。现在我们对两个表进行比较，tbl_A中的keyCol列的数据如果在tbl_B的keyCol列的数据中可以找到， 返回结果'Matched',如果没有找到，返回结果'Unmatched'。
要实现下面这个功能，可以使用下面两条语句
--使用IN的时候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) THEN 'Matched' ELSE 'Unmatched' END Label
FROM tbl_A;
--case查询语句
select a1.orgname as orgname ,a2.lq as lq ,a2.jyl as jyl from
(--获取组织表当前账户下级信息
select orgid,orgname from (
select t.orgid,t.orgname,t.orgtype from Sys_Org t
start with t.orgid=#orgId#
connect by t.orgsupid= prior t.orgid)a1
where length(a1.orgid)<=6
and a1.orgtype=(case
when length(#orgId#) <=6 then
2
else
1
end )）a1
left join (
--获取省or市数据
select
h2,sum(lq)as lq ,sum(jyl)as jyl from(
select
(case
when length(#orgId#)<=6
then
w.a1
else
w.a3
end
)as h2,
w.lq,w.jyl from (
select w1.a1,nvl(w2.b1,0) as lq ,nvl(w1.a2,0) as jyl ,w1.a3 from
(
--各市19年 降雨量合计
select
city_code as a1, sum(t1.rain_24) as a2,substr(city_code,0,2)||'0000' as a3
from cma_cleandata t1,
(select trunc(sysdate,'yyyy')as bn from dual )bn
where
to_number(to_char(t1.weather_time,'yyyy'))=to_number(to_char(bn.bn,'yyyy'))
group by city_code
)w1
left join
(---各市19年涝情次数
select a1,count(1)b1 from (
select
f_cityid as a1
from W_cslqfsxx t,
(select trunc(sysdate,'yyyy')as bn from dual )bn
where to_number (to_char(t.F_fssj,'yyyy'))=to_number(to_char(bn.bn,'yyyy'))
and f_cityid is not null
)
group by a1
)w2
on rtrim(ltrim(w1.a1,' '))= rtrim(ltrim(w2.a1 ,' '))
)w)group by h2
)a2
on a1.orgid=a2.h2
order by a1.orgid asc
特殊用法2：oracle树型结构表查询
层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,在做页面目录、城市地区、商品分类等数据库的设计的时候会用到树形结构，其查询语法如下:
SELECT [LEVEL] ,column,expression,...//LEVEL:为伪列,用于表示树的层次，
FROM table_name
[WHERE where_clause]
[START WITH start_condition]//start_condition：层次化查询的起始条件，
[CONNECT BY PRIOR prior_condition]];//prior_condition：定义父节点和子节点之间的关系
举例如：
select orgid,orgname from (
select t.orgid,t.orgname,t.orgtype from Sys_Org t
where length(a1.orgid)<=6
and a1.orgtype=(case
when length(#orgId#) <=6 then
2
else
1
end )
start with t.orgid=#orgId#
connect by t.orgsupid= prior t.orgid)a1
日常开发优化：
在Oracle中可以用：start with···connect by···语句对层级（树形）结构的数据表进行层级汇总查询，在实际环境中如果层级数据较大，会存在查询速度很慢的情况，此时要把筛选条件放入查询表并将查询结果作为新表进行查询，会大大提高查询的效率。
。。。。总结继续中！！！
