MySQL Crash Course
Published on 22. December 2005
Book
Paperback/Softback
336 pages
978-0-672-32712-4 (ISBN)
Description
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.
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.
More details
Other editions
New editions
Person
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.
Content
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 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
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