📄 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="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="ch09.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch09.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="ch11.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch11.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 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 database
users who have already had some exposure to SQL. Days 1 through 8 covered nearly
all the introductory material you need to get started using SQL and relational databases.
Day 9, "Creating and Manipulating Tables," was devoted to a discussion
of database design, table creation, and other data manipulation commands. The common
feature of the objects discussed so far--databases, tables, records, and fields--is
that they are all physical objects located on a hard disk. Today the focus shifts
to two features of SQL that enable you to view or present data in a different format
than it appears on the disk. These two features are the view and the index. By the
end 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 the
following 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 the
disk. 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 columns
within a table. When an index is used, the data is presented to the user in a sorted
order, which you can control with the <TT>CREATE INDEX</TT> statement. You can usually
gain substantial performance improvements by indexing on the correct fields, particularly
fields 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 a
view 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. When
data in a table changes, what you see when you query the view also changes. Views
do 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...)] AS
SELECT <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 contains
many examples that illustrate the uses and advantages of views. This command tells
SQL to create a view (with the name of your choice) that comprises columns (with
the names of your choice if you like). An <TT>SQL SELECT</TT> statement determines
the 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 advantage
of your newfound knowledge of the <TT>DROP DATABASE</TT> command. You can simply
re-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>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -