經(jīng)典SQL語句--收藏
[個(gè)人收藏]經(jīng)典SQL語句.值得收藏
精典的SQL語句，推薦收藏
在網(wǎng)上經(jīng)常轉(zhuǎn),常常看到有些人為了求得某些SQL語句而焦頭爛額,現(xiàn)在我特別把自己收藏的一些比較精典的SQL拿出來和大家分享一下
1. 行列轉(zhuǎn)換--普通
假設(shè)有張學(xué)生成績表(CJ)如下
Name ? Subject ? Result
張三 ? 語文 ? ? 80
張三 ? 數(shù)學(xué) ? ? 90
張三 ? 物理 ? ? 85
李四 ? 語文 ? ? 85
李四 ? 數(shù)學(xué) ? ? 92
李四 ? 物理 ? ? 82
想變成 ?
姓名 ? 語文 ? 數(shù)學(xué) ? 物理
張三 ? 80 ? 90 ? 85
李四 ? 85 ? 92 ? 82
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)
2. 行列轉(zhuǎn)換--合并
有表A,
id pid
1 ? 1
1 ? 2
1 ? 3
2 ? 1
2 ? 2
3 ? 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
創(chuàng)建一個(gè)合并的函數(shù)
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
--調(diào)用自定義函數(shù)得到結(jié)果
select distinct id,dbo.fmerg(id) from 表A
3. 如何取得一個(gè)數(shù)據(jù)表的所有列名
方法如下：先從SYSTEMOBJECT系統(tǒng)表中取得數(shù)據(jù)表的SYSTEMID,然后再SYSCOLUMN表中取得該數(shù)據(jù)表的所有列名。
SQL語句如下：
declare @objid int,@objname char(40)
set @objname = ''tablename''
select @objid = id from sysobjects where id = object_id(@objname)
select ''Column_name'' = name from syscolumns where id = @objid order by colid
是不是太簡單了？ 呵呵 不過經(jīng)常用阿.
4. 通過SQL語句來更改用戶的密碼
修改別人的,需要sysadmin role ?
EXEC sp_password NULL, ''newpassword'', ''User''
如果帳號(hào)為SA執(zhí)行EXEC sp_password NULL, ''newpassword'', sa
5. 怎么判斷出一個(gè)表的哪些字段不允許為空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=''NO'' and TABLE_NAME=tablename
6. 如何在數(shù)據(jù)庫里找到含有相同字段的表？
a. 查已知列名的情況
SELECT b.name as TableName,a.name as columnname
From syscolumns ? a INNER JOIN ? sysobjects b ?
ON a.id=b.id ?
AND b.type=''U'' ?
AND a.name=''你的字段名字''
b. 未知列名查所有在不同表出現(xiàn)過的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
? And o.type = ''U''
? And Exists (
? ? Select 1 From syscolumns s2 ?
? ? Where s1.name = s2.name ?
? ? And s1.id <> s2.id
? ? )
7. 查詢第xxx行數(shù)據(jù)
假設(shè)id是主鍵：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游標(biāo)也是可以的
fetch absolute [number] from [cursor_name]
行數(shù)為絕對(duì)行數(shù)
8. SQL Server日期計(jì)算
a. 一個(gè)月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上個(gè)月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一個(gè)星期一
select DATEADD(wk, DATEDIFF(wk,0, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ? dateadd(dd,6-datepart(day,getdate()),getdate()) ? ?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ), 0) ? ?
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
-----------------------------------------------------------------------
1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.數(shù)據(jù)庫加密:
select encrypt(''原始密碼'')
select pwdencrypt(''原始密碼'')
select pwdcompare(''原始密碼'',''加密后密碼'') = 1--相同；否則不相同 encrypt(''原始密碼'')
select pwdencrypt(''原始密碼'')
select pwdcompare(''原始密碼'',''加密后密碼'') = 1--相同；否則不相同
3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+'',''+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=''表A''
set @sql=''select ''+right(@list,len(@list)-1)+'' from 表A''
exec (@sql)
4.查看硬盤分區(qū):
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.殺掉所有的事件探察器進(jìn)程:
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 ''?''
7.記錄搜索:
開頭到N條記錄
Select Top N * From 表
-------------------------------
N到M條記錄(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到結(jié)尾記錄
Select Top N * From 表 Order by ID Desc
8.如何修改數(shù)據(jù)庫的名稱:
sp_renamedb ''old_name'', ''new_name''
9：獲取當(dāng)前數(shù)據(jù)庫中的所有用戶表
select Name from sysobjects where xtype=''u'' and status>=0
10：獲取某一個(gè)表的所有字段
select name from syscolumns where id=object_id(''表名'')
11：查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ''%表名%''
12：查看當(dāng)前數(shù)據(jù)庫中所有存儲(chǔ)過程
select name as 存儲(chǔ)過程名稱 from sysobjects where xtype=''P''
13：查詢用戶創(chuàng)建的所有數(shù)據(jù)庫
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=''sa'')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14：查詢某一個(gè)表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = ''表名''
[n].[標(biāo)題]:
Select * From TableName Order By CustomerName
[n].[標(biāo)題]:
Select * From TableName Order By CustomerName