Incidentally, the pronunciation of SQL is somewhat of a contentious issue. The official party line is that SQL should be pronounced "es queue el." However, many people opt for the more casual and also more efficient pronunciation, "sequel." Count me in the latter camp!
Retrieving Records Using SELECT
Just about everything in SQL is carried out via a query, which is simply the act of communicating with the database according to an established set of SQL commands. The query used to retrieve data from a database is called the
SELECT statement. It has several parts, not all of which are mandatory. The most basic
SELECT statement is composed of two partsthe select list and the
FROM clause. A very simple
SELECT statement looks like this:
SELECT * FROM students
Following are the database records returned as the results of the query:
+--------+----------------+----------------+-------+----------+---------+ | id_st..| student_name | city | state | classif. | tuition | +--------+----------------+----------------+-------+----------+---------+ | 1 | Franklin Pierce| Hillsborough | NH | senior | 5000 | | 2 | James Polk | Mecklenburg Co | NC | freshman | 11000 | | 2 | Warren Harding | Marion | OH | junior | 3500 | +--------+----------------+----------------+-------+----------+---------+
In this case, the
* is the select list. The select list indicates which database columns should be included in the query results. When a
* is supplied, it indicates that all of the columns in the table or tables listed in the
FROM clause should be included in the query results.
FROM clause contains the list of tables from which the data will be retrieved. In this case, the data is retrieved from just one table,
students. I'll explain how to retrieve data from multiple tables in a bit.
Let's go back to the select list. If you use a select list that isn't simply
*, you include a list of column names separated by commas. You can also rename columns in the query results (useful in certain situations), using the
AS keyword, as follows:
SELECT id_students AS id, student_name, state FROM students
As the results show, only the student name and state columns are returned for the records:
+------+-----------------+-------+ | id | student_name | state | +------+-----------------+-------+ | 1 | Franklin Pierce | NH | | 2 | James Polk | NC | | 2 | Warren Harding | OH | +------+-----------------+-------+
id_students column is renamed id in the query results using the reserved word 'AS'. The other keyword you'll often use in a select statement is
DISTINCT. When you include
DISTINCT at the beginning of a select statement, it indicates that no duplicates should be included in the query results. Here's a sample query:
SELECT DISTINCT city FROM students
And here are the results:
+--------------------+ | city | +--------------------+ | Hillsborough | | Mecklenburg County | | Marion | +--------------------+
DISTINCT, this query would return the
city of every student in the
students table. In this case, it returns only the distinct values in the table, regardless of how many of each of them there are. In this case, there are only three records in the table and each of them has a unique city, so the result set is the same as it would be if
DISTINCT were left off.
Both of the previous queries simply return all of the records in the
students table. Often, you'll want to constrain the resultset so that it returns only those records you're actually interested in. The
WHERE clause is used to specify which records in a table should be included in the results of a query. Here's an example:
SELECT student_name FROM students WHERE id_students = 1
Only the record with the matching ID is returned in the results:
+-----------------+ | student_name | +-----------------+ | Franklin Pierce | +-----------------+
When you use the
WHERE clause, you must include an expression that filters the query results. In this case, the expression is very simple. Given that
id_students is the primary key for this table, this query is sure to return only one row. You can use other comparison operators as well, like the
!= operators. It's also possible to use Boolean operators to create compound expressions. For example, you can retrieve all of the students who pay more than $10,000 per year in tuition and who are classified as freshmen using the following query:
SELECT student_name FROM students WHERE tuition > 10000 AND classification = 'freshman'
Following are the results of this query:
+--------------+ | student_name | +--------------+ | James Polk | +--------------+
There are also several other functions you can use in the
WHERE clause that enable you to write more powerful queries. The
LIKE function allows you to search for fields containing a particular string using a regular expression like syntax. The
BETWEEN function allows you to search for values between the two you specify, and
IN allows you to test whether a value is a member of a set you specify.
Because the goal in this tutorial is ultimately to learn how to use XML with databases, I won't go into any more detail on these query functions, but feel free to do some additional SQL learning online at
http://www.w3schools.com/sql/default.asp, or pick up a book on SQL. Fortunately, you don't have to be a SQL guru to get the benefits of this lesson.
INSERT statement is used to insert records into a table. The syntax is simple, especially if you plan on populating every column in a table. To insert a record into
majors, use the following statement:
INSERT INTO majors VALUES (115, 50, 'Math', 'English')
The values in the list correspond to the
minor columns respectively. If you only want to specify values for a subset of the columns in the table, you must specify the names of the columns as well, as in the following:
INSERT INTO students (id_students, student_name) VALUES (50, 'Milton James')
When you create tables, you can specify whether values are required in certain fields, and you can also specify default values for fields. For example, the
classification column might default to
freshman because most new student records being inserted will be for newly enrolled students, who are classified as freshmen.
When you want to modify one or more records in a table, the
UPDATE statement is used. Here's an example:
UPDATE students SET classification = 'senior'
The previous SQL statement will work, but I bet you can figure out what's wrong with it. Nowhere is it specified which records to update. If you don't tell it which records to update, it just assumes that you want to update all of the records in the table, thus the previous query would turn all of the students into seniors. That's probably not what you have in mind. Fortunately, the
UPDATE statement supports the
WHERE clause, just like the
UPDATE students SET classification = 'senior' WHERE id_students = 1
That's more like it. This statement updates the classification of only one student. You can also update multiple columns with one query, as in the following:
UPDATE students SET classification = 'freshman', tuition = 7500 WHERE id_students = 5
As you can see from the example, you can supply a list of fields to update with your
UPDATE statement, and they will all be updated by the same query.
The last SQL statement I'll discuss is the
DELETE statement, which is similar to the
UPDATE statement. It accepts a
FROM clause, and optionally a
WHERE clause. If you leave out the
WHERE clause, it deletes all the records in the table. Here's an example:
DELETE FROM students WHERE id_students = 1
You now know just enough about SQL to get into trouble! Actually, your newfound SQL knowledge will come in handy a bit later in the lesson when you develop an application that carefully extracts data from a database and encodes it in XML. But first, you find out how to export an entire database table as XML.