ECNU数据库作业——Lab5

Lab 5

EX1

To execute the following statements to create table t1 and three views v1, v2, and v3:

CREATE TABLE t1 (
	c INT
);

CREATE OR REPLACE VIEW v1
AS SELECT c FROM t1 WHERE c > 10;

CREATE OR REPLACE VIEW v2
AS SELECT c FROM v1
WITH CASCADED CHECK OPTION;

CREATE OR REPLACE VIEW v3
AS SELECT c FROM v2 WHERE c < 20;

Please indicate whether the following four statements can be executed successfully and provide an explanation for each.

INSERT INTO v1(c) VALUES (5);
INSERT INTO v2(c) VALUES (5);
INSERT INTO v3(c) VALUES (8);
INSERT INTO v3(c) VALUES (30);

在数据库中运行上述创建和插入代码，结果如下：

INSERT INTO v1(c) VALUES (5)
> Affected rows: 1
> 查询时间: 0.024s

INSERT INTO v2(c) VALUES (5)
> 1369 - CHECK OPTION failed 'lab5.v2'
> 查询时间: 0s

INSERT INTO v3(c) VALUES (8)
> 1369 - CHECK OPTION failed 'lab5.v3'
> 查询时间: 0.001s

INSERT INTO v3(c) VALUES (30)
> Affected rows: 1
> 查询时间: 0.024s

可以看到第1、4条插入语句运行成功，而第2、3条语句运行失败，

1. v1视图创建时没有with check option，因此不会拒绝不满足where子句的值插入，因此可以插入成功
2. v2视图依赖于v1视图，但加入了with cascaded check option，插入值5不满足where中的条件，拒绝插入
3. v3视图依赖于v2视图，由于v2视图中的级联检查选型，v3视图中也需要检查where c > 10，因此插入值8不满足where中的条件，拒绝插入
4. 类似于语句3，插入值30满足了where c > 10的条件，因此可以插入

注：而v3定义中的where c < 20条件，由于v3中没有加入with check option，因此不会拒绝不满足该条件的值插入，只会检查where c > 10条件

EX2

(1)

Design a stored procedure that takes a stadium ID as a parameter and returns the average temperature and the highest temperature for that stadium. If the stadium ID does not exist, return an appropriate error message.

DELIMITER //
CREATE PROCEDURE ex1 (IN ID INT, OUT avg_temperature INT, OUT max_temperature INT)
BEGIN
	-- 声明错误信息
    DECLARE error_message VARCHAR(255);
    -- 检查ID是否存在
    IF (EXISTS (SELECT * FROM stadium WHERE Stadium_ID = ID)) 
    THEN
        SELECT Average, Highest INTO avg_temperature, max_temperature
        FROM stadium
        WHERE Stadium_ID = ID;
    -- 如果ID不存在则输出错误信息
    ELSE
        SET error_message = CONCAT('Stadium with ID ', ID, ' does not exist.');
        -- 使用sql中的异常状态码
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = error_message;
    END IF;
END //
DELIMITER ;

-- 调用存储过程
CALL ex1(5, @s_avg_temperature, @s_max_temperature);
SELECT @s_avg_temperature, @s_max_temperature;

CALL ex1(100, @s_avg_temperature, @s_max_temperature);
SELECT @s_avg_temperature, @s_max_temperature;

上述两个调用分别输出：

@s_avg_temperature	@s_max_temperature
642	1125

CALL ex1(100, @s_avg_temperature, @s_max_temperature)
> 1644 - Stadium with ID 100 does not exist.

(2)

Create a function that takes a year as a parameter and returns the number of concerts held in that year. Consider returning 0 if no concerts took place.

DELIMITER //
CREATE FUNCTION ex2(y TEXT)
RETURNS INT
READS SQL DATA -- 这里需要加上函数的性质，否则会报错
BEGIN
	-- 检查是否存在某个year的演唱会
	IF(EXISTS(SELECT * FROM concert WHERE `Year` = y)) THEN
		RETURN(
			SELECT COUNT(*)
			FROM concert
			WHERE `Year` = y
		);
	ELSE 
		RETURN(0);
	END IF;
END//
DELIMITER ;

-- 调用
SELECT ex2(2014);
SELECT ex2(2015);
SELECT ex2(2016);
SELECT ex2(2017);

