MySQL
(MYSQL.AJ1) / ISBN : 978-1-64459-480-3This course includes
Lessons
TestPrep
Hands-On Labs
AI Tutor (Add-on)
About This Course
The MySQL course empowers you with essential insights you need to make the most out of MySQL. This course breaks down how databases function and teaches you useful skills such as writing and managing SQL queries, designing databases, and improving their performance. By the end of this course, you'll have the practical expertise to create, manage, and optimize robust databases using MySQL. Whether you're a beginner or have some experience, this course is your gateway to mastering the art of database management.
Get the support you need. Enroll in our Instructor-Led Course.
Lessons
16+ Lessons | 133+ Exercises | 90+ Quizzes | 54+ Flashcards | 54+ Glossary of terms
TestPrep
55+ Pre Assessment Questions | 55+ Post Assessment Questions |
Hands-On Labs
35+ LiveLab | 31+ Video tutorials | 43+ Minutes
1
Preface
- Who this course is for
- What this course covers
- To get the most out of this course
2
Background Concepts
- Introducing databases
- Exploring MySQL
- Exercise 1.01: Organizing data in a relational format
- Exploring MySQL architecture
- Storage engines (InnoDB and MyRocks)
- Data modeling
- Normalization
- Activity 1.01: Creating an optimized table for an employee project
- Summary
3
Creating a Database
- Developing databases
- The MySQL Workbench GUI
- Accessing MySQL through the command-line interface
- Creating a database
- Using Workbench to add a table
- MySQL table indexes and foreign keys
- Reverse engineering a database
- Activity 2.01 – modifying the EER diagram, the model, and the database
- Summary
4
Using SQL to Work with a Database
- An introduction to working with databases using SQL
- Working with data
- Backing up databases
- Restoring databases
- Working with SQL code to maintain a database
- Creating a new database
- Creating and modifying tables
- SQL queries to create indexes and foreign keys
- Activity 3.1 – creating a table with indexes and foreign keys
- Altering table queries
- Adding data to a table
- Updating data in a record
- Deleting data from tables
- Blobs, files, and file paths
- Activity 3.2 – adding image file paths to the database
- Summary
5
Selecting, Aggregating, and Applying Functions
- An introduction to querying data
- Querying tables in MySQL
- Exercise 4.01 – working with simple queries
- Filtering results
- Exercise 4.02 – filtering results
- Using functions on data
- Exercise 4.03 – using functions
- Aggregating data
- Exercise 4.04 – aggregating data
- Case statements
- Exercise 4.05 – writing case statements
- Activity 4.01 – collecting information for a travel article
- Summary
6
Correlating Data across Tables
- Introduction to processing data across tables
- Joining two tables
- Analyzing subqueries
- Common table expressions
- Analyzing query performance with EXPLAIN
- Activity 5.01: The Sakila video store
- Activity 5.02: Generating a list of years
- Summary
7
Stored Procedures and Other Objects
- Introduction to database objects
- Exploring various database objects
- Working with views
- Activity 6.01 – updating the data in a view
- Working with user-defined functions
- Working with stored procedures
- Working with IN, OUT, and INOUT
- Exploring triggers
- Using transactions
- Summary
8
Creating Database Clients in Node.js
- Introduction to database management with Node.js
- Best practices for SQL client development
- JavaScript using Node.js
- Connecting to MySQL
- Activity 7.01 – building a database application with Node.js
- Summary
9
Working with Data Using Node.js
- Interacting with databases
- Inserting records in Node.js
- Updating the records of a table
- Activity 8.01 – multiple updates
- Displaying data in browsers
- ODBC connections
- Activity 8.02 – designing a customer database
- Summary
10
Microsoft Access – Part 1
- Introduction to MS Access
- MS Access database application configurations
- Upsizing an MS Access database to MySQL
- Manually exporting MS Access tables
- Adjusting field properties
- Migrating with wizards
- Linking to your tables and views
- Refreshing linked MySQL tables
- Activity 9.01 – linking the remaining MySQL tables to your MS Access database
- Summary
11
Microsoft Access – Part 2
- Introduction to MS Access
- Migrating an MS Access application to MySQL
- Activity 10.01 – Converting gender and job statistics
- Calling MySQL functions
- Activity 10.02 – Creating a function and calling it
- Calling MySQL stored procedures
- Activity 10.03 – Creating MySQL stored procedures and using them in VBA
- Using parameters
- Activity 10.04 – Parameterized stored procedure (series list)
- Activity 10.05 – Multiple parameters stored procedure (date list)
- The Bad Bits form
- Summary
12
MS Excel VBA and MySQL – Part 1
- Introduction to Excel
- Exploring the ODBC connection
- Exploring the Excel VBA structure
- Learning about VBA libraries
- Connecting to the MySQL database using VBA
- Reading data from MySQL using VBA
- Populating charts
- Activity 11.01 – Creating a chart (artist track sales)
- Summary
13
Working With Microsoft Excel VBA – Part 2
- An introduction to MySQL connections
- Connecting to the MySQL database using ODBC
- Exploring generic data read functions
- Creating connections to MySQL in Excel
- Inserting data using MySQL for Excel
- Updating data using MySQL for Excel
- Pushing data from Excel
- Pivot tables
- Activity 12.01 – building a MySQL-based Excel document
- Summary
14
Getting Data into MySQL
- An introduction to data preparation
- Working with the X DevAPI
- Inserting documents
- Loading data from a SQL file
- Loading data from a CSV file
- Loading data from a JSON file
- Using the CSV storage engine to export data
- Using the CSV storage engine to import data
- Searching and filtering JSON documents
- Using JSON functions and operators to query JSON columns
- Using generated columns to query and index JSON data
- Activity 13.01 – Exporting report data to CSV for Excel
- Summary
15
Manipulating User Permissions
- Introduction to user permissions
- Exploring users and accounts
- Exercise 14.01 – creating users and granting permissions
- Changing users
- Flush privileges
- Changing permissions
- Exercise 14.02 – modifying users and revoking permissions
- Using roles
- Exercise 14.03 – using roles to manage permissions
- Troubleshooting access problems
- Activity 14.01 – creating users for managing the world schema
- Summary
16
Logical Backups
- An introduction to backups
- Understanding the basics of backups
- Logical and physical backup
- Types of restore
- Scheduling backups
- Using point-in-time recovery with binlog files
- Activity 15.01 – backing up and restoring a single schema
- Activity 15.02 – performing a point-in-time restore
- Summary
1
Background Concepts
- Creating a Relational Database
- Creating a New Database Using MS Access
- Normalizing Data from 2NF to 3NF
- Normalizing Data from 1NF to 2NF
2
Creating a Database
- Creating a Connection With the MySQL Workbench GUI
- Creating a Database using MySQL Workbench
- Creating a Table Using MySQL Workbench
- Creating an Index
- Creating a Foreign Key
- Creating an EER Diagram
3
Using SQL to Work with a Database
- Modifying an Existing Table
- Adding a Record
- Updating a Record
- Deleting Data from a Table
4
Selecting, Aggregating, and Applying Functions
- Filtering Results
- Using Functions
- Aggregating Data
- Using CASE Statements
5
Correlating Data across Tables
- Joining Two Tables
- Using Common Table Expressions (CTEs)
- Using the EXPLAIN Command
6
Stored Procedures and Other Objects
- Creating a View
- Creating a Stored Procedure
- Using Triggers
- Implementing a Transaction
7
Creating Database Clients in Node.js
- Testing the Output of a Node.js Script
- Writing to a Disk File
8
Working with Data Using Node.js
- Creating a LAN DSN/ODBC Connection
9
Microsoft Access – Part 1
- Creating an ODBC for a MySQL Database Schema
- Upsizing a Table from Microsoft Access to MySQL
10
Microsoft Access – Part 2
- Calling a MySQL Function
- Creating a Parameterized Stored Procedure
11
MS Excel VBA and MySQL – Part 1
- Activating the Developer tab and the VBA IDE
- Creating a Code Module
13
Getting Data into MySQL
- Importing a SQL File using MySQL Workbench
Related Courses
All Course
PHP and MySQL Web Development
uCertify Pearson-PHP-MYSQL-complete
PHP and MySQL Web Development
ISBN: 978-1-61691-045-7uCertify Pearson-PHP-MYSQL-complete