As a user-friendly alternative to custom code solutions (and now a standard language) for communicating with databases, SQL offers a variety of functions that follow a strict set of rules. Some of the most frequently used commands include:
SELECT
INSERT
UPDATE
DELETE
All sample queries can be launched using the web-based MySQL client phpMyAdmin (ID: ocad / PW: csdm2n15). SQL
tab will open automatically upon opening the link.
Upon clicking Browse
tab, one can discover that we are dealing with a rather simple, single-table database with a handful of sample data rows.
SELECT
The SELECT statement is used to select one or more rows in the database. The selected rows are provided in a form of a result set, which can be used in turn by PHP to display in the browser.
SELECT * FROM posts
id
and title
from all entries, ordered by title
(descending order)SELECT `id`, `title` FROM posts ORDER BY title DESC
title
from entries whose id
equals to 3SELECT `title` FROM posts WHERE id = 3
title
and content
from the first two available entriesSELECT `title`, `content` FROM posts LIMIT 2
As a database table becomes more complex, one can combine ORDER BY
, WHERE
, and LIMIT
to further tailor the result set.
INSERT
The INSERT statement allows the user to push to the database table by identifying the table name, the names of affected columns, and the desired values for the aforementioned columns:
title
and content
INSERT INTO posts (`title`, `content`) VALUES ('Hello world', 'Testing')
The table will automatically generate an id
value as per database configuration.
UPDATE
The UPDATE statement should be used with caution, as this command has an ability to modify an existing row irreversibly.
id
5 with the new title
UPDATE posts SET `title` = 'Quick brown fox' WHERE `id` = 5
It is especially important for one to review the WHERE
and SET
clauses carefully, as one may inadvertently provide wrong values.
DELETE
The DELETE statement is a destructive command, and will irreparably damage a database table if used improperly.
id
5DELETE FROM posts WHERE `id` = 5
content
'Testing'DELETE FROM posts WHERE `content` = 'Testing'
Launching this command with no WHERE
clause may cause the database to delete all rows in a table.