granicus.if.org Git - postgresql/blob - src/tutorial/basics.source

   1 ---------------------------------------------------------------------------
   2 --
   3 -- basics.sql-
   4 --    Tutorial on the basics (table creation and data manipulation)
   5 --
   6 --
   7 -- src/tutorial/basics.source
   8 --
   9 ---------------------------------------------------------------------------
  10
  11 -----------------------------
  12 -- Creating a New Table:
  13 --      A CREATE TABLE is used to create base tables.  PostgreSQL has
  14 --      its own set of built-in types.  (Note that SQL is case-
  15 --      insensitive.)
  16 -----------------------------
  17
  18 CREATE TABLE weather (
  19         city            varchar(80),
  20         temp_lo         int,            -- low temperature
  21         temp_hi         int,            -- high temperature
  22         prcp            real,           -- precipitation
  23         date            date
  24 );
  25
  26 CREATE TABLE cities (
  27         name            varchar(80),
  28         location        point
  29 );
  30
  31
  32 -----------------------------
  33 -- Populating a Table With Rows:
  34 --      An INSERT statement is used to insert a new row into a table.  There
  35 --      are several ways you can specify what columns the data should go to.
  36 -----------------------------
  37
  38 -- 1. The simplest case is when the list of value correspond to the order of
  39 --    the columns specified in CREATE TABLE.
  40
  41 INSERT INTO weather
  42     VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
  43
  44 INSERT INTO cities
  45     VALUES ('San Francisco', '(-194.0, 53.0)');
  46
  47 -- 2. You can also specify what column the values correspond to.  (The columns
  48 --    can be specified in any order.  You may also omit any number of columns,
  49 --    e.g., unknown precipitation below.
  50
  51 INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
  52     VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
  53
  54 INSERT INTO weather (date, city, temp_hi, temp_lo)
  55     VALUES ('1994-11-29', 'Hayward', 54, 37);
  56
  57
  58 -----------------------------
  59 -- Querying a Table:
  60 --      A SELECT statement is used for retrieving data.  The basic syntax is
  61 --      SELECT columns FROM tables WHERE predicates.
  62 -----------------------------
  63
  64 -- A simple one would be:
  65
  66 SELECT * FROM weather;
  67
  68 -- You may also specify expressions in the target list.  (The 'AS column'
  69 -- specifies the column name of the result.  It is optional.)
  70
  71 SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
  72
  73 -- If you want to retrieve rows that satisfy certain condition (i.e., a
  74 -- restriction), specify the condition in WHERE.  The following retrieves
  75 -- the weather of San Francisco on rainy days.
  76
  77 SELECT *
  78     FROM weather
  79     WHERE city = 'San Francisco'
  80         AND prcp > 0.0;
  81
  82 -- Here is a more complicated one.  Duplicates are removed when DISTINCT is
  83 -- specified. ORDER BY specifies the column to sort on.  (Just to make sure the
  84 -- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
  85
  86 SELECT DISTINCT city
  87     FROM weather
  88     ORDER BY city;
  89
  90
  91 -----------------------------
  92 -- Joins Between Tables:
  93 --      queries can access multiple tables at once or access the same table
  94 --      in such a way that multiple instances of the table are being processed
  95 --      at the same time.
  96 -----------------------------
  97
  98 -- The following joins the weather table and the cities table.
  99
 100 SELECT *
 101     FROM weather, cities
 102     WHERE city = name;
 103
 104 -- This prevents a duplicate city name column:
 105
 106 SELECT city, temp_lo, temp_hi, prcp, date, location
 107     FROM weather, cities
 108     WHERE city = name;
 109
 110 -- since the column names are all different, we don't have to specify the
 111 -- table name. If you want to be clear, you can do the following. They give
 112 -- identical results, of course.
 113
 114 SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location
 115     FROM weather, cities
 116     WHERE cities.name = weather.city;
 117
 118 -- JOIN syntax
 119
 120 SELECT *
 121     FROM weather JOIN cities ON (weather.city = cities.name);
 122
 123 -- Outer join
 124
 125 SELECT *
 126     FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
 127
 128 -- Suppose we want to find all the records that are in the temperature range
 129 -- of other records.  W1 and W2 are aliases for weather.
 130
 131 SELECT W1.city, W1.temp_lo, W1.temp_hi,
 132        W2.city, W2.temp_lo, W2.temp_hi
 133 FROM weather W1, weather W2
 134 WHERE W1.temp_lo < W2.temp_lo
 135    and W1.temp_hi > W2.temp_hi;
 136
 137
 138 -----------------------------
 139 -- Aggregate Functions
 140 -----------------------------
 141
 142 SELECT max(temp_lo)
 143     FROM weather;
 144
 145 SELECT city FROM weather
 146     WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
 147
 148 -- Aggregate with GROUP BY
 149 SELECT city, max(temp_lo)
 150     FROM weather
 151     GROUP BY city;
 152
 153 -- ... and HAVING
 154 SELECT city, max(temp_lo)
 155     FROM weather
 156     GROUP BY city
 157     HAVING max(temp_lo) < 40;
 158
 159
 160 -----------------------------
 161 -- Updates:
 162 --      An UPDATE statement is used for updating data.
 163 -----------------------------
 164
 165 -- Suppose you discover the temperature readings are all off by 2 degrees as
 166 -- of Nov 28, you may update the data as follow:
 167
 168 UPDATE weather
 169     SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
 170     WHERE date > '1994-11-28';
 171
 172 SELECT * FROM weather;
 173
 174
 175 -----------------------------
 176 -- Deletions:
 177 --      A DELETE statement is used for deleting rows from a table.
 178 -----------------------------
 179
 180 -- Suppose you are no longer interested in the weather of Hayward, then you can
 181 -- do the following to delete those rows from the table.
 182
 183 DELETE FROM weather WHERE city = 'Hayward';
 184
 185 SELECT * FROM weather;
 186
 187 -- You can also delete all the rows in a table by doing the following.  (This
 188 -- is different from DROP TABLE which removes the table in addition to the
 189 -- removing the rows.)
 190
 191 DELETE FROM weather;
 192
 193 SELECT * FROM weather;
 194
 195
 196 -----------------------------
 197 -- Removing the tables:
 198 --      DROP TABLE is used to remove tables.  After you have done this, you
 199 --      can no longer use those tables.
 200 -----------------------------
 201
 202 DROP TABLE weather, cities;