一. 实验环境:
1、 操作系统:window10
2、 MySQL 5.7
二. 实验内容与完成情况:
1.创建一个教工表teacher(tno,tname,tadd,telphone,tsex,id),将教工号tno设为主键,性别默认值为“男”:
create table teacher( tno char(7) primary key, tname char(10), tadd char(10), telphone char(10), tsex char(2) default '男', id int )engine=InnoDB
2.根据教工表teacher完成以下任务。
- 设置telphone默认值为00000000:
alter table teacher modify telphone char(10) default '00000000';
2) 设置tsex的check检查约束为:输入值只能为“男”或“女”:ALTER TABLE teacher modify tsex enum('男','女') default '男' ;
3) 设置id的位数为15位或18位,每位都是数字:
先创建一个判断数字的函数IsNum:delimiter $$ drop function if exists IsNum $$ create function IsNum(str varchar(25)) returns int begin declare iResult int default 0; if isnull(str) then return 0;end if; if str='' then return 0;end if; select str REGEXP '^[0-9]*$' into iResult; if iResult=1 then return 1; else return 0; end if; end $$
alter table teacher modify id char(20);
delimiter //
create trigger TR_ID_IN after insert on teacher
for each row
begin
if (length(new.id)!=15 and length(new.id)!=18) or (IsNum(new.id)!=1) then
signal sqlstate '45000'
set message_text='Insert Error';
end if;
end;//
delimiter ;
delimiter //
create trigger TR_ID_UP after update on teacher
for each row
begin
if (length(new.id)!=15 and length(new.id)!=18) or (IsNum(new.id)!=1) then
signal sqlstate '45000'
set message_text='Update Error';
end if;
end;//
delimiter ;