MySQL Crash Course: A Hands-on Introduction to Database Development

PRAISE FOR MySQL Crash Course

A fantastic resource for anyone who wants to learn about MySQL . . . and an excellent refresher for more seasoned developers.

—Scott Stroz, MySQL Developer Advocate

Understand not just the ‘what,’ but the ‘why’ behind MySQL development.

—Steven Sian, web and mobile application developer

MySQL Crash Course

A Hands-on Introduction to Database Development

by Rick Silva

MYSQL CRASH COURSE. Copyright © 2023 by Rick Silva.

All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher.

First printing

ISBN-13: 978-1-7185-0300-7 (print)

ISBN-13: 978-1-7185-0301-4 (ebook)

Publisher: William Pollock

Managing Editor: Jill Franklin

Production Manager: Sabrina Plomitallo-González

Production Editor: Jennifer Kepler

Developmental Editors: Rachel Monaghan, Eva Morrow, and Frances Saux

Cover Illustrator: Gina Redman

Interior Design: Octopod Studios

Technical Reviewer: Frédéric Descamps

Copyeditor: Rachel Monaghan

Compositor: Jeff Lytle, Happenstance Type-O-Rama

Proofreader: Scout Festa

For information on distribution, bulk sales, corporate sales, or translations, please contact No Starch Press, Inc. directly at [email protected] or:

No Starch Press, Inc.

245 8th Street, San Francisco, CA 94103

phone: 1.415.863.9900

www.nostarch.com

Library of Congress Cataloging-in-Publication Data

Names: Silva, Rick, author.

Title: MySQL crash course : a hands-on introduction to database development / Rick Silva.

Description: San Francisco, CA : No Starch Press, Inc., [2023] | Includes index.

Identifiers: LCCN 2022050277 (print) | LCCN 2022050278 (ebook) | ISBN 9781718503007 (print) | ISBN

   9781718503014 (ebook)

Subjects: LCSH: SQL (Computer program language) | MySQL (Electronic resource) | Computer

   programming.

Classification: LCC QA76.73.S67 S557 2023 (print) | LCC QA76.73.S67 (ebook) | DDC 005.75/6--dc23/

   eng/20221128

LC record available at https://lccn.loc.gov/2022050277

LC ebook record available at https://lccn.loc.gov/2022050278

No Starch Press and the No Starch Press logo are registered trademarks of No Starch Press, Inc. Other product and company names mentioned herein may be the trademarks of their respective owners. Rather than use a trademark symbol with every occurrence of a trademarked name, we are using the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.

The information in this book is distributed on an As Is basis, without warranty. While every precaution has been taken in the preparation of this work, neither the author nor No Starch Press, Inc. shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in it.

To my wife, Patti, for her patience, love, and support. You are Mother Teresa in a scarf.

About the Author

Rick Silva is a software developer with decades of database experience. Silva has worked at Harvard Business School, Zipcar, and various financial services companies. A Boston native and a Boston College alum, he now lives in the Raleigh, North Carolina, area with his wife, Patti, and his dog, Dixie. When he’s not joining database tables, he’s playing banjo at a local bluegrass jam.

About the Technical Reviewer

Frédéric Descamps (@lefred) has been consulting on open source and MySQL for more than 20 years. After graduating with a degree in management information technology, he started his career as a developer for an ERP system under HP-UX. He then opted for a career in the world of open source by joining one of the first Belgian startups dedicated 100 percent to free projects around GNU/Linux. In 2011 Frédéric joined Percona, one of the leading MySQL-based specialists. He joined the MySQL Community Team in 2016 as a MySQL Community Manager for EMEA and APAC. Descamps is a regular speaker at open source conferences and a technical reviewer for several books. His blog, mostly dedicated to MySQL, is at https://lefred.be.

Descamps is also the devoted father of three adorable daughters: Wilhelmine, Héloïse, and Barbara.

Acknowledgments

This book wouldn’t have been possible without the team of professionals at No Starch Press, to whom I am deeply thankful. Thanks to Bill Pollock for believing in the concept and setting the ship on the right course.

I am grateful for the exceptionally talented team of editors at No Starch Press, including Rachel Monaghan, Eva Morrow, Frances Saux, Jenn Kepler, and Jill Franklin. Thanks to Miles Bond for his hard work on the book, and to Eric Matthes for taking the time to share his insights with me.

I want to thank Frédéric Descamps, affectionately known as @lefred in MySQL circles, for being the technical reviewer of this book. His attention to detail and deep knowledge of MySQL are impressive and appreciated.

Thanks to Jimmy Allen and the gang at Oasis for all the encouragement.

Introduction

In the mid-1980s, I landed my first software development job, which introduced me to the relational database management system (RDBMS), a system to store and retrieve data from a database. The concept has been around since 1970, when E.F. Codd published his famous paper introducing the relational model. The term relational refers to the fact that the data is stored in a grid of rows and columns, otherwise known as a table.

At the time I started out, commercial database systems weren’t widely available. In fact, I didn’t know anybody else who was using one. The RDBMS I used was imperfect, with no graphical interface and a command line interface that periodically crashed for no apparent reason. Since the World Wide Web had yet to be invented, there were no websites I could turn to for help, so I had no choice but to start my system back up and hope for the best.

Still, the idea was pretty cool. I saved large amounts of data in tables I created based on the nature of the information I wanted to store. I defined table columns, loaded data into the tables from files, and queried that data with Structured Query Language (SQL), a language for interacting with databases that allowed me to add, change, and delete multiple rows of data in a snap. I could manage an entire company’s data using this technology!

Today, relational database management systems are ubiquitous and, thankfully, far more stable and advanced than the clunkers I used in the ’80s. SQL has also vastly improved. The focus of this book is MySQL, which has become the most popular open source RDBMS in the world since its creation in 1995.

About This Book

This book will teach you to use MySQL using its Community Server (also known as the Community Edition), which is free to use and has the features most people need. There are also paid versions of MySQL, including the Enterprise Edition, that come with extra features and capabilities. All editions run on a wide variety of operating systems, such as Linux, Windows, macOS, and even the cloud, and have a robust set of features and tools.

Throughout this book, you’ll explore the most useful parts of MySQL development, as well as insights I’ve picked up over the years. We’ll cover how to write SQL statements; create tables, functions, triggers, and views; and ensure the integrity of your data. In the last three chapters, you’ll see how to use MySQL in the real world through hands-on projects.

This book is organized in five parts:

Part I: Getting Started

Chapter 1: Installing MySQL and Tools Shows you how to download MySQL and offers some tips for installing it on various operating systems. You’ll also install two tools to access MySQL: MySQL Workbench and the MySQL command line client.

Chapter 2: Creating Databases and Tables Defines databases and tables and shows how to create them. You’ll also add constraints to your tables to enforce rules about the data they will allow and see how indexes can speed up data retrieval.

Part II: Selecting Data from a MySQL Database

Chapter 3: Introduction to SQL Covers how to query database tables to select the information you want to display. You’ll order your results, add comments to your SQL code, and deal with null values.

Chapter 4: MySQL Data Types Discusses the data types you can use to define the columns in your tables. You’ll see how to define columns to hold strings, integers, dates, and more.

Chapter 5: Joining Database Tables Summarizes the different ways you can select from two tables at once, covering the main types of joins and how to create aliases for your columns and tables.

Chapter 6: Performing Complex Joins with Multiple Tables Shows you how to join many tables as well as use temporary tables, Common Table Expressions, derived tables, and subqueries.

Chapter 7: Comparing Values Walks you through comparing values in SQL. For example, you’ll see ways to check whether one value is equal to another, greater than another, or within a range of values.

Chapter 8: Calling Built-in MySQL Functions Explains what a function is, how to call functions, and what the most useful functions are. You’ll learn about functions that deal with math, dates, and strings, and use aggregate functions for groups of values.

Chapter 9: Inserting, Updating, and Deleting Data Describes how to add, change, and remove data in your tables.

Part III: Database Objects

Chapter 10: Creating Views Explores database views, or virtual tables based on a query you create.

Chapter 11: Creating Functions and Procedures Shows you how to write reusable stored routines.

Chapter 12: Creating Triggers Explains how to write database triggers that automatically execute when a change is made to data.

Chapter 13: Creating Events Shows you how to set up functionality to run based on a defined schedule.

Part IV: Advanced Topics

Chapter 14: Tips and Tricks Discusses how to avoid some common problems, support existing systems, and load data from a file into a table.

Chapter 15: Calling MySQL from Programming Languages Explores calling MySQL from within PHP, Python, and Java programs.

Part V: Projects

Chapter 16: Building a Weather Database Shows you how to build a system to load weather data into a trucking company’s database using technologies such as cron and Bash.

Chapter 17: Tracking Changes to Voter Data with Triggers Guides you through the process of building an election database, using database triggers to prevent data errors, and tracking user changes to data.

Chapter 18: Protecting Salary Data with Views Shows you how to use views to expose or hide sensitive data from particular users.

Every chapter includes Try It Yourself exercises to help you master the concepts explained in the text.

Who Is This Book For?

This book is suitable for anyone interested in MySQL, including folks new to MySQL and databases, developers who would like a refresher, and even seasoned software developers transitioning to MySQL from another database system.

Since this book focuses on MySQL development rather than administration, MySQL database administrators (DBAs) may want to look elsewhere. While I occasionally wander into a topic of interest to a DBA (like granting permissions on tables), I don’t delve into server setup, storage capacity, backup, recovery, or most other DBA-related issues.

I’ve designed this book for MySQL beginners, but if you’d like to attempt the exercises in your own MySQL environment, Chapter 1 will guide you through downloading and installing MySQL.

SQL in MySQL vs. SQL in Other Database Systems

Learning SQL is an important part of using MySQL. SQL allows you to store, modify, and delete data from your databases, as well as create and remove tables, query your data, and much more.

Relational database management systems other than MySQL, including Oracle, Microsoft SQL Server, and PostgreSQL, also use SQL. In theory, the SQL used in these systems is standardized according to the American National Standards Institute (ANSI) specifications. In practice, however, there are some differences among the database systems.

Each database system comes with its own extension of SQL. For example, Oracle provides a procedural extension of SQL called Procedural Language/SQL (PL/SQL). Microsoft SQL Server comes with Transact-SQL (T-SQL). PostgreSQL comes with Procedural Language/PostgreSQL (PL/pgSQL). MySQL doesn’t have a fancy name for its extension; it’s simply called the MySQL stored program language. These SQL extensions all use different syntaxes.

Database systems created these extensions because SQL is a non-procedural language, meaning it’s great for retrieving and storing data to or from a database, but it isn’t designed to be a procedural programming language like Java or Python that allows us to use if...then logic or while loops, for example. The database procedural extensions add that functionality.

Therefore, while much of the SQL knowledge you learn from this book will be transferable to other database systems, some of the syntax may require tweaking if you want to run your queries with a database system other than MySQL.

Using the Online Resources

This book includes many example scripts, which you can find at https://github.com/ricksilva/mysql_cc. The scripts for Chapters 2–18 follow the naming convention chapter_X.sql, where X is the chapter number. Chapters 15 and 16 have additional scripts in folders named chapter_15 and chapter_16.

Each script creates the MySQL databases and tables shown in the corresponding chapter. The script also contains example code and answers for the exercises. I recommend attempting the exercises yourself, but feel free to use this resource if you get stuck or want to check your answers.

You can browse through the scripts and copy commands as you see fit. From GitHub, paste the commands into your environment using a tool like MySQL Workbench or the MySQL command line client (these tools are discussed in Chapter 1). Alternatively, you can download the scripts to your computer. To do this, navigate to the GitHub repository and click the green Code button. Choose the Download ZIP option to download the scripts as a ZIP file.

For more information on MySQL and the tools available, visit https://dev.mysql.com/doc/. The MySQL reference manual is particularly helpful. Documentation for MySQL Workbench can be found at https://dev.mysql.com/doc/workbench/en/, and for documentation on the MySQL command line you can check out https://dev.mysql.com/doc/refman/8.0/en/mysql.html.

MySQL is a fantastic database system to learn. Let’s get started!

Part I

Getting Started

In this part of the book, you’ll install MySQL and the tools to access your MySQL databases. Then, you’ll start getting familiar with these tools by creating your first database.

In Chapter 1, you’ll install MySQL, MySQL Workbench, and the MySQL command line client on your computer.

In Chapter 2, you’ll create your own MySQL database and a database table.

1

Installing MySQL and Tools

To begin working with databases, you’ll install the free version of MySQL, called MySQL Community Server (also known as MySQL Community Edition), and two handy tools: MySQL Workbench and the MySQL command line client. This software can be downloaded for free from the MySQL website. You will use these tools to work on projects and exercises later in this book.

The MySQL Architecture

MySQL uses a client/server architecture, as shown in Figure 1-1.

Figure 1-1: The client/server architecture

