程序员常见面试之数据库知识点小结（二）

最新推荐文章于 2025-02-05 14:34:17 发布

学习编程知识

最新推荐文章于 2025-02-05 14:34:17 发布

阅读量2.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： java 数据库文章标签：数据库 java 面试题程序员 sql

本文链接：https://blog.youkuaiyun.com/never_cxb/article/details/50396910

java 同时被 2 个专栏收录

90 篇文章

订阅专栏

数据库

6 篇文章

订阅专栏

本文详细介绍了数据库中的聚簇索引与非聚簇索引的区别，包括它们的定义、建立方法及其特点。此外，还深入探讨了触发器的概念、作用及应用场景，并提供了具体的实现代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

笔者悉心整理的数据库面试知识点，希望对各位朋友有所帮助。
文章链接 http://blog.youkuaiyun.com/never_cxb/article/details/50420521 ，转载请保留出处。

聚簇索引与非聚簇索引的区别

聚簇索引的顺序就是数据的物理存储顺序
每个表中只能有一个聚集索引
聚集索引要求：“既不能绝大多数都相同，又不能只有极少数相同”的规则。
非聚簇索引的解释是:索引顺序与数据物理排列顺序无关
一个表最多只能有一个聚簇索引。

建立索引代码

-----------------------------------------------------------
---- Create T_Pet table in tempdb. 
-----------------------------------------------------------
USE tempdb

CREATE TABLE T_Pet
(
    animal    VARCHAR(20),
    [name]    VARCHAR(20),
    sex        CHAR(1),
    age        INT
)

-----------------------------------------------------------
---- Create Unique Clustered Index
-----------------------------------------------------------
CREATE UNIQUE  CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)

非聚集索引代码

-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX. 
-----------------------------------------------------------
USE tempdb

CREATE TABLE T_Pet
(
    animal    VARCHAR(20),
    [name]    VARCHAR(20),
    sex        CHAR(1),
    age        INT
)

-----------------------------------------------------------
---- Create Unique NonClustered Index
-----------------------------------------------------------
CREATE UNIQUE  NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)

NonClustered 和 Clustered 就是聚集和非聚集的区别

稠密和非稠密的区别

稠密就是一条搜索码值都有一个索引，
非稠密索引中，只为搜索码的某些值建立索引项。非稠密是一个索引对应一些记录的范围

Note
辅助索引必须是稠密索引

游标

比如 select * from table_a 可以得到结果集，游标用来定位结果集的行，可以对结果集修改等等。
@@FETCH_STATUS 全局变量可以判断游标是不是到了最后，该变量为非0表示到了最后或者出错

代码

Set serveroutput on;

declare 
    ---define Cursor
    Cursor cur_policy is
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account
     from t_contract_master cm
     where cm.liability_state = 2
     and cm.policy_type = 1
     and cm.policy_cate in ('2','3','4')
     and rownum < 5
     order by cm.policy_code desc;
    curPolicyInfo cur_policy%rowtype;---定义游标变量
Begin
   open cur_policy; ---open cursor
   Loop 
     --deal with extraction data from DB
     Fetch cur_policy into curPolicyInfo;
     Exit when cur_policy%notfound;

     Dbms_Output.put_line(curPolicyInfo.policy_code);
   end loop;
   Exception 
     when others then
         close cur_policy;
         Dbms_Output.put_line(Sqlerrm);

   if cur_policy%isopen then  
    --close cursor 
      close cur_policy;
   end if;
end;

触发器

触发器是一个特殊的存储过程。 ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等

触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。
语句触发器是对每一个语句触发一次
行触发器是对语句受影响的每一行触发一次
行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。
比如插入500行的 insert 命令，语句触发器只触发一次，而行触发器出发了500次
行触发器有 for each row子句。语句触发器没有for each row 子句

代码

BEFORE DELETE 这儿的 before 可以换成 after
for each row 可以有或者省略
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON scott.emp
   FOR EACH ROW   --说明创建的是行级触发器
BEGIN
   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

    Oracle 中默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');
 END IF;
END;

其他示例

我为什么要使用触发器？比如，这么两个表：

      Create Table Student(              --学生表 
        StudentID int primary key,       --学号 
        .... 
       ) 

      Create Table BorrowRecord(               --学生借书记录表 
        BorrowRecord   int identity(1,1),       --流水号   
        StudentID      int ,                    --学号 
        BorrowDate     datetime,                --借出时间 
        ReturnDAte     Datetime,                --归还时间 
        ... 
      )

 用到的功能有: 
    1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 
    2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 
 等等。 

 这时候可以用到触发器。对于1，创建一个Update触发器：

     Create Trigger truStudent 
       On Student                         --在Student表中创建触发器 
       for Update                          --为什么事件触发 
     As                                        --事件触发后所要做的事情 
       if Update(StudentID)            
       begin 

         Update BorrowRecord 
           Set StudentID=i.StudentID 
           From BorrowRecord br , Deleted   d ,Inserted i      --Deleted和Inserted临时表 
           Where br.StudentID=d.StudentID 

       end

SQLServer 理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：

Tables	虚拟表Inserted	虚拟表Deleted
在表记录新增时	存放新增的记录	存放新增的记录
修改时	存放用来更新的新记录	存放更新前的记录
删除时	不存储记录	存放被删除的记录