一 . 数据类型
数据类型:
char : 固定长度类型,最多存2000个字节
varchar2:可变字符类型,最多存储4000个字节
long:大文本类型,最多存2个G
number:数值类型(默认18) number(5) 存五位数 最大存99999
number(5,2) 最大存999.99
integer : 相当于number 小数位数为0
oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。
int类型只能存储整数;
number可以存储浮点数，也可以存储整数；
number(8,1)存储小数位为1位，总长度为8的浮点数，如果小数位数不足，则用0补全；
number(8)存储总长度为8的整数；
int相当于number(22),存储总长度为22的整数。
decimal,numeric,int等都为SQL、DB2等数据库的数据类型，ORACLE为了兼容才将其引入；但实际上在ORACLE 内 部还是以NUMBER的形式将其存入。
date:日期时间型,精确到秒
java.util.date与java.sql.date相互转换问题:
先转long再转java.sql.date new java.sql.Date(date.getTime());
timestamp:精确到秒后9位
clob:存字符,最大4个G
blob:存二进制,最多4个G
创建表空间: (在Tablespaces下可见)
create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m
创建用户: (user下可见)
create user wateruser
identified by admin
default tablespace waterboss
以system登录给wateruser赋权
grant dba to wateruser
创建表:
create table T_OWNERS(
ID NUMBER PRIMARY KEY,
NAME varchar2(30),
address number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
)
修改表:
追加字段:
alter table T_OWNER add(
remark varchar2(20),
outdate date
)
修改字段:
alter table T_OWNER modify(
remark char(20),
outdate timespamp
)
修改字段名:
alter table T_OWNER rename column XXXX to XXXX
删除字段名:
alter table T_OWNER drop column NAME
删除表
drop table 表名称
数据删除
delete from T_OWNER where id=? (并未真正删除)
commit
truncate table T_OWNER (不需提交,先摧毁表结构,再重建表结构)
插入数据:
insert into T_OWNERS values(X,X,XX)
commit
插入当前日期: sysdate
修改语句:
update T_OWNERS set adddate=adddate-3 where id=1;
-----------------------------------------------------------------------------
JDBC连接:
驱动包:c:/oracle/product/10.2.0/db_1/jdbc/lib/ojdbc14.jar
Class.forName("oracle.jdbc.driver.OracleDriver");
DriverManager.getConnection("jdbc:oracle:thin@192.168.168.7:1521:orcl", "wateruser",
"admin");
-------------------------------------------------------------------
数据导出和导入(全库)
导出: 在虚拟机中打开命令提示符(一般用system用户)
exp system/admin full=y 默认导成EXPDATE.DMP文件
exp system/admin full=y file=water.dmp 重命名
导入:
imp system/admin full=y
exp system/admin full=y file=water.dmp
指定用户导入导出:
exp system/admin owner=wateruser file=wateruser.dmp
imp system/admin file=wateruser.dmp fromuser=wateruser
按表导入导出:
exp wateruser/admin file=a.dmp tables=t_account,a_area
imp wateruser/admin file=a.dmp tables=t_account,a_area
-----------------------------------------------------------------------------
条件查询:
where 是分组之前的条件 having是分组之后的条件,对聚合统计之后结果再统计
子查询: where 单行 多行
from
select 单行
分页查询
简单分页: select * from t_account t where rownum<10 (只能用<或<=号)
一般用子查询实现先产生值
嵌两层
select * from (select rownm r,t.* from t_account t)
where r<=20 and r>10
基于排序的分页:
嵌三层:
select * from
(select rownum r,t.* from
(select * from t_account t order by usenum desc) t
)
where r<=20 and r>10
--------------------------------------------------------------------
单行函数
字符函数: length('abcd')
select length('abcd') from dual
substr('原字符串',从第几位,截取字符数)
select substr('abcd',2,2) from dual
concat('abc','d')
select concat('abc','d') from dual
select 'abc'||'d'||'ef' from dual
数值函数:
四舍五入:round(100.5)
select round(100.567,2) from dual
数字截取:trunc(100.567)
select trunc(100.567,2) from dual
取模:mod(10,3)
select mod(10,4) from dual
日期函数:
sysdate:当前日期
加月:
select add_months(sysdate,2) from dual
所在月最后一天:last_day
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
转换函数:
数字转字符串:to_char()
select to_char(100) from dual
select 100||'' from dual
日期转字符串:to_char(sysdate)
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' from dual
字符串转日期:to_date('2016_03_10','yyyy-mm-dd')
select to_date('2016-03-10','yyyy-mm-dd') from dual
字符串转数字: to_number('100')
select to_number('100')+10 from dual
select '100'+0 from dual
其他函数:
空值处理:nvl(null,0) 判断第一个值是否为null 是则返回第二个值
select nvl(null,0) from dual 为null是返回0
nvl2(null,0,100) 为null则第二个参数, 否则为第一个餐数1
条件取值: decode 条件判断
select decode(100,1,2,3,4,100,200) from dual
select name,decode(ownertypeid,1,'居民',2,'行政',3,'商业','其他')
sql1999
case when then
select name,(case ownertypeid
when 1 then '居民'
when 2 then '行政'
when 3 then '商业'
else '其他'
end
) from t_owners
或者
case when then
select name,(case
when ownertypeid=1 then '居民'
when ownertypeid=2 then '行政'
when ownertypeid=3 then '商业'
else '其他'
end
) from t_owners
行列转换: 报表常用
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<='08' 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) , t.* from t_account t
值相同,排名相同,序号连续
select dense_rank() over(order by usenum desc) 排名,t.* from t_account t
序号连续,不管值是否相同
select row_number() over(order by usenum desc) 排名,t.* from t_account t
分析函数分页:
select * from (
select row_number() over(order by usenum desc) rownunber ,t.* from t_account t)
where rownumber<=20 and rownunber>10
集合运算:
并集
select * from t_owners where id>5
union all(有重复 union 无重复)
select * from t_owners where id<8
交集
select * from t_owners where id>5
intersect
select * from t_owners where id<8
差集
minus 去掉交叉部分
select * from t_owners where id>5
minus
select * from t_owners where id<8
select * from t_owners where id<8
minus
select * from t_owners where id>5
差集分页:
select rownum ,t.* from t_account t where rownum<=20
minus
select rownum, t.* from t_account t where rownum<=10
写法顺序: select ... from ... where ... group by... having... order by ...
执行顺序: from where group by having select order by
----------------------------------------------------------------------------------
视图:
一种虚表,封装sql语句
简化数据操作,不让看到敏感数据,权限,
创建或修改视图:
create [or replace] view view_name as subquery
简单视图:
创建简单视图:
create view view_owners1 as
select * from t_owners where ownertypeid=1
查询视图
select count(*) from view_owners1 where id in(1,2,3,4,5)
修改视图
update view_name set XXX where
带检查约束的视图:
create view view_address2 as
select * from t_address where areaid=2
with check option -- 带检查约束
update view_address2 set areaid=3 where id=4 --修改失败 因为该视图的条件是areaid=2
只读视图--只能查,不能改,删,增
create or replace view view_owners1 as
select * from t_owners where ownertypeid=1
with read only
带错误的视图(t_test不存在)
create force view view_test as
select * from t_test
复杂视图
多表关联:
create or replace view view_owners as
select * from t_owenrs ow,t_ownertype ot where ow.ownertypeid=ot.id
--键保留表:把主键保留下来的表,只能修改键保留表的数据
--注意视图最好加上别名
分组聚合统计: 无键保留表,所以不能修改
create view view_accountsum as
select year,month ,sum(money) money
from t_account
group by year,month
order by year,month
select * from view_accountsum
物化视图: 相当于真实的表,效率高,但占用空间
创建一个手动刷新的视图:
create materialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
begin
DBMS_MVIEW.refresh('MV_ADDRESS1','C');
end;
自动刷新
create materialized view mv_address1
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
创建时不生成数据:
create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
--产生数据
begin
DBMS_MVIEW.refresh('MV_ADDRESS3','C');
end;
增量刷新的视图: 前提先有物化视图日志:基表发生了那些变化
create materialized view log on t_address with rowid
create materialized view log on t_area with rowid create materialized view mv_address4
build deferred
refresh fast
as
select ad.rowid,ar.rowid,ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
