Wordware Publishing - SQL Anywhere Studio 9 Developer's Guide.pdf - Web Dev Books - stinger123

SQL Anywhere ®

Studio 9

Breck Carter

Wordware Publishing, Inc.

Library of Congress Cataloging-in-Publication Data

Carter, Breck.

SQL anywhere Studio9/byBreck Carter.

p. cm.

ISBN 1-55622-506-7

1. SQL (Computer program language) 2. Relational databases. I. Title.

QA76.73.S67C36 2004

005.13'3—dc22

2004011573

2320 Los Rios Boulevard

Plano, Texas 75074

No part of this book may be reproduced in any form or by any means

without permission in writing from Wordware Publishing, Inc.

Printed in the United States of America

ISBN 1-55622-506-7

10987654321

0406

SQL Anywhere is a registered trademark of Sybase, Inc. and its subsidiaries.

All brand names and product names mentioned in this book are trademarks or service marks of their

respective companies. Any omission or misuse (of any kind) of service marks or trademarks should not be

regarded as intent to infringe on the property of others. The publisher recognizes and respects all marks

used by companies, manufacturers, and developers as a means to distinguish their products.

All inquiries for volume purchases of this book should be addressed to Wordware

Publishing, Inc., at the above address. Telephone inquiries may be made by calling:

(972) 423-0090

Contents

Preface........................................ix

Acknowledgments .................................xiii

About the Author .................................xiv

Chapter 1 Creating ..........................1

1.1 Introduction ...................................1

1.2 Codd’s 12 Rules for Relational Databases ...................2

1.3 Five Types of Tables ...............................4

1.4 Global Permanent Table .............................6

1.5 Data Types ....................................7

1.5.1 A String Is a String: BINARY, CHARACTER, LONG .........8

1.5.2 Dates and Times .............................10

1.5.3 Exact Decimals .............................10

1.5.4 Floating-Point Numbers .........................11

1.5.5 Binary Integers .............................12

1.5.6 Literals ..................................13

1.6 Column Properties ...............................14

1.7 Computed Columns ..............................14

1.8 DEFAULT Values ...............................16

1.8.1 DEFAULT AUTOINCREMENT ....................17

1.8.2 DEFAULT GLOBAL AUTOINCREMENT ..............18

1.8.3 Literal Defaults .............................19

1.8.4 Special Update Defaults .........................20

1.8.5 Expressions as Defaults .........................21

1.9 NULL Property .................................21

1.10 Column Constraints ..............................21

1.10.1 NOT NULL Constraint ........................22

1.10.2 Column CHECK Constraint ......................22

1.10.3 PRIMARY KEY Column Constraint .................23

1.10.4 Foreign Key Column Constraint ....................24

1.10.5 UNIQUE Column Constraint .....................25

1.11 User-Defined Data Types ...........................25

1.12 Free Space...................................27

1.13 Table Constraints ...............................27

1.13.1 Table CHECK Constraint .......................28

1.13.2 PRIMARY KEY Table Constraint ...................28

1.13.3 FOREIGN KEY Table Constraint ...................29

1.13.4 UNIQUE Table Constraint .......................30

1.14 Remote Data Access .............................31

1.14.1 CREATE SERVER ...........................32

1.14.2 CREATE EXTERNLOGIN ......................32

1.14.3 CREATE Remote and Proxy Tables ..................33

iii

Contents

1.14.4 CREATE EXISTING TABLE .....................33

1.15 Temporary Tables ...............................35

1.15.1 Global Temporary Tables .......................36

1.15.2 Local Temporary Tables ........................37

1.15.2.1 CREATE TABLE #table_name ................37

1.15.2.2 DECLARE LOCAL TEMPORARY TABLE .........38

1.15.2.3 SELECT INTO #table_name .................40

1.16 Normalized Design ..............................40

1.16.1 First Normal Form ...........................41

1.16.2 Second Normal Form .........................42

1.16.3 Third Normal Form ..........................43

1.16.4 Boyce-Codd Normal Form.......................44

1.16.5 Fourth Normal Form ..........................45

1.16.6 Fifth Normal Form ...........................46

1.17 Chapter Summary ...............................47

Chapter 2 Inserting .........................49

2.1 Introduction...................................49

2.2 INSERT .....................................49

2.2.1 INSERT All Values ...........................50

2.2.2 INSERT Named Values .........................52

2.2.3 INSERT Select All Columns ......................53

2.2.4 INSERT Select Column List ......................54

2.2.5 INSERT Select With Auto Name ....................55

2.3 LOAD TABLE .................................56

2.4 ISQL INPUT ..................................64

2.5 Chapter Summary ...............................68

Chapter 3 Selecting .........................69

3.1 Introduction...................................69

3.2 Logical Execution of a SELECT........................70

3.3 FROM Clause .................................80

3.4 JOIN ......................................82

3.4.1 CROSS JOIN ..............................82

3.4.2 INNER JOIN ..............................83

3.4.3 LEFT OUTER JOIN...........................84

3.4.4 RIGHT OUTER JOIN..........................85

3.4.5 FULL OUTER JOIN ..........................86

3.5 Derived Tables .................................87

3.6 Multi-Table Joins ................................90

3.7 SELECT FROM Procedure Call ........................96

3.8 LATERAL Procedure Call ...........................99

3.9 SELECT List .................................101

3.10 Expressions and Operators .........................102

3.10.1 IF and CASE Expressions ......................105

3.11 Top 15 Scalar Built-in Functions ......................107

3.12 Boolean Expressions and the WHERE Clause ...............113

3.12.1 Comparison Predicates ........................116

Wordware Publishing - SQL Anywhere Studio 9 Developer's Guide.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: