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.

SQL

Create Database

CREATE DATABASE IF NOT EXISTS database_name

Drop Database

DROP DATABASE IF EXISTS database_name

Create Table

CREATE TABLE IF NOT EXISTS table_name (
  column_a Datatype Constraints,
  column_b Datatype Constraints,
);

Datatypes

**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

**Constraint**|**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

ALTER TABLE table_a
         ADD column_a DataType
ALTER COLUMN column_a DataType   
        DROP column_a
   RENAME TO table_b

Drop Table

DROP TABLE IF EXISTS table_name

Select Rows

   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 Distint Rows

SELECT DISTINCT column_name
           FROM table_name

Joins

**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

**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

**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

INSERT INTO table_name (column_a, column_b)
     VALUES ("value_1", "value_2")

Update Rows

UPDATE table_name
   SET column_a = "value_1"
       column_b = "value_2"
 WHERE Condition

Delete Rows

DELETE FROM table_name
      WHERE Condition

PDO / PHP

Open Connection

$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

**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

$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

$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

$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

$sql = "DELETE FROM users 
              WHERE id = :id";

$statement = $connection->prepare($sql);
$statement->bindValue(':id', 5, PDO::PARAM_INT);

$delete = $statement->execute();