📄 ch14.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 14 -- Dynamic Uses of SQL</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><A HREF="../ch13/ch13.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../wk2rev/wk2rev.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Day 14 -<BR>Dynamic Uses of SQL</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>The purpose of today's lesson is to show you where to start to apply what youhave learned so far. Today's lesson covers, in very broad strokes, practical applicationsof SQL. We focus on applications in the Microsoft Windows environment, but the principlesinvolved are just as applicable to other software platforms. Today you will learnthe following:<UL> <LI>How various commercial products--Personal Oracle7, open database connectivity (ODBC), InterBase ISQL, Microsoft's Visual C++, and Borland's Delphi--relate to SQL <P> <LI>How to set up your environment for SQL <P> <LI>How to create a database using Oracle7, Microsoft Query, and InterBase ISQL <P> <LI>How to use SQL inside applications written in Visual C++ and Delphi</UL><P>After reading this material, you will know where to start applying your new SQLskills.<H2><FONT COLOR="#000077">A Quick Trip</FONT></H2><P>This section examines several commercial products in the context of the MicrosoftWindows operating system and briefly describes how they relate to SQL. The principles,if not the products themselves, apply across various software platforms.<H3><FONT COLOR="#000077">ODBC</FONT></H3><P>One of the underlying technologies in the Windows operating system is ODBC, whichenables Windows-based programs to access a database through a driver. Rather thanhaving a custom interface to each database, something you might very well have towrite yourself, you can connect to the database of your choice through a driver.The concept of ODBC is very similar to the concept of Windows printer drivers, whichenables you to write your program without regard for the printer. Individual differences,which DOS programming forced you to address, are conveniently handled by the printerdriver. The result is that you spend your time working on the tasks peculiar to yourprogram, not on writing printer drivers.</P><P>ODBC applies this idea to databases. The visual part of ODBC resides in the controlpanel in Windows 3.1, 3.11, and Windows 95 and in its own program group in WindowsNT.</P><P>We cover ODBC in more detail when we discuss creating the database later today.<H3><FONT COLOR="#000077">Personal Oracle7</FONT></H3><P>Personal Oracle7 is the popular database's latest incursion into the personalPC market. Don't be put off by the number of programs that Oracle7 installs--we builtall the examples used in the first several days using only the Oracle Database Managerand SQL*Plus 3.3. SQL*Plus is shown in Figure 14.1.</P><P><A NAME="01"></A><A HREF="01.htm"><B>Figure 14.1.</B></A></P><P><I>Oracle7's SQL*Plus.</I></P><P><H3><FONT COLOR="#000077">INTERBASE SQL (ISQL)</FONT></H3><P>The tool used in the other examples is Borland's ISQL. It is essentially the sameas Oracle7 except that Oracle7 is character oriented and ISQL is more Windows-like.</P><P>An ISQL screen is shown in Figure 14.2. You type your query in the top edit box,and the result appears in the lower box. The Previous and Next buttons scroll youthrough the list of all the queries you make during a session.</P><P><A NAME="02"></A><A HREF="02.htm"><B>Figure 14.2.</B></A></P><P><I>InterBase's Interactive SQL.</I></P><P><H3><FONT COLOR="#000077">Visual C++</FONT></H3><P>Dozens of books have been written about Visual C++. For the examples in this book,we used version 1.52. The procedures we used are applicable to the 32-bit version,C++ 2.0. It is used here because of its simple interface with ODBC. It is not theonly compiler with the capability to connect to ODBC. If you use a different compiler,this section provides a good point of departure.</P><P>Visual C++ installs quite a few tools. We use only two: the compiler and the resourceeditor.<H3><FONT COLOR="#000077">Delphi</FONT></H3><P>The last tool we examine is Borland's Delphi, which is the subject of many newbooks. Delphi provides a scalable interface to various databases.</P><P>Delphi has two programs that we use: the InterBase Server (Ibmgr) and the WindowsISQL (Wisql).<H2><FONT COLOR="#000077">Setting Up</FONT></H2><P>Enough with the introductions--let's get to work. After you install your SQL engineor your ODBC-compatible compiler, you must do a certain amount of stage setting beforethe stars can do their stuff. With both Oracle7 and InterBase, you need to log onand create an account for yourself. The procedures are essentially the same. Thehardest part is sorting through the hard copy and online documentation for the defaultpasswords. Both systems have a default system administrator account. (See Figure14.3.)</P><P><A NAME="03"></A><A HREF="03.htm"><B>Figure 14.3.</B></A></P><P><I>InterBase Security manager screen.</I></P><P>After logging on and creating an account, you are ready to create the database.<H2><FONT COLOR="#000077">Creating the Database</FONT></H2><P>This step is where all your SQL training starts to pay off. First, you have tostart up the database you want to use. Figure 14.4 shows Oracle7's stoplight visualmetaphor.</P><P><A NAME="04"></A><A HREF="04.htm"><B>Figure 14.4.</B></A></P><P><I>Oracle7 Database Manager.</I></P><P>After you get the green light, you can open up the SQL*Plus 3.3 tool shown inFigure 14.5.</P><P><A NAME="05"></A><A HREF="05.htm"><B>Figure 14.5.</B></A></P><P><I>Oracle SQL*Plus.</I></P><P>At this point you can create your tables and enter your data using the <TT>CREATE</TT>and <TT>INSERT</TT> keywords. Another common way of creating tables and enteringdata is with a script file. A script file is usually a text file with the SQL commandstyped out in the proper order. Look at this excerpt from a script file deliveredwith Oracle7:</P><PRE><FONT COLOR="#0066FF">-------------------------------------------------------------- Script to build seed database for Personal Oracle------------------------------------------------------------ NTES Called from buildall.sql-- MODIFICATIONS-- rs 12/04/94 - Comment, clean up, resize, for production------------------------------------------------------------startup nomount pfile=%rdbms71%\init.ora-- Create database for Windows RDBMScreate database oracle controlfile reuse logfile '%oracle_home%\dbs\wdblog1.ora' size 400K reuse, '%oracle_home%\dbs\wdblog2.ora' size 400K reuse datafile '%oracle_home%\dbs\wdbsys.ora' size 10M reuse character set WE8ISO8859P1;</FONT></PRE><P>The syntax varies slightly with the implementation of SQL and the database youare using, so be sure to check your documentation. Select File | Open to load thisscript into your SQL engine.</P><P>Borland's InterBase loads data in a similar way. The following excerpt is fromone of the files to insert data:</P><PRE><FONT COLOR="#0066FF">/* * Add countries.*/INSERT INTO country (country, currency) VALUES ('USA', 'Dollar');INSERT INTO country (country, currency) VALUES ('England', 'Pound');INSERT INTO country (country, currency) VALUES ('Canada', 'CdnDlr');INSERT INTO country (country, currency) VALUES ('Switzerland', 'SFranc');INSERT INTO country (country, currency) VALUES ('Japan', 'Yen');INSERT INTO country (country, currency) VALUES ('Italy', 'Lira');INSERT INTO country (country, currency) VALUES ('France', 'FFranc');INSERT INTO country (country, currency) VALUES ('Germany', 'D-Mark');INSERT INTO country (country, currency) VALUES ('Australia', 'ADollar');INSERT INTO country (country, currency) VALUES ('Hong Kong', 'HKDollar');INSERT INTO country (country, currency) VALUES ('Netherlands', 'Guilder');INSERT INTO country (country, currency) VALUES ('Belgium', 'BFranc');INSERT INTO country (country, currency) VALUES ('Austria', 'Schilling');INSERT INTO country (country, currency) VALUES ('Fiji', 'fdollar');</FONT></PRE><H5>ANALYSIS:</H5><P>This example inserts a country name and the type currency used in that countryinto the <TT>COUNTRY</TT> table. (Refer to Day 8, "Manipulating Data,"for an introduction to the <TT>INSERT</TT> command.)</P><P>There is nothing magic here. Programmers always find ways to save keystrokes.If you are playing along at home, enter the following tables:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">/* Table: CUSTOMER, Owner: PERKINS */<B>CREATE TABLE CUSTOMER (NAME CHAR(10), ADDRESS CHAR(10), STATE CHAR(2), ZIP CHAR(10), PHONE CHAR(11), REMARKS CHAR(10));</B></FONT></PRE><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">/* Table: ORDERS, Owner: PERKINS */<B>CREATE TABLE ORDERS (ORDEREDON DATE, NAME CHAR(10), PARTNUM INTEGER, QUANTITY INTEGER, REMARKS CHAR(10));</B></FONT></PRE><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">/* Table: PART, Owner: PERKINS */<B>CREATE TABLE PART (PARTNUM INTEGER, DESCRIPTION CHAR(20), PRICE NUMERIC(9, 2));</B></FONT></PRE><P>Now fill these tables with the following data:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT * FROM CUSTOMER</B>NAME ADDRESS STATE ZIP PHONE REMARKS========== ========== ====== ====== ======== ==========TRUE WHEEL 55O HUSKER NE 58702 555-4545 NONEBIKE SPEC CPT SHRIVE LA 45678 555-1234 NONELE SHOPPE HOMETOWN KS 54678 555-1278 NONEAAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGRJACKS BIKE 24 EGLIN FL 34567 555-2314 NONE</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"><B>SELECT * FROM ORDERS</B> ORDEREDON NAME PARTNUM QUANTITY REMARKS =========== ========== =========== =========== =======15-MAY-1996 TRUE WHEEL 23 6 PAID19-MAY-1996 TRUE WHEEL 76 3 PAID 2-SEP-1996 TRUE WHEEL 10 1 PAID30-JUN-1996 TRUE WHEEL 42 8 PAID30-JUN-1996 BIKE SPEC 54 10 PAID30-MAY-1996 BIKE SPEC 10 2 PAID30-MAY-1996 BIKE SPEC 23 8 PAID17-JAN-1996 BIKE SPEC 76 11 PAID17-JAN-1996 LE SHOPPE 76 5 PAID 1-JUN-1996 LE SHOPPE 10 3 PAID 1-JUN-1996 AAA BIKE 10 1 PAID 1-JUL-1996 AAA BIKE 76 4 PAID 1-JUL-1996 AAA BIKE 46 14 PAID11-JUL-1996 JACKS BIKE 76 14 PAID</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"><B>SELECT * FROM PART</B> PARTNUM DESCRIPTION PRICE=========== ==================== =========== 54 PEDALS 54.25 42 SEATS 24.50 46 TIRES 15.25 23 MOUNTAIN BIKE 350.45 76 ROAD BIKE 530.00 10 TANDEM 1200.00</FONT></PRE><P>After you enter this data, the next step is to create an ODBC connection. Openthe Control Panel (if you are in Win 3.1, 3.11, or Windows 95) and double-click theODBC icon.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Several flavors of SQL engines load ODBC. Visual C++, Delphi, and Oracle7 load ODBC as part of their setup. Fortunately, ODBC is becoming as common as printer drivers. <HR></BLOCKQUOTE><P>The initial ODBC screen is shown in Figure 14.6.</P><P><A NAME="06"></A><A HREF="06.htm"><B>Figure 14.6.</B></A></P><P><I>ODBC's Data Sources selection.</I></P><P>This screen shows the current ODBC connections. You want to create a new connection.Assuming you used InterBase and called the new database TYSSQL (give yourself 10bonus points if you know what TYSSQL stands for), press the Add button and selectthe InterBase Driver, as shown in Figure 14.7.</P><P><A NAME="07"></A><A HREF="07.htm"><B>Figure 14.7.</B></A></P><P><I>Driver selection.</I></P><P>From this selection you move to the setup screen. Fill it in as shown in Figure14.8.</P><P><A NAME="08"></A><A HREF="08.htm"><B>Figure 14.8.</B></A></P><P><I>Driver setup.</I></P><P>You can use your own name or something short and easy to type, depending on theaccount you set up for yourself. The only tricky bit here, at least for us, was figuringout what InterBase wanted as a database name. Those of you coming from a PC or smalldatabase background will have to get used to some odd-looking pathnames. These pathnamestell the SQL engine where to look for the database in the galaxy of computers thatcould be connected via LANs.<H2><FONT COLOR="#000077">Using Microsoft Query to Perform a Join</FONT></H2><P>Now that you have made an ODBC connection, we need to make a slight detour toa rather useful tool called Microsoft Query. This program is loaded along with VisualC++. We have used it to solve enough database and coding problems to pay for thecost of the compiler several times over. Query normally installs itself in its ownprogram group. Find it and open it. It should look like Figure 14.9.</P><P><A NAME="09"></A><A HREF="09.htm"><B>Figure 14.9.</B></A></P><P><I>Microsoft Query.</I></P><P>Select File | New Query. Your TYSSQL ODBC link does not appear, so click the Otherbutton to bring up the ODBC Data Sources dialog box, shown in Figure 14.10, and selectTYSSQL.</P><P><A NAME="10"></A><A HREF="10.htm"><B>Figure 14.10.</B></A></P><P><I>Data Sources dialog box.</I></P><P>Click OK to return to the Select Data Source dialog box. Select TYSSQL and clickUse, as shown in Figure 14.11.</P><P><A NAME="11"></A><A HREF="11.htm"><B>Figure 14.11.</B></A></P><P><I>Select Data Source dialog box.</I></P><P>Again, small database users aren't accustomed to logging on. Nevertheless, typeyour password to move through the screen.</P><P>The Add Tables dialog box, shown in Figure 14.12, presents the tables associatedwith the database to which you are connected. Select <TT>PART</TT>, <TT>ORDERS</TT>,and <TT>CUSTOMER</TT>, and click Close.</P><P><A NAME="12"></A><A HREF="12.htm"><B>Figure 14.12.</B></A></P><P><I>Selecting tables in Query.</I></P><P>Your screen should look like Figure 14.13. Double-click <TT>ADDRESS</TT> and <TT>NAME</TT>from the <TT>CUSTOMER</TT> table. Then double-click <TT>ORDEREDON</TT> and <TT>PARTNUM</TT>from <TT>ORDERS</TT>.</P><P><A NAME="13"></A><A HREF="13.htm"><B>Figure 14.13.</B></A></P><P><I>Visual representation of a table in Query.</I></P><P>Now for some magic! Click the button marked SQL in the toolbar. Your screen shouldnow look like Figure 14.14.</P><P><A NAME="14"></A><A HREF="14.htm"><B>Figure 14.14.</B></A></P><P><I>The query that Query built.</I></P><P>This tool has two functions. The first is to check the ODBC connection. If itworks here, it should work in the program. This step can help you determine whether⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -