MySQL

Client/Server Concepts

• General MySQL Architecture
• Invoking Client Programs
• Server SQL Modes

MySQL Client Program

• Using mysql Interactively
• Statement Terminators
• The mysql Prompts
• Editing Keys in mysql
• Using Script Files with MySQL
• mysql Output Formats
• Client Commands and SQL Statements
• Using Server-Side Help
• Using the --safe-updates Option

Data Types

• Numeric Data Types
• The BIT Data Type
• String Data Types
• Temporal Data Types
• Column Attributes
• Using the AUTO_INCREMENT Column Attribute
• Handling Missing or Invalid Data Values

Identifiers

• Identifier Syntax
• Case Sensitivity
• Using Qualified Names
• Reserved Words as Identifiers

Databases

• Database Properties
• Creating Databases
• Altering Databases
• Dropping Databases
• Obtaining Database Metatdata

Tables and Indexes

• Table Properties
• Creating Tables
• Altering Tables
• Emptying Tables
• Dropping TablesIndexes
• Indexes
• Dropping Indexes
• Obtaining Table and Index Metadata

Querying for Data

• Using SELECT to Retrieve Data
• Specifying Which Columns to Retrieve
• Specifying Which Rows to Retrieve
• Aggregating Results
• Grouping Results
• Using UNION

SQL Expressions

• Components of SQL Expressions
• Numeric Expressions
• String Expressions
• Temporal Expressions
• NULL Values
• Functions in SQL Expressions
• Functions in SQL Expressions
• Comments in SQL Statements

Updating Data

• Update Operations
• The INSERT Statement
• The REPLACE Statement
• The UPDATE Statement
• The DELETE and TRUNCATE TABLE Statements
• Privileges Required for Update Statements

Joins

• Overview
• Writing Inner Joins
• Writing Outer Joins
• Resolving Name Clashes Using Qualifiers and Aliases
• Multiple-Table UPDATE and DELETE Statements

Multiple-Table UPDATE and DELETE Statements

• Types of Subqueries
• Subqueries as Scalar Expressions
• Correlated Subqueries
• Comparing Subquery Results to Outer Query Columns
• Comparison Using Row Subqueries
• Using Subqueries in the FROM Clause
• Converting Subqueries to Joins
• Using Subqueries in Updates

Views

• Reasons to Use Views
• Creating Views
• Altering Views
• Dropping Views
• Checking Views
• Displaying Information About Views
• Privileges for Views

Importing and Exporting Data

• Import and Export Operations
• Importing and Exporting Using SQL
• Importing and Exporting Data from the Command Line

User Variables

• User Variable Syntax
• User Variable Properties

Prepared Statements

• Benefits of Prepared Statements
• Using Prepared Statements from the mysql Client
• Preparing a Statement
• Executing a Prepared Statement
• Deallocating a Prepared Statement

Stored Routines

• Benefits of Stored Routines
• Differences Between Stored Procedures and Functions
• The Namespace for Stored Routines
• Defining Stored Routines
• Creating Stored Routines
• Altering Stored Routines
• Dropping Stored Routines
• Invoking Stored Routines
• Obtaining Stored Routine Metadata
• Stored Routine Privileges and Execution Security

Triggers

• Reasons to Use Triggers
• Trigger Concepts
• Creating a Trigger
• Restrictions on Triggers
• Referring to Old and New Column Values
• Destroying a Trigger
• Privileges Required for Triggers

Obtaining Database Metadata

• Overview of Metadata Access Methods
• Using INFORMATION_SCHEMA to Obtain Metadata
• Using SHOW and DESCRIBE to Obtain Metadata
• Using mysqlshow to Obtain Metadata

Debugging MySQL Applications

• Interpreting Error Messages
• The SHOW WARNINGS Statement
• The SHOW ERRORS Statement
• The perror Utility

Basic Optimizations

• Overview of Optimization Principles
• Using Indexes for Optimization
• General Query Enhancement
• Choosing Appropriate Storage Engines
• Normalization

MySQL Architecture

• Client/Server Overview
• Communication Protocols
• The SQL Parser and Storage Engine Tiers
• How MySQL Uses Disk Space
• How MySQL Uses Memory

Starting, Stopping, and Configuring MySQL

• Types of MySQL Distributions
• Starting and Stopping MySQL Server on Windows
• Starting and Stopping MySQL Server on Unix

Starting, Stopping, and Configuring MySQL

• Runtime MySQL Configuration
• Log and Status Files
• Loading Time Zone Tables
• Security-Related Configuration
• Setting the Default SQL Mode
• Upgrading MySQL

Client Programs for DBA Work

• Overview of Administrative Clients
• mysql
• mysqladmin
• mysqlimport
• mysqldump
• Client Program Limitations

Character Set Support

• Performance Issues
• Choosing Data Types for Character Columns