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

Cover
Title page
Copyright and credits
Contributors
About the reviewer
Table of Contents
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
Index