MySQL for Developers Course

Syllabus – Key points

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 options

·      Configuration files

·      Precedence of logging in options

·      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

·      MySQL Utilities

·      Exercises: Using the MySQL client program

Session 3: DATA TYPES

•     Bit data type

•     Numeric data types

•     Auto_increment

•     Character string data types

•     Character sets and collation

•     Binary string data types

•     Enum and Set data types

•     Temporal data types

•     Timezone support

•     Spatial Datatypes

•     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 columns with table and database names

•     Using reserved words as identifiers

•     Function names

•     Exercises: Using identifiers

Session 5: 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

Course Contents – DAY 2

Session 6: 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 7: 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 8: CONNECTORS

•     MySQL client interfaces

•     MySQL connectors

•     Oracle and community conectors

•     Connecting to MySQL server using Java and PHP connectors

•     MySQL and NoSQL

•     Innodb integration with memcached

Session 9: OBTAINING DATABASE METADATA

•     What is metadata?

•     Using mysqlshow utility with MariaDB

•     The show and describe commands

•     Describing tables

•     The information_schema

•     Listing tables

•     Listing columns

•     Listing views

•     Listing key_columns_usage

•     Exercises: Obtaining database metadata

Session 10: DEBUGGING

•     MySQL error messages

•     The show statement

•     Show errors

•     Show count(*) errors

•     Show warnings

•     Show count(*) warnings

•     Note messages

•     The perror utility

•     Exercises: Debugging

Course Contents – DAY 3

Session 11: IMPORT AND EXPORT

•     Exporting using SQL

•     Privileges required to export data

•     Importing using SQL

•     Messages when loading data

•     Privileges required to load data

•     Exporting from the command line

•     Mysqldump main options

•     Importing from the command line

•     Mysqlimport main options

•     Exercises: Importing and exporting

Session 12: SUBQUERIES

•     Types of subquery

•     Multiple-column subqueries

•     Correlated subqueries

•     Using the ANY, ALL and SOME operators

•     Using the EXISTS operator

•     Subqueries as scalar expressions

•     Derived Table

•     WITH Clause ( Common Table Expression – CTE )

•     Recursive CTEs

•     Using subqueries in updates and deletes

•     Exercises: Coding subqueries

Session 13: VIEWS

Session 14: USING WINDOW FUNCTIONS

Session 15: REGULAR EXPRESSION SUPPORT

Course Contents – DAY 4

Session 16: USER VARIABLES AND PREPARED STATEMENTS

Session 17: INTRODUCTION TO STORED ROUTINES

Session 18: STORED ROUTINES – PROGRAM LOGIC

Session 19: PROCEDURES WITH PARAMETERS

Course Contents – DAY 5

Session 20: STORED ROUTINES – EXCEPTION HANDLERS & CURSORS

Session 21: FUNCTIONS

Session 22: TRIGGERS

Session 23: BASIC OPTIMIZATIONS

Session 24: MORE ABOUT INDEX