MySQL Development Training

4.3 out of 5 - Read Testimonials

Course Length: 3 days

Delivery Methods: Multiple delivery options

Course Overview

In this MySQL course, students learn to query a MySQL database using Database Manipulation Language (DML) statements.

Course Benefits

Create views to reuse SELECT statements
Learn data retrieval using SELECT statement.
Troubleshoot typical warnings and errors.
Change or add data.
Understand MySQL data validation.
Delete data from tables.
Generate aggregated query data using various criteria.
Connect data from multiple table rows using various types of JOIN constructs.
Use several different types of sub-queries.
Extensive coverage of MySQL Functions and expressions.
Use expressions in SQL statements for more functional and flexible retrieval.
Learn to export and import data.

Available Delivery Methods

Public Class
Public expert-led online training from the convenience of your home, office or anywhere with an internet connection. Guaranteed to run .

Private Class
Private classes are delivered for groups at your offices or a location of your choice.

Course Outline

An introduction to MySQL
1. An introduction to relational databases
  1. An introduction to client/server systems
    1. The hardware components of a client/server system
    2. The software components of a client/server system
    3. Other client/server architectures
  2. An introduction to the relational database model
    1. How a table is organized
    2. How tables are related
    3. How columns are defined
    4. How to read a database diagram
  3. An introduction to SQL and SQL-based systems
    1. A brief history of SQL
    2. A comparison of Oracle, DB2, Microsoft SQL Server, and MySQL
  4. The SQL statements
    1. An introduction to the SQL statements
    2. How to work with database objects
    3. How to query a single table
    4. How to join data from two or more tables
    5. How to add, update, and delete data in a table
    6. SQL coding guidelines
  5. How to use SQL from an application program
    1. Common options for accessing MySQL data
    2. PHP code that retrieves data from MySQL
    3. Java code that retrieves data from MySQL
2. How to use MySQL Workbench and other development tools
  1. An introduction to MySQL Workbench
    1. The Home page of MySQL Workbench
    2. How to open a database connection
    3. How to start and stop the database server
    4. How to navigate through the database objects
    5. How to view and edit the data for a table
    6. How to view and edit the column definitions for a table
  2. How to use MySQL Workbench to run SQL statements
    1. How to enter and execute a SQL statement
    2. How to use snippets
    3. How to handle syntax errors
    4. How to open and save SQL scripts
    5. How to enter and execute SQL scripts
  3. How to use the MySQL Reference Manual
    1. How to view the manual
    2. How to look up information
  4. How to use the MySQL Command Line Client
    1. How to start and stop the MySQL Command Line Client
    2. How to use the MySQL Command Line Client to work with a database
3. How to retrieve data from a single table
  1. An introduction to the SELECT statement
    1. The basic syntax of the SELECT statement
    2. SELECT statement examples
  2. How to code the SELECT clause
    1. How to code column specifications
    2. How to name the columns in a result set using aliases
    3. How to code arithmetic expressions
    4. How to use the CONCAT function to join strings
    5. How to use functions with strings, dates, and numbers
    6. How to test expressions by coding statements without FROM clauses
    7. How to eliminate duplicate rows
  3. How to code the WHERE clause
    1. How to use the comparison operators
    2. How to use the AND, OR, and NOT logical operators
    3. How to use the IN operator
    4. How to use the BETWEEN operator
    5. How to use the LIKE and REGEXP operators
    6. How to use the IS NULL clause
  4. How to code the ORDER BY clause
    1. How to sort by a column name
    2. How to sort by an alias, expression, or column number
  5. How to code the LIMIT clause
    1. How to limit the number of rows
    2. How to return a range of rows
4. How to retrieve data from two or more tables
  1. How to work with inner joins
    1. How to code an inner join
    2. How to use table aliases
    3. How to join to a table in another database
    4. How to use compound join conditions
    5. How to use a self-join
    6. How to join more than two tables
    7. How to use the implicit inner join syntax
  2. How to work with outer joins
    1. How to code an outer join
    2. Outer join examples
  3. Other skills for working with joins
    1. How to join tables with the USING keyword
    2. How to join tables with the NATURAL keyword
    3. How to use cross joins
  4. How to work with unions
    1. How to code a union
    2. A union that combines result sets from different tables
    3. A union that combines result sets from the same tables
    4. A union that simulates a full outer join
5. How to insert, update, and delete data
  1. How to create test tables
    1. How to create the tables for this book
    2. How to create a copy of a table
  2. How to insert new rows
    1. How to insert a single row
    2. How to insert multiple rows
    3. How to insert default values and null values
    4. How to use a subquery in an INSERT statement
  3. How to update existing rows
    1. How to update rows
    2. How to use a subquery in an UPDATE statement
  4. How to delete existing rows
    1. How to delete rows
    2. How to use a subquery in a DELETE statement
