Introduction To SQL
by Kevin Waterson
Contents
- Abstract
- Data Storage
- CREATE A Table
- INSERT Data
- INSERT Multiple
- Data Storage
- SELECT Data
- SELECT Specific Data
- UPDATE Records
- DELETE Records
- DELETE A Table
- Ordering Data
- Summary
Abstract
SQL is the acronym for Structured Query Language. The American National Standards Institute (ANSI) defines SQL as "standard languate used to communicate with a relational database". SQL is pronounced "ESS KEW ELL". Anybody trying to call it SEQUEL should be ridiculed publicly.
There are many relational databases available and most are used for websites. These include MySQL, PostGreSQL, MSSQL, Oracle, Sybase, SQLite and others.
This tutorial endeavours to introduce some basic concepts of SQL in a bid to improve web applications that use a database as the backend. This tutorial was compiled using MySQL and like all databases, has queries specific to this database.
Data Storage
Database information is stored in tables. These tables are comprised of records, or rows of data. The data in each row is stored in a field which is named for identification. Here an example table is shown which contains a table of cars
+--------+----------+-----------+-----------------+ | car_id | car_cost | car_price | car_description | +--------+----------+-----------+-----------------+ | 1 | 10000 | 12000 | Nice Car | | 2 | 9999 | 11000 | Fast Car | | 3 | 12000 | 15000 | Faster Car | | 4 | 14000 | 15999 | Really Fast Car | +--------+----------+-----------+-----------------+
The table contains 4 records or rows. Each column, or field, is named. The field names, or column names, are car_id, car_cost, car_price and car_description. Note that each of the fields is named in a descriptive manner. This may seem rather obvious at this time, but consider a second table named "dolls" that also has an id, cost, price, and description fields. This clash of names can become confusing when later JOINing tables or needing to discern exactly which column is being used.
CREATE A Table
Before any data can be stored in the database, a table needs to be created to hold it. To create a table using SQL the CREATE keyword is used. For the purposes of this tutorial, a small table named cars will be created.
CREATE TABLE IF NOT EXISTS cars ( car_id int(11) NOT NULL AUTO_INCREMENT, car_cost int(11) NOT NULL, car_price int(11) NOT NULL, car_description varchar(100) NOT NULL, PRIMARY KEY (car_id) );
Stepping through the table creation, four fields, or columns, have been created within the table. The car_id field is of type int(), which means any value placed in that field, must be a numeric value. The field is also marked as NOT NULL which specifies the field MUST have a value and cannot be empty. Finally the car_id is marked as AUTO_INCREMENT. The AUTO_INCREMENT is used by MySQL to keep a numeric index of all records in the table. This field should not be modified unless you know what you are doing.
The car_cost and car_price fields are also numeric field types and specified as NOT NULL but have no AUTO_INCREMENT. The car_description is described as a varible character field with a width of 100 characters.
Lastly, a PRIMARY KEY is defined, in this case it is the car_id field that is the PRIMARY KEY. The primary key is a special index that databases use interally to make lookups faster.
INSERT Data
With the table created, information can now be put into the database for storage and retrieval. The keyword to insert data into a database is INSERT. By naming each field and each value to be inserted, the database will store them for later retrieval.
INSERT INTO cars (car_cost, car_price, car_description) VALUES (10000, 12000, 'Nice Car');
Note thaat the car_id field was not specified. This is because MySQL will automatically create the value because it was specified as AUTO_INCREMENT.
INSERT Multiple
The above query works fine for inerting a record into the database table, but often it is required to insert more than a single record. Calling a seperate INSERT for each record would put great strain on a database in a multi user environment and with a large number of INSERTs. IN this example, multiple inserts are accomplished with a single query.
INSERT INTO cars (car_cost, car_price, car_description) VALUES (9999, 11000, 'Fast Car'), (12000, 15000, 'Faster Car'), (14000, 15999, 'Really Fast Car');
Note that once again, no car_id field was specified, as the internal AUTO_INCREMENT functions to do this without any need from userland.
SELECT Data
To get information out of a database, the SQL SELECT command is used as shown here.
SELECT car_id, car_cost, car_price, car_description FROM cars;
This same query can be formatted to make easier reading and debugging in longer queries.
SELECT
car_id,
car_cost,
car_price,
car_description
FROM
cars;
SELECT Specific Data
In the above SQL queries, the SELECT statement was used to select all the data in cars table. This is good when all the datata is required, but more often than not, only a limited amount of data is reqired. To specify exactly which data is selected, SQL comes with a number of conditional clauses that can be used with the WHERE keyword.
In the following SQL query, only the record with the car_id of 3 is selected.
SELECT
car_id,
car_cost,
car_price,
car_description
FROM
cars
WHERE
car_id=3;
The above query returns only one row, that is the record containing the data with the car_id of 3.
+--------+----------+-----------+-----------------+ | car_id | car_cost | car_price | car_description | +--------+----------+-----------+-----------------+ | 3 | 12000 | 15000 | Faster Car | +--------+----------+-----------+-----------------+
The WHERE clause in this query used the equals (= condition to SELECT only the record with the car_id equal to 3. The SQL language contains a sevaral of these conditions as shown in this list.
- = Equal to
- > Greater than
- < Less than
- >= Greater than or equal to
- <= Less than or equal to
- <> Not equal to
- LIKE Special cases
It is not too difficult to see what each of these conditional operators do. In the following example, all cars that cost more than $10,000.00.
SELECT
car_id,
car_cost,
car_price,
car_description
FROM
cars
WHERE
car_cost > 10000;
Now two records are returned as shown here
+--------+----------+-----------+-----------------+ | car_id | car_cost | car_price | car_description | +--------+----------+-----------+-----------------+ | 3 | 12000 | 15000 | Faster Car | | 4 | 14000 | 15999 | Really Fast Car | +--------+----------+-----------+-----------------+
As mentioned earlier, these conditions, with the excption of equals, relate to numeric values. The LIKE condition however, is a special operator and works on strings. In the following query, the SQL will get only cars that are described as Fast.
SELECT
car_id,
car_cost,
car_price,
car_description
FROM
cars
WHERE
car_description LIKE '%Fast%';
The results return only those records with the string 'Fast' in them. the % character works as a wild card making the pattern matching abilities similar to a regular expression. In this instance, the wildcards have been used before and after the word to match so that the records returned show any use of the word Fast in the car_description field.
Of course, the wildcard could have been used only before or after the word to match. Lets see it in action.
SELECT
car_id,
car_cost,
car_price,
car_description
FROM
cars
WHERE
car_description LIKE 'Fast%';
Now only records where the car_desctiption field begins with the word Fast are show
+--------+----------+-----------+-----------------+ | car_id | car_cost | car_price | car_description | +--------+----------+-----------+-----------------+ | 2 | 9999 | 11000 | Fast Car | | 3 | 12000 | 15000 | Faster Car | +--------+----------+-----------+-----------------+
UPDATE Recoreds
Not all records stored in the database table will be valid all the time. Data needs to be kept up to date. To change or update the data the UPDATE keyword is used. In this example, the record with a a car_id of 3 is updated to a new value of 14500.
UPDATE cars SET car_price = 14500 WHERE car_id = 3;
A quick SELECT shows the updated result.
SELECT car_price FROM cars WHERE car_id = 3;
In this SELECT only a single field has been selected. The SELECT statement can be used to select any amount of fields from a table, and in any order. The results from the above SELECT query produce this result.
+-----------+ | car_price | +-----------+ | 14500 | +-----------+
When UPDATE is used, any of the conditional operators can be used as above with a SELECT. It is important to specify exaclty which record, or records, are to be updated. If no WHERE clause is specified, ALL fields in the table will be updated. This may be what is required, however, care should be taken.
DELETE Records
Probably the easiest of queries is the DELETE statement. The DELETE keyword is used and once again, it is important specify exaclty which records are to be deleted, as there is no un-delete function to help you out after a query is completed.
To DELETE the record with the car_id of 3:
DELETE A Table
To delete or remove a table the DROP TABLE command is used. There is no turning back with this so use caution.
There will be no prompting of any kind, the table will just cease to exist and all data will be gone forever.
Ordering Data
When using SELECT, the rows, or records, that are returned are in the order in which they were inserted. Basically, ordered by the primary key, which in this case is the car_id field. This behavior can be changed and making more ordering options available.
- ASC Ascending
- DESC Descending
The keywords for ordering are the ORDER BY clause. When used, the rows returned can be ordered by any field in ascending or descending order. Lets see it in action by ordering the results by the car_cost in descending order. This means from most expensive, to least expensive.
SELECT car_id, car_description, car_cost FROM cars ORDER BY car_cost DESC;
+--------+-----------------+----------+ | car_id | car_description | car_cost | +--------+-----------------+----------+ | 4 | Really Fast Car | 14000 | | 3 | Faster Car | 12000 | | 1 | Nice Car | 10000 | | 2 | Fast Car | 9999 | +--------+-----------------+----------+
It is quite clear to see the result is as requested. The car_cost field begins with the largest cost and follows down to the least. Had the query not specified that the ORDER was descending, the default value of ascending would have been used.
Summary
In this tutorial the basics of SQL have been covered. The ability to CREATE a table, INSERT data, SELECT data, UPDATE data, and ORDER the data results have been covered.
Stay tuned for the next installment of Advanced Database Use.