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:
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.
Browse tab, one can discover that we are dealing with a rather simple, single-table database with a handful of sample data rows.
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
titlefrom all entries, ordered by
SELECT `id`, `title` FROM posts ORDER BY title DESC
titlefrom entries whose
idequals to 3
SELECT `title` FROM posts WHERE id = 3
contentfrom the first two available entries
SELECT `title`, `content` FROM posts LIMIT 2
As a database table becomes more complex, one can combine
LIMIT to further tailor the result set.
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:
INSERT INTO posts (`title`, `content`) VALUES ('Hello world', 'Testing')
The table will automatically generate an
id value as per database configuration.
The UPDATE statement should be used with caution, as this command has an ability to modify an existing row irreversibly.
id5 with the new
UPDATE posts SET `title` = 'Quick brown fox' WHERE `id` = 5
It is especially important for one to review the
SET clauses carefully, as one may inadvertently provide wrong values.
The DELETE statement is a destructive command, and will irreparably damage a database table if used improperly.
DELETE FROM posts WHERE `id` = 5
DELETE FROM posts WHERE `content` = 'Testing'
Launching this command with no
WHERE clause may cause the database to delete all rows in a table.