ckportfolio.com - SQL: Basic Syntax

SQL: Basic Syntax

Introduction

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.

Preset Database

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 all columns from all entries

SELECT * FROM posts

Select id and title from all entries, ordered by title (descending order)

SELECT `id`, `title` FROM posts ORDER BY title DESC

Select title from entries whose id equals to 3

SELECT `title` FROM posts WHERE id = 3

Select title and content from the first two available entries

SELECT `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:

Insert a new post with 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.

Update row with 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.

Remove row with id 5

DELETE FROM posts WHERE `id` = 5

Remove row with 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.

Fin