More SQL skills as you need them
1. How to code summary queries
  1. How to work with aggregate functions
    1. How to code aggregate functions
    2. Queries that use aggregate functions
  2. How to group and summarize data
    1. How to code the GROUP BY and HAVING clauses
    2. Queries that use the GROUP BY and HAVING clauses
    3. How the HAVING clause compares to the WHERE clause
    4. How to code compound search conditions
    5. How to use the WITH ROLLUP operator
    6. How to use the GROUPING function
  3. How to code aggregate window functions
    1. How the aggregate window functions work
    2. How to use frames
    3. How to use named windows
2. How to code subqueries
  1. An introduction to subqueries
    1. Where to code subqueries
    2. When to use subqueries
  2. How to code subqueries in the WHERE clause
    1. How to use the IN operator
    2. How to use the comparison operators
  3. How to use the ALL keyword
    1. How to use the ANY and SOME keywords
    2. How to cde correlated subqueries
    3. How to use the EXISTS operator
  4. How to code subqueries in other clauses
    1. How to code subqueries in the HAVING clause
    2. How to code subqueries in the SELECT clause
    3. How to code subqueries in the FROM clause
  5. How to work with complex queries
    1. A complex query that uses subqueries
    2. A procedure for building complex queries
  6. How to work with common table expressions
    1. How to code a CTE
    2. How to code a recursive CTE
3. How to work with data types
  1. The data types
    1. Overview
    2. The character types
    3. The integer types
    4. The fixed-point and floating-point types
    5. The date and time types
    6. The ENUM and SET types
    7. The large object types
  2. How to convert data
    1. How implicit data conversion works
    2. How to convert data using the CAST and CONVERT functions
    3. How to convert data using the FORMAT and CHAR functions
4. How to use functions
  1. How to work with string data
    1. A summary of the string functions
    2. Examples that use string functions
    3. How to sort by a string column that contains numbers
    4. How to parse a string
  2. How to work with numeric data
    1. How to use the numeric functions
    2. How to search for floating-point numbers
  3. How to work with date/time data
    1. How to get the current date and time
    2. How to parse dates and times with date/time functions
    3. How to parse dates and times with the EXTRACT function
    4. How to format dates and times
    5. How to perform calculations on dates and times
    6. How to search for a date
    7. How to search for a time
  4. Other functions you should know about
    1. How to use the CASE function
    2. How to use the IF, IFNULL, and COALESCE functions
    3. How to use the regular expression functions
    4. How to use the ranking functions
    5. How to use the analytic functions
Stored program development
1. Language skills for writing stored programs
  1. An introduction to stored programs
    1. Four types of stored programs
    2. A script that creates and calls a stored procedure
    3. A summary of statements for coding stored programs
  2. How to write procedural code
    1. How to display data
    2. How to declare and set variables
    3. How to code IF statements
    4. How to code CASE statements
    5. How to code loops
    6. How to use a cursor
    7. How to declare a condition handler
    8. How to use a condition handler
    9. How to use multiple condition handlers
2. How to use transactions and locking
  1. How to work with transactions
    1. How to commit and rollback transactions
    2. How to work with save points
  2. How to work with concurrency and locking
    1. How concurrency and locking are related
    2. The four concurrency problems that locks can prevent
    3. How to set the transaction isolation level
    4. How to lock selected rows
    5. How to prevent deadlocks
3. How to create stored procedures and functions
  1. How to code stored procedures
    1. How to create and call a stored procedure
    2. How to code input and output parameters
    3. How to set a default value for a parameter
    4. How to validate parameters and raise errors
    5. A stored procedure that inserts a row
    6. How to work with user variables
    7. How to work with dynamic SQL
    8. How to drop a stored procedure
  2. How to code stored functions
    1. How to create and call a function
    2. How to use function characteristics
    3. A function that calculates balance due
    4. How to drop a function
  3. How to use Workbench with procedures and functions
    1. How to view and edit stored routines
    2. How to create stored routines
    3. How to drop stored routines
4. How to create triggers and events
  1. How to work with triggers
    1. How to create a BEFORE trigger
    2. How to use a trigger to enforce data consistency
    3. How to create an AFTER trigger
    4. How to view or drop triggers
  2. How to work with events
    1. How to turn the event scheduler on or off
    2. How to create an event
    3. How to view, alter, or drop events

Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following would be useful for this MySQL class:

Some knowledge of database concepts.

Follow-on Courses

MySQL Administration Training

MySQL Development Training

Register for a Live Class

Request a Private Class