MySQL Usage Cheat Sheet
lorem ipsum is trying very hard.
Changelog
13/01/2010: First created.
Rules of Thumb
- Use upper case for keywords
- Proper line break and indention for readability
- Have performance in mind when buliding a query
Cheat Sheet
General
Select Statement
SELECT [DISTINCT] select_expr FROM table_reference AS table_alias WHERE where_condition ORDER BY col_name [ASC|DESC] LIMIT [offset,] row_count;
Create Statement
CREATE DATABASE [IF NOT EXISTS] db_name;
CREATE TABLE [IF NOT EXISTS] tbl_name ( [col_name data_type [NOT NULL|NULL] [DEFAULT default_value] , [PRIMARY KEY (col_name)] );
Insert Statement
INSERT INTO tbl_name
[(col_name, ...)]
{VALUES|VALUE} (expr, ...) [, (expr, ...)]
INSERT INTO tbl_name [(col_name, ...)] SELECT select_expr ...
Update Statement
UPDATE tbl_name SET col_name = value [, col_name = value] WHERE where_constraint;
UPDATE table_1 LEFT JOIN table_2 ON table_1.col_name = table_2.col_name SET table_1.col_name = value WHERE where_condition;
Alter Statement
ALTER TABLE tbl_name ADD COLUMN col_name data_type data_definition AFTER col_name;
Delete Statement
DELETE FROM tbl_name WHERE where_condition LIMIT row_count;
DELETE table_1 FROM table_1 LEFT JOIN table_2 ON table_1.col_name = table_2.col_name WHERE table_2.col_name IS NULL;
Utility
SHOW PROCESSLIST;
SHOW Status; //-- Show Database System Status
SHOW DATABASES;
SHOW TABLES; //-- In Selected Database
DESCRIBE TABLE;
EXPLAIN query;
Functions
Concatenate Values
CONCAT(string_1, string_2 [, string_n]);
Date Difference
DATEDIFF(date_1, date_2); //-- Return value in days
ADDDATE(NOW() ,INTERVAL 2 DAY);
SUBDATE(CURDATE(), INTERVAL 2 MONTH);
Change Datatype
CAST(col_name AS type)
Clean Code