MySQL / MariaDB For Developers

Course Description

Whether you`re new to SQL or a more experienced developer, this course is a great start and an eye-opener on how much you`re missing out on in SQL. You will cover, how to create complex inner and outer joins, summary queries, and subqueries. What the different data types are and how to handle the common problems you run into with various types of data. How to use built-in functions or write your own to handle everyday challenges. You will also see the basics of database design and implementation using the MySQL Workbench.
4 Days
Contact us for pricing
 

Prerequisites

No particular programming experience is required, the course is taught from a developer`s perspective but it will also suit those new to relational databases and database technology.

Customisation

The final module of this course will demonstrate how to connect to MySQL from your programming language of choice.

An introduction to relational databases and SQL

An introduction to client/server systems
An introduction to the relational database model
An introduction to SQL and SQL-based systems
SQL statements
How to work with other database objects
How to use SQL from an application program

How to use the MySQL Workbench

The history of MySQL / MariaDB
An introduction to MySQL Workbench
How to create a database installation
How to view and modify databases
How to work with queries

How to retrieve data from a single table

An introduction to the SELECT statement
How to code the SELECT clause
How to code the WHERE clause
How to code the ORDER BY clause

How to retrieve data from two or more tables

How to work with inner joins
How to work with outer joins
Other skills for working with joins
How to work with unions

How to code summary queries

How to work with aggregate functions
How to group and summarize data
How to summarize data using SQL Server extensions

How to code subqueries

An introduction to subqueries
How to code subqueries in search conditions
Other ways to use subqueries
Guidelines for working with complex queries
How to work with common table expressions

How to insert, update, and delete data

How to create test tables
How to insert new rows
How to modify existing rows
How to delete existing rows
How to merge rows

How to work with data types

A review of the SQL data types
How to convert data

How to work with functions

How to work with string data
How to work with numeric data
How to work with date/time data
Other functions you should know about

How to design a database

How to design a data structure
How to normalize a data structure

How to create a database and its tables with MySQL Workbench

How to work with a database
How to work with tables
How to generate scripts

How to create a database and its tables with SQL Statements

MySQL DDL statements
How to create databases, tables, and indexes
How to use constraints
How to change databases and tables
Understanding the scripts generated automatically by MySQL Workbench

How to work with views

An introduction to views
How to create and manage views
How to use views
How to use the View Designer

How to code scripts

MySQL can run a series of commands in sequence - this is known as a script
How to work with variables and temporary tables
How to control the execution of a script
Advanced scripting techniques

How to code stored procedures, functions, and triggers

Procedural programming options in MySQL
How to code stored procedures
How to code user-defined functions
How to code triggers

How to manage transactions and locking

How to work with transactions
An introduction to concurrency and locking
How MySQL manages locking
How to prevent deadlocks

How to manage database security

How to work with logins
How to work with permissions
How to work with roles
How to manage security using the MySQL Workbench

Query Optimisation

Create a database with some large tables
Create some queries
How to examine performance
The EXPLAIN command
Understanding indexes

SQL Injection Attacks

What is an SQL injection attack?
Engineering SQL injection attacks.
Understanding perpared statements.

MySQL integration with programming languages

This final module goes through connecting your programming language of choice to MySQL.
This module will be customised to match the programming stack that the participants will be using.
MySQLSQLMySQL for DevelopersMariaDBMaria DBWorkbenchMySQL Workbench