MYSQL Cheat Sheet

Database Commands

  SHOW DATABASES;

  CREATE DATABASE databasename;

  USE databasename;

  DROP DATABASE databasename;

  DROP DATABASE IF EXISTS databasename;

Table Commands

  CREATE TABLE tablename (
    columnname1 DATATYPE,
    columnname2 DATATYPE,
    columnname3 DATATYPE,
    PRIMARY KEY(columnname)
  );

  SHOW TABLES;

  DESCRIBE tablename;

  DROP TABLE tablename;

  DROP TABLE IF EXISTS tablename;

MYSQL Design

Database > Table(s) > Data

  Table Columns:
    Limit: 4096 Columns/Table
    Each Column Identified By Name
    A Datatype Must Be Specified For Each Column

  Table Rows:
    Rows Are Added As New Data Is Stored
    Data Is Limited By Column Datatype
    Each Row Should Be Uniquely Identifiable

MYSQL Syntax

  Statements Must End With Semicolons (;)

  Reserve Words Can Be Capitalized
    SELECT, FROM, AND, LIKE, Etc.

Connecting/Disconnecting

  mysql -h host -u username -p
  quit

MYSQL Data Manipulation

  INSERT INTO tablename (
    columnname1,
    columnname2
  ) VALUES (
    'data',
    'data'
  );

  SELECT * FROM tablename;

  SELECT * FROM tablename
    WHERE columnname LIKE 'data';

  SELECT columnname1,
    columnname2 FROM tablename;

  UPDATE tablename
    SET columnname = 'data'
    WHERE columnname = 'data';

  DELETE FROM tablename
    WHERE columnname = 'data';

Datatypes

  TINYINT
  MALLINT
  MEDIUMINT
  INT
  BIGINT
  CHAR()
  VARCHAR()
  TINYTEXT
  TEXT
  MEDIUMTEXT
  LONGTEXT
  DATETIME
  DATE
  TIMESTAMP
  TIME
  YEAR