As you can see, SQL can be a very powerful and useful tool for retrieving the data that you need. You
may be asking yourself, "How would I use this in a real-life situation?" The answer is simple; You would
embed your SQL statements inside a programming language to retrieve the data that you want. This is
covered in detail on Day 12, "How to Get to the Data—Database Interfaces," Day 13, "How to Use
MyODBC," and Day 14, "The Perl Database Interface." Today, you will learn the basics of SQL. In the
coming days, you will learn how to get around your data. You will be able to easily view what is
contained in your tables, delete unnecessary data, and even add new data to your database.Manipulating Data with SQL
The data manipulation segment of the structured query language can be broken down into the following four
commands: SELECT, INSERT, UPDATE, and DELETE. These commands cover the full range of what you can
do to your data. Each command has its own nuances and will be further explained later in this chapter. I
would like to take a moment and explain some of the things that are common to all the statements.
After each verb, there is a list of column headers. You can name each column that you want to receive
or, if you need to, you can use the wildcard character to view them all. The asterisk () is the wildcard
character for MySQL (as well as most other RDBMS). If you wanted to see all the columns from the
Customer table, you could issue the following statement:
SELECT FROM Customers
This statement would show all the columns.There are some rules that are common to all statements. These are general syntax rules and must be
followed for the database to understand what you are telling it.The following are some of the rules that must be followed:
If you are using more than one table in the FROM clause, all fields that are the same
name must be prefixed by the table name wherever they are used. For example: The
Orders table and the Customers table both have a Customer_ID field. If you were
to perform a query on these tables, you would have to explicitly state the table name
for the Customer_ID field whenever you use it:
SELECT Orders.Order_ID FROM Orders, Customers
WHERE Customer.Customer_ID = Orders.Customer_ID
Multiple statements in a WHERE clause must be connected by either an AND or an OR
keyword. Be careful of the order in which you use these commands. You can group
statements using parentheses to get the desired query.
All SQL statements must have a verb, a FROM or INTO clause, and usually a
parameter list of column names that may be one or all of the columns in a given table.
Another thing that is common to all the verbs is the ability to alias a table. To alias a table is to give it
another name to help save time and space when identifying the tables from which you want to get
information. Aliasing looks like the following:
SELECT O.Order_ID from Orders AS O, Customers AS C
WHERE C.Customer_ID = O.Customer_ID
As you can see, aliasing allows you to use a kind of shorthand when referencing tables, instead of
typing the following code. The keyword AS must be used when you alias with MySQL.
SELECT Orders.Order_ID FROM Orders, Customers
WHERE Customers.Customer_ID = Orders.Customer_ID
It can save you some time and is less cumbersome then typing the full table name out. You have to
state the table name when using more than one table in the FROM clause so that the database knows
which field you are talking about. If you have to do that, you might as well take this shortcut.
Be careful when using more than command. Make sure your joins are complete and succinct (joins are
covered in the "Working with Joins" section later today). If not, you may cause a Cartesian join. A
Cartesian join is when you join every row of one table to every row of another table. This is very CPU-
intensive and is usually done by mistake. To avoid this, make sure you understand what you are joining
and that the join is complete. If not, you will have the distinct pleasure of recreating data from a log
because you wiped out a whole lot of needed data.SELECT
The SELECT statement is probably the most used of all SQL statements. The SELECT statement will
only return the data that is held in the database. MySQL probably performs this type of query faster than
any other database on the market. The SELECT syntax is as follows: