Cheatography
https://cheatography.com
MySQL Data TypesCHAR | 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 | | One of preset options | | 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 queriesselect 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 modifyingcreate 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 datamatching data using LIKE SELECT * FROM tbl1 WHERE col LIKE ‘%value%’ | matching data using REGEX SELECT * FROM tbl1 WHERE col RLIKE ‘regular_expression’ |
| | JoinsINNER 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 mySQLCompare 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 functionsCount 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 keyCREATE TABLE table_name (
id INT AUTO_INCREMENT,
column VARCHAR(2),
column VARCHAR(32),
PRIMARY KEY (id )
); |
|
Help Us Go Positive!
We offset our carbon usage with Ecologi. Click the link below to help us!
Created By
Metadata
Favourited By
and 32 more ...
Comments
Just starting to learn SQL on my own. This sheet will be invaluable!
Reviewed a few and liked the layout of this one. seemed to be geared to the data that i need to remind myself as a new user.
Add a Comment
Related Cheat Sheets
More Cheat Sheets by guslong