The server side of this architecture hosts and manages resources or services that the client side needs to access. This means that, in a live production environment, the server software (MySQL Community Server) would run on a dedicated computer housing the MySQL database. The tools used to access the database, MySQL Workbench and the MySQL command line client, would reside on the user’s computer.

Because you’re setting up a development environment for learning purposes, you’ll install both the MySQL client tools and the MySQL Community Server software on the same computer. In other words, your computer will act as both the client and the server.

Installing MySQL

Instructions for installing MySQL are available at https://dev.mysql.com. Click MySQL Documentation, and under the MySQL Server heading, click MySQL Reference Manual and select the most recent version. You’ll then be taken to the reference manual for that version. On the left-hand menu, click Installing and Upgrading MySQL. Find your operating system in the table of contents and follow the instructions to download and install MySQL Community Server.

There are countless ways to install MySQL—for example, from a ZIP archive, the source code, or a MySQL installer program. The instructions vary based on your operating system and which MySQL products you want to use, so the best and most current resource for installation is always the MySQL website. However, I’ll offer a few tips:

When you install MySQL, it creates a database user called root and asks you to choose a password. Don’t lose this password; you’ll need it later.

In general, I’ve found it easier to use an installer program, like MySQL Installer, if one is available.

If you’re using Windows, you’ll be given the option of two different installers: a web installer or a full bundle installer. However, it’s not obvious which one is which, as shown in Figure 1-2.

Figure 1-2: Selecting the web installer for Windows

The web installer has a much smaller file size and its filename contains the word web, as highlighted in the figure. I recommend choosing this option because it allows you to select the MySQL products you want to install, and it downloads them from the web. The full bundle installer contains all MySQL products, which shouldn’t be necessary.

As of this writing, both installers appear on this web page as 32-bit. This refers to the installation application, not MySQL itself. Either installer can install 64-bit binaries. In fact, on Windows, MySQL is available only for 64-bit operating systems.

You can download MySQL without creating an account if you prefer. On the web page shown in Figure 1-3, select No Thanks, Just Start My Download at the bottom of the screen.

Figure 1-3: Downloading MySQL without creating an account

From here, your next step is to download MySQL Workbench, a graphical tool used to access MySQL databases. With this tool, you can explore your database, run SQL statements against that database, and review the data that gets returned. To download MySQL Workbench, go to https://dev.mysql.com/doc/workbench/en/. This takes you directly to the MySQL Workbench reference manual. Click Installation in the left-hand menu, choose your operating system, and follow the instructions.

When you install MySQL Community Server or MySQL Workbench on your computer, the MySQL command line client should be installed automatically. This client allows you to connect to a MySQL database from the command line interface of your computer (also called the console, command prompt, or terminal). You can use this tool to run a SQL statement, or many SQL statements saved in a script file, against a MySQL database. The MySQL command line client is useful in situations where you don’t need to see your results in a nicely formatted graphical user interface.

You’ll use these three MySQL products for most of what you do in MySQL, including the exercises in this book.

Now that your computer is set up with MySQL, you can start creating databases!

Summary

In this chapter, you installed MySQL, MySQL Workbench, and the MySQL command line client from the official website. You located the MySQL Server and MySQL Workbench reference manuals, which contain tons of useful information. I recommend using these if you get stuck, have questions, or want to learn more.

In the next chapter, you’ll learn how to view and create MySQL databases and tables.

2

Creating Databases and Tables

In this chapter, you’ll use MySQL Workbench to view and create databases in MySQL. Then you’ll learn how to create tables to store data in those databases. You’ll define the name of the table and its columns and specify the type of data that the columns can contain. Once you’ve practiced these basics, you’ll improve your tables using two helpful MySQL features, constraints and indexes.

Using MySQL Workbench

As you learned in Chapter 1, MySQL Workbench is a visual tool you can use to enter and run SQL commands and view their results. Here, we’ll walk through the basics of how to use MySQL Workbench to view databases.

Note

If you’re using another tool, such as PhpMyAdmin, MySQL Shell, or the MySQL command line client, be sure to read through this section anyway. The MySQL commands are the same regardless of the tool you use to connect to MySQL.

You’ll start by opening MySQL Workbench by double-clicking its icon. The tool looks like Figure 2-1.

Figure 2-1: Showing databases with MySQL Workbench

In the top-right panel, enter the show databases; command. Make sure to include the semicolon, which indicates the end of the statement. Then click the lightning bolt icon, highlighted in Figure 2-1, to execute the command. The results, a list of available MySQL databases, appear in the Result Grid panel (your results will look different from mine):

