mysql 函数 相关应用 实例(笔试题) (1)

创建表 td_user(userid,name,sex) ,td_check(userid, workhours, closinghours),表td_user表示员工表，td_check表表示上班、下班考勤表，其中workhours表示上班时间，closinghours表示下班时间

create table td_user(

userid int(11) primary key auto_increment not null, 

name char(10), 

sex char(10));

create table td_check(

userid int(11) not null, 

workhours datetime, 

closinghours datetime);

（删除表drop table if exists td_check;）

创建的表td_check:

创建的表td_user:

练习

  1.查询2011年5月份迟到的员工姓名，9点以后到公司即按迟到处理。

select distinct td_user.name
from
td_user,
(select 
 hour(td_check.workhours) as starthour,
month(td_check.workhours) as months,
td_check.userid as cid 
from td_check
) as latetime
where latetime.starthour>=9 
   and td_user.userid = latetime.cid
   and latetime.months = 5

运行结果：

 2.查询每个月迟到3次以上的员工姓名，并按次数的多少进行排列

select counttable.latenum, counttable.latename,counttable.everymonth
from
(select  count(td_user.name) as latenum, td_user.name as latename,
 latetime.months as everymonth
from
td_user,
(select 
 hour(td_check.workhours) as starthour,
month(td_check.workhours) as months,
td_check.userid as cid 
from td_check
) as latetime
where latetime.starthour>=9 
   and td_user.userid = latetime.cid
group by latetime.months
order by latetime.months
) as counttable
where counttable.latenum>=3

运行结果：

3.编写触发器，当删除员工时，也将删除所有的出勤记录。

（drop trigger if exists  t_after_on_td_user;）

//下面的不是很对啊,,,,! 如果有谁会用 触发器，可以给我回个贴么？万分感谢！

create trigger t_after_on_td_user
after insert on td_user
for each row
begin 
   insert into td_check(userid)  values(new.td_user.userid);
end;

注：第一次笔试题