Mastering MySQL 4

MySQL is a relational database management system (RDBMS). It's a program capable of storing an enormous amount and a wide variety of data and serving it up to meet the needs of any type of organization, from mom-and-pop retail establishments to the largest commercial enterprises and governmental bodies. MySQL competes with well-known proprietary RDBMSs, such as Oracle, SQL Server, and DB2.

MySQL comes with everything needed to install the program, set up differing levels of user access, administer the system, and secure and back up the data. You can develop database applications in most programming languages used today and run them on most operating systems, including some of which you've probably never heard. MySQL utilizes Structured Query Language (SQL), the language used by all relational databases, which you'll meet later in this chapter (see the section titled "Creating and Using Your First Database"). SQL enables you to create databases, as well as add, manipulate, and retrieve data according to specific criteria.

But I'm getting ahead of myself. This chapter provides a brief introduction to relational database concepts. You'll learn exactly what a relational database is and how it works, as well as key terminology. Armed with this information, you'll be ready to jump into creating a simple database and manipulating its data.

What Is a Database?

The easiest way to understand a database is as a collection of related files. Imagine a file (either paper or electronic) of sales orders in a shop. Then there's another file of products, containing stock records. To fulfill an order, you'd need to look up the product in the order file and then look up the stock levels for that particular product in the product file. A database and the software that controls the database, called a database management system (DBMS), helps with this kind of task. Most databases today are relational databases, named such because they deal with tables of data related by a common field. For example, Table 1.1 shows the Product table, and Table 1.2 shows the Invoice table. As you can see, the relation between the two tables is based on the common field stock_code. Any two tables can relate to each other simply by having a field in common.

Database Terminology

Let's take a closer look at the previous two tables to see how they are organized:

• Each table consists of many rows and columns.

• Each row contains data about one single entity (such as one product or one order). This is called a record. For example, the first row in Table 1.1 is a record; it describes the A416 product, which is a box of nails that costs 14 cents. To reiterate, the terms row and record are interchangeable.

• Each column (also called a tuple) contains one piece of data that relates to the record, called an attribute. Examples of attributes are the quantity of an item sold or the price of a product. An attribute, when referring to a database table, is called a field. For example, the data in the Description column in Table 1.1 are fields. To reiterate, the terms attribute and field are interchangeable.

Given this kind of structure, the database gives you a way to manipulate this data: SQL. SQL is a powerful way to search for records or make changes. Almost all DBMSs use SQL, although many have added their own enhancements to it. This means that when you learn about SQL in this chapter and in more detail in later chapters, you aren't learning something specific to MySQL. Most of what you learn can be used on any other relational database, such as PostgreSQL, Oracle, Sybase, or SQL Server. But after tasting the benefits of MySQL, you probably won't want to change!