As if PHP wasn't enough, now you find out you
have to be able to work with databases as well! Well, never fear,
Timothy has a database primer that will get you headed in the right
direction. Databases are stores of information.
Databases and SQL As
if PHP wasn't enough, now you find out you have to be able to work with
databases as well! Well, never fear, Timothy has a database primer that
will get you headed in the right direction.
Databases are stores of
information. They allow one to easily record and access large amounts
of information for a variety of purposes. Because pretty much any type
of information can be stored in a database, they can be found literally
everywhere. Databases store medical records, police reports, sale
transactions, music and video collections and more!
Databases
have proven handy in web development, too. Database servers such as
MySQL, PostgreSQL, Oracl and DB2 provide efficient storage and data
management tasks for dynamic, content driven sites.
Databases
store their information in groups of records known as tables. For
example, a particular table may resemble the following:
Last Name First Name Address City State Zip Code
---------------------------------------------------------------------
Adams Gwendolyn 205 W Third St. Brownville AL 35020
Johnson Diane 82 Richardson Ave. Fresno CA 93702
Smith Harold 321 Elm St. Portsmouth RI 02871 |
The
table contains the name and addresses of several individuals; each line
forms a record consisting of an individual's last name, first name,
address, city, state and postal code. The table keeps these similar
records grouped together.
A database can contain more than one
table. A database that can search and retrieve information from
different tables and then relate it to that from other tables in the
database is often called a Relational Database.
If you are
familiar with spreadsheet applications, you may think a table akin to a
spreadsheet. The spreadsheet is a table with each row comprises one
record and each column is a different field. And as a spreadsheet
workbook is a collection of related spreadsheets, a database is a
collection of related tables.
Databases and SQL - Adding Information To A Database
One
interacts with a database by making queries to the database server. A
query is a request to the server to perform some specific action on one
or more tables. Such actions could be creating a table, adding
information to a table, retrieving information from a table and even
dropping tables or deleting entire databases!
The Structured
Query Language (SQL) is a set of commands used to communicate with a
database. Its syntax is simple and its queries read very much like
natural language.
Create
The CREATE command
can be used to create new databases and tables. To create a database,
issue the CREATE command followed by DATABASE and the database name.
SQL statements terminate with a semi-colon.
| CREATE DATABASE myDatabase; |
If
there is more than one database available to you, you can select one to
work with by issuing the USE command followed by the name of the
desired database.
To
create a table, issue the CREATE command followed by TABLE, the table
name, and then in parenthesis a list of field/column names and their
corresponding data types. A comma separates each field and data type
pair.
CREATE TABLE addresses
(last_name VARCHAR(50), first_name VARCHAR(50),
address VARCHAR(50), city VARCHAR(50), state CHAR(2),
zip_code CHAR(5)); |
CREATE TABLE employees
(employee_id INTEGER, last_name VARCHAR(50),
first_name VARCHAR(50), hire_date DATE, notes TEXT); |
The
first example creates a table named addresses that stores a person’s
name and address. The second example creates a table named employees
that stores an employee's identification number, name, hire date and
any special comments.
Different database servers support various
types of data such as integers, floating point decimals, dates and
binary files. For a complete list of data types available for a
particular server, you will need to refer to each server's
documentation. There are also many resources available on the Internet.
Insert
The
INSERT command can be used to add records to a table. To add a record,
issue the INSERT command followed by INTO, the desired table, VALUES
and then the corresponding values surrounded by parentheses. Commas
should separate the values, strings should be quoted and the entire
query statement is terminated with a semi-colon.
INSERT INTO addresses VALUES
("Smith", "Harold", "321 Elm St.", "Portsmouth", "RI", "02871"); |
INSERT INTO employees VALUES
(355, "Harold", "Smith", "2003-03-30", NULL); |
The
first example adds Harold's information to the addresses table. The
second example adds Harold's information to the employees table. NULL
is a special keyword used to represent an empty field, so in this case
there are no special comments to record.
Databases and SQL - Retrieving Information From A Database
It
wouldn't make sense to save information without the ability to retrieve
it at some future date. The SELECT command is used to retrieve records
from a table.
The SELECT statement is both powerful and flexible.
But because this is an introductory tutorial, all of the nuances of the
SELECT command won't be covered. I'll instead illustrate the basic
usage of the command.
A simple form of the SELECT command is
SELECT followed by the desired field to retrieve, FROM and then
followed by the desired table. An asterisk can be used as shorthand to
request all fields.
The results of such a query might resemble our initial table example.
Last Name First Name Address City State Zip Code
---------------------------------------------------------------------
Adams Gwendolyn 205 W Third St. Brownville AL 35020
Johnson Diane 82 Richardson Ave. Fresno CA 93702
Smith Harold 321 Elm St. Portsmouth RI 02871 |
Of
course returning every record might not be desired. The keyword WHERE
may be used to set forth conditions to satisfy when selecting matching
records.
| SELECT * FROM addresses WHERE state = "CA"; |
Such a query would return only the records where the abbreviation for California appears in the state field.
Last Name First Name Address City State Zip Code
------------------------------------------------------------------
Johnson Diane 82 Richardson Ave. Fresno CA 93702 |
AND and OR may also be used to identify more than one field when constructing a WHERE condition.
SELECT * FROM employees WHERE first_name = "Diane" AND
last_name = "Johnson"; |
Such a query would return only the records where the value of first_name is Diane and the value of last_name is Johnson.
ID First Name Last Name Date Hired Notes
-----------------------------------------------------------------------
189 Diane Johnson 2001-07-15 secretary of the year for 2003 |
As
mentioned earlier, the asterisk is a shorthand request for all fields.
Individual fields may be requested by listing them after SELECT. Commas
separate multiple requested fields.
| SELECT first_name, last_name FROM addresses; |
Such a query would return just the values of the first_name and last_name fields in the address table.
First Name Last Name
---------------------
Gwendolyn Adams
Diane Johnson
Harold Smith |
Databases and SQL - Changing Information In A Database
Occasionally
data that is stored in a database may become stale. SQL's DELETE,
UPDATE and DROP commands allow you to manage records and keep the
information current.
Delete
The DELETE
command is used to remove records from a table. Issue the DELETE
command followed by FROM, the table that holds the records, WHERE and a
condition statement that will be used to match the records.
DELETE from addresses where first_name = "Harold"
AND last_name = "Smith"; |
The example query would remove Harold Smith's record from the addresses table.
Update
The
UPDATE command can be used to update a record's fields. Issue the
UPDATE command followed by the table name, SET, the field names and
their new values, WHERE and a condition statement that will be used to
match the correct record.
For example, assume Gwendolyn Adams
from our previous examples has decided to move to Milwaukee, WI. Her
record in the database needs to be updated to reflect her new address.
UPDATE addresses SET address = "613 Wright St.",
city = "Milwaukee", state = "WI", zip_code="53216"
WHERE (first_name = "Gwendolyn" AND last_name = "Adams"); |
The
query would update the record in the addresses table for Ms. Adams to
store her new address of 613 Write St. Milwaukee, WI, 53216.
Drop
When
data is no longer needed, the DROP command can be used to delete a
table or an entire database. To delete a table, issue the DROP command,
followed by TABLE and the name of the table to be dropped.
To delete an entire database, issue the DROP command, followed by DATABASE and the name of the database to be dropped.
| DROP DATABASE myDatabase; |
Be
careful! Deleting entire tables and databases cannot be undone so one
should be extremely cautious when using the DROP command.
Databases and SQL - Conclusion
To
recap, databases store, manage and allow access to large amounts of
information quickly and easily. They can be used to record any type of
information and are found practically everywhere.
The standard
language used to communicate with database servers is SQL. With SQL,
one can perform various actions such as create, add, drop and delete
tables, records and even entire databases. It's syntax is simple and
its queries read very much like natural language.
About The Author
Timothy
Boronczyk lives in Syracuse, NY, where he works as an E-Services
Coordinator for a local credit union. He has a background in elementary
education, over 5 years experience in web design and has written
tutorials on web design, PHP, Ruby, XML and various other topics. His
hobbies include photography and composing music.
|
