Master the database that powers the web. Learn to install MySQL, store, retrieve and update data, work with multiple relational tables, connect and program the database, and back everything up!

Jump Start MySQL

Preface

This chapter presents the first steps of getting started with MySQL. I’ll show you how to install MySQL on both Linux and Windows systems, so be sure to follow along on the platform of your choice. Then you’ll begin to get acquainted with MySQL’s command-line client as we use it to connect to the database server and create our first database.

Getting Started with MySQL

Data stored in a relational database is organized into tables . A database table organizes data in a grid-like fashion, where each entry forms a row and each column identifies a specific value in the entry. To illustrate this, here’s a table showing the number of medals won by each of the top five medal-winning countries that participated in the 2014 Winter Olympic Games. Each row lists the country’s name, how many gold medals, silver medals, and bronze medals were won, and the total number of medals won.

Storing Data

In Chapter 2, I briefly introduced you to the SELECT statement by using SELECT * to display all of the rows in the employee and address tables. But SELECT is certainly more powerful and flexible than that. It has the ability to retrieve only certain columns, filter and sort rows, and even select rows from multiple tables based on the relationships we’ve defined. You’ll learn how to use SELECT to do all that and more in this chapter and in Chapter 4.

Retrieving and Updating Data

Next to the table, the most important concept in the world of relational databases is, not surprisingly, relationships. Very early on in our discussions you saw how a record can be organized across multiple tables—each row in a table being part of a larger whole. It follows, then, that you’ll sometimes find the need to work with several tables at the same time.

Working with Multiple Tables

It’s more common for users to work with data stored in a database through an application’s display than to directly send queries to the server using a command-line client. Although any specific details depend on the nature and purpose of each application, generally speaking the program provides an interface to accept input and display data, and some internal process to formulate queries and communicate with the database management system. Users may not even be aware their data is stored in a database behind all of that. In this chapter, we’ll discuss the basics of communicating with MySQL from programming code.

Connecting from Code

A stored routine is a set of statements stored and executed in the database. From the perspective of a database administrator, routines can provide an additional level of security because we can give users access through an API of routines instead of allowing them to access or modify data in the tables directly. From the programmer's perspective, routines can improve an application’s performance in some cases because they process data while it’s still in the database. The logic may execute more efficiently, because of things like server hardware and programming language choices, and aggregate calculations (such as summation, averaging, etc.) reduce the amount of data sent across the network back to an application. To help reduce code duplication, logic that’s common to various applications using the same database can even be written as stored routines.

Programming the Database

As our lives become more and more datacentric, the importance of having backups cannot be overstated. The effects of data loss can range from mildly irritating, such as losing your contacts list, to having a serious economic impact, such as a bank losing financial records. The more important your data is, and the more difficult it is to recreate, the more important it is to have backups.

Community Questions