Book Image

The MySQL Workshop

By : Thomas Pettit, Scott Cosentino
5 (1)
Book Image

The MySQL Workshop

5 (1)
By: Thomas Pettit, Scott Cosentino

Overview of this book

Do you want to learn how to create and maintain databases effectively? Are you looking for simple answers to basic MySQL questions as well as straightforward examples that you can use at work? If so, this workshop is the right choice for you. Designed to build your confidence through hands-on practice, this book uses a simple approach that focuses on the practical, so you can get straight down to business without having to wade through pages and pages of dull, dry theory. As you work through bite-sized exercises and activities, you'll learn how to use different MySQL tools to create a database and manage the data within it. You'll see how to transfer data between a MySQL database and other sources, and use real-world datasets to gain valuable experience of manipulating and gaining insights from data. As you progress, you'll discover how to protect your database by managing user permissions and performing logical backups and restores. If you've already tried to teach yourself SQL, but haven't been able to make the leap from understanding simple queries to working on live projects with a real database management system, The MySQL Workshop will get you on the right track. By the end of this MySQL book, you'll have the knowledge, skills, and confidence to advance your career and tackle your own ambitious projects with MySQL.

Related Content you might be interested in

Current Title:

Small book image
The MySQL Workshop
Thomas Pettit | Scott Cosentino
Apr, 2022 | 726 pages
Small book image
The SQL Workshop
Frank Solomon | Awni Al Saqqa | Prashanth Jayaram
Dec, 2019 | 288 pages
Small book image
Advanced MySQL 8
Eric Vanier | Tejaswi Malepati | Birju Shah
Jan, 2019 | 286 pages
Small book image
Learn SQL Database Programming
Josephine Bush
May, 2020 | 564 pages
Table of Contents (22 chapters)
Preface
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
1
Section 1: Creating Your Database
Section 1: Creating Your Database
Free Chapter
2
Chapter 1: Background Concepts
Chapter 1: 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
Chapter 2: Creating a Database
Chapter 2: 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
Chapter 3: Using SQL to Work with a Database
Chapter 3: 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
Chapter 4: Selecting, Aggregating, and Applying Functions
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
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
Section 2: Managing Your Database
Section 2: Managing Your Database
7
Chapter 5: Correlating Data across Tables
Chapter 5: 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
8
Chapter 6: Stored Procedures and Other Objects
Chapter 6: 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
9
Chapter 7: Creating Database Clients in Node.js
Chapter 7: 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
10
Chapter 8: Working with Data Using Node.js
Chapter 8: 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
11
Section 3: Querying Your Database
Section 3: Querying Your Database
12
Chapter 9: Microsoft Access – Part 1
Chapter 9: 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
13
Chapter 10: Microsoft Access – Part 2
Chapter 10: 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
14
Chapter 11: MS Excel VBA and MySQL – Part 1
Chapter 11: 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
15
Chapter 12: Working With Microsoft Excel VBA – Part 2
Chapter 12: 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
16
Section 4: Protecting Your Database
Section 4: Protecting Your Database
17
Chapter 13: Getting Data into MySQL
Chapter 13: 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
18
Chapter 14: Manipulating User Permissions
Chapter 14: 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
19
Chapter 15: Logical Backups
Chapter 15: 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
20
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
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?

Using JSON functions and operators to query JSON columns

For this, we use the MySQL client. You can also use MySQL Shell in SQL mode; just issue \sql after connecting with MySQL Shell.

There are many convenient functions to deal with JSON data when you are working with collections in SQL mode or tables that use JSON fields.

The first thing to do is extract and unquote fields. This is something we did in the previous chapter, so here is a quick reminder of it. The functions are JSON_EXTRACT() and JSON_UNQUOTE(). However, it is more convenient to use the operators that were created to do this – -> to extract and ->> to extract and unquote. You have to specify a JSON path expression to the extract function, which in its most basic form looks like $.name, to extract the name field.

Consider the following example:

SELECT doc->>'$.name' FROM worldcol LIMIT 5;

This produces the following output:

Figure 13.38 – The...

Previous Section
End of Section 11
Next Section