数据库基础Database3-高级SQL

数据库基础Database3

四 高级SQL

4.1 使用程序设计语言访问数据库

JDBC

4.2 函数和过程 （FUNCTION AND PROCEDURE)

和一般程序设计语言一样，SQL也允许自定义函数。

注意：每个数据库的函数和过程声明和调用有可能不一样，请参考具体的数据库。

函数

CREATE FUNCTION a_test_func(name1 TYPE1, name2 TYPE2 ...)
    RETURNS TYPE
    BEGIN
    DECLARE ret TYPE;   # declare return variable
        DECLARE var1 TYPEi; # some some variable declarations
        # SOME OPERATIONS
    RETURN ret;
    END

例子：

CREATE FUNCTION dept_count_func(dept_name VARCHAR(20))
    RETURNS INTEGER
    BEGIN
    DECLARE d_count INTEGER;
        SELECT COUNT(*) INTO d_count
        FROM instructor
        WHERE instructor.dept_name = dept_name;
    RETURN d_count;
    END

注意：要注意对返回变量的声明和赋值。DECLARE, SELECT INTO

过程

CREATE PROCEDURE a_test_proc(IN name1 TYPE1, ..., OUT name2 TYPE2, ...)
    BEGIN
        // SOME OPERATIONS
    END

IN表示传进来的参数，OUT表示返回的参数。包含OUT的叫作存储过程（STORED PROCEDURE)。

例子：

CREATE PROCEDURE dept_count_proc(IN dept_name VARCHAR(20), OUT d_count INTEGER)
    BEGIN
        SELECT COUNT(*) INTO d_count
        FROM instuctor
        WHERE instructor.dept_name = dept_name;
    END

调用函数和过程

DECLARE d_count INTEGER;    # 变量声明请参考具体数据库

SET d_count = CALL dept_count_func('Physics')
CALL dept_count_proc('Physics', d_count)

注意：具体的变量声明、变量赋值、调用函数和过程请参考具体的数据库！！！

控制流

SQL也支持通用程序设计语言的if else, for, while，case

WHILE 布尔表达式 DO
    # some operations
END WHILE

REPEAT 
    # some operations
UNTIL #布尔表达式
END REPEAT

# FOR 暂时忽略

IF 布尔表达式
    # SOME OPERATIONS
ELSEIF 布尔表达式
    # SOME OPERATIONS
ELSE
    # SOME OPERATIONS
END IF

# case请参考前面

注意：每个数据库的变量声明和赋值、函数、过程、控制流等，可能和这里介绍的不一样，请查阅手册。

4.3 触发器（TRIGGER)

触发器：触发器是一条语句，当对数据库作修改的时候，它自动被系统执行。要设置触发器机制，必须满足两个要求：

• 指明什么条件下执行触发器
• 指明触发器执行时的动作

直接看例子：

CREATE TABLE time_slot (
    time_slot_id INT AUTO INCREMENT,
    PRIMARY KEY time_slot_id
)

CREATE TABLE section (
    time_slot_id INT,
    time TIMESTAMP,
    PRIMARY KEY time_slot_id,
    FORERIGN KEY (time_slot_id) REFERENCES time_slot
);

CREATE TRIGGER timeslot_check1 AFTER INSERT ON section
    REFERENCING NEW ROW AS nrow # nrow是插入的元组集合
    FOR EACH ROW        # 在每一个被插入的元组上进行迭代
    WHEN (nrow.time_slot_id NOT IN (SELECT time_slot_id FROM time_slot))
        BEGIN
            # some other operations if any
            ROLLBACK    # rollback insert
        END;
        

CREATE TRIGGER timeslot_check2 AFTER DELETE ON section
    REDERENCING OLD ROW AS orow # orow是被删除的元组的集合
    FOR EACH ROW        # 在每一个被删除的元组上进行迭代
    WHEN (orow.time_slot_id NOT IN (SELECT time_slot_id FROM section) AND orow.time_slot_id IN (SELECT time_slot_id FROM time_slot))
        BEGIN
            # some other operations if any
            ROLLBACK    # rollback delete
        END
        
CREATE TRIGGER timeslot_update AFTER UPDATE ON section
    # some operations
    
# 针对section的time_slot_id执行trigger
CREATE TRIGGER TIMESLOT_UPDATE OF section ON time_slot_id
    # some operations

REFERENCING OLD/NEW ROW AS 可以建立一个变量用来存储已经更新或删除的旧值。REFERENCING NEW ROW AS除了可以用在插入里面，还可以用在更新里面。

FOR EACH ROW的意思是行触发器，每一行受影响的话都会执行该触发器。如果省略，则无论多少行受影响，读只执行一次（不同数据库可能不一样）。

注意：如果数据量大，最好少用TRIGGER，能用其他方式替换就不要用Trigger。Trigger也可以通过存储过程来替换。

五 形式化关系查询语言

5.1 查询运算 SELECT

SELECT * FROM r WHERE P;

等价于：
_{σP='Physics'}(r)

例子：

SELECT * FROM instrcutor WHERE dept_name = 'Physics'

等价于：

_{σdept_name='Physics'}(instrcutor)

SELECT * FROM instructor WHERE salary > 9000;

等价于:

_{σsalary>9000='Physics'}(instrcutor)

5.2 投影运算 PROJECTION

_{πa1, a2}r

其中a1, a2是我们关系的属性，可能有一个或多个。

等价于SELECT 后面的属性。

5.3 运算组合

运算是可以组合的：

_{σsalary>9000$\bigwedge$dept_name='Physics'}='Physics'(instrcutor)

这里$\bigwedge$打不出来，是逻辑与(AND).

5.4 并运算 UNION

比如：找出年龄大20或小于10的人。

_πage>18section$\bigcup$_πage<10section

这里$\bigcup$是并集的操作。

鉴于笔者使用的markdown编辑器不支持LaTeX数学符号，其他的请参考《数据库系统概念》6th, 第6章。