forked from ninas/umonya_notes
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatabase_SQL.html
More file actions
658 lines (511 loc) · 25.6 KB
/
database_SQL.html
File metadata and controls
658 lines (511 loc) · 25.6 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Introductory Programming in Python: Structured Query Language</title>
<link rel='stylesheet' type='text/css' href='style.css' />
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
<script src="animation.js" type="text/javascript">
</script>
</head>
<body onload="animate_loop()">
<div class="page">
<h1>Introductory Programming in Python: Lesson 34<br />
Structured Query Language</h1>
<div class="centered">
[<a href="database_relational.html">Prev: Relational Databases</a>] [<a href="index.html">Course Outline</a>] [<a href="database_coding.html">Next: Interfacing with Databases using Python</a>]
</div>
<p>This lesson deals with standard structured query language (<a
href='http://en.wikipedia.org/wiki/Sql'>SQL</a>). SQL is a not a
programming language as such, but rather, as its name suggests, a query
language. It is a language designed specifically to let one phrase a
complex question in a human readable but also machine parsable manner,
that can extract arbitrary collection of data from a given database.</p>
<p>The details of database administration are not part of the SQL
standard, and as such not covered in this lesson (or this course for
that matter). They are different for every implementation of SQL, and
covered by each implementation's documentation. For the moment, it is
assumed that you have an account on a database already set up. This
lesson assumes a PostgreSQL database.</p>
<h2>Starting the database client</h2>
<p>The first thing to do is start up your database client. In a
terminal, or command line window if using windows (Why are you still
using Windows?), type <code>psql</code> for PostgreSQL:</p>
<pre class='listing'>sirlark@hephaestus ~ $ psql
Welcome to psql 8.0.15, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
sirlark=></pre>
<p>You are now connected to the PostgreSQL server, and are using the
database named <your username> (in this case sirlark).</p>
<p>For MySQL type <code>mysql -D <your username> -p</code>:</p>
<pre class='listing'>sirlark@hephaestus ~ $ mysql -D sirlark -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.60-log Gentoo Linux mysql-5.0.60-r1
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> </pre>
<h2>Creating a new database</h2>
<p>While the creation of a new database is nominally an administrative
issue, it is covered by the SQL standard. Quite simply we use the
command <code>CREATE DATABASE <database name>;</code>. SQL
commands are not case sensitive, though table names and column names may
be depending on implementation, however it is common practice to use
uppercase for SQL specific keywords to make your queries more
legible. Note the trailing semicolon. SQL is insensitive to the amount
and nature of whitespace used, i.e. all whitespace is considered
equivalent. As such a newline does not end a command. Instead all
commands end is a semicolon.</p>
<h2>Creating Tables</h2>
<p>The first thing we want to do with any relational database is to
specify its structure. We do this by creating tables, which initially
contain no rows. The postgres client allows us to list all the tables
present in the database using the command <code>\dt</code> (The MySQL
equivalent is <code>SHOW TABLES</code>).</p>
<p>Creating a new table is as simple as:</p>
<pre class='definition'>CREATE TABLE <table name> (
<column name> <type> [<constraints>] [DEFAULT <default value>] [,
...
]
);</pre>
<h3>Columns Types</h3>
<p>Like python, relational databases have type, except they enforce the
type of data present in a column of a table. Different implementations
provide various different types, but the SQL standard specifies a shared
collection of types available in all implementations.</p>
<dl>
<dt>[UNSIGNED] INTEGER</dt><dd>Nuf' said.</dd>
<dt>FLOAT</dt><dd>Ditto</dd>
<dt>DOUBLE</dt><dd>A bigger float</dd>
<dt>NUMERIC(<precision>[, <scale>])</dt><dd>Numeric data
with guaranteed precision. <em>Precision</em> is the total number of
digits (maximum 1000), and scale is the number of digits to the
right of the decimal point (default 0). NUMERIC data is slow to work
with, compared to INTEGER and FLOAT, but provides guaranteed
accuracy.</dd>
<dt>VARCHAR(X)</dt><dd>String data up to X characters in
length. X must be less than 256.</dd>
<dt>CHAR(X)</dt><dd>String data up to X characters in length.
Regardless of how many characters are present in the string X
characters are stored on disk. Faster than VARCHAR, but uses more
space. X must be less than 256.</dd>
<dt>TEXT</dt><dd>String data of essentially indefinite length.
Maximum length varies with implementation.</dd>
<dt>DATE</dt><dd>A date</dd>
<dt>TIME</dt><dd>A time</dd>
<dt>YEAR</dt><dd>A four digit year</dd>
<dt>TIMESTAMP [{WITH|WITHOUT} TIME ZONE]</dt><dd>A date and a time,
optionally with automatic timezone conversion, which is the default.</dd>
<dt>INTERVAL</dt><dd>A length of time, e.g. 3 days and 4 hours</dd>
<dt>BOOLEAN</dt><dd>Either <em>TRUE</em> or <em>FALSE</em></dd>
</dl>
<p>There are many more standard SQL types, which you can find in the SQL
standard, or in your database software documentation. The above types
however will cover most of your bases.</p>
<h3>Column Constraints</h3>
<p>Although every column we specify must have a type, constraints are
optional. Constraints are rules we specify that constrain not the type
of data that may be stored in a column, but the content of that data.
Constraints can be specified in any order. Some common constraint
keywords are:</p>
<dl>
<dt>PRIMARY KEY</dt><dd>Indicates that this column should be
considered the primary key of the table.</dd>
<dt>UNIQUE</dt><dd>Indicates that entries in this column should be
unique within this column of this table.</dd>
<dt>[NOT] NULL</dt><dd>Species whether the column can have NULL
values (the default), or whether every row must have an entry for
this column.</dd>
<dt>REFERENCES <table name ></dt><dd>Specifies this the
contents of this column are foreign keys reference the primary key
of the table 'table name'. No values can be stored in this column
unless they exist in the primary key column of the table 'table
name'.</dd>
</dl>
<h3>Specialised Primary Key Mechanisms</h3>
<p>As mentioned before most database software allows the automatic
generation of unique primary keys, however different software
implementations do this in different ways, and there is no standard way
to specify such behaviour in standard SQL.</p>
<p>In PostgreSQL a special column type is provided, called
<em>SERIAL</em>. Making a column, not necessarily a primary key, of type
SERIAL will assign a default value that starts at 1 and increments by 1
for every row inserted into the table that doesn't specify a value for
that column.</p>
<pre class='listing'>CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
somestuff TEXT
);</pre>
<p>The same functionality can be achieved, but only for columns which
are primary keys, in MySQL using the AUTO_INCREMENT qualifier (which is
essentially a constraint).</p>
<pre class='listing'>CREATE TABLE my_table (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
somestuff TEXT
);</pre>
<h3>Default Values</h3>
<p>In addition to specifying column name, type and constraints, one may
specify a default value for cases where rows are inserted without
specifying the column in question. If no default is specified, the value
becomes NULL. Defaults are specified with the in the form <code>DEFAULT
<value></code>, generally after any constraints.</p>
<h3>Indexing</h3>
<p>Finally, one can specify that an index be created for the table on
the specified column, using the keyword <code>INDEX</code>. This is
implied by the use of <em>PRIMARY KEY</em>, but other columns can be
indexed for cases where they will be used often in queries. Indexing
speeds up access and querying of the table when conditions using indexed
columns are specified. The actual mechanisms of indexing and how they
work are otherwise not worth going into in detail unless your intent is
to become a professional database software developer.</p>
<h3>Table Constraints</h3>
<p>After all columns have been specified, additional <strong>table
constraints</strong> can be specified. Most constraints can be specified
using either column constraints or table constraints, but there are some
constraints which are restricted by syntax to being specified only as
table constraints. In particular, constraints that span multiple
columns, as in the multi-column primary key constraints of link tables
used to represent many-to-many relationships. Some of these constraints
are:</p>
<dl>
<dt>CONSTRAINT <constraint name> PRIMARY KEY (<column
name>[, <column name>[, ...])</dt><dd>Acts in the same
manner as the <em>PRIMARY KEY</em> column constraint when a single
column is specified, but also allows primary keys of multiple
columns to be specified.</dd>
<dt>CONSTRAINT <constraint name> FOREIGN KEY (<column
name>[, <column name>[, ...])<br />REFERENCES (<table
name>)</dt><dd>Acts in the same manner as the
<em>REFERENCES</em> column constraint when a single column is
specified, but an be used to specify a combination of columns that
act in concert as a single foreign key referencing a table with a
primary key composed of the same number of columns and the same
respective column types.</dd>
</dl>
<p>As an aside, it should be mentioned that as of this writing, MySQL
does not enforce column foreign key constraints, but does parse them and
ignore them without warning or comment.</p>
<h3>An Example Database Creation Script</h3>
<p>Let's get to practical grips with SQL now. Save the following
listing to a file called library.sql, and pipe it into you database
client... <code>psql < library.sql</code>. If you're using MySQL
you will need to change all instances of 'SERIAL' to 'INTEGER
AUTO_INCREMENT' in library.sql and use this command instead:
<code>mysql -p < library.sql</code>. This SQL script defines a new
database to deal with the management of a small library, including
searching for books by category, author name, or title, and managing
their withdrawal and return by members.</p>
<pre class='listing'>
CREATE TABLE Category (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE Book (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
category INTEGER REFERENCES Category NOT NULL
);
CREATE TABLE Author (
id SERIAL PRIMARY KEY,
surname VARCHAR(128) NOT NULL,
firstname VARCHAR(128) NOT NULL
);
CREATE TABLE Authorship (
author INTEGER REFERENCES Author NOT NULL,
book INTEGER REFERENCES Book NOT NULL,
PRIMARY KEY (author, book)
);
CREATE TABLE Member (
id SERIAL PRIMARY KEY,
surname VARCHAR(128) NOT NULL,
firstname VARCHAR(128) NOT NULL,
address TEXT,
phone CHAR(12),
fax CHAR(12),
signup_date TIMESTAMP DEFAULT NOW()
);
CREATE TABLE Withdrawal (
id SERIAL PRIMARY KEY,
withdrawal TIMESTAMP NOT NULL,
return TIMESTAMP NULL,
book INTEGER REFERENCES Book NOT NULL,
member INTEGER REFERENCES Member NOT NULL
);
</pre>
<h2>Inserting Rows</h2>
<p>Of course simply creating a data structure is not helpful unless we
can populate it with data. Thus we come to the insertion of rows.</p>
<pre class='definition'>
INSERT INTO <table name>
(<column name>[, <column name>[, ...]])
VALUES
(<value>[, <value>[, ...]]);
</pre>
<p>Very simply, the <em>INSERT</em> statement inserts a new rows into
the table 'table name'. In the newly inserted row, the columns listed by
name will be populated with the values listed, by respective position.
String and text data needs to be enclosed in single quotes, integers,
floats, numerics and boolean need not. Date and times also need to have
single quotes and are always in 'YYYY-MM-DD' format for dates, and
'hh:mm:ss' for times, and 'YYYY-MM-DD hh:mm:ss' for timestamps.</p>
<p>Continuing with our library example, let's use the <em>INSERT</em>
statement to populate out database with some information.</p>
<pre class='listing'>
INSERT INTO Category (name) VALUES ('Fiction');
INSERT INTO Category (name) VALUES ('Non-Fiction');
INSERT INTO Category (name) VALUES ('Reference');
INSERT INTO Book (title, category) VALUES ('Introductory Programming in Python', 2);
INSERT INTO Book (title, category)
VALUES ('Lesser Known Molluscs of the Karoo Flood Plain', 2);
INSERT INTO Book (title, category) VALUES ('Advanced Programming Techniques', 2);
INSERT INTO Book (title, category) VALUES ('PostgreSQL Reference Manual', 3);
INSERT INTO Book (title, category) VALUES ('Pride & Prejudice', 1);
INSERT INTO Book (title, category) VALUES ('The Time Machine', 1);
INSERT INTO Book (title, category) VALUES ('Day of the Triffids', 1);
INSERT INTO Book (title, category) VALUES ('The Bogus Book', 1);
INSERT INTO Author (surname, firstname) VALUES ('Dominy', 'James');
INSERT INTO Author (surname, firstname) VALUES ('Wells', 'Herbert George');
INSERT INTO Author (surname, firstname) VALUES ('Wyndham', 'John');
INSERT INTO Author (surname, firstname) VALUES ('Snailman', 'Fred B');
INSERT INTO Author (surname, firstname) VALUES ('Newman', 'Alfred E');
INSERT INTO Author (surname, firstname) VALUES ('Austen', 'Jane');
INSERT INTO Authorship (author, book) VALUES (1, 1);
INSERT INTO Authorship (author, book) VALUES (1, 2);
INSERT INTO Authorship (author, book) VALUES (5, 2);
INSERT INTO Authorship (author, book) VALUES (1, 3);
INSERT INTO Authorship (author, book) VALUES (5, 4);
INSERT INTO Authorship (author, book) VALUES (6, 5);
INSERT INTO Authorship (author, book) VALUES (2, 6);
INSERT INTO Authorship (author, book) VALUES (3, 7);
</pre>
<h2>Updating Rows</h2>
<p>We can modify the contents of rows that already exist in a table
using the <em>UPDATE</em> statement.</p>
<pre class='definition'>
UPDATE <table name>
SET <column name> = <value>[, <column name> = <value>[,...]]
[WHERE <condition>];
</pre>
<p>The <em>UPDATE</em> statements updates the contents of
<strong>all</strong> rows in the specified table, unless a WHERE clause
is given, in which case only rows that match the condition are updated.
Usually the condition will be of the form <code>WHERE prikey = <some
id></code>, but more general updates can be useful. For example one
might want to update all rows in a table that have a date column with
values before 5 years ago, such that their 'archived' column becomes
true, because they have cycled out of the active tax storage
requirements.</p>
<p>By the way, in our example library database, one of the authorship
records is wrong, so we should update it. James Dominy knows nothing
about molluscs, and Fred Snailman is in fact the correct author.</p>
<pre class='listing'>
UPDATE Authorship SET author = 4 WHERE author = 1 AND book = 2;
</pre>
<h2>Deleting Rows</h2>
<p>Obviously, over time we will need to delete records from out
database. Again, simply done with the <em>DELETE</em> statement.</p>
<pre class='definition'>
DELETE FROM <table name> [WHERE <condition>];
</pre>
<p>Note that the <em>DELETE</em> statement deletes all rows from the
table unless a WHERE clause is provided, in which case only those rows
matching the specified condition are removed. Rows cannot be removed if
they are referred to by a foreign key in another table, i.e. removal is
blocked if such a removal would cause a violation of the foreign key
constraints of the database.</p>
<p>Let's get rid of the bogus book in our example library database.</p>
<pre class='listing'>
DELETE FROM Book WHERE id = 8;
</pre>
<h2>Querying Information</h2>
<p>The true power behind SQL is the ability to dynamically specify what
information we want from the database. This is done using the
<em>SELECT</em> statement. The <em>SELECT</em> statement is possibly the
most complex statement in the SQL language, so we'll deal with it by
example, starting simple, and expanding from there.</p>
<h3>Simple SELECT Statements</h3>
<p>We'll start out with a simple single table query; What titles are
available in our library?</p>
<pre class='listing'>
SELECT title FROM Book;
</pre>
<p>As we can see the <em>SELECT</em> statement follows the form
<code>SELECT <column name> FROM <table name>;</code>. But
getting only the values from a single column is not particularly useful.
What about data from multiple columns at once?</p>
<pre class='listing'>
SELECT title, category FROM Book;
</pre>
<p>Okay, so we can list multiple columns from the table in comma
separated list. But what if we don't want all the rows from the table?
What if we only want to see non-fiction books?</p>
<pre class='listing'>
SELECT title, category FROM Book WHERE category = 2;
</pre>
<h3>Inner Joins</h3>
<p>Great! But displaying the numbers for categories and having to use
those numbers for our conditions is annoying. So how do we implement one
of those infamous join thingies?</p>
<pre class='listing'>
SELECT Book.title, Category.name
FROM Book
JOIN Category ON Category.id = Book.category
WHERE category = 2;
</pre>
<p>Reasonably obvious! After we've specified the main table to query
from using FROM, we can specify which tables to join onto it with a JOIN
clause. The JOIN clause specifies which table to join (Category in this
case) and which row from the table being joined should be joined to the
FROM table, using 'ON' and a condition (for each row in book find all
rows in Category with an id equal to the category column in Book,
combine the rows, add put them in the result table). Since we are now
dealing with multiple tables in a single query we need to prefix column
names with the table names they come from, hence 'Category.id' to
distinguish it from 'Book.id'.</p>
<p>Performing a join creates a resultant table with all the columns from
all the tables joined together. The SELECT statement then extracts only
the columns of interest. So while the above query only shows two
columns, the join itself produces a table with five columns.</p>
<h3>Left Joins</h3>
<p>So now we can join tables, but in doing so we exclude certain rows.
How do we, for example, obtain a list of all categories and their books,
even if there are no books in a category. First let's add such a
category to the library database, then we'll perform the query.</p>
<pre class='listing'>
INSERT INTO Category (name) VALUES ('Periodical');
SELECT Category.name, Book.title
FROM Category
LEFT JOIN Book ON Book.category = Category.id;
</pre>
<p>Aha! We can now see all categories, even the new one without any
associated books. The results show a row with NULL in the place of the
book title for the category 'Periodical'. This is because no row from
book could be found to join to the 'Periodical' row from Category, but
because we used a <em>LEFT JOIN</em> all rows already present in the
left hand side of the join will be preserved and the right side of the
join will be filled with NULL's where necessary.</p>
<h3>Implicit Inner Joins using WHERE</h3>
<p>We can also specify multiple tables in the FROM clause, and their
respective join conditions in the WHERE clause. This always implies an
INNER JOIN join though (i.e. a normal exclusive JOIN, and not a LEFT
JOIN)</p>
<pre class='listing'>
SELECT Book.title, Category.name
FROM Book, Category
WHERE Category.id = Book.category AND category = 2;
</pre>
<h3>Aggregation using GROUP BY</h3>
<p>We can also display how many books are in each category.</p>
<pre class='listing'>
SELECT Category.name, count(Book.id)
FROM Category
LEFT JOIN Book on Book.category = Category.id
GROUP BY Category.name;
</pre>
<p>Note the use of the function 'count'. SQL provides a number of
aggregation functions which can used to calculate values over multiple
rows of selected data. Aggregation functions are always used in
conjunction with a <em>GROUP BY</em> clause. If no GROUP BY clause is
specified, then the entire selection of rows is treated as a single
group, otherwise rows with the same values in the specified column of
the GROUP BY clause are grouped together. Each distinct value of the
column (or distinct combination of values if multiple columns are
specified) are treated as a group. Some commonly used aggregation
functions are:</p>
<dl>
<dt>count()</dt><dd>Returns the number of rows in the group.</dd>
<dt>sum()</dt><dd>Returns the sum of the values of the specified
column, which must obviously have a numeric (not NUMERIC) type.</dd>
<dt>avg()</dt><dd>Returns the average of the values of the specified
column, which must obviously have a numeric type.</dd>
</dl>
<h3>Sorting with ORDER BY</h3>
<p>Finally, we can sort the results eventually displayed.</p>
<pre class='listing'>
SELECT Category.name, count(Book.id) as number
FROM Category
LEFT JOIN Book on Book.category = Category.id
GROUP BY Category.name
ORDER BY Category.name;
</pre>
<p>And as can be seen below, we can specify sub-sorting and whether the
sort should be ascending (the default) or descending (DESC)</p>
<pre class='listing'>
SELECT Category.name, count(Book.id)
FROM Category
LEFT JOIN Book on Book.category = Category.id
GROUP BY Category.name, Book.title
ORDER BY Category.name DESC, Book.title;
</pre>
<h2>Aliasing</h2>
<p>If our table names are long, or we're just lazy, it can become a pain
to type out full table names when differentiating column names. SQL
allows us to create on the fly aliases for both table names and column
names. Observe:</p>
<pre class='listing'>
SELECT C.name as category, count(B.id) as number
FROM Category as C
LEFT JOIN Book as B on B.category = C.id
GROUP BY C.name
ORDER BY number;
</pre>
<p>Here we are dealing with two different types of aliases, although
they both have the same syntax. We can, for the scope of the current
query, alias either a column name or a table name by following it with
<code>as <alias></code>.</p>
<p>Aliasing column names affects the actual output of the query, in that
the column headings printed out (or returned to your program if
connecting to the db from python), which are generally automatically
assigned to be the names of the columns chosen, are overridden by any
alias names provided. As above, 'C.name' will now be named 'category'
instead of 'name' in the output. Another use of aliasing is that it
provides us with an easy way to reference aggregate columns in 'ORDER
BY' or 'WHERE' clauses, as done here with 'number'.</p>
<p>Aliasing tables with 'as' in the FROM and JOIN clauses allows us to
use the alias names to specify table differentiation of columns
throughout the rest of query.</p>
<h2>Dropping a table</h2>
<p>We can get rid of an entire table, assuming there are no rows in the
table which are still pointed to by foreign keys in other tables of
course, by issuing the <code>DROP TABLE <table name>;</code>
command. There's no undo, and asking for confirmation on this. Be
sure!</p>
<h2>Dropping an entire database</h2>
<p>Similarly, we can drop an entire database. Again, no confirmation, no
undo!</p>
<pre class='definition'>
DROP DATABASE <database name>;
</pre>
<h2>Transactions</h2>
<p>One other cool thing about relational database management systems
(RDBMS) is the concept of atomic transactions. Transactions allow one
to group together SQL commands into a single atomic unit, known as a
transaction. The commands are executed in sequence, but if one of the
commands does not complete successfully, the database is rolled back to
the point prior to the transaction starting, i.e. it's all or nothing.
This is very useful when we want to insert multiple different rows into
different tables that all refer to one another. If the connection goes
dead mid insertion process, we don't want half the data there with
foreign keys potentially unsatisfied. Instead we'd rather know that the
whole thing failed, nothing was changed, and that if we redo from
scratch it won't fail because unique keys already exists.</p>
<p>We start a transaction with <code>BEGIN;</code>, and everything
entered up until <code>END;</code> becomes part of the transaction. Once
the <em>END</em> command is issued, the entire transaction is executed
at once. Actually, the commands are executed as you type them in, but if
one causes an error, no more commands are valid until the 'END' of the
transaction, the all commands will be automatically undone.</p>
<!-- Entity Relationship Diagrams -->
<div class="centered">
[<a href="database_relational.html">Prev: Relational Databases</a>] [<a href="index.html">Course Outline</a>] [<a href="database_coding.html">Next: Interfacing with Databases using Python</a>]
</div>
</div>
<div class="pagefooter">
Copyright © James Dominy 2007-2008; Released under the <a href="http://www.gnu.org/copyleft/fdl.html">GNU Free Documentation License</a><br />
<a href="intropython.tar.gz">Download the tarball</a>
</div>
</body>
</html>