Web Database Applications with PHP and MySQL, 2nd Edition

Chapter 5. SQL and MySQL

In this chapter, we introduce the SQL database query language and the MySQL©^[1] database management system. Using our case study winestore database as a worked example, we show you how to use SQL to define, manipulate, and query databases. At the end of this chapter, you'll have the database skills to build a database tier for your web database applications.

In this chapter, we cover the following topics:

• A short introduction to relational databases

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

• The MySQL command interpreter and the basic features of MySQL

• Using SQL to create and drop databases and tables

• Using SQL to insert, delete, and update data

• Querying with SQL, illustrated through examples and a case study

We assume that you have already installed MySQL and loaded the sample winestore database. If not, the guides in Appendix A through Appendix C will help you.

The techniques that we discuss are used to interact with MySQL after a database has been designed and expressed as SQL statements. An introduction to relational modeling and design can be found in Appendix E. Managing and using the MySQL database server, and more advanced SQL features, are discussed in Chapter 15. Chapter 8 covers issues that arise when multiple users are writing to web databases.

Database Basics

The field of databases has its own terminology. Terms such as database, table, attribute, row, primary key, and relational model have specific meanings ...