oracle

基本概念
DDL 数据定义语言 -  建立数据库对象
    create /alter/ drop/ truncate
DML 数据操纵语言 - 数据的查看和维护
    select / insert /delete /update
TCL 事务控制语言 -  数据是否保存到数据库中
    commit / rollback / savepoint
DCL 数据控制语言 -- 查看对象的权限
grant / revoke
数据库设计
关系数据模型的组成
一张或多张表
表的索引
视图
触发器
表与表的关系
数据库Schema
概念上的 ： 一组DDL
物理上的 ： 一个命名空间，包含了表，过程，视图的集合

常用命令
connect 用户名/密码  连接到指定用户
desc tab_name             察看表tab_name的结构
quit/exit             退出
clear screen          清除屏幕
set linesize 200      设置一行显示200个字符
set pagesize 20       设置每页显示20行
dbms_output.put_line() 打印,类似于System.out.println();
set serveroutput on 打开服务器输出，否则上面打印语句报错
edit 编辑缓存中的语句
/ 执行上一次语句块/可重复执行
@ sqlpath 执行某sql文件
@@ sqlfile 执行sqlpath环境变量中的文件
spool file 打印日志到文本文件
spool off 停止打印
# 在输入sql语句的过程中临时先运行一个sql*plus命令
--查看大字短的大小
SELECT DBMS_LOB.GETLENGTH(MYCLOB) FROM ATTACHMENT;  //MYCLOB是列名,ATTACHMENT 是表名
SELECT DBMS_LOB.GETLENGTH(MYBLOB) FROM ATTACHMENT;
常用表
user_constraints   用户约束
user_tables 用户表
all_all_tables 所有用户表
all_constraints 所有约束
user_cons_columns 约束列
all_cons_columns 所有约束列
nls_session_parameters  当前会话信息
v$nls_parameters   系统参数
数据类型
字符型
char   最大2000个字节 定长
varchar2最大4000个字节 变长
数字类型
number   10的-38次方 到10的38次方  可以表示小数 也可以表示整数
日期
date   包含年月日和时分秒  7个字节
大对象
clob 字符型大对象 >4000字节 最大4G
blob 二进制大对象    图像/声音  4G
伪类型
%type 可以引用某列的类型
%rowtype  可以引用某表，作为类型
rownum 结果集每行的行号
表操作基础
创建表
create table tab_name
(
column_name data_type ,
column_name data_type ,
column_name data_type ,
……..
)
修改表
插入列
alter table tab_name add(column_name data_type)
修改列
alter table tab_name modify(column_name data_type)
删除列
alter table tab_name drop column column_name;
表重命名
rename tab_name to change_name
删除表
drop table tab_name删除记录和结构,不可恢复

添加数据
insert into tab_name[(column_name , column_name…)] values(value,value…)
序列 sequence  <仅oracle中有>
创建序列
create sequence 自定义序列名字;
获得下一个值
seq_name.nextval
获得当前值
seq_name.currval
复杂序列   序列的nextval是不可逆的
create sequence 自定义序列名字
increment by 5  <--递增5-->
start with 0  <--从0开始-->
maxvalue 100  <--最大值 也可以使用nomaxvalue-->
minvalue -100  <--最小值 也可以使用nominvalue-->
cycle <--循环增长 也可以使用nocycle-->
cache 30 <--缓存 也可以使用nocache-->

修改数据
update tab_name set expression
删除数据
delete from tab_name 删除记录，不删除结构，可以恢复
delete from tab_name where expression 条件删除
truncate table tab_name 仅清除数据，保留结构，不可恢复
约束
主键约束 用来唯一表示一条数据的字段，其值不能重复，不能为null
create table test
(
nationality varchar2(20),
city varchar(20),
constraint nick_pk primary key(nationality,city)
);
外键约束 引用其他表的主键到本表,在本表中叫外键，用来做表关系
create table test
(
nationality varchar2(20),
city varchar(20),
constraint nick_fk foreign key(nationality) references
tab_name(nationality)
);
<** create table test1
(
id varchar2(20),
name varchar(20),
tid varchar(20),
primary key(id),
constraint test1 foreign key(tid) references
test2(tid)
);
“constraint test1:外键关联名”
create table test2
(
tid varchar2(20),
descp varchar(20),
primary key(tid)
);  **>
非空约束
create table test
(
nationality varchar2(20) not null,
);
唯一约束
create table test
(
nationality varchar2(20) ,
constraint nick_uk unique(nationality)
);
检查约束
create table test1
(
nationality varchar2(20) ,
constraint nick_ck check(nationality <> '美国')
);
默认值
default
设定约束条件无效
disable|enable constraint_name
级联删除<级联更新要使用触发器>
references … ON DELETE CASCADE
运算符
< 小于
<= 小于等于
> 大于
>= 大于等于
= 等于
!= 不等于
<> 不等于
:= 赋值
is null 如果操作数为null返回true
like 比较字符串 _代表一个字符 %代表多个字符

