Review
Product Description
SQL (Structured Query Language) is a standard programming language for generating, manipulating, and retrieving information from a relational database. If you're working with a relational database--whether you're writing applications, performing administrative tasks, or generating reports--you need to know how to interact with your data. Even if you are using a tool that generates SQL for you, such as a reporting tool, there may still be cases where you need to bypass the automatic generation feature and write your own SQL statements.
To help you attain this fundamental SQL knowledge, look to Learning SQL, an introductory guide to SQL, designed primarily for developers just cutting their teeth on the language.
Learning SQL moves you quickly through the basics and then on to some of the more commonly used advanced features. Among the topics discussed:
- The history of the computerized database
- SQL Data Statements--those used to create, manipulate, and retrieve data stored in your database; example statements include select, update, insert, and delete
- SQL Schema Statements--those used to create database objects, such as tables, indexes, and constraints
- How data sets can interact with queries
- The importance of subqueries
- Data conversion and manipulation via SQL's built-in functions
- How conditional logic can be used in Data Statements
Put the power and flexibility of SQL to work. With Learning SQL you can master this important skill and know that the SQL statements you write are indeed correct.
From the Publisher
About the Author
Alan Beaulieu has been designing, building, and implementing custom database applications for over 13 years. He currently runs his own consulting company that specializes in designing Oracle databases and supporting services in the fields of Financial Services and Telecommunications. In building large databases for both OLTP and OLAP environments, Alan utilizes such Oracle features as Parallel Query, Partitioning, and Parallel Server. Alan has a Bachelor of Science degree in Operations Research from the Cornell University School of Engineering. He lives in Massachusetts with his wife and two daughters and can be reached at albeau_mosql@yahoo.com.
Excerpted from Learning SQL by Alan Beaulieu. Copyright © 2005. Reprinted by permission. All rights reserved.
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 select statement, 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 is 2. 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:
This query returns the first and last names of all of the employees in the employee table. 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.
Query Clauses
There are several components or clauses that make up the select statement. While only one of them is mandatory when using MySQL (the select clause), you will usually include at least two or three of the six available clauses. Table 3-1 shows the different clauses and their purposes.
All of the clauses shown in Table 3-1 are included in the ANSI specification; additionally, there are several other clauses unique to MySQL that will be explored in Appendix B. The following sections delve into the uses of the six major query clauses.
