Chapter 3: Advanced SQL

Overview

You got a taste of SQL in Chapter 1, "Quickstart to MySQL," but there's much more to learn. To begin to appreciate the possibilities and approach MySQL mastery, you need to understand the various logical, arithmetic, comparison, and bit operators available to MySQL. These allow you to build much more complex queries than the simple kind that you learned in the first chapter. Similarly, you'll find the need to run queries that require data from more than one table, called joins. The vagaries of left joins, right joins, outer joins, inner joins, and natural joins can seem confusing. This chapter will introduce all these to you, showing you when and how to use them.

Featured in this chapter:

• Logical, arithmetic, comparison, and bit operators

• Advanced joins (inner, outer, left, right, and natural joins)

• Joining results with UNION

• vRewriting sub-selects as joins

• Removing records with DELETE and TRUNCATE

• User variables

• Running MySQL in batch mode

• Performing transactions with BEGIN and COMMIT

• Consistent reads

• Table locks

• Read locks for updating and for sharing