使用T-SQL实现日历
分类:默认栏目
|
| |||
| |||
|
if object_id('dbo.backtable') is not null
drop proc dbo.backtable
go
create proc dbo.backtable
@EmployeeID int=5,
@TableName varchar(128),
@Path varchar(256)
AS
declare @str varchar(4000)
set @str='bcp "select * from Northwind..'+@TableName+' where EmployeeID='+cast(@EmployeeID as varchar(10))+'" queryout '+@path+' -c -Usa -Padmin'
exec master..xp_cmdshell @str
go
exec dbo.backtable 5,'Orders','d:dd.txt'
刚学sql server两天,写了一个简单的触发器.纪念一下.
if exists(select name from sysobjects
where name='tri_update' and type='tr')
drop trigger tri_update
go
create trigger tri_update
on Student for update
AS
declare @old_id int
declare @new_id int
declare @row_count int
select @row_count=@@rowcount
if @row_count>=1
begin
select @old_id=d.StudentID from deleted d
select @new_id=i.StudentID from inserted i
if exists(select stu_id from stu_new where stu_id=(select StudentID from inserted i where i.StudentID=@new_id))
update stu_new
set stu_id=i.StudentID,stu_name=i.StudentName,stu_sex=i.StudentSex
from inserted i
else
insert into stu_new
select * from inserted i
if exists(select stu_id from stu_old where stu_id=(select StudentID from deleted d where d.StudentID=@old_id))
update stu_old
set stu_id=d.StudentID,stu_name=d.StudentName,stu_sex=d.StudentSex
from deleted d
else
insert into stu_old
select * from deleted d
end
触发器能进行一些约束.
这是个小例子,当Student表的StudentID列被发生更改时,BorrowStudent表的StudentID列也跟着更改.如果Student表删除某记录,BorrowStudent也删除对应StudentID的记录.
创建表的脚本就不贴出来了.
delimiter ＄＄
drop trigger if exists tduStudent＄＄
drop trigger if exists tddStudent＄＄
create trigger tduStudent before update
on Student for each row
begin
if new.StudentID!=old.StudentID then
update BorrowStudent
set BorrowStudent.StudentID=new.StudentID
where BorrowStudent.StudentID=old.StudentID;
end if;
end＄＄
create trigger tddStudent before delete
on Student for each row
begin
delete
from BorrowStudent
where BorrowStudent.StudentID=old.StudentID;
end＄＄
delimiter ;
刚写的一个查询小例子.
分类:默认栏目
drop table if exists `Computer`;
drop table if exists `Record`;
create table `Card`(
`ID` int not null auto_increment,
`CardID` char(8) not null,
`pwd` varchar(16) not null,
`balance` float not null,
`name` varchar(16) not null,
primary key(`ID`)
)engine=myisam;
create table `Computer`(
`ID` int not null auto_increment,
`ComputerID` char(3) not null,
`CardID` char(8) not null,
`isUse` bit(1) not null default 0,
`remark` varchar(300),
primary key(`ID`),
foreign key(`CardID`) references `Card`(`CardID`)
)engine=myisam;
create table `Record`(
`ID` int not null auto_increment,
`CardID` char(8) not null,
`ComputerID` char(8) not null,
`BeginTime` datetime ,
`EndTime` datetime,
primary key(`ID`),
foreign key(`ComputerID`) references `Computer`(`ComputerID`),
foreign key(`CardID`) references `Card`(`CardID`)
)engine=myisam;
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000001','123456',15.3,'purana');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000002','756321',15.3,'jiajia');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000003','12345',15.3,'jiadf');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000004','75145',15.3,'mysql');
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('001','123456',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('002','756321',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('003','12345',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('015','75145',1);
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000001','001','2007-09-09 10:15:12','2007-09-09 11:01:02');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000002','002','2007-09-09 15:35:33','2007-09-09 16:22:33');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000003','003','2007-09-09 20:14:01','2007-09-10 01:33:02');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000004','015','2007-09-10 01:42:10','2007-09-10 22:01:02');
select Card.CardID,name,t.BeginTime,t.EndTime
from `Card` join (select CardID,BeginTime,EndTime,(EndTime-BeginTime) as `time`
from Record
where BeginTime between '2007-09-09 00:00:00' and '2007-09-09 23:55:55') As t
on Card.CardID=t.CardID
order by t.`time` desc
limit 3;
name num
tom 3
jerry 2
lily 1
现在要得出如下结果集
tom
tom
tom
jerry
jerry
lily
编写存储过程如下:
delimiter ＄＄
drop procedure if exists sp_test ＄＄
create procedure sp_test()
begin
declare done int default 0;
declare n_num int default 0;
declare n_count int default 0;
declare i int default 0;
declare sname varchar(64);
declare cur cursor for (select name,num from test);
declare continue handler for sqlstate '02000' set done=1;
create temporary table temp_test(name varchar(64));
open cur;
fetch cur into sname,n_count;
while done=0 do
set n_num=0;
while n_num<n_count do
insert into temp_test values(sname);
set n_num=n_num+1;
end while;
fetch cur into sname,n_count;
end while;
close cur;
select name from temp_test;
end＄＄
delimiter ;
如果相反,就更简单了.
insert into test
select name,count(name)
from temp
group by name;
缺点：
1、可移植性是存储过程和触发器最大的缺点。
2、占用服务器端太多的资源，对服务器造成很大的压力
3、不能做DDL。
4、触发器排错困难，而且数据容易造成不一致，后期维护不方便。
优点：
1、预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。
2、存储过程可以重复使用，减少开发人员的工作量。
3、业务逻辑封装性好，修改方便。
4、安全。不会有SQL语句注入问题存在。
使用mysql可以导出html和xml.
用法:
mysql -uroot -p --html "--execute=select * from tblname" dbname > filename
这样就可以导出到一个html文件
mysql -uroot -p --xml "--execute=select * from tblname" dbname > filename
这样可以导出xml文件.
还可以利用其它工具进行导出.
在这里也介绍几个我个人觉得比较好用的工具.
MySQL Administrator 在管理MySQL,备份,恢复..都是很不错的..
MySQL Query Browser 用来查询,执行脚本.比较两个查询的差别..都是很好的.
SQLyog 则是强烈推荐的工具,无论在哪方面.都做得很出色.
delimiter ＄＄
drop procedure if exists sp_mul＄＄
create procedure sp_mul(out o_mul int)
begin
declare i_temp int default 1;
declare i_error int default 0;
declare i_mul int default 1;
declare rs_cursor cursor for (select number1 from temp);
declare continue handler for sqlstate '02000' set i_error=1;
open rs_cursor;
while i_error=0 do
fetch rs_cursor into i_temp;
set i_mul=i_mul*i_temp;
end while;
close rs_cursor;
set o_mul=i_mul;
end＄＄
delimiter ;
通常调用以上的存储过程.就可以实现了.
mysql中使用游标
分类:默认栏目
delimiter ＄＄
drop procedure if exists sp_max ＄＄
create procedure sp_max(in i_count int,out o_max int)
begin
declare i,iMax,iTemp int;
declare i_error int default 0;
declare rs_cursor cursor for select eid from emp;
declare continue handler for sqlstate '02000' set i_error=1;
set i=0;
set iMax = 0;
open rs_cursor;
while i<i_count do
fetch rs_cursor into iTemp;
if iTemp>iMax then
set iMax=iTemp;
end if;
set i=i+1;
end while;
close rs_cursor;
set o_max=iMax;
end＄＄
delimiter ;
再加上刚写的一个函数,一起帖出来.
delimiter ＄＄
drop function if exists sp_GetNameStr ＄＄
create function sp_GetNameStr(in_name varchar(10))
returns varchar(300)
begin
declare nameStr varchar(300);
declare tempStr varchar(10);
declare i_error int default 0;
declare iCount int;
declare rs_cur cursor for (select admin from t2 where t2.name=in_name);
declare continue handler for sqlstate '02000' set i_error=1;
set nameStr='';
open rs_cur;
while i_error=0 do
fetch rs_cur into tempStr;
if nameStr='' then
set nameStr=tempStr;
else
set nameStr=concat(nameStr,',',tempStr);
end if;
end while;
close rs_cur;
return nameStr;
end＄＄
delimiter ;
/*
* TempTest.java
*
* Created on 2007年8月28日, 上午10:55
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package com.test;
import java.util.*;
import java.io.*;
/**
*
* @author Administrator
*/
public class TempTest {
public static void main(String[] args) throws Exception{
TreeMap<String,Integer> tm=new TreeMap<String,Integer>();
Scanner scanner=new Scanner(new File("e:/dj5.txt"));
while(scanner.hasNext()){
String word=scanner.next();
int count=getCount(word,tm)+1;
tm.put(word,new Integer(count));
}
System.out.println(tm.toString());
}
public static int getCount(String word,TreeMap<String,Integer> tm){
if(tm.containsKey(word)){
return tm.get(word).intValue();
}else{
return 0;
}
}
}
|
| |||
| |||
|
| ||
|
扫一扫
3984
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
