Oracle学习及练习

---

1-sql语句

创建表空间

create tablespace waterboss
datafile 'D:\Oracle19\waterboss\waterboss.dbf'
size 100m  -- 存储大小
autoextend on 
next 10m

创建用户

create user wateruser 
identified by bc123
default tablespace waterboss

赋予权限

grant dba to wateruser

子查询 where

单横操作符 =等于， >大于， >=大于等于， <小于， <=小于等于 ，<>不等于
select * from T_account where year='2012' and  month='01' and usenum>
(select AVG(USENUM) from T_account where year='2012' and  month='01' );

多横子查询 in等于列表中任何一个，any和子查询返回任意一个值比较，all和子查询返回的所有值比较

select * from t_owners where addressid in 
(select a.id from T_address a where name like '%花园%')

不含有花园

select * from t_owners where addressid not in 
(select a.id from T_address a where name like '%花园%')

子查询 from

select * from
(select o.id,o.name,t.name "业主类型" from t_owners o,t_ownertype t where ownertypeid=t.id)
where 业主类型='居民';

子查询 select

select id,name,(select name from t_address where id=addressid) "地址名称" from t_owners
多重嵌套查询
select id,name,
(select name from t_address where id=addressid) "地址名称",
(select areaid from t_address where id=addressid) "所在区域id",
(select (select name from t_area where id=areaid) from t_address where id=addressid) "所在区域"
 from t_owners;

分页查询

简单分页
select * from t_account where rownum<=10;	

赋值r输出
select * from (select rownum  r,t.* from t_account t)   where r>=10 and r<20;

升序排列
select * from (select rownum  r,t.* from (select * from t_account t order by usenum desc) t)   where r>=10 and r<20;

单横函数

字符函数 length
伪表 dual
select length('abcde') from dual; 字符长度
select substr('abcde',4,2) from dual; 截取字符
select concat('abcde','abnnn') from dual; 字符拼接 
select 'abcde'||'2'||'w' from dual; 推荐使用
select round(100.523145) from dual; 四舍五入函数ROUND
select round(100.523145,2) from dual; 保留两位小数
select trunc((100.523145) from dual数字截取
select trunc(100.523145) from dual 截取整数
select mod(10,3) from dual取余

日期函数

select add_months(sysdate ,2)  from dual 加月
select last_day(sysdate)  from dual 所在月的最后一天
日期截取
select trunc(sysdate)  from dual 按日截取
select trunc(sysdate,'mm')  from dual 按月截取
select trunc(sysdate,'yyyy')  from dual 按年截取
select trunc(sysdate,'hh')  from dual 按小时截取
select trunc(sysdate,'mi')  from dual 按分钟截取

转换函数

select to_char(1024)  from dual 数字转字符串
select to_char(sysdate,'yyyy-mm-dd')  from dual 日期转字符串
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'  from dual 年月拼接

字符串转日期

select to_date('2016-03-10','yyyy-mm-dd')  from dual

字符串转数字

select to_number('12589')  from dual

其他函数-空值处理

select nvl(null,0)  from dual 第一个参数是null就返回0 第一个参数100 就返回100
select l.*,nvl(maxnum,99999) from t_pricetable l 查询为空的默认填99999

条件取值

select name,decode(ownertypeid,1,'商业',2,'居民') from t_owners

另一种方式

select name,(case 
when ownertypeid=1 then '商业'
when ownertypeid=2 then '居民'
else '其他'
  end) from t_owners

 select ((case when 100>1 then 5 else null end)*5) from dual  --条件计算

行列转换

select 
(select name from t_area where id=areaid) "区域",
sum(case when month='01' then money else 0 end) "一月",
sum(case when month='02' then money else 0 end) "二月",
sum(case when month='03' then money else 0 end) "三月",
sum(case when month='04' then money else 0 end) "四月",
sum(case when month='05' then money else 0 end) "五月",
sum(case when month='06' then money else 0 end) "六月",
sum(case when month='07' then money else 0 end) "七月",
sum(case when month='08' then money else 0 end) "八月",
sum(case when month='09' then money else 0 end) "九月",
sum(case when month='10' then money else 0 end) "十月",
sum(case when month='11' then money else 0 end) "十一月",
sum(case when month='12' then money else 0 end) "十二月"
from t_account where year='2012' group by areaid

添加条件

select 
(select name from t_area where id=areaid) "区域",
sum(case when month>='01' and month<='03' then money else 0 end) "第一季度",
sum(case when month>='04' and month<='06' then money else 0 end) "第二季度",
sum(case when month>='07' and month<'=09' then money else 0 end) "第三季度",
sum(case when month>='10' and month<'=12' then money else 0 end) "第四季度"
from t_account where year='2012' group by areaid

分析函数

值相同 排名相同 序号跳跃
select rank() over(order by usenum desc),l.* from t_account l	
值相同 排名相同 序号连续
select dense_rank() over(order by usenum desc),l.* from t_account l
序号连续 不管值是否相同
select row_number() over(order by usenum desc),l.* from t_account l

集合运算

并集(加all就会查出重复的所有结果)