Reflection

SQL is a powerful and extremely concise language. You do not simply sit down and write SQL create statements to define tables in a database. Instead, you use what you learned from Chapter 5, plus your knowledge about the particular problem to design the database. Similarly, if you think of the select statement as being the equivalent of a program, you are more likely to give yourself the time to grasp the meaning of an existing select query or develop the one you need.

There can be more than one way to write a select statement to accomplish a specific task. It might be that one way is more efficient than another. When you need to turn your project into a robust application, you can find tools to analyze the workings of the select statements. MySQL has the explain command that indicates the order of operations for a statement. Such tools also can help in determining if it would be beneficial to define index fields.

A parallel to the notion that SQL statements are little programs is that they perform time-consuming actions. You need to appreciate that a middleware statement involving a select operation might not be as quick as other statements. A corollary to this is that database operations take significant enough amounts of time to perform one of these operations. Why is this something of concern? If you have more than one user reading and writing to the database at the same time, you need to be concerned with interactions between user requests. The basic DBMS makes sure that updates are uninterrupted. However, you might have a set of operations that should be invoked without interruption. Suppose that your code reads and displays information from the database. The person sitting in front of the computer takes his time and then makes a choice. Your code then does a calculation and then updates the database with new values. What if some other person got in there “in between” and made a change? The DBMS products have facilities for locking and unlocking records and, perhaps, defining a set of steps as a transaction. These can include facilities to roll back the database if something should happen in the middle of an operation or a transaction. It is in these areas that the DBMS products differ, and some have more features than others. See Chapter 16, “Scaling Up Your Application,” for more on this topic.

In the next chapter, you will read about the specifics of using MySQL and Access on servers, including how to make queries directly against the database. Then, in the following chapters, you will learn how to construct SQL statements and pass them to the DBMS for your database using PHP and ASP functions and methods.