sqlserver 语法总结

查看当前数据库中存在的临时表表名称命令

 

 

select name 
from tempdb.dbo.sysobjects 
where type='u' and name like '#%'

--查询实际表表名称

 

select name 
from syscolumns 
where id=
(
     select max(id) 
     from sysobjects 
     where xtype='u' and name='表名'
)

跨库访问sql语句

 

方法-、

 

exec sp_addlinkedserver '服务器名称','','SQLOLEDB','ip地址'
exec sp_addlinkedsrvlogin '服务器名称','false',null,'sa','数据库密码' 

 

方法二：

select *

from PlatformOneServer.dbo.PlatformOneDB.TableUser

Union ALL

select *

from PlatformTwoServer.dbo.PlatformTwoDB.TableUser

方法三、

 

select tT_Mobile 
from OPENDATASOURCE('SQLOLEDB','Data Source=ip;User ID=用户名;Password=密码').数据库名.dbo.表名 
where 字段A = 1081 
and not exists
(     
     select sS_Mobile 
     from smg_ServerBook 
     where sS_Mobile = tT_Mobile 
) 

 

 

创建主键

alter table wyf_test201006 add constraint PK_wyf_test201006_ID primary key CLUSTERED (ID)

创建check约束

alter table wyf_test201007 add constraint CK_ID_wyf_test201006 check(ID between 5001 and 10000)

创建默认值

alter table wyf_test201007 add constraint DF_Storage_M_Shelf_Default default(0) for ID

创建外键

alter table wyf_test201007 add constraint FK_Goods_Pr_GID_Storage_Go_GID foreign key(GID) references Storage_Goods(GID)

 

查询数据库的所有默认值

 

select * 
from sys.default_constraints

 

 

/*删除游标*/

deallocate result_cur 

 

sql服务重启命令

 

net stop mssqlserver
net start mssqlserver

查找现有表的约束名称

 

exec sp_helpconstraint 表名

计算列删除与添加

Alter   table   Table1   drop   column   ColumnC

Alter   table   Table1   add   ColumnC   as   ColumnA+ColumnB 

 

delete 中使用别名，其实delete时，真正起作用的是from后面的表

 

delete a
from #ta a 
where exists (select 1 from #tb b where a.id=b.id) 

 

 

sqlserver排名函数

全年段排名

 

RANK() OVER(ORDER BY sumPoint DESC)

 

按班级排名

 

RANK() OVER(PARTITION  BY classId  ORDER BY sumPoint DESC)

 

 

A.   重命名表
下例将表   customers   重命名为   custs。

EXEC   sp_rename   'customers ',   'custs ' 

B.   重命名列
下例将表   customers   中的列   contact   title   重命名为   title。

sp_rename 'HSL_TestPassStatistics.[aa]', 'bb'

 

 

在sql语句中使用sum, min,max,avg聚合函数，会自动过滤null的数据行，不会纳入统计

sql触发器查看与管理

1、通过可视化操作来管理和查看触发器
在Microsoft SQL Server Management Studio中，选中某一数据库的某一张表时，在“对象资源管理器详细”窗口中有“触发器”项。
通过“触发器”的右键菜单功能，我们可以新建触发器。如果原来的表中已经存在了触发器，通过双击“触发器”项可以查看到具体的触发器，在此处可以执行 修改、删除等操作。

2、通过查询分析器来管理和查看触发器
1）查看表中的触发器类型:
sp_helptrigger:返回对当前数据库的指定表定义的 DML 触发器的类型。sp_helptrigger 不能用于 DDL 触发器。
示例:

EXEC sp_helptrigger '表名'

 

2）查看触发器的有关信息:
sp_help:报告有关数据库对象（sys.sysobjects 兼容视图中列出的所有对象）、用户定义数据类型或某种数据类型的信息。
示例:

EXEC sp_help '触发器名'

 

3）显示触发器的定义:
sp_helptext:显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。
示例:

EXEC sp_helptext '触发器名'

 

4）查看当前库中所有的触发器:
查询脚本：

SELECT * FROM Sysobjects WHERE xtype = 'TR'

 

 

5）查看当前库中所有的触发器和与之相对应的表：
查询脚本：

SELECT tb2.name AS tableName,tb1.name AS triggerName 
FROM Sysobjects tb1 
JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id
WHERE tb1.type='TR'

 

 

mssql中的pivot 和unPivot行转列关键字使用方法

           pivot语法

 

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明，请参阅 FROM (Transact-SQL)。

以下是带批注的 PIVOT 语法。

SELECT <非透视的列>,

    [第一个透视的列] AS <列名称>,

    [第二个透视的列] AS <列名称>,

    ...

    [最后一个透视的列] AS <列名称>,

FROM

    (<生成数据的 SELECT 查询>)

    AS <源查询的别名>

PIVOT

(

    <聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

    IN ( [第一个透视的列], [第二个透视的列],

    ... [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

           pivot使用示例

 

            WITH StudentPointStandardSubjectCountCTE AS(
                SELECT StudentId, StandardName, COUNT(*) levelCount
                FROM PointStandard B,HSL_Point A
                WHERE  A.subjectId = B.SubjectId
                AND Point >= PointDown and  Point <= tP_PointUp
                GROUP BY StudentId, StandardName
            ),  
            studentPointStandardSubjectCountPivotCTE AS(
                SELECT tP_StudentId,tP_Asterisk,
                    [55001] [excellentCount],
                    [55002] [favorableCount],
                    [55004] [passCount],
                    [55005] [noPassCount],
                    [55006] [qualifedCount],
                    [55007] [noQualifedCount]
                FROM
                (
                    SELECT StudentId,levelCount,StandardName
                    FROM StudentPointStandardSubjectCountCTE                
                )AS AA
                PIVOT
                (
                    sum([levelCount])
                    FOR [StandardName]
                    IN([55001],[55002],[55004],[55005],[55006],[55007])
                )AS TableStudentPointStandardSubjectCountPivot
            )
            select *
            FROM studentPointStandardSubjectCountPivotCTE;

unpivot使用示例，引用自msdn上面的例子

 

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

 

 

 

group by 结合grouping 用法

 

1、如果希望再在分类统计中，添加汇总行，可以使用以下语句：

      Select CategoryID, SUM(UnitPrice), GROUPING(CategoryID) AS 'Grouping'

      FROM dbo.Products

      GROUP BY CategoryID

      WITH ROLLUPGrouping 

      这一列用于标识出哪一行是汇总行。它使用 ROLLUP 操作添加汇总行。

2、如果使用 WITH CUBE 将会产生一个多维分类数据集，如下：

      Select CategoryID, SupplierID, SUM(UnitPrice) AS SumPrice

      FROM dbo.Products

      GROUP BY CategoryID, SupplierID

     WITH CUBE 

    它会产生一个交叉表，产生所有可能的组合汇总。

3、使用 ROLLUP CUBE 会产生一个 NULL 空值，可以使用以下语法解决，如下：

      Select CASE WHEN (GROUPING(SupplierID) = 1) THEN '-1' ELSE SupplierID END AS SupplierID, SUM(UnitPrice) AS QtySum

      FROM dbo.Products

     GROUP BY SupplierID

     WITH CUBE

    它首先检查当前行是否为汇总行，如果是就可以设置一个值，这里设置为 '-1' 。

4、使用grouping 进行总计、合计、小计的sql写法示例

 

 SELECT Groups=CASE 
        WHEN GROUPING(Color)=0 THEN Groups
        WHEN GROUPING(Groups)=1 THEN '总计'
        ELSE '' END,
    Item=CASE 
        WHEN GROUPING(Color)=0 THEN Item
        WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
        ELSE '' END,
    Color=CASE 
        WHEN GROUPING(Color)=0 THEN Color
        WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
        ELSE '' END,
    Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP

sql临时表生命周期

 

1、局部临时表（#开头）只对当前连接有效，当前连接断开时自动删除。   
2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。   
3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop   table   #Tmp(或者drop   table   ##Tmp)来显式删除临时表。   

 

 

在sql server 2008上查询缓存点击率

 

SELECT cntr_value 
FROM sys.dm_os_performance_counters  
where counter_name = 'Buffer cache hit ratio'

 

bcp命令

触发器禁用

/* 触发器固然能禁用触发器，但不能区分用户 */

ALTER   TABLE   表名   DISABLE   TRIGGER  triggerName

 

查看数据表索引

第一种方式

 

select * from sysindexes where  id = object_id('tablename')

select * from sysindexes where indid>=1 and indid<>255 and name not like '_WA_Sys_%' AND id=OBJECT_ID(表)

 

 

第二种

 

sp_helpindex HSL_SumPoint

len()表示字符长度

datalength()字节长度

SQLSERVER分区函数示例

 

 

create PARTITION FUNCTION [PartionFunction](int)
AS RANGE LEFT FOR VALUES ( 1,1111,2111)
go
CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO
([db1_fg_00], [db1_fg_00], [db1_fg_00], [PRIMARY])

CREATE TABLE tb1 (id INT PRIMARY KEY, NAME NVARCHAR(1000)) ON [PartionStruct](id)

 

-- 删除分区方案和分区函数 

DROP PARTITION SCHEME HitDateRangeScheme  

DROP PARTITION FUNCTION HitDateRange

openRowSet () 竟然不支持参数传递文件路径，只能采用动态sql执行

查看当前数据库的数据文件和日志文件

sp_helpfile

mssqlServer性能查看工具

select * from Sys.dm_exec_requests

查看当前数据库正在执行和等待执行的sql语句

select * from sys.dm_exec_requests   er cross apply  sys.dm_exec_sql_text(er.sql_handle)

解决代理计划中作业无法删除问题
1. 在msdb中先执行下面的语句将维护计划的ID查出。
select * from sysmaintplan_plans

2. 将查出的ID填入到下面几句话中的''中，并执行。
delete from sysmaintplan_log where plan_id = ''

delete from sysmaintplan_subplans where plan_id = ''

delete from sysmaintplan_plans where id = ''

3. 在Sql Agent中删除相应的Job(维护计划会自动建立相应的Job).

DBCC CHECKDB(N'databaseName') 检查数据库完整一致性

dbcc updateusage(0) : 报告目录视图中的页数和行数错误并进行更正

4、查看sqlserver数据库日志大小和占用空间

DBCC SQLPERF(LOGSPACE)

日志应该采用线性增长方式，最好是预估未来的日志大小，直接设置空间大小

 

 

恢复模式：将恢复模式设定为True意味着让SQL自动截去tempdb的日志文件（在使用了每个表格之后），要找出tempdb所使用的恢复模式，可以使用如下命令：

SELECT DATABASEPROPERTYEX('tempdb','recovery')

恢复模式有三种选择：简单、完整或大量记录（bulk-logged），如要改变设置，可以使用以下命令：

ALTER DATABASE tempdb SET RECOVERY SIMPLE

查询数据库当前最耗资源的10个查询语句

SELECT TOP 10
total_worker_time/execution_count AS [Avg CPU Time],
(
     SELECT 
         SUBSTRING(text,statement_start_offset/2,
         (CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) 
     FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC

 

sqlserver2008的内存对象

 

SELECT SUM (pages_allocated_count * page_size_in_bytes) as 'Bytes Used', type 
FROM sys.dm_os_memory_objects
GROUP BY type 
ORDER BY 1 DESC;
GO

sqlserver2008查看当前访问数据库连接数

 

SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT
   [DBID]
FROM
   [Master].[dbo].[SYSDATABASES]
WHERE
   NAME='databaseName'
)

 

 

select * 
from sysprocesses 
where dbid in (select dbid from sysdatabases where name='databaseName')

在sqlserver中插入或update可以直接output值很神奇，做一下记录

 

DECLARE  @table table(keyvalue int) 
UPDATE TableMaxId
SET tT_MaxId = tT_MaxId + 1
OUTPUT inserted.tT_MaxId 
into @table(keyvalue)
WHERE tT_Name='HSL_ClassType'