sqlug.pdf

(8784 KB) Pobierz
Transact-SQL
®
Users Guide
Adaptive Server
®
Enterprise
15.7
DOCUMENT ID:
DC32300-01-1570-01
LAST REVISED:
September 2011
Copyright © 2011 by Sybase, Inc. All rights reserved.
This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes.
Information in this document is subject to change without notice. The software described herein is furnished under a license agreement,
and it may be used or copied only in accordance with the terms of that agreement.
To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.
Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All other
international customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduled
software release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic,
mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.
Sybase trademarks can be viewed at
the Sybase trademarks page at http://www.sybase.com/detail?id=1011207
. Sybase and the marks listed
are trademarks of Sybase, Inc. ® indicates registration in the United States of America.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of
SAP AG in Germany and in several other countries all over the world.
Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.
Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.
IBM and Tivoli are registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
All other company and product names mentioned may be trademarks of the respective companies with which they are associated.
Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013
for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.
Sybase, Inc., One Sybase Drive, Dublin, CA 94568.
Contents
CHAPTER 1
SQL Building Blocks....................................................................... 1
SQL in Adaptive Server.................................................................... 1
Queries, data modification, and commands .............................. 2
Tables, columns, and rows........................................................ 3
Relational operations................................................................. 3
Compiled objects ....................................................................... 4
Compliance to ANSI standards ........................................................ 5
Federal Information Processing Standards (FIPS) flagger........ 6
Chained transactions and isolation levels ................................. 7
Identifiers ................................................................................... 7
SQL standard-style comments .................................................. 7
Right truncation of character strings.......................................... 8
Permissions required for update and delete statements ........... 8
Arithmetic errors ........................................................................ 8
Synonymous keywords ............................................................. 9
Treatment of nulls...................................................................... 9
Naming conventions....................................................................... 10
SQL data characters ............................................................... 10
SQL language characters........................................................ 10
Identifiers ................................................................................. 11
Expressions in Adaptive Server ..................................................... 18
Arithmetic and character expressions ..................................... 18
Relational and logical expressions .......................................... 24
Transact-SQL extensions............................................................... 25
compute clause ....................................................................... 26
Control-of-flow language ......................................................... 26
Stored procedures ................................................................... 26
Extended stored procedures ................................................... 27
Triggers ................................................................................... 27
Defaults and rules ................................................................... 28
Error handling and set options ................................................ 28
Additional Adaptive Server extensions to SQL........................ 29
Adaptive Server login accounts...................................................... 30
isql utility......................................................................................... 31
Default databases ................................................................... 32
iii
Transact-SQL Users Guide
Contents
Network-based security services with isql............................... 33
Displaying SQL text........................................................................ 33
CHAPTER 2
Queries: Selecting Data from a Table .......................................... 35
Queries........................................................................................... 35
select syntax............................................................................ 36
Choosing columns using the select clause .................................... 38
Choosing all columns using select * ....................................... 38
Choosing specific columns...................................................... 39
Rearranging the column order................................................. 40
Renaming columns in query results ........................................ 40
Using expressions ................................................................... 41
Selecting text, unitext, and image values ................................ 46
Select list summary ................................................................. 48
Using select for update .................................................................. 49
Using select for update in cursors and DML ........................... 49
Concurrency issues................................................................. 50
Eliminating duplicate query results with distinct ............................. 51
Specifying tables with the from clause ........................................... 53
Selecting rows using the where clause .......................................... 54
Comparison operators in where clauses ................................. 55
Ranges (between and not between) ....................................... 56
Lists (in and not in) .................................................................. 58
Matching patterns........................................................................... 60
Matching character strings: like............................................... 60
“Unknown” values: NULL ........................................................ 66
Connecting conditions with logical operators .......................... 71
Using multiple select items in a nested exists query...................... 73
Using a column alias in nested select statements ......................... 74
Using Aggregates, Grouping, and Sorting.................................. 75
Using aggregate functions ............................................................. 75
Aggregate functions and datatypes......................................... 77
count vs. count(*) .................................................................... 78
Aggregate functions with distinct............................................. 79
Null values and the aggregate functions ................................. 80
Using statistical aggregates .................................................... 81
Organizing query results into groups: the group by clause ............ 82
group by and SQL standards .................................................. 83
Nesting groups with group by.................................................. 84
Referencing other columns in queries using group by ............ 84
Expressions and group by....................................................... 87
Using group by in nested aggregates...................................... 89
CHAPTER 3
iv
Adaptive Server Enterprise
Contents
Null values and group by......................................................... 89
where clause and group by ..................................................... 91
group by and all....................................................................... 92
Aggregates without group by................................................... 93
Selecting groups of data: the having clause .................................. 94
How the having, group by, and where clauses interact ........... 96
Using having without group by ................................................ 99
Sorting query results: the order by clause.................................... 100
order by and group by ........................................................... 102
order by and group by used with select distinct .................... 103
Summarizing groups of data: the compute clause ....................... 104
Row aggregates and compute .............................................. 107
Specifying more than one column after compute .................. 108
Using more than one compute clause................................... 109
Applying an aggregate to more than one column.................. 110
Using different aggregates in the same compute clause ...... 110
Generating totals: compute without by .................................. 111
Combining queries: the union operator ........................................ 112
Guidelines for union queries.................................................. 114
Using union with other Transact-SQL commands................. 116
CHAPTER 4
Joins: Retrieving Data from Several Tables .............................
How joins work .............................................................................
Join syntax ............................................................................
Joins and the relational model...............................................
How joins are structured ..............................................................
The from clause.....................................................................
The where clause ..................................................................
How joins are processed ..............................................................
Equijoins and natural joins ...........................................................
Joins with additional conditions....................................................
Joins not based on equality..........................................................
Self-joins and correlation names..................................................
The not-equal join ........................................................................
Not-equal joins and subqueries.............................................
Joining more than two tables .......................................................
Outer joins....................................................................................
Inner and outer tables ...........................................................
Outer join restrictions ............................................................
Views used with outer joins ...................................................
ANSI inner and outer joins ....................................................
ANSI outer joins ....................................................................
Transact-SQL outer joins ......................................................
Relocated joins.............................................................................
119
120
120
121
121
123
124
126
127
128
129
130
131
133
134
135
136
136
137
137
143
154
159
v
Transact-SQL Users Guide

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin