Query Primer (Page 1 of 4 )
If you're learning SQL, it's important to understand the Select statement. This article, the first of three parts, dissects its different parts. It is excerpted from chapter three of the book
Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
So far, you have seen a few examples of database queries (a.k.a. select statements) sprinkled throughout the first two chapters. Now it’s time to take a closer look at the different parts of the select statement and how they interact.
Query Mechanics Before dissecting the selectstatement, it might be interesting to look at how queries are executed by the MySQL server (or, for that matter, any database server). If you are using the mysql command-line tool (which I assume you are), then you have already logged in to the MySQL server by providing your username and password (and possibly a hostname if the MySQL server is running on a different computer). Once the server has verified that your username and password are correct, a database connection is generated for you to use. This connection is held by the application that requested it (which, in this case, is the mysql tool) until either the application releases the connection (i.e., as a result of your typing quit) or the server closes the connection (i.e., when the server is shut down). Each connection to the MySQL server is assigned an identifier, which is shown to you when you first log in:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.11-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
In this case, my connection ID is2. This information might be useful to your database administrator if something goes awry, such as a malformed query that runs for hours, so you might want to jot it down.
Once the server has verified your username and password and issued you a connection, you are ready to execute queries (along with other SQL statements). Each time a query is sent to the server, the server checks the following things prior to statement execution:
- Do you have permission to execute the statement?
- Do you have permission to access the desired data?
- Is your statement syntax correct?
If your statement passes these three tests, then your query is handed to the query optimizer, whose job it is to determine the most efficient way to execute your query. The optimizer will look at such things as the order in which to join the tables named in the query and what indexes are available, and then picks an execution plan, which is used by the server to execute your query.
Understanding and influencing how your database server chooses execution plans is a fascinating topic that many of you will wish to explore. For those readers using MySQL, you might consider reading High Performance MySQL (O’Reilly). Among other things, you will learn how to generate indexes, analyze execution plans, influence the optimizer via query hints, and tune your server’s startup parameters. If you are using Oracle Database or SQL Server, there are dozens of tuning books available.
Once the server has finished executing your query, the result set is returned to the calling application (which is, once again, the mysql tool). As was mentioned in Chapter 1, a result set is just another table containing rows and columns. If your query fails to yield any results, the mysql tool will show you the message found at the end of the following example:
mysql> SELECT emp_id, fname, lname
-> FROM employee
-> WHERE lname = 'Bkadfl';
Empty set (0.00 sec)
If the query returns one or more rows, the mysql tool will format the results by adding column headers and by constructing boxes around the columns using the -, |, and + symbols, as shown in the next example:
mysql> SELECT fname, lname
-> FROM employee;
+----------+-----------+
| fname | lname |
+----------+-----------+
| Michael | Smith |
| Susan | Barker |
| Robert | Tyler |
| Susan | Hawthorne |
| John | Gooding |
| Helen | Fleming |
| Chris | Tucker |
| Sarah | Parker |
| Jane | Grossman |
| Paula | Roberts |
| Thomas | Ziegler |
| Samantha | Jameson |
| John | Blake |
| Cindy | Mason |
| Frank | Portman |
| Theresa | Markham |
| Beth | Fowler |
| Rick | Tulman |
+----------+-----------+
18 rows in set (0.00 sec)
This query returns the first and last names of all of the employees in theemployeetable. After the last row of data is displayed, the mysql tool displays a message telling you how many rows were returned, which, in this case, is 18.
Next: Query Clauses >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 2 
