|
|
To extract data from a database using SQL, you use a SELECT statement. All SELECT statements start in the same way, using the SELECT keyword. The rest of the statement varies but generally includes the names of the columns in which you're interested and the name of the table from which you want to extract data. The following sections focus on how to retrieve data for every row of every column of a table and how to limit your queries to a specific column (or columns).
On the rare occasion that you need to obtain all data from all columns in the table, you can achieve it using the following syntax:
SELECT * FROM Table;
Here, SELECT and FROM are SQL keywords, * is a wildcard that means "all columns," and Table is the name of the table that contains the data you want.
| Caution |
Some RDBMSs will add a semicolon to the end of your SQL automatically if you don't include it. In some cases, if you're executing SQL statements from an application through a data access technology such as ActiveX Data Objects (ADO) or Open Database Connectivity (ODBC), the driver may add a semicolon automatically so an error would be caused if you add one manually. |
So, to retrieve every row of data from every column of a table called Customers, you could use the following SQL query:
SELECT * FROM Customers;
This statement would work when executed against any database that contained a table called Customers. It's a good idea to try out some simple SELECT * queries against the InstantUniversity database. For example, try executing the following query:
SELECT * FROM Student;
You should see results that look something like this:
StudentID Name ----------- ---------------- 1 John Jones 2 Gary Burton 3 Emily Scarlett 4 Bruce Lee ... ...
Notice that the column header given to each column in this result set is simply the name of the column as it exists in the database table. The columns are returned in the order they were created in the database.
It's important to note that although this simple SELECT *... syntax is easy to use, it's rarely the best way to go about things. When you use the * wildcard, you actually end up putting far more of a strain on an RDBMS than using one of the methods examined later in this chapter. This is because the RDBMS must examine the table itself to find out how many columns there are and what they're called. Moreover, you can't predict in what order the columns will be returned. As a general rule of thumb, you should only use the * syntax given here for testing.
You may have noted that, so far, you haven't specified a particular database to use in these queries. This implies that you're executing your statements against, for example, the Customer table in the database (or schema in the case of Oracle) to which you're directly connected.
SQL Server and Access also allow you to specify the database to find the table in by prefixing the database name followed by two dots to the table name:
SELECT * FROM pubs..authors; -- SQL Server
With Access, the database name will be the path and filename of the .mdb file:
SELECT * FROM C:\NWind.mdb..Employees;
MySQL has similar functionality but uses only one dot instead of two:
SELECT * FROM InstantUniversity.Student;
In SQL Server, Oracle, and DB2, if you want to access a table in a different schema from the one used to connect to the database, you can prefix the schema name to the table name using the syntax Schema.Table. For example:
SELECT * FROM scott.EMP; SELECT * FROM db2admin.Employee;
This will, of course, only work if you're currently connected under a user account that has access rights to this table (for example, if you're connected as a database administrator). Chapter 11, "Users and Security," covers security in more detail.
Occasionally, because of poor initial data design, you may find that you need to access a table or column that contains spaces. If such a situation occurs, then some RDBMSs require you to enclose the names in either square brackets or quotes. In SQL Server and Access, you use square brackets; in Oracle and DB2 (and in MySQL running in ANSI mode), you use double quotes. For example:
SELECT * FROM [Valued Customers]; -- SQL Server or Access
Or, for example:
SELECT * FROM "Valued Customers"; -- DB2 or Oracle
Be aware, though, that using the double-quote syntax of Oracle and DB2 means that the table or column name will be treated as case sensitive. Therefore, the following query:
SELECT * FROM "Valued Customers";
is a different query from this:
SELECT * FROM "valued customers";
You may also need to use the double-quote or square-bracket syntax if a table or column name is identical to a SQL keyword or a word that your database vendor has "reserved." Again, this is bad design and should be avoided whenever possible, but you may be caught out by future expansions to SQL or by expansions in your database vendor's list of reserved words.
| Note |
Reserved words have special meaning to the database and can't be redefined. Therefore, they can't be used to name database objects. For example, Oracle doesn't permit the name of the pseudo-column ROWID to be used as a column or table name. |
Say, for example, that you are accessing a table called Language and then, at a later date, your vendor adds Language to its list of reserved words. You could still use the table in your queries by using the following:
SELECT * FROM [Language]; -- SQL Server or Access
or using the following:
SELECT * FROM "LANGUAGE"; -- DB2 or Oracle
Note the use of capital letters for the table name in the second example. If the Language table was created without using the double-quote syntax, both DB2 and Oracle would create it as LANGUAGE, so chances are that the previous query would succeed but that something such as SELECT * FROM "Language"; would fail. Conversely, if the table was created as "Language" (with quotes), attempting to access it using the statement SELECT * FROM "LANGUAGE"; would fail.
With a simple modification to the SELECT * query used previously, you can obtain all the values from a single-named column. The syntax is as follows:
SELECT Column FROM Table;
Here, Column is the name of the column you're interested in from Table. You can write this column name in several different ways. The simplest is to include just the name as follows:
SELECT CustomerName FROM Customers;
To see this working in practice, try executing the following command against the InstantUniversity database:
SELECT Name FROM Professor;
The results are as follows:
Name ---------------- Prof. Dawson Prof. Williams Prof. Ashby Prof. Patel Prof. Jones Prof. Hwa
You can also enclose the column name within delimiters. The same arguments apply to the use of square-bracket or double-quote delimiters with column names that apply to table names:
SELECT [Language] FROM Countries; -- SQL Server or Access SELECT "LANGUAGE" FROM Countries; -- Oracle or DB2
Sometimes it's useful to include information as to what table the column can be found in, as follows:
SELECT Customers.CustomerName FROM Customers;
Although this is a bit pointless in this example because only the Customers table is being queried, it can be invaluable in more complex scenarios. In later chapters, you'll see how you can extract data from multiple tables simultaneously, and in this case the syntax shown previously serves two purposes. First, it makes it easier for you to see at a glance what information comes from which table. Second, it allows you to differentiate between columns in different tables that have the same name.
Getting data from more than one column is just as easy. Rather than specifying simply a column name, you specify multiple column names separated by commas:
Here ColumnA, ColumnB, and ColumnC are the names of three columns you want from Table.
For example, the following query gets the ID for each student and the ID and comments for each exam they took from the InstantUniversity database:
SELECT StudentID, ExamID, Comments FROM StudentExam;
The first four records in the result set are as follows:
StudentID ExamID Comments
--------- -------- ------------------------------------------
1 1 Satisfactory
1 2 Good result
2 3 Scraped through
2 5 Failed, and will need to retake this one
later...
... ... ...
Note that the columns are returned in the order you specify them—although in practice this may not be an issue because when you manipulate data such as this in your own application code, you can ignore such structure and impose your own.
|
|