set foreign_key_checks = 0;  # 关闭外键

create table dims.students (
    StudentID bigint not null primary key comment "学号",
    SName varchar(100) not null comment "学生姓名",
    SGender varchar(10) not null comment "性别",
    SBirth date comment "出生日期",
    SPhone varchar(15) not null comment "联系电话",
    SEmail varchar(50) comment "电子邮件",
    DormitoryID int(20) not null comment "宿舍号",
    foreign key (DormitoryID) references dims.dormitory(DormitoryID)
) engine = InnoDB default charset = utf8 comment "学生表";

set foreign_key_checks = 1;  # 打开外键

DELIMITER $$
CREATE TRIGGER email_suffix_check  # 添加触发器，在向students表添加记录前检查电子邮件书写的格式
BEFORE INSERT ON dims.students
FOR EACH ROW
BEGIN
    DECLARE email_domain VARCHAR(255);
    SET email_domain = SUBSTRING_INDEX(NEW.SEmail, '@', -1);
    IF email_domain NOT LIKE '%.%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email address must end with @__.__';
    END IF;
END$$
DELIMITER ;

insert into dims.students values
(2210515202, '张三', '男', '20040101', '12345678911', '123@qq.com', 1001),
(2210515203, '李四', '男', '20040202', '11987654321', '4567@163.com', 1011),
(2210515204, '王五', '女', '20040303', '13579246802', '89012@126.com', 901),
(2210515205, '赵六', '男', '20040404', '12468097531', '135791@icloud.com', 911),
(2210515206, '钱七', '女', '20040505', '19283745073', '2468024@nit.edu', 801),
(2210515207, '孙八', '男', '20040606', '18629375638', '66666666@ali.com', 811);