SQL: Fetching & Parsing with SELECT

The SELECT statement is used in SQL to select data from the database. SQL stands for structured query language. It is the language used for working with a relational database.

SQL commands can be split into two groups. One is for working with the data within the database (called the The Data Manipulation Language, or DML). The other works designing the tables, as well as defining the relationship among them (The Data Definition Language, or DDL).

All of these commands can be used from the database command line prompt, once you've gained admittance to the Relational Database Management System (that is the MySQL software itself, or whatever RDMS you are using).

SELECT Falls under DML. It is the basic command for pulling data from the database, either directly from the command line, or as part of a Java or PHP connector. The basic format for SELECT is:

SELECT [column(s)] FROM [table]

(Note for all these blog pages, you fill your own values in the brackets “[ ]“). So, if you wanted to see all the info in the database "test," you would write:

SELECT * from test;

("*" is a wildcard operator. MySQL requires a ";" at the end of each statement, not shown here. Multiple columns can be selected to show, separated by a comma.) If you just wanted to see one column of data, say the column of numbers called "measure" you would write:

SELECT measure FROM test;

So SELECT is pretty easy, yes? But it is also rather coarse-grained, returning a tree when what you want is a branch. You need to do some more whittlin'! You build a more precise query from SELECT using additional qualifiers. The main way of doing this is through the WHERE keyword, which come after the FROM statement that specifies the table. To further qualify you can also append, in various mixes, the BETWEEN, LIKE, IN, AND or OR keywords, along with selected values to filter by. Finally, you can order the results by ORDER BY and filter them by DISTINCT, niether of which require WHERE, but could be used in conjunction with WHERE. What follows are the details:

With WHERE you can pull only those records from a table that meet some "specific criteria," as the W3C puts it. WHERE is part of a SELECT statement.

SELECT [column] FROM [table] WHERE [column]=[value you seek]

i.e.,

SELECT measure FROM test WHERE measure=0;

In this example, I used the "=" But other conditionals include...


<>  	Not equal
> 	        Greater than
< 	        Less than
>= 	        Greater than or equal
<= 	        Less than or equal

Now, for some other keywords:

BETWEEN: BETWEEN returns a range of data specified between two values. The format for this is:

SELECT [column(s)] FROM [table] WHERE [column] 
BETWEEN [lower value] AND [upper value]

The values can be numbers, date or even text! Text is ranked, ascending or descending, by alphabetical order. Here is an example:

SELECT * FROM Bike WHERE Date 
BETWEEN 20090501 AND 20090515

...Will return all the data in all the columns in the table Bike that have a date between May 1 and May 15 2009.

IN: IN specifies specific entries. When you are looking for values within known entries, then IN is what you'd use.

SELECT [column] FROM [table] WHERE [column] IN ([value(s)])

Commas separate multiple values in the IN set. And, as always if the values are text, they should be in single quotes. Example:

SELECT * FROM Bike WHERE RideTime IN [30, 60]

..Returns all the rows in the Bike table where the RideTime column equals 30 or 60.

LIKE and NOT LIKE: LIKE is a pattern-matcher. You can structure a query with the elements that you know are in a value that you are looking for, with wild card elements both before and after the known-nugget of info. The format:

SELECT [column(s)] FROM [table] WHERE [column] LIKE [pattern]

For these operations the percentage sign, %, is the wildcard operator. In a hypothetical database, "Turkey%" will return "TurkeyShoot" and "TurkeyBreast" and "%Turkey" will return "unTurkey" and "%Turkey%" will return all three i.e. NOT LIKE is the same but returns all the results that DON'T match the criteria you select.

SELECT * FROM BirdWords WHERE Fowl LIKE '%Turkey%'

NOTE: Single quotes are used also for fetching numerical responses, dig?

DISTINCT: DISTINCT is actually a precursor to WHERE--It comes before WHERE in the SQL statement and, in fact, can be used without WHERE. It picks out all of the original values in a column, eliminating any duplicates.

SELECT DISTINCT [column] FROM [Table]

AND/OR An AND or OR statement may be placed after WHERE for further distinction:

SELECT [column(s)] FROM table WHERE [column]=[value1] AND [column]=[value2]

SELECT [column(s)] FROM table WHERE [column]=[value1] OR [column]=[value2]

SELECT [column(s)] FROM [table] WHERE [column]=[value1] AND ([column]=[value2] OR [column]=[value3])

Note, operators other than the "=" could be used. See above. In the final example AND and OR can be combined, with a parenthesis to clarify order of evaluation with the machine. Play around with these for greater nuances, i.e.

SELECT * from Run WHERE Time=60 OR Time=50 AND (Date=20090101 OR Date=20090201);

...Will return all those entries with a date of Jan 1 2009 or Feb 1 2009 in which the Run Time was 50 or 60.

ORDER BY The ORDER BY specifies how the results of a query should be returned. They results can be returned either in a numerically ascending (or alphabetical), or descending (reverse alphabetical) for whatever column you choose. Default is ascending. Again, ORDER BY can be done without WHERE. It is placed at the end of the SQL statement:

SELECT [column(s)] FROM [table] ORDER BY [column] [ASC or DESC]

Example:

SELECT * FROM Run ORDER BY Time DESC

...Will return all the entries from the Run table ranked by time, longest time first.

Taken from the W3C School's tutorial on SQL, as well as from SQLCourse, a helpful site I found on the Web.

MySQL

Back