SQL Server T-SQL 实用技巧第四版

需积分: 31 26 浏览量更新于2024-07-18 收藏 5.77MB PDF 举报

"SQL Server T-SQL Recipes, 4th Edition" 是一本专注于SQL Server中T-SQL语言实用技巧的书籍，适用于数据库管理和开发人员。本书提供了各种解决常见问题的配方，涵盖从基本查询到复杂数据操作的多个方面。在书中，读者将能够找到以下章节的详尽内容： 1. Getting Started with SELECT：这一章介绍如何开始使用SELECT语句，包括基础的查询语法，选择特定列，以及从表中检索数据的基本方法。 2. Elementary Programming：深入讲解T-SQL编程的基础，如变量的声明与使用、流程控制（如IF...ELSE, WHILE循环）以及函数的运用。 3. Working with NULLS：讨论SQL中的NULL值处理，如何进行NULL值的比较和操作，以及使用IS NULL和IS NOT NULL运算符。 4. Querying from Multiple Tables：涵盖了联接（JOIN）的概念，包括内连接、外连接和自连接，以及如何使用子查询来处理多表查询。 5. Aggregations and Grouping：介绍聚合函数（如SUM, COUNT, AVG, MAX, MIN）以及GROUP BY和HAVING子句的用法，用于对数据进行分组统计。 6. Advanced Select Techniques：讲解更高级的查询技巧，如使用PIVOT和UNPIVOT操作，以及集合函数的应用。 7. Windowing Functions：探讨窗口函数，如RANK(), ROW_NUMBER(), LAG()和LEAD()，它们在处理排序和分组数据时非常有用。 8. Inserting, Updating, Deleting：详细介绍如何插入、更新和删除数据，包括批量操作和事务管理。 9. Working with Strings：介绍字符串操作函数，如CONCAT(), SUBSTRING(), PATINDEX(), 和REPLACE()等，以及字符串比较和转换。 10. Working with Dates and Times：讨论日期和时间数据类型及函数，如DATEADD(), DATEDIFF(), 和CONVERT()等，帮助处理日期和时间相关的计算和格式化。 11. Working with Numbers：涵盖数值运算，包括数学函数和精度处理，以及在数值比较和计算中的最佳实践。 12. Transactions, Locking, Blocking, and Deadlocking：深入探讨事务的ACID属性，锁定机制，阻塞和死锁的概念，以及如何诊断和解决这些问题。 13. Managing Tables：讲解如何创建、修改和删除表，包括约束、索引和分区。 14. Managing Views：介绍视图的概念，如何创建和使用视图，以及视图在数据库设计和权限管理中的作用。 15. Managing Large Tables and Databases：讨论大数据量表的管理策略，如分区、压缩和优化查询性能。 16. Managing Indexes：深入介绍索引的原理，创建和维护索引，以及如何通过索引来提高查询性能。这本书全面覆盖了SQL Server T-SQL的各个方面，对于希望提升SQL技能或解决实际问题的数据库专业人士来说是一本宝贵的参考书。通过实例和实践技巧，读者可以快速掌握并应用这些知识到日常工作中。

CHAPTER 1 ■ GETTING STARTED WITH SELECT

How It Works

You can specify any expression you like in the SELECT list, and the value of that expression will be returned

as a column in the query results. Most of the time you’ll be referring to at least one table column from such

an expression, but there are actually useful expressions that can be written that stand alone, that do not take

other columns as input.

Recipe 1-8 introduces column aliases. It‘s especially important to provide them for computed columns.

That‘s because if you don’t provide an alias for a computed column, one is not created for you, and thus

there is no name by which to refer to the column, nor is there a name to place in the output heading when

executing the query ad-hoc from Management Studio.

1-11. Negating a Search Condition

Problem

You are finding it easier to describe those rows that you do not want rather than those that you do want.

Solution

Describe the rows that you do not want. Then use the NOT operator to essentially reverse the description

so that you get those rows that you do want. The NOT logical operator negates the expression that follows it.

For example, you can retrieve all employees having a title of anything but “Ms.” by executing the

following query:

SELECT Title, FirstName, LastName

FROM Person.Person

WHERE NOT Title = 'Ms.';

Title FirstName LastName

-------- ----------- -----------

Mr. Jossef Goldberg

Mr. Hung-Fu Ting

...

Sr. Humberto Acevedo

Sra. Pilar Ackerman

...

How It Works

NOT specifies the reverse of a search condition, in this case specifying that only rows that don’t have the

Title equal to “Ms.” be returned. You can apply the NOT operator to individual expressions in a WHERE clause.

You can also apply it to a group of expressions. For example:

WHERE NOT (Title = 'Ms.' OR Title = 'Mr.')

Think in terms of finding all the rows having “Ms.” or “Mr.” and then returning everything else except

those rows. The parentheses force evaluation of the OR condition first. Then all rows not meeting that

condition are returned by the query.

CHAPTER 1 ■ GETTING STARTED WITH SELECT

1-12. Keeping the WHERE Clause Unambiguous

Problem

You are writing several expressions in a WHERE clause that are linked together using AND and OR, and sometimes

NOT. You worry that future maintainers of your query will misconstrue your intentions.

Solution

Enclose expressions in parentheses to make clear your intent. For example:

SELECT Title, FirstName, LastName

FROM Person.Person

WHERE Title = 'Ms.' AND

(FirstName = 'Catherine' OR

LastName = 'Adams');

How It Works

You can write multiple operators (AND, OR, NOT) in a single WHERE clause, but it is important to make

your intentions clear by properly embedding your ANDs and ORs in parentheses. The NOT operator takes

precedence (is evaluated first) over AND. The AND operator takes precedence over the OR operator. Using both

AND and OR operators in the same WHERE clause without parentheses can return unexpected results.

Consider the solution query and pretend for a moment that there are no parentheses. Is the intention

to return results for all rows with a Title of “Ms.,” and of those rows, only include those with a FirstName of

Catherine or a LastName of Adams? Or did the query author wish to search for all people titled “Ms.” with a

FirstName of Catherine, as well as anyone with a LastName of Adams? The parentheses make the author’s

intentions crystal clear.

It is good practice to use parentheses to clarify exactly what rows should be returned. Even if you

are fully conversant with the rules of operator precedence, those who come after you may not be. Make

judicious use of parentheses to remove all doubt as to your intentions.

1-13. Testing for Existence

Problem

You want to know whether something is true, but you don’t really care to see the data that proves it. For

example, you want to know the answer to the following business question: “Are there really employees

having more than 80 hours of sick time?”

Solution

One solution is to execute a query to return one row in the event that what you care about is true, and to

return no rows otherwise. The following example returns the value 1 in the event of any employee having

more than 80 hours of sick time:

SELECT TOP(1) 1

FROM HumanResources.Employee

WHERE SickLeaveHours > 80;

CHAPTER 1 ■ GETTING STARTED WITH SELECT

SalesOrderID ShipDate

------------ -----------------------

43758 2005-07-23 00:00:00.000

43759 2005-07-23 00:00:00.000

43760 2005-07-23 00:00:00.000

...

How It Works

This recipe demonstrates the BETWEEN operator, which tests whether a column’s value falls between two

values that you specify. The value range is inclusive of the two endpoints.

Notice that we designated the specific time in hours, minutes, and seconds as well. The time-of-day

defaults to 00:00:00, which is midnight at the start of a date. In this example, we wanted to include all of

7/24/2005. Thus, we specified the last possible second of that day.

However, there is an issue you must be aware of when using BETWEEN with date-time values: What if the

shipment date is 2005-07-23 23:59:59.456? A safer approach is to test for dates being greater than or equal to

the starting point, and less than the earliest time just after the end point. For example:

SELECT SalesOrderID, ShipDate

FROM Sales.SalesOrderHeader

WHERE ShipDate >= '2005-07-23' AND ShipDate < '2005-07-25';

This solution is safer, because it’s trivial to specify the earliest possible time on the 25

, and then to test

for ShipDate being less than that. It is not so easy to know the maximum possible fractional seconds value

to specify for the BETWEEN approach. Should those be 59.997? 59.9999? 59.999999? How many nines? Don’t

waste time trying to figure that out. Just take the safer approach unless you are certain that your data never

includes fractional seconds.

Caution ■ You encounter the same issue with decimal digits when using BETWEEN with decimal and

floating-point values as with date-time values.

1-15. Checking for Null Values

Problem

Some of the values in a column might be NULL. You wish to identify rows having or not having NULL values.

Solution

Make use of the IS NULL and IS NOT NULL tests to identify rows having or not having NULL values in a given

column. For example, the following query returns any rows for which the value of the product’s weight is

unknown:

SELECT ProductID, Name, Weight

FROM Production.Product

WHERE Weight IS NULL;

剩余946页未读，继续阅读

kernelkoder

粉丝: 57
资源: 317

SQL Server T-SQL 实用技巧第四版

XML and JSON Recipes for SQL Server A Problem-Solution Approach epub

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach

SQL Server T-SQL Recipes(Apress,4ed,2015)

SQL Server 2005 T-SQL Recipes

SQL Server 2012 T-SQL Recipes

SQL Server 2012 T-SQL Recipes （高清原版）

Salesforce-Marketing-Cloud-Developer-Edition-Recipes, Salesforce营销云开发者版菜谱.zip

SQL.Server.2008.Transact-SQL.Recipes

SQL Server 2012 T-SQL 实用技巧 (英文版)

[电子书] SQL Server 2012 T-SQL 实用技巧 (英文版)

最新资源