SQLServer T-SQL 部分查询语句归纳

数据库/表操作

获取所有用户名:

Select name FROM Sysusers where status='2' and islogin='1'

• islogin=’1’ :表示帐户
• islogin=’0’ :表示角色
• status=’2’ :表示用户帐户
• status=’0’ :表示糸统帐户

获取所有数据库名:

Select Name FROM Master..SysDatabases orDER BY Name

获取所有表名:

Select Name FROM DatabaseName..SysObjects Where XType='U' orDER BY Name  
SELECT TABLE_NAME FROM dbName.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
-- dbName 数据库名称

• XType=’U’ :表示所有用户表;
• XType=’S’ :表示所有系统表;

获取所有字段名:

Select Name FROM SysColumns Where id=Object_Id('表名')

查询表的字段定义:

select * from information_schema.columns where table_name = '表名';

获取数据库所有类型:

select name from systypes

获取主键字段:

Select  name FROM SysColumns Where id=Object_Id('表名') and colid=(select top 1 keyno from sysindexkeys where id=Object_Id('表名'))

数据库多表查询

Join连接

SQL JOIN 用于把来自两个或多个表的行结合起来。

SQL JOIN 类型：

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

INNER JOIN 内连接

inner-join

SELECT testOrders.OrderID, Customers.CustomerName, testOrders.OrderDate
FROM testOrders
INNER JOIN Customers
ON testOrders.CustomerID=Customers.CustomerID;

通过某一约束条件 （ON table.XXX = table2.XXX） 进行关联，如果表中有至少一个匹配，则返回行，输出查询的字段。

SQL左链接

LEFT JOIN关键字返回左表（表1）中的所有行，即使在右表（表2）中没有匹配。如果在正确的表中没有匹配，结果是NULL。

left-join

SELECT Customers.CustomerName, testOrders.OrderID
FROM Customers
LEFT JOIN testOrders 
ON Customers.CustomerID = testOrders.CustomerID

LEFT JOIN 关键字通过某一条件 （ON Customers.CustomerID = testOrders.CustomerID）返回左表（Customers）中的所有行，即使在右边表（testOrders）中没有匹配。

RIGHT JOIN 右连接

SQL右链接 RIGHT JOIN 关键字返回右表（table2）的所有行，即使在左表（table1）上没有匹配。如果左表没有匹配，则结果为NULL。

RIGHT-join

SELECT testOrders.OrderId, testOrders.OrderDate, Customers.CustomerName
FROM testOrders
RIGHT JOIN Customers
ON Customers.CustomerID = testOrders.CustomerID

RIGHT JOIN 关键字返回右表（Employees）的所有行，即使在左表（Orders）中没有匹配。

FULL OUTER JOIN 完整外部连接

当左（表1）或右（表2）表记录匹配时，FULL OUTER JOIN关键字将返回所有记录。

full-join

SELECT * FROM testOrders;
SELECT * FROM Customers;

SELECT Customers.CustomerName, testOrders.OrderID
FROM Customers
FULL OUTER JOIN testOrders ON Customers.CustomerID=testOrders.CustomerID
ORDER BY Customers.CustomerName;

FULL OUTER JOIN关键字返回左表（Customers）中的所有行，以及右表（testOrders）中的所有行。如果 “Customers”中的行中没有”testOrders”中的匹配项，或者”testOrders”中的行中没有 “Customers”中的匹配项，那么这些行也会列出(没有的字段为NULL)。

Self JOIN自连接

自联接是一种常规联接，但表本身是连接的。

self-join

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.Country
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.Country = B.Country 
ORDER BY A.Country;

匹配出同一个国家的公司。

SQL 索引

索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。 索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度**。 索引的创建与删除不会对表中的数据产生影响。

CREATE INDEX 命令：

CREATE INDEX命令的基本语法如下：

CREATE INDEX index_name ON table_name;

单列索引：

单列索引基于单一的字段创建，其基本语法如下所示：

CREATE INDEX index_name
ON table_name (column_name);

唯一索引：

唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值。其基本语法如下所示：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

聚簇索引：

聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下所示：

CREATE INDEX index_name
on table_name (column1, column2);

创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。

如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。

隐式索引：

隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。

DROP INDEX 命令：

索引可以用 SQL DROP 命令删除。删除索引时应当特别小心，数据库的性能可能会因此而降低或者提高。

其基本语法如下：

DROP INDEX table_name.index_name;

什么时候应当避免使用索引？

尽管创建索引的目的是提升数据库的性能，但是还是有一些情况应当避免使用索引。下面几条指导原则给出了何时应当重新考虑是否使用索引：

• 小的数据表不应当使用索引；
• 需要频繁进行大批量的更新或者插入操作的表；
• 如果列中包含大数或者 NULL 值，不宜创建索引；
• 频繁操作的列不宜创建索引。

行转列： PIVOT

在数据库操作中，有些时候我们遇到需要实现“行转列”的需求，例如:

pivot

SELECT [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]
FROM WEEK_INCOME 
PIVOT
(
    SUM(INCOME) for [week] in([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
)TBL

• SUM(INCOME) for [week] in([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])是PIVOT第一步骤，也是核心的地方，进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值，是总和(sum)，还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”，其中一条的income是1000,另一条income是500，那么在这里使用sum，行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]…)中 for [week]就是说将week列的值分别转换成一个个列，也就是“以值变列”。但是需要转换成列的值有可能有很多，我们只想取其中几个值转换成列，那么怎样取呢？就是在in里面了，比如我此刻只想看工作日的收入，在in里面就只写“星期一”至“星期五”（注意，in里面是原来week列的值,”以值变列”）。总的来说，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来，就是说：将列[week]值为”星期一”,”星期二”,”星期三”,”星期四”,”星期五”,”星期六”,”星期日”分别转换成列，这些列的值取income的总和。
• FROM WEEK_INCOME这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误
• SELECT [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列(也就是某些天)
• TBL别名 不能缺省

行转列也可以使用 CONVERT 来实现，两种方法均可以参考：重温SQL——行转列，列转行

数据库事务

事务是单个工作单元。 如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。 如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。

SQL Server 以下列事务模式运行：

• 自动提交事务,每条单独的语句都是一个事务。
• 显式事务,每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。
• 隐式事务,在前一个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。
• 批处理级事务,只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。 当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚。

一个数据库事务主要有三部分组成：

• 开始事物：begin transaction
• 提交事物：commit transaction
• 回滚事物：rollback transaction

示例：

begin transaction  
 
declare @errorSum int      --定义局部变量  
 
set @errorSum=0  --初始化临时变量  
 
update bank set currentMoneycurrentMoney= currentMoney-1000 where customerName='张三' 
 
set @errorSum=@errorSum+@@error    --累计是否有错误  
 
update bank set currentMoneycurrentMoney= currentMoney+1000 where customerName='李四' 
 
set @errorSum=@errorSum+@@error    --累计是否有错误  
 
if @errorSum<>0     --如果有错误  
 
begin  
 
rollback transaction  
 
end  
 
else  
 
begin  
 
commit  transaction  
 
end  
 
go

SQL SERVER的锁