Mysql/CheatSheet

From Wiki

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)

other