04oracle学习笔记

一、查询：

--内连接：两边数据能对应起来的，对应不出来的查不到

 

select * from t_employee e inner join t_dept d on e.depton=d.depton;

select * from t_employee e,t_dept d where e.depton=d.depton;

--外连接：分为

--左外连接 左边的是主表（主表的信息全部展示），右边的从表（从表的信息如果跟主表对应不起来显示为null）

select * from t_employee e left join t_dept d on e.depton=d.depton;

select * from t_employee e , t_dept d where e.depton=d.depton(+);

SELECT
  a.ship_voyage_id
FROM
  ssp_ship_voyage a
  LEFT JOIN cod_ship_data b
    ON a.ship_data_id = b.ship_data_id
WHERE b.c_ship_nam = #{cShipNam} and a.eta= #{eta};

--右外连接

select * from t_employee e right join t_dept d on e.depton=d.depton;

select * from t_employee e , t_dept d where e.depton（+）=d.depton;

--全连接

select * from t_employee e full join t_dept d on e.depton=d.depton;

 

总结：sql查询的基本原理：两种情况介绍。
第一、   单表查询：根据where条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据select的选择列选择相应的列进行返回最终结果。

第二、   两表连接查询：对两表求积（笛卡尔积）并用on条件和连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。

第三、   多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
理解sql查询的过程是进行sql优化的理论依据。

第四、on后面的条件（on条件）和where条件的区别：

on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

on条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
where条件：在有on条件的select语句中是过滤中间表的约束条件。在没有on的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将where条件移入on后面是不恰当的。推荐的做法是：
on只进行连接操作，where只过滤中间表的记录。

 

二视图：

view，是一种有结构（有行有列），但没有结果（结构中不真实存放数据）的虚拟表，虚拟表的结构来源不是自己定义的，而是从对应的基表（视图的数据来源）中产生的。

1、创建视图的语法：

create or replace view my_view

as

select e.*,d.deptname from t_emploee e,t_dept d where  e.depton=d.deptno;

2、视图的作用：

 

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；

视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

INSERT INTO v_emp_10  VALUES(1234, 'DOCTOR', 4000, 10);

工作中实践了一遍，视图---虚拟的表只能查询，不能修改

CREATE VIEW ship_bill_voyage AS
SELECT 
  ship_bill_id,
  ship_bill_no,
  ship_bill.ship_voyage_id,
  c_ship_nam,
  i_voyage,
  ship_mmsi,
  eta,
  etd,
  rta,
  rtd,
  TRADE_ID,
  trade_id,
  EXPORT_OR_IMPORT,
  NATIONALITY,
  TRAVEL_PORT_NAME,
  ARRIVAL_PORT_NAME,
  ship_bill_type,
  shipper,
  consignee,
  descrtiption,creator,created,changer,CHANGED 
FROM
  ship_bill,
  ssp_ship_voyage,
  cod_ship_data 
WHERE ship_bill.ship_voyage_id = ssp_ship_voyage.ship_voyage_id 
  AND cod_ship_data.ship_data_id = ssp_ship_voyage.ship_data_id ;

`ship_bill_voyage`

 

三、索引：

 

索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。

索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。

创建索引的语法：

 

create unique index index_name on table (列名)；

 

create index index_emp_name on emp(ename);

四、游标：

游标可以理解为是一个select的结果集合
作用主要是在存储过程里面，用来对取得的结果集进行遍历

 

declare
cursor my_cur is select * from m_user;
--定义一个游标，名字叫my_cur,存储t_staff；
v_row m_user%rowtype;--定义为行类型
begin
--把游标遍历出来
--打开游标
open my_cur;
--2循环游标获取值
loop
--3取出每一行数据
fetch my_cur into v_row;
--4添加退出条件，避免死循环
exit when my_cur%notfound;
--5取出信息
dbms_output.put_line(v_row.psw||'=='||v_row.sex);
end loop;
--6关闭游标
close my_cur;
end;

declare
cursor my_cur is select * from m_user;
--定义一个游标，名字叫my_cur,存储t_staff；
v_row m_user%rowtype;--定义为行类型
begin
--把游标遍历出
for v_row in my_cur
loop
dbms_output.put_line(v_row.psw||'=='||v_row.sex);
end loop;
end;

五、触发器：

 

触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle系统自动触发。

 

create or replace
trigger my_tri
after insert or update or delete on t_staff
--for each row
begin


  if inserting then
     insert into t_log values('插入语句',sysdate);
  elsif updating then
     insert into t_log values('修改语句',sysdate);
  elsif deleting then
     insert into t_log values('删除语句',sysdate);
  end if;
  
  
  --记录新值和旧值
--  dbms_output.put_line('旧salary='||:old.stasalary);
--  dbms_output.put_line('新salary='||:new.stasalary);
 
end;