MySQL Reference Sheet, Cheat Sheet of Database Programming

Download MySQL Reference Sheet and more Cheat Sheet Database Programming in PDF only on Docsity! connecting to a database # mysql [-h hostname] [-u username] [-ppassword] [dbname] importing data backup a database # mysql dbname < dbdump!le.sql # mysqldump [-options] dbname [> dump!le.sql] TINYINT[(digits)] [unsigned|zero!ll] 256 BIT,BOOL,BOOLEAN synonyms for tinyint(1) SMALLINT[(digits)] [unsigned|zero!ll] 65,536 MEDIUMINT[(digits)] [unsigned|zero!ll] 16,777,216 INT,INTEGER[(digits)] [unsigned|zero!ll] 4,294,967,296 BIGINT[(digits)] [unsigned|zero!ll] 18,446,744,073,709,551,616 FLOAT[(digits, digits after decimal)] [unsigned|zero!ll] 23 digits DOUBLE[(digits, digits after decimal)] [unsigned|zero!ll] 24…53 digits DECIMAL[(digits, digits after decimal)] [unsigned|zero!ll] a type of DOUBLE stored as a string DATE 'YYYY-MM-DD' DATETIME 'YYYY-MM-DD HH:MM:SS' TIMESTAMP[(display width)] 'YYYY-MM-DD HH:MM:SS' – display widths: 6, 8, 12 or 14 TIME 'HH:MM:SS' YEAR[(2|4)] 'YYYY' – a year in 2-digit or 4-digit format CHAR[(length)] 0…255 – fixed length, right-padded with spaces VARCHAR[(length)] 0…255 – variable length (trailing spaces removed) BINARY,VARBINARY[(length)] 0…255 – stores bytes instead of character strings TINYTEXT|TINYBLOB 0…255 – text stores strings, blob stores bytes TEXT|BLOB 0…65,535 – text stores strings, blob stores bytes MEDIUMTEXT|MEDIUMBLOB 0…16,777,215 – text stores strings, blob stores bytes LONGTEXT|LONGBLOB 0…4,294,967,295 – text stores strings, blob stores bytes ENUM('value1', 'value2',…) list of up to 65,535 members, can have only one value SET('value1', 'value2',…) list of up to 64 members, can have zero or more values functions WEEK('date'[, mode]) WEEKDAY('date') DAYOFWEEK('date') DAYOFYEAR('date') MONTH('date') MONTHNAME('date') QUARTER('date') YEAR('date') YEARWEEK('date'[, mode]) HOUR('date') MINUTE('date') SECOND('date') TO_DAYS('date') FROM_DAYS(number) LAST_DAY('date') SEC_TO_TIME(seconds) TIME_TO_SEC('time') SYSDATE() CURTIME(),CURRENT_TIME(),CURRENT_TIME TIME_FORMAT('date', 'format') CURDATE(),CURRENT_DATE(),CURRENT_DATE DATE_FORMAT('date', 'format') NOW(),CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,LOCALTIME(),LOCALTIME UNIX_TIMESTAMP(['date']) FROM_UNIXTIME('unix_timestamp'[, 'format']) PERIOD_ADD('period', num) PERIOD_DIFF('period', num) EXTRACT(unit FROM 'date') ADDDATE('date', days) | ADDDATE('date', INTERVAL expr unit),DATE_ADD('date', INTERVAL expr unit) SUBDATE('date', days) | SUBDATE('date', INTERVAL expr unit),DATE_SUB('date', INTERVAL expr unit) functions ASCII('str') CONV(number,from_base,to_base) BIN(num),OCT(num),HEX(num) ORD('str') CHAR(number[ USING charset],…) CONCAT('str'1, 'str1',…) LENGTH('str') CHAR_LENGTH('str') CONCAT_WS('separator', 'str1', 'str2') BIT_LENGTH('str') REVERSE('str') SOUNDEX('str') LCASE('str') UCASE('str') QUOTE('str') LPAD('str', len, 'padstr') RPAD('str', len, 'padstr') ELT(number, 'str1', 'str2', 'str3',…) LEFT('str', length) RIGHT('str', length) FIELD('str', 'str1', 'str2', 'str3',…) LTRIM('str') RTRIM('str') TRIM('str') LOAD_FILE('!lename') SPACE(count) REPEAT('str', count) SUBSTRING('str', pos[, length]) REPLACE('str', 'from', 'to') INSERT('str', pos, length, 'newstr') SUBSTRING_INDEX('str', 'del', count) INSTR('str', 'substr') LOCATE('substr', 'str'[, pos]) STRCMP('str1', 'str2') functions ABS(X) SIGN(X) FLOOR(X) CEILING(X) ROUND(X[,D]) EXP(X) DIV(X) MOD(N,M) POW(X,Y) POWER(X,Y) SQRT(X) RAND([seed]) PI() DEGREES(X) RADIANS(X) COT(X) COS(X) ACOS(X) SIN(X) ASIN(X) TAN(X) ATAN(X) ATAN2(X) LOG(X), LOG2(X), LOG10(X) LN(X) TRUNCATE(X, D) REFERENCE SHEET numeric strings date & time commands REGEXP 'expression'