MySQL is one of the most popular database management systems available, powering everything from Internet powerhouses to individual corporate databases to simple end-user applications, and everything in between. And this book will teach you all you need to know to be immediately productive with MySQL. By working through 30 highly focused hands-on lessons, your MySQL Crash Course will be both easier and more effective than you'd have thought possible. Learn how to: Retrieve and sort data Filter data using comparisons, regular expressions, full text search, and much more Join relational data Create and alter tables Insert, update, and delete data Leverage the power of stored procedures and triggers Use views and Cursors Manage transactional processing Create user accounts and manage security via access control Ben Forta is Macromedia's Senior Technical Evangelist, and has almost 20 years of experience in the computer industry in product development, support, training, and product marketing. Ben is the author of the best-selling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into over a dozen languages), ColdFusion Web Application Construction Kit, and Advanced ColdFusion Development (both published by Que Publishing), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as books on SQL, Flash, JSP, HomeSite, WAP, Windows 2000, and other subjects.

Table of Contents:
MySQL¨Crash Course Table of Contents Introduction                                                                   1 Who Is This Book For? ................................................................................. 2 Companion Website ...................................................................................... 2 Conventions Used in This Book ................................................................. 3 1  Understanding SQL                                                        5 Database Basics ............................................................................................. 5 What Is SQL? ............................................................................................... 11 Try It Yourself ............................................................................................... 11 2  Introducing MySQL                                                       13 What Is MySQL? ......................................................................................... 13 MySQL Tools ............................................................................................... 16 3  Working with MySQL                                                   21 Making the Connection .............................................................................. 21 Selecting a Database .................................................................................. 22 Learning About Databases and Tables .................................................. 23 4  Retrieving Data                                                            27 The SELECT Statement .............................................................................. 27 Retrieving Individual Columns ................................................................ 27 Retrieving Multiple Columns ................................................................... 29 Retrieving All Columns ............................................................................. 31 Retrieving Distinct Rows .......................................................................... 31 Limiting Results ........................................................................................... 33 Using Fully Qualified Table Names ...................................................... 35 5  Sorting Retrieved Data                                                  37 Sorting Data .................................................................................................. 37 Sorting by Multiple Columns .................................................................... 39 Specifying Sort Direction .......................................................................... 40 6  Filtering Data                                                               45 Using the WHERE Clause ......................................................................... 45 The WHERE Clause Operators ................................................................. 46 7  Advanced Data Filtering                                               53 Combining WHERE Clauses ..................................................................... 53 Using the IN Operator ................................................................................ 57 Using the NOT Operator ........................................................................... 59 8  Using Wildcard Filtering                                               61 Using the LIKE Operator ........................................................................... 61 Tips for Using Wildcards ........................................................................... 65 9  Searching Using RegularExpressions                              67 Understanding Regular Expressions ....................................................... 67 Using MySQL Regular Expressions ....................................................... 68 10  Creating Calculated Fields                                             81 Understanding Calculated Fields ............................................................ 81 Concatenating Fields .................................................................................. 82 Performing Mathematical Calculations ................................................. 86 11  Using Data ManipulationFunctions                                89 Understanding Functions ........................................................................... 89 Using Functions ........................................................................................... 90 12  Summarizing Data                                                        99 Using Aggregate Functions ...................................................................... 99 Aggregates on Distinct Values .............................................................. 106 Combining Aggregate Functions ........................................................... 108 13  Grouping Data                                                            111 Understanding Data Grouping ............................................................... 111 Creating Groups ......................................................................................... 112 Filtering Groups ......................................................................................... 113 Grouping and Sorting ................................................................................ 116 SELECT Clause Ordering ......................................................................... 119 14  Working with Subqueries                                             121 Understanding Subqueries ....................................................................... 121 Filtering by Subquery ................................................................................ 121 Using Subqueries As Calculated Fields ............................................. 126 15  Joining Tables                                                             131 Understanding Joins .................................................................................. 131 Creating a Join ............................................................................................ 134 16  Creating Advanced Joins                                              143 Using Table Aliases ................................................................................. 143 Using Different Join Types ..................................................................... 144 Using Joins with Aggregate Functions ................................................ 149 Using Joins and Join Conditions ............................................................ 151 17  Combining Queries                                                      153 Understanding Combined Queries ........................................................ 153 Creating Combined Queries ................................................................... 154 18  Full-Text Searching                                                     161 Understanding Full-Text Searching ...................................................... 161 Using Full-Text Searching ...................................................................... 163 19  Inserting Data                                                             177 Understanding Data Insertion ................................................................. 177 Inserting Complete Rows ........................................................................ 177 Inserting Multiple Rows ........................................................................... 181 Inserting Retrieved Data .......................................................................... 183 20  Updating and Deleting Data                                         187 Updating Data ............................................................................................ 187 Deleting Data ............................................................................................. 189 Guidelines for Updating and Deleting Data ...................................... 190 21  Creating and ManipulatingTables                                 193 Creating Tables .......................................................................................... 193 Updating Tables ......................................................................................... 203 Deleting Tables .......................................................................................... 205 Renaming Tables ....................................................................................... 205 22  Using Views                                                              207 Understanding Views ............................................................................... 207 Using Views ............................................................................................... 210 23  Working with Stored Procedures                                  217 Understanding Stored Procedures .......................................................... 217 Why Use Stored Procedures ................................................................... 218 Using Stored Procedures .......................................................................... 219 24  Using Cursors                                                             231 Understanding Cursors .............................................................................. 231 Working with Cursors ............................................................................... 232 25  Using Triggers                                                            241 Understanding Triggers ............................................................................ 241 Creating Triggers ........................................................................................ 242 Dropping Triggers ...................................................................................... 243 Using Triggers ............................................................................................. 244 26  Managing TransactionProcessing                                  249 Understanding Transaction Processing ................................................ 249 Controlling Transactions .......................................................................... 252 27  Globalization and Localization                                    257 Understanding Character Sets and Collation Sequences .............................................................................................. 257 Working with Character Set and Collation Sequences .............................................................................................. 258 28  Managing Security                                                      263 Understanding Access Control .............................................................. 263 Managing Users ......................................................................................... 264 29  Database Maintenance                                                273 Backing Up Data ....................................................................................... 273 Performing Database Maintenance ...................................................... 274 Diagnosing Startup Problems ................................................................. 275 Review Log Files ...................................................................................... 275 30  Improving Performance                                                277 Improving Performance ............................................................................ 277 A  Getting Started with MySQL                                        281 What YouÕll Need ..................................................................................... 281 Obtaining the Software ............................................................................ 282 Installing the Software ............................................................................. 282 Preparing for Your Chapters ................................................................... 283 B  The Example Tables                                                    285 Understanding the Sample Tables ........................................................ 285 Creating the Sample Tables ................................................................... 290 C  MySQL Statement Syntax                                            293 ALTER TABLE ............................................................................................. 293 COMMIT ........................................................................................................ 294 CREATE INDEX .......................................................................................... 294 CREATE PROCEDURE .............................................................................. 294 CREATE TABLE .......................................................................................... 294 CREATE USER ............................................................................................ 295 CREATE VIEW ............................................................................................ 295 DELETE ......................................................................................................... 295 DROP ............................................................................................................. 295 INSERT .......................................................................................................... 296 INSERT SELECT .......................................................................................... 296 ROLLBACK .................................................................................................. 296 SAVEPOINT ................................................................................................. 296 SELECT ......................................................................................................... 297 START TRANSACTION ............................................................................ 297 UPDATE ....................................................................................................... 297 D  MySQL Datatypes                                                      299 String Datatypes ......................................................................................... 300 Numeric Datatypes ................................................................................... 301 Date and Time Datatypes ....................................................................... 303 Binary Datatypes ....................................................................................... 304 E  MySQL Reserved Words                                              305 Index                                                                         309