?? ch10.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 10 -- Creating Views and Indexes</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="../ch09/ch09.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch11/ch11.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 10 -<BR>Creating Views and Indexes</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Today we begin to cover topics that may be new even to programmers or databaseusers who have already had some exposure to SQL. Days 1 through 8 covered nearlyall the introductory material you need to get started using SQL and relational databases.Day 9, "Creating and Manipulating Tables," was devoted to a discussionof database design, table creation, and other data manipulation commands. The commonfeature of the objects discussed so far--databases, tables, records, and fields--isthat they are all physical objects located on a hard disk. Today the focus shiftsto two features of SQL that enable you to view or present data in a different formatthan it appears on the disk. These two features are the view and the index. By theend of today, you will know the following:<UL> <LI>How to distinguish between indexes and views <P> <LI>How to create views <P> <LI>How to create indexes <P> <LI>How to modify data using views <P> <LI>What indexes do</UL><P>A view is often referred to as a virtual table. Views are created by using the<TT>CREATE VIEW</TT> statement. After the view has been created, you can use thefollowing SQL commands to refer to that view:<UL> <LI><TT>SELECT</TT> <P> <LI><TT>INSERT</TT> <P> <LI><TT>INPUT</TT> <P> <LI><TT>UPDATE</TT> <P> <LI><TT>DELETE</TT></UL><P>An index is another way of presenting data differently than it appears on thedisk. Special types of indexes reorder the record's physical location within a table.Indexes can be created on a column within a table or on a combination of columnswithin a table. When an index is used, the data is presented to the user in a sortedorder, which you can control with the <TT>CREATE INDEX</TT> statement. You can usuallygain substantial performance improvements by indexing on the correct fields, particularlyfields that are being joined between tables.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Views and indexes are two totally different objects, but they have one thing in common: They are both associated with a table in the database. Although each object's association with a table is unique, they both enhance a table, thus unveiling powerful features such as presorted data and predefined queries. <HR></P> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>We used Personal Oracle7 to generate today's examples. Please see the documentation for your specific SQL implementation for any minor differences in syntax. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Using Views</FONT></H2><P>You can use views, or virtual tables, to encapsulate complex queries. After aview on a set of data has been created, you can treat that view as another table.However, special restrictions are placed on modifying the data within views. Whendata in a table changes, what you see when you query the view also changes. Viewsdo not take up physical space in the database as tables do.</P><P>The syntax for the <TT>CREATE VIEW</TT> statement is</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CREATE VIEW <view_name> [(column1, column2...)] ASSELECT <table_name column_names>FROM <table_name></FONT></PRE><P>As usual, this syntax may not be clear at first glance, but today's material containsmany examples that illustrate the uses and advantages of views. This command tellsSQL to create a view (with the name of your choice) that comprises columns (withthe names of your choice if you like). An <TT>SQL SELECT</TT> statement determinesthe fields in these columns and their data types. Yes, this is the same <TT>SELECT</TT>statement that you have used repeatedly for the last nine days.</P><P>Before you can do anything useful with views, you need to populate the <TT>BILLS</TT>database with a little more data. Don't worry if you got excited and took advantageof your newfound knowledge of the <TT>DROP DATABASE</TT> command. You can simplyre-create it. (See Tables 10.1, 10.2, and 10.3 for sample data.)</P><H5><B>INPUTOUTPUT:</B></H5><PRE><FONT COLOR="#0066FF">SQL> <B>create database BILLS;</B>Statement processed.</FONT></PRE><H5><B>INPUTOUTPUT:</B></H5><PRE><FONT COLOR="#0066FF">SQL> <B>create table BILLS (</B> 2 <B>NAME CHAR(30) NOT NULL,</B> 3 <B>AMOUNT NUMBER,</B> 4 <B>ACCOUNT_ID NUMBER NOT NULL);</B>Table created.</FONT></PRE><H5><B>INPUTOUTPUT:</B></H5><PRE><FONT COLOR="#0066FF">SQL> <B>create table BANK_ACCOUNTS (</B> 2 <B>ACCOUNT_ID NUMBER NOT NULL,</B> 3 <B> TYPE CHAR(30),</B> 4 <B>BALANCE NUMBER,</B> 5 <B> BANK CHAR(30));</B>Table created.</FONT></PRE><H5><B>INPUTOUTPUT:</B></H5><PRE><FONT COLOR="#0066FF">SQL> <B>create table COMPANY (</B> 2 <B> NAME CHAR(30) NOT NULL,</B> 3 <B>ADDRESS CHAR(50),</B> 4 <B>CITY CHAR(30),</B> 5 <B>STATE CHAR(2));</B>Table created.</FONT></PRE><H4><FONT COLOR="#000077">Table 10.1. Sample data for the BILLS table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Name</B></TD> <TD ALIGN="LEFT"><B>Amount</B></TD> <TD ALIGN="LEFT"><B>Account_ID</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Phone Company</TD> <TD ALIGN="LEFT">125</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Power Company</TD> <TD ALIGN="LEFT">75</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Record Club</TD> <TD ALIGN="LEFT">25</TD> <TD ALIGN="LEFT">2</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Software Company</TD> <TD ALIGN="LEFT">250</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Cable TV Company</TD> <TD ALIGN="LEFT">35</TD> <TD ALIGN="LEFT">3</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Joe's Car Palace</TD> <TD ALIGN="LEFT">350</TD> <TD ALIGN="LEFT">5</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">S.C. Student Loan</TD> <TD ALIGN="LEFT">200</TD> <TD ALIGN="LEFT">6</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Florida Water Company</TD> <TD ALIGN="LEFT">20</TD> <TD ALIGN="LEFT">1</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">U-O-Us Insurance Company</TD> <TD ALIGN="LEFT">125</TD> <TD ALIGN="LEFT">5</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Debtor's Credit Card</TD> <TD ALIGN="LEFT">35</TD> <TD ALIGN="LEFT">4</TD> </TR></TABLE><H4><FONT COLOR="#000077">Table 10.2. Sample data for the BANK_ACCOUNTS table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Account_ID</B></TD> <TD ALIGN="LEFT"><B>Type</B></TD> <TD ALIGN="LEFT"><B>Balance</B></TD> <TD ALIGN="LEFT"><B>Bank</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">1</TD> <TD ALIGN="LEFT">Checking</TD> <TD ALIGN="LEFT">500</TD> <TD ALIGN="LEFT">First Federal</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">2</TD> <TD ALIGN="LEFT">Money Market</TD> <TD ALIGN="LEFT">1200</TD> <TD ALIGN="LEFT">First Investor's</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">3</TD> <TD ALIGN="LEFT">Checking</TD> <TD ALIGN="LEFT">90</TD> <TD ALIGN="LEFT">Credit Union</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">4</TD> <TD ALIGN="LEFT">Savings</TD> <TD ALIGN="LEFT">400</TD> <TD ALIGN="LEFT">First Federal</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">5</TD> <TD ALIGN="LEFT">Checking</TD> <TD ALIGN="LEFT">2500</TD> <TD ALIGN="LEFT">Second Mutual</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">6</TD> <TD ALIGN="LEFT">Business</TD> <TD ALIGN="LEFT">4500</TD> <TD ALIGN="LEFT">Fidelity</TD> </TR></TABLE><H4><FONT COLOR="#000077">Table 10.3. Sample data for the COMPANY table.</FONT></H4><P><TABLE BORDER="1"> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT"><B>Name</B></TD> <TD ALIGN="LEFT"><B>Address</B></TD> <TD ALIGN="LEFT"><B>City</B></TD> <TD ALIGN="LEFT"><B>State</B></TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Phone Company</TD> <TD ALIGN="LEFT">111 1st Street</TD> <TD ALIGN="LEFT">Atlanta</TD> <TD ALIGN="LEFT">GA</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Power Company</TD> <TD ALIGN="LEFT">222 2nd Street</TD> <TD ALIGN="LEFT">Jacksonville</TD> <TD ALIGN="LEFT">FL</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Record Club</TD> <TD ALIGN="LEFT">333 3rd Avenue</TD> <TD ALIGN="LEFT">Los Angeles</TD> <TD ALIGN="LEFT">CA</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Software Company</TD> <TD ALIGN="LEFT">444 4th Drive</TD> <TD ALIGN="LEFT">San Francisco</TD> <TD ALIGN="LEFT">CA</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Cable TV Company</TD> <TD ALIGN="LEFT">555 5th Drive</TD> <TD ALIGN="LEFT">Austin</TD> <TD ALIGN="LEFT">TX</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Joe's Car Palace</TD> <TD ALIGN="LEFT">1000 Govt. Blvd</TD> <TD ALIGN="LEFT">Miami</TD> <TD ALIGN="LEFT">FL</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">S.C. Student Loan</TD> <TD ALIGN="LEFT">25 College Blvd</TD> <TD ALIGN="LEFT">Columbia</TD> <TD ALIGN="LEFT">SC</TD> </TR> <TR ALIGN="LEFT" rowspan="1"> <TD ALIGN="LEFT">Florida Water Company</TD> <TD ALIGN="LEFT">1883 Hwy 87</TD> <TD ALIGN="LEFT">Navarre</TD>?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -