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 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