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;