create table test
(
a number
)
insert into test value(10);
insert into test value(20);
insert into test value(30);
between 验证值是否在范围之内
select * from test where a between 10 and 20;
in 验证操作书在设定的一系列值中
select * from test where a in(1,10,20,30);
all 表示子查询返回值中的所有值,相当于比较最大值
select sal from emp where sal > all( select sal from emp where sal<2000);
any 表示子查询返回值中的任意值，相当于最小值

and 两个条件都满足
or 只满足一个
not 取反
+ 加
- 减
* 乘
/ 除
基础查询
<--基本的select语句-->
select 列名,列名,.. from 表名,表名,.. where 条件 group by 列名 having 条件
select 是必须的 ，后面添你要查询的列名 ，* 代表所有
from 是必须的 ， 后面填写你要查询的表名，可以有多个
where 可选的，后面填写你的查询条件
group by 可选的，对聚合进行分组，当查询内容多于一列且包含聚合函数时使用
having 仅用于group by 的关键字，和where作用一样

查询最大值:
select * from product where price not in (select p.price from product p, product d
where p.price<d.price)


--价格大于30排序的
select name,price from book where price > 30 order by price desc;

--列出lisi写的书
select b.name,a.aname from book b,authod a where b.authid=a.authid and a.aname='lisi'

select b.name,a.aname from book b,authod a where b.authid=a.authid and a.authid=1


<--消除相同的行-->
select distinct 列名,列名,..from 表名

<--排序 order by-->
select 列名,列名,.. from 表名 order by 要排序的列名

<--注意：order by指令需聚合函数配合使用，否则只能是单列-->

<--聚合函数-->
avg 平均值
select avg(emp.sal) from emp;
sum 求和
select sum(emp.sal) from emp;
max 最大值
select max(emp.sal) from emp;
min 最小值
select min(emp.sal) from emp;
count 总数
select count(emp.sal) from emp;

<--排序-->
升序
select * from emp order by emp.sal ;
降序
select * from emp order by emp.sal desc;

<--数据复制-->
select * into myemp from emp;

<--表复制-->
create table myemp as select * from emp;

<--小技巧-->
哑元表 在没有查询表的情况下使用
select 1+1 from dual
查询分割 ||
select a.a1 ||'----'|| a.a2 from a;
查询行数限制
select * from a where rownum<=2;
查询表结构
desc tab_name
数据复制
select tab_name into tab_name|var from tab_name

--检索订单总额最大的用户
select * from users where id =
(
   select userid from
   (
     select userid, sum(price*quantity) as total
     from orders group by userid order by total desc
   )
   where rownum=1
);

mysql:技巧,可将文件的内容插入到表中.
load data local infile 'd:/sql.txt' into table product;
高级查询
笛卡尔乘积
select dname,ename from emp,dept;  这种通过多张表简单对加是没有太大意义的

集合运算
交集 用来得到两个或者多个不同集合的共同元素,两个集合的交集就是其中所有属性相等的元素.交集有一个严格的限制：每个结果集中所有列都必须匹配相等

减集 用来查找在一个集合中出现过，而在另一个集合中没有出现的元素,与交集相反的是: 每个结果集中所有列都必须匹配不相等

并集 用来合并两个或者多个类似的集合

交集 intersect
select语句 intersect select语句
减集 minus
select语句 minus select语句
并集 union or union all
select语句 union select语句

内连接
在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
select dname,ename from dept,emp where dept.deptno = emp.deptno


外连接
条件列使用(+)，则此列为外连列，主列信息全部显示，外连列没有则显示空
多行子查询
返回多个行，必须包含一个多行运算符。
视图
视图语法
创建视图: create [or replace] view <名字> as <select 语句>
视图用于简化查询，视图中实际存放的是一个查询语句而已，返回的是结果集
在视图中可以修改数据，但是: 建立视图的查询语句必须是一个简单的select(只查询一个表,并且不含有分组函数)
查看视图: select * from 视图名;

程序块
在这之前，我们所有的sql语句都是一句一句执行的，如果我们把很多事情看作一个整体提交执行的话，必须使用程序块。

声明部分：声名变量及初始化
关键字：declare

执行部分：存放所有可执行的代码，这些代码包含在begin/end中
关键字：begin end

每个指令结束用;表示，--表示注释


--这是一个示例
declare
i number:=5; y number:=6;
begin
i:=i+y;
dbms_output.put_line(i);
end;
/
流程控制
--条件if......then...elseif.. .end if;
declare
i number:=50;
begin
if i=50
then dbms_output.put_line(i);
elsif i<50
then dbms_output.put_line(0);
end if;
end;
/
-----使用loop循环(exit when 退出条件)
declare
i number;
begin
i:=0;
loop
exit when i=10;
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

-----使用while-loop循环
declare
i number;
begin
i:=0;
while i<10
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/

-----使用for-loop循环

begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/

-----使用for-loop反序循环

begin
for i in REVERSE 1..10
loop
dbms_output.put_line(i);
end loop;
end;
事务处理
隔离级别 脏读 不可重复读 虚读
读未提交
Read uncommitted 可以 可以 可以
读已提交
Read committed 不可以 可以 可以
可重复读
Repeatable read 不可以 不可以 可以
可串行化
Serializable 不可以 不可以 不可以

脏读是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
例如：你银行有1000块，你取出500，但没最终提交，你老婆这时通过ATM查帐，你放弃了取款，这时帐户应该还有1000，但是你老婆看到的是500，于是你老婆提交了，结果你损失了500

Ps: 用线程方式理解------读数据不加锁

不可重复读是指当事务两次读取同一行数据，但每次得到的数据都不一样时，就会发生这种事件。
例如：你用网络察看你银行帐户，你老婆这时用ATM察看，这时你取出了500，你老婆准备取1000块发现不够了…..

Ps: 用线程方式理解------读之前加锁，读完放锁

脏读和不可重复读的区别是，一个未提交读取，一个以提交读取

虚读是指一个事物查询两次，另一个事物在这两次之间插入了数据，导致两次查询的结果不同
Ps: 用线程方式理解------读之前加锁，读完不放锁，直到commit或rollback才放锁

串行化-----完全安全的数据访问模式
Ps: 用线程方式理解------读之前加条件锁，读完不放锁，直到commit或rollback才放锁

commit 提交上一个事物，开始下一个事物
savepoint var 保存点
rollback 回滚到事务开始处，或某保存处
存储过程
语法格式
create or replace procedure 过程名(参数 参数设置 参数类型) is
        声明语句段;
    begin
        执行语句段;
    exception
        异常处理语句段;
    end;
参数设置
in参数：读入参数，主程序向过程传递参数值。
out参数：读出参数，过程向主程序传递参数值。
in out 参数：双向参数，过程与主程序双向交流数据。
调用存储过程方法
无返回参数 execute 过程名(参数);
有返回参数的要在另一个过程中调用并赋值

例子:
--赋值语句< := >
create or replace procedure pro4(w in integer,h in integer)
as
width integer :=w;
hight integer :=h;
area real;
begin
area :=(width+hight)*2;
dbms_output.put_line('area=' || area);
end;
/

--条件语句<if .. then ... end if>
create or replace procedure myro5(num in integer) as
n integer :=num;
begin
if n=5 then
dbms_output.put_line('ok');
else
dbms_output.put_line('not equals');
end if;
end;
/

--循环<while ** loop *** end loop; for * in ***loop end loop;>
create or replace procedure mypro6(n in integer) as
con integer :=0;
begin
dbms_output.put_line('while loop.=========');
while con<n loop
dbms_output.put_line(con);
con :=con+1;
end loop;

dbms_output.put_line('for loop ==========');
con :=0;
for con in 1..n loop
for con in n..1 loop
dbms_output.put_line('*');
end loop;
end loop;
end;
/


--使SQLPLUS控制台可以输出
set serveroutput on
set serveroutput off

--控制台输出
declare
begin
dbms_output.put_line('this is ');
end;
/


declare
aaa varchar2(100);
begin
dbms_output.put_line((2+3)*6);
end;
/
异常处理
自定义异常处理

    1. 定义异常处理
    定义异常处理的语法如下：
    declare
        异常名 exception;
  begin
    2. 触发异常处理
    触发异常处理的语法如下：
    raise 异常名;
    3. 处理异常
    触发异常处理后，可以定义异常处理部分，语法如下：
    Exception
    When 异常名1 then
       异常处理语句段1;
    When 异常名2 then
       异常处理语句段2;
  end;
小技巧
raise_application_error(错误代码,错误原因) 函数可以直接抛异常
其中错误代码为-20000到-20999之间，错误原因为2000个以内的字符
触发器
触发器相当于java中的事件监听，当某事件发生时激活特定的事件并执行相应的逻辑
DML触发器中包含了三种事件 insert update delete
语法格式
create [or replace] trigger 触发器名
{before| after | instead of} {insert|delete|update}
on 表名
[for each row]
when 条件
begin
end;
before 在事件开始前执行begin/end
一般应用场合:
1 判断触发事件(一般是一个DML 语句)是否应该被执行
2 在触发事件之前计算一个列的值

after 在事件开始后执行begin/end

一般应用场合:
1 完成触发事件

for each row 表示每操作一次都触发，称作行级，不写表示无论操作多少行，只触发一次，称作表级

when(条件) 必须是行级
小技巧：
触发器中可以使用三个条件词 Inserting,deleting,updating
触发器中可以使用两个变量ld | :new  分别表示旧有的值和新值，必须是行级(在过程中加:)

instead of 替代触发器: 只能作用在视图上，用于替代DML语句 ，行级，不可与when同用

两个任务:
第一题
创建一张表 create table test(aa number primary key);
执行10次 insert into test values(任意整数) ; 语句
查询此表，结果为 1 2 3 4 5 6 7 8 9 10

第二题
创建一触发器保证每周的周六周日，以及每天早八点前和晚六点后不允许对emp表进行任何的DML（insert,delete,update）操作。
第三题
实现emp表中的外键deptid的级联更新功能
游标
游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。
首先我们先看看emp表,select * from scott.emp ;

EMPNO ENAME    JOB        MGR HIREDATE       SAL       COMM     DEPTNO
------ ---------- --------- ---------- ---------- ---------- ---------- ----------
7369   SMITH      CLERK       7902 1980-12-17        800             20
7499   ALLEN      SALESMAN   7698 1981-02-20        1600        300         30
我想查找sal<1000得数据,
select * from scott.emp where sal <1000
返回若干行，返回的若干行事实上是一个临时表。游标的作用是对这个临时表进行逐行处理
游标通常用于在存储过程及客户端开发中
游标流程

声明游标；CURSOR cursor_name IS select_statement

为查询打开游标；OPEN cursor_name

取得结果放入PL/SQL变量中；
              FETCH cursor_name INTO list_of_variables;
              FETCH cursor_name INTO PL/SQL_record;
关闭游标；CLOSE cursor_name
游标的属性
mycursor%isopen  --是否打开
mycursor%found --fetch有数据
mycursor%notfound –fetch没有数据
mycursor%rowcount --返回游标的行数,若返回值为0，没有提取出数据。

--最基本的游标
declare
cursor mycursor is select * from scott.emp where sal > 1000 ;
temp mycursor%rowtype ;
begin
open mycursor ;
fetch mycursor into temp ;
dbms_output.put_line(temp.sal);
close mycursor;
end;
/

--游标的滚动
declare 
num number;
    i emp%rowtype; 
    --定义游标 
    cursor mycursor is 
       select * from emp; 
begin 
    select count(*) into num from emp; 
    if num>0 then 
      open mycursor; --打开游标 
      loop 
         fetch mycursor into i; --读取游标中的值到变量中 
         exit when mycursor%notfound; --没有取到记录时，就退出循环 
         dbms_output.put_line(i.sal); 
      end loop; 
      close mycursor; --关闭游标 
    else 
      dbms_output.put_line('emp=0'); 
    end if; 
end;
日期函数
函数名: to_date(string)
含义: 字符串转日期
示例: select to_date('2002-1-1') from dual;
结果: 2002-1-1

函数名: add_months(date,month)
含义: 增加或减小月份
示例: select add_months(sysdate,1) from dual ;
结果: xxxx-xx-xx

函数名:sysdate
含义:当前日期，可直接加减天数
示例:select sysdate+1 from dual
结果:当前日期+1天

函数名: extract(year|month|day from dateType)
含义:截取日期
示例: select extract(year from sysdate)"This year" from dual;
结果: 2007
问题：获取几个月以后的年份

函数名: last_day(dateType)
含义: 最后一天
示例: select last_day(sysdate) from dual ;
结果: xxxx-xx-xx

函数名: months_between(dateType,dateType)
含义: 返回两个日期间的月份
示例: select months_between(‘2002-1-1’,’2003-1-1’) from dual
结果: -12

函数名: to_char(dateType,yyyy|dd|mm|hh{12|24}|mi|ss|day|ww|dy)
含义: 把日期转换为字符串
示例: select to_char(sysdate,’yyyy-mm-dd-hh24:mi:ss’) from dual;
结果: xxxxxxxx
问题: 两个日期间共有几星期

字符串函数
函数名: ASCII(char)
含义: 返回与指定的字符对应的十进制数
示例: select ascii('孟') from dual;
结果: 50127

函数名: CHR(int)
含义: 给出整数,返回对应的字符
示例: select chr(50127) from dual;
结果: 孟

函数名: CONCAT(string,string)
含义: 连接两个字符串
示例: select concat('我是','孟庆晨') from dual;
结果: 我是孟庆晨

函数名: INITCAP(string)
含义: 返回字符串并将字符串的第一个字母变为大写
示例: select INITCAP('aaa') from dual;
结果: Aaa

函数名: INSTR(被搜索字符串,搜索的字符串,搜索的开始位置默认为1,第几次出现默认为1)
含义: 在一个字符串中搜索指定的字符,返回发现指定的字符的位置
示例: select INSTR('我是孟庆晨','孟',1,1) from dual;
结果: 3

函数名: LENGTH(string)
含义: 返回字符串的长度
示例: select length(‘孟庆晨’) from dual ;
结果: 3

函数名: LOWER(string)
含义: 小写形式
示例: select lower(‘AAA’) from dual
结果: aaa

函数名: UPPER
含义: 大写形式
示例: select upper(‘aaa’) from dual ;
结果: AAA

函数名: RPAD & LPAD
含义: 黏贴字符串到目标字符串左右
示例: select lpad(‘a’,10,’*’) from dual ;
结果: *********a

函数名: ltrim & rtrim
含义: 删除左边（右边）的字符串
示例: select ltrim(‘abc’,’a’) from dual ;
结果: bc

函数名: substr
含义: 取字符串(原字符串,第几个开始,取几个)
示例: select substring(‘abcdefg’,3,4) from dual
结果: cdef

函数名: REPLACE(string,string,string)
含义: 替换字符串
示例: select replace(‘abcde’ , ‘abc’ , ‘aaa’ ) from dual ;
结果: aaade

数学函数
函数名: abs
含义: 返回指定值的绝对值
示例: select abs(-10) from dual
结果: 10

自定义函数:

--函数function
create or replace function prices
(
proprice in number
)
return integer
is
pricecount integer;
begin
select count(price) into pricecount from product where price>proprice;
if(pricecount >0) then
return pricecount;
else
return 0;
end if;
end prices;

declare
counter number := 0;
begin
counter := prices(30);
if counter >0 then
dbms_output.put_line('counts:'||counter );
else
dbms_output.put_line('counts:'||counter );
end if;
end;
/
常见问题
1 不能启动监听
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleOraHome92TNSListener
添加字符串项ImagePath,值为c:\oracle\ora90\bin\TNSLSNR
2 启动时,监听器不启动或打开出错
错误现象：
　　Oracle启动时，监听器不启动或打开出错；服务器端：用username/password登录正常，但用username/password@alias登录不成功；客户端：用username/password@alias登录不成功
　　解决方法
　　（1）如果是因为修改了NT的机器名，则把listener.ora文件中的host参数全部改为新的NT机器名，重新启动OracleTNSListener80服务即可。
　　
　　例如:
　　
　　LISTENER =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(Host = NT_Name)(Port = 1521))
　　(ADDRESS=(PROTOCOL=TCP)(Host=NT_Name)(Port= 1526))
　　)

3 运行oracle后tomcat运行出错
原因：端口冲突
解决方法
修改tomcat的conf.xml文件，查找8080，修改为其他

4 本地日期问题
找到注册表MACHINE/SOFTWARE/ORACLE/HOME0/
添加字符串 NLS_DATE_FORMAT  值为yyyy-mm-dd