Database

--------

information_schema

location

music

mysql

performance_schema

restaurant

solar_system

subway

sys

Some databases in this list are system databases that were created automatically when MySQL was installed—such as information_schema, mysql, and performance_schema—and others are databases I’ve created. Any databases you create should appear in this list.

Database Terminology

In MySQL, the term schema is synonymous with database. You can use show databases or show schemas to return a list of all of your databases. Notice in Figure 2-1 that schemas is the term MySQL Workbench uses.

Sometimes the term database gets conflated with database system or relational database management system (RDBMS). For example, you might hear somebody ask, Which database are you using, Oracle, MySQL, or PostgreSQL? What they’re really asking is, "Which database system are you using?" A MySQL database is a place to organize your data, while a database system is software that is used to store and retrieve data.

You can also browse databases by using the Navigator panel on the left. Click the Schemas tab at the bottom of the panel to show a list of databases, and click the right arrow (▶) to investigate the contents of your databases. Note that, by default, the Navigator panel doesn’t show the system databases that were automatically created when MySQL was installed.

Now that you’ve seen how to view the list of databases in MySQL, it’s time to try creating your own.

Creating a New Database

To create a new database, you use the create database command along with a name for the database you want to create:

create database circus;

create database finance;

create database music;

Your database’s name should describe the type of data stored there. In this example, the database called circus might contain tables for data on clowns, tightrope walkers, and trapeze acts. The finance database might have tables for accounts receivable, income, and cash flow. Tables of data on bands, songs, and albums might go in the music database.

To remove a database, use the drop database command:

drop database circus;

drop database finance;

drop database music;

These commands remove the three databases you just created, any tables in those databases, and all of the data in those tables.

Of course, you haven’t actually created any tables yet. You’ll do that now.

Try it Yourself

2-1. Run show databases in MySQL Workbench. How many databases do you have?

2-2. Using MySQL Workbench, create a database named cryptocurrency. After you have created the database, run the show databases command. Do you see the new database in your list of databases? How many databases do you have now?

2-3. Drop the cryptocurrency database and then run show databases again. Is the database gone from your list of databases?

Creating a New Table

In this example, you’ll create a new table to hold global population data and specify what type of data the table can contain:

create database land;

use land;

create table continent

(

  continent_id      int,

  continent_name    varchar(20),

  population        bigint

);

First, you create a database called land using the create database command you saw earlier. On the next line, the use command tells MySQL to use the land database for the SQL statements that follow it. This ensures that your new table will be created in the land database.

Next, you use the create table command followed by a descriptive name for the table, continent. Within parentheses, you create three columns in the continent table—continent_id, continent_name, and population—and for each column you choose a MySQL data type that controls the type of data allowed in that column. Let’s go over this in more detail.

You define the continent_id column as an int so that it will accept integer (numeric) data. Each continent will have its own distinct ID number in this column (1, 2, 3, and so on). Then, you define the continent_name column as a varchar(20) to accept character data up to 20 characters long. Finally, you define the population as a bigint to accept big integers, as the population of an entire continent can be quite a large number.

Note

Chapter 4 covers MySQL data types, including bigint, in more depth.

When you run this create table statement, MySQL creates an empty table. The table has a table name and its columns are defined, but it doesn’t have any rows yet. You can add, delete, and modify the rows in the table whenever you need.

If you try to add a row with data that doesn’t match one of the column’s data types, however, MySQL will reject the entire row. For example, because the continent_id column was defined as an int, MySQL won’t allow that column to store values like Continent #1 or A because those values contain letters. MySQL won’t allow you to store a value like The Continent of Atlantis in the continent_name column either, since that value has more than 20 characters.

Constraints

When you create your own database tables, MySQL allows you to put constraints, or rules, on the data they contain. Once you define constraints, MySQL will enforce them.

Constraints help maintain data integrity; that is, they help keep the data in your database accurate and consistent. For example, you might want to add a constraint to the continent table so that there can’t be two rows in the table with the same value in a particular column.

The constraints available in MySQL are primary key, foreign key, not null, unique, check, and default.

Primary Keys

Identifying the primary key in a table is an essential part of database design. A primary key consists of a column, or more than one column, and uniquely identifies the rows in a table. When you create a database table, you need to determine which column(s) should make up the primary key, because that information will help you retrieve the data later. If you combine data from multiple tables, you’ll need to know how many rows to expect from each table and how to join the tables.