SQL Summary

Russell Bateman
last update:

Legend

table   - name of a table.
column   - name of a column.
index   - name of an index.
etc.

Command syntax

CREATE TABLE Create new table CREATE TABLE table ( column1 datatype, ... columnN datatype )
ALTER TABLE Modify table ALTER TABLE table ADD column datatype
ALTER TABLE table DROP column
DROP TABLE Erase table DROP TABLE table
SELECT Query data in table SELECT column1 [ , ...columnN ] FROM table WHERE condition
Conditional operators =   >   <   =>   =<   <>   LIKE pattern
Pattern matching 'A%' strings that start with A
'%a' strings that end with a
'%b%' strings that contain b
INSERT INTO Insert new row INSERT INTO table ( column1 [ , ...columnN ] ) VALUES ( value1 [ , ...valueN ] )
UPDATE Modify an existing row UPDATE table SET column1 = value1 [ , columnN = valueN ] WHERE condition
DELETE Remove an existing row DELETE FROM table WHERE condition
JOIN Create temporary table of pertinent data from others* SELECT column1 [ , ...columnN ] FROM table1
[INNER|LEFT|RIGHT|FULL ] JOIN
ON table1 . column CONDITIONAL-OPERATOR table2 . column
INNER (Default) Returns all rows from both tables where there is at least one match in both.
OUTER
LEFT Returns all rows from first ("left") table plus those rows that match in the second ("right") table.
RIGHT Returns all rows from second ("right") table plus those rows that match in the first ("left") table.
FULL Return all rows where there is any match in either table.
UNION Select data from two tables with columns of identical datatype. SELECT column1 [ , ...columnN ] FROM table1
UNION
SELECT column1 [ , ...columnN ] FROM table2
CREATE INDEX Create index on which to search a table CREATE INDEX index ON table ( column1 [ , columnN ] )
DROP INDEX Delete an index—vendor-specific DROP INDEX index
ALTER TABLE table DROP INDEX index (MySQL)

Data types

Vary from vendor to vendor.

CHAR[ACTER] Fixed-length n string.
VARCHAR(n) Variable-length string, maximum length n.
BINARY Fixed-length n, binary string.
BOOLEAN TRUE or FALSE.
VARBINARY(n) Variable-length, binary string, maximum length n.
INTEGER(p) Integer numeric, precision p.
SMALLINT Integer numeric, precision 5.
INTEGER Integer numeric, precision 10.
BIGINT Integer numeric, precision 19.
DECIMAL(p,s) Exact numerica, precision p.
NUMERIC(p,s) Exact numerica, precision p, identical to DECIMAL.
FLOAT(p) Approximate numeric, mantissa precision p, in base 10.
REAL Approximate numeric, mantissa precision 7.
FLOAT Approximate numeric, mantissa precision 16.
DOUBLE PRECISION Approximate numeric, mantissa precision 16.
DATE Year, month and day.
TIME Hours, minutes and seconds.
TIMESTAMP Year, month, day, hour, minute and seconds.
INTERVAL Integer fields representing period of time.
ARRAY Set-length, ordered collection.
MULTISET Variable-length, unordered collection.
XML XML data.

Links

Table joins

JOIN is a way of creating a new table from two others such that the result contains only (mostly) the data inside that you specifically want, as if it had existed all along. Below are some very useful, if a tiny bit misleading, Venn diagrams that cleverly illustrate LEFT, RIGHT, INNER and OUTER JOIN statements.

Batched inserts/updates example

package com.dzone.batchsql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Demonstrate batch update.
 *
 * id  name  code  department
 *
 * 1   Allen abc   Sales
 * 2   Max   102   Marketing
 * 3   Ward  55    Sales
 * 4   Sam   xyz   Marketing
 *
 * TODO: Integrate use of PreparedStatement into this example.
 */
public class BatchSql1
{
  private static final String CONNECTION_URL = "jdbc:mysql://localhost/personnel?user=root&;password=test123";

  Connection connection = null;

  public BatchSql1()
  {
    try
    {
      connection = DriverManager.getConnection( CONNECTION_URL );
    }
    catch( SQLException e )
    {
      System.out.println( "SQLException: " + e.getMessage() );
      System.out.println( "SQLState:     " + e.getSQLState() );
      System.out.println( "VendorError:  " + e.getErrorCode() );
    }
  }

  private final String[] newEmployeesString =
  {
    "INSERT INTO employee { id, name, code, department } values ( ‘1’, ‘Allen’, ‘abc’, ‘Sales’ )",
    "INSERT INTO employee { id, name, code, department } values ( ‘2’, ‘Max’,   ‘102’, ‘Marketing’ )",
    "INSERT INTO employee { id, name, code, department } values ( ‘3’, ‘Ward’,  ‘xyz’, ‘Sales’ )",
    "INSERT INTO employee { id, name, code, department } values ( ‘4’, ‘Sam’,   ‘55’,  ‘Marketing’ )",
  };

  /**
   * Hits the database for each insert statement.
   * @throws SQLException
   */
  public void doItTheSlowWay() throws SQLException
  {
    Statement statement  = connection.createStatement();

    for( String insert : newEmployeesString )
      statement.execute( insert );

    statement.close();
    connection.close();
  }

  private final Employee[] newEmployees =
  {
    new Employee( 1, "Allen", "abc", "Sales" ),
    new Employee( 2, "Max",   "102", "Marketing" ),
    new Employee( 3, "Ward",  "xyz", "Sales" ),
    new Employee( 4, "Sam",   "55",  "Marketing" )
  };

  /**
   * Create a batch of inserts, then insert all together at one time. Note
   * that batch updates can include or consist of updates or deletes too.
   * @throws SQLException
   */
  public void doItTheFasterWay() throws SQLException
  {
    PreparedStatement statement = connection.prepareStatement( INSERT );

    for( Employee employee : newEmployees )
    {
      StringBuilder query = new StringBuilder();

      query.append( "INSERT INTO employee( id, name, code, department ) " );
      query.append( "VALUES (' " );
      query.append( employee.getId()         ).append( "', '" );
      query.append( employee.getName()       ).append( "', '" );
      query.append( employee.getCode()       ).append( "', '" );
      query.append( employee.getDepartment() );
      query.append( "')" );
      statement.addBatch( query.toString() );
    }

    statement.executeBatch();

    statement.close();
    connection.close();
  }

  private final String INSERT = "INSERT INTO employee( id, name, code, department ) VALUES( ?, ?, ?, ? )";

  public void doItFastAndThePreparedWay() throws SQLException
  {
    PreparedStatement statement = connection.prepareStatement( INSERT );

    for( Employee employee : newEmployees )
    {
      statement.setInt   ( 1, employee.getId() );
      statement.setString( 2, employee.getName() );
      statement.setString( 3, employee.getCode() );
      statement.setString( 4, employee.getDepartment() );
      statement.addBatch();
    }

    statement.executeBatch();

    statement.close();
    connection.close();
  }
}
// vim: set tabstop=2 shiftwidth=2 expandtab: