Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • The MySQL Workshop
  • Toc
  • feedback
The MySQL Workshop

The MySQL Workshop

By : Thomas Pettit , Scott Cosentino
4.7 (12)
close
The MySQL Workshop

The MySQL Workshop

4.7 (12)
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.
Table of Contents (22 chapters)
close
close
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
chevron up
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?

Analyzing query performance with EXPLAIN

EXPLAIN is a very useful tool when it comes to performance. The SQL query is used to tell the database what you want, but EXPLAIN asks the database how it thinks it is going to do it.

Let's use the city table in the world_simple database as an example:

SELECT * FROM city WHERE ID=2460;
EXPLAIN SELECT * FROM city WHERE ID=2460;

This produces the following output:

Figure 5.30 – SELECT and EXPLAIN

Note that it says 1 warning. You can see the actual message by running SHOW WARNINGS;. This is expected for EXPLAIN as there will be a note with a rewritten version of the statement. You can ignore this for now.

We select a single city (Skopje) and are using an ID (2460) to do this lookup.

Let's go over the EXPLAIN output to see what each field means:

Figure 5.31 – Meaning of each field

Now let's add a primary key to this table and run EXPLAIN...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 6
Next Section
bookmark search playlist download
font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete