Essential MySQL Cheat Sheet

In this article, we'll take a look at Hide

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;

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 using LIKE SELECT * FROM tbl1 WHERE col LIKE ‘%value%’

matching data using REGEX SELECT * FROM tbl1 WHERE col RLIKE ‘regular_expression’

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

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).

JOIN syntax:SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id;