S7

MySQL for Developers I Course Overview

This MySQL Developer training course is designed for MySQL Developers who have a good understanding of a MySQL database and experience using 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 MySQL for Developers I course covers the topics required for the MySQL 5 Developer Certified Professional Exam, Part I (1Z0-871).

Before taking any exam, ensure you have the recommended experience.  The Oracle 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

• Introduction to MySQL

Follow-On Courses

• MySQL for Developers II
• MySQL Database Administration
• PHP Programming & MySQL for Web Development
• Perl Programming

NOTE: Course technical content is subject to change without notice.

---

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
• 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

• Bit data type
• Numeric data types
• Using Auto_increment
• Character string data types
• Binary string data types
• Enum and Set data typesTemporal data types
• Timezone support
• Handling Missing Or Invalid Data Values
• SQL_MODE options
• Exercises:   Using data types

Session 4: IDENTIFIERS

• Using Quotes with Identifier naming
• Case sensitivity in Identifier naming
• Qualifying column with table and database 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 using Select or 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
• Changing the table storage engine
• Multiple alterations
• Droping tables
• Emptying tables
• Obtaining table metadata
• 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 enum datatype
• Order by with set datatype
• Ordering with distinct and group by
• Special features of union
• Limit and order by clauses
• Group By clause
• Group_concat
• Using Rollup in a Group By clause
• Exercises:   Querying for data

Session 8: SQL EXPRESSIONS AND FUNCTIONS

• Components of expressions
• Nulls
• Numeric expressions
• String expressions
• Temporal expressions
• 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 a set clause
• Inserting duplicate values
• Replacing rows
• Updating rows
• Update using the order by and limit clauses
• Deleting rows
• The delete and truncate statements
• Exercise:   Inserting, updating, replacing and deleting data

Session 10: CONNECTORS

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