MySQL

You will learn how to

• Configure and optimise MySQL on multiple platforms
• Design, create and maintain MySQL databases
• Construct efficient SQL queries incorporating MySQL extensions
• Identify the correct database table types to use in your application
• Manage MySQL using graphical tools
• Optimise database performance and enhance reliability

Course benefits
MySQL is the leading open source relational database management system and is extensively deployed around the world. It runs on a variety of platforms, including UNIX/Linux, Windows and Macintosh. The combination of its low cost, ease of maintenance and high speed makes MySQL ideal for many application areas.

This course provides a comprehensive introduction to the MySQL RDBMS. You gain the skills needed to administer and maintain MySQL databases while addressing scalability and reliability issues.

Who should attend
This course is valuable for anyone involved in administering a MySQL relational database management system (RDBMS). Basic familiarity with database concepts is helpful, but not essential.

Hands-on training
You gain hands-on experience using a MySQL database management system to organise, manipulate and retrieve data:

• Employing the MySQL command line interface to manipulate data
• Building databases and manipulating data with SQL queries
• Structuring data in Third Normal Form
• Importing and exporting data to and from flat files
• Modifying MySQL configuration with graphical and command line tools
• Removing SQL from external applications with stored procedures

Course content

Designing and Querying Databases Database modelling Defining RDBMS concepts Mapping real-world object to databases and tables Determining appropriate table schemas Normalising database tables for optimal data management Implementing basic SQL queries Retrieving data from tables Adding and removing rows with INSERT and DELETE Modifying existing data with UPDATE Building stored procedures and employing triggers Controlling and Managing the MySQL Server Choosing and installing MySQL Identifying the different licensing models for MySQL Comparing MySQL to other commercial RDBMSs Determining which version of MySQL to download Accessing the database server Creating user accounts Allocating user privileges with GRANT and REVOKE Avoiding denial-of-service (DoS) attacks Directly manipulating the MySQL access privilege tables Leveraging graphical front-ends Managing the MySQL server with MySQL Administrator Querying databases and tables with MySQL Query Builder Managing Databases with MySQL Administering databases Constructing a database using command line tools Identifying different SQL data types Building and altering tables Adding indexes to tables for best performance MySQL table types Building temporary MEMORY tables Achieving the greatest speed with MyISAM tables Avoiding operating system limitations with MERGE tables Importing and exporting data Importing flat-file data with LOAD DATA INFILE Exporting data to text files Saving complete table and data definitions with mysqldump Safeguarding data integrity Backing up databases using MySQL and operating system tools Advanced SQL Techniques Creating advanced queries Manipulating and formatting data Organising output using ORDER BY Employing outer joins Building queries incorporating subqueries Safeguarding data integrity with transactions Taking advantage of SQL functions Manipulating data and time data Aggregating data with GROUP BY Calculating mean, sum, maximum and minimum values from columns Optimising MySQL Configuration Increasing performance Implementing full-text indexing Identifying slow and problematic SQL queries Activating the Query Cache Building reliability and scalability into your applications Replicating databases on multiple servers Identifying different replication configurations Repairing database tables Ensuring 99.999% uptime with MySQL Cluster Implementing stored procedures Creating user variables Building user-defined functions Writing stored procedures Passing data to and from stored procedures