MySQL Cheat Sheet

2011-09-15

TEXT TYPES

  • CHAR( )
    A fixed section from 0 to 255 characters long.
  • VARCHAR( )
    A variable section from 0 to 255 characters long.
  • TINYTEXT
    A string with a maximum length of 255 characters.
  • TEXT
    A string with a maximum length of 65535 characters.
  • BLOB
    A string with a maximum length of 65535 characters.
  • MEDIUMTEXT
    A string with a maximum length of 16777215 characters.
  • MEDIUMBLOB
    A string with a maximum length of 16777215 characters.
  • LONGTEXT
    A string with a maximum length of 4294967295 characters.
  • LONGBLOB
    A string with a maximum length of 4294967295 characters.

NUMBER TYPES

  • TINYINT( )
    -128 to 127 normal, 0 to 255 UNSIGNED.
  • SMALLINT( )
    -32768 to 32767 normal, 0 to 65535 UNSIGNED.
  • MEDIUMINT( )
    -8388608 to 8388607 normal, 0 to 16777215 UNSIGNED.
  • INT( )
    -2147483648 to 2147483647 normal, 0 to 4294967295 UNSIGNED.
  • BIGINT( )
    -9223372036854775808 to 9223372036854775807 normal, 0 to 18446744073709551615 UNSIGNED.
  • FLOAT
    A small number with a floating decimal point.
  • DOUBLE( , )
    A large number with a floating decimal point.
  • DECIMAL( , )
    A DOUBLE stored as a string , allowing for a fixed decimal point.
  • BOOL
    A synonym for TINYINT(1). 0, FALSE or 1+, TRUE.

DATE TYPES

  • DATE
    YYYY-MM-DD
  • DATETIME
    YYYY-MM-DD HH:MM:SS
  • TIMESTAMP
    YYYYMMDDHHMMSS
  • TIME
    HH:MM:SS
  • YEAR
    YYYY.

ENUM and SET TYPES

  • ENUM( )
    Short for ENUMERATION which means that each column may have one of a specified possible values.
  • SET
    Similar to ENUM except each column may have more than one of the specified possible values.

Examples

CREATE DATABASE mydb; CREATE USER 'mydbuser'@'localhost'; GRANT ALL ON mydb.* TO 'mydbuser'@'localhost' IDENTIFIED BY 'P@$$W0rD'; CREATE TABLE `cheat_sheet` ( `bigint` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `int` INT NOT NULL, `mediumint` MEDIUMINT NOT NULL, `smallint` SMALLINT NOT NULL, `tinyint` TINYINT NOT NULL COMMENT 'comment', `float` FLOAT NOT NULL, `double` DOUBLE NOT NULL, `decimal` DECIMAL NOT NULL, `char` CHAR (255) NOT NULL, `varchar` VARCHAR(255) NOT NULL, `binary` BINARY NOT NULL, `varbinary` VARBINARY (2040) NOT NULL, `longtext` LONGTEXT NOT NULL, `mediumtext` MEDIUMTEXT NOT NULL, `text` TEXT NOT NULL, `tinytext` TINYTEXT NOT NULL, `longblob` LONGBLOB NOT NULL, `mediumblob` MEDIUMBLOB NOT NULL, `blob` BLOB NOT NULL, `tinyblob` TINYBLOB NOT NULL, `date` DATE NOT NULL, `datetime` DATETIME NOT NULL, `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `time` TIME NOT NULL, `year` YEAR NOT NULL, `enum` ENUM ('y','n') NOT NULL, `set` SET ('r','g','b') NOT NULL, `bool` BOOL NOT NULL, INDEX (`varchar`), UNIQUE ( `char` ), FULLTEXT ( `text` ) ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'table comment'; ALTER TABLE `cheat_sheet` ADD `addedint` INT NOT NULL AFTER `bigint`; ALTER TABLE `cheat_sheet` CHANGE `addedint` `addedintedited` INT( 11 ) NOT NULL; ALTER TABLE `cheat_sheet` DROP `addedintedited`; INSERT INTO `mydb`.`cheat_sheet` ( `bigint` , `varchar` , `timestamp` , ) VALUES ( NULL , 'hello', CURRENT_TIMESTAMP ); DELETE FROM `cheat_sheet` WHERE `cheat_sheet`.`bigint` = 2 LIMIT 1; UPDATE `mydb`.`cheat_sheet` SET `varchar` = 'hello' WHERE `cheat_sheet`.`bigint` = 3 LIMIT 1; SELECT * FROM `cheat_sheet` WHERE `varchar` LIKE '%lo%' ORDER BY `bigint` DESC LIMIT 0 , 30;

Uber-Sweet Function List

MySQL Useful Functions and Clauses