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