ZK数据库实验十三（Mysql与SQL Server区别）

\1. 数据库：建立一个“选课系统”数据库，命名为choose，该数据库的字符集设置为gbk。

CREATE DATABASE choose CHARACTER SET gbk;

\2. 表：在choose数据库中创建1张表，学生情况表(编号，学号、姓名、专业、性别、出生日期、年龄、运动爱好)，其中编号为AUTO_INCREMENT，主键为编号，性别要求只能输入“男”或“女”(提示：使用enum)，年龄在10-40岁之间，运动爱好只能在篮球、足球、排球、羽毛球、乒乓球、游泳和其他这几个选项选择，可以多选（提示：使用set）。创建完表后插入几行数据测试一下。-- 创建数据库

-- 使用数据库
USE choose;

-- 创建学生情况表
CREATE TABLE 学生情况表 (
  编号 INT AUTO_INCREMENT PRIMARY KEY,
  学号 VARCHAR(10) NOT NULL,
  姓名 VARCHAR(100) NOT NULL,
  专业 VARCHAR(100) NOT NULL,
  性别 ENUM('男', '女') NOT NULL,
  出生日期 DATE NOT NULL,
  年龄 INT CHECK (年龄 >= 10 AND 年龄 <= 40),
  运动爱好 SET('篮球', '足球', '排球', '羽毛球', '乒乓球', '游泳', '其他') NOT NULL
);

-- 插入示例数据
INSERT INTO 学生情况表 (学号, 姓名, 专业, 性别, 出生日期, 年龄, 运动爱好)
VALUES
  ('001', '张三', '计算机科学', '男', '2000-01-01', 20, '篮球,足球'),
  ('002', '李四', '化学工程', '女', '2001-02-03', 19, '乒乓球,游泳'),
  ('003', '王五', '机械工程', '男', '1999-05-10', 22, '排球,游泳,其他');

\3 查询

（1）建立一个“jiaoxue”数据库，通过运行jiaoxue.sql里面的SQL语句导入“jiaoxue”数据库；“jiaoxue”数据库中4张表信息如下：

教职工表Teacher(Tno, TName, age, sal, mgr, DNo)，TNo为教职工编号，TName为教职工姓名，age年龄，sal为月薪，mgr为教职工的上一级领导的职工号，DNo为部门号；

课程表Course(Cno, CName, CCredits, CTno)，Cno为课程编号，CName为课程名字，CCredits为课程学分, CTno为该课程上课老师的编号，其中外键CTno来自表Teacher的Tno键；

学生表Student(Sno, CLno, SName, SSex, SBir, Email)，其中Sno为学生学号，CLno为系别编号，SName为学生姓名，SSex为学生性别，SBir为学生出生日期；

学生选课表SC(Sno, Cno, Score)。

（2）对jiaoxue数据库进行如下检索

1）查询课程号为001的成绩前五名的学生学号和成绩（假设成绩没有重复）。

SELECT Sno, Score**

**FROM SC**

**WHERE Cno = '001'**

**ORDER BY Score DESC**

**LIMIT 5;

查询每个系的学生人数以及全部学生总人数（包括总人数的汇总）：

2）查询每个系的学生人数以及全部学生总人数（提示：with rollup）；

SELECT CLno, COUNT(\*) AS StudentCount**

**FROM Student**

**GROUP BY CLno WITH ROLLUP;

3）查询每个学生的学号、姓名和选课的课程号，每个学生只显示一行数据，如果该学生选了多门课，则所选的课程号放在同一行里，用逗号隔开（提示：group_concat()）；

 **SELECT Sno, SName, GROUP_CONCAT(Cno SEPARATOR ',') AS Courses**

**FROM SC**

**JOIN Student ON SC.SNo = Student.SNo**

**GROUP BY Student.Sno, Student.SName;

**

4）查询课程名以英文字母开头的课程信息（提示：正则表达式查询）；

SELECT \***

**FROM Course**

**WHERE CName REGEXP '^[A-Za-z]';

5）查询课程名纯英文字母的课程信息（提示：正则表达式查询）。

SELECT \***

**FROM Course**

**WHERE CName REGEXP '^[A-Za-z]+$';

\4. 自定义函数

（1）创建一个函数，参数为学号，使函数实现通过学号查学生姓名，并调用该函数查询学号“00001”和“00002”的姓名；

drop function GetStudentNameBySno;

-- 创建函数

 

CREATE FUNCTION get_student_info_by_sno(sno VARCHAR(10))

RETURNS VARCHAR(300)

DETERMINISTIC

BEGIN

RETURN (SELECT s.sname

FROM student s WHERE sno =s.sno );

END//

DELIMITER ;

 

-- 调用函数

SELECT get_student_info_by_sno('00001');

-- 调用函数查询学号为"00001"的姓名

（2）创建一个函数，参数为n，计算1至n之间的偶数相加并返回相加结果，分布使用while、repeat和loop实现。

