一. 实验环境:
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 ;