经典SQL语句--收藏
[个人收藏]经典SQL语句.值得收藏
精典的SQL语句，推荐收藏
在网上经常转,常常看到有些人为了求得某些SQL语句而焦头烂额,现在我特别把自己收藏的一些比较精典的SQL拿出来和大家分享一下
1. 行列转换--普通
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 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. 行列转换--合并
有表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
创建一个合并的函数
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
3. 如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
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
是不是太简单了？ 呵呵 不过经常用阿.
4. 通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
EXEC sp_password NULL, ''newpassword'', ''User''
如果帐号为SA执行EXEC sp_password NULL, ''newpassword'', sa
5. 怎么判断出一个表的哪些字段不允许为空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=''NO'' and TABLE_NAME=tablename
6. 如何在数据库里找到含有相同字段的表？
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. 未知列名查所有在不同表出现过的列名
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行数据
假设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)
如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数
8. SQL Server日期计算
a. 一个月的第一天
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. 上个月的最后一天
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. 本月的第一个星期一
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.数据库加密:
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.查看硬盘分区:
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 ''?''
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到结尾记录
Select Top N * From 表 Order by ID Desc
8.如何修改数据库的名称:
sp_renamedb ''old_name'', ''new_name''
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype=''u'' and status>=0
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 ''%表名%''
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype=''P''
13：查询用户创建的所有数据库
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：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ''表名''
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
Select * From TableName Order By CustomerName
SQL中CONVERT转化函数的用法
CONVERT的使用方法:
////////////////////////////////////////////////////////////////////////////////////////
格式:
CONVERT(data_type,expression[,style])
说明:
此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)
相互转换的时候才用到.
例子:
SELECT CONVERT(varchar(30),getdate(),101) now
结果为
now
---------------------------------------
09/15/2001
/////////////////////////////////////////////////////////////////////////////////////
style数字在转换时间时的含义如下
-------------------------------------------------------------------------------------------------
Style(2位表示年份) | Style(4位表示年份) | 输入输出格式
-------------------------------------------------------------------------------------------------
- | 0 or 100 | mon dd yyyy hh:miAM(或PM)
-------------------------------------------------------------------------------------------------
1 | 101 | mm/dd/yy
-------------------------------------------------------------------------------------------------
2 | 102 | yy-mm-dd
-------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy
-------------------------------------------------------------------------------------------------
4 | 104 | dd-mm-yy
-------------------------------------------------------------------------------------------------
5 | 105 | dd-mm-yy
-------------------------------------------------------------------------------------------------
6 | 106 | dd mon yy
-------------------------------------------------------------------------------------------------
7 | 107 | mon dd,yy
-------------------------------------------------------------------------------------------------
8 | 108 | hh:mm:ss
-------------------------------------------------------------------------------------------------
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
-------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy
-------------------------------------------------------------------------------------------------
11 | 111 | yy/mm/dd
-------------------------------------------------------------------------------------------------
12 | 112 | yymmdd
-------------------------------------------------------------------------------------------------
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
14 | 114 | hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小时制)
-------------------------------------------------------------------------------------------------
- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
concat方法的结果等于:result = string1 + string2 + string3 + … + stringN