sybase_trasact_sql_sqlug.pdf

Transact-SQL User’s Guide

Adaptive Server ® Enterprise

15.0

DOCUMENT ID: DC32300-01-1500-02

LAST REVISED: October 2005

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, the Sybase logo, ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server,

Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive Server Enterprise Replication,

Adaptive Server Everywhere, Adaptive Warehouse, Afaria, Answers Anywhere, Anywhere Studio, Application Manager, AppModeler,

APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-Translator, APT-Library, AvantGo Mobile Delivery, AvantGo Mobile

Inspection, AvantGo Mobile Marketing Channel, AvantGo Mobile Pharma, AvantGo Mobile Sales, AvantGo Pylon, AvantGo Pylon

Application Server, AvantGo Pylon Conduit, AvantGo Pylon PIM Server, AvantGo Pylon Pro, Backup Server, BizTracker,

ClearConnect, Client-Library, Client Services, Convoy/DM, Copernicus, Data Pipeline, Data Workbench, DataArchitect, Database

Analyzer, DataExpress, DataServer, DataWindow, DataWindow .NET, DB-Library, dbQueue, Developers Workbench, DirectConnect,

DirectConnect Anywhere, Distribution Director, e-ADK, E-Anywhere, e-Biz Impact, e-Biz Integrator, E-Whatever, EC Gateway,

ECMAP, ECRTP, eFulfillment Accelerator, Embedded SQL, EMS, Enterprise Application Studio, Enterprise Client/Server, Enterprise

Connect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work

Designer, Enterprise Work Modeler, eProcurement Accelerator, EWA, Financial Fusion, Financial Fusion Server, Gateway Manager,

GlobalFIX, iAnywhere, iAnywhere Solutions, ImpactNow, Industry Warehouse Studio, InfoMaker, Information Anywhere, Information

Everywhere, InformationConnect, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, M2M Anywhere, Mach Desktop, Mail

Anywhere Studio, Mainframe Connect, Maintenance Express, Manage Anywhere Studio, M-Business Channel, M-Business Network,

M-Business Server, MDI Access Server, MDI Database Gateway, media.splash, MetaWorks, mFolio, Mirror Activator, MySupport, Net-

Gateway, Net-Library, New Era of Networks, ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL

Toolkit, Open Biz, Open Client, Open ClientConnect, Open Client/Server, Open Client/Server Interfaces, Open Gateway, Open Server,

Open ServerConnect, Open Solutions, Optima++, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, PocketBuilder, Pocket

PowerBuilder, Power++, power.stop, PowerAMC, PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner,

PowerDimensions, PowerDynamo, PowerScript, PowerSite, PowerSocket, Powersoft, PowerStage, PowerStudio, PowerTips, Powersoft

Portfolio, Powersoft Professional, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst, QAnywhere, Rapport, RemoteWare,

RepConnector, Replication Agent, Replication Driver, Replication Server, Replication Server Manager, Replication Toolkit, Report-

Execute, Report Workbench, Resource Manager, RFID Anywhere, RW-DisplayLib, RW-Library, S-Designor, SDF, Search Anywhere,

Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SOA Anywhere, SQL

Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere, SQL

Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL SMART, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL

Server SNMP SubAgent, SQL Station, SQLJ, STEP, SupportNow, S.W.I.F.T. Message Format Libraries, Sybase Central, Sybase Client/

Server Interfaces, Sybase Financial Server, Sybase Gateways, Sybase IQ, Sybase MPP, Sybase SQL Desktop, Sybase SQL Lifecycle,

Sybase SQL Workgroup, Sybase User Workbench, SybaseWare, Syber Financial, SyberAssist, SybFlex, SyBooks, System 10, System

11, System XI (logo), SystemTools, Tabular Data Stream, TradeForce, Transact-SQL, Translation Toolkit, UltraLite, UltraLite.NET,

UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, VisualWriter, VQL, WarehouseArchitect, Warehouse

Control Center, Warehouse Studio, Warehouse WORKS, Watcom, Watcom SQL, Watcom SQL Server, Web Deployment Kit, Web.PB,

Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server, XcelleNet, and XP Server are trademarks of

Sybase, Inc. 06/05

Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.

All other company and product names used herein may be trademarks or registered trademarks of their respective companies.

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

About This Book ......................................................................................................................... xix

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

Naming conventions......................................................................... 6

SQL data characters ................................................................. 6

SQL language characters.......................................................... 6

Identifiers ................................................................................... 7

Expressions in Adaptive Server ..................................................... 14

Arithmetic and character expressions ..................................... 14

Relational and logical expressions .......................................... 20

Transact-SQL extensions............................................................... 21

compute clause ....................................................................... 22

Control-of-flow language ......................................................... 22

Stored procedures ................................................................... 22

Extended stored procedures ................................................... 23

Triggers ................................................................................... 23

Defaults and rules ................................................................... 24

Error handling and set options ................................................ 24

Additional Adaptive Server extensions to SQL........................ 26

Compliance to ANSI standards ...................................................... 27

Federal Information Processing Standards (FIPS) flagger...... 28

Chained transactions and isolation levels ............................... 29

Identifiers ................................................................................. 29

SQL standard-style comments ................................................ 29

Right truncation of character strings........................................ 30

Permissions required for update and delete statements ......... 30

Arithmetic errors ...................................................................... 30

Synonymous keywords ........................................................... 31

Treatment of nulls.................................................................... 32

Transact-SQL User’s Guide

iii

Contents

Adaptive Server login accounts...................................................... 32

Group membership.................................................................. 33

Role membership .................................................................... 33

Information about your Adaptive Server account .................... 34

Password changes .................................................................. 34

Remote logins ......................................................................... 35

isql utility......................................................................................... 37

Default databases ................................................................... 37

Network-based security services with isql............................... 38

isql logout ................................................................................ 39

pubs2 and pubs3 sample databases ............................................. 39

Sample database content........................................................ 39

CHAPTER 2

Queries: Selecting Data from a Table .......................................... 41

What are queries? .......................................................................... 41

select syntax............................................................................ 42

Choosing columns using the select clause .................................... 43

Choosing all columns using select * ....................................... 44

Choosing specific columns...................................................... 45

Rearranging the column order................................................. 45

Renaming columns in query results ........................................ 46

Using expressions ................................................................... 46

Selecting text, unitext, image, and values ............................... 53

Select list summary ................................................................. 54

Eliminating duplicate query results with distinct ............................. 55

Specifying tables with the from clause ........................................... 57

Selecting rows using the where clause .......................................... 58

Comparison operators............................................................. 59

Ranges (between and not between) ....................................... 60

Lists (in and not in) .................................................................. 62

Matching patterns........................................................................... 64

Matching character strings: like............................................... 64

Character strings and quotation marks ................................... 70

“Unknown” values: NULL ........................................................ 71

Connecting conditions with logical operators .......................... 76

CHAPTER 3

Using Aggregates, Grouping, and Sorting .................................. 79

Using aggregate functions ............................................................. 79

Aggregate functions and datatypes......................................... 81

count vs. count(*) .................................................................... 82

Aggregate functions with distinct............................................. 83

Null values and the aggregate functions ................................. 84

Organizing query results into groups: the group by clause ............ 85

Adaptive Server Enterprise

Contents

group by syntax ....................................................................... 86

group by and SQL standards .................................................. 86

Nesting groups with group by .................................................. 87

Referencing other columns in queries using group by ............ 87

Using outer joins and and aggregate extended columns ........ 91

Expressions and group by ....................................................... 93

Using group by in nested aggregates...................................... 94

Null values and group by ......................................................... 95

where clause and group by ..................................................... 96

group by and all ....................................................................... 97

Aggregates without group by................................................... 98

Selecting groups of data: the having clause ................................ 100

How the having, group by, and where clauses interact ......... 101

Using having without group by .............................................. 104

Sorting query results: the order by clause.................................... 105

order by and group by ........................................................... 108

order by and group by used with select distinct .................... 108

Summarizing groups of data: the compute clause ....................... 109

Row aggregates and compute .............................................. 112

Specifying more than one column after compute .................. 113

Using more than one compute clause................................... 114

Applying an aggregate to more than one column.................. 115

Using different aggregates in the same compute clause ...... 116

Generating totals: compute without by .................................. 116

Combining queries: the union operator ........................................ 118

Guidelines for union queries.................................................. 120

Using union with other Transact-SQL commands ................. 122

CHAPTER 4

Joins: Retrieving Data from Several Tables ............................. 125

How joins work ............................................................................. 126

Join syntax ............................................................................ 126

Joins and the relational model............................................... 127

How joins are structured .............................................................. 127

The from clause..................................................................... 129

The where clause .................................................................. 130

How joins are processed .............................................................. 132

Equijoins and natural joins ........................................................... 133

Joins with additional conditions .................................................... 134

Joins not based on equality.......................................................... 135

Self-joins and correlation names .................................................. 136

The not-equal join ........................................................................ 137

Not-equal joins and subqueries ............................................. 139

Joining more than two tables ....................................................... 140

Outer joins .................................................................................... 142

Transact-SQL User’s Guide

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: