MySQL for Developers I

Prerequisites:

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

Course Description:

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.

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

This course includes the following modules:

Client/Server Concepts

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

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

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

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

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

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

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

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

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

Connectors

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