📄 vcg05.htm
字号:
<HTML>
<HEAD>
<TITLE>vcg05.htm </TITLE>
<LINK REL="ToC" HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm">
<LINK REL="Index" HREF="htindex.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/htindex.htm">
<LINK REL="Next" HREF="vcg06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg06.htm">
<LINK REL="Previous" HREF="vcg04.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg04.htm"></HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">
<A NAME="I0"></A><P ALIGN=CENTER>
<A HREF="vcg04.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg04.htm" TARGET="_self"><IMG SRC="blanprev.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blanprev.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Previous Page"></A>
<A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm" TARGET="_self"><IMG SRC="blantoc.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantoc.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="TOC"></A>
<A HREF="vcg06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg06.htm" TARGET="_self"><IMG SRC="blannext.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blannext.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Next Page"></A>
<HR ALIGN=CENTER>
<P>
<UL>
<UL>
<UL>
<LI>
<A HREF="#E68E33" >Reviewing the Foundations of SQL</A>
<UL>
<LI>
<A HREF="#E69E116" >Elements of SQL Statements</A>
<LI>
<A HREF="#E69E117" >Differences Between SQL and Procedural Computer Languages</A>
<LI>
<A HREF="#E69E118" >Types of ANSI SQL</A></UL>
<LI>
<A HREF="#E68E34" >Writing ANSI SQL Statements</A>
<UL>
<LI>
<A HREF="#E69E119" >Categories of SQL Statements</A>
<LI>
<A HREF="#E69E120" >The Formal Grammar of SQL</A>
<LI>
<A HREF="#E69E121" >The Practical Grammar of a Simple SQL SELECT Statement</A>
<LI>
<A HREF="#E69E122" >Using the MS Query Application to Explore Queries</A>
<LI>
<A HREF="#E69E123" >SQL Operators and Expressions</A>
<UL>
<LI>
<A HREF="#E70E35" >Dyadic Arithmetic Operators and Functions</A>
<LI>
<A HREF="#E70E36" >Calculated Query Columns</A>
<LI>
<A HREF="#E70E37" >Monadic Text Operators, Null Value Predicates, and Functions</A></UL>
<LI>
<A HREF="#E69E124" >Joining Tables</A>
<UL>
<LI>
<A HREF="#E70E38" >Conventional Inner or Equi-Joins</A>
<LI>
<A HREF="#E70E39" >Multiple Equi-Joins</A>
<LI>
<A HREF="#E70E40" >OUTER JOINs</A>
<LI>
<A HREF="#E70E41" >Theta Joins and the DISTINCTROW Keyword</A>
<LI>
<A HREF="#E70E42" >Self-Joins and Composite Columns</A></UL>
<LI>
<A HREF="#E69E125" >SQL Aggregate Functions and the GROUP BY and HAVING Clauses</A></UL>
<LI>
<A HREF="#E68E35" >Comparing the Access SQL Dialect and ODBC</A>
<UL>
<LI>
<A HREF="#E69E126" >ANSI SQL Reserved Words and Access SQL Keywords</A>
<LI>
<A HREF="#E69E127" >Data Type Conversion Between ANSI SQL and Access SQL</A></UL>
<LI>
<A HREF="#E68E36" >Summary</A></UL></UL></UL>
<HR ALIGN=CENTER>
<H1 ALIGN=CENTER>
<CENTER>
<FONT SIZE=6 COLOR="#FF0000"><B>- 5 -</B>
<BR><B>Learning Structured Query Language</B></FONT></CENTER></H1>
<BR>
<P>Structured query language (SQL) is the <I>lingua franca</I> of relational database management systems. Visual C++ 4.0 and Microsoft Access both use SQL exclusively to process queries against desktop, client-server, and mainframe databases. Access includes a graphical query by example (QBE) tool—the query design mode window—to write Access SQL statements for you. You can develop quite sophisticated applications using Access without even looking at an SQL statement in Access's SQL window. Visual C++ 4.0 doesn't include a graphical QBE tool, so until some enterprising third-party developer creates a Query OLE Control, you'll need to learn Access SQL in order to create Visual C++ 4.0 applications that interact in any substantial way with databases.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>Microsoft Access without a version number refers to Access 2.0 and Access for Windows 95, version 7.0. There are no significant differences between Access SQL in these two versions of Access. Access 2.0 added a substantial number of reserved words to the SQL vocabulary of Access 1.x. SQL statements for adding tables to Access databases, plus adding fields and indexes to Access tables, are discussed in Chapter 8, "Running Crosstab and Action Queries." Microsoft calls the Visual C++ 4.0 dialect of SQL Microsoft Jet Database Engine SQL. This book uses the term Access SQL because the dialect originated in Access 1.0.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<P>The first part of this chapter introduces you to the standardized version of SQL specified by the American National Standards Institute (ANSI), a standard known as X.3.135-1992 and called SQL-92 in this book. (When you see the terms SQL-89 and SQL-92, the reference is to ANSI SQL, not the Access variety.) ANSI SQL-92 has been accepted by the International Standards Organization (ISO), a branch of the United Nations headquartered in Geneva, and the International Electrotechnical Commission (IEC) as ISO/IEC 9075:1992, "Database Language SQL." A separate ANSI standard, X.3.168-1989, defines "Database Language Embedded SQL." Thus, SQL-92 is a thoroughly standardized language, much more so than xBase, for which no independent standards yet exist. Today's client-server RDBMSs support SQL-89 and many of SQL-92's new SQL reserved words; many RDBMSs also add their own reserved words to create proprietary SQL dialects. A knowledge of ANSI SQL is required to use SQL pass-through techniques with the Jet 3.0 database engine and to employ Remote Data Objects (RDO) and Remote Data Control (RDC). SQL pass-through is described in Chapter 20, "Creating Front Ends for Client-Server Databases".
<BR>
<P>The second part of this chapter, beginning with the section "Comparing the Access SQL Dialect and ODBC," discusses the differences between SQL-92 and Access SQL. If you're fluent in the ANSI versions of SQL, either SQL-89 or SQL-92, you'll probably want to skip to the latter part of this chapter, which deals with the flavor of SQL used by the Jet 3.0 database engine. Chapter 7, "Using the Open Database Connectivity API," describes how the Jet 3.0 database engine translates Access SQL into the format used by ODBC drivers. Although this chapter describes the general SQL syntax for queries that modify data (called <I>action queries</I> by Access and in this book) and the crosstab queries of Access SQL, examples of the use of these types of queries are described in Chapter 8.
<BR>
<BR>
<A NAME="E68E33"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Reviewing the Foundations of SQL</B></FONT></CENTER></H3>
<BR>
<P>Dr. E. F. Codd's relational database model of 1970, discussed in the preceding chapter, was a theoretical description of how relational databases are designed, not how they're used. You need a database application language to create tables and specify the fields that the tables contain, establish relations between tables, and manipulate the data in the database. The first language that Dr. Codd and his associates at the IBM San Jose laboratory defined was Structured English Query Language (SEQUEL), which was designed for use with a prototype relational database that IBM called System R. The second version of SEQUEL was called SEQUEL/2. SEQUEL/2 was later renamed SQL. Technically, SQL is the name of an IBM data manipulation language, not an abbreviation for "structured query language." As you'll see later in this chapter, there are significant differences between IBM's SQL used for its DB2 mainframe and DB2/2 OS/2 databases, and ANSI SQL-92.
<BR>
<P>The sections that follow describe the differences between SQL and the procedural languages commonly used for computer programming, and how applications use SQL with desktop, client-server, and mainframe databases.
<BR>
<BR>
<A NAME="E69E116"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Elements of SQL Statements</B></FONT></CENTER></H4>
<BR>
<P>This book has made extensive use of the term <I>query</I> without defining what it means. Because Visual C++ 4.0 uses SQL to process all queries, this book defines <I>query</I> as an expression in any dialect of SQL that defines an operation to be performed by a database management system. A query usually contains at least the following three elements:
<BR>
<UL>
<LI>A <I>verb,</I> such as SELECT, that determines the type of operation
<BR>
<BR>
<LI>A <I>predicate object</I> that specifies one or more field names of one or more table object(s), such as * to specify all of the fields of a table
<BR>
<BR>
<LI>A <I>prepositional clause</I> that determines the object(s) in the database on which the verb acts, such as FROM <I>TableName</I>
<BR>
<BR>
</UL>
<P>The simplest SQL query that you can construct is SELECT * FROM <I>TableName</I><I>,</I> which returns the entire contents of <I>TableName</I> as the query result set. Queries are classified in this book as select queries, which return data (query result sets), or action queries, which modify the data contained in a database without returning any data.
<BR>
<P>IBM's original version of SQL, implemented as SEQUEL, had relatively few reserved words and simple syntax. Over the years, new reserved words have been added to the language by publishers of database management software. Many of the reserved words in proprietary versions of SQL have found their way into the ANSI SQL standards. Vendors of SQL RDBMSs that claim adherence to the ANSI standards have the option of adding their own reserved words to the language, as long as the added reserved words don't conflict with the usage of the ANSI-specified reserved words. Transact-SQL, the language used by the Microsoft and Sybase versions of SQL Server (both of these products were originally developed from the same product), has many more reserved words than conventional ANSI SQL. Transact-SQL even includes reserved words that allow conditional execution and loops within SQL statements. (The CASE, NULLIF, and COALESCE reserved words of SQL-92 are rather primitive for conditional execution purposes.) Access SQL includes the TRANSFORM and PIVOT statements needed to create crosstab queries that, while missing from ANSI SQL, are a very useful construct. TRANSFORM and PIVOT can be accomplished using ANSI SQL, but the construction of such an ANSI SQL statement would be quite difficult.
<BR>
<P>A further discussion of the details of the syntax of SQL statements appears after the following sections, which describe the basic characteristics of the SQL language and tell you how to combine SQL and conventional 3GL source code statements.
<BR>
<BR>
<A NAME="E69E117"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Differences Between SQL and Procedural Computer Languages</B></FONT></CENTER></H4>
<BR>
<P>All the dialects of SQL are fourth-generation languages (4GLs). The term <I>fourth-generation</I> derives from the following descriptions of the generations in the development of languages to control the operation of computers:
<BR>
<UL>
<LI><I>First-generation</I> languages required that you program in the binary language of the computer's hardware, called <I>object</I> or <I>machine code</I>. (The computer is the object in this case.) As an example, in the early days of mini- and microcomputers, you started (booted) the computer by setting a series of switches that sent instructions directly to the computer's CPU. Once you booted the computer, you could load binary-coded instructions with a paper tape reader. 1GLs represent programming the hard way. The first computer operating systems (OS) were written directly in machine code and loaded from paper tape or punched cards.
<BR>
<BR>
<LI><I>Second-generation</I> languages greatly improved the programming process by using assembly language to eliminate the necessity of setting individual bits of CPU instructions. Assembly language lets you use simple alphabetic codes—called <I>mnemonic</I> codes because they're easier to remember than binary instructions—and octal or hexadecimal values to substitute for one or more CPU instructions in the more arcane object code. Once you've written an assembly language program, you compile the assembly code into object code instructions that the CPU can execute. Microsoft's MASM is a popular assembly language compiler for Intel 80x86 CPUs. Assembly language remains widely used today when speed or direct access to the computer hardware is needed.
<BR>
<BR>
<LI><I>Third-generation</I> languages, typified by the early versions of FORTRAN (Formula Translator) and BASIC (Beginners' All-purpose Symbolic Instruction Code), let programmers substitute simple statements, usually in a structured version of English, for assembly code. 3GLs are called <I>procedural</I> languages because the statements you write in a 3GL are procedures that the computer executes in the sequence you specify in your program's source code. Theoretically, procedural languages should be independent of the type of CPU for which you compile your source code. Few 3GL languages actually achieve the goal of being fully platform-independent; most, such as Microsoft Visual Basic and Visual C++, are designed for 80x86 CPUs. (You can, however, run Visual C++ applications on Digital Equipment's Alpha workstations and workstations that use the MIPS RISC (Reduced Instruction Set Computer) CPU using Windows NT as the operating system. In this case, the operating system, Windows NT, handles the translation of object code to differing CPUs.)
<BR>
<BR>
<LI><I>Fourth-generation</I> languages are often called <I>nonprocedural</I> languages. The source code you write in 4GLs tells the computer the ultimate result you want, not how to achieve it. SQL is generally considered to be a 4GL language because, for example, your SQL query statements specify the data you want the database manager to send you, rather than instructions that tell the DBM how to accomplish this feat. Whether SQL is a true 4GL is subject to controversy, because the SQL statements you write are actually executed by a 3GL or, in some cases, a 2GL language that deals directly with the data stored in the database file(s) and is responsible for sending your application the data in a format that the application can understand.
<BR>
<BR>
</UL>
<P>Regardless of the controversy over whether generic SQL is a 4GL, you need to be aware of some other differences between SQL and conventional 3GLs. The most important of these differences are as follows:
<BR>
<UL>
<LI>SQL is a <I>set-oriented</I> language, whereas most 3GLs can be called <I>array-oriented</I> languages. SQL returns sets of data in a logical tabular format. The query-return sets are dependent on the data in the database, and you probably won't be able to predict the number of rows (data set members) that a query will return. The number of members of the data set may vary each time you execute a query and also may vary almost instantaneously in a multiuser environment. 3GLs can handle only a fixed number of tabular data elements at a time, specified by the dimensions that you assign to a two-dimensional array variable. Thus, the application needs to know how many columns and rows are contained in the result set of an SQL query so that the application can handle the data with row-by-row, column-by-column methods. Visual C++ 4.0's CRecordSet object handles this transformation for you automatically.
<BR>
<BR>
<LI>SQL is a <I>weakly typed</I> language, whereas most 3GLs are <I>strongly </I><I>typed</I>. You don't need to specify field data types in SQL statements; SQL queries return whatever data types have been assigned to the fields that constitute the columns of the query return set. Most compiled 3GL languages are strongly typed. COBOL, C, C++, Pascal, Modula-2, and ADA are examples of strongly typed compiled programming languages. Strongly typed languages require that you declare the names and data types of all your variables before you assign values to the variables. If the data type of a query column doesn't correspond to the data type you defined for the receiving variable, an error (sometimes called an <I>impedance mismatch error</I>) occurs. Visual C++ is a compiled language and is strongly typed.
<BR>
<BR>
</UL>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -