One term you can't go far into databases without encountering is SQL, which stands for Structured Query Language. SQL is the language used to retrieve, add, modify, and delete records in databases. Let's look at each of these features in turn.
By the Way
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_students | student_name | city | state | classification | tuition |
+-------------+-----------------+--------------------+-------+----------------+---------+
| 1 | Franklin Pierce | Hillsborough | NH | senior | 5000 |
| 2 | James Polk | Mecklenburg County | 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.
The 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 |
+------+-----------------+-------+
The 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 |
+--------------------+
Without 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.
The WHERE Clause
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 > or != 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.
By the Way
Because the goal in this hour 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.
Inserting Records
The 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 id_majors, id_students, major, and 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.
Updating Records
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 SELECT statement.
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.
Deleting Records
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.
Sty - Knowledge is Free
Read More..
By the Way
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_students | student_name | city | state | classification | tuition |
+-------------+-----------------+--------------------+-------+----------------+---------+
| 1 | Franklin Pierce | Hillsborough | NH | senior | 5000 |
| 2 | James Polk | Mecklenburg County | 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.
The 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 |
+------+-----------------+-------+
The 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 |
+--------------------+
Without 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.
The WHERE Clause
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 > or != 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.
By the Way
Because the goal in this hour 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.
Inserting Records
The 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 id_majors, id_students, major, and 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.
Updating Records
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 SELECT statement.
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.
Deleting Records
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.
Sty - Knowledge is Free
