This is a draft cheat sheet. It is a work in progress and is not finished yet.
Data Types
CHAR / VARCHAR |
String (0 - 255) |
TEXT |
String (0 - 65535) |
MEDIUMTEXT |
String (0 - 16777215) |
LONGTEXT |
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 up to 23 digits) |
DOUBLE |
Decimal (24 to 53 digits) |
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 queries
|
Select all columns SELECT * FROM tbl;
|
|
Select some columns SELECT col1, col2 FROM tbl;
|
|
Filter results [SELECT Statement] WHERE condition;
|
|
Column alias [SELECT Statement] AS newname;
|
|
Order results [SELECT Statement] ORDER BY col [ASC | DESC];
|
|
Group results [SELECT Statement] GROUP BY col1;
|
|
Limit amount of Results [SELECT Statement] LIMIT = num
|
|
Select only unique records SELECT DISTINCT FROM tbl
|
|
|
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(col1, col2);
|
|
Insert data into a table INSERT INTO tbl VALUES("val1", "val2");
|
|
Delete a row DELETE * FROM tbl WHERE condition;
|
|
Add a column to 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);
|
|
Update the values of a column UPDATE tbl SET col1="val" WHERE condition;
|
|
Clear everything except table structure TRUNCATE TABLE tbl;
|
|
Delete a table DROP TABLE tbl;
|
|
Delte a database DROP DATABASE db_name;
|
|
List all tables in database SHOW TABLES;
|
Joins
INNER JOIN |
Returns only when in both tables |
OUTER JOIN |
Returns non-matching records if in at least one table |
LEFT JOIN |
Also returns non-mathing records from the left table |
RIGHT JOIN |
Also returns non-matching records from the right table |
JOIN syntax:
SELECT * FROM tbl1 [INNER | OUTER | LEFT | RIGHT] JOIN tbl2 ON tbl1.id = tbl2.id;
|
|
String functions
Compare strings |
STRCMP("str1", "str2"); |
Convert to lower case |
LOWER("str"); |
Convert to upper case |
UPPER("str"); |
(Right) trim |
RTIM("str"); |
Left trim |
LTRIM("str"); |
Substring |
SUBSTRING("str", start_pos, num_chars); |
Concatenate |
CONCAT("str1", "str2"); |
|
