📄 ch10_03.htm
字号:
<?label 10.3. Introduction to SQL?><html><head><title>Introduction to SQL (CGI Programming with Perl)</title><link href="../style/style1.css" type="text/css" rel="stylesheet" /><meta name="DC.Creator" content="Scott Guelich, Gunther Birznieks and Shishir Gundavaram" /><meta scheme="MIME" content="text/xml" name="DC.Format" /><meta content="en-US" name="DC.Language" /><meta content="O'Reilly & Associates, Inc." name="DC.Publisher" /><meta scheme="ISBN" name="DC.Source" content="1565924193L" /><meta name="DC.Subject.Keyword" content="stuff" /><meta name="DC.Title" content="CGI Programming with Perl" /><meta content="Text.Monograph" name="DC.Type" /></head><body bgcolor="#ffffff"><img src="gifs/smbanner.gif" alt="Book Home" usemap="#banner-map" border="0" /><map name="banner-map"><area alt="CGI Programming with Perl" href="index.htm" coords="0,0,466,65" shape="rect" /><area alt="Search this book" href="jobjects/fsearch.htm" coords="467,0,514,18" shape="rect" /></map><div class="navbar"><table border="0" width="515"><tr><td width="172" valign="top" align="left"><a href="ch10_02.htm"><img src="../gifs/txtpreva.gif" alt="Previous" border="0" /></a></td><td width="171" valign="top" align="center"><a href="index.htm">CGI Programming with Perl</a></td><td width="172" valign="top" align="right"><a href="ch10_04.htm"><img src="../gifs/txtnexta.gif" alt="Next" border="0" /></a></td></tr></table></div><hr align="left" width="515" /><h2 class="sect1">10.3. Introduction to SQL</h2><p><a name="INDEX-2086" /> <a name="INDEX-2,087" />Because of the sheernumber of different database systems that exist, most databasevendors have standardized on a <a name="INDEX-2088" />query language (SQL) toupdate as well as access their databases. Before we go any further,let's look more deeply into how this query language is used tocommunicate with various database systems.</p><p>SQL is the standardized language to access and manipulate data within<a name="INDEX-2089" />relational databasesystems. The original SQL prototype defined a"structured" language, thus the term Structured QueryLanguage, but this is no longer true of the current SQL-92 standard.SQL was designed specifically to be used in conjunction with aprimary high-level programming language. In fact, most of the basicconstructs that you would find in a high-level language, such asloops and conditionals, do not exist in SQL.</p><p>All major commercial relational database systems, such as Oracle,Informix, and Sybase, and many open source databases, such asPostgreSQL, MySQL, and mSQL, support SQL. As a result, the code toaccess and manipulate a database can be ported easily and quickly toany platform. Let's look at SQL.</p><a name="ch10-10-fm2xml" /><div class="sect2"><h3 class="sect2">10.3.1. Creating a Database</h3><p><a name="INDEX-2090" /><a name="INDEX-2091" />Wewill start out by discussing how a database is created. Suppose youhave the following information:</p><a name="ch10-11-fm2xml" /><table border="1"><tr><th><p>Player</p></th><th><p>Years</p></th><th><p>Points</p></th><th><p>Rebounds</p></th><th><p>Assists</p></th><th><p>Championships</p></th></tr><tr><td><p>Larry Bird</p></td><td><p>12</p></td><td><p>28</p></td><td><p>10</p></td><td><p>7</p></td><td><p>3</p></td></tr><tr><td><p>Magic Johnson</p></td><td><p>12</p></td><td><p>22</p></td><td><p>7</p></td><td><p>12</p></td><td><p>5</p></td></tr><tr><td><p>Michael Jordan</p></td><td><p>13</p></td><td><p>32</p></td><td><p>6</p></td><td><p>6</p></td><td><p>6</p></td></tr><tr><td><p>Karl Malone</p></td><td><p>15</p></td><td><p>26</p></td><td><p>11</p></td><td><p>3</p></td><td><p>0</p></td></tr><tr><td><p>Shaquille O'Neal</p></td><td><p>8</p></td><td><p>28</p></td><td><p>12</p></td><td><p>3</p></td><td><p>0</p></td></tr><tr><td><p>John Stockton</p></td><td><p>16</p></td><td><p>13</p></td><td><p>3</p></td><td><p>11</p></td><td><p>0</p></td></tr></table><p>The SQL code to create this database is:</p><blockquote><pre class="code">create table Player_Info( Player varchar (30) not null, Years integer, Points integer, Rebounds integer, Assists integer, Championships integer);</pre></blockquote><p>The <em class="emphasis">create table</em><a name="INDEX-2092" /><a name="INDEX-2093" />command creates a database, or a table. The<a name="INDEX-2094" /> <a name="INDEX-2,095" /><em class="emphasis">Player</em> field isstored as a non-null varying character string. In other words, if thedata in the field is less than thirty characters, the database willnot pad it with spaces, as it would for a regular character datatype. Also, the database forces the user to enter a value for the<em class="emphasis">Player</em> field; it cannot be empty.</p><p>The rest of the fields are defined to be integers. Some of the othervalid data types include <em class="emphasis">datetime</em>,<em class="emphasis">smallint</em>, <em class="emphasis">numeric</em>, and<em class="emphasis">decimal</em>. The <em class="emphasis">numeric</em> and<em class="emphasis">decimal</em> data types allow you to specifyfloating-point values. For example, if you want a five-digitfloating-point number with a precision to the hundredth place, youcan specify <tt class="command">decimal (5, 2)</tt>.</p></div><a name="ch10-12-fm2xml" /><div class="sect2"><h3 class="sect2">10.3.2. Inserting Data</h3><p>Before we discuss how to obtain data from a database table, we needto discuss how to populate the database in the first place. In<a name="INDEX-2096" /><a name="INDEX-2097" /><a name="INDEX-2098" /> <a name="INDEX-2,099" />SQL, we do this with the<tt class="command">insert</tt> statement. Say we need to add anotherplayer to the database. We could do it this way:</p><blockquote><pre class="code">insert into Player_Info values ('Hakeem Olajuwon', 16, 23, 12, 3, 2);</pre></blockquote><p>As you can see, it is very simple to insert an element into thetable. However, if you have a database with a large number ofcolumns, and you want to insert a row into the table, you canmanually specify the columns:</p><blockquote><pre class="code">insert into Player_Info (Player, Years, Points, Rebounds, Assists, Championships) values ('Hakeem Olajuwon', 10, 27, 11, 4, 2);</pre></blockquote><p>When used in this context, the order of the fields does notnecessarily have to match the order in the database, as long as thefields and the values specified match each other.</p></div><a name="ch10-13-fm2xml" /><div class="sect2"><h3 class="sect2">10.3.3. Accessing Data</h3><p>The language required for <a name="INDEX-2100" /><a name="INDEX-2101" />accessing data has a lot more featuresthan what we have discussed so far for simply creating and insertingdata into a table. These additional elements make SQL an incrediblyrich language for retrieving data once it is stored inside ofdatabase tables. We will also see later that updating and deletingdata relies on the information in this section in order to determinewhich rows in a table actually become modified or removed from thedatabase.</p><p>Let's say you want a list of the entire database. You can usethe following code:</p><blockquote><pre class="code">select * from Player_Info;</pre></blockquote><p>The <em class="emphasis">select</em><a name="INDEX-2102" /> command retrieves specificinformation from the database. In this case, all columns are selectedfrom the <em class="emphasis">Player_Info</em> database. The"*" should be used with great caution, especially onlarge databases, as you might inadvertently extract a lot ofinformation. Notice that we are dealing only with columns, and notrows. For example, if you wanted to list all the players in thedatabase, you could do this:</p><blockquote><pre class="code">select Player from Player_Info;</pre></blockquote><p>Now, what if you want to list all the players who scored more than 25points? Here is the code needed to accomplish the task:</p><blockquote><pre class="code">select * from Player_Info where Points > 25;</pre></blockquote><p>This would list all the columns for the players who scored more than25 points:</p><a name="ch10-14-fm2xml" /><table border="1"><tr><th><p>Player</p></th><th><p>Years</p></th><th><p>Points</p></th><th><p>Rebounds</p></th><th><p>Assists</p></th><th><p>Championships</p></th></tr><tr><td><p>Larry Bird</p></td><td><p>12</p></td><td><p>28</p></td><td><p>10</p></td><td><p>7</p></td><td><p>3</p></td></tr><tr><td><p>Michael Jordan</p></td><td><p>13</p></td><td><p>32</p></td><td><p>6</p></td><td><p>6</p></td><td><p>6</p></td></tr><tr><td><p>Karl Malone</p></td><td><p>15</p></td><td><p>26</p></td><td><p>11</p></td><td><p>3</p></td><td><p>0</p></td></tr><tr><td><p>Shaquille O'Neal</p></td><td><p>8</p></td><td><p>28</p></td><td><p>12</p></td><td><p>3</p></td><td><p>0</p></td></tr></table><p>But, say you wanted to list just the <em class="emphasis">Player</em> and<em class="emphasis">Points</em> columns:</p><blockquote><pre class="code">select Player, Points from Player_Info where Points > 25;</pre></blockquote><p>Here is an example that returns all the players who scored more than25 points and won a championship:</p><blockquote><pre class="code">select Player, Points, Championships from Player_Info where Points > 25 and Championships > 0;</pre></blockquote><p>The output of this SQL statement would be:</p><a name="ch10-15-fm2xml" /><table border="1"><tr><th><p>Player</p></th><th><p>Points</p></th><th><p>Championships</p></th></tr><tr><td><p>Larry Bird</p></td><td><p>28</p></td><td><p>3</p></td></tr><tr><td><p>Michael Jordan</p></td><td><p>32</p></td><td><p>6</p></td></tr></table><p>You could also use <a name="INDEX-2103" />wildcards in a<em class="emphasis">select</em> command. For example, the following willreturn all the players that have a last name of "Johnson":</p><blockquote><pre class="code">select * from Player_Info where Player like '% Johnson';</pre></blockquote><p>This will match a string ending with "Johnson".</p></div><a name="ch10-16-fm2xml" /><div class="sect2"><h3 class="sect2">10.3.4. Updating Data</h3><p><a name="INDEX-2104" /><a name="INDEX-2105" />Let'ssuppose that Shaquille O'Neal won a championship. We need toupdate our database to reflect this. This is how it can be done:</p><blockquote><pre class="code">update Player_Info set Championships = 1 where Player = 'Shaquille O''Neal';</pre></blockquote><p>Note the <tt class="literal">where</tt> clause. In order to modify data,you have to let SQL know what rows will be set to new values. To dothis, we use the same syntax that is used to access data in a tableexcept that instead of retrieving records, we are just changing them.Also note that we must escape a single quote by using another singlequote.</p><p>SQL also has methods to modify entire columns. After every basketballseason, we need to increment the <em class="emphasis">Years</em> column byone:</p><blockquote><pre class="code">update Player_Info set Years = Years + 1;</pre></blockquote></div><a name="ch10-17-fm2xml" /><div class="sect2"><h3 class="sect2">10.3.5. Deleting Data</h3><p><a name="INDEX-2106" /><a name="INDEX-2107" />If you wanted to delete "JohnStockton" from the database, you could do this:</p><blockquote><pre class="code">delete from Player_Info where Player = 'John Stockton';</pre></blockquote><p>If you want to delete all the records in the table, the followingstatement is used:</p><blockquote><pre class="code">delete from Player_Info;</pre></blockquote><p>And finally, the <em class="emphasis">drop table</em> command deletes theentire database:</p><blockquote><pre class="code">drop table Player_Info;</pre></blockquote><p>For more information on SQL, see the reference guide on SQL-92 at<a href="http://sunsite.doc.ic.ac.uk/packages/perl/db/refinfo/sql2/sql1992.txt">http://sunsite.doc.ic.ac.uk/packages/perl/db/refinfo/sql2/sql1992.txt</a>.<a name="INDEX-2108" /></p></div><hr align="left" width="515" /><div class="navbar"><table border="0" width="515"><tr><td width="172" valign="top" align="left"><a href="ch10_02.htm"><img src="../gifs/txtpreva.gif" alt="Previous" border="0" /></a></td><td width="171" valign="top" align="center"><a href="index.htm"><img src="../gifs/txthome.gif" alt="Home" border="0" /></a></td><td width="172" valign="top" align="right"><a href="ch10_04.htm"><img src="../gifs/txtnexta.gif" alt="Next" border="0" /></a></td></tr><tr><td width="172" valign="top" align="left">10.2. DBM Files</td><td width="171" valign="top" align="center"><a href="index/index.htm"><img src="../gifs/index.gif" alt="Book Index" border="0" /></a></td><td width="172" valign="top" align="right">10.4. DBI</td></tr></table></div><hr align="left" width="515" /><img src="../gifs/navbar.gif" alt="Library Navigation Links" usemap="#library-map" border="0" /><p><font size="-1"><a href="copyrght.htm">Copyright © 2001</a> O'Reilly & Associates. All rights reserved.</font></p><map name="library-map"><area href="../index.htm" coords="1,1,83,102" shape="rect" /><area href="../lnut/index.htm" coords="81,0,152,95" shape="rect" /><area href="../run/index.htm" coords="172,2,252,105" shape="rect" /><area href="../apache/index.htm" coords="238,2,334,95" shape="rect" /><area href="../sql/index.htm" coords="336,0,412,104" shape="rect" /><area href="../dbi/index.htm" coords="415,0,507,101" shape="rect" /><area href="../cgi/index.htm" coords="511,0,601,99" shape="rect" /></map></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -