oracle笔记 <只是基本知识。。。>

Oracle配置
打开服务 启动oracle toys
在dos命令行中输入isqlplusctl start
输入相应的网站启动oracle plus
用户名user1
密码user1

oracle的物理组件：1.数据文件 2.控制文件 3.日志文件
oracle的逻辑组件：1.数据库 2.表空间 3.段 4.区 5.数据块

Sql类别的命令
DDL 数据定义语言
DML 数据操纵语言
TCL 事务控制语言
DCL 数据控制语言

数据类型： 字符（char，varchar，long），数值（number[(p[,s])],p表示的是精度，s表示的是小数点的位数），日期时间（Data，Timestamp),LOB（是大对象用于放置音频，视频一类）
to_date('03-15-1989','MM-DD-YYYY');
日期类型的值
日期数据类型的默认格式为"day-month-year"
使用to_date函数转换

DDL：
create table
alter table
truncate table 删除所有行，比不加where的delete更快速。
drop table

DML
select
insert
update
delete

TCL
事务是最小的工作单元，作为一个整体进行工作
保证事务的整体成功或失败，称为事务控制
savepoint 标记事务中可以回滚的点
rollback撤销事务中的已完成的工作
commit提交并结束事务处理

DCL
数据控制语言为用户提供权限控制命令
grant 授予权限
revoke撤销已授予的权限

distinct 选择无重复的行。

子查询：
1.单列单执行 可以用比较运算符
2.单列多行  可以用in
3.多行多列  用exists

外层查询的关键字段必须要跟子查询的关键字段相匹配才行。
select输出字段
from数据源
where筛选条件
order by [asc/des]排序（asc升序，des降序）默认asc
group by分组字段
having 分组筛选

两个表连接
alter table employees
add constraint fkdepId foreign key(deptId)references departments(depId);

join 自然连接 自动找两个表中相同的匹配项。
cross join交叉连接

配置path JDK的JRE 要在ORCALE 前面

主要的4张表
select *from scott.emp; (方案)
select *from scott.dept;
select *from scott.salgrade;
select *from scott.bonus;

alter user scott account unlock;解锁
在开发环境中 尽量避免*
comm有0有null的时候 用：nvl(comm,0)
连接字符串||
select ename||'is a'||job from emp;

like模糊查询
以j开头的‘J% ’表示通配符
_下划线表示单个字符占位
顺序从后往前的查询
having主要作为分组函数的筛选条件
主要：在选择列中如果有列表达式 和分组函数 那么在这些列和表达式必须出现在group by字句中，
replace(ename,'A','无锡')
0和null的区别
initcap(ename)首字母大写，其他小写
select *from emp for update;(锁表) 权限增删改。
trunc()截取字段，括号里面的数字可以理解为截取小数点的位数
mod()取模 dual虚拟表
floor向下取整
ceil向上取整
length()长度，长度抓取
substr(ename,1,1)截取字符串
replace()代替
round()四舍五入
trunc()截取字段，小数点后面的位数
to_date()
select deptno,count(*)from emp where  hiredate>to_date('1981-01-01','yyyy-mm-dd')group by deptno;
返回指定日期加行指定月数后的日期值
select*from emp where sysdate>add_months(hiredate,8) 显示8个月以前入职的员工
last_day(d) 返回指定日期当月的最后一天的日期值
to_char() 转换函数
例：select deptno,count(*) from emp where to_char(hiredate,'yyyy-mm-dd')>'1981-01-01' group by deptno;
多表查询尽量用别名
----显示各个员工的姓名，工资，及其工资的级别、
select e.ename,e.sal,s.grade from emp e ,salgrade s
 where e.sal between s.losal and s.hisal;
---外连接 ！！！！重点！！！！ 燃烧了---------
----1。左边外 2。右边外 3.完全----
----1.右连接 说明等号右侧所有记录均会被显示，无论其在左侧是否得到匹配
----2、左连接与之相反
（+）在右是左连接，（+）在左是右连接
另：left outer join/right outer join/full outer join
using用于相同的列。
on用于不同的列。
-------------------------------------------
三张表或以上的连接
select ename,dname from emp a1 join dept a2 using

-------------------------------------------
分页技术：
 ps=cn.prepareStatement("select id,stuname,stuage,stugender,stuemail from(select rownum as num,s.*from students s) a  where a.num between "+((pageNow-1)*pageSize+1)+" and "+pageSize*pageNow+"");
-----------------------------------------------------
pl/sql编程
sql：
delcare
 v_name varchar2(10);
 v_num number(5,2);
c_name constant varchar2(10):='niit';
c_num constant number(4,2):=2.54;
 begin
v_name:='haha';
dbms_output.put_line(v_name);
end;
命令行
 set serveroutput on;
 复制之前在sql的代码 不要忘记在end;后面加上/
要在sql里面更改代码，然后复制到命令窗口里，不能在sql里更改代码
&on 或者&字段 表示的是从控制台输入
------------------------------------------------
ref游标
select执行一些动态的sql语句，动态sql语句 pl/sql块编译时sql是不确定的根据用户输入的参数不同而执行不同的操作
声明类型：
type <ref_cursor_name> is ref  cursor return <return type>
开打游标:
open ref_cursor_name for select statement
 强类型ref游标 带return
 弱类型ref游标 不带return
例：(强类型)
declare
type emp_cursor is ref cursor return emp%rowtype;
v_empobj emp_cursor;
v_empRecorder emp_cursor%rowtype;
begin
open v_empobj for select *from emp;
loop
fetch v_empobj into v_empRecorder
exit when v_empobj%notfound;
dbms_output.put_line(v_empRecorder.ename);
end loop;
close emp_cursor;
end;
例：(弱类型)
declare
type ref_cursor_type  is ref cursor;
ref_cursor ref_cursor_type;
v1 number(7,2);
v2 varchar(20);

begin
open ref_cursor for select empno,ename from emp;
loop
fetch ref_cursor into v1,v2;-- 注意顺序
exit when ref_cursor%notfound;
dbms_output.put_line(v1||v2);
end loop;
close ref_cursor;
end;----------------for循环不能用于ref游标

-----------更新游标 锁表
declare
cursor emp_cursor is select ename,sal.empno from emp for update;(锁表)
dno int:=&num;
v_empRecorder emp%type;
begin
for v_empRecorder in emp_cursor loop
if  dno=v_empRecorder.empno then
update emp set sal=sal*1.1 where current of emp_cursor;
end if;
endloop;
end;
-------------------------------------
编写完存储过程之后编译之后需要在运行中传递参数进去 就好像存储过程是一个带参数的函数，需要传递在存储过程中定义的相匹配的参数。
create or replace procedure sp_pro2(spNum emp.empno%type,spName out emp.ename%type)
is
begin
select ename into spName from emp where empno=spNum;
end;
运行  out参数 在运行的时候需要声明一个变量去接收他
declare
v_name varchar2(30);
begin
sp_pro2(7369,v_name);
dbms_output.put_line(v_name);
end; 
--------------------------------------------------
in 参数是传递进去的参数，out是打印出来的参数；
-----------------------------------------------------------
自定义函数与存储过程最大的不同就是有返回值，函数运行需要定义参数去return的值，（默认是in 参数）
----------------------------------------------------
                    包与触发器
-------------------------------------------------------
包一般是过程和函数的集合，对过程和函数进行更好的封装。
一般不针对字段，包的构成有两部分1.包头：仅仅对包中的方法进行说明，而没有实现，2.包体：实现的方法在包体中。
包头和包体的名称要保持一致！
create or replace package mypackage is
function fun_a return varchar2;
end;

create or replace package body mypackage is
function fun_a return varchar2
is
type emp_ref is ref cursor;
emp_cur emp_ref;
v_emp emp%rowtype;
begin
  open emp_cur for select *from emp;
  loop
    fetch emp_cur into v_emp;
    exit when emp_cur%notfound;
    dbms_output.put_line(v_emp.ename);
    end loop;
    close emp_cur;
    return '';
  end;
  end;

declare
v_ename varchar2(30);
begin
  v_ename:=mypackage.fun_a();
  dbms_output.put_line(v_ename);
  end;
-----------------------------------------------------------
                          触发器
--------------------------------------------------------------
触发器由三部分组成：
1.触发器语句(事件)
2.触发器的限制条件
3.触发器的操作

create or replace trigger tril after update of sal on emp for each row
begin
  if:new.sal<=:old.sal then
  raise_application_error(-20001,'修改后的薪水大于修改前的');
  elsif :new.sal>:old.sal then
  raise_application_error(-20002,'工资增量不能超过原工资的10%');
  end;
 --------------------------
数据库的设计-- 表 行与列的组合 对象（游标 过程 函数 触发器）
1.实体表要由主键（必须有的）或者复合主键，没有主键的表叫关联表，但是要有外键。注意表与表之间的关系有三种：1.一对一 2.一对多 3.多对多。
建表。消除数据的冗余。
第一范式，第二范式，第三范式。
第一范式：删除所有重复的组 确定一个主键或复合主键。
第二范式：确定表处于第一范式 消除任何部分依赖性 
第三范式：确定第一第二范式，消除任何可传递依赖性。
truncate 清空表 保留表的结构。不用提交，删除的数据不能恢复！慎用。
------------------------------------
表与表之间的关联，外键
表级定义 主外键约束
create table t2(id number,t1_id number(2),name varchar2(20))
constraint pk_t2 primary key(id),constraint fk_t1 foreign key (t1_id)references t1(id));
cteate table t1(id number(2) primary key,name varchar2(20))
-------------------
索引提高查询效率
前提 大表才有意义，就是比较庞大的数据量。
在where字段 或者连接条件上经常引用的列上建立索引。
索引不是越多越好，因为要占有空间，特别是在更新数据的时候