?? ch01.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 1 -- Introduction to 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="../wk1ag/wk1ag.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch02/ch02.htm"><IMG
SRC="../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 1 - <BR>
Introduction to SQL</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077"><B>A Brief History of SQL</B></FONT></H2>
<P>The history of SQL begins in an IBM laboratory in San Jose, California, where
SQL was developed in the late 1970s. The initials stand for Structured Query Language,
and the language itself is often referred to as "sequel." It was originally
developed for IBM's DB2 product (a relational database management system, or RDBMS,
that can still be bought today for various platforms and environments). In fact,
SQL makes an RDBMS possible. SQL is a nonprocedural language, in contrast to the
procedural or third-generation languages (3GLs) such as COBOL and C that had been
created up to that time.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><I><B> </B>Nonprocedural</I> means <I>what</I>
rather than <I>how</I>. For example, SQL describes what data to retrieve, delete,
or insert, rather than how to perform the operation.
<HR>
</BLOCKQUOTE>
<P>The characteristic that differentiates a DBMS from an RDBMS is that the RDBMS
provides a set-oriented database language. For most RDBMSs, this set-oriented database
language is SQL. <I>Set oriented</I> means that SQL processes sets of data in groups.</P>
<P>Two standards organizations, the American National Standards Institute (ANSI)
and the International Standards Organization (ISO), currently promote SQL standards
to industry. The ANSI-92 standard is the standard for the SQL used throughout this
book. Although these standard-making bodies prepare standards for database system
designers to follow, all database products differ from the ANSI standard to some
degree. In addition, most systems provide some proprietary extensions to SQL that
extend the language into a true procedural language. We have used various RDBMSs
to prepare the examples in this book to give you an idea of what to expect from the
common database systems. (We discuss procedural SQL--known as PL/SQL--on Day 18,
"PL/SQL: An Introduction," and Transact-SQL on Day 19, "Transact-SQL:
An Introduction.")
<H2><FONT COLOR="#000077"><B>A Brief History of Databases</B></FONT></H2>
<P>A little background on the evolution of databases and database theory will help
you understand the workings of SQL. Database systems store information in every conceivable
business environment. From large tracking databases such as airline reservation systems
to a child's baseball card collection, database systems store and distribute the
data that we depend on. Until the last few years, large database systems could be
run only on large mainframe computers. These machines have traditionally been expensive
to design, purchase, and maintain. However, today's generation of powerful, inexpensive
workstation computers enables programmers to design software that maintains and distributes
data quickly and inexpensively.
<H3><FONT COLOR="#000077"><B>Dr. Codd's 12 Rules for a Relational Database Model</B></FONT></H3>
<P>The most popular data storage model is the relational database, which grew from
the seminal paper "A Relational Model of Data for Large Shared Data Banks,"
written by Dr. E. F. Codd in 1970. SQL evolved to service the concepts of the relational
database model. Dr. Codd defined 13 rules, oddly enough referred to as Codd's 12
Rules, for the relational model:
<DL>
<DD><B>0. </B>A relational DBMS must be able to manage databases entirely through
its relational capabilities.<BR>
<BR>
<B>1.</B> Information rule-- All information in a relational database (including
table and column names) is represented explicitly as values in tables.<BR>
<BR>
<B>2.</B> Guaranteed access--Every value in a relational database is guaranteed to
be accessible by using a combination of the table name, primary key value, and column
name.<BR>
<BR>
<B>3. </B>Systematic null value support--The DBMS provides systematic support for
the treatment of null values (unknown or inapplicable data), distinct from default
values, and independent of any domain.<BR>
<BR>
<B>4. </B>Active, online relational catalog--The description of the database and
its contents is represented at the logical level as tables and can therefore be queried
using the database language.<BR>
<BR>
<B>5. </B>Comprehensive data sublanguage--At least one supported language must have
a well-defined syntax and be comprehensive. It must support data definition, manipulation,
integrity rules, authorization, and transactions.<BR>
<BR>
<B>6. </B>View updating rule--All views that are theoretically updatable can be updated
through the system.<BR>
<BR>
<B>7. </B>Set-level insertion, update, and deletion--The DBMS supports not only set-level
retrievals but also set-level inserts, updates, and deletes.<BR>
<BR>
<B>8.</B> Physical data independence--Application programs and ad hoc programs are
logically unaffected when physical access methods or storage structures are altered.<BR>
<BR>
<B>9. </B>Logical data independence--Application programs and ad hoc programs are
logically unaffected, to the extent possible, when changes are made to the table
structures.<BR>
<BR>
<B>10.</B> Integrity independence--The database language must be capable of defining
integrity rules. They must be stored in the online catalog, and they cannot be bypassed.<BR>
<BR>
<B>11. </B>Distribution independence--Application programs and ad hoc requests are
logically unaffected when data is first distributed or when it is redistributed.<BR>
<BR>
<B>12.</B> Nonsubversion--It must not be possible to bypass the integrity rules defined
through the database language by using lower-level languages.
</DL>
<P>Most databases have had a "parent/child" relationship; that is, a parent
node would contain file pointers to its children. (See Figure 1.1.)</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 1.1.</B></A><B><BR>
</B><I>Codd's relational database management system.</I></P>
<P>This method has several advantages and many disadvantages. In its favor is the
fact that the physical structure of data on a disk becomes unimportant. The programmer
simply stores pointers to the next location, so data can be accessed in this manner.
Also, data can be added and deleted easily. However, different groups of information
could not be easily joined to form new information. The format of the data on the
disk could not be arbitrarily changed after the database was created. Doing so would
require the creation of a new database structure.</P>
<P>Codd's idea for an RDBMS uses the mathematical concepts of relational algebra
to break down data into sets and related common subsets.</P>
<P>Because information can naturally be grouped into distinct sets, Dr. Codd organized
his database system around this concept. Under the relational model, data is separated
into sets that resemble a table structure. This table structure consists of individual
data elements called columns or fields. A single set of a group of fields is known
as a record or row. For instance, to create a relational database consisting of employee
data, you might start with a table called <TT>EMPLOYEE</TT> that contains the following
pieces of information: <TT>Name</TT>, <TT>Age</TT>, and <TT>Occupation</TT>. These
three pieces of data make up the fields in the <TT>EMPLOYEE</TT> table, shown in
Table 1.1.
<H4><FONT COLOR="#000077"><B>Table 1.1. The </B>EMPLOYEE<B> table.</B></FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><B>Name</B></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><B>Age</B></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><B>Occupation</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Will Williams</TD>
<TD ALIGN="LEFT" VALIGN="TOP">25</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Electrical engineer</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Dave Davidson</TD>
<TD ALIGN="LEFT" VALIGN="TOP">34</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Museum curator</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Jan Janis</TD>
<TD ALIGN="LEFT" VALIGN="TOP">42</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Chef</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Bill Jackson</TD>
<TD ALIGN="LEFT" VALIGN="TOP">19</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Student</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Don DeMarco</TD>
<TD ALIGN="LEFT" VALIGN="TOP">32</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Game programmer</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Becky Boudreaux</TD>
<TD ALIGN="LEFT" VALIGN="TOP">25</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Model</TD>
</TR>
</TABLE>
</P>
<P>The six rows are the records in the <TT>EMPLOYEE</TT> table. To retrieve a specific
record from this table, for example, Dave Davidson, a user would instruct the database
management system to retrieve the records where the <TT>NAME</TT> field was equal
to Dave Davidson. If the DBMS had been instructed to retrieve all the fields in the
record, the employee's name, age, and occupation would be returned to the user. SQL
is the language that tells the database to retrieve this data. A sample SQL statement
that makes this query is</P>
<PRE><FONT COLOR="#0066FF">SELECT *
FROM EMPLOYEE
</FONT></PRE>
<P>Remember that the exact syntax is not important at this point. We cover this topic
in much greater detail beginning tomorrow.</P>
<P>Because the various data items can be grouped according to obvious relationships
(such as the relationship of <TT>Employee Name</TT> to <TT>Employee Age</TT>), the
relational database model gives the database designer a great deal of flexibility
to describe the relationships between the data elements. Through the mathematical
concepts of join and union, relational databases can quickly retrieve pieces of data
from different sets (tables) and return them to the user or program as one "joined"
collection of data. (See Figure 1.2.) The join feature enables the designer to store
sets of information in separate tables to reduce repetition.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 1.2.</B></A><B><BR>
</B><I>The join feature.</I></P>
<P>Figure 1.3 shows a union. The union would return only data common to both sources.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 1.3.</B></A><B><BR>
</B><I>The union feature.</I></P>
<P>Here's a simple example that shows how data can be logically divided between two
tables. Table 1.2 is called <TT>RESPONSIBILITIES</TT> and contains two fields: <TT>NAME</TT>
and <TT>DUTIES</TT>.
<H4><FONT COLOR="#000077"><B>Table 1.2. The </B>RESPONSIBILITIES<B> table.</B></FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Name</B></TD>
<TD ALIGN="LEFT"><B>Duties</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Becky Boudreaux</TD>
<TD ALIGN="LEFT">Smile</TD>
</TR>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -