Informix Guide to SQL: Tutorial
522 pages
English
Le téléchargement nécessite un accès à la bibliothèque YouScribe
Tout savoir sur nos offres

Informix Guide to SQL: Tutorial

Le téléchargement nécessite un accès à la bibliothèque YouScribe
Tout savoir sur nos offres
522 pages
English

Description

Informix Guide to SQL
Tutorial
Version 7.2
April 1996
Part No. 000-7883A ®Published by INFORMIX Press Informix Software, Inc.
4100 Bohannon Drive
Menlo Park, CA 94025
The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in the
United States of America as indicated by “®,” and in numerous other countries worldwide:
INFORMIX ; NewEra ; ViewPoint ; C-ISAM -OnLine Dynamic Server ; INFORMIX -SuperView (SuperView technology Patent Pending)
The following are worldwide trademarks of the indicated owners or their subsidiaries, registered in the
Adobe Systems Incorporated: PostScript
International Business Machines Corporation: DRDA ; IBM
Microsoft Corporation: Microsoft ; MS ; MS-DOS ; CodeView ; MS Windows ; Windows ; Windows
NT ; ODBC ; Visual Basic ; Visual C++
Microsoft Memory Management Product: HIMEM.SYS
(“DOS” as used herein refers to MS-DOS and/or PC-DOS operating systems.)
X/OpenCompany Ltd.: UNIX ; X/Open
Some of the products or services mentioned in this document are provided by companies other than Informix.
These products or services are identified by the trademark or servicemark of the appropriate company. If you
have a question about one of those products or services, please call the company in question directly.
Documentation Team: Diana Chase, Geeta Karmarkar, Tom Noronha, Kami Shahi
Copyright © 1981-1996 by Informix Software, Inc. All rights reserved.
No part of this work covered by the copyright hereon may be ...

Sujets

Informations

Publié par
Nombre de lectures 289
Langue English
Poids de l'ouvrage 1 Mo

Exrait

Informix Guide to SQL Tutorial Version 7.2 April 1996 Part No. 000-7883A ®Published by INFORMIX Press Informix Software, Inc. 4100 Bohannon Drive Menlo Park, CA 94025 The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in the United States of America as indicated by “®,” and in numerous other countries worldwide: INFORMIX ; NewEra ; ViewPoint ; C-ISAM -OnLine Dynamic Server ; INFORMIX -SuperView (SuperView technology Patent Pending) The following are worldwide trademarks of the indicated owners or their subsidiaries, registered in the Adobe Systems Incorporated: PostScript International Business Machines Corporation: DRDA ; IBM Microsoft Corporation: Microsoft ; MS ; MS-DOS ; CodeView ; MS Windows ; Windows ; Windows NT ; ODBC ; Visual Basic ; Visual C++ Microsoft Memory Management Product: HIMEM.SYS (“DOS” as used herein refers to MS-DOS and/or PC-DOS operating systems.) X/OpenCompany Ltd.: UNIX ; X/Open Some of the products or services mentioned in this document are provided by companies other than Informix. These products or services are identified by the trademark or servicemark of the appropriate company. If you have a question about one of those products or services, please call the company in question directly. Documentation Team: Diana Chase, Geeta Karmarkar, Tom Noronha, Kami Shahi Copyright © 1981-1996 by Informix Software, Inc. All rights reserved. No part of this work covered by the copyright hereon may be reproduced or used in any form or by any means—graphic, electronic, or mechanical, including photocopying, recording, taping, or information storage and retrieval systems—without permission of the publisher. To the extent that this software allows the user to store, display, and otherwise manipulate various forms of data, including, without limitation, multimedia content such as photographs, movies, music and other binary large objects (blobs), use of any single blob may potentially infringe upon numerous different third-party intellectual and/or proprietary rights. It is the user's responsibility to avoid infringements of any such third- party rights. RESTRICTED RIGHTS LEGEND Software and accompanying materials acquired with United States Federal Government funds or intended for use within or for any United States federal agency are provided with “Restricted Rights” as defined in DFARS 252.227-7013(c)(1)(ii) or FAR 52.227-19. ii Informix Guide to SQL: Tutorial Table of Contents Table of Contents Introduction About This Manual.................. 3 Organization of This Manual............. 3 Types of Users 5 Software Dependencies............... 5 Demonstration Database .............. 6 New Features of This Product 9 Conventions .................... 14 Typographical Conventions 15 Icon Conventions................. 15 Sample-Code Conventions 18 Terminology Conventions 19 Additional Documentation ............... 19 Printed Documentation 20 On-Line Documentation .............. 21 Related Reading 2 Compliance with Industry Standards ........... 23 Informix Welcomes Your Comments............ 24 Section I Using Basic SQL Chapter 1 Informix Databases The Data Illustration of a Data Model ...........1-3 Concurrent Use and Security.............1-8 Centralized Management ..............1-8 Important Database Terms ...............1-10 The Relational Model1-10 Structured Query Language1-14 Standard SQL ..................1-14 Informix SQL and ANSI SQL ............ 1-15 ANSI-Compliant Databases............. 1-16 GLS Databases ................. 1-16 The Database Software ................ 1-16 The Applications 1-16 The Database Server 1-17 Interactive SQL 1-17 Reports and Forms 1-17 General Programming............... 1-18 Applications and Database Servers .......... 1-19 Summary..................... 1-19 Chapter 2 Composing Simple SELECT Statements Introducing the SELECT Statement............ 2-4 Some Basic Concepts 2-5 The Forms of SELECT 2-10 Special Data Types ................2-1 Single-Table SELECT Statements............. 2-12 Selecting All Columns and Rows ........... 2-12 Selecting Specific Columns 2-18 Using the WHERE Clause.............. 2-28 Creating a Comparison Condition 2-29 Expressions and Derived Values 2-46 Using Functions in SELECT Statements......... 2-52 Using Stored Procedures in SELECT Statements...... 2-64 Multiple-Table SELECT Statements............ 2-66 Creating a Cartesian Product............. 2-66 Creating a Join.................. 2-68 Some Query Shortcuts............... 2-75 Summary..................... 2-80 Chapter 3 Composing Advanced SELECT Statements Using the GROUP BY and HAVING Clauses ........ 3-4 Using the GROUP BY Clause ............ 3-4 Using the HAVING Clause ............. 3-8 Creating Advanced Joins ............... 3-10 Self-Joins ...................3-1 Outer Joins 3-19 Subqueries in SELECT Statements 3-29 Using ALL 3-30 Using ANY 3-31 Single-Valued Subqueries.............. 3-32 iv Informix Guide to SQL: Tutorial Correlated Subqueries ...............3-33 Using EXISTS ..................3-34 Set Operations ...................3-38 Union .....................3-39 Intersection3-47 Difference....................3-49 Summary3-51 Chapter 4 Modifying Data Statements That Modify Data ..............4-3 Deleting Rows ..................4-4 Deleting a Known Number of Rows...........4-5 Inserting Rows4-7 Updating Rows4-12 Database Privileges4-16 Displaying Table Privileges4-18 Data Integrity....................4-19 Entity Integrity4-19 Semantic Integrity.................4-20 Referential Integrity ................4-21 Object Modes and Violation Detection..........4-25 Interrupted Modifications ...............4-27 The Transaction..................4-28 Transaction Logging4-28 Specifying Transactions4-29 Backups and Logs4-30 Backing Up with INFORMIX-SE............4-30 Backing Up with INFORMIX-OnLine Dynamic Server ....4-31 Concurrency and Locks ................4-32 Data Replication...................4-33 INFORMIX-OnLine Dynamic Server Data Replication ....4-34 Summary .....................4-34 Chapter 5 Programming with SQL SQL in Programs5-4 SQL in SQL APIs .................5-4 SQL in Application Languages ............5-5 Static Embedding5-6 Dynamic Statements................5-6 Program Variables and Host Variables..........5-6 Table of Contents v Calling the Database Server .............. 5-9 The SQL Communications Area ........... 5-9 The SQLCODE Field ............... 5-12 The SQLERRD Array 5-13 The SQLWARN Array 5-13 The SQLSTATE Value 5-14 Retrieving Single Rows ................ 5-14 Data Type Conversion 5-16 Working with Null Data .............. 5-17 Dealing with Errors 5-18 Retrieving Multiple Rows ............... 5-20 Declaring a Cursor 5-21 Opening a Cursor 5-22 Fetching Rows.................. 5-22 Cursor Input Modes................ 5-24 The Active Set of a Cursor ............. 5-25 Using a Cursor: A Parts Explosion........... 5-27 Dynamic SQL ................... 5-30 Preparing a Statement............... 5-30 Executing Prepared SQL .............. 5-32 Dynamic Host Variables 5-34 Freeing Prepared Statements 5-34 Quick Execution................. 5-35 Embedding Data Definition Statements .......... 5-35 Embedding Grant and Revoke Privileges ........ 5-36 Summary..................... 5-38 Chapter 6 Modifying Data Through SQL Programs Using DELETE................... 6-3 Direct Deletions 6-4 Deleting with a Cursor............... 6-7 Using INSERT 6-9 Using an Insert Cursor 6-9 Rows of Constants ................ 6-12 An Insert Example 6-12 Using UPDATE 6-15 Using an Update Cursor .............. 6-15 Cleaning Up a Table 6-17 Summary..................... 6-18 vi Informix Guide to SQL: Tutorial Chapter 7 Programming for a Multiuser Environment Concurrency and Performance..............7-3 Locking and Integrity .................7-3 Locking and Performance ...............7-4 Concurrency Issues..................7-4 How Locks Work7-6 Kinds of Locks7-7 Lock Scope ...................7-7 The Duration of a Lock7-10 Locks While Modifying7-10 Setting the Isolation Level7-1 Comparing SET TRANSACTION with SET ISOLATION . . . 7-12 ANSI Read Uncommitted and Informix Dirty Read Isolation . . 7-13 ANSI Read Committed and Informix Committed Read Isolation . 7-14 Informix Cursor Stability Isolation ...........7-14 ANSI Serializable, ANSI Repeatable Read, and Informix Repeatable Read Isolation...............7-16 Controlling Data Modification with Access Modes .......7-17 Setting the Lock Mode.................7-18 Waiting for Locks7-18 Not W7-18 Waiting a Limited Time7-19 Handling a Deadlock................7-19 Handling External Deadlock .............7-20 Simple Concurrency7-20 Locking with Other Database Servers ...........7-21 Isolation While Reading...............7-22 Locking Updated Rows7-22 Hold Cursors ....................7-23 Summary .....................7-24 Section II Designing and Managing Databases Chapter 8 Building Your Data Model Why Build a Data Model ................8-3 Entity-Relationship Data-Model Overview ........8-4 Identifying and Defining Your Principal Data Objects ......8-5 Discovering Entities8-5 Defining the Relationships..............8-9 Identifying Attributes ...............8-17 Table of Contents vii Diagramming Your Data Objects............. 8-19 Translating E-R Data Objects into Relational Constructs..... 8-22 Rules for Defining Tables, Rows, and Columns ...... 8-23 Determining Keys for Tables 8-25 Resolving Your Relationships.............. 8-29 Normalizing Your Data Model 8-31 Summary..................... 8-36 Chapter 9 Implementing Your Data Model Defining the Domains ................ 9-3 Data Types................... 9-4 Null Values 9-25 Default Values.................. 9-25 Check Constraints 9-26 Creating the Database 9-26 Using CREATE DATABASE............. 9-27TE TABLE............... 9-30 Using Command Scripts .............. 9-32 Populating the Tables 9-33 Fragmenting Tables and Indexes 9-36 Creating a Fragmented Table 9-36 Fragmenting a New Table 9-37 Crable from Nonfragmented Tables . . 9-38 Modifying a Fragmented Table ............. 9-39 Modifying Fragmentation Strategies.......... 9-40 Dropping a Fragment ............... 9-42 Accessing Data Stored in Fragmented Tables ........ 9-43 Using Primary Keys Instead of Rowids ......... 9-43 Summary..................... 9-46 Chapter 10 Granting and Limiting Access to Your Database Controlling Access to Databases ............. 10-4 Securing Database Files .............. 10-4 Securing Confidential Data 10-5 Granting Privileges ................. 10-6 Database-Level Privileges 10-6 Ownership Rights ................ 10-8 Table-Level Privileges............... 10-9 Procedure-Level Privileges .............10-13 Automating Privileges10-14 viii Informix Guide to SQL: Tutorial Controlling Access to Data Using Stored Procedures ...... 10-19 Restricting Reads of Data .............. 10-20 Restricting Changes to Data ............. 10-21 Monitoring Changes to Data 10-21 Restricting Object Creation 10-23 Using Views .................... 10-24 Creating Views.................. 10-25 Modifying Through a View 10-28 Privileges and Views ................. 10-31 Privileges When Creating a View............ 10-31 Privileges When Using a View 10-32 Summary ..................... 10-34 Chapter 11 Understanding Informix Networking What Is a Network?..................1-4 Database Management System Configurations ........1-4 A Single-User Configuration .............1-5 A Local Multiuser Configuration............1-7 A Remote Configuration...............1-9 Single-Computer Configuration That Uses Network Communication 11-10 Distributed Databases11-12 Distributed Databases That Use Multiple Vendor Servers . . . 11-13 Connecting to Data on a UNIX Network ..........11-14 Example of Client/Server Connections11-15 Environment Variables11-16 Connection Information...............11-17 SQL Connection Statements .............11-17 Accessing Tables...................11-18 Using Synonyms with Table Names...........11-19 Synonym Chains .................11-20 Protecting Your Data in a Networked Environment.......11-21 Data Protection with INFORMIX-SE ..........11-21otection with INFORMIX-OnLine Dynamic Server . . . 11-21 Data Integrity for Distributed Data11-22 Summary .....................11-23 Table of Contents ix Section III Using Advanced SQL Chapter 12 Creating and Using Stored Procedures Introduction to Stored Procedures and SPL ......... 12-3 What You Can Do with Stored Procedures........ 12-4 Relationship Between SQL and a Stored Procedure..... 12-4 Creating and Using Stored Procedures........... 12-5 Creating a Procedure Using DB-Access 12-5 Cre in a Program 12-6 Commenting and Documenting a Procedure ....... 12-7 Diagnosing Compile-Time Errors 12-7 Looking at Compile-Time Warnings .......... 12-9 Generating the Text or Documentation ......... 12-9 Executing a Procedure...............12-10 Executing a Stored Procedure Dynamically........12-12 Debugging a Procedure ..............12-12 Privileges on Stored Procedures .............12-15 Privileges Necessary at Creation ...........12-16 Privileges Necessary at Execution12-16 Revoking Privileges................12-18 Variables and Expressions12-18 Variables....................12-18 SPL Expressions.................12-23 Program Flow Control12-24 Branching ...................12-24 Looping12-25 Function Handling ................12-25 Passing Information into and out of a Procedure .......12-27 Returning Results12-27 Exception Handling12-29 Trapping an Error and Recovering...........12-29 Scope of Control of an ON EXCEPTION Statement.....12-30 User-Generated Exceptions .............12-32 Summary.....................12-34 Chapter 13 Creating and Using Triggers When to Use Triggers................. 13-3 How to Create a Trigger................ 13-4 Assigning a Trigger Name ............. 13-5 Specifying the Trigger Event 13-5 Defining the Triggered Actions............ 13-6 A Complete CREATE TRIGGER Statement........ 13-7 x Informix Guide to SQL: Tutorial