The MySQL Workshop: A practical guide to working with data and managing databases with MySQL

Preface
Section 1: Creating Your Database
Chapter 1: Background Concepts
Introducing databases
Database architecture
MS Access as a database
Database management system
RDBMS
Exploring MySQL
Data types
Exercise 1.01: Organizing data in a relational format
Exploring MySQL architecture
The MySQL layers
Storage engines (InnoDB and MyRocks)
ACID compliance
Data modeling
Normalization
Activity 1.01: Creating an optimized table for an employee project
Summary
Chapter 2: Creating a Database
Developing databases
The MySQL Workbench GUI
Connecting the Workbench GUI to MySQL
Exercise 2.01 – creating a connection with the MySQL Workbench GUI
Accessing MySQL through the command-line interface
Creating a database
Exercise 2.02 – creating the autoclub database
Using Workbench to add a table
Importing objects from a SQL script file
Exercise 2.03 – importing tables from an SQL script file
MySQL table indexes and foreign keys
Indexes
Exercise 2.04 – creating an index
Indexes on multiple columns
Foreign keys
Exercise 2.05 – creating a foreign key
Reverse engineering a database
Exercise 2.06 – creating an EER model from the autoclub database
Exercise 2.07 – using the EER diagram and forward engineering to manage the database model
Exercise 2.08 – committing model changes to the production database with Synchronize Model
Activity 2.01 – modifying the EER diagram, the model, and the database
Summary
Chapter 3: Using SQL to Work with a Database
An introduction to working with databases using SQL
Working with data
Types of SQL statements
Backing up databases
Exercise 3.01 – Backing up the autoclub database
Restoring databases
Exercise 3.02 – restoring the autoclub database
Working with SQL code to maintain a database
Creating a new database
Exercise 3.03 – creating a new database
Creating and modifying tables
Exercise 3.04 – creating a new table
SQL queries to create indexes and foreign keys
Exercise 3.05 – creating tables with indexes and foreign keys
Activity 3.1 – creating a table with indexes and foreign keys
Altering table queries
Exercise 3.06 – modifying an existing table
Adding data to a table
Exercise 3.07 – adding a single record to a members table
Updating data in a record
Exercise 3.08 – updating a record
Deleting data from tables
Drop queries
Blobs, files, and file paths
Exercise 3.09 – files and blobs
Files and file paths
Activity 3.2 – adding image file paths to the database
Summary
Chapter 4: 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
Math functions
String functions
Date and time functions
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
Section 2: Managing Your Database
Chapter 5: Correlating Data across Tables
Introduction to processing data across tables
Joining two tables
Accidental cross joins
LEFT JOIN versus INNER JOIN
Exercise 5.01: Joining two tables
Analyzing subqueries
Dependent subqueries
Exercise 5.02: Using a subquery
Common table expressions
Recursive CTE
Exercise 5.03: Using a CTE
Analyzing query performance with EXPLAIN
Exercise 5.04: Using EXPLAIN
Activity 5.01: The Sakila video store
Activity 5.02: Generating a list of years
Summary
Chapter 6: Stored Procedures and Other Objects
Introduction to database objects
Exploring various database objects
Working with views
Exercise 6.01 – creating a mailing list with a view
Updatable views
Activity 6.01 – updating the data in a view
Working with user-defined functions
Exercise 6.02 – creating a function
Working with stored procedures
Exercise 6.03 – creating a stored procedure
Exercise 6.04 – stored procedures and parameters
Working with IN, OUT, and INOUT
Exercise 6.05 – IN and INOUT
Exploring triggers
Advantages of triggers
Disadvantages of triggers
Restrictions with triggers
Exercise 6.06 – triggers to enforce business rules
Using transactions
Exercise 6.07 – implementing a transaction
Summary
Chapter 7: Creating Database Clients in Node.js
Introduction to database management with Node.js
Best practices for SQL client development
Installing a development MySQL server
Creating a development MySQL server
Backing up before making changes
Restoring a database
Recovering from accidental data deletion
Exercise 7.01 – safely deleting records
JavaScript using Node.js
Setting up Node.js
Getting started with Node.js
Basics of Node.js
Exercise 7.02 – basic output in the console
Exercise 7.03 – testing outputs in a browser
Writing outputs to files
Exercise 7.04 – writing to a disk file
Connecting to MySQL
Exercise 7.05 – connecting to the MySQL server
Troubleshooting connection errors
Modularizing the MySQL connection
Exercise 7.06 – modularizing the MySQL connection
Creating databases in Node.js
Exercise 7.07 – creating a new database
Creating tables in Node.js
Exercise 7.08 – creating a table in a database
Activity 7.01 – building a database application with Node.js
Summary
Chapter 8: Working with Data Using Node.js
Interacting with databases
Inserting records in Node.js
Exercise 8.01 – inserting a record into a table
Inserting multiple records
Exercise 8.02 – inserting multiple records into a table
Inserting with multiple fields
Exercise 8.03 – populating records from the existing tables
Updating the records of a table
Exercise 8.04 – updating a single record
Activity 8.01 – multiple updates
Displaying data in browsers
Exercise 8.05 – formatting data to the web browser
ODBC connections
Types of DSNs
Determining whether ODBC drivers have been installed
Local, LAN, and remote ODBC connections
Exercise 8.06 – creating a LAN or remote DSN/ODBC connection to the world_statistics database
Creating file DSN/ODBC connections
Activity 8.02 – designing a customer database
Summary
Section 3: Querying Your Database
Chapter 9: Microsoft Access – Part 1
Introduction to MS Access
MS Access database application configurations
Upsizing an MS Access database to MySQL
Exercise 9.01 – preparing your MySQL database and ODBC
Manually exporting MS Access tables
Exercise 9.02 – manually upsizing a table
Adjusting field properties
Exercise 9.03 – manually migrating tables and adjusting their field properties
Migrating with wizards
Exercise 9.04 – using the Workbench Migration Wizard to upsize the table
Linking to your tables and views
Exercise 9.05 – linking a good MySQL table to Access
Exercise 9.07 – linking a problematic MySQL table to Access
Refreshing linked MySQL tables
Activity 9.01 – linking the remaining MySQL tables to your MS Access database
Summary
Chapter 10: Microsoft Access – Part 2
Introduction to MS Access
Migrating an MS Access application to MySQL
Passthrough queries
Exercise 10.01 – Passthrough (simple SQL conversion)
Activity 10.01 – Converting gender and job statistics
Calling MySQL functions
Exercise 10.02 – Passthrough (calling MySQL functions)
Activity 10.02 – Creating a function and calling it
Calling MySQL stored procedures
Exercise 10.03 – Calling a MySQL stored procedure
Activity 10.03 – Creating MySQL stored procedures and using them in VBA
Using parameters
Parameterized stored procedures
Exercise 10.04 – Parameterized stored procedure (series list)
Activity 10.04 – Parameterized stored procedure (series list)
Exercise 10.05 – Multiple parameters stored procedure (country list)
Activity 10.05 – Multiple parameters stored procedure (date list)
Exercise 10.06 – Multiple parameters stored procedure (crosstab queries)
The Bad Bits form
Exercise 10.07 – Bad Bits demonstration
Unbound forms
Another way to unbind a form from a linked table
Exercise 10.08 – Removing all linked tables
Summary
Chapter 11: MS Excel VBA and MySQL – Part 1
Introduction to Excel
Exercise 11.01 – Setting up a sample MySQL database
Exploring the ODBC connection
The Developer menu
Exercise 11.02 – Activating the Developer tab and the VBA IDE
Exploring the Excel VBA structure
Preparing your Excel project
Exercise 11.03 – Creating a code module
Learning about VBA libraries
Exercise 11.04 – Referencing a library
Exercise 11.05 – Inserting worksheets
Connecting to the MySQL database using VBA
Setting the scene
Exercise 11.06 – The connection variable
Connection functions in VBA
Exercise 11.07 – Creating a connection function
Reading data from MySQL using VBA
Exercise 11.08 – ReadGenreSales
Exercise 11.09 – Genre dropdown
Auto-running functions when opening a workbook
Exercise 11.10 – Auto-running functions when opening a workbook
Populating charts
Populating a chart – Genre sales
Exercise 11.11 – Loading Genre Sales chart data
Running code on changes to a document
Exercise 11.12 – Detecting and working with worksheet changes
Activity 11.01 – Creating a chart (artist track sales)
Summary
Chapter 12: Working With Microsoft Excel VBA – Part 2
An introduction to MySQL connections
Connecting to the MySQL database using ODBC
Exercise 12.01 – creating a DSN connection function
Exploring generic data read functions
Exercise 12.02 – a generic data reader
Creating connections to MySQL in Excel
Exercise 12.03 – creating a connection to MySQL
Inserting data using MySQL for Excel
Exercise 12.04 – inserting the top 25 selling artists
Updating data using MySQL for Excel
Exercise 12.05 – updating MySQL data – employees
Pushing data from Excel
Exercise 12.06 – pushing data from Excel to a new MySQL table
Pivot tables
Exercise 12.07 – album sales
Activity 12.01 – building a MySQL-based Excel document
Summary
Section 4: Protecting Your Database
Chapter 13: Getting Data into MySQL
An introduction to data preparation
Working with the X DevAPI
An example of the X DevAPI
Using MySQL Shell with the X DevAPI
Exercise 13.01 – inserting values with MySQL Shell in JS mode
Inserting documents
Exercise 13.02 – inserting documents into a table
Loading data from a SQL file
Exercise 13.03 – loading data from a SQL file and viewing tables
Exercise 13.04 – importing a SQL file using MySQL Workbench
Loading data from a CSV file
The SELECT…INTO OUTFILE Format
The LOAD DATA INFILE…INTO format
Exercise 13.05 – loading data from a CSV file
Loading data from a JSON file
Exercise 13.06 – loading data from a JSON file
Using the CSV storage engine to export data
Exercise 13.07 – utilizing the CSV storage engine to export data
Using the CSV storage engine to import data
Exercise 13.08 – utilizing the CSV storage engine to import data
Searching and filtering JSON documents
Exercise 13.09 – Searching collections and filtering documents
Using JSON functions and operators to query JSON columns
Exercise 13.10 – querying JSON data with SQL
Using generated columns to query and index JSON data
Activity 13.01 – Exporting report data to CSV for Excel
Summary
Chapter 14: Manipulating User Permissions
Introduction to user permissions
Exploring users and accounts
How to connect to MySQL with a set of credentials
Creating, modifying, and dropping a user
Granting permissions
Inspecting users
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
Chapter 15: Logical Backups
An introduction to backups
Understanding the basics of backups
Logical and physical backup
Types of restore
Performing backups
Using mysqldump
Exercise 15.01 – backup using mysqldump
Using mysqlpump
Exercise 15.02 – backing up using mysqlpump
Scheduling backups
Full restore
Partial restore
Exercise 13.03 – restore a single schema backup
Using point-in-time recovery with binlog files
GTID format
Using mysqlbinlog to inspect binlog contents
Activity 15.01 – backing up and restoring a single schema
Activity 15.02 – performing a point-in-time restore
Summary
Appendix
Other Books You May Enjoy