楠槡

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

2018-06-10

数据库/表操作

获取所有用户名:

1
Select name FROM Sysusers where status='2' and islogin='1'
  • islogin=’1’ :表示帐户
  • islogin=’0’ :表示角色
  • status=’2’ :表示用户帐户
  • status=’0’ :表示糸统帐户

获取所有数据库名:

1
Select Name FROM Master..SysDatabases orDER BY Name

获取所有表名:

1
2
3
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’ :表示所有系统表;

获取所有字段名:

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

查询表的字段定义:

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

获取数据库所有类型:

1
select name from systypes

获取主键字段:

1
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

1
2
3
4
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

1
2
3
4
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

1
2
3
4
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

1
2
3
4
5
6
7
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

1
2
3
4
5
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命令的基本语法如下:

1
CREATE INDEX index_name ON table_name;

单列索引:

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

1
2
CREATE INDEX index_name
ON table_name (column_name);

唯一索引:

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

1
2
CREATE UNIQUE INDEX index_name
on table_name (column_name);

聚簇索引:

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

1
2
CREATE INDEX index_name
on table_name (column1, column2);

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

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

隐式索引:

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

DROP INDEX 命令:

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

其基本语法如下:

1
DROP INDEX table_name.index_name;

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

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

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

行转列: PIVOT

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

pivot

1
2
3
4
5
6
7
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

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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的锁

Tags: dotNET
使用支付宝打赏
使用微信打赏

若你觉得我的文章对您有帮助,点击上方按钮请我喝杯咖啡☕

若文章中存在问题,或您有任何意见和疑问,均可与我联系

扫描二维码,分享此文章 📱