Basic SQL - Learning MySQL - page 135

CHAPTER 5
Basic SQL
SQL is the only database language in widespread use. Since it was first proposed in the
early 1970s, it has been criticized, changed, extended, and finally adopted by all the
players in the database market. The latest standard is SQL-2003—the 2003 denotes its
release year—but the version supported by most database servers is more closely related
to its predecessors, SQL-1999 and SQL-1992. MySQL supports most of the features of
SQL-1992 and many from the newer SQL standards, but it also includes many non-
standard features that give more control over the database server and how it evaluates
queries and returns results.
This chapter introduces the basics of MySQL's implementation of SQL. We show you
how to read data from a database with the SELECT statement, and how to choose what
data is returned and the order it is displayed in. We also show you the basics of mod-
ifying your databases with the INSERT statement to add data, UPDATE to change, and
DELETE to remove it. We also explain how to use the nonstandard SHOW TABLES and SHOW
COLUMNS statements to explore your database.
Following our example-based approach, we use the music database designed in Chap-
ter 4 to show you how to work with an existing database, and use basic SQL to read
and write data. In Chapter 6, we'll explain how to create the music database on your
MySQL server. We'll also show how you can create your own database and tables, and
modify the structure of existing ones. In Chapters 7 and 8, you'll learn about some
advanced features of the SQL variant used by MySQL.
Using the Music Database
In Chapter 4, we showed you how we understood the requirements for storing a music
collection and how we designed the music ER model. We also introduced the steps you
take to convert an ER model to a format that makes sense for constructing a relational
database. For convenience, we've reproduced the music database ER diagram in Fig-
ure 5-1. In this section, we show you the structure of the MySQL database that we
created after converting the ER model into SQL statements. We don't explain the SQL
statements we used to create the database; that's the subject of Chapter 6.