MySQL for Developers I — A 2-Day Course

 Open Public Course Closed In-House Course

Course Synopsis

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.

Course Objectives

By the end of the course, delegates will be able to:

• Use advanced features of the MySQL Client
• Use advanced data types
• Manage the structure of databases and tables
• Manage and use indexes
• Write complex SQL query statements
• Use advanced SQL expressions and functions
• Perform advanced Insert, Update, Delete, Replace and Truncate Operations

Suitable For

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

Course Prerequisities

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

Publicly scheduled dates, locations, and prices

Contents

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

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

Identifiers

• Quotes
• Syntax
• Maximum lengths
• Case sensitivity
• Using qualified 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 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

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

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

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

Connectors

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