Mysql/CheatSheet

Databases

• List all

SHOW DATABASES;

• Create

CREATE DATABASE [IF NOT EXISTS] database_name;

• Use

USE database_name;

• Delete

DROP DATABASE [IF EXISTS] database_name;

• Storage Engines
  • MyISAM (optimized for compression and speed. portable. not transaction safe. used to be default)
  • InnoDB (optimized for speed. portable. default)
  • MERGE
  • MEMORY (HEAP)
  • ARCHIVE
  • CSV
  • FEDERATED

Tables

• list

SHOW TABLES;

• create

CREATE TABLE [IF NOT EXISTS] table_name(
  column1 datatype constraint,
  column2 datatype constraint
);

CREATE TABLE IF NOT EXISTS cars(
  vehicle_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  first_seen DATE,
  description TEXT
);

• constraints

NOT NULL
UNIQUE
PRIMARY KEY   (NOT NULL and UNIQUE)
FOREIGN KEY
CHECK
DEFAULT
INDEX

data types

TINYINT	        (A very small integer: 0/255, 1 byte)
SMALLINT	(A small integer: 0/65535, 2 bytes)
MEDIUMINT	(A medium-sized integer: 0/16777215, 3 bytes)
INT	        (A standard integer: 0/4294967295 4 bytes)
BIGINT	        (A large integer: 0/18446744073709551615 8 bytes)
DECIMAL(M,D)    (A fixed-point number: M=total digits(max=65), D=decimals(max=30))
DEC             (=DECIMAL)
FLOAT[(M,D)]    (A single-precision float (4 bytes)(M=digits, D=decimals))
DOUBLE[(M,D)]   (A double-precision float (8 bytes))
BIT[(M)]	(A bit field)

CHAR[(M)]       (A fixed-length character string, M=0-255, default=1)
VARCHAR(M)      (A variable-length string, M=0-65532) 
BINARY(M)       (A fixed-length binary string, M=bytes)
VARBINARY(M)	(A variable-length binary string, M=bytes)

TINYTEXT	(A very small string, max 255 chars)
TEXT[(M)]       (A small  string, M=0-65532)
MEDIUMTEXT	(A medium-sized string, 16777215 chars)
LONGTEXT	(A large string, 4294967295 chars) 
JSON            (=LONGTEXT)

ENUM	        (An enumeration; each column value may be assigned one enumeration member)
                (e.g. ENUM('v1','v2'))
SET	        (A set; each column value may be assigned zero or more SET members)
                (e.g. SET('v1,'v2'))

TINYBLOB	(A very small BLOB (binary large object)
BLOB     	(A small BLOB)
MEDIUMBLOB	(A medium-sized BLOB)
LONGBLOB	(A large BLOB)

DATE	        (A date value: YYYY-MM-DD, 3 bytes)
TIME	        (A time value: hh:mm:ss, 3 bytes)
DATETIME	(A date and time value: YYYY-MM-DD hh:mm:ss, 8 bytes)
TIMESTAMP	(A timestamp value: YYYY-MM-DD hh:mm:ss, 4 bytes)
YEAR	        (A year value: YYYY, 1 byte)

• data type attributes

SIGNED
UNSIGNED
ZEROFILL
UNSIGNED ZEROFILL
ZEROFILL UNSIGNED 

AUTO_INCREMENT

• charsets and collations

utf8_general_ci      (maxlen=1)
latin1_swedish_ci    (maxlen=3)
_ci = case insensitive