MySQL for Developers I Course Overview

This course is designed for MySQL Developers who have a basic understanding of a MySQL database and SQL commands.  The course provides practical experience in more advanced MySQL commands and SQL statements.

Skills Gained

The delegate will practise:

• Using advanced features of the Mysql Client
• Using advanced data types
• Managing the structure of databases and tables
• Managing and using indexes
• Writing complex SQL query statements
• Using advanced SQL expressions
• Using advanced SQL functions
• Performing advanced Insert, Update, Delete, Replace and Truncate Operations

Who will the Course Benefit?

MySQL Developers who have a basic understanding of a MySQL database and SQL commands, as covered on the Introduction to MySQL course.

Course Objectives

To provide the skills needed to write more advanced queries and database maintenance statements on a MySQL database.

Examinations

Together with the Introduction to MySQL course, the content of this course covers the topics required for the Certified MySQL Developer I examination.

Before taking any exam, ensure you have the recommended experience.  The Sun Microsystems website lists all exam requirements and these are updated regularly.

Exams are not included as part of the course.

Requirements

A working knowledge of MySQL is required.  This can be gained by attendance on the Introduction to MySQL course.

Pre-Requisite Courses

• MySQL for Beginners

Follow-On Courses

• MySQL for Developers II
• MySQL for Database Administrators

---

MySQL for Developers I Training Course

Course Contents - DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: CLIENT/SERVER CONCEPTS

• MySQL client/server architecture
• Server modes
• Using client programs
• Logging in
• Configuration files
• Precedence
• SQL modes
• Exercises:   Using client/server

Session 2: THE MySQL CLIENT PROGRAM

• Using MySQL interactively
• The MySQL prompts
• Client commands and SQL statements
• Statement terminators
• Editing
• Selecting a database
• Help
• Safe updates
• Using script files
• Using a source file
• Redirecting output into a file
• Command line execution
• Mysql output formats
• Overriding the defaults
• HTML and XML output
• Exercises:   Using the MySQL client program

Session 3: DATA TYPES

• Column attributes
• Data type classification
• Bit data type
• Numeric data types
• Integer data types
• Floating point data types
• Decimal data type
• Auto_increment
• Character string data types
• Varchar
• Tinytext, text, mediumtext, longtext
• Character sets and collation
• Binary string data types
• Binary
• Varbinary
• Tinyblob, blob, mediumblob, longblob
• Enum and set data types
• Enum
• Set
• Temporal data types
• Date
• Time
• Datetime
• Timestamp
• Automatic Update Of Timestamp Columns
• Year
• Timezone support
• Handling Missing Or Invalid Data Values
• Strict mode
• Missing values
• Invalid values
• Exercises:   Using data types

Session 4: IDENTIFIERS

• Quotes
• Syntax
• Maximum lengths
• Case sensitivity
• Using qualified names
• Using reserved words as identifiers
• Function names
• Exercises:   Using identifiers

Session 5: DATABASES

• Database properties
• Creating a database
• Selecting a database
• Altering databases
• Dropping databases
• Obtaining database metadata
• The SHOW command
• The INFORMATION_SCHEMA database
• The SHOW CREATE command
• Exercises:   Using databases

---

MySQL for Developers I Training Course

Course Contents - DAY 2

Session 6: TABLES AND INDEXES

• Table properties
• Creating tables
• Create table select
• Create table like
• Temporary tables and memory tables
• Altering tables
• Adding columns
• Changing column widths and types
• Renaming columns
• Dropping columns
• Adding constraints
• Dropping constraints
• Renaming tables
• Change the engine for tables
• Multiple alterations
• Dropping tables
• Emptying tables
• The delete statement
• The truncate statement
• Obtaining table metadata
• Show
• Show create table
• The information_schema
• Index introduction
• Structure of a mysql index
• Creating and dropping indexes
• Creating an index
• Altering a table to add an index
• Specifying index type
• Dropping indexes
• Obtaining Index Metadata
• Exercises:   Creating, altering and dropping tables/indexes

Session 7: QUERYING FOR DATA

• The SQL select statement and MYSQL differences
• Advanced order by
• Order by and collation
• Order by with enums
• Order by with sets
• Ordering with distinct and group by
• Special features of union
• Limit and order by
• Group by
• Group_concat
• Rollup
• Exercises:   Querying for data

Session 8: SQL EXPRESSIONS AND FUNCTIONS

• Components of expressions
• Nulls
• Numeric expressions
• String expressions
• Temporal expressions
• Additional functions
• Comparison functions
• Flow control functions
• Numeric functions
• String functions
• Temporal functions
• Exercises:   Using expressions and functions

Session 9: UPDATING DATA

• Update operations and privileges
• Inserting rows
• Insert using set
• Insert and duplicate values
• Replacing rows
• Updating rows
• Update with order by and limit
• Deleting rows
• The delete and truncate statements
• Exercise:   Inserting, updating, replacing and deleting data

Session 10: CONNECTORS

• MySQL client interfaces
• Connectors
• Connector/j
• Connector/odbc
• Connector/net