sql server 实用sql语句整理（暂未完整分类）

 目录

一、表相关

1、复制表（源表名：a，新表名：b）

1）仅复制表结构

方法1：

select * into b from a where 1<>1;

方法2：

select top 0 * into b from a

2）拷贝表数据

<1>同一个数据库

insert into b(a, b, c) select d, e, f from b;

<2>跨数据库之间表的拷贝(具体数据使用绝对路径)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

3）初始化表

TRUNCATE TABLE table1

二、数据操作

1、排序

1）按姓氏笔画排序

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2、分页

方法1：

select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名 As tab) As t

方法2：

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

3、系统查询操作

1）获取当前数据库中的所有用户表

select * from sysobjects where xtype='U' and category=0

2）获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

3）查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

4）查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

5）查询用户创建的所有数据库

方法1：

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

方法2：

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

6）查询某一个表的字段和数据类型

select column_name,data_typefrom information_schema.columnswhere table_name='表名'

7)计算执行SQL语句查询时间

declare @d datetime

set @d=getdate()

select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

8)计算一个库里各个表的记录总数

select b.name,a.rowcntfrom sysindexes a,sysobjects bwhere a.id=b.idand a.indid<2and b.xtype='u'

9)统计数据库里每个表的详细情况

EXEC sp_MSforeachtable@command1="sp_spaceused'?'"

10)获得每个表的记录数和容量

 EXEC sp_MSforeachtable@command1="print'?'",@command2="sp_spaceused'?'",@command3= "SELECTcount(*)FROM ? "

4、数据处理

1）日期、时间

DECLARE @dt datetime

SET@dt=GETDATE()

DECLARE @number

intSET@number=3

<1>指定日期该年的第一天或最后一天

A) 年的第一天

SELECTCONVERT(char(5),@dt,120)+'1-1'

B) 年的最后一天

SELECTCONVERT(char(5),@dt,120)+'12-31'

<2>指定日期所在季度的第一天或最后一天

A) 季度的第一天

SELECTCONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt)-2,@dt),120)+'1')

B.)季度的最后一天（CASE判断法）

SELECTCONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt),@dt),120)+CASEWHENDATEPART(Quarter,@dt)in(1,4)THEN'31'ELSE'30'END)

C.)季度的最后一天（直接推算法）

SELECTDATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,@dt)*3-Month(@dt),@dt),120)+'1')

<3>指定日期所在月份的第一天或最后一天

A) 月的第一天

SELECTCONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

B) 月的最后一天

SELECTDATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

C.)月的最后一天（容易使用的错误方法）

SELECTDATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

<4>指定日期所在周的任意一天

SELECTDATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

<5>指定日期所在周的任意星期几

A)星期天做为一周的第1天

SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

B) 星期一做为一周的第1天

SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)

5、范围

<1>between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1

 6、关联

1)四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

7、查询

1）包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

2）随机取出10条数据
select top 10 * from tablename order by newid()

3）选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

8、删除

1）删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

三、动态sql语句

1、普通SQL语句可以用exec执行

Select *from tableName

exec('select * from tableName')

exec sp_executesql N'select * from tableName'-- 请注意字符串前一定要加N

2、字段名，表名，数据库名之类作为变量时，必须用动态SQL

方法1:

declare @fname varchar(20)

set @fname='FiledName'

Select @fnamefrom tableName-- 错误,不会提示错误，但结果为固定值FiledName,并非所要。

exec('select'+@fname+ ' from tableName')-- 请注意 加号前后的 单引号的边上加空格

fangfa 2:将字符串改成变量的形式

declare @fname varchar(20)

set @fname='FiledName'--设置字段名

declare@svarchar(1000)

set @s='select'+@fname+ ' from tableName'

exec(@s)

四、数据库操作

1. 查看数据库的版本
select @@version

2. 查看数据库所在机器操作系统参数
exec master..xp_msver

3. 查看数据库启动的参数
sp_configure

4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1

查看数据库服务器名和实例名
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb

重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'

6. 查看所有数据库用户登录信息
sp_helplogins

查看所有数据库用户所属的角色信息
sp_helpsrvrolemember

修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程

更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'

注意: 更改对象名的任一部分都可能破坏脚本和存储过程。

把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本

7. 查看链接服务器
sp_helplinkedsrvlogin

查看远端数据库用户登录信息
sp_helpremotelogin

8.查看某数据库下某个数据对象的大小
sp_spaceused @objname

还可以用sp_toptables过程看最大的N(默认为50)个表

查看某数据库下某个数据对象的索引信息
sp_helpindex @objname

还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname

clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname

9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name'

查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'

创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数

解密加密过的存储过程和函数可以用sp_decrypt过程

10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock

进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()

推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3

检查死锁用sp_who_lock过程
sp_who_lock

11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)

12.分析SQL Server SQL 语句的方法:

set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划

在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形

文本方式显示查询执行计划
set showplan_all {on | off}

set showplan_text { on | off }
set statistics profile { on | off }

13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法

先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作

alter database [@error_database_name] set single_user

修复出现不一致错误的表

dbcc checktable('@error_table_name',repair_allow_data_loss)

或者可惜选择修复出现不一致错误的小型数据库名

dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，
以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。
修复完成后，请备份数据库。
repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。
这些修复可以很快完成，并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。
执行这些修复时不会有丢失数据的危险。