SQL and PDO Operations Reference
Structured Query Language, or SQL, is a widely used language the allows users to query and manage data in a database. Databases such as MySQL, MariaDB, SQLite, PostgreSQL, Oracle, and Microsoft SQL Server are all based on the SQL standard, with some slight variations. This resource uses the MySQL flavor of SQL.
I've created an overview resource to quickly be able to reference the appropriate syntax for the most popular SQL commands, and code to use the PDO class in PHP to securely connect to and work with a database.
To see PHP and MySQL in action, view the Creating a Simple Database Application from Scratch tutorial - Part One: Create and Read and Part Two: Update and Delete.
The logo in this article is of Sequel Pro, an awesome free MySQL GUI for Mac.
Contents
- Create Database
- Drop Database
- Create Table
- Datatypes
- Constraints
- Alter Table
- Drop Table
- Select Rows
- Select Distinct Rows
- Joins
- Aggregate Functions
- Conditions
- Insert Rows
- Update Rows
- Delete Rows
- PDO: Open Connection
- PDO Datatypes
- PDO: Select Rows
- PDO: Insert Row
- PDO: Update Row
- PDO: Delete Row
SQL
Common SQL syntax and statements.
Create Database
Create a new database.
CREATE DATABASE IF NOT EXISTS database_name
Drop Database
Delete an existing database.
DROP DATABASE IF EXISTS database_name
Create Table
Creates a new table with corresponding structure. The structure schema consists of a comma separated list of column name, followed by the datatype, followed by any constraints (optional) and a default value (optional).
CREATE TABLE IF NOT EXISTS table_name (
column_a Datatype Constraints DEFAULT 'value_1',
column_b Datatype Constraints
);
Create Table Example
CREATE TABLE IF NOT EXISTS users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
location VARCHAR(50) DEFAULT 'Chicago'
);
The charts below identify some of the most widely used datatypes and constraints in SQL.
Datatypes
The type of value a column can hold.
Datatype | Description |
---|---|
INT(n) |
Integer values |
FLOAT(n, d) |
Decimal values |
VARCHAR(n) |
String with max number of characters |
TEXT |
String with without set limit (max value of 65,535) |
DATE('YYYY-MM-DD') |
Year, month, and day |
DATETIME('YYYY-MM-DD HH:MI:SS') |
Year, month, day, hour, minute, and second |
TIMESTAMP('YYYY-MM-DD HH:MI:SS') |
Datetime corresponding to UNIX epoch time |
Constraints
Rules applied to a column.
Constrant | Description |
---|---|
PRIMARY KEY |
Unique identifier |
AUTO_INCREMENT |
Integer value is automatically added and incremented |
UNIQUE |
Value must be unique |
NOT NULL |
Value cannot be NULL |
DEFAULT |
Initialized with default value |
Alter Table
Add, modify, rename, or drop a column. Rename a table.
ALTER TABLE table_a
ADD column_a Datatype Constraints
ALTER COLUMN column_a Datatype Constraints
DROP column_a
RENAME TO table_b
Drop Table
Delete an existing table.
DROP TABLE IF EXISTS table_name
Select Rows
Select data from a database. Only SELECT
and FROM
are mandatory; the rest of the fields are optional. The order of a SELECT
statement is as follows:
SELECT
- select*
(all), specific columns, or aggregate functions.AS
- assign an alias to a column nameFROM
- table name to pull data fromJOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN ... ON
- combine data from tables by a common keyWHERE ... AND, OR, NOT
- filter data by conditionsGROUP BY
- group a result set by column.HAVING
- filter groups by conditions.ORDER BY
- sort a result set by column in ascending or descending orderLIMIT
- limit number of resultsOFFSET
- offset the results
SELECT *, column_a, column_b, AggregateFunction(column_a)
AS Alias
FROM table_a
JOIN table_b
ON table_a.column_a = table_b.column_a
WHERE Condition
AND Condition
OR Condition
NOT Condition
GROUP BY column_a
HAVING Condition
ORDER BY column_a
ASC
DESC
LIMIT Count
OFFSET Count
Select Rows Example
SELECT username,
AVG(age) AS average_age
FROM users
JOIN memberships
ON users.id = memberships.user_id
WHERE join_date >= '01-01-2010'
AND level = 'Paid'
GROUP BY average_age
HAVING average_age > 21
ORDER BY join_date DESC
LIMIT 100
OFFSET 1
Select Distinct Rows
Filter out duplicates to select unique results.
SELECT DISTINCT column_name
FROM table_name
Joins
Combine data from multiple tables based on a common column.
Join | Description |
---|---|
(INNER) JOIN |
Returns only matches from both tables |
LEFT JOIN |
Returns all entries from left table, and matches from right table |
RIGHT JOIN |
Returns all entries from right table, and matches from left table |
FULL JOIN |
Returns all entries from both tables |
Aggregate Functions
Apply a function to a column.
Function | Description |
---|---|
COUNT(column) |
Counts number of rows |
SUM(column) |
Adds all values |
MIN(column) |
Find the smallest value |
MAX(column) |
Find the largest value |
AVG(column) |
Find the average value |
Conditions
Filter based on specified conditions with these operators.
Operator | Condition |
---|---|
= , != |
Equal, not equal |
< , > |
Less than, greater than |
<= , >= |
Less/greater than or equal to |
BETWEEN ... AND ... |
Within range of two values |
NOT BETWEEN ... AND ... |
Not within range of two values |
IN (...) |
Exists in list |
NOT IN (...) |
Does not exist in list |
LIKE |
Case insensitive equality comparison |
NOT LIKE |
Case insensitive inequality comparison |
% |
Matches a sequence of characters |
_ |
Matches a single character |
IS NULL |
Value is null |
IS NOT NULL |
Value is not null |
ANY (...) |
If any values meet condition |
ALL |
If all values meet condition |
EXISTS |
If one or more records exist |
Insert Rows
Add new rows into a table.
INSERT INTO table_name (column_a, column_b)
VALUES ("value_1", "value_2")
Update Rows
Modify existing rows in a table.
UPDATE table_name
SET column_a = "value_1"
column_b = "value_2"
WHERE Condition
Delete Rows
Delete existing rows from a table.
DELETE FROM table_name
WHERE Condition
PDO
Syntax for opening a connecting and running insert, select, update, and delete commands.
Open Connection
Set connection details.
$host = 'localhost';
$username = 'root';
$password = 'root';
$dbname = 'pdo';
$dsn = "mysql:host=$host;dbname=$dbname";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false
];
$connection = new PDO($dsn, $username, $password, $options);
Datatypes
Set datatypes for properly prepared statements.
Datatype | Description |
---|---|
PDO::PARAM_BOOL |
Represents a boolean data type |
PDO::PARAM_NULL |
Represents the SQL NULL data type |
PDO::PARAM_INT |
Represents the SQL INTEGER data type |
PDO::PARAM_STR |
Represents the SQL CHAR, VARCHAR, or other string data type. |
Select Rows
Select rows with optional binded parameters.
$sql = "SELECT *
FROM users
WHERE location = :location";
$location = 'Chicago';
$statement = $connection->prepare($sql);
$statement->bindParam(':location', $location, PDO::PARAM_STR);
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['location'];
}
Insert Row
Insert rows with binded values.
$sql = "INSERT INTO users (username, email)
VALUES (:username, :email)";
$username = 'Tania';
$email = 'tania@example.com';
$statement = $connection->$prepare($sql);
$statement->bindValue(':username', $username, PDO::PARAM_STR);
$statement->bindValue(':email', $email, PDO::PARAM_STR);
$insert = $statement->execute();
Update Row
Update rows with an associated array of data.
$user = [
'username' => 'Tania',
'email' => 'tania@example.com',
'location' => 'Chicago',
];
$sql = "UPDATE users
SET username = :username,
email = :email,
location = :location,
WHERE id = :id";
$statement = $connection->prepare($sql);
$statement->execute($user);
Delete Row
Delete existing rows.
$sql = "DELETE FROM users
WHERE id = :id";
$statement = $connection->prepare($sql);
$statement->bindValue(':id', 5, PDO::PARAM_INT);
$delete = $statement->execute();
Conclusion
Hopefully this helps you out if you need a quick refresher on the order of a SELECT
, or how to work with PDO. Enjoy!
Comments