17.1 An introduction to SQL databases

17.1 An introduction to SQL databases

17.1.1 What is an SQL database?

Over the last decade, computing technologies have made permanent changes in the way business is conducted in offices around the world. Whether you are running a large corporation or just the owner of a small business, orders placed by customers in foreign countries can now be instantly processed by a few mouse clicks on the Internet. By traveling at a speed well beyond 6,000 miles (9,600 km) per second with a good connection, emails and millions of Web sites on the Internet are now an integral part of the so-called global economy.

Also, thanks to relational database management systems (RDBMS) and the Structured Query Language (SQL), database systems have become indispensable structures for almost all corporations, large or small. We can say that information is now stored on databases in every conceivable business environment. Apart from some word processing skills, a basic knowledge of databases and SQL is a requirement for most office staff nowadays. With the Internet and World Wide Web, we are all riding the wave of the so-called online age: online banking, shopping, and trading are just some examples. Indeed, for many companies, putting their databases online is vital for their business expansion and to get into the international global market.

In this chapter, an introduction to databases on the Internet is presented. In particular, a database product called MySQL is used for most examples and demonstrations. Some details on how to install and use it on the Internet are given. Along with SQL, information on other database products such as Microsoft Access and Oracle is also provided. They are the so-called SQL-based databases or simply SQL databases since they can be controlled and accessed by SQL. This capability is the main characteristic of all RDBMS dominating the database market and applications. From Microsoft Access and FoxPro, to Borland's Paradox and to Sybase, Informix, Oracle, and MySQL all these database products use SQL.

Compared to others, MySQL from MySQL AB (see www.mysql.com) is a relatively new arrival in the database race. The decision to use MySQL for examples and demonstrations in this chapter is based on the following reasons:

• It is a mature implementation of the SQL standard, easy to use, and can be used with Web applications in mind. For example, PHP has built-in support for MySQL access.

• It is available on virtually all platforms and operating environments including Microsoft Windows and UNIX/LINUX systems.

• It is free with source code available.

This chapter is an introduction to database programming on the Web. Some dedicated pages or utilities are developed to access and control MySQL on the Internet. Together with server scripting techniques, Open Database Connectivity (ODBC) and Database Interface (DBI) technologies are also discussed in detail. To understand and program databases on the Web, let's begin with the characteristics of SQL databases.

17.1.2 The characteristics of SQL databases

Roughly speaking, a database that can be controlled and accessed by the SQL commands and language is called an SQL database. The following is a sample SQL command (see example ex15-15.htm):

SELECT * FROM people;

This command, basically, would select and display all the fields from a table called people. SQL commands are not case sensitive. In order to distinguish between system commands and user-defined names, we generally use capital letters for system keywords and lower case for defined names. Consider the SQL command above; it pays no attention to what kind of database is involved. Once the database involved has been set up on a remote server or on the Internet, whether it is a Microsoft Access, FoxPro, Oracle, or MySQL database, the same command can be used to access the data.

SQL is a standard language from the American National Standards Institute (ANSI) used to manipulate and retrieve data from databases. SQL provides a complete set of languages to enable a programmer or database administrator to do all sorts of things, such as query a database for information, update the contents, or even change the structure of a database. You will learn more about SQL databases and statements in the coming sections.

Another characteristic of SQL databases is the use of tables. All information in an SQL database or relational database is represented explicitly as values in tables. One database can have multiple tables with names and related to each other. Using a table structure, all information in the database is guaranteed to be accessible by using a combination of the table name, primary key value, and column (or field) name. For example, suppose you have created a database called personnel with MySQL. Inside this database, a table called people may look as in Table 17.1.

Table 17.1. An MySQL table: people

	id	name	birth	sex	location	salary
	1	Michael	19501218	M	London	30000
	2	Mary	19800622	F	Paris	23000
	3	Peter	19751011	M	New York	28000
	4	Tom	19590224	M	Paris	28000
	5	Richard	19690308	M	New York	23000
	6	Ben	19790914	M	London	31000
	7	Sue	19690119	F	Paris	30000
	8	Rose	19760818	F	Tokyo	22000
	9	Jenny	19721027	F	New York	27000
	10	Anne	19761102	F	London	28000

In this case, the first column id is the primary key of the table providing a unique numbered reference to the record. The 10 rows are the records in the table. To retrieve the first five records, a user would issue the SQL command

SELECT * FROM people WHERE id < 6;

To retrieve a specific record, e.g., Mary, a user may use

SELECT * FROM people WHERE name="Mary";

Don't worry too much about the syntax and exact explanation at this moment; more details and explanations will be given later in this chapter. The important point at this stage is that every element inside the database can be accessed using the table structure.

Before we continue the discussion of SQL, let's install and set up a database product called MySQL on our system.