The SQL cheat sheet provides you with the most commonly used SQL statements for your reference.
mysql -u <user> -p
mysql [db_name]
mysql -h <host> -P <port> -u <user> -p [db_name]
mysql -h <host> -u <user> -p [db_name]
| - | - |
|---|---|
CREATE DATABASE db ; |
Create database |
SHOW DATABASES; |
List databases |
USE db; |
Switch to db |
CONNECT db ; |
Switch to db |
DROP DATABASE db; |
Delete db |
| - | - |
|---|---|
SHOW TABLES; |
List tables for current db |
SHOW FIELDS FROM t; |
List fields for a table |
DESC t; |
Show table structure |
SHOW CREATE TABLE t; |
Show create table sql |
TRUNCATE TABLE t; |
Remove all data in a table |
DROP TABLE t; |
Delete table |
| - | - |
|---|---|
show processlist; |
List processes |
kill pid; |
kill process |
| - | - |
|---|---|
exit or \q |
Exit MySQL session |
Create a backup
mysqldump -u user -p db_name > db.sql
Export db without schema
mysqldump -u user -p db_name --no-data=true --add-drop-table=false > db.sql
Restore a backup
mysql -u user -p db_name < db.sql
Create a new table with three columns
CREATE TABLE t (
id INT,
name VARCHAR DEFAULT NOT NULL,
price INT DEFAULT 0
PRIMARY KEY(id)
);
Delete the table from the database
DROP TABLE t ;
Add a new column to the table
ALTER TABLE t ADD column;
Drop column c from the table
ALTER TABLE t DROP COLUMN c ;
Add a constraint
ALTER TABLE t ADD constraint;
Drop a constraint
ALTER TABLE t DROP constraint;
Rename a table from t1 to t2
ALTER TABLE t1 RENAME TO t2;
Rename column c1 to c2
ALTER TABLE t1 RENAME c1 TO c2 ;
Remove all data in a table
TRUNCATE TABLE t;
Query data in columns c1, c2 from a table
SELECT c1, c2 FROM t
Query all rows and columns from a table
SELECT * FROM t
Query data and filter rows with a condition
SELECT c1, c2 FROM t
WHERE condition
Query distinct rows from a table
SELECT DISTINCT c1 FROM t
WHERE condition
Sort the result set in ascending or descending order
SELECT c1, c2 FROM t
ORDER BY c1 ASC [DESC]
Skip offset of rows and return the next n rows
SELECT c1, c2 FROM t
ORDER BY c1
LIMIT n OFFSET offset
Group rows using an aggregate function
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
Filter groups using HAVING clause
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition
Inner join t1 and t2
SELECT c1, c2
FROM t1
INNER JOIN t2 ON condition
Left join t1 and t1
SELECT c1, c2
FROM t1
LEFT JOIN t2 ON condition
Right join t1 and t2
SELECT c1, c2
FROM t1
RIGHT JOIN t2 ON condition
Perform full outer join
SELECT c1, c2
FROM t1
FULL OUTER JOIN t2 ON condition
Produce a Cartesian product of rows in tables
SELECT c1, c2
FROM t1
CROSS JOIN t2
Another way to perform cross join
SELECT c1, c2
FROM t1, t2
Join t1 to itself using INNER JOIN clause
SELECT c1, c2
FROM t1 A
INNER JOIN t1 B ON condition
Using SQL Operators Combine rows from two queries
SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2
Return the intersection of two queries
SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2
Subtract a result set from another result set
SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2
Query rows using pattern matching %, _
SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern
Query rows in a list
SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list
Query rows between two values
SELECT c1, c2 FROM t
WHERE c1 BETWEEN low AND high
Check if values in a table is NULL or not
SELECT c1, c2 FROM t
WHERE c1 IS [NOT] NULL
Set c1 and c2 as a primary key
CREATE TABLE t(
c1 INT, c2 INT, c3 VARCHAR,
PRIMARY KEY (c1,c2)
);
Set c2 column as a foreign key
CREATE TABLE t1(
c1 INT PRIMARY KEY,
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);
Make the values in c1 and c2 unique
CREATE TABLE t(
c1 INT, c1 INT,
UNIQUE(c2,c3)
);
Ensure c1 > 0 and values in c1 >= c2
CREATE TABLE t(
c1 INT, c2 INT,
CHECK(c1> 0 AND c1 >= c2)
);
Set values in c2 column not NULL
CREATE TABLE t(
c1 INT PRIMARY KEY,
c2 VARCHAR NOT NULL
);
Insert one row into a table
INSERT INTO t(column_list)
VALUES(value_list);
Insert multiple rows into a table
INSERT INTO t(column_list)
VALUES (value_list),
(value_list), …;
Insert rows from t2 into t1
INSERT INTO t1(column_list)
SELECT column_list
FROM t2;
Update new value in the column c1 for all rows
UPDATE t
SET c1 = new_value;
Update values in the column c1, c2 that match the condition
UPDATE t
SET c1 = new_value,
c2 = new_value
WHERE condition;
Delete all data in a table
DELETE FROM t;
Delete subset of rows in a table
DELETE FROM t
WHERE condition;
Create a new view that consists of c1 and c2
CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;
Create a new view with check option
CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;
WITH [CASCADED | LOCAL] CHECK OPTION;
Create a recursive view
CREATE RECURSIVE VIEW v
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part
Create a temporary view
CREATE TEMPORARY VIEW v
AS
SELECT c1, c2
FROM t;
Delete a view
DROP VIEW view_name;
Create or modify a trigger
CREATE OR MODIFY TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;
| - | - |
|---|---|
BEFORE |
invoke before the event occurs |
AFTER |
invoke after the event occurs |
| - | - |
|---|---|
INSERT |
invoke for INSERT |
UPDATE |
invoke for UPDATE |
DELETE |
invoke for DELETE |
| - | - |
|---|---|
FOR EACH ROW |
|
FOR EACH STATEMENT |
Create an index on c1 and c2 of the t table
CREATE INDEX idx_name
ON t(c1,c2);
Create a unique index on c3, c4 of the t table
CREATE UNIQUE INDEX idx_name
ON t(c3,c4)
Drop an index
DROP INDEX idx_name;
| - | - |
|---|---|
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) |
ENUM |
One of preset options |
SET |
Selection of preset options |
| Data Type | Format |
|---|---|
DATE |
yyyy-MM-dd |
TIME |
hh:mm:ss |
DATETIME |
yyyy-MM-dd hh:mm:ss |
TIMESTAMP |
yyyy-MM-dd hh:mm:ss |
YEAR |
yyyy |
| - | - |
|---|---|
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 |