2018-1-8

1、分页查询   rownum ——伪列

记录了表中每一条记录的行号，该行号指的是记录的插入先后顺序

在取结果集前若干条记录时，针对rownum加条件，需要将rownum写在结果集的外层

     注意：rownum这一列比较特殊，它只能支持<和<=，而不能支持>和>=，所以当我们需要使用rownum进行分页查询的时候，需要对它进行身份的降低，给rownum起别名，将它由一个特殊的列降低为普通的列，最后再在外层使用该普通的列来进行条件判断

  例：select * from (

select a.*,rownum rn from(

              select * from student t

              order by t.birthday desc) a) c   /*此处的a、c为别名*/

       where c.rn >3 and c.rn<=6

     在实际开发中，从前端页面传入到后端数据库的分页查询的参数作为页数以及每页多少条，我们需要自己编写公式将其转换为开始条数和结束条数。

   写法：select * from (

select a.*,rownum rn from(

                 select * from 表名 t

                 order by t.排序字段 desc) a) c

        where c.rn >（pageNo-1）*pageSize

        and c.rn<=pageNo*pageSize ;

 其中：——页数：pageNo

       ——每页多少条：pageSize

 

2、序列

定义：一种用来提供唯一数值的数据库对象

   往往用于给表的主键赋值

   使用 序列名.nextval  可以访问该序列的下一个值

   往往在insert into语句中使用序列名.nextval来为记录插入主键值

   可以使用user_sequences视图中来查看当前数据库中的视图信息

   删除序列：drop sequence 序列名

   在实际开发中，往往会通过图形化的界面直接创建和删除序列，而不需要编写语句

  例：1、create sequence xu

 increment by 1   /*每次增长1*/

         start with 10    /*从10开始增长*/              创建一个序列

         maxvalue 9999999999    /*提供的最大值*/

 nocycle    /*不需要循环*/

 nocache    /*不需要缓存登陆*/  

select xu.nextval from dual                     访问该序列下一个值

 

2、insert into tempteacher values (xu.nextval,'张三'，30，'北京')

      

3、select sequence_name,min_value,max_value,increment_by,last_number

from 

user_sequences

4、drop sequence xu

3、索引

   索引是加在数据库表中某个字段上的一种数据库对象，它可以提高该表的查询效率

   创建方式：1、自动创建：当某个字段被设置为主键或者添加唯一性约束时，系统会自动为其添加索引

2、 手动创建：create index 索引名 on 表名（字段）

例：create index no_index on tempteacher(tno)

   缺点：影响数据库中数据的增删改的效率，因为在增删改数据的时候，需要花费额外的资源来维护索引

 

（什么时候需要加索引，什么时候不需要？面试）

1、表中的记录特别少，不需要加；表中的记录很多很多（上万），则考虑加索引

2、该字段的取值范围很窄，不需要加；该字段的取值范围很宽，则需要加

3、该表经常性的被查询，而不怎么增删改，则需要加索引；该表经常性的被增删改，而不怎么被查询，则不需要加索引

4、该表经常性的查询范围占总表的百分比特别高或特别低，则不需要加索引，否则需要加索引

 

删除索引：drop index 索引名

例：drop index no_index

 

4、同义词：

   使用同义词可以将数据库对象的别名保存在数据库中

   格式：create synonym 同义词名 for 数据库对象

   例：create synonym t for teacher

   删除同义词：drop synonym 同义词名

5、PL/SQL的结构

PL/SQL由三部分组成：声明、执行、异常处理

格式：

     declare

        声明部分的代码

     begin

        执行部分的代码

     exception  

        异常处理的代码

      end

6、定义变量的格式：

   在declare代码块中使用：变量名  变量类型

     select into语句格式：select 字段 into 变量 from 表

   例：declare                     

           v_sal varchar2(8);     /*定义一个变量*/

begin

           select t.sal into v_sal from teacher t

           where t.tno=16;

           dbms_output.put_line(v_sal);

end;

7、在定义变量的时候可以使用%type来指定具体某个变量的类型

   格式：变量名 表名.字段%type

   例：declare 

v_date student.birthday%type；

8、在PL/SQL中，可以自定义一个记录类型，该类型中可以包含指定的若干字段。利用该类型声明的变量可以同时接收这些字段的值。

格式：type  记录类型名  is recode（

              字段1    字段类型1，

              字段2    字段类型2，

              ·······

 

）；

变量名 记录类型名

例：declare

   type stu_recode is record(

     v_sname student.sname%type,

     v_date  student.birthday%type,

     v_dno   student.dno%type);

    v_stu stu_recode;

begin

   select s.sname,s.birthday,s.dno

   into v_stu

   from student s

   where s.sno='12';

dbms_output.put_line(v_stu.v_sname||','||v_stu.v_date||','||v_stu.v_dno);

end;

9、可以手动给变量赋值

   格式：变量名：=值

   例：v_sno:='12'

       v_sname:='这是一个好学生'

10、可以使用%rowtype来把变量定义成一个包含表中所有字段的类型

格式：变量名 表名%rowtype

11、在PL/SQL程序中，可以使用update语句来修改表中的记录

   例：declare

v_tno tempteacher.tno%type;

begin

          v_tno :=16;

          update tempteacher e set e.tage=e.tage+1

          where e.tno=v_tno;

         dbms_output.put_line('执行成功')；

end;

12、分支语句：

1、if 条件 then 结果

end if；

2、if 条件 then 结果1

else 结果2

end if；

3、if 条件1 then 结果1

   elsif 条件2 then 结果2

   ······

   else 结果n

   end if;

13、在PL/SQL中可以使用case语句来做分支判断

格式：

    case 变量

    when 值1 then 结果1

    when 值2 then 结果2

    ·····

    else 结果n

    end;

    例：declare

  v_sal tempteacher.sal%type;

  v_temp varchar2(100);

begin

  select sal into v_sal from tempteacher

  where tno='16'

  v_temp :=case trunc((v_sal/5000),0)

            when 0 then 'sal<5000'

            when 1 then '5000<sal<10000'

            else 'sal>=10000'

            end;

  dbms_output.put_line(v_temp);

end;

14、循环语句

     循环四要素：1、初始化表达式

2、布尔值测试表达式

3、循环体

4、更改表达式

一、loop循环

   格式：

     初始化表达式

     loop

        循环体

        更改表达式

     exit when 布尔值测试表达式

         end loop;

       例：/*从1输出到100*/

declare

  v_temp number(8);

begin

  v_temp:=1;

  loop

     dbms_output.put_line(v_temp);

     v_temp := v_temp +1;

     exit when v_temp >100;

  end loop;

end;

二、while 循环

格式：

    初始化表达式

    while 布尔值测试表达式 loop

    循环体

    更改表达式

    end loop

例：/*从1输出到100*/

   declare 

v_i number(20) :=1;

begin

       while v_i <= 100 loop

       dbms_output.put_line(v_i);

       v_i :=v_i+1;

       end loop;

end;

三、for循环

       格式：

         for 变量 初始值 .. 结束值 loop

         循环体

         end loop

       例：/*从1输出到100*/

declare

               v_temp number(8);

begin

               for v_temp in 1..100 loop

               dbms_output.put_line(v_temp);

               end loop;

end;

14、goto语句

     当程序执行到goto语句时，将会无条件跳转到打了标记的地方。

     标记使用<<标记名>>来定义。

     格式：

      goto 标记名；

     例：/*从1输出到100，到50时跳转出来*/

declare

   v_temp number(8);

begin

  for v_temp in 1..100 loop

     dbms_output.put_line(v_temp);

     if v_temp=50 then

        goto flag;

     end if;

  end loop;

  <<flag>>

  dbms_output.put_line('打印结束');

end;

15、游标

游标用于记录多条结果

格式：

定义游标：cursor 游标名 is select语句；

            打开游标：open 游标名

            提取游标：fetch 游标名 into 变量

            关闭游标：close 游标名

    例：declare

          v_tno teacher.tno%type;

          v_sal teacher.sal%type;

          cursor teacher_cursor is

          select e.tno,e.sal from teacher e;

begin

          open teacher_cursor;

          fetch teacher_cursor

          into v_tno,v_sal;

          while teacher_cursor%found loop

             dbms_output.put_line('tno:'||v_tno||',salary:'||v_sal);

          fetch teacher_cursor

          into v_tno,v_sal;

          end loop;

          close teacher_cursor;

end;