(Continued from previous part...)

Database Basics

Databases are managed by a relational database management system (RDBMS). An RDBMS supports a database language to create and delete databases and to manage and search data. The database language used in almost all DBMSs is SQL, a set of statements that define and manipulate data. After creating a database, the most common SQL statements used are INSERT, UPDATE, DELETE, and SELECT, which add, change, remove, and search data in a database, respectively.

Database
A repository to store data.

Table
The part of a database that stores the data. A table has columns or attributes, and the data stored in rows.

Attributes
The columns in a table. All rows in table entities have the same attributes. For example, a customer table might have the attributes name, address, and city. Each attribute has a data type such as string, integer, or date.

Rows
The data entries in a table. Rows contain values for each attribute. For example, a row in a customer table might contain the values "Matthew Richardson," "Punt Road," and "Richmond." Rows are also known as records.

Relational model
A model that uses tables to store data and manage the relationship between tables.

Relational database management system
A software system that manages data in a database and is based on the relational model. DBMSs have several components described in detail in Chapter 1.

SQL
A query language that interacts with a DBMS. SQL is a set of statements to manage databases, tables, and data.

Constraints
Restrictions or limitations on tables and attributes. For example, a wine can be produced only by one winery, an order for wine can't exist if it isn't associated with a customer, having a name attribute could be mandatory for a customer.

Primary key
One or more attributes that contain values that uniquely identify each row. For example, a customer table might have the primary key of cust ID. The cust ID attribute is then assigned a unique value for each customer. A primary key is a constraint of most tables.

Index
A data structure used for fast access to rows in a table. An index is usually built for the primary key of each table and can then be used to quickly find a particular row. Indexes are also defined and built for other attributes when those attributes are frequently used in queries.

Entity-relationship modeling
A technique used to describe the real-world data in terms of entities, attributes, and relationships.

Normalized database
A correctly designed database that is created from an ER model. There are different types or levels of normalization, and a third-normal form database is generally regarded as being an acceptably designed relational database.

MySQL Command Interpreter

The MySQL command interpreter is commonly used to create databases and tables in web database applications and to test queries. Throughout the remainder of this chapter we discuss the SQL statements for managing a database. All these statements can be directly entered into the command interpreter and executed. The statements can also be included in server-side PHP scripts, as discussed in later chapters.

Once the MySQL DBMS server is running, the command interpreter can be used. The command interpreter can be run using the following command from the shell, assuming you've created a user hugh with a password shhh:

% /usr/local/bin/mysql -uhugh -pshhh The shell prompt is represented here as a percentage character, %.

Running the command interpreter displays the output:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 to server version: 3.22.38

Type 'help' for help.

mysql>
The command interpreter displays a mysql> prompt and, after executing any command or statement, it redisplays the prompt. For example, you might issue the statement:

mysql> SELECT NOW( );
This statement reports the time and date by producing the following output:

+---------------------+
| NOW( ) |
+---------------------+
| 2002-01-01 13:48:07 |
+---------------------+
1 row in set (0.00 sec)

mysql>
After running a statement, the interpreter redisplays the mysql> prompt. We discuss the SELECT statement later in this chapter.

As with all other SQL statements, the SELECT statement ends in a semicolon. Almost all SQL command interpreters permit any amount of whitespace—spaces, tabs, or carriage returns—in SQL statements, and they check syntax and execute statements only after encountering a semicolon that is followed by a press of the Enter key. We have used uppercase for the SQL statements throughout this book. However, any mix of upper- and lowercase is equivalent.

On startup, the command interpreter encourages the use of the help command. Typing help produces a list of commands that are native to the MySQL interpreter and that aren't part of SQL. All non-SQL commands can be entered without the terminating semicolon, but the semicolon can be included without causing an error.

The MySQL command interpreter allows flexible entry of commands and SQL statements:

The up and down arrow keys allow previously entered commands and statements to be browsed and used.

The interpreter has command completion. If you type the first few characters of a string that has previously been entered and press the Tab key, the interpreter automatically completes the command. For example, if wines is typed and the Tab key pressed, the command interpreter outputs winestore, assuming the word winestore has been previously used.

If there's more than one option that begins with the characters entered, or you wish the strings that match the characters to be displayed, press the Tab key twice to show all matches. You can then enter additional characters to remove any ambiguity and press the Tab key again for command completion.

Several common statements and commands are pre-stored, including most of the SQL keywords discussed in this chapter.

To use the default text editor to create SQL statements, enter the command edit in the interpreter. This invokes the editor defined by the EDITOR shell environment variable. When the editor is exited, the MySQL command interpreter reads, parses, and runs the file created in the editor.

When the interpreter is quit and run again later, the history of commands and statements is kept. It is still possible to scroll up using the up arrow and to execute commands and statements that were entered earlier.

You can run commands and SQL statements without actually launching the MySQL command interpreter. For example, to run SELECT now( ) from the Linux shell, enter the following command:

mysql -ppassword -e "SELECT now( );" This is particularly useful for adding SQL commands to shell or other scripts.

(Continued on next part...)