Duration 5 days – 35 hrs.
Overview
Welcome to the MySQL Development and Administration Training Course, designed to empower you with the knowledge and skills needed to leverage the world’s leading open-source relational database management system. MySQL is widely deployed globally, running seamlessly on various platforms such as UNIX/Linux, Windows, and MacOS X. Its combination of affordability, ease of maintenance, and high-performance positions MySQL as the preferred choice for diverse application areas.
Objectives
- View a database design structure
- Extract basic database information using the SELECT command
- Delete or modify a database
- Delete or modify table row data
- Grouping query data using aggregation
- Connect data from multiple table rows using JOIN
- Perform nested sub-queries
- Use simple functions (String, Date, Numerical)
- Backup and restore the database
- Understand the MySQL storage engines, transactions and concurrency
- Administer databases with native tools like MySQL Administrator and MySQL Query Browser
- Enhance database performance using stored procedures and triggers
- Understand the features and benefits of MySQL
- Understand the basics of relational databases
- Understand the SQL and MySQL languages
- Understand Data/Column types with regard to efficient database design
Audience
- Database Administrators: Professionals responsible for designing, implementing, and managing MySQL databases.
- Software Developers: Individuals involved in creating applications that interact with MySQL databases.
- System Administrators: IT professionals responsible for the overall maintenance and operation of IT systems, including MySQL databases.
- IT Managers: Those overseeing IT departments, making strategic decisions related to database management.
- Web Developers: Professionals building web applications that utilize MySQL databases.
- Business Analysts: Individuals seeking a deeper understanding of MySQL to analyze and interpret data for business insights.
- IT Consultants: Those offering expertise in MySQL solutions to various organizations.
- Anyone Interested in Database Management: Individuals who want to enhance their skills in MySQL development and administration.
Pre- requisites
- Basic understanding of databases and relational database concepts.
- Familiarity with general IT concepts and terminologies.
- No prior experience with MySQL required, but a willingness to learn and engage with the course content.
Course Content
Introduction to MySQL
- Relational Database Systems
- MySQL Licensing
- Downloading MySQL
- MySQL Command Line Client
- Basic MySQL Commands
- MySQL Query Browser
Database Design Basics
- Tables and Relational Databases
- Primary Keys and Foreign Keys
- Relations
- Creating a Data Model
- Normalization
- Data Types
Data Definition Language
- The Structured Query Language
- Creating, Altering, and Deleting Databases
- Creating Tables
- Creating Columns
- Defining Primary Keys
- Foreign Keys and Column Constraints
The SELECT Statement
- Basic Form
- Displaying Specific Columns
- Using Aliases
- Arithmetic Expressions
- Filtering Data using the WHERE Clause
- Conditional and Logical Expressions
Altering Data
- Inserting Data into Tables (INSERT)
- Replacing Table Rows (REPLACE)
- Updating Table Rows (UPDATE)
- Deleting Table Rows (DELETE)
Working with Multiple Tables
- Queries on Multiple Tables
- Table Aliases
- Joins
- Updating and Deleting Multiple Tables
Advanced Queries
- Retrieving Unique Values (DISTINCT)
- Grouping and Aggregating Data (GROUP BY)
- Filtering Aggregated Results (HAVING)
- Sorting Results (ORDER BY)
- Limiting the Results (LIMIT)
Subqueries, Indexes, and Views
- Using Subquery Operators
- Query Optimization using Indexes
- Creating and Deleting Indexes
- Creating and Editing Views
MySQL Functions
- Character Strings
- Using the Date, Time, DateTime Functions
- Numeric Functions
- Full Text Search Functions
Concurrency and Transactions
- ACID Principles
- Transactions
- Table Locking
MySQL Administration
- Creating Users
- Setting Passwords
- Access Privileges
- Backup and Restore
- Introduction to Migration
- Using the MySQL Administrator
Stored Procedures and Triggers
- Basic Concepts
- Functions and Procedures
- Variables and Parameters
- Control Flow Statements
- Loops
- Error Handling
- Cursors
- Stored Procedure Administration
- Triggers
