delete from tb where datediff(yy,借阅时间,getdate())>=nupdate tb set s=3*b
udpate tableName set s=b*3 where s=10
update tablename set s=b*3 where date<dateadd(yy,-3,getdate())--3年前
delete tablename where 借阅时间<'2010-01-01'--这样指定个日期之前的删除delete tablename where datediff(yy,借阅时间,getdate())>3--3年前删除update tablename set s=b*3 where s=10 and b=8
再设计图书管理时,表里有个借阅时间,数据类型是varchar。如果想把借阅时间超过n年的信息删除,sql语句要怎么写? --如果是算年. delete from tb where datediff(yy,借阅时间,getdate()) >=n --如果不只是算年,精确到月日. delete from tb where (datediff(yy,借阅时间,getdate())) >n or (datediff(yy,借阅时间,getdate()) = n and right(convert(varchar(10),cast(借阅时间 as datetime),120),5) < right(convert(varchar(10),getdate(),120),5) )
还有同一张表上,s的price是10 ,b的price是8, 如果要把s的price修改为b的3倍,sql语句要怎么写? --s , b是什么意思?书名?假设字段为bookname update tb set price = (select 3*price from tb where bookname = 's') where bookname = 'b'update tb set price = (select 3*price from tb where bookname = 's' and price = 8) where bookname = 'b' and price = 10
delete tb where datediff(year,borrow_date,getdate())>=4
注意,borrow_date 的数据库类型是字符串的,不是smalldatetime,
create table borrow(reader_ID numeric(3,0),book_ID numeric(10,0),borrow_date varchar(20)) insert into borrow select 112,445501,'19-3月-2006' insert into borrow select 125,332211,'12-2月-2006' insert into borrow select 111,445503,'21-8月-2006' insert into borrow select 112,112266,'14-3月-2006' insert into borrow select 114,665544,'21-10月-2006' insert into borrow select 120,114455,'02-11月-2006' insert into borrow select 120,118801,'18-10月-2006' insert into borrow select 119,446603,'12-11月-2006' insert into borrow select 112,449901,'23-10月-2006' insert into borrow select 115,449902,'21-8月-2006' insert into borrow select 118,118801,'10-9月-2006' --上面日期的全删除掉了 insert into borrow select 118,118801,'10-9月-2009' --加一个日期 go declare @n int set @n=4 set dateformat dmydelete from borrow where datediff(d,convert(datetime,replace(replace(borrow_date,'月-','/'),'-','/')),getdate())>365*@n select * from borrow /* reader_ID book_ID borrow_date --------------------------------------- --------------------------------------- -------------------- 118 118801 10-9月-2009(1 行受影响) */ go drop table borrow
更精细一点,算闰年: create table borrow(reader_ID numeric(3,0),book_ID numeric(10,0),borrow_date varchar(20)) insert into borrow select 112,445501,'19-3月-2006' insert into borrow select 125,332211,'12-2月-2006' insert into borrow select 111,445503,'21-8月-2006' insert into borrow select 112,112266,'14-3月-2006' insert into borrow select 114,665544,'21-10月-2006' insert into borrow select 120,114455,'02-11月-2006' insert into borrow select 120,118801,'18-10月-2006' insert into borrow select 119,446603,'12-11月-2006' insert into borrow select 112,449901,'23-10月-2006' insert into borrow select 115,449902,'21-8月-2006' insert into borrow select 118,118801,'10-9月-2006' --上面日期的全删除掉了 insert into borrow select 118,118801,'10-9月-2009' --加一个日期 go declare @n int set @n=4 set dateformat dmydelete from borrow where datediff(d,convert(datetime,replace(replace(borrow_date,'月-','/'),'-','/')),getdate())>(365*@n+@n/4) select * from borrow /* reader_ID book_ID borrow_date --------------------------------------- --------------------------------------- -------------------- 118 118801 10-9月-2009(1 行受影响) */ go drop table borrow
第二问有点不理解,如果s的price是10,它与b的price有何关系,只要它为10就改? update tb set price=8*3 where price=10
楼主的第2个问题是这个意思?--假设表为table1,s和b所在的字段为name update table1 set price=(select price from table1 where name='b')*3 where name='s'
set s=b*3
where s=10
set s=b*3
where date<dateadd(yy,-3,getdate())--3年前
set s=b*3
where s=10 and b=8
--如果是算年.
delete from tb where datediff(yy,借阅时间,getdate()) >=n
--如果不只是算年,精确到月日.
delete from tb where (datediff(yy,借阅时间,getdate())) >n or (datediff(yy,借阅时间,getdate()) = n and right(convert(varchar(10),cast(借阅时间 as datetime),120),5) < right(convert(varchar(10),getdate(),120),5) )
--s , b是什么意思?书名?假设字段为bookname
update tb set price = (select 3*price from tb where bookname = 's') where bookname = 'b'update tb set price = (select 3*price from tb where bookname = 's' and price = 8) where bookname = 'b' and price = 10
表名 borrow
列名 数据类型
reader_ID numeric(3,0)
book_ID numeric(10,0)
borrow_date varchar(20)reader_ID book_ID borrow_date
112 445501 19-3月-2006
125 332211 12-2月-2006
111 445503 21-8月-2006
112 112266 14-3月-2006
114 665544 21-10月-2006
120 114455 02-11月-2006
120 118801 18-10月-2006
119 446603 12-11月-2006
112 449901 23-10月-2006
115 449902 21-8月-2006
118 118801 10-9月-2006
删除借阅4年以上的信息.
如果要删除借阅n年的信息.
-
insert into borrow select 112,445501,'19-3月-2006'
insert into borrow select 125,332211,'12-2月-2006'
insert into borrow select 111,445503,'21-8月-2006'
insert into borrow select 112,112266,'14-3月-2006'
insert into borrow select 114,665544,'21-10月-2006'
insert into borrow select 120,114455,'02-11月-2006'
insert into borrow select 120,118801,'18-10月-2006'
insert into borrow select 119,446603,'12-11月-2006'
insert into borrow select 112,449901,'23-10月-2006'
insert into borrow select 115,449902,'21-8月-2006'
insert into borrow select 118,118801,'10-9月-2006' --上面日期的全删除掉了
insert into borrow select 118,118801,'10-9月-2009' --加一个日期
go
declare @n int
set @n=4
set dateformat dmydelete from borrow where datediff(d,convert(datetime,replace(replace(borrow_date,'月-','/'),'-','/')),getdate())>365*@n
select * from borrow
/*
reader_ID book_ID borrow_date
--------------------------------------- --------------------------------------- --------------------
118 118801 10-9月-2009(1 行受影响)
*/
go
drop table borrow
create table borrow(reader_ID numeric(3,0),book_ID numeric(10,0),borrow_date varchar(20))
insert into borrow select 112,445501,'19-3月-2006'
insert into borrow select 125,332211,'12-2月-2006'
insert into borrow select 111,445503,'21-8月-2006'
insert into borrow select 112,112266,'14-3月-2006'
insert into borrow select 114,665544,'21-10月-2006'
insert into borrow select 120,114455,'02-11月-2006'
insert into borrow select 120,118801,'18-10月-2006'
insert into borrow select 119,446603,'12-11月-2006'
insert into borrow select 112,449901,'23-10月-2006'
insert into borrow select 115,449902,'21-8月-2006'
insert into borrow select 118,118801,'10-9月-2006' --上面日期的全删除掉了
insert into borrow select 118,118801,'10-9月-2009' --加一个日期
go
declare @n int
set @n=4
set dateformat dmydelete from borrow where datediff(d,convert(datetime,replace(replace(borrow_date,'月-','/'),'-','/')),getdate())>(365*@n+@n/4)
select * from borrow
/*
reader_ID book_ID borrow_date
--------------------------------------- --------------------------------------- --------------------
118 118801 10-9月-2009(1 行受影响)
*/
go
drop table borrow
update tb set price=8*3 where price=10
update table1
set price=(select price from table1 where name='b')*3
where name='s'