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.
A repository to store data.
The part of a database that stores the data. A table has
columns or attributes, and the data stored in rows.
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
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.
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.
A query language that interacts with a DBMS. SQL is a
set of statements to manage databases, tables, and data.
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.
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.
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.
A technique used to describe the real-world data in
terms of entities, attributes, and relationships.
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
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:
Type 'help' for help.
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)
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
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
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
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
mysql -ppassword -e "SELECT now( );" This is
particularly useful for adding SQL commands to shell or
Page Numbers : 1