我需要做一个老师的日程表
数据库中有一个table(teacher)是所有老师列表,大概是这样:
id name
1 teacher1
2 teacher2
还有一个table(teacherCalendar)是日期,每个老师对应的至少有7天,大概如下:
id teacherId date
1 1 2009-1-21
2 1 2009-1-22
... ... ...
2 2009-1-22我现在想的是,是否可以在每天的某个时间点,在teacherCalendar表中,增加记录,记录的内容是每个老师的一天时间现在不知道怎么实现,如何定时实现
数据库中有一个table(teacher)是所有老师列表,大概是这样:
id name
1 teacher1
2 teacher2
还有一个table(teacherCalendar)是日期,每个老师对应的至少有7天,大概如下:
id teacherId date
1 1 2009-1-21
2 1 2009-1-22
... ... ...
2 2009-1-22我现在想的是,是否可以在每天的某个时间点,在teacherCalendar表中,增加记录,记录的内容是每个老师的一天时间现在不知道怎么实现,如何定时实现
解决方案 »
- 谁知道怎样判断表里面有没这条数据,如果没就添加有就更新
- 急... 子句中的分页如何实现
- Postgresql8.4 DELETE 命令删除时间段
- 在线求助
- 我打的语句为什么都有问题;
- 在检索数据库数据时,提示“Can't create/write to file 'c:\windows\temp\#sql_7f0_0.MYI'错误。。。
- mysql命令行的声音报警
- 安装MYSQL 4.0后,启动winmysqladmin,在mysql-front中以root登录,提示主机不允许连接到MYSQL SERVER。咋回事?
- 问一个连接MYSQL时的一个奇怪的问题,急求解!
- 安装mysql.............
- 一个诡异的访问mysql结果集的问题。
- 50分求一个sql语句的写法!
在你的MySQL所在的主机上定时执行一个脚本,比如下面这个命令。[code=BatchFile]mysql -u userid -ppasswd mydb -e 'insert into teacherCalendar(teacherId,date) select id,CURDATE() from teacher;'[/code]这样每天可以把所有教师在表teacherCalendar添加当日的记录,你可以在主机定在早上5点运行.Linux下见参见crontab命令, windows下则简单,直接看计划任务就行了
2009-1-21 00:00-01:00
2009-1-21 01:00-02:00
... ...
2009-1-21 23:00-00:00
这样的24条记录请教如何写
谢谢
ON SCHEDULE
EVERY 1 DAY
COMMENT 'test..'
DO
BEGIN
INSERT INTO site_activity.totals (when, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |DELIMITER ;
我这里执行的东西有点复杂,我不知道怎么写。就是要判断teacherCalender里面对应某个老师,date列从当天算起往后记录如果少于7天就把它补到7天,比如:今天是2009-1-20日,如果只有2009-1-21这一条记录,那么就要增加6条记录,分别是2009-1-22,...,2009-1-27。这些弄好了以后,要在另外一个table(teacher_calendar_time)里面,要插入对应每一天的24条记录,是按小时的,如:2009-1-21这天,就有:
2009-1-21 00:00-01:00
2009-1-21 01:00-02:00
... ...
2009-1-21 23:00-00:00
这样的24条记录
--------------------------
我的主要问题是 上面这些处理我不知道怎么写SQL,谢谢各位,望不吝赐教
declare i int;
declare j int;
set @bdate=curdate();
set i=0;while i<8
insert into teacherCalendar(teacherId,date) select id,@bdate from teacher where id not in (select id from teacherCalendar where date=@bdate);
insert into teacher_calendar_time(teacherId,date,xxxCol) select id,@bdate from teacher where not exists (select id from teacher_calendar_time where date=@bdate and teacherId=teacher.id and xxxCol='00:00-01:00');
insert into teacher_calendar_time(teacherId,date,xxxCol) select id,@bdate from teacher where not exists (select id from teacher_calendar_time where date=@bdate and teacherId=teacher.id and xxxCol='01:00-02:00');
....
insert into teacher_calendar_time(teacherId,date,xxxCol) select id,@bdate from teacher where not exists (select id from teacher_calendar_time where date=@bdate and teacherId=teacher.id and xxxCol='23:00-00:00');
set i=i+1;
set @bdate=@bdate+1;
end while;
declare i int;
declare j int;
set @bdate=curdate();
set i=0;
这附近有错,不知道哪错了,是@符号不要吗,如果不要,下面的insert语句里面该怎么写?
CREATE EVENT test
ON SCHEDULE
EVERY 5 SECOND
DO
BEGIN
DECLARE i int;
SET i=0;
WHILE i<2 DO
insert into teacher(name) values('teacher');
SET i=i+1;
END WHILE
END为什么不执行呢,我发现teacher这个table里面的记录没变
DECLARE i int;
SET i=0;
WHILE i<2 DO
insert into teacher(name) values('teacher');
SET i=i+1;
END WHILE
END
现在的问题是
while i<8
insert into teacherCalendar(teacherId,date) select id,@bdate from teacher where id not in (select id from teacherCalendar where date=@bdate);
insert into teacher_calendar_time(teacherId,date,xxxCol) select id,@bdate from teacher where not exists (select id from teacher_calendar_time where date=@bdate and teacherId=teacher.id and xxxCol='00:00-01:00');
insert into teacher_calendar_time(teacherId,date,xxxCol) select id,@bdate from teacher where not exists (select id from teacher_calendar_time where date=@bdate and teacherId=teacher.id and xxxCol='01:00-02:00');
....
insert into teacher_calendar_time(teacherId,date,xxxCol) select id,@bdate from teacher where not exists (select id from teacher_calendar_time where date=@bdate and teacherId=teacher.id and xxxCol='23:00-00:00');
set i=i+1;
set @bdate=@bdate+1;
end while;这里面bdate自动加以,我测试的时候,他的值变成20090132的时候,就插入不成功了,因为20090132不是一个正确的日期。如何能让他自动变成20090101呢?