Oracle的一些简单入门

一、Oracle数据库：sql
1.SQL语句的种类；
oracle / db2(ibm) / sybase
sql server(asp,iis) / mysql (php,tomcat)

DQL:select
DML:insert / update / delete
DDL:create / drop / alter / truncate
TCL:commit / rollback / savepoint
DCL:grant / revoke

2.SQL操作
单表 / 连接 / 单行函数 / 组函数 /
DDL操作

建立数据环境：
1.建表：列名，列的数据类型，约束条件，表间关系
Book：id, isbn, name, price, author, ptime,brief,typeid
1001,abcd, tianlongbabu, 35, jinyong, good book, 1

BookType: id, name
1, wuxia
2, java
3, oracle
4, movie

create table booktype_ning(
id number(2) primary key,
name varchar2(10)
);
insert into booktype_ning
values(1, 'wuxia');
insert into booktype_ning
values(2, 'java');
insert into booktype_ning
values(3, 'oracle');
commit;

create table book_ning(
id number(4) primary key,
isbn char(10) unique,
name varchar2(20) not null,
price number(5,2),
author varchar2(20),
ptime date,
brief varchar2(200),
typeid number(2)
);
alter table book_ning
add constraint bookning_tid_fk
foreign key (typeid)
references booktype_ning(id);

alter table book_ning
add constraint bookning_price_ck
check (price > 0);

insert into book_ning values(
1001, 'abcd', 'tianlongbabu', 35, 'jinyong', to_date('1970-05-05','yyyy-mm-dd'),
'good book', 1);

insert into book_ning values(1002, '1234','tij', 50, 'bruce', to_date('1999-02-02','yyyy-mm-dd'),'another book', 2);

commit;

--删除表数据，保留表结构，立即生效
--不需要事务参与，立即提交
truncate table book_ning;

--删除表结构，所有表数据都被删除。
drop table book_ning;

2）查询
--单表查询：
select * from book_ning
where price > 40
order by price;

--查询：书名和类别名 内连接，等值连接
select b.name, bt.name
from book_ning  b
join booktype_ning bt
on b.typeid = bt.id;

select b.name, bt.name
from book_ning  b, booktype_ning bt
where b.typeid = bt.id;

--把没有书的类别查出来
select b.name, bt.name
from book_ning  b
right outer join booktype_ning bt
on b.typeid = bt.id;

--把没有类别的书查出来
select b.name, bt.name
from book_ning  b
left outer join booktype_ning bt
on b.typeid = bt.id;

--全外连接
select b.name, bt.name
from book_ning  b
full outer join booktype_ning bt
on b.typeid = bt.id;

select *
from book_ning  b
right outer join booktype_ning bt
on b.typeid = bt.id;

--t1：驱动表，t2: 匹配表
t1 left outer join t2 --左外连接
t2 right outer join t1
--t2：驱动表，t1:匹配表
t1 right outer join t2 --右外连接
t2 left outer join t1

select...
from
t1 join t2 on t1.c1 = t2.c2 and t1.c = 1;

t1 join t2 on t1.c1 = t2.c2 where t1.c = 1;

t1, t2 where t1.c1(+) = t2.c2;

select
from t1 join t2 on t1.c1 = t2.c2
            join t3 on t3.c3 = t2.c

--查询条件
select *
from book_ning
where price > 40;

select *
from book_ning
where price between 40 and 80; [40, 80]

select *
from book_ning
where name like '%t%i%';

select *
from book_ning
where typeid in (1, 2);

--空值的比较，使用is null,不是= null
--不是空值：is not null
select *
from book_ning
where ptime is null;

3）函数：单行函数 / 组函数

数字 / 字符 / 日期

单行函数：
字符函数：upper / lower / initcap
lpad / rpad / length / substr / replace / trim
select * from booktype_ning
where initcap(name) = 'Java';

select id, lpad(name, 10,'*')
from book_ning;

数字函数：round / trunc / mod
 select name, trunc(price * 10 * 0.57)
 from book_ning;
