If you’re a web designer or budding developer and know nothing about SQL (Structured Query Language), right now is as good time as any to play “catch up”. In this installment, I’ll begin with the raw basics of SQL to get you started.
SQL is the most commonly used language used to communicate with databases. Although there are slight differences in how they use SQL with their own proprietary extensions (which in truth, don’t change much to the rudimentary functionality), many database management systems employ it: MySQL, MS SQL Server, PostgreSQL, Oracle, and Sybase are just a few of the more popular examples.
At the core of SQL programming are statements. A statement is simply a command to do something with data in a database table. The first statement we’ll look at is the select statement. The name is self-explanatory; it allows you to select, or get, records from a table.
Here is the syntax of the select statement that gets data from “column_name” that resides in the “table_name” table:
SELECT column_name from table_name
Easy enough, isn’t it? Suppose you want more than one column selected. Just add extra columns like this:
SELECT column_name, column_name2, column_name3 from table_name
Or if you want to select ALL columns from a table, replace the column name(s) with an asterisk (*) that acts as a “wildcard”:
SELECT * from table_name
Where Cluases
If you need to select data from any of the table’s rows, use the where clause like this:
SELECT column_name2 from table_name
WHERE row_name = row_name2
The above example will select data from the column “column_name2” and the row “row_name2”. For the where clause, you can use any of the following conditional selections:
= Equal
> Greater than
< Less than
>= Greater than or equal to
< = Less than or equal to
<> Not equal to
LIKE It shows data that is “like” what you specify. The percentage sign (%) acts as a wildcard in this instance.
Let’s see the where clause in action some more. The next example will select the name and age of people off a list who are older than 25 years:
SELECT first_name, last_name, age from people_list
WHERE age > 25
Or if you want to pull a list of everyone who’s last name begins with “W”:
SELECT first_name, last_name from people_list
WHERE last_name LIKE 'W%'
Order By Clauses
The second clause we’ll look at in this lesson is order by. You must use this clause if you want records from a database returned in a particular order.
SELECT column_name2 from table_name
WHERE row_name = row_name2
ORDER BY column_name2
Distinct/From Clauses
Lastly, there are the distinct and from clauses. Let’s say that your previous people_list table had 25 individuals listed but they all had as their last name either “Smith” or “Jones”. If you use the next statement to select the distinct last_name column, only two records would be returned: Smith and Jones. Here’s the code:
SELECT DISTINCT last_name
FROM people_list
Coming soon: SQL Nuts and Bolts Tutorial Part Two!