MySQL Administration and Development

Course Details

• Course Introduction
• Course Objectives
• Course Overview
• Using the Workbook
• Suggested References
• Introduction to Database Concepts and MySQL
• Features of a Relational Database
• Where does SQL Fit in?
• Database Access
• Why MySQL?
• The History of MySQL
• Installation, Configuration, and Upgrading
• MySQL Software
• MySQL Software Features
• Preparing to Install MySQL
• Available Client Software
• After the Download
• Configuring the Server
• Starting the Server
• The Initial User Accounts
• Verifying Server Operation
• Upgrading
• Copying a Database between Architectures
• Environment Variables
• Database Design
• Developing the Design of a Database
• Database Entities
• The Primary Key
• Foreign Key Relationships
• Data Models and Normalization
• Second Normal Form (2NF)
• Third Normal Form (3NF) and Beyond
• Translating a Data Model into a Database Design
• The mysql Command-Line Tool
• Running the mysql Client
• Customizing the mysql Prompt
• mysql Commands
• Using the Help Command
• Some Useful mysql Options
• Working with a Database
• Examining Table Definitions
• Other SHOW Options
• DDL – Data Definition Language
• DDL & DML Overview
• Building Table Definitions
• Identifiers
• Column Definitions
• Numeric Datatypes
• ENUM and SET Types
• Date and Time Datatypes
• AUTO_INREMENT
• UNIQUE Constraints
• Primary Keys
• Modifying Tables
• Foreign Keys
• DML – Data Manipulation Language
• DDL & DML Overview
• Data Values: Numbers
• Data Values: Strings
• Working with NULL Values
• Bulk Loading of Data
• Bulk Data Format
• Working with Special Values in Bulk Data
• Adding New Table Rows with INSERT
• Copying Rows
• UPDATE
• REPLACE
• Removing Table Rows
• Queries – The SELECT Statement
• SELECT Syntax Summary
• Choosing Data Sources and Destinations for SELECT
• Presentation of Table Data with SELECT
• Being Selective about Which Rows are Displayed
• User-Defined Variables
• Expressions and Functions
• Control Flow Operators and Functions
• Function Names
• Comparison Operators and Functions
• String Functions
• Numeric Operators and Functions
• Date and Time Functions
• Building a Result Set from Several Sources
• UNION
• Combining Data from Two Tables
• Using WHERE to Choose Matching Rows
• INNER JOIN
• OUTER JOINs
• Multiple Tables, Fields, Joins, and Ordering
• SELECT * and USING Columns
• Advanced SQL Techniques
• MySQL Pattern Matching
• Multipliers, Anchors, and Grouping
• GROUP BY
• Aggregates
• Subqueries
• Subquery Comparisons and Quantifiers
• Other Subqueries
• Subquery Alternatives and Restrictions
• InnoDB Multi-Table Updates and Deletes
• Building a VIEW
• Updatable VIEWs
• MySQL Storage Engines
• Storage Engine Overview
• Other Storage Engine Types
• The Basics of Commonly Used Storage Engines
• MyISAM Limits and Features
• MyISAM Data File Format
• InnoDB and Hardware Limitations
• InnoDB Shared Tablespace Configuration
• InnoDB Per-Table Tablespaces
• InnoDB Data Management
• MEMORY and FEDERATED
• MERGE and ARCHIVE
• Utilities
• Client Overview
• Specifying Options for Command-Line Clients
• Client Option Files
• Checking Tables with myisamchk and mysqlchk
• Using myisamchk and mysqlchk for Repairs
• mysqlshow and mysqlimport
• The MySQL Workbench – General
• MySQL Workbench - Execution
• MySQL Administration via the Workbench
• Data Modeling with the Workbench
• SQL Development
• Third Party Tools
• Administering a Database and Users
• The Server-Side Programs
• Starting the MySQL Server
• Using SET for Server Options, Table Management
• Server Log Files, mysqladmin
• Backup and Restore
• Miscellaneous Functions
• User Account Management
• Understanding User Privileges
• User Account Rights Management
• User Account Privileges
• Managing Access to the Database Environment
• Database Programmability
• Stored Routines: Basic Concepts
• Routine Creation and Use
• Flow Control Statements
• Writing Blocks of Code, Triggers
• Stored Routines, Triggers, and the Binary Log
• Table HANDLERs
• Prepared Statements
• Optimization and Performance Tuning
• Handware Limitations
• Optimizing the MySQL Server's Interaction with the External World
• Adjusting the MySQL Server Configuration
• Optimizing Your Database, Table Partitioning, Optimizing Queries
• The Use of Indexes to Support Queries,
• Thinkng about JOIN Queries
• Query Sorts, Indexes, and Short-Circuiting
• INSERT, UPDATE, DELETE, and Table Locks
• Some General Optimizations
• Optimizations Specific to MyISAM
• Optimizations Specific to InnoDB
• MySQL Programming Interfaces
• Database Application Architectures
• Connecting MySQL to ODBC
• Connecting MySQL to MS/Office and MS/Access
• Connecting to MySQL from Perl
• Programming Perl to MySQL
• Connecting to MySQL from PHP
• Programming PHP to MySQL