-- 是否可以定义一个函数
-- 将作者编号作为参数统计其作品数量并将其返回
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
-- 根据给定的作者编号获取其相应的作品数量
create function GetTitleCountByAuID(@au_id varchar(12))
returns int
begin
return (select count(title_id)
from titleauthor
where au_id = @au_id)
end
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
declare @title varchar(80)
declare @price numeric(9,4)
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
fetch cur_titles into @title_temp, @price_temp
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)
创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
set @list = ''
--set @sql = ''
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='tb_user_msg'
set @sql='select '+right(@list,len(@list)-1)+' from tb_user_msg'
-- print @sql
exec sp_ExecuteSql @sql
4.查看硬盘分区:
EXEC master..xp_fixeddrives
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR Select 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
Where program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
10：获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
SQL字符函数：
返回字符串中从左边开始指定个数的字符。LEFT ( character_expression , integer_expression )
Select LEFT(Name, 5) FROM Production.Product orDER BY ProductID;