Database Basics

At its very simplest, a database is an organized way of holding together various pieces of information. The term database actually refers to the collection of data, not the means by which it is stored. For a computer database, the software used to manage the data is known as a database-management system (DBMS).

A database need not be stored on computeralthough, of course, that is what this book is about! A filing cabinet and a telephone directory both contain databases: They hold information, organized in a structured manner so that you can easily access the individual item you want to find.

Relational Databases

MySQL is a relational database-management system (RDBMS). The term relational indicates that MySQL can store its data as a number of different tables that are related to each other in some way.

The advantage of this type of database over a flat table system is that very large databases can be constructed from different tables, each of which contains only information relevant to that table.

Much has been written about relational database theory, but you need to understand only a little to create efficient databases. You don't need to be scared by what appears to be a very academic topica great deal of the principles of relational database design are grounded in common sense. You will learn more about this in Lesson 16, "Designing Your Database."

Why Use MySQL?

You can choose from many different RDBMS options, so why use MySQL over another system?

One of the primary factors when choosing an RDBMS is cost. MySQL is distributed as open-source software under the GNU General Public License, so you can actually use MySQL free of charge.

Also available for MySQL is a commercial license that includes various levels of technical support for users with mission-critical systems. Even with the cost of commercial support, MySQL offers a significantly lower total cost of ownership over other enterprise-level RDBMS.

MySQL is robust, powerful, and scalable. It can be used in applications from tiny web databases to very large data warehouses with terabytes of data. You can use MySQL replication or clustering to guarantee 100% availability. A number of case studies published on MySQL.com give an example of just what MySQL is capable of.

Case Studies

You can read about how MySQL has been used in large projects at http://www.mysql.com/why-mysql/case-studies/.

Anatomy of a Database

A database consists of a series of tables. Each table has a name, which is how it is referenced in the SQL language. Each database also has a name, and the same RDBMS can manage many different databases. MySQL is a multiuser database and can restrict access to each database to only specific users.

A database table looks somewhat like a spreadsheet: a set of rows and columns that can contain data at each intersection. In a relational database, you store different types of data in different tables. For instance, the sample database in this book has separate tables for customers, products, and orders.

Each table has a defined set of columns, which determine the values it can store. For example, the table that contains information about products needs to store a name, price, and weight for every product. Each row of data can contain values for only the columns defined in the table.

In addition, each column is defined as a particular data type, which determines the kind of values it can store. The data type might restrict values to numeric or date values, or it might impose a maximum size.

What Is SQL?

The Structured Query Language (SQL) is the most common way to retrieve and manage database information. An SQL query consists of a series of keywords that define the data set you want to fetch from the database. SQL uses descriptive English keywords, so most queries are easy to understand.

SQL

Sometimes you will hear SQL pronounced as the word sequel, but it's also okay to pronounce it as the letters S-Q-L.

Virtually every RDBMS on the market uses the SQL language. The MySQL implementation of SQL conforms to the ANSI SQL standards and implements some of its own extension to handle features that are specific to MySQL.

About MySQL AB and mysql.com

MySQL AB is the Swedish company that develops, maintains, and markets the MySQL database server and tools.

MySQL Source

Although MySQL is distributed as open-source software, MySQL AB owns the source code to MySQL and determines the terms under which it is made available. Currently, MySQL is distributed under the GNU General Public License.

MySQL AB provides technical support to users with commercial support packages. The company also runs MySQL training courses around the world and issues MySQL Certification to those who want to become accredited MySQL Developers or Database Administrators.

The MySQL website, http://www.mysql.com/, contains a section called Developer Zone (which you can access directly at http://dev.MySQL.com/) where you can find online support in the form of forums, mailing lists, and user groups. Consider joining one of the online communities; they are a great way to learn more about MySQL from other users.

You can download or view online (in the Developer Zone) a comprehensive reference manual for MySQL. You can download a printable PDF version or browse an HTML-format manual that is fully searchable.

Online Manual

You can use the shortcut URL http://www.mysql.com/ANYTHING to search the online manual forANYTHING. If there is an exact match, you will be taken straight to the appropriate page; otherwise, you will see a list of possible matches.

Other Resources

A number of MySQL books have been published under the MySQL Press brand. Currently, these include MySQL Tutorial (2004), MySQL Language Reference (2005), MySQL Administrator's Guide (2005), and MySQL Certification Study Guide (2004).

You can view the current range of MySQL Press titles at http://dev.mysql.com/books/mysqlpress/index.html.