book

The MySQL Workshop

Name: The MySQL Workshop
ISBN: 9781839214905

by Thomas Pettit, Scott Cosentino, Dr. Vlad Sebastian Ionescu

April 2022

Beginner

726 pages

14h 27m

English

Packt Publishing

Read now

Unlock full access

The MySQL Workshop
ContributorsAbout the authorsAbout the reviewer
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesDownload the color imagesConventions usedGet in touchShare Your Thoughts
Section 1: Creating Your Database
Chapter 1: Background Concepts
Introducing databasesDatabase architectureMS Access as a databaseDatabase management system RDBMSExploring MySQLData typesExercise 1.01: Organizing data in a relational formatExploring MySQL architectureThe MySQL layersStorage engines (InnoDB and MyRocks)ACID complianceData modelingNormalizationActivity 1.01: Creating an optimized table for an employee projectSummary
Chapter 2: Creating a Database
Developing databasesThe MySQL Workbench GUIConnecting the Workbench GUI to MySQLExercise 2.01 – creating a connection with the MySQL Workbench GUIAccessing MySQL through the command-line interfaceCreating a databaseExercise 2.02 – creating the autoclub databaseUsing Workbench to add a tableImporting objects from a SQL script fileExercise 2.03 – importing tables from an SQL script fileMySQL table indexes and foreign keysIndexesExercise 2.04 – creating an indexIndexes on multiple columnsForeign keysExercise 2.05 – creating a foreign keyReverse engineering a databaseExercise 2.06 – creating an EER model from the autoclub databaseExercise 2.07 – using the EER diagram and forward engineering to manage the database modelExercise 2.08 – committing model changes to the production database with Synchronize ModelActivity 2.01 – modifying the EER diagram, the model, and the databaseSummary
Chapter 3: Using SQL to Work with a Database
An introduction to working with databases using SQLWorking with dataTypes of SQL statementsBacking up databasesExercise 3.01 – Backing up the autoclub databaseRestoring databasesExercise 3.02 – restoring the autoclub databaseWorking with SQL code to maintain a databaseCreating a new database Exercise 3.03 – creating a new database Creating and modifying tables Exercise 3.04 – creating a new tableSQL queries to create indexes and foreign keysExercise 3.05 – creating tables with indexes and foreign keysActivity 3.1 – creating a table with indexes and foreign keysAltering table queriesExercise 3.06 – modifying an existing tableAdding data to a tableExercise 3.07 – adding a single record to a members tableUpdating data in a recordExercise 3.08 – updating a recordDeleting data from tablesDrop queriesBlobs, files, and file pathsExercise 3.09 – files and blobs Files and file pathsActivity 3.2 – adding image file paths to the databaseSummary
Chapter 4: Selecting, Aggregating, and Applying Functions
An introduction to querying dataQuerying tables in MySQLExercise 4.01 – working with simple queriesFiltering resultsExercise 4.02 – filtering resultsUsing functions on dataMath functionsString functionsDate and time functionsExercise 4.03 – using functionsAggregating dataExercise 4.04 – aggregating dataCase statementsExercise 4.05 – writing case statementsActivity 4.01 – collecting information for a travel articleSummary
Section 2: Managing Your Database
Chapter 5: Correlating Data across Tables
Introduction to processing data across tablesJoining two tablesAccidental cross joinsLEFT JOIN versus INNER JOINExercise 5.01: Joining two tablesAnalyzing subqueriesDependent subqueriesExercise 5.02: Using a subqueryCommon table expressionsRecursive CTEExercise 5.03: Using a CTEAnalyzing query performance with EXPLAINExercise 5.04: Using EXPLAINActivity 5.01: The Sakila video storeActivity 5.02: Generating a list of yearsSummary
Chapter 6: Stored Procedures and Other Objects
Introduction to database objectsExploring various database objectsWorking with viewsExercise 6.01 – creating a mailing list with a viewUpdatable viewsActivity 6.01 – updating the data in a view Working with user-defined functionsExercise 6.02 – creating a functionWorking with stored proceduresExercise 6.03 – creating a stored procedureExercise 6.04 – stored procedures and parametersWorking with IN, OUT, and INOUTExercise 6.05 – IN and INOUTExploring triggersAdvantages of triggersDisadvantages of triggersRestrictions with triggersExercise 6.06 – triggers to enforce business rulesUsing transactionsExercise 6.07 – implementing a transactionSummary

Chapter 7: Creating Database Clients in Node.js
Introduction to database management with Node.jsBest practices for SQL client developmentInstalling a development MySQL serverCreating a development MySQL serverBacking up before making changesRestoring a databaseRecovering from accidental data deletionExercise 7.01 – safely deleting recordsJavaScript using Node.jsSetting up Node.jsGetting started with Node.jsBasics of Node.js Exercise 7.02 – basic output in the consoleExercise 7.03 – testing outputs in a browserWriting outputs to filesExercise 7.04 – writing to a disk fileConnecting to MySQLExercise 7.05 – connecting to the MySQL serverTroubleshooting connection errorsModularizing the MySQL connectionExercise 7.06 – modularizing the MySQL connectionCreating databases in Node.jsExercise 7.07 – creating a new databaseCreating tables in Node.jsExercise 7.08 – creating a table in a databaseActivity 7.01 – building a database application with Node.jsSummary
Chapter 8: Working with Data Using Node.js
Interacting with databasesInserting records in Node.jsExercise 8.01 – inserting a record into a tableInserting multiple recordsExercise 8.02 – inserting multiple records into a tableInserting with multiple fieldsExercise 8.03 – populating records from the existing tablesUpdating the records of a tableExercise 8.04 – updating a single recordActivity 8.01 – multiple updatesDisplaying data in browsersExercise 8.05 – formatting data to the web browserODBC connectionsTypes of DSNsDetermining whether ODBC drivers have been installedLocal, LAN, and remote ODBC connectionsExercise 8.06 – creating a LAN or remote DSN/ODBC connection to the world_statistics databaseCreating file DSN/ODBC connectionsActivity 8.02 – designing a customer databaseSummary
Section 3: Querying Your Database
Chapter 9: Microsoft Access – Part 1
Introduction to MS AccessMS Access database application configurationsUpsizing an MS Access database to MySQLExercise 9.01 – preparing your MySQL database and ODBCManually exporting MS Access tablesExercise 9.02 – manually upsizing a tableAdjusting field propertiesExercise 9.03 – manually migrating tables and adjusting their field propertiesMigrating with wizardsExercise 9.04 – using the Workbench Migration Wizard to upsize the tableLinking to your tables and viewsExercise 9.05 – linking a good MySQL table to Access Exercise 9.07 – linking a problematic MySQL table to AccessRefreshing linked MySQL tablesActivity 9.01 – linking the remaining MySQL tables to your MS Access databaseSummary
Chapter 10: Microsoft Access – Part 2
Introduction to MS AccessMigrating an MS Access application to MySQLPassthrough queriesExercise 10.01 – Passthrough (simple SQL conversion)Activity 10.01 – Converting gender and job statisticsCalling MySQL functionsExercise 10.02 – Passthrough (calling MySQL functions)Activity 10.02 – Creating a function and calling itCalling MySQL stored proceduresExercise 10.03 – Calling a MySQL stored procedureActivity 10.03 – Creating MySQL stored procedures and using them in VBAUsing parametersParameterized stored proceduresExercise 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 formExercise 10.07 – Bad Bits demonstrationUnbound formsAnother way to unbind a form from a linked tableExercise 10.08 – Removing all linked tablesSummary
Chapter 11: MS Excel VBA and MySQL – Part 1
Introduction to ExcelExercise 11.01 – Setting up a sample MySQL databaseExploring the ODBC connectionThe Developer menuExercise 11.02 – Activating the Developer tab and the VBA IDEExploring the Excel VBA structurePreparing your Excel projectExercise 11.03 – Creating a code moduleLearning about VBA librariesExercise 11.04 – Referencing a libraryExercise 11.05 – Inserting worksheetsConnecting to the MySQL database using VBASetting the sceneExercise 11.06 – The connection variableConnection functions in VBAExercise 11.07 – Creating a connection functionReading data from MySQL using VBAExercise 11.08 – ReadGenreSalesExercise 11.09 – Genre dropdownAuto-running functions when opening a workbookExercise 11.10 – Auto-running functions when opening a workbookPopulating chartsPopulating a chart – Genre salesExercise 11.11 – Loading Genre Sales chart dataRunning code on changes to a documentExercise 11.12 – Detecting and working with worksheet changesActivity 11.01 – Creating a chart (artist track sales)Summary
Chapter 12: Working With Microsoft Excel VBA – Part 2
An introduction to MySQL connectionsConnecting to the MySQL database using ODBCExercise 12.01 – creating a DSN connection functionExploring generic data read functionsExercise 12.02 – a generic data readerCreating connections to MySQL in ExcelExercise 12.03 – creating a connection to MySQLInserting data using MySQL for ExcelExercise 12.04 – inserting the top 25 selling artistsUpdating data using MySQL for ExcelExercise 12.05 – updating MySQL data – employeesPushing data from ExcelExercise 12.06 – pushing data from Excel to a new MySQL tablePivot tablesExercise 12.07 – album salesActivity 12.01 – building a MySQL-based Excel documentSummary
Section 4: Protecting Your Database
Chapter 13: Getting Data into MySQL
An introduction to data preparationWorking with the X DevAPIAn example of the X DevAPIUsing MySQL Shell with the X DevAPIExercise 13.01 – inserting values with MySQL Shell in JS modeInserting documentsExercise 13.02 – inserting documents into a tableLoading data from a SQL fileExercise 13.03 – loading data from a SQL file and viewing tablesExercise 13.04 – importing a SQL file using MySQL WorkbenchLoading data from a CSV fileThe SELECT…INTO OUTFILE FormatThe LOAD DATA INFILE…INTO formatExercise 13.05 – loading data from a CSV fileLoading data from a JSON fileExercise 13.06 – loading data from a JSON fileUsing the CSV storage engine to export dataExercise 13.07 – utilizing the CSV storage engine to export dataUsing the CSV storage engine to import dataExercise 13.08 – utilizing the CSV storage engine to import dataSearching and filtering JSON documentsExercise 13.09 – Searching collections and filtering documentsUsing JSON functions and operators to query JSON columnsExercise 13.10 – querying JSON data with SQLUsing generated columns to query and index JSON dataActivity 13.01 – Exporting report data to CSV for ExcelSummary
Chapter 14: Manipulating User Permissions
Introduction to user permissionsExploring users and accountsHow to connect to MySQL with a set of credentialsCreating, modifying, and dropping a userGranting permissionsInspecting usersExercise 14.01 – creating users and granting permissionsChanging usersFlush privilegesChanging permissionsExercise 14.02 – modifying users and revoking permissionsUsing rolesExercise 14.03 – using roles to manage permissionsTroubleshooting access problemsActivity 14.01 – creating users for managing the world schemaSummary
Chapter 15: Logical Backups
An introduction to backupsUnderstanding the basics of backups Logical and physical backupTypes of restorePerforming backups Using mysqldumpExercise 15.01 – backup using mysqldumpUsing mysqlpumpExercise 15.02 – backing up using mysqlpumpScheduling backupsFull restorePartial restoreExercise 13.03 – restore a single schema backupUsing point-in-time recovery with binlog filesGTID formatUsing mysqlbinlog to inspect binlog contentsActivity 15.01 – backing up and restoring a single schemaActivity 15.02 – performing a point-in-time restoreSummary
Appendix
Solution to Activity 1.1Solution to Activity 2.1Solution to Activity 3.1Solution to Activity 3.2Solution to Activity 4.1Solution to Activity 5.1Solution to Activity 5.2Solution to Activity 6.1Solution to Activity 7.1Solution to Activity 8.1Solution to Activity 8.2Solution to Activity 9.1Solution to Activity 10.1Solution to Activity 10.2Solution to Activity 10.3Solution to Activity 10.4Solution to Activity 10.5Solution to Activity 11.1Solution to Activity 12.1Solution to Activity 13.1Solution to Activity 14.1Solution to Activity 15.1Solution to Activity 15.2
Why subscribe?
Other Books You May EnjoyPackt is searching for authors like youShare Your Thoughts

Content preview from The MySQL Workshop

Chapter 6: Stored Procedures and Other Objects

In this chapter, we will continue exploring SQL coding and working with our database. We will create objects that can be reused and flexible enough to accept parameters. By doing this, you will learn how to create views, functions, stored procedures, and transactions to allow users to interact with a MySQL database easily.

This chapter covers the following topics:

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

Introduction to database objects

In the previous chapter, you learned ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Database-Driven Web Development: Learn to Operate at a Professional Level with PERL and MySQL

Publisher Resources

ISBN: 9781839214905

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

The MySQL Workshop

by Thomas Pettit, Scott Cosentino, Dr. Vlad Sebastian Ionescu

Chapter 6: Stored Procedures and Other Objects

Introduction to database objects

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.