有用的SQL 语句(不断更新)
1.WAITFOR
--WAITFOR
--例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay '01:02:03'
select * from employee
--例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time '23:08:00'
select * from employee
2.查看sqlserver版本
select serverproperty('productversion'),serverproperty('productlevel'),serverproperty('edition')
3.在SQLSERVER,简单的组合sp_spaceused和sp_MSforeachtable这两个存储过程,可以方便的统计出用户数据表的大小,包括记录总数和空间占用情况,非常实用,在SqlServer2K和SqlServer2005中都测试通过。
/**//*
1. exec sp_spaceused '表名' (SQL统计数据,大量事务操作后可能不准)
2. exec sp_spaceused '表名', true (更新表的空间大小,准确的表空大小,但可能会花些统计时间)
3. exec sp_spaceused (数据库大小查询)
4. exec sp_MSforeachtable "exec sp_spaceused '?'" (所有用户表空间表小,SQL统计数据,,大量事务操作后可能不准)
5. exec sp_MSforeachtable "exec sp_spaceused '?',true" (所有用户表空间表小,大数据库慎用)
*/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"
select * from #t
drop table #t
4.从表中随即抽取N个数据
SELECT TOP 10 *
FROM LargeTable
ORDER BY NEWID()
5.SQL语句附加数据库
use master
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
6.数据库表间数据复制
--1.表结构相同的表,且在同一数据库(如,table1,table2)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Sql :insert into table1 select * from table2 (完全复制)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert into table1 select distinct * from table2(不复制重复纪录)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert into table1 select top 5 * from table2 (前五条纪录)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--2.不在同一数据库中(如,db1 table1,db2 table2)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sql: insert into db1..table1 select * from db2..table2 (完全复制)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert into db1..table1 select distinct * from db2table2(不复制重复纪录)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert into tdb1..able1 select top 5 * from db2table2 (前五条纪录)
7.按姓氏笔画排序
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
8.多条件查询
多条件查询在实际应用中用的很多,在一些页面上会要求根据时间,类别,或是其他的一些关键字来查询数据,而且这些条件并不是一定要输入的。以前有过在C#代码中根据条件选中的情况来拼where条件字串,也有过在存储过程中用if来判断传进的参数,但是后来知道早存储过程中使用OR是最简单的,如下:
create Table Users
(
id int,
name nvarchar(20),
age int
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create proc sp_SGetUserInfo
(
@Name nvarchar(20),
@Age int
)
as
select
*
from
Users
where
(@Name='' or name=@Name)
and
(@Age='' or age=@Age)
9.sqlserver中取随机数的两种方法
a.创建一个表Rand,字段是:RandomNum ,存储0到9的数据。
使用下面SQL语句可产生随机数:
select top 1 RandomNum from Rand order by NewID()
b.使用sqlserver提供的Rand()函数
select cast( floor(rand()*N) as int)
--产生0到N-1之间的随机数
select cast(ceiling(rand() * N) as int)
--产生1到N之间的随机数
10.在数据库中几个常用的日期格式转换
日期格式转换也是经常用到的,通常2008-04-12和20080412 这两种格式用的比较多,下面列出一些常用的:
CONVERT(VARCHAR(10),GETDATE(),120)
--返回2008-04-12
CONVERT(VARCHAR(10),GETDATE(),20)
--返回2008-04-12
CONVERT(VARCHAR(10),GETDATE(),112)
--返回20080412
CONVERT(VARCHAR(10),GETDATE(),111)
--返回2008/04/12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2.查看sqlserver版本
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3.在SQLSERVER,简单的组合sp_spaceused和sp_MSforeachtable这两个存储过程,可以方便的统计出用户数据表的大小,包括记录总数和空间占用情况,非常实用,在SqlServer2K和SqlServer2005中都测试通过。
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4.从表中随即抽取N个数据
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5.SQL语句附加数据库
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6.数据库表间数据复制
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7.按姓氏笔画排序
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8.多条件查询
多条件查询在实际应用中用的很多,在一些页面上会要求根据时间,类别,或是其他的一些关键字来查询数据,而且这些条件并不是一定要输入的。以前有过在C#代码中根据条件选中的情况来拼where条件字串,也有过在存储过程中用if来判断传进的参数,但是后来知道早存储过程中使用OR是最简单的,如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9.sqlserver中取随机数的两种方法
a.创建一个表Rand,字段是:RandomNum ,存储0到9的数据。
使用下面SQL语句可产生随机数:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10.在数据库中几个常用的日期格式转换
日期格式转换也是经常用到的,通常2008-04-12和20080412 这两种格式用的比较多,下面列出一些常用的:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
//****************************************
by: Amen cnblogs博客 转载请注明出处
//****************************************
by: Amen cnblogs博客 转载请注明出处
//****************************************