CREATE FUNCTION SumEvenNumbersWhile(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    

    WHILE i <= n DO
        IF i % 2 = 0 THEN
            SET sum = sum + i;
        END IF;
        SET i = i + 1;
    END WHILE;
    
    RETURN sum;

END;


CREATE FUNCTION SumEvenNumbersRepeat(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    

    REPEAT
        IF i % 2 = 0 THEN
            SET sum = sum + i;
        END IF;
        SET i = i + 1;
    UNTIL i > n END REPEAT;
    
    RETURN sum;

END;


CREATE FUNCTION SumEvenNumbersLoop(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    

    my_loop: LOOP
        IF i % 2 = 0 THEN
            SET sum = sum + i;
        END IF;
        SET i = i + 1;
        IF i > n THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
    
    RETURN sum;

END;

\5. 存储过程

（1）创建一个存储过程，参数为课程号，返回选修某门课程的总人数，然后再执行该过程后把总人数显示出来，建立完存储过程后测试该存储过程；

1）

DELIMITER //

CREATE PROCEDURE GetCourseEnrollmentCount(IN cno VARCHAR(10), OUT enrollment_count INT)

BEGIN

  SELECT COUNT(*) INTO enrollment_count FROM SC WHERE Cno = cno;

END//

DELIMITER ;

 

-- 测试存储过程，返回选修课程号为'001'的总人数

CALL GetCourseEnrollmentCount('001', @enrollment_count);

SELECT @enrollment_count;

（2）创建一个存储过程，通过学号和课程号进行成绩查询，如果查询结果为空，则显示“该学生没有选这门课”，返回0，如果查询结果低于60分，则显示“该学生考试成绩不及格”，返回1，如果查询结果在60-89之间，则显示“该学生考试成绩为及格”，返回2，如果查询结果大于等于90，则显示“该学生考试成绩为优秀”，返回3，建立完存储过程后测试该存储过程。

DROP PROCEDURE GetGradeInfo ;

DELIMITER //

CREATE PROCEDURE GetGradeInfo(IN sno VARCHAR(10), IN cno VARCHAR(10), OUT result_code INT)

BEGIN

  DECLARE grade INT;

  SELECT Score INTO grade FROM SC WHERE Sno = sno AND Cno = cno;

  IF grade IS NULL THEN

​    SELECT '该学生没有选这门课' AS result;

​    SET result_code = 0;

  ELSEIF grade < 60 THEN

​    SELECT '该学生考试成绩不及格' AS result;

​    SET result_code = 1;

  ELSEIF grade >= 60 AND score <= 89 THEN

​    SELECT '该学生考试成绩为及格' AS result;

​    SET result_code = 2;

  ELSE

​    SELECT '该学生考试成绩为优秀' AS result;

​    SET result_code = 3;

  END IF;

END//

DELIMITER ;

-- 测试存储过程，查询学号为'00001'、课程号为'001'的成绩情况

CALL GetGradeInfo('00001', '001', @result_code);

SELECT @result_code;

select * from student s where s.SNo='00001';

INSERT INTO Course (Cno, CName, CCredits, CTno) VALUES ('005', '职业规划', 4, '0014');

\6. 触发器

（1）在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，当插入数据时，规定出生年份为2003和2004的学生才能选修职业规划这门课程，否则则输出’不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00002’,’005’,null)和(‘00003’,’005’,null)看看结果；

-- 创建触发器
DELIMITER //
CREATE TRIGGER CheckEnrollment
BEFORE INSERT ON SC
FOR EACH ROW
BEGIN
    DECLARE birth_year INT;
    

    -- 查询学生的出生年份
    SELECT YEAR(SBir) INTO birth_year
    FROM Student
    WHERE Sno = NEW.Sno;
    
    -- 判断是否符合选修条件，如果不符合则抛出异常
    IF NEW.Cno = '005' AND (birth_year <> 2003 AND birth_year <> 2004) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能选修该门课程';
    END IF;

END//
DELIMITER ;

-- 插入数据测试触发器
INSERT INTO SC (Sno, Cno, Score) VALUES ('00002', '005', NULL);  --
INSERT INTO SC (Sno, Cno, Score) VALUES ('00003', '005', NULL);  -- 

（2）在表Teacher中创建触发器，当表Teacher进行更新操作时，如果更新工资比以前低，则把工资改回来原来值，如果更新名字时出现重名，则把名字改回原来的值，建立完触发器后测试该触发器；

–

创建触发器

DELIMITER //
CREATE TRIGGER CheckUpdate
BEFORE UPDATE ON Teacher
FOR EACH ROW
BEGIN
    DECLARE old_salary INT;
    DECLARE old_name VARCHAR(100);
    

    -- 检查更新前的工资
    SELECT sal INTO old_salary
    FROM Teacher
    WHERE Tno = OLD.Tno;
    
    -- 检查更新前的姓名
    SELECT TName INTO old_name
    FROM Teacher
    WHERE Tno = OLD.Tno;
    
    -- 检查工资是否比原来低，如果是则将工资改回原来的值
    IF NEW.sal < old_salary THEN
        SET NEW.sal = old_salary;
    END IF;
    
    -- 检查姓名是否重复，如果是则将姓名改回原来的值
    IF NEW.TName = old_name THEN
        SET NEW.TName = old_name;
    END IF;

END//
DELIMITER ;

-- 更新操作测试触发器
UPDATE Teacher SET sal = sal - 1000 WHERE Tno = '001';  -- 工资减少，触发器将工资恢复原值
UPDATE Teacher SET TName = 'John Doe' WHERE Tno = '001'; -- 名字重复，触发器将名字恢复原值

（3）在表course中建立删除触发器，实现表course和表SC的级联删除，也就是只要删除表course中课程号为c1的元组，则表SC中cno为c1的元组也要删除，建立完触发器后测试触发器。

DELIMITER //
CREATE TRIGGER CascadeDelete
AFTER DELETE ON Course
FOR EACH ROW
BEGIN
    DELETE FROM SC WHERE Cno = OLD.Cno;
END//