Oracle 语法讲解

–创建表空间
create tablespace waterboss
datafile ‘C:\waterboss.dbf’
size 100m
autoextend on
next 10m;

–创建用户
create user wateruser
identified by itcast
default tablespace waterboss;

如果一次想执行多条语句，语句之间使用分号(;)分隔
如果只想执行某一条语句，那么，用鼠标选中这一条语句，然后就可以执行，加不加分号无所谓。

–给wateruser用户赋予DBA权限
grant dba to wateruser

–创建业主表
CREATE TABLE T_OWNERS(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(30),
ADDRESSID NUMBER,
HOUSENUMBER VARCHAR2(30),
WATERMETER VARCHAR2(30),
ADDDATE DATE,
OWNERTYPE NUMBER
)

表的增删改查






数据的增删改查



–插入语句
insert into T_OWERTYPE (ID,NAME) VALUES (1,‘居民’);

insert into T_OWNERS VALUES (1,‘张三丰’,1,‘1-1’,‘123456’,sysdate,1);
commit;

–修改语句
update T_OWNERS set adddate=adddate-3 where id=1;
commit;

–删除语句 效率低 (从表中删除这条数据,将数据保存在回滚段中，回滚时，再次调入)
delete from T_OWNERS where id=1;
commit;

–删除语句(毁掉表结构，重建表）执行后，不可以回滚。效率高
truncate table T_OWERTYPE

–分析函数
–值相同,排名相同,序号跳跃
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) rownumber,t.* from t_account t)
where rownumber<=20 and rownumber>10

–集合运算
–并集(包含重复记录)
select * from t_owners where id>5
union all
select * from t_owners where id<8
–并集(去掉重复记录)
select * from t_owners where id>5
union
select * from t_owners where id<8

–交集
select * from t_owners where id>5
intersect
select * from t_owners where id<8

–差集
select * from t_owners where id>5
minus
select * from t_owners where id<8
–差集分页
select rownum,t.* from t_account t where rownum<=20
minus
select rownum,t.* from t_account t where rownum<=10