MariaDB for Developers — A 5 day course

Synopsis

This MariaDB Developers training course is designed for MariaDB Developers who have a good understanding of a MariaDB (or MySQL) database and experience of using SQL commands. The course provides further practical experience in more advanced MariaDB commands and SQL statements including Stored Routines and Triggers.

Objectives

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

The delegates will practise:

Using advanced features of the MySQL Client with MariaDB
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
Using user variable syntax and properties
Importing and exporting data from within MariaDB
Importing and exporting data from the command line
Performing complex joins to access multiple tables
Performing complex subqueries
Creating, managing and using views
Using MariaDB Connectors
MariaDB and NoSQL integration
Using prepared statements
Creating and using stored routines
Creating and using triggers
Obtaining database metadata
Optimizing queries
Working with the main storage engines
Debugging MariaDB applications

Client/Server Concepts

MariaDB client/server architecture
Server modes
Using client programs
Logging in options
Configuration files
Precedence of logging in options
Exercises: Using client/server

The MySQL Client Program for MariaDB

Using MariaDB interactively
The MariaDB 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
MariaDB output formats
Overriding the defaults
Html and xml output
MySQL Utilities
Exercises: Using the MySQL client program to connect to MariaDB

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

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

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 & 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
Change the table storage engine
Multiple alterations
Dropping tables
Emptying tables
Obtaining table metadata
Show create table
The information_schema
Index introduction
Structure of a MariaDB 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 MAriaDB 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 & 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

MariaDB connectors
Connecting to MariaDB server using Java and PHP connectors
MariaDB and NoSQL

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

Debugging

MariaDB error messages
The show statement
Show errors
Show count(*) errors
Show warnings
Show count(*) warnings
Note messages
The perror utility
Exercises: Debugging

Joins

Overview of inner joins
Cartesian product
Inner joins with original syntax
Non equi-join
Using table aliases to avoid name clashes
Inner Joins With ISO/ANSI Syntax
Outer Joins
Left outer joins
Right outer joins
Full outer joins
Updating multiple tables simultaneously
Updating rows in one table based on a condition in another
Updating rows in one table reading data from another
Deleting from multiple tables simultaneously
Deleting rows in one table based on a condition in another
Exercises: Coding joins

Subqueries

Types of subquery
Multiple-column subqueries
Correlated subqueries
Using the ANY, ALL and SOME operators
Using the EXISTS operator
Subqueries as scalar expressions
Inline views
Converting subqueries to joins
Using subqueries in updates and deletes
Exercises: Coding subqueriess

Views

Why views are used
Creating views
View creation restrictions
View algorithms
Updateable views
Altering and dropping views
Displaying information about views
Privileges for views
Exercises: Using views

Import & 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

User Variables & Prepared Statements

Creating User variables
User variables in a select
Prepared statements
The prepare statement
The execute statement
The deallocate statement
Using prepared statements in code, with connectors
Exercises: Using variables and prepared statements

Introduction to Stored Routines

Types of stored routines
Benefits of stored routines
Stored routine features
Differences between procedures and functions
Introduction to the Block
Declaring variables and constants
Assigning values to variables
Definer rights and invoker rights
Using SELECT in stored routines
Altering and dropping stored routines
Obtaining stored routine metadata
Stored routine privileges and execution security
Exercises: Writing simple stored routines

Stored Routines - Program Logic

The IF .. THEN .. ELSEIF construct
The CASE statement
The basic loop
The while loop
The repeat loop
The iterate statement
Nested loops
Exercises: Writing stored routines with program logic

Stored Routines - Exception Handlers & Cursors

Dealing with errors using Exception handlers
Cursors
What is a cursor?
Cursor operations
Declaring cursors
Opening and closing cursors
Fetching rows
Status checking
Exercises: Writing stored routines with program logic

Procedures with Parameters

Creating procedures with parameters
Calling Procedures With Parameters
Exercises: Writing stored routines with parameters

Functions

What is a function?
The create function statement
Executing functions
Executing functions from code
Executing functions from SQL statements
The deterministic and SQL clauses
Exercises: Writing functions

Triggers

Trigger creation
Restrictions on triggers
The create trigger statement
Using the old and new qualifiers
Managing triggers
Destroying triggers
Required privileges
Exercises: Writing triggers

Basic Optimizations

Normalisation of data to third normal form
Using indexes for optimization
General query enhancement
Using Explain to analyze queries
Choosing an INNODB or MYISAM storage engine
Using MariaDB Enterprise Monitor in query optimization
Exercises: Making use of basic optimizations

More About Indexes

Indexes and joins
Exercises: Investigating indexes and joins

Publicly scheduled dates, locations, and prices

A schedule of dates for this subject is not currently available. Please call 0333 210 0140 or use our contact form to enquire about places and availability.

Suitable For