MySQL Cheat Sheet

MySQL Cheat Sheet

• 1.
  MySQL Cheat Sheet MySQL Data Types CHAR String (0 - 255) VARCHAR String (0 - 255) TINYTEXT String (0 - 255) TEXT String (0 - 65535) BLOB String (0 - 65535) MEDIUMTEXT String (0 - 16777215) MEDIUMBLOB String (0 - 16777215) LONGTEXT String (0 - 4294967295) LONGBLOB String (0 - 4294967295) TINYINT x Integer (-128 to 127) SMALLINT x Integer (-32768 to 32767) MEDIUMINT x Integer (-8388608 to 8388607) INT x Integer (-2147483648 to 2147483647) BIGINT x Integer (-9223372036854775808 to 9223372036854775807) FLOAT Decimal (precise to 23 digits) DOUBLE Decimal (24 to 53 digits) DECIMAL "DOUBLE" stored as string DATE YYYY-MM-DD DATETIME YYYY-MM-DD HH:MM:SS TIMESTAMP YYYYMMDDHHMMSS TIME HH:MM:SS ENUM One of preset options SET Selection of preset options Integers (marked x) that are "UNSIGNED" have the same range of values but start from 0 (i.e., an UNSIGNED TINYINT can have any value from 0 to 255). Select queries select all columns SELECT * FROM tbl; select some columns SELECT col1, col2 FROM tbl; select only unique records SELECT DISTINCT FROM tbl WHERE condition; column alias with AS SELECT col FROM tbl AS newname; order results SELECT * FROM tbl ORDER BY col [ASC | DESC]; group results SELECT col1, SUM(col2) FROM tbl GROUP BY col1; Creating and modifying create a database CREATE DATABASE db_name; select a database USE db_name; list the databases on the server SHOW DATABASES; show a table's fields DESCRIBE tbl; create a new table CREATE TABLE tbl (field1, field2); insert data into a table INSERT INTO tbl VALUES ("val1", "val2"); delete a row DELETE * FROM tbl WHERE condition; add a column from a table ALTER TABLE tbl ADD COLUMN col; remove a column from a table ALTER TABLE tbl DROP COLUMN col; make a column a primary key ALTER TABLE tbl ADD PRIMARY KEY (col); return only 1 row matching query ... LIMIT = 1 amend the values of a column UPDATE table SET column1="val1" WHERE ... clear all the values, leaving the table structure TRUNCATE TABLE tbl; delete the table DROP TABLE tbl; delete the database DROP DATABASE db_name; Matching data matching data using LIKE SELECT * FROM tbl1 WHERE col LIKE ‘%value%’ matching data using REGEX SELECT * FROM tbl1 WHERE col RLIKE ‘regular_expression’ Joins INNER JOIN returns only where match in both tables OUTER JOIN also returns non-matching records from both tables LEFT JOIN also returns non-matching records from left table RIGHT JOIN also returns non-matching records in right table JOIN syntax: SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id; String functions MySQL Compare strings STRCMP("str1","str2") Convert to lower case LOWER("str") Convert to upper case UPPER("str") Left trim LTRIM("str") Substring of a string SUBSTRING("str","inx1","inx2") Concatenate CONCAT("str1","str2") MySQL calculation functions Count rows COUNT(col) Average AVG(col) Minimum value MIN(col) Maximum value MAX(col) Sum of values SUM(col) Create table with auto-incrementing primary key CREATE TABLE table_name ( id INT AUTO_INCREMENT, column VARCHAR(2), column VARCHAR(32), PRIMARY KEY (id) ); Updated by Saeid Zebardast Twitter: @saeid Email: saeid.zebardast@gmail.com Home: http://zebardast.ir About: http://about.me/saeid Source: http://cheatography.com/guslong