select mod(price, 10) from book_ning;

日期
months_between / next_day
--dual：虚表
select sysdate from dual;
select last_day(sysdate) from dual;
select sysdate + 7 from dual;
select name, sysdate - ptime from book_ning;

默认的显示格式：DD-MON-RR

假设现在是2011
                YY              RR
05           2005            2005
99           2099            1999

转换函数：to_date / to_char / to_number
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss day dy')
from dual;

通用函数：nvl  / decode / coalesce
select name, nvl(price, 0),
 to_char(nvl(ptime, sysdate),'DD/MM/YY')
from book_ning;

组函数 count / max / min / avg / sum
select typeid, count(*)
from book_ning
group by typeid
having count(*) > 1;

select typeid,count(id)
from book_ning
group by typeid;

--4）事务
insert...
update...
create...（隐式提交）
delete...
alter....（隐式提交）
insert
commit;（显式提交）
delete...
rollback; (显式回滚)
insert...
commit;
rollback;

insert...1,
savepoint A;
insert ...2
savepoint B;
insert ...3
rollback to B;
commit;

Transaction(*******)

3.Oracle数据库的主要对象
1)table
2)view：视图
create or replace view v_book
as
select id, name, price from book_ning
with read only;
--视图的使用方式和表相同：
select * from v_book;
desc v_book
--简单视图，如果没有with read only短语，可以通过视图修改基表数据
update v_book set price = 100 where id = 1001;
--复杂视图，不可以通过视图改基表
create view v_book_ning
as
select typeid, count(*)  count_book
from book_ning
group by typeid;

3)sequence序列
mysql:
create table book(
id int primary key auto_increment,
name char(20));
insert into book(name) values('tlbb');
insert into book(name) values('bxj');
select * from book;
1  tlbb
2  bxj

--oracle使用序列提供主键值。
--起点是2000,步进是10
--默认起点是1，步进是1
create sequence myseq_ning
start with 2000 increment by 10;

insert into book_ning (id, name)
values(myseq_ning.nextval, 'baimaxxf');

insert into book_ning (id, name)
values(myseq_ning.nextval, 'ldj');

insert into book_ning (id, name)
values(myseq_ning.nextval, 'xsfh');

--nextval: 取出下一个值
--currval: 当前值
select myseq_ning.nextval from dual;

insert into booktype(id, name)
values(myseq_ning.nextval, 'history');

4)constraint

5) index

二、JDBC
1.概念
Java  ----- JDBC API ------ Database
                 jar包

2.利用JDBC API访问数据库
1)ojdbc14_11g.jar
加入到项目的build path中；
2)编写java程序，访问数据库。

a)获得连接数据库的参数；
b)注册驱动
c)获得连接
d)获得语句对象
e)发送sql语句，并返回结果。
f)关闭资源

2.分页策略
1）基于缓存
全部取出放在内存中。
根据页数计算显示哪些记录。
好处：除了第一次以后每次获取数据都很快；数据库只访问一次。
坏处：内存压力；第一次取数据时慢。

可滚动的结果集。
Statement
PreparedStatement : 预编译的语句对象
      select * from book where price > ?

 

2）基于查询
只取出所查询的页面的数据记录。
好处：没有内存压力。每次取数据时间都差不多。
坏处：频繁的数据库访问；每次取数据会有延迟。

获取数据库中41-50条数据
rownum：类似行号，oracle独有的伪列

--行内视图 / 匿名视图
select id
from (
    select rownum rn, id
    from (
        select id from test order by id)
)where rn between 41 and 50;
--取10条，不排序
select id
from (select rownum rn, id from test
order by id )
where rn > 90 and rn < 100
order by id;

--mysql:
select id
from test
limit 41, 10;

oracle 伪列：rownum, rowid

构造大表(PL/SQL匿名块)：
SQL>create table test(id number);
SQL>begin
           for i in 1 .. 1000 loop
              insert into test values(i);
            end loop;
            commit;
         end;

-》指针的初始位置在beforeFirst
 1
 2
 3