MySQL 5.1 for Developers

1 Introduction

1.1 Learning Objectives
1.2 MySQL Overview
1.3 MySQL Products
1.4 MySQL Services
1.5 The MySQL Enterprise Subscription

2 MySQL Client/Server Concepts

2.1 Learning Objectives
2.2 MySQL General Architecture
2.3 How MySQL Uses Disk Space

3 MySQL Clients

3.1 Learning Objectives
3.2 Invoking Client Programs
3.3 Using Option Files
3.4 The mysql command line client
3.5 MySQL Query Browser
3.6 MySQL Connectors
3.7 Third-Party API’s

4 Querying for Table Data

4.1 Learning Objectives
4.2 The SELECT Statement
4.3 Aggregating Query Results
4.4 Using UNION 31

5 Handling Errors and Warnings

5.1 Learning Objectives
5.2 SQL modes affecting syntax
5.3 Handling Missing or Invalid Data Values

6 Data Types

6.1 Learning Objectives
6.2 Data Type Overview
6.3 Numeric Data Types
6.4 Character String Data Types
6.5 Binary String Data Types
6.6 Temporal Data Types
6.7 NULLs
6.8 Chapter Summary

7 SQL Expressions

7.1 Learning Objectives
7.2 SQL Comparisons
7.3 Functions in SQL Expressions

8 Obtaining Metadata

8.1 Learning Objectives
8.2 Metadata Access Methods
8.3 The INFORMATION_SCHEMA database
8.4 Using SHOW and DESCRIBE
8.5 The mysqlshow Client Program

9 Databases

9.1 Learning Objectives
9.2 Database Properties
9.3 Design Practices
9.4 Identifiers
9.5 Creating Databases
9.6 Altering Databases
9.7 Dropping Databases

10 Tables

10.1 Learning Objectives
10.2 Creating Tables
10.3 Altering Tables
10.4 Dropping Tables
10.5 Foreign Key Constraints

11 Manipulating Table Data

11.1 Learning Objectives
11.2 The INSERT Statement
11.3 The DELETE Statement
11.4 The UPDATE Statement
11.5 The REPLACE Statement
11.6 INSERT with ON DUPLICATE KEY UPDATE
11.7 The TRUNCATE TABLE Statement

12 Transactions

12.1 Learning Objectives
12.2 What is a Transaction?
12.3 Transaction Control Statements
12.4 Isolation Levels
12.5 Locking

13 Joins

13.1 Learning Objectives
13.2 What is a Join?
13.3 Joining tables in SQL
13.4 Basic JOIN syntax
13.5 Inner joins29
13.6 Outer joins
13.7 Other types of joins
13.8 Joins in UPDATE and DELETE statements

14 Subqueries

14.1 Learning Objectives
14.2 Overview
14.3 Types of subqueries
14.4 Table subquery operators
14.5 Correlated and non-Correlated Subqueries
14.6 Converting Subqueries to Joins

15 Views

15.1 Learning Objectives
15.2 What Are Views?
15.3 Creating Views
15.5 Managing Views
15.6 Obtaining View Metadata

16 Prepared Statements

16.1 Learning Objectives
16.2 Why Use Prepared Statements?
16.3 Using Prepared Statements from the mysql Client
16.4 Preparing a Statement
16.5 Executing a Prepared Statement
16.6 Deallocating a Prepared Statement

17 Exporting and Importing Data

17.1 Learning Objectives
17.2 Exporting and Importing Data
17.3 Export and Import Data Using SQL
17.4 Export and Import Using MySQL Client Programs

18 Stored Routines

18.1 Learning Objectives
18.2 What is a Stored Routine?
18.3 Creating Stored Routines
18.4 Compound Statements
18.5 Assign Variables
18.6 Parameter Declarations
18.7 Execute Stored Routines
18.8 Examine Stored Routines
18.9 Delete Stored Routines
18.10 Flow Control Statements
18.11 Declare and Use Handlers
18.12 Cursors.18-34

19 Triggers

19.1 Learning Objectives
19.2 What are Triggers?
19.3 Delete Triggers
19.4 Restrictions on Triggers

20 Storage Engines

20.1 Learning Objectives
20.2 SQL Parser and Storage Engine Tiers
20.3 Storage Engines and MySQL
20.4 The MyISAM Storage Engine
20.4.1 MyISAM Row Storage Formats
20.4.2 Compressing MyISAM Tables
20.4.3 MyISAM Locking
20.5 The InnoDB Storage Engine
20.5.1 The InnoDB Tablespace and Logs
20.5.2 InnoDB and ACID Compliance
20.5.3 InnoDB Locking
20.6 The MEMORY Storage Engine
20.6.1 MEMORY Indexing Options
20.6.2 Storage Engine Summary
20.7 Other Storage Engines

21 Optimization

21.1 Learning Objectives
21.2 Overview of Optimization Principles
21.3 Using Indexes for Optimization
21.3.1 Types of Indexes
21.3.2 Creating Indexes
21.3.3 Adding Indexes to Existing Tables
21.3.4 Dropping Indexes
21.3.5 Principles for Index Creation
21.3.6 Indexing Column Prefixes
21.3.7 Leftmost Index Prefixes
21.3.8 FULLTEXT Indexes
21.4 Using EXPLAIN to Analyze Queries
21.4.1 How EXPLAIN Works
21.4.2 EXPLAIN Output Columns
21.5 Query Rewriting Techniques
21.6 Optimizing Queries by Limiting Output
21.7 Using Summary Tables
21.8 Optimizing Updates
21.9 Choosing Appropriate Storage Engines