Chapter 3. MySQL and SQL

In this chapter, we introduce the MySQLdatabase management system (DBMS) and the SQL database query language for defining and manipulating databases. Using our case study, Hugh and Dave's Online Wines, as a guide, we illustrate examples of how to use SQL. The techniques that we discuss are used to interact with a DBMS after a database has been designed. An introduction to relational modeling and design can be found in Appendix C, and a more comprehensive introduction to MySQL and SQL can be found in many of the resources that are listed in Appendix E.

In this chapter, we cover the following topics:

• A short introduction to relational databases and relational modeling

• A quick start guide to the winestore database and its full entity-relationship model

• The MySQL command interpreter and the basic features of MySQL

• Using SQL to create and modify databases, tables, and indexes

• Using SQL to insert, delete, and update data

• The SQL SELECT statement for querying, with examples of simple and advanced queries

• Functions and operators in SQL and MySQL

• Advanced features, including managing indexes and keys, tuning the MySQL DBMS, security, and the limitations of MySQL

We assume that you have already installed MySQL. If not, the guide in Appendix A will help you. Chapter 6 covers other selected advanced database topics that arise when writing to databases, such as supporting multiple users, transactions, and locking in MySQL. Complete examples of SQL queries and MySQL in use in a web database application can be found in Chapter 10 to Chapter 13.