Oracle使用

一、简单sql

t_student

t_teacher

查询没有安排老师教的学生: select * from t_student std where not exists (select tea.* from t_teacher tea where tea.std_id = std.id)

查询同一个学生有多少个任课老师:

select std_id,LTRIM(MAX(SYS_CONNECT_BY_PATH(name,  ',' )),  ',' ) name
from (
select name ,std_id,(ROW_NUMBER() OVER(PARTITION BY std_id ORDER BY name))  NUMID
from t_teacher 
)
start with NUMID = 1 connect by NUMID -1 = prior NUMID
group by std_id

查询第一条数据：select * from t_student where rownum = 1 （sqlserver:select top 1 列名 from 表名 mysql:select * from 表名 limit 1)

创建索引：CREATE INDEX t_student_index1 ON t_student (name)（用来加速搜索/查询，缺点是更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新）

--创建序列
create sequence seq_newsId
increment by 1
start with 1
maxvalue 999999999;
--查询序列
select seq_newsid.nextval from sys.dual;
--删除序列
DROP SEQUENCE seq_newsId；

--创建视图

CREATE  OR  REPLACE  VIEW  stu_tea_vw 
AS 
select name,age,password,cd.teachernames
from t_student std
left join (
select std_id,LTRIM(MAX(SYS_CONNECT_BY_PATH(name,  ',' )),  ',' ) teachernames
from (
select name ,std_id,(ROW_NUMBER() OVER(PARTITION BY std_id ORDER BY name))  NUMID
from t_teacher 
)
start with NUMID = 1 connect by NUMID -1 = prior NUMID
group by std_id) cd
on std.id = cd.std_id;

--查询用户
select * from dba_roles;
--创建用户 
create user test identified by test;
--删除用户
drop user test;
--授权
grant all on STU_TEA_VW to test;
grant select on STU_TEA_VW TO TEST;
--同义词
create synonym STU_TEA_VW for test.STU_TEA_VW
drop synonym STU_TEA_VW

--创建触发器
CREATE OR REPLACE TRIGGER tri_teacher  
  BEFORE INSERT OR UPDATE ON T_TEACHER
  FOR EACH ROW   
  DECLARE V_AUDIT_CODE VARCHAR2(1);
  V_ENTITY_NAME VARCHAR2(35) := 'T_TEACHER';
DECLARE   
BEGIN   
  IF INSERTING THEN V_AUDIT_CODE := 'I'; 
  INSERT INTO NBIS_AUDIT_LOG (ID, AUDIT_CODE,ENTITY_NAME,BEFORE_VALUE, AFTER_VALUE)
    VALUES(AUDIT_LOG_SEQUENCE.NEXTVAL,V_AUDIT_CODE,V_ENTITY_NAME,NULL,:NEW.NAME)
  END IF;
  IF UPDATING THEN V_AUDIT_CODE := 'U'; 
  INSERT INTO NBIS_AUDIT_LOG (ID, AUDIT_CODE,ENTITY_NAME,BEFORE_VALUE, AFTER_VALUE)
    VALUES(AUDIT_LOG_SEQUENCE.NEXTVAL,V_AUDIT_CODE,V_ENTITY_NAME,:OLD.NAME,:NEW.NAME)
  END IF;
END tri_teacher;  
--删除触发器
DROP TRIGGER tri_teacher;

create or replace
procedure caluteTeachersNo(isal in t_teacher.std_id%type, 
                            sname out number,sjob in out varchar)
   as icount number;
   begin
        select count(*) into icount from t_teacher where std_id = isal;
        
        sname := icount;
        insert into t_student values(1000,'XXX','20','XXXX');
       
  exception
       when too_many_rows then
       DBMS_OUTPUT.PUT_LINE('返回值多于1行');
       when others then
       DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
  end caluteTeachersNo;
  
  declare
         realsal t_teacher.std_id%type;
         realname number;
         realjob varchar(40);
   begin   --存储过程调用开始
         realsal:='18';
         realname:=0;
         realjob:='CLERK';
         caluteTeachersNo(realsal,realname,realjob);     --必须按顺序
        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
  END;  --过程调用结束

--自定义类型
create type mytype as object(id int ,name varchar(20));
create table test(id int ,person mytype);
insert into test values(1,mytype(1,'23sdf'));

二、sql优化

1.任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2.对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的
3.任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
4.SELECT子句中避免使用 ‘ * ‘
5.on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表 后，再由where进行过滤，然后再计算，计算完后再由having进行过滤
6.用EXISTS替代IN、用NOT EXISTS替代NOT IN：
7.使用索引同样能提高效率,需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O 
8.sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行 
9.避免在索引列上使用计算． WHERE SAL * 12 > 25000; WHERE SAL > 25000/12; （高效）
10.用>=替代> (>=4 比>3 更高效)
11.用UNION替换OR (适用于索引列) 如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
12.用IN来替换OR 
13.避免在索引列上使用IS NULL和IS NOT NULL (>=0; 比 IS NOT NULL更高效）
14.如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引