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.

Constraints

Rules applied to a column.

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 name
• FROM - table name to pull data from
• JOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN ... ON - combine data from tables by a common key
• WHERE ... AND, OR, NOT - filter data by conditions
• GROUP BY - group a result set by column.
• HAVING - filter groups by conditions.
• ORDER BY - sort a result set by column in ascending or descending order
• LIMIT - limit number of results
• OFFSET - 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.

Aggregate Functions

Apply a function to a column.

Conditions

Filter based on specified conditions with these operators.

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.

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!