MySQL: Advanced Routines, Optimization, and DCL

Personnel at all levels of an enterprise seeking to attain competency in MySQL

Prerequisite
None

Expected Duration
144 minutes

Description
Writing advanced routines can be a big part of being a database developer as well as optimizing the database to ensure queries are running efficiently for end users. In this course, you’ll learn how to create and use advanced procedures, functions, and triggers and how to optimize queries and performance of a MySQL database. This course also covers how to grant, revoke, and deny user permissions for the underlying database objects.

Objective

Advanced Stored Routines and Triggers

  • start the course
  • recognize the difference between stored procedures and stored functions
  • create and use stored functions
  • recognize the functionality provided by input and output parameters in stored procedures
  • create stored procedures with input and output parameters
  • recognize the stored procedure recursion feature
  • identify when to use before and after triggers
  • use triggers to automatically respond to data modifications

Practice: Writing Applications

  • write basic applications for use with MySQL

Query and Performance Optimization

  • identify MySQL queries that require optimization
  • identify the index types in MySQL and understand their use
  • identify if an index is useful for improving query performance
  • use EXPLAIN to analyze query execution
  • use explicit statements to direct MySQL to use specific join types or indexes
  • examine various tools used to optimize query execution

Use GRANT and REVOKE Statements

  • describe the DCL statements used to assign user permissions to database objects
  • grant user access to any database, at any level
  • remove previously assigned permissions

Practice: Performing Optimization and Using DCL

  • optimize queries and the database and use DCL statements to administer permissions

MONTHLY SUBSCRIPTION

$129/month
 

ANNUAL SUBSCRIPTION

$1295/year

Multi-license discounts available for Annual and Monthly subscriptions.