?? ch09.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 9 -- Creating and Maintaining Tables</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="ch08.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch08.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="ch10.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch10.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 9 -<BR>
Creating and Maintaining Tables</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>Today you learn about creating databases. Day 9 covers the <TT>CREATE DATABASE</TT>,
<TT>CREATE TABLE</TT>, <TT>ALTER TABLE</TT>, <TT>DROP TABLE</TT>, and <TT>DROP DATABASE</TT>
statements, which are collectively known as data definition statements. (In contrast,
the <TT>SELECT</TT>, <TT>UPDATE</TT>, <TT>INSERT</TT>, and <TT>DELETE</TT> statements
are often described as data manipulation statements.) By the end of the day, you
will understand and be able to do the following:
<UL>
<LI>Create key fields
<P>
<LI>Create a database with its associated tables
<P>
<LI>Create, alter, and drop a table
<P>
<LI>Add data to the database
<P>
<LI>Modify the data in a database
<P>
<LI>Drop databases
</UL>
<P>You now know much of the SQL vocabulary and have examined the SQL query in some
detail, beginning with its basic syntax. On Day 2, "Introduction to the Query:
The <TT>SELECT</TT> Statement," you learned how to select data from the database.
On Day 8, "Manipulating Data," you learned how to insert, update, and delete
data from the database. Now, nine days into the learning process, you probably have
been wondering just where these databases come from. For simplicity's sake, we have
been ignoring the process of creating databases and tables. We have assumed that
these data objects existed currently on your system. Today you finally create these
objects.</P>
<P>The syntax of the <TT>CREATE</TT> statements can range from the extremely simple
to the complex, depending on the options your database management system (DBMS) supports
and how detailed you want to be when building a database.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The examples used today were generated
using Personal Oracle7. Please see the documentation for your specific SQL implementation
for any minor differences in syntax.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">The CREATE DATABASE Statement</FONT></H2>
<P>The first data management step in any database project is to create the database.
This task can range from the elementary to the complicated, depending on your needs
and the database management system you have chosen. Many modern systems (including
Personal Oracle7) include graphical tools that enable you to completely build the
database with the click of a mouse button. This time-saving feature is certainly
helpful, but you should understand the SQL statements that execute in response to
the mouse clicks.</P>
<P>Through personal experience, we have learned the importance of creating a good
SQL install script. This script file contains the necessary SQL code to completely
rebuild a database or databases; the script often includes database objects such
as indexes, stored procedures, and triggers. You will see the value of this script
during development as you continually make changes to the underlying database and
on occasion want to completely rebuild the database with all the latest changes.
Using the graphical tools each time you need to perform a rebuild can become extremely
time-consuming. In addition, knowing the SQL syntax for this procedure enables you
to apply your knowledge to other database systems.</P>
<P>The syntax for the typical <TT>CREATE DATABASE</TT> statement looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE DATABASE database_name
</FONT></PRE>
<P>Because the syntax varies so widely from system to system, we will not expand
on the <TT>CREATE DATABASE</TT> statement's syntax. Many systems do not even support
an SQL <TT>CREATE DATABASE</TT> command. However, all the popular, more powerful,
relational database management systems (RDBMSs) do provide it. Instead of focusing
on its syntax, we will spend some time discussing the options to consider when creating
a database.
<H3><FONT COLOR="#000077">CREATE DATABASE Options</FONT></H3>
<P>The syntax for the <TT>CREATE DATABASE</TT> statement can vary widely. Many SQL
texts skip over the <TT>CREATE DATABASE</TT> statement and move directly on to the
<TT>CREATE TABLE</TT> statement. Because you must create a database before you can
build a table, this section focuses on some of the concepts a developer must consider
when building a database. The first consideration is your level of permission. If
you are using a relational database management system (RDBMS) that supports user
permissions, you must make sure that either you have system administrator-level permission
settings or the system administrator has granted you <TT>CREATE DATABASE</TT> permission.
Refer to your RDBMS documentation for more information.</P>
<P>Most RDBMSs also allow you to specify a default database size, usually in terms
of hard disk space (such as megabytes). You will need to understand how your database
system stores and locates data on the disk to accurately estimate the size you need.
The responsibility for managing this space falls primarily to system administrators,
and possibly at your location a database administrator will build you a test database.</P>
<P>Don't let the <TT>CREATE DATABASE</TT> statement intimidate you. At its simplest,
you can create a database named <TT>PAYMENTS</TT> with the following statement:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>CREATE DATABASE PAYMENTS;</B>
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Again, be sure to consult your database
management system's documentation to learn the specifics of building a database,
as the <TT>CREATE DATABASE</TT> statement can and does vary for the different implementations.
Each implementation also has some unique options.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">Database Design</FONT></H3>
<P>Designing a database properly is extremely important to the success of your application.
The introductory material on Day 1, "Introduction to SQL," touched on the
topics of relational database theory and database normalization.</P>
<P><I>Normalization</I> is the process of breaking your data into separate components
to reduce the repetition of data. Each level of normalization reduces the repetition
of data. Normalizing your data can be an extremely complex process, and numerous
database design tools enable you to plan this process in a logical fashion.</P>
<P>Many factors can influence the design of your database, including the following:
<UL>
<LI>Security
<P>
<LI>Disk space available
<P>
<LI>Speed of database searches and retrievals
<P>
<LI>Speed of database updates
<P>
<LI>Speed of multiple-table joins to retrieve data
<P>
<LI>RDBMS support for temporary tables
</UL>
<P>Disk space is always an important factor. Although you may not think that disk
space is a major concern in an age of multigigabyte storage, remember that the bigger
your database is, the longer it takes to retrieve records. If you have done a poor
job of designing your table structure, chances are that you have needlessly repeated
much of your data.</P>
<P>Often the opposite problem can occur. You may have sought to completely normalize
your tables' design with the database and in doing so created many tables. Although
you may have approached database-design nirvana, any query operations done against
this database may take a very long time to execute. Databases designed in this manner
are sometimes difficult to maintain because the table structure might obscure the
designer's intent. This problem underlines the importance of always documenting your
code or design so that others can come in after you (or work with you) and have some
idea of what you were thinking at the time you created your database structure. In
database designer's terms, this documentation is known as a data dictionary.
<H3><FONT COLOR="#000077">Creating a Data Dictionary</FONT></H3>
<P>A data dictionary is the database designer's most important form of documentation.
It performs the following functions:
<UL>
<LI>Describes the purpose of the database and who will be using it.
<P>
<LI>Documents the specifics behind the database itself: what device it was created
on, the database's default size, or the size of the log file (used to store database
operations information in some RDBMSs).
<P>
<LI>Contains SQL source code for any database install or uninstall scripts, including
documentation on the use of import/export tools, such as those introduced yesterday
(Day 8).
<P>
<LI>Provides a detailed description of each table within the database and explains
its purpose in business process terminology.
<P>
<LI>Documents the internal structure of each table, including all fields and their
data types with comments, all indexes, and all views. (See Day 10, "Creating
Views and Indexes.")
<P>
<LI>Contains SQL source code for all stored procedures and triggers.
<P>
<LI>Describes database constraints such as the use of unique values or <TT>NOT NULL</TT>
values. The documentation should also mention whether these constraints are enforced
at the RDBMS level or whether the database programmer is expected to check for these
constraints within the source code.
</UL>
<P>Many computer-aided software engineering (CASE) tools aid the database designer
in the creation of this data dictionary. For instance, Microsoft Access comes prepackaged
with a database documenting tool that prints out a detailed description of every
object in the database. See Day 17, "Using SQL to Generate SQL Statements,"
for more details on the data dictionary.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Most of the major RDBMS packages
come with either the data dic-tionary installed or scripts to install it.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">Creating Key Fields</FONT></H3>
<P>Along with documenting your database design, the most important design goal you
should have is to create your table structure so that each table has a primary key
and a foreign key. The primary key should meet the following goals:
<UL>
<LI>Each record is unique within a table (no other record within the table has all
of its columns equal to any other).
<P>
<LI>For a record to be unique, all the columns are necessary; that is, data in one
column should not be repeated anywhere else in the table.
</UL>
<P>Regarding the second goal, the column that has completely unique data throughout
the table is known as the <I>primary key field</I>. A <I>foreign key field</I> is
a field that links one table to another table's primary or foreign key. The following
example should clarify this situation.</P>
<P>Assume you have three tables: <TT>BILLS</TT>, <TT>BANK_ACCOUNTS</TT>, and <TT>COMPANY</TT>.
Table 9.1 shows the format of these three tables.
<H4><FONT COLOR="#000077">Table 9.1. Table structure for the PAYMENTS database.</FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Bills</B></TD>
<TD ALIGN="LEFT"><B>Bank_Accounts</B></TD>
<TD ALIGN="LEFT"><B>Company</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>NAME, CHAR(30)</TT></TD>
<TD ALIGN="LEFT"><TT>ACCOUNT_ID, NUMBER</TT></TD>
<TD ALIGN="LEFT"><TT>NAME, CHAR(30)</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>AMOUNT, NUMBER</TT></TD>
<TD ALIGN="LEFT"><TT>TYPE, CHAR(30)</TT></TD>
<TD ALIGN="LEFT"><TT>ADDRESS, CHAR(50)</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>ACCOUNT_ID, NUMBER</TT></TD>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -