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