?? 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="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch13.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="wk2rev.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/wk2rev.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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 you
have learned so far. Today's lesson covers, in very broad strokes, practical applications
of SQL. We focus on applications in the Microsoft Windows environment, but the principles
involved are just as applicable to other software platforms. Today you will learn
the 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 SQL
skills.
<H2><FONT COLOR="#000077">A Quick Trip</FONT></H2>
<P>This section examines several commercial products in the context of the Microsoft
Windows 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, which
enables Windows-based programs to access a database through a driver. Rather than
having a custom interface to each database, something you might very well have to
write 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, which
enables you to write your program without regard for the printer. Individual differences,
which DOS programming forced you to address, are conveniently handled by the printer
driver. The result is that you spend your time working on the tasks peculiar to your
program, not on writing printer drivers.</P>
<P>ODBC applies this idea to databases. The visual part of ODBC resides in the control
panel in Windows 3.1, 3.11, and Windows 95 and in its own program group in Windows
NT.</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 personal
PC market. Don't be put off by the number of programs that Oracle7 installs--we built
all the examples used in the first several days using only the Oracle Database Manager
and SQL*Plus 3.3. SQL*Plus is shown in Figure 14.1.</P>
<P><A NAME="01"></A><A HREF="01-2.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/01-2.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 same
as 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 you
through the list of all the queries you make during a session.</P>
<P><A NAME="02"></A><A HREF="02-2.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/02-2.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 the
only 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 resource
editor.
<H3><FONT COLOR="#000077">Delphi</FONT></H3>
<P>The last tool we examine is Borland's Delphi, which is the subject of many new
books. Delphi provides a scalable interface to various databases.</P>
<P>Delphi has two programs that we use: the InterBase Server (Ibmgr) and the Windows
ISQL (Wisql).
<H2><FONT COLOR="#000077">Setting Up</FONT></H2>
<P>Enough with the introductions--let's get to work. After you install your SQL engine
or your ODBC-compatible compiler, you must do a certain amount of stage setting before
the stars can do their stuff. With both Oracle7 and InterBase, you need to log on
and create an account for yourself. The procedures are essentially the same. The
hardest part is sorting through the hard copy and online documentation for the default
passwords. Both systems have a default system administrator account. (See Figure
14.3.)</P>
<P><A NAME="03"></A><A HREF="03-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/03-1.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 to
start up the database you want to use. Figure 14.4 shows Oracle7's stoplight visual
metaphor.</P>
<P><A NAME="04"></A><A HREF="04-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/04-1.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 in
Figure 14.5.</P>
<P><A NAME="05"></A><A HREF="05-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/05-1.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 entering
data is with a script file. A script file is usually a text file with the SQL commands
typed out in the proper order. Look at this excerpt from a script file delivered
with 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 RDBMS
create 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 you
are using, so be sure to check your documentation. Select File | Open to load this
script into your SQL engine.</P>
<P>Borland's InterBase loads data in a similar way. The following excerpt is from
one 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 country
into 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 NONE
BIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE
LE SHOPPE HOMETOWN KS 54678 555-1278 NONE
AAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGR
JACKS BIKE 24 EGLIN FL 34567 555-2314 NONE</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -