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

The MySQL Workshop
Contributors
About the authors
About the reviewer
Preface
    Who this book is for
    What this book covers
    To get the most out of this book
    Download the example code files
    Download the color images
    Conventions used
    Get in touch
    Share Your Thoughts
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
    Solution to Activity 1.1
    Solution to Activity 2.1
    Solution to Activity 3.1
    Solution to Activity 3.2
    Solution to Activity 4.1
    Solution to Activity 5.1
    Solution to Activity 5.2
    Solution to Activity 6.1
    Solution to Activity 7.1
    Solution to Activity 8.1
    Solution to Activity 8.2
    Solution to Activity 9.1
    Solution to Activity 10.1
    Solution to Activity 10.2
    Solution to Activity 10.3
    Solution to Activity 10.4
    Solution to Activity 10.5
    Solution to Activity 11.1
    Solution to Activity 12.1
    Solution to Activity 13.1
    Solution to Activity 14.1
    Solution to Activity 15.1
    Solution to Activity 15.2
    Why subscribe?
Other Books You May Enjoy
    Packt is searching for authors like you
    Share Your Thoughts