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. 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(P,D)    (A fixed-point number: P=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	        (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, 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