haue数据库原理第四次实验

南桥

发布于 2024-12-10 08:22:06

8800

代码可运行

文章被收录于专栏：南桥谈编程南桥谈编程

运行总次数：0

代码可运行

一、实验学时

2学时

二、实验目的

（1）掌握使用SQL语句CREATE TABLE定义完整性约束的方法。（2）掌握使用SQL语句ALTER TABLE增加或删除完整性约束的方法。（3）掌握使用SQL语句CREATE TRIGGER创建触发器的方法。（4）掌握存储过程的定义及调用方法。（5）掌握函数的定义及调用方法。

三、实验内容

1．查看student、course表的所有约束。（5分）

SELECT * FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME IN ('student', 'course');

2．在student表的姓名列建立唯一约束名uq_sname。（5分）

ALTER TABLE student ADD CONSTRAINT uq_sname UNIQUE (Sname);

3．在course表的学分列建立检查约束ck_xf，检查条件为学分>0。（注意：MySQL下CHECK约束可以设置但不起作用）（5分）

ALTER TABLE course ADD CONSTRAINT ck_xf CHECK (Ccredit > 0);

4．删除student表的唯一约束uq_sname。（5分）

ALTER TABLE student DROP INDEX uq_sname;

5．删除course表的检查约束ck_xf。（5分）

DROP TRIGGER IF EXISTS ck_xf;

6．创建测试表test，包含两个字段：sno，字段类型char(8)；date_time，字段类型varchar(50)；

创建触发器test_trig，实现在student表中每插入一条学生记录后，则自动在test表中追加一条插入成功时的学号和日期时间。SYSDATE()函数用来获取当前的日期和时间。为student表插入一条记录引发触发器，查看test表的内容。（15分）

CREATE TABLE test (
sno CHAR(8),
date_name VARCHAR(5)
);

DELIMITER $$

CREATE TRIGGER test_trig
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    INSERT INTO test (sno, date_time)
    VALUES (NEW.sno, SYSDATE());
END $$

DELIMITER ;

7．创建存储过程p_max，输入3个数，输出最大的数。（15分）

DELIMITER $$

CREATE PROCEDURE p_max(
    IN num1 INT,
    IN num2 INT,
    IN num3 INT,
    OUT max_num INT
)
BEGIN
    -- 使用 IF-ELSE 语句找出最大值
    IF num1 >= num2 AND num1 >= num3 THEN
        SET max_num = num1;
    ELSEIF num2 >= num1 AND num2 >= num3 THEN
        SET max_num = num2;
    ELSE
        SET max_num = num3;
    END IF;
END $$

DELIMITER ;

-- 声明一个变量来接收输出
SET @max_value = 0;

-- 调用存储过程
CALL p_max(10, 20, 15, @max_value);

-- 查看结果
SELECT @max_value;

8．创建存储过程p_sum，计算1~100以内偶数的和并输出。（15分）

DELIMITER $$

CREATE PROCEDURE p_sum(OUT total_sum INT)
BEGIN
    DECLARE num INT DEFAULT 2;  -- 从2开始，因为2是第一个偶数
    DECLARE sum INT DEFAULT 0;  -- 初始化和为0

    -- 循环遍历从2到100的偶数
    WHILE num <= 100 DO
        SET sum = sum + num;  -- 累加偶数到sum
        SET num = num + 2;     -- 下一个偶数
    END WHILE;

    -- 将计算结果输出
    SET total_sum = sum;
END $$

DELIMITER ;

-- 声明一个变量接收结果
SET @sum = 0;

-- 调用存储过程
CALL p_sum(@sum);

-- 查看结果
SELECT @sum;

9．使用stu_course数据库中的student表、sc表。（15分）

（1）创建存储过程stu_name，当任意输入一个学生的姓名时，查看其所选课程的最高分、最低分、平均分。

DELIMITER $$

CREATE PROCEDURE stu_name(IN stu_name_input VARCHAR(100))
BEGIN
    DECLARE max_score DECIMAL(5, 2);
    DECLARE min_score DECIMAL(5, 2);
    DECLARE avg_score DECIMAL(5, 2);

    -- 查找学生的最高分、最低分和平均分
    SELECT MAX(score), MIN(score), AVG(score)
    INTO max_score, min_score, avg_score
    FROM sc
    JOIN student ON sc.student_id = student.student_id
    WHERE student.student_name = stu_name_input;

    -- 输出结果
    SELECT max_score AS 'Highest Score', min_score AS 'Lowest Score', avg_score AS 'Average Score';
END $$

DELIMITER ;

（2）调用存储过程stu_name。

CALL stu_name ('张三');

（3）删除存储过程stu_name。

DROP PROCEDURE IF EXISTS stu_name;

10．创建一个函数get_num，以学号为输入参数，返回该学生的选课数量。调用该函数。（15分）

DELIMITER $$

CREATE FUNCTION get_num(student_id_input INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE course_count INT;

    -- 查询该学生选修的课程数量
    SELECT COUNT(DISTINCT course_id) INTO course_count
    FROM sc
    WHERE student_id = student_id_input;

    -- 返回选课数量
    RETURN course_count;
END $$

DELIMITER ;

-- 调用函数并传入学号
SELECT get_num(12345) AS 'Selected Course Count';

-- 删除函数
DROP FUNCTION IF EXISTS get_num;