MySQL Crash Course

Ben Forta is Macromedia Corporation's product evangelist for the ColdFusion product line. Ben has over 15 years of experience in the computer industry, and spent 6 years as part of the development team responsible for creating OnTime, one of the most successful calendar and group-scheduling products, with over one million users worldwide. Ben is the author of the popular ColdFusion Web Application Construction Kit, and its sequel Advanced ColdFusion 4 Application Development. His book Sams Teach Yourself SQL in 10 Minutes is one of the world's bestselling SQL books with over 100,000 copies sold.

• MySQL¨Crash CourseTable of ContentsIntroduction                                                                   1Who Is This Book For? ................................................................................. 2Companion Website ...................................................................................... 2Conventions Used in This Book ................................................................. 31  Understanding SQL                                                        5Database Basics ............................................................................................. 5What Is SQL? ............................................................................................... 11Try It Yourself ............................................................................................... 112  Introducing MySQL                                                       13What Is MySQL? ......................................................................................... 13MySQL Tools ............................................................................................... 163  Working with MySQL                                                   21Making the Connection .............................................................................. 21Selecting a Database .................................................................................. 22Learning About Databases and Tables .................................................. 234  Retrieving Data                                                            27The SELECT Statement .............................................................................. 27Retrieving Individual Columns ................................................................ 27Retrieving Multiple Columns ................................................................... 29Retrieving All Columns ............................................................................. 31Retrieving Distinct Rows .......................................................................... 31Limiting Results ........................................................................................... 33Using Fully Qualified Table Names ...................................................... 355  Sorting Retrieved Data                                                  37Sorting Data .................................................................................................. 37Sorting by Multiple Columns .................................................................... 39Specifying Sort Direction .......................................................................... 406  Filtering Data                                                               45Using the WHERE Clause ......................................................................... 45The WHERE Clause Operators ................................................................. 467  Advanced Data Filtering                                               53Combining WHERE Clauses ..................................................................... 53Using the IN Operator ................................................................................ 57Using the NOT Operator ........................................................................... 598  Using Wildcard Filtering                                               61Using the LIKE Operator ........................................................................... 61Tips for Using Wildcards ........................................................................... 659  Searching Using RegularExpressions                              67Understanding Regular Expressions ....................................................... 67Using MySQL Regular Expressions ....................................................... 6810  Creating Calculated Fields                                             81Understanding Calculated Fields ............................................................ 81Concatenating Fields .................................................................................. 82Performing Mathematical Calculations ................................................. 8611  Using Data ManipulationFunctions                                89Understanding Functions ........................................................................... 89Using Functions ........................................................................................... 9012  Summarizing Data                                                        99Using Aggregate Functions ...................................................................... 99Aggregates on Distinct Values .............................................................. 106Combining Aggregate Functions ........................................................... 10813  Grouping Data                                                            111Understanding Data Grouping ............................................................... 111Creating Groups ......................................................................................... 112Filtering Groups ......................................................................................... 113Grouping and Sorting ................................................................................ 116SELECT Clause Ordering ......................................................................... 11914  Working with Subqueries                                             121Understanding Subqueries ....................................................................... 121Filtering by Subquery ................................................................................ 121Using Subqueries As Calculated Fields ............................................. 12615  Joining Tables                                                             131Understanding Joins .................................................................................. 131Creating a Join ............................................................................................ 13416  Creating Advanced Joins                                              143Using Table Aliases ................................................................................. 143Using Different Join Types ..................................................................... 144Using Joins with Aggregate Functions ................................................ 149Using Joins and Join Conditions ............................................................ 15117  Combining Queries                                                      153Understanding Combined Queries ........................................................ 153Creating Combined Queries ................................................................... 15418  Full-Text Searching                                                     161Understanding Full-Text Searching ...................................................... 161Using Full-Text Searching ...................................................................... 16319  Inserting Data                                                             177Understanding Data Insertion ................................................................. 177Inserting Complete Rows ........................................................................ 177Inserting Multiple Rows ........................................................................... 181Inserting Retrieved Data .......................................................................... 18320  Updating and Deleting Data                                         187Updating Data ............................................................................................ 187Deleting Data ............................................................................................. 189Guidelines for Updating and Deleting Data ...................................... 19021  Creating and ManipulatingTables                                 193Creating Tables .......................................................................................... 193Updating Tables ......................................................................................... 203Deleting Tables .......................................................................................... 205Renaming Tables ....................................................................................... 20522  Using Views                                                              207Understanding Views ............................................................................... 207Using Views ............................................................................................... 21023  Working with Stored Procedures                                  217Understanding Stored Procedures .......................................................... 217Why Use Stored Procedures ................................................................... 218Using Stored Procedures .......................................................................... 21924  Using Cursors                                                             231Understanding Cursors .............................................................................. 231Working with Cursors ............................................................................... 23225  Using Triggers                                                            241Understanding Triggers ............................................................................ 241Creating Triggers ........................................................................................ 242Dropping Triggers ...................................................................................... 243Using Triggers ............................................................................................. 24426  Managing TransactionProcessing                                  249Understanding Transaction Processing ................................................ 249Controlling Transactions .......................................................................... 25227  Globalization and Localization                                    257Understanding Character Sets and CollationSequences .............................................................................................. 257Working with Character Set and CollationSequences .............................................................................................. 25828  Managing Security                                                      263Understanding Access Control .............................................................. 263Managing Users ......................................................................................... 26429  Database Maintenance                                                273Backing Up Data ....................................................................................... 273Performing Database Maintenance ...................................................... 274Diagnosing Startup Problems ................................................................. 275Review Log Files ...................................................................................... 27530  Improving Performance                                                277Improving Performance ............................................................................ 277A  Getting Started with MySQL                                        281What YouÕll Need ..................................................................................... 281Obtaining the Software ............................................................................ 282Installing the Software ............................................................................. 282Preparing for Your Chapters ................................................................... 283B  The Example Tables                                                    285Understanding the Sample Tables ........................................................ 285Creating the Sample Tables ................................................................... 290C  MySQL Statement Syntax                                            293ALTER TABLE ............................................................................................. 293COMMIT ........................................................................................................ 294CREATE INDEX .......................................................................................... 294CREATE PROCEDURE .............................................................................. 294CREATE TABLE .......................................................................................... 294CREATE USER ............................................................................................ 295CREATE VIEW ............................................................................................ 295DELETE ......................................................................................................... 295DROP ............................................................................................................. 295INSERT .......................................................................................................... 296INSERT SELECT .......................................................................................... 296ROLLBACK .................................................................................................. 296SAVEPOINT ................................................................................................. 296SELECT ......................................................................................................... 297START TRANSACTION ............................................................................ 297UPDATE ....................................................................................................... 297D  MySQL Datatypes                                                      299String Datatypes ......................................................................................... 300Numeric Datatypes ................................................................................... 301Date and Time Datatypes ....................................................................... 303Binary Datatypes ....................................................................................... 304E  MySQL Reserved Words                                              305Index                                                                         309