PHP Tutorial – Databases in PHP, SQL Basics

In the last part of this tutorial series we talked about the basic structure
of databases in modern database management systems (DBMS) and mentioned the
structured language used in accessing those databases.  The language is
the Structured Query Language, SQL, which is commonly pronounced as if it was a
word – "Seekul".  Now, before we get into coding specific commands in PHP,
it is very worthwhile to get an overview of the basic elements of the language
and the purpose of those elements.

In SQL everything is done in a "query".  Personally I think this must be
because somebody got the idea that since it’s a query language, everything must
be a query, and made it so even if it’s an instruction like "add this record". 
That would be an insert query!  Ah well, if I want to be a master of SQL I
will have to toe the line an accept the expanded notion of  "queries"! 
More about them later — first, lets take a look at the most common commands
you’ll see.

Create

It seems logical to begin with the create command, since it is used to create
databases and the tables within them.  In practice, databases are usually
created outside an application like a PHP program.  They are most
frequently created using a management tool of the DBMS.  For this reason,
you probably won’t write many database create commands.  It is also used to
create new tables, however.  You might write a few of those commands!

Drop

The opposite of create, drop is used to delete databases and tables. 
For the same reasons stated for create, you’ll probably write more table drops
than database drops.

Insert

The first of the record level commands, insert is used to create a record in
a table.  Most of the work you do in database systems will involve
operations on records.  Remember that a record corresponds to a row in a
table (the columns are the data elements, or fields, within a record.)

Delete

At the record level, delete is the opposite of insert.  It is used to
delete records in a table.

Update

The other thing you’ll want to do to a record — change some of the
information in it!  Update is used to update, or rewrite, a record.

Select

The select instruction picks a record, or records, from a table or tables. 
Select is the command that provides the real "query" in SQL.  the select
command enables you to, for example, select all addresses where the city is
Atlanta and the TV Brand Installed is Sony.  In this manner you are
querying the database for all records that match some specific criteria. 
You will spend lots of time writing select queries.  Options in select
commands allow you to create a special view of the information in a database and
sequence the record however you wish.

These few simple commands provide the basis of all the power that a database
management system can bring to your application.  It seems too simple to be
true, but it is that very simplicity which makes the Structured Query Language
such a valuable tool.  There are other commands available, especially in
some DBMS implementations, but these basic commands are always available. 
Remembering these few command will help you very much as you progress with your
DBMS coding education.