?? 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="../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="../ch08/ch08.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch10/ch10.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 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> statementsare often described as data manipulation statements.) By the end of the day, youwill 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 somedetail, 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 deletedata from the database. Now, nine days into the learning process, you probably havebeen wondering just where these databases come from. For simplicity's sake, we havebeen ignoring the process of creating databases and tables. We have assumed thatthese data objects existed currently on your system. Today you finally create theseobjects.</P><P>The syntax of the <TT>CREATE</TT> statements can range from the extremely simpleto the complex, depending on the options your database management system (DBMS) supportsand 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 needsand the database management system you have chosen. Many modern systems (includingPersonal Oracle7) include graphical tools that enable you to completely build thedatabase with the click of a mouse button. This time-saving feature is certainlyhelpful, but you should understand the SQL statements that execute in response tothe mouse clicks.</P><P>Through personal experience, we have learned the importance of creating a goodSQL install script. This script file contains the necessary SQL code to completelyrebuild a database or databases; the script often includes database objects suchas indexes, stored procedures, and triggers. You will see the value of this scriptduring development as you continually make changes to the underlying database andon 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 extremelytime-consuming. In addition, knowing the SQL syntax for this procedure enables youto 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 expandon the <TT>CREATE DATABASE</TT> statement's syntax. Many systems do not even supportan SQL <TT>CREATE DATABASE</TT> command. However, all the popular, more powerful,relational database management systems (RDBMSs) do provide it. Instead of focusingon its syntax, we will spend some time discussing the options to consider when creatinga database.<H3><FONT COLOR="#000077">CREATE DATABASE Options</FONT></H3><P>The syntax for the <TT>CREATE DATABASE</TT> statement can vary widely. Many SQLtexts 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 canbuild a table, this section focuses on some of the concepts a developer must considerwhen building a database. The first consideration is your level of permission. Ifyou are using a relational database management system (RDBMS) that supports userpermissions, you must make sure that either you have system administrator-level permissionsettings 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 termsof hard disk space (such as megabytes). You will need to understand how your databasesystem 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 thetopics of relational database theory and database normalization.</P><P><I>Normalization</I> is the process of breaking your data into separate componentsto reduce the repetition of data. Each level of normalization reduces the repetitionof data. Normalizing your data can be an extremely complex process, and numerousdatabase 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 diskspace is a major concern in an age of multigigabyte storage, remember that the biggeryour database is, the longer it takes to retrieve records. If you have done a poorjob of designing your table structure, chances are that you have needlessly repeatedmuch of your data.</P><P>Often the opposite problem can occur. You may have sought to completely normalizeyour tables' design with the database and in doing so created many tables. Althoughyou may have approached database-design nirvana, any query operations done againstthis database may take a very long time to execute. Databases designed in this mannerare sometimes difficult to maintain because the table structure might obscure thedesigner's intent. This problem underlines the importance of always documenting yourcode or design so that others can come in after you (or work with you) and have someidea of what you were thinking at the time you created your database structure. Indatabase 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 designerin the creation of this data dictionary. For instance, Microsoft Access comes prepackagedwith a database documenting tool that prints out a detailed description of everyobject 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 youshould have is to create your table structure so that each table has a primary keyand 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 throughoutthe table is known as the <I>primary key field</I>. A <I>foreign key field</I> isa field that links one table to another table's primary or foreign key. The followingexample 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 + -