PHP Tutorial :: SQL reference (I)

Reference of SQL

The basics of databases

Databases allow to store large amounts of information with a great level or organization while allowing a simultaneous access to the data from many users without risk of damaging the information. Databases also improve the security of the information from external attacks. All these advantages are given by the sofware that manages the database. While the design of any website and any required scripts are stored in files (such as .htm, .css, .js or .php), the core information (the data that the users search for or require, such as the dinamically displayed content or the secret credentials) should be stored in databases. Every data input coming from the users should be treated by databases and not by files, because of the forementioned reasons.

Databases are organized in tables, that in turn are organized in colums (also called fields) and rows. Tables would refer to wide concepts inside the database and inside each table, the columns would refer to the different characteristics of the concept, while each row would be a different instance of the concept. So, speaking clear, if for instance the database stores information about a restaurant, we could have one table for dishes, other one for drinks and another one for desserts. In the table for the dishes, we would have a different dish stored in each row and each column would refer to characteristics that are inherent to dishes, such as their price or their ingredients. Generally, each row in a table has assigned an unique identifier.

Reference of SQL

The basics of SQL

SQL (Structured Query Language) is a widespread language that is used to perform queries to the database software. PHP sends instructions to the database software via SQL queries. Basically, these queries are used to return data from or insert data to the database. Additional operations commonly used are modification or deleting of data. The built-in functions of the database software will return values indicating if the operations were successful or not.

When writing SQL statements, some syntax rules apply, as in any other language. SQL keywords are not case-sensitive, but in the source code examples they are often written in uppercase to distinguish them better from the associated language (in this case, PHP). The names for tables and columns are case-sensitive, so it should be a good practice to use only lower-case letters in them to avoid unnecessary hassle. Writing keywords in upper-case and names in lower-case should make the source code clearer and it is indeed a widespread practice. Every verbatim value that is used in a query will be case-sensitive as well, so searching for 'fried bean curd' will not return a result if the value stored is 'Fried Bean Curd'.

This basic reference guide for SQL will cover the fundamental commands: CREATE TABLE, DROP TABLE, INSERT INTO, UPDATE, DELETE, SELECT, WHERE, ORDER BY and LIMIT. Learning the very basics for managing a database with SQL is easy task, but of course, to become a good database manager you would have to learn the SQL language in depth. However, to make use of a database in a simple website, this should suffice. To create the structure of a database in the server after creating the database, you can use both SQL statements or manually with the software provided by the host provider.

Reference of SQL

Common data types

VARCHAR(lenght): Stores a string that can have a maximum length of the specified amount of characters (255 is the maximum allowed in MySQL)

INT: Stores an integer number

BLOB: Stores a string or binary data up to 64 KB

DECIMAL(total_digits, decimal_digits): Stores a float number with the specified number of total digits and decimal digits

DATETIME: Stores a date and an hour, in the format yyyy-mm-dd hh:mm:ss

Reference of SQL


= (equal than)

<> (not equal than)

> (greater than)

< (lesser than)

>= (greater or equal than)

<= (lesser or equal than)

AND (logical AND)

OR (logical OR)

( ) (grouping)

Reference of SQL

Common SQL queries - CREATE and DROP

Example: Create a table that has four fields, intended to store the different dishes available in a restaurant.

CREATE TABLE dishes (dish_id INT, dish_name VARCHAR(255), price DECIMAL(4,2), is_spicy INT)

Example: Delete the table dishes (permanently).


Reference of SQL

Common SQL queries - INSERT

The command INSERT adds a new row to an existing table.

Example: Insert a new row in the table dishes.

INSERT INTO dishes (dish_id, dish_name, price, is_spicy) VALUES (1, 'Braised Sea Cucumber', 6.50, 0)

Note that strings are delimited by single quotes. If the string itself contains single quotes, these have to be escaped with the backslash symbol (\). For example:

INSERT INTO dishes (dish_id, dish_name, price, is_spicy) VALUES (2, 'General Tso\'s Chicken', 6.75, 1)

As you see, we have to match the specified valued with the specified fields. We can as well omit some columns:

INSERT INTO dishes (dish_name, is_spicy) VALUES ('Salt Baked Scallops', 0)

We can as well omit the columns' list if we are inserting values for all of them:

INSERT INTO dishes VALUES (1, 'Braised Sea Cucumber', 6.50, 0)

Reference of SQL

Common SQL queries - UPDATE

The command UPDATE changes the data that exists in a table.

Example: Change the price to 5.50 in all rows of the table dishes

UPDATE dishes SET price = 5.50

Example: Double the price in all rows of the table dishes

UPDATE dishes SET price = price * 2

Example: Change to 1 the spicy status of the dish called Eggplant with Chili Sauce

UPDATE dishes SET is_spicy = 1 WHERE dish_name = 'Eggplant with Chili Sauce'

Example: Decrease by 1 the price of the dish called General Tso's Chicken

UPDATE dishes SET price = price - 1 WHERE dish_name = 'General Tso\'s Chicken'

Reference of SQL

Common SQL queries - DELETE

The command DELETE removes rows from a table.

Example: Delete all the rows of the table dishes (but not the table itself)


Example: Delete all the rows whose price is greater than 10.00

DELETE FROM dishes WHERE price > 10.00

Example: Delete all the rows in which dish_name is exactly "Walnut Bun"

DELETE FROM dishes WHERE dish_name = 'Walnut Bun'