1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter7.Queries</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql.html" title="PartII.The SQL Language">
<link rel="prev" href="dml-delete.html" title="6.3.Deleting Data">
<link rel="next" href="queries-table-expressions.html" title="7.2.Table Expressions">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="queries">
<div class="titlepage"><div><div><h2 class="title">
<a name="queries"></a>Chapter7.Queries</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="queries.html#queries-overview">7.1. Overview</a></span></dt>
<dt><span class="sect1"><a href="queries-table-expressions.html">7.2. Table Expressions</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="queries-table-expressions.html#queries-from">7.2.1. The <code class="literal">FROM</code> Clause</a></span></dt>
<dt><span class="sect2"><a href="queries-table-expressions.html#queries-where">7.2.2. The <code class="literal">WHERE</code> Clause</a></span></dt>
<dt><span class="sect2"><a href="queries-table-expressions.html#queries-group">7.2.3. The <code class="literal">GROUP BY</code> and <code class="literal">HAVING</code> Clauses</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="queries-select-lists.html">7.3. Select Lists</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="queries-select-lists.html#queries-select-list-items">7.3.1. Select-List Items</a></span></dt>
<dt><span class="sect2"><a href="queries-select-lists.html#queries-column-labels">7.3.2. Column Labels</a></span></dt>
<dt><span class="sect2"><a href="queries-select-lists.html#queries-distinct">7.3.3. <code class="literal">DISTINCT</code></a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="queries-union.html">7.4. Combining Queries</a></span></dt>
<dt><span class="sect1"><a href="queries-order.html">7.5. Sorting Rows</a></span></dt>
<dt><span class="sect1"><a href="queries-limit.html">7.6. <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code></a></span></dt>
</dl>
</div>
<a name="id579983"></a><a name="id579993"></a><p> The previous chapters explained how to create tables, how to fill
them with data, and how to manipulate that data. Now we finally
discuss how to retrieve the data out of the database.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="queries-overview"></a>7.1.Overview</h2></div></div></div>
<p> The process of retrieving or the command to retrieve data from a
database is called a <em class="firstterm">query</em>. In SQL the
<code class="command">SELECT</code> command is used to specify queries. The
general syntax of the <code class="command">SELECT</code> command is
</p>
<pre class="synopsis">SELECT <em class="replaceable"><code>select_list</code></em> FROM <em class="replaceable"><code>table_expression</code></em> [<span class="optional"><em class="replaceable"><code>sort_specification</code></em></span>]</pre>
<p>
The following sections describe the details of the select list, the
table expression, and the sort specification.
</p>
<p> A simple kind of query has the form
</p>
<pre class="programlisting">SELECT * FROM table1;</pre>
<p>
Assuming that there is a table called <code class="literal">table1</code>,
this command would retrieve all rows and all columns from
<code class="literal">table1</code>. (The method of retrieval depends on the
client application. For example, the
<span class="application">psql</span> program will display an ASCII-art
table on the screen, while client libraries will offer functions to
extract individual values from the query result.) The select list
specification <code class="literal">*</code> means all columns that the table
expression happens to provide. A select list can also select a
subset of the available columns or make calculations using the
columns. For example, if
<code class="literal">table1</code> has columns named <code class="literal">a</code>,
<code class="literal">b</code>, and <code class="literal">c</code> (and perhaps others) you can make
the following query:
</p>
<pre class="programlisting">SELECT a, b + c FROM table1;</pre>
<p>
(assuming that <code class="literal">b</code> and <code class="literal">c</code> are of a numerical
data type).
See <a href="queries-select-lists.html" title="7.3.Select Lists">Section7.3, “Select Lists”</a> for more details.
</p>
<p> <code class="literal">FROM table1</code> is a particularly simple kind of
table expression: it reads just one table. In general, table
expressions can be complex constructs of base tables, joins, and
subqueries. But you can also omit the table expression entirely and
use the <code class="command">SELECT</code> command as a calculator:
</p>
<pre class="programlisting">SELECT 3 * 4;</pre>
<p>
This is more useful if the expressions in the select list return
varying results. For example, you could call a function this way:
</p>
<pre class="programlisting">SELECT random();</pre>
<p>
</p>
</div>
</div></body>
</html>
|
