一、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
join booktype_ning bt
on b.typeid = bt.id;
select b.name, bt.name
from book_ning
where b.typeid = bt.id;
--把没有书的类别查出来
select b.name, bt.name
from book_ning
right outer join booktype_ning bt
on b.typeid = bt.id;
--把没有类别的书查出来
select b.name, bt.name
from book_ning
left outer join booktype_ning bt
on b.typeid = bt.id;
--全外连接
select b.name, bt.name
from book_ning
full outer join booktype_ning bt
on b.typeid = bt.id;
select *
from book_ning
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
--查询条件
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 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
05
99
转换函数:to_date / to_char / to_number
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss day dy')
from dual;
通用函数:nvl
select name, nvl(price, 0),
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(*)
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
2
--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
2.利用JDBC API访问数据库
1)ojdbc14_11g.jar
加入到项目的build path中;
2)编写java程序,访问数据库。
a)获得连接数据库的参数;
b)注册驱动
c)获得连接
d)获得语句对象
e)发送sql语句,并返回结果。
f)关闭资源
2.分页策略
1)基于缓存
全部取出放在内存中。
根据页数计算显示哪些记录。
好处:除了第一次以后每次获取数据都很快;数据库只访问一次。
坏处:内存压力;第一次取数据时慢。
可滚动的结果集。
Statement
PreparedStatement : 预编译的语句对象
2)基于查询
只取出所查询的页面的数据记录。
好处:没有内存压力。每次取数据时间都差不多。
坏处:频繁的数据库访问;每次取数据会有延迟。
获取数据库中41-50条数据
rownum:类似行号,oracle独有的伪列
--行内视图 / 匿名视图
select id
from (
)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
-》指针的初始位置在beforeFirst