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?

Exercise 1.01: Organizing data in a relational format

Suppose you are working for a company, ABC Corp. Your manager would like to develop a database that stores clients' contact information, as well as the orders a client has made. You have been asked to determine how to organize the data in a relational format. In addition, the company would like you to define the data types that are appropriate for each field. The following is a list of properties that are to be stored in the relational model:

  • Customer Data:
    • Customer ID
    • Customer Name
    • Customer Address
    • Customer Phone Number
  • Order Data:
    • Customer ID
    • Order ID
    • Order Price

Perform the following steps to create a relational database structure:

  1. First, determine the data types that are appropriate for the data. The ID fields should be int data type, since IDs are typically numeric. For fields containing names, addresses, and phone numbers, a varchar data type is appropriate since it can store general text. Finally, a price can be defined as double, since it needs to be able to store decimal values.
  2. Determine how many tables you should have. In this case, you have two sets of data, which means you should have two tables – CustomerData and OrderData.
  3. Consider how tables are related to each other. Since a customer can have an order in the order data, you can conclude that customers and orders are related to one another.
  4. Next, look at what columns are the same between the two sets of data. In this case, both tables contain the CustomerID column.

Finally, combine all the information. You have two tables, CustomerData and OrderData. You can relate them by using the column they share, which is CustomerID. The relational model would look like the following:

Figure 1.6 – The data for customers and orders organized in a relational format

Figure 1.6 – The data for customers and orders organized in a relational format

With this, you now have a fully defined relational structure for your data. This structure with data types can be used to construct a proper relational database.

Now, you will delve into the architecture of MySQL in the following section.

Previous Section
End of Section 4
Next Section