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.

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

Share Your Thoughts

Section 1: Creating Your Database

Section 1: Creating Your Database

Free Chapter

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)

Activity 1.01: Creating an optimized table for an employee project

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

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

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

Section 2: Managing Your Database

Section 2: Managing Your Database

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

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

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

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

Section 3: Querying Your Database

Section 3: Querying Your Database

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

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

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)

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

Activity 12.01 – building a MySQL-based Excel document

Section 4: Protecting Your Database

Section 4: Protecting Your Database

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

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

Flush privileges

Changing permissions

Exercise 14.02 – modifying users and revoking permissions

Exercise 14.03 – using roles to manage permissions

Troubleshooting access problems

Activity 14.01 – creating users for managing the world schema

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

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

Customer Reviews

5 (1)

5 star

100%

4 star

0

3 star

0

2 star

0

1 star

0

Solution to Activity 8.1

In this activity, you are tasked with adding new details to the existing country table – that is, capital cities, countries' independence statuses, and currency types. Perform the following steps to accomplish this:

Create a new script file named Activity-MultipleUpdates.js.

Add the connection module and instruct the server to use the world_statistics database. Include error handling:

var mysqlconnection = require("./mysqlconnection.js")
mysqlconnection.query("USE world_statistics", function (err, result) {

Deal with the error, should one occur. Each distinct task performed by the code is embedded in the part of the if statement that is executed if there is no error. This means that, for each task to be performed, the preceding task must be error-free. If an error occurs in any given task, the script will report the error and then exit, and none of the following tasks will be attempted:
```
if (err) throw "Instructing...
```