SQL Server T-SQL Recipes读书笔记 Day 1

第一章 从Select开始

1. 1.1   连接数据库

USE AdventureWorks2014;

1. 1.2   检查数据库版本

SELECT @@VERSION;

1. 1.3   获取数据库名字

select DB_NAME();

1. 1.4   检查你的用户名

SELECT ORIGINAL_LOGIN(), CURRENT_USER, SYSTEM_USER;

1. 1.5   查询一个表

SELECT NationalIDNumber, LoginID, JobTitle FROM HumanResources.Employee;

1. 1.6   返回特定的行

SELECT Title, FirstName, LastName FROM Person.Person WHERE Title = 'Ms.';

1. 1.7   列出可选的表

SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'HumanResources';SELECT name FROM sys.tables WHERE SCHEMA_NAME(schema_id)='HumanResources';

 

1. 1.8   命名输出栏位名称

SELECT BusinessEntityID AS "Employee ID", VacationHours AS "Vacation", SickLeaveHours AS "Sick Time" FROM HumanResources.Employee;

1. 1.9   提供表的别名

SELECT E.BusinessEntityID AS "Employee ID", E.VacationHours AS "Vacation", E.SickLeaveHours AS "Sick Time" FROM HumanResources.Employee AS E WHERE E.VacationHours > 40;

1. 1.10 从已有栏位计算新列

SELECT BusinessEntityID AS "EmployeeID", VacationHours + SickLeaveHours AS "AvailableTimeOff" FROM HumanResources.Employee;

1. 1.11 否定一个查询条件

SELECT Title, FirstName, LastName FROM Person.Person WHERE NOT Title = 'Ms.';

 

1.12 保持Where条件顺序不模糊

通过括号实现

SELECT Title, FirstName, LastName FROM Person.Person WHERE Title = 'Ms.' AND (FirstName = 'Catherine' OR LastName = 'Adams');

1. 1.13 测试存在性

SELECT TOP(1) 1 FROM HumanResources.Employee WHERE SickLeaveHours > 80;SELECT 1 WHERE EXISTS ( SELECT * FROM HumanResources.Employee WHERE SickLeaveHours > 40 );

 

1. 1.14 指定一个范围

SELECT SalesOrderID, ShipDate FROM Sales.SalesOrderHeader WHERE ShipDate BETWEEN '2005-07-23 00:00:00.0' AND '2005-07-24 23:59:59.0';

1. 1.15 检查Null值

SELECT ProductID, Name, Weight FROM Production.Product WHERE Weight IS NULL;

1.16 写一个in列表

SELECT ProductID, Name, Color FROM Production.Product WHERE Color IN ('Silver', 'Black', 'Red');

注意：当写Not In 时，如果列表里有一个是NULL，

则整个表达式会返回unknown，不会有结果返回。

1.17 进行匹配查询

SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'B%';

1.18 排序结果集

SELECT p.Name, h.EndDate, h.ListPrice FROM Production.Product p INNER JOIN Production.ProductListPriceHistory h ON p.ProductID = h.ProductID ORDER BY p.Name, h.EndDate;

1.19 指定一个大小写敏感的排序

SELECT p.Name, h.EndDate, h.ListPrice FROM Production.Product p INNER JOIN Production.ProductListPriceHistory h ON p.ProductID = h.ProductID ORDER BY p.Name COLLATE Latin1_General_BIN ASC, h.EndDate DESC;

1.20 Nulls排在前面或后面

SELECT ProductID, Name, Weight FROM Production.Product ORDER BY ISNULL(Weight, 1) DESC, Weight;SELECT ProductID, Name, Weight FROM Production.Product ORDER BY IIF(Weight IS NULL, 1, 0), Weight;

1.21 强制特殊排序顺序

SELECT p.ProductID, p.Name, p.Color FROM Production.Product AS p WHERE p.Color IS NOT NULL ORDER BY CASE p.Color WHEN 'Red' THEN NULL ELSE p.COLOR END;

1.22 通过结果集分页

SELECT ProductID, Name FROM Production.Product ORDER BY Name OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

1.23 抽样一个子集

SELECT * FROM Purchasing.PurchaseOrderHeader TABLESAMPLE (5 PERCENT);SELECT * FROM Purchasing.PurchaseOrderHeader TABLESAMPLE (200 ROWS);

 

注意：抽样返回的行数不一定是你写的参数的行数