Learning SQL

Appendix B. MySQL Extensions to the SQL Language

Since this book uses the MySQL server for all of the examples, I thought it would be useful for those readers planning to continue using MySQL to include an Appendix on MySQL's extensions to the SQL language. This appendix explores some of MySQL's extensions to the select, insert, update, and delete statements that can be very useful in certain situations.

Select Extensions

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

The limit Clause

In some situations, you may not be interested in all of the rows returned by a query. For example, you might construct a query that returns all of the bank tellers along with the number of accounts opened by each teller. If your reason for executing the query is to determine the top three tellers so that they can receive an award from the bank, then you don't necessarily need to know who came in fourth, fifth, etc. To help with these types of situations, MySQL's select statement includes the limit clause, which allows you to restrict the number of rows returned by a query.

To demonstrate the utility of the limit clause, I will begin by constructing a query to show the number of accounts opened by each bank teller:

    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 | ...