Tania Rascia

Skip Navigation
Overview of SQL Commands and PDO Operations

Overview of SQL Commands and PDO Operations

 /  6 responses

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.

You can view the commands alone without explanations on GitHub through the below link.

View on GitHub

The logo in this article is of Sequel Pro, an awesome free MySQL GUI for Mac.

Contents

SQL

Common SQL syntax and statements.

Note: There are many accepted styles for formatting SQL. For consistency in these examples, I chose right-aligned keywords.

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.

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

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.

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 = '[email protected]';

$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'     => '[email protected]',
  '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!

View on GitHub

Note

Hi, my name is Tania Rascia. I've turned down every advertiser, sponsored post, and affiliate who has contacted me. I write free resources that have helped thousands of people successfully transition into a web development career.

My committment is to no bullshit, no sponsored posts, no ads, and no paywalls. If you enjoy my content, please consider supporting what I do.

Support my work

Write a response

Your email address will not be published.

All code will be displayed literally.

Discussion