Home >Database >Mysql Tutorial >SqlServer 查询语句

SqlServer 查询语句

WBOY
WBOYOriginal
2016-06-07 15:13:491673browse

SqlServer中查询语句 ? --ChildID,Name为该临时表的列名SELECT ChildID , Value AS NameINTO #pinYinFROM dbo.SpotWeb WHERE Name = '拼音' AND ParentId = 'f7cf01f4-76fa-4e8d-b715-05727371f3ae' ? IF OBJECT_ID('tempdb..#temp') is NOT NULL DROP TABLE

SqlServer中查询语句

?

--ChildID,Name为该临时表的列名
SELECT ChildID , Value AS Name
INTO #pinYin
FROM dbo.SpotWeb  
WHERE Name = '拼音' 
	  AND ParentId = 'f7cf01f4-76fa-4e8d-b715-05727371f3ae'
?
IF OBJECT_ID('tempdb..#temp') is NOT NULL
   DROP TABLE #temp;
--查询是否存在有#temp临时表,如存在,将其删除

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE name = 'temp')
DROP TABLE temp;
--查询是否存在有表temp,如果有,则将其删除
?

?

EXEC sys.sp_who @loginame = 'sa'
--查看'sa'用户登陆的数据库的用户和进程 

EXEC sys.sp_depends @objname = 'dbo.Audit_Stat' -- nvarchar(776)
--查看该表相关的依赖信息

EXEC sys.sp_help @objname = 'dbo.Audit_Stat' -- nvarchar(776)
--查看该对象的所有信息

EXEC sys.sp_helpindex @objname = 'dbo.Audit_Stat' -- nvarchar(776)
--查看该对象所对应的索引信息

EXEC sys.sp_helptext @objname = 'dbo.qry_Audit'
--查看该存储过程对应的信息

EXEC sys.sp_password @old = NULL, -- sysname
    @new = NULL, -- sysname
    @loginame = NULL -- sysname
--修改数据的密码信息
?

?

SET IDENTITY_INSERT  tableName OFF|ON
--允许将显示值插入到标识符字段
SET NOCOUNT OFF|ON
--允许返回影响行数
SET ROWCOUNT OFF|ON
--在返回指定的行数之后停止处理查询。
?
@@FETCH_STATUS
--返回被 FETCH 语句执行的最后游标的状态,
--而不是任何当前被连接打开的游标的状态。
--0表示FETCH 语句成功
--可以用于循环取游标中数据

?

?

PRINT @@ERROR
--无错时,返回为0
--有错时,返回该错误代码
?
SELECT * INTO #temp FROM dbo.Audit_Stat WHERE AuditItem = '020' ;
PRINT @@IDENTITY;
DROP TABLE #temp;
--@@IDENTITY;保存系统中最后修改的标识符
?
DECLARE
@Item VARCHAR(10)
SET @Item = '020';
SELECT AuditItem, Date, Obj, ObjType, Count INTO #temp FROM dbo.Audit_Stat WHERE AuditItem = @Item;
--在创建时,对该游标时时进行赋值
--DECLARE temp_cursor CURSOR FOR SELECT * FROM #temp;
  DECLARE @temp_cursor CURSOR;
--创建时不指定游标的范围时是全局的
--SET @temp_cursor = CURSOR FOR SELECT * FROM #temp;
--创建时FORWARD_ONLY,游标移动时只能FETCH NEXT
--SET @temp_cursor = CURSOR LOCAL FORWARD_ONLY FOR SELECT * FROM #temp;
--创建时SCROLL,游标可以任意移动  
  SET @temp_cursor = CURSOR LOCAL SCROLL FOR SELECT * FROM #temp;
  SET @temp_cursor = CURSOR LOCAL  FOR SELECT * FROM #temp;
  OPEN @temp_cursor;
  DECLARE @itemCode VARCHAR(10);
  DECLARE @theDate common.DT_Date;
  DECLARE @obj VARCHAR(10);
  DECLARE @objType VARCHAR(10);
  DECLARE @theCount INT ;
  FETCH NEXT FROM @temp_cursor INTO  @itemCode, @theDate, @obj, @objType, @theCount 
  UPDATE #temp SET Count = 100 WHERE CURRENT OF @temp_cursor;
  SELECT * FROM #temp;
  CLOSE @temp_cursor;
  DEALLOCATE @temp_cursor;
DROP TABLE #temp;
??
TRUNCATE TABLE #temp;
--Truncate删除比Delete快,
--但删除后不能RollBack
--Delete是物理-次一行删除
--Truncate释放所有与表关联列
?
SELECT TOP 2 * 
FROM dbo.Audit_Stat 
WHERE AuditItem = '010'
UNION ALL
SELECT TOP 2 * 
FROM  dbo.Audit_Stat 
WHERE AuditItem = '010'
--UNION,UNION ALL
--可以将查询出的结果进行联合,但必须注意的是,
--查询的结果对应结构相同,对应的类型兼容
--UNION:对于查询的结果,如果相同,则合并
--UNION ALL: 对应查询的结果,直接联接,不合并
?
SELECT AuditItem, SUM(Count) sum 
FROM dbo.Audit_Stat 
GROUP BY AuditItem
HAVING SUM(Count) > 1000;
--HAVING可对分组后的集合进行过滤
?
SELECT AuditItem, SUM(Count) FROM dbo.Audit_Stat WHERE AuditItem = '030'
GROUP BY ALL AuditItem;
--注意Where语句的添加后,对于all更有对比性
--不加all时,仅显示AuditItem = '030'的Code和对应的和
--加上all时,显示所有的AuditItem,但对应的和为空
?
SELECT * FROM dbo.Audit_Stat WHERE AuditItem LIKE '0[^0-2]0';
--%匹配任意个字符
--_匹配单个字符
--[0-9]匹配该范围中的单个字符
--[^0-9]匹配不在该范围中的单个字符

?

SELECT * FROM dbo.Audit_Stat 
WHERE Count BETWEEN 1 AND 2 ;
--Between N1 and N2 是[N1,N2],包含两边界
--Not Between N1 and N2 是 < N1 and > N2意思
??
SELECT IDENTITY(INT,100,1) AS '序号', Count, AuditItem 
INTO #temp 
FROM dbo.Audit_Stat
--创建使用IDENTITY属性的新列,
--直接创建#temp临时表,并将数据插入到该表中
??
SELECT  TOP 3 AuditItem, SUM(Count) FROM dbo.Audit_Stat  GROUP BY AuditItem ORDER BY AuditItem;
--Top的应用
--查询结果中取前面3行

?

DECLARE @ItemCode VARCHAR(10);
SELECT @ItemCode = AuditItem FROM dbo.Audit_Stat WHERE ID = -1;
--查询时赋值,当有多个值是将最后查询值赋入当前声明变量中

?

--写法1
SELECT  
	CASE count 
	WHEN 1 THEN 'YES' 
	WHEN 2 THEN 'NO' 
	ELSE 'OTHER' END TEST, * 
FROM dbo.Audit_Stat;

--写法2
SELECT 
	CASE
	WHEN COUNT = 1 THEN 'YES'
	WHEN COUNT = 2 THEN 'NO'
	ELSE  'OTHER' END  TEST, *
FROM dbo.Audit_Stat;

--注意其中case判断的结果必须为同一类型
--例:'YES', 'NO', 'OTHER'均为字符型
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn