MySQL Extensions to the SQL Language: Appendix B - Learning SQL

MySQL’s implementation of the select statement includes two additional clauses, which are discussed in the following subsections.

mysql> SELECT open_emp_id, COUNT(*) how_many

    -> FROM account
    -> GROUP BY open_emp_id;
+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
|           1 |        8 |
|          10 |        7 |
|          13 |        3 |
|          16 |        6 |
+-------------+----------+
4 rows in set (0.31 sec)

mysql> SELECT open_emp_id, COUNT(*) how_many

    -> FROM account
    -> GROUP BY open_emp_id
    -> LIMIT 3;
+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
|           1 |        8 |
|          10 |        7 |
|          13 |        3 |
+-------------+----------+
3 rows in set (0.06 sec)

mysql> SELECT open_emp_id, COUNT(*) how_many

    -> FROM account
    -> GROUP BY open_emp_id
    -> ORDER BY how_many DESC
    -> LIMIT 3;

+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
|           1 |        8 |
|          10 |        7 |
|          16 |        6 |
+-------------+----------+
3 rows in set (0.03 sec)

mysql> SELECT open_emp_id, COUNT(*) how_many

    -> FROM account
    -> GROUP BY open_emp_id
    -> ORDER BY how_many DESC
    -> LIMIT 2, 1;

+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
|          16 |        6 |
+-------------+----------+
1 row in set (0.00 sec)

mysql> SELECT open_emp_id, COUNT(*) how_many

    -> FROM account
    -> GROUP BY open_emp_id
    -> ORDER BY how_many DESC
    -> LIMIT 2, 999999999;

+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
|          16 |        6 |
|          13 |        3 |
+-------------+----------+
2 rows in set (0.00 sec)

mysql> SELECT open_emp_id, COUNT(*) how_many

    -> FROM account
    -> GROUP BY open_emp_id
    -> ORDER BY how_many ASC
    -> LIMIT 2;

+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
|          13 |        3 |
|          16 |        6 |
+-------------+----------+
2 rows in set (0.24 sec)

mysql> SELECT emp_id, fname, lname, start_date

    -> INTO OUTFILE 'C:\\TEMP\\emp_list.txt'
    -> FROM employee; Query OK, 18 rows affected (0.20 sec)

1    Michael    Smith    2001-06-22
2    Susan    Barker    2002-09-12
3    Robert    Tyler    2000-02-09
4    Susan    Hawthorne    2002-04-24
...
16    Theresa    Markham    2001-03-15
17    Beth    Fowler    2002-06-29
18    Rick    Tulman    2002-12-12

mysql> SELECT emp_id, fname, lname, start_date
    -> INTO OUTFILE 'C:\\TEMP\\emp_list_delim.txt'

    ->   FIELDS TERMINATED BY '|'
    -> FROM employee; Query OK, 18 rows affected (0.02 sec)

1|Michael|Smith|2001-06-22
2|Susan|Barker|2002-09-12
3|Robert|Tyler|2000-02-09
4|Susan|Hawthorne|2002-04-24
...
16|Theresa|Markham|2001-03-15
17|Beth|Fowler|2002-06-29
18|Rick|Tulman|2002-12-12

mysql> SELECT data.num, data.str1, data.str2

    -> INTO OUTFILE 'C:\\TEMP\\comma1.txt'
    ->   FIELDS TERMINATED BY ','
    -> FROM
    ->  (SELECT 1 num, 'This string has no commas' str1,

    ->     'This string, however, has two commas' str2) data;
Query OK, 1 row affected (0.04 sec)

1,This string has no commas,This string\, however\, has two commas

mysql> SELECT emp_id, fname, lname, start_date
    -> INTO OUTFILE 'C:\\TEMP\\emp_list_atsign.txt'
    ->   FIELDS TERMINATED BY '|'

    ->   LINES TERMINATED BY '@'
    -> FROM employee;
Query OK, 18 rows affected (0.03 sec)

1|Michael|Smith|2001-06-22@2|Susan|Barker|2002-09-12@3|Robert|Tyler|2000-02-
09@4|Susan|Hawthorne|2002-04-24@5|John|Gooding|2003-11-14@6|Helen|Fleming|2004-03-
17@7|Chris|Tucker|2004-09-15@8|Sarah|Parker|2002-12-02@9|Jane|Grossman|2002-05-
03@10|Paula|Roberts|2002-07-27@11|Thomas|Ziegler|2000-10-23@12|Samantha|Jameson|2003-
01-08@13|John|Blake|2000-05-11@14|Cindy|Mason|2002-08-09@15|Frank|Portman|2003-04-
01@16|Theresa|Markham|2001-03-15@17|Beth|Fowler|2002-06-29@18|Rick|Tulman|2002-12-12@

Let’s say that you have been asked to create a table to capture information about which of the bank’s branches are visited by which customers. The table needs to contain the customer’s ID, the branch’s ID, and a datetime column indicating the last time the customer visited the branch. Rows are added to the table whenever a customer visits a certain branch, but if the customer has already visited the branch, then the existing row should simply have its datetime column updated. Here’s the table definition:

CREATE TABLE branch_usage
 (branch_id SMALLINT UNSIGNED NOT NULL,
  cust_id INTEGER UNSIGNED NOT NULL,
  last_visited_on DATETIME,
  CONSTRAINT pk_branch_usage PRIMARY KEY (branch_id, cust_id)
 );

Along with the three column definitions, the branch_usage table defines a primary key constraint on the branch_id and cust_id columns. Therefore, the server will reject any row added to the table whose branch/customer pair already exists in the table.

Let’s say that, after the table is in place, customer ID 5 visits the main branch (branch ID 1) three times in the first week. After the first visit, you can insert a record into the branch_usage table, since no record exists yet for customer ID 5 and branch ID 1:

mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)

    -> VALUES (1, 5, CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.02 sec)

The next time the customer visits the same branch, however, you will need to update the existing record rather than inserting a new record; otherwise, you will receive the following error:

ERROR 1062 (23000): Duplicate entry '1-5' for key 1

To avoid this error, you can query the branch_usage table to see whether a given customer/branch pair exists and then either insert a record if no record is found or update the existing row if it already exists. To save you the trouble, however, the MySQL designers have extended the insert statement to allow you to specify that one or more columns be modified if an insert statement fails due to a duplicate key. The following statement instructs the server to modify the last_visited_on column if the given customer and branch already exist in the branch_usage table:

mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)

    -> VALUES (1, 5, CURRENT_TIMESTAMP())
    -> ON DUPLICATE KEY UPDATE last_visited_on = CURRENT_TIMESTAMP();
Query OK, 2 rows affected (0.02 sec)

The on duplicate key clause allows this same statement to be executed every time customer ID 5 conducts business in branch ID 1. If run 100 times, the first execution results in a single row being added to the table, and the next 99 executions result in the last_visited_on column being changed to the current time. This type of operation is often referred to as an upsert, since it is a combination of an update and an insert statement.

Earlier in the appendix, I showed you how to write queries using the limit clause in conjunction with an order by clause to generate rankings, such as the top three tellers in terms of accounts opened. MySQL also allows the limit and order by clauses to be used in both update and delete statements, thereby allowing you to modify or remove specific rows in a table based on a ranking. For example, imagine that you are asked to remove records from a table used to track customer logins to the bank’s online banking system. The table, which tracks the customer ID and date/time of login, looks as follows:

CREATE TABLE login_history
 (cust_id INTEGER UNSIGNED NOT NULL,
  login_date DATETIME,
  CONSTRAINT pk_login_history PRIMARY KEY (cust_id, login_date)
 );

The following statement populates the login_history table with some data by generating a cross join between the account and customer tables and using the account’s open_date column as a basis for generating login dates:

mysql> INSERT INTO login_history (cust_id, login_date)

    -> SELECT c.cust_id,
    ->   ADDDATE(a.open_date, INTERVAL a.account_id * c.cust_id HOUR)
    -> FROM customer c CROSS JOIN account a;
Query OK, 312 rows affected (0.03 sec)
Records: 312  Duplicates: 0  Warnings: 0

The table is now populated with 312 rows of relatively random data. Your task is to look at the data in the login_history table once a month, generate a report for your manager showing who is using the online banking system, and then delete all but the 50 most-recent records from the table. One approach would be to write a query using order by and limit to find the 50^th most recent login, such as:

mysql> SELECT login_date

    -> FROM login_history
    -> ORDER BY login_date DESC
    -> LIMIT 49,1;
+---------------------+
| login_date          |
+---------------------+
| 2004-07-02 09:00:00 |
+---------------------+
1 row in set (0.00 sec)

Armed with this information, you can then construct a delete statement that removes all rows whose login_date column is less than the date returned by the query:

mysql> DELETE FROM login_history

    -> WHERE login_date < '2004-07-02 09:00:00';
Query OK, 262 rows affected (0.02 sec)

The table now contains the 50 most-recent logins. Using MySQL’s extensions, however, you can achieve the same result with a single delete statement using limit and order by clauses. After returning the original 312 rows to the login_history table, you can run the following:

mysql> DELETE FROM login_history

    -> ORDER BY login_date ASC
    -> LIMIT 262;
Query OK, 262 rows affected (0.05 sec)

With this statement, the rows are sorted by login_date in ascending order, and then the first 262 rows are deleted, leaving the 50 most recent rows.

DELETE FROM login_history
ORDER BY login_date DESC
LIMIT 49, 9999999;

Along with deleting data, you can use the limit and order by clauses when modifying data as well. For example, if the bank decides to add $100 to each of the 10 oldest accounts to help retain loyal customers, you can do the following:

mysql> UPDATE account

    -> SET avail_balance = avail_balance + 100
    -> WHERE product_cd IN ('CHK', 'SAV', 'MM')
    -> ORDER BY open_date ASC
    -> LIMIT 10;

Query OK, 10 rows affected (0.06 sec)
Rows matched: 10  Changed: 10  Warnings: 0

This statement sorts accounts by the open date in ascending order and then modifies the first 10 records, which, in this case, are the 10 oldest accounts.

In certain situations, you might need to modify or delete data from several different tables to perform a given task. If you discover that the bank’s database contains a dummy customer left over from system testing, for example, you might need to remove data from the account, customer, and individual tables.

CREATE TABLE individual2 AS
SELECT * FROM individual;
CREATE TABLE customer2 AS
SELECT * FROM customer;
CREATE TABLE account2 AS
SELECT * FROM account;

If the customer ID of the dummy customer is 1, you could generate three individual delete statements against each of the three tables, as in:

DELETE FROM account2
WHERE cust_id = 1;
DELETE FROM customer2
WHERE cust_id = 1;
DELETE FROM individual2
WHERE cust_id = 1;

Instead of writing individual delete statements, however, MySQL allows you to write a single multitable delete statement, which, in this case, looks as follows:

mysql> DELETE account2, customer2, individual2

    -> FROM account2 INNER JOIN customer2
    ->   ON account2.cust_id = customer2.cust_id
    ->   INNER JOIN individual2
    ->   ON customer2.cust_id = individual2.cust_id

    -> WHERE individual2.cust_id = 1;
Query OK, 5 rows affected (0.02 sec)

This statement removes a total of five rows, one from each of the individual2 and customer2 tables, and three from the account2 table (customer ID 1 has three accounts). The statement comprises three separate clauses:

The multitable delete statement looks a lot like a select statement, except that a delete clause is used instead of a select clause. If you are deleting rows from a single table using a multitable delete format, the difference becomes even less noticeable. For example, here’s a select statement that finds the account IDs of all accounts owned by John Hayward:

mysql> SELECT account2.account_id

    -> FROM account2 INNER JOIN customer2
    ->   ON account2.cust_id = customer2.cust_id
    ->   INNER JOIN individual2
    ->   ON individual2.cust_id = customer2.cust_id

    -> WHERE individual2.fname = 'John'
    ->   AND individual2.lname = 'Hayward';
+------------+
| account_id |
+------------+
|          8 |
|          9 |
|         10 |
+------------+
3 rows in set (0.01 sec)

If, after viewing the results, you decide to delete all three of John’s accounts from the account2 table, you need only replace the select clause in the previous query with a delete clause naming the account2 table, as in:

mysql> DELETE account2
    -> FROM account2 INNER JOIN customer2

    ->   ON account2.cust_id = customer2.cust_id
    ->   INNER JOIN individual2
    ->   ON customer2.cust_id = individual2.cust_id
    -> WHERE individual2.fname = 'John'

    ->   AND individual2.lname = 'Hayward';
Query OK, 3 rows affected (0.01 sec)

Hopefully, this gives you a better idea of what the delete and from clauses are used for in a multitable delete statement. This statement is functionally identical to the following single-table delete statement, which uses a subquery to identify the customer ID of John Hayward:

DELETE FROM account2
WHERE cust_id =
 (SELECT cust_id
  FROM individual2
  WHERE fname = 'John' AND lname = 'Hayward';

When using a multitable delete statement to delete rows from a single table, you are simply choosing to use a querylike format involving table joins rather than a traditional delete statement using subqueries. The real power of multitable delete statements lies in the ability to delete from multiple tables in a single statement, as I demonstrated in the first statement in this section.

Along with the ability to delete rows from multiple tables, MySQL also gives you the ability to modify rows in multiple tables using a multitable update. Let’s say that your bank is merging with another bank, and the databases from both banks have overlapping customer IDs. Your management decides to fix the problem by incrementing each customer ID in your database by 10,000 so that the second bank’s data can be safely imported. The following statement shows how to modify the ID of customer ID 3 across the individual2, customer2, and account2 tables using a single statement:

mysql> UPDATE individual2 INNER JOIN customer2

    ->   ON individual2.cust_id = customer2.cust_id
    ->   INNER JOIN account2
    ->   ON customer2.cust_id = account2.cust_id
    -> SET individual2.cust_id = individual2.cust_id + 10000,

    ->   customer2.cust_id = customer2.cust_id + 10000,
    ->   account2.cust_id = account2.cust_id + 10000
    -> WHERE individual2.cust_id = 3;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 5  Changed: 4  Warnings: 0

This statement modifies four rows: one in each of the individual2 and customer2 tables, and two in the account2 table. The multitable update syntax is very similar to that of the single-table update, except that the update clause contains multiple tables and their corresponding join conditions rather than just naming a single table. Just like the single-table update, the multitable version includes a set clause, the difference being that any tables referenced in the update clause may be modified via the set clause.