|
--use tables of another database
select * from master.dbo.sysmessages
select * from master..sysmessages
--create a new table which has another table‘s structure
select * into target_table from source_table where 0=1
--to see a table or procedure existing or not
IF exists(select name from sysobjects where name=‘tablename‘ and type=‘U‘)
--
insert into target_table select * from source_table
--define output parameter and set default value
@affect_rows integer=0 output
--use procedure
create procedure up_test
@out_val integer=0 output
as
begin
set @out_val=1
return 9;
end
declare @ret_val int
declare @output_val int
exec @ret_val=up_test @out_val=@output_val output
select @ret_val,@output_val
--use transaction
begin tran
delete from tablename
if @@error<>0
begin
rollback tran
else
commit tran
--define cursor
declare cur_tablename cursor FORWARD_ONLY | SCROLL
for select * from tablename
for update [OF column_name [,...n]]
update tablename set column_name=‘r‘ where current of cur_tablename
--format string ‘050330‘ as ‘2005-03-30‘
select ‘20‘+SubString(‘050330‘,1,2) +‘-‘+ SubString(‘050330‘,3,2) +‘-‘+ SubString(‘050330‘,5,2)
--字段AA為字符型﹐它的內容為X+Y+Z。其中X和Z都為任意個字母字符﹐Y為任意個數字字符
--現我要得到X+Y﹐即去掉后而的Z。for example:‘BR0021GD ‘-->‘BR0021‘
reverse(substring(reverse(AA),patindex(‘%[^0-9]%‘,reverse(AA)),len(AA))
replace(AA,right(AA,patindex(‘%[0-9]%‘,reverse(AA))),‘‘)
--using variable as tablename(partly or fully)
declare @tablename varchar(20)
set @tablename=‘ment‘
if EXISTS (SELECT name FROM sysobjects WHERE name = ‘depart‘+@tablename AND type = ‘U‘)
begin
print ‘table department exists‘
exec(‘selcect * from depart‘+@tablename)
end
else
print ‘table department donot exists‘
--显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate
from table where table.title=a.title) b
--一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
select d_id,count(*) from department group by d_id WITH ROLLUP
select d_id, Count(*) from department group by d_id compute count(d_id)
--add check constraint
alter table people add constraint ccme_people_sex
check (p_sex=‘m‘ or p_sex=‘w‘);
alter table people add constraint ccme_people_birthday
check(p_birthday is null or p_birthday like ‘[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]‘);
alter table people with nocheck
add constraint cfme_people_dept foreign key(p_dept) references department(d_id);
--use case
select p_familyname+p_firstname as ‘姓名‘,
case p_sex when ‘m‘ then ‘男‘ when ‘w‘ then ‘女‘ end as ‘性别‘ from tablename
--import_date is smalldatetime,type as yyyy-mm-dd
convert(varchar(10),import_date,20)
convert(varchar(10),import_date,121)
|