注：创建函数时，需要指定函数的性质，即函数是否是确定性的（DETERMINISTIC）、是否没有 SQL 操作（NO SQL）、或者是否具有读取 SQL 数据的能力（READS SQL DATA）

上述调用的输出结果分别为（其中2017年没有演唱会，返回0）：

(3)

Create a stored procedure that takes a country as a parameter and returns the number of singers born in that country.

DELIMITER //
CREATE PROCEDURE ex3(IN cName TEXT, OUT num_singer_born INT)
BEGIN
	SELECT COUNT(*) INTO num_singer_born
	FROM singer
	GROUP BY Country
	HAVING Country = cName;
END//
DELIMITER ;

-- 调用
CALL ex3('France', @num_singer_born);
SELECT @num_singer_born;

CALL ex3('United States', @num_singer_born);
SELECT @num_singer_born;

上述代码中的两个调用分别输出：

(4)

Design a stored procedure that takes a concert ID as a parameter and returns two result sets: one with the basic information of the concert (name, theme, year), and the other with information about the participating singers (name, country).

DELIMITER //
CREATE PROCEDURE ex4 (IN ID INT)
BEGIN
	-- 第一个结果集
	SELECT concert_Name, Theme, `Year`
	FROM concert
	WHERE concert_ID = ID;
	-- 第二个结果集
	SELECT singer.`Name`, singer.Country
	FROM singer
	JOIN singer_in_concert ON singer_in_concert.Singer_ID = singer.Singer_ID
	WHERE singer_in_concert.concert_ID = ID;
END//
DELIMITER ;

-- 调用存储过程，直接用call语句即可返回两个结果集，不需要select
CALL ex4(1);

上述调用输出：

(5)

Design a trigger that, upon inserting a new record into the ‘singer’ table, automatically inserts a new record into the ‘concert’ table, representing a concert held by that singer in the same year. Assume the concert’s name is “Singer’s Concert” and the theme is “Artist Showcase.”

DELIMITER //
CREATE TRIGGER ex5
AFTER INSERT ON singer
FOR EACH ROW
BEGIN
	DECLARE count_id INT;
	SET count_id = (
		(SELECT COUNT(*) FROM concert)
	);
	INSERT INTO concert VALUES(count_id + 1, CONCAT(new.NAME, '\'s Concert'), 'Artist Showcase', NULL, NULL);
END//
DELIMITER ;

-- 往singer表中插入记录
INSERT INTO singer VALUES(15, 'Taylor Swift', 'United States', 'Love Story', '2000', 22, 0);

singer表插入记录后，触发器被触发，往concert表中插入记录，结果如下：

(6)

Create a trigger that, upon updating records in the ‘stadium’ table, checks if the new maximum capacity is greater than twice the old maximum capacity. If so, reject the update and return an error message.

DELIMITER //
CREATE TRIGGER ex6
BEFORE UPDATE ON stadium
FOR EACH ROW
BEGIN
    IF NEW.Highest > 2 * OLD.Highest THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Update rejected';
    END IF;
END//
DELIMITER ;

-- 更新stadium表中的记录
UPDATE stadium SET Highest = 2000 WHERE Stadium_ID = 1;
UPDATE stadium SET Highest = 2000 WHERE Stadium_ID = 10;

上述更新语句中，第一个更新语句执行前后的stadium表如下，可以看到更新成功：

第二个更新语句中，更新失败，输出如下：

UPDATE stadium SET Highest = 2000 WHERE Stadium_ID = 10
> 1644 - Update rejected

(7)

Create a trigger that, when a record is deleted from the ‘concert’ table, automatically deletes all records in the ‘singer_in_concert’ table related to the corresponding concert ID.

DELIMITER //
CREATE TRIGGER ex7
BEFORE DELETE ON concert -- 注意由于singer_in_concert中的外键约束，这里需要用BEFORE
FOR EACH ROW
BEGIN
	DELETE FROM singer_in_concert
	WHERE old.concert_ID = singer_in_concert.concert_ID;
END//
DELIMITER ;

INSERT INTO singer_in_concert VALUES(9, 15);

DELETE FROM concert WHERE concert_ID = 9;

执行删除操作前后的singer_in_concert表内容分别如下：