Mysql/CheatSheet

Databases

• Create

CREATE DATABASE [IF NOT EXISTS] database_name;

• Use

USE database_name;

• Delete

DROP DATABASE [IF EXISTS] database_name;

• List all

SHOW DATABASES;

• Storage Engines
  • MyISAM (optimized for compression and speed. portable. not transaction safe. used to be default)
  • InnoDB (optimized for speed. portable. 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: -128/128 or 0/255)
SMALLINT	(A small integer: -32768/32767 or 0/65535)
MEDIUMINT	(A medium-sized integer: -8388608/8388607 or 0/16777215)
INT	        (A standard integer: -2147483648/2147483647 or 0/4294967295)
BIGINT	        (A large integer: -9223372036854775808/9223372036854775807 or 0/18446744073709551615)
DECIMAL(P,D)    (A fixed-point number: P=digits(max=65), D=decimals(max=30))
DEC             (=DECIMAL)
FLOAT	        (A single-precision floating point number
DOUBLE	        (A double-precision floating point number
BIT	        (A bit field

CHAR	        (A fixed-length nonbinary (character) string
VARCHAR	        (A variable-length non-binary string
BINARY	        (A fixed-length binary string
VARBINARY	(A variable-length binary string
TINYBLOB	(A very small BLOB (binary large object)
BLOB     	(A small BLOB
MEDIUMBLOB	(A medium-sized BLOB
LONGBLOB	(A large BLOB
TINYTEXT	(A very small non-binary string
TEXT	        (A small non-binary string
MEDIUMTEXT	(A medium-sized non-binary string
LONGTEXT	(A large non-binary string
ENUM	        (An enumeration; each column value may be assigned one enumeration member
SET	        (A set; each column value may be assigned zero or more SET members

DATE	        (A date value: YYYY-MM-DD)
TIME	        (A time value: hh:mm:ss)
DATETIME	(A date and time value: YYYY-MM-DD hh:mm:ssformat
TIMESTAMP	(A timestamp value: YYYY-MM-DD hh:mm:ss 
YEAR	        (A year value: YYYY)