MySQL cheatsheet

The SQL cheat sheet provides you with the most commonly used SQL statements for your reference.

# Getting Started
Connect MySQL - Commons

Database
mysql -u <user> -p
CREATE DATABASE db ; Create database
mysql [db_name]
SHOW DATABASES; List databases
mysql -h <host> -P <port> -u <user> -p [db_name]
USE db; Switch to db
mysql -h <host> -u <user> -p [db_name]
CONNECT db ; Switch to db

DROP DATABASE db; Delete db

Table

SHOW TABLES; List tables for current db

- Backups

Create a backup SHOW FIELDS FROM t; List fields for a table

DESC t; Show table structure

mysqldump -u user -p db_name > db.sql
SHOW CREATE TABLE t; Show create table sql
Export db without schema
TRUNCATE TABLE t; Remove all data in a table

mysqldump -u user -p db_name --no-data=true --add-drop-table=false >

DROP TABLE t; Delete table
db.sql
Process
Restore a backup show processlist; List processes

mysql -u user -p db_name < db.sql kill pid; kill process

Other

exit or \q Exit MySQL session

# MySQL Examples
- Managing tables - Querying data from a table - Querying from multiple tables

Create a new table with three columns Query data in columns c1, c2 from a table Inner join t1 and t2

CREATE TABLE t ( SELECT c1, c2 FROM t SELECT c1, c2

id INT, FROM t1
name VARCHAR DEFAULT NOT NULL, Query all rows and columns from a table INNER JOIN t2 ON condition
price INT DEFAULT 0
PRIMARY KEY(id) Left join t1 and t1
SELECT * FROM t
);

Query data and filter rows with a condition SELECT c1, c2

Delete the table from the database FROM t1
LEFT JOIN t2 ON condition
SELECT c1, c2 FROM t
DROP TABLE t ;
WHERE condition
Right join t1 and t2
Add a new column to the table
Query distinct rows from a table
SELECT c1, c2
ALTER TABLE t ADD column; FROM t1
SELECT DISTINCT c1 FROM t
RIGHT JOIN t2 ON condition
WHERE condition
Drop column c from the table
Perform full outer join
Sort the result set in ascending or descending order
ALTER TABLE t DROP COLUMN c ;
SELECT c1, c2
SELECT c1, c2 FROM t
Add a constraint FROM t1
ORDER BY c1 ASC [DESC]
FULL OUTER JOIN t2 ON condition

ALTER TABLE t ADD constraint; Skip offset of rows and return the next n rows
Produce a Cartesian product of rows in tables

Drop a constraint SELECT c1, c2 FROM t

SELECT c1, c2
ORDER BY c1
FROM t1
ALTER TABLE t DROP constraint; LIMIT n OFFSET offset
CROSS JOIN t2

Rename a table from t1 to t2 Group rows using an aggregate function

Another way to perform cross join

ALTER TABLE t1 RENAME TO t2; SELECT c1, aggregate(c2)

SELECT c1, c2
FROM t
FROM t1, t2
Rename column c1 to c2 GROUP BY c1

Join t1 to itself using INNER JOIN clause

ALTER TABLE t1 RENAME c1 TO c2 ; Filter groups using HAVING clause

SELECT c1, c2
Remove all data in a table SELECT c1, aggregate(c2)
FROM t1 A
FROM t
INNER JOIN t1 B ON condition
GROUP BY c1
TRUNCATE TABLE t;
HAVING condition
Using SQL Operators Combine rows from two queries

- Using SQL constraints - Modifying Data SELECT c1, c2 FROM t1

UNION [ALL]
Set c1 and c2 as a primary key Insert one row into a table
SELECT c1, c2 FROM t2

CREATE TABLE t( INSERT INTO t(column_list)

Return the intersection of two queries
c1 INT, c2 INT, c3 VARCHAR, VALUES(value_list);
PRIMARY KEY (c1,c2)
); SELECT c1, c2 FROM t1
Insert multiple rows into a table
INTERSECT
SELECT c1, c2 FROM t2
Set c2 column as a foreign key INSERT INTO t(column_list)
VALUES (value_list),
Subtract a result set from another result set
CREATE TABLE t1( (value_list), …;
c1 INT PRIMARY KEY,
c2 INT, SELECT c1, c2 FROM t1
Insert rows from t2 into t1
FOREIGN KEY (c2) REFERENCES t2(c2) MINUS
); SELECT c1, c2 FROM t2
INSERT INTO t1(column_list)
SELECT column_list
Make the values in c1 and c2 unique Query rows using pattern matching %, _
FROM t2;

CREATE TABLE t( SELECT c1, c2 FROM t1

Update new value in the column c1 for all rows
c1 INT, c1 INT, WHERE c1 [NOT] LIKE pattern
UNIQUE(c2,c3)
UPDATE t
); Query rows in a list
SET c1 = new_value;

Ensure c1 > 0 and values in c1 >= c2 SELECT c1, c2 FROM t

Update values in the column c1, c2 that match the
WHERE c1 [NOT] IN value_list
condition
CREATE TABLE t(
c1 INT, c2 INT, Query rows between two values
UPDATE t
CHECK(c1> 0 AND c1 >= c2)
SET c1 = new_value,
); SELECT c1, c2 FROM t
c2 = new_value
WHERE condition; WHERE c1 BETWEEN low AND high
Set values in c2 column not NULL

Delete all data in a table Check if values in a table is NULL or not

CREATE TABLE t(
c1 INT PRIMARY KEY, SELECT c1, c2 FROM t
DELETE FROM t;
c2 VARCHAR NOT NULL WHERE c1 IS [NOT] NULL
);
Delete subset of rows in a table

DELETE FROM t
WHERE condition;

- Managing Views - Managing triggers - Managing indexes

Create a new view that consists of c1 and c2 Create or modify a trigger Create an index on c1 and c2 of the t table

CREATE VIEW v(c1,c2) CREATE OR MODIFY TRIGGER trigger_name CREATE INDEX idx_name
AS WHEN EVENT ON t(c1,c2);
SELECT c1, c2 ON table_name TRIGGER_TYPE
FROM t; EXECUTE stored_procedure; Create a unique index on c3, c4 of the t table

WHEN
Create a new view with check option CREATE UNIQUE INDEX idx_name
BEFORE invoke before the event occurs ON t(c3,c4)
CREATE VIEW v(c1,c2)
AS AFTER invoke after the event occurs
Drop an index
SELECT c1, c2
EVENT
FROM t;
DROP INDEX idx_name ON t;
WITH [CASCADED | LOCAL] CHECK OPTION; INSERT invoke for INSERT

UPDATE invoke for UPDATE

Create a recursive view

DELETE invoke for DELETE

CREATE RECURSIVE VIEW v
AS TRIGGER_TYPE
select-statement -- anchor part
FOR EACH ROW
UNION [ALL]
select-statement; -- recursive part FOR EACH STATEMENT

Create a temporary view

CREATE TEMPORARY VIEW v

AS
SELECT c1, c2
FROM t;

Delete a view

DROP VIEW view_name;

# MySQL Data Types

Strings Date & time Numeric

CHAR String (0 - 255) DATE yyyy-MM-dd TINYINT x Integer (-128 to 127)

VARCHAR String (0 - 255) TIME hh:mm:ss SMALLINT x Integer (-32768 to 32767)

TINYTEXT String (0 - 255) DATETIME yyyy-MM-dd hh:mm:ss MEDIUMINT x Integer (-8388608 to 8388607)

TEXT String (0 - 65535) TIMESTAMP yyyy-MM-dd hh:mm:ss INT x Integer (-2147­483648 to 214748­3647)

BLOB String (0 - 65535) YEAR yyyy Integer (-9223­372­036­854­775808 to

BIGINT x
922337­203­685­477­5807)
MEDIUMTEXT String (0 - 16777215)
FLOAT Decimal (precise to 23 digits)
MEDIUMBLOB String (0 - 16777215)
DOUBLE Decimal (24 to 53 digits)
LONGTEXT String (0 - 429496­7295)
DECIMAL "­DOU­BLE­" stored as string
LONGBLOB String (0 - 429496­7295)

ENUM One of preset options

SET Selection of preset options

# MySQL Functions & Operators

Strings Date and Time Numeric

ASCII() BIN() ADDDATE() ADDTIME() %, MOD *

BIT_LENGTH() CHAR() CONVERT_TZ() CURDATE() + -

CHARACTER_LENGTH() CHAR_LENGTH() CURRENT_DATE() CURRENT_TIME() - /

CONCAT() CONCAT_WS() CURRENT_TIMESTAMP() CURTIME() ABS() ACOS()

ELT() EXPORT_SET() DATE() DATE_ADD() ASIN() ATAN()

FIELD() FIND_IN_SET() DATE_FORMAT() DATE_SUB() ATAN2(), ATAN() CEIL()

FORMAT() FROM_BASE64() DATEDIFF() DAY() CEILING() CONV()

HEX() INSERT() DAYNAME() DAYOFMONTH() COS() COT()

INSTR() LCASE() DAYOFWEEK() DAYOFYEAR() CRC32() DEGREES()

LEFT() LENGTH() EXTRACT() FROM_DAYS() DIV EXP()

LIKE LOAD_FILE() FROM_UNIXTIME() GET_FORMAT() FLOOR() LN()

LOCATE() LOWER() HOUR() LAST_DAY LOG() LOG10()

LPAD() LTRIM() LOCALTIME() LOCALTIMESTAMP() LOG2() MOD()

MAKE_SET() MATCH MAKEDATE() MAKETIME() PI() POW()

MID() NOT LIKE MICROSECOND() MINUTE() POWER() RADIANS()

NOT REGEXP OCT() MONTH() MONTHNAME() RAND() ROUND()

OCTET_LENGTH() ORD() NOW() PERIOD_ADD() SIGN() SIN()

POSITION() QUOTE() PERIOD_DIFF() QUARTER() SQRT() TAN()

REGEXP REGEXP_INSTR() SEC_TO_TIME() SECOND() TRUNCATE()

REGEXP_LIKE() REGEXP_REPLACE() STR_TO_DATE() SUBDATE()

Aggregate
REGEXP_SUBSTR() REPEAT() SUBTIME() SYSDATE()

REPLACE() REVERSE() TIME() TIME_FORMAT() AVG() BIT_AND()

RIGHT() RLIKE TIME_TO_SEC() TIMEDIFF() BIT_OR() BIT_XOR()

RPAD() RTRIM() TIMESTAMP() TIMESTAMPADD() COUNT() COUNT(DISTINCT)

SOUNDEX() SOUNDS LIKE TIMESTAMPDIFF() TO_DAYS() GROUP_CONCAT() JSON_ARRAYAGG()

SPACE() STRCMP() TO_SECONDS() UNIX_TIMESTAMP() JSON_OBJECTAGG() MAX()

SUBSTR() SUBSTRING() UTC_DATE() UTC_TIME() MIN() STD()

SUBSTRING_INDEX() TO_BASE64() UTC_TIMESTAMP() WEEK() STDDEV() STDDEV_POP()

TRIM() UCASE() WEEKDAY() WEEKOFYEAR() STDDEV_SAMP() SUM()

UNHEX() UPPER() YEAR() YEARWEEK() VAR_POP() VAR_SAMP()

WEIGHT_STRING() GET_FORMAT() VARIANCE()

JSON Cast Flow Control

-> BINARY CAST() CASE IF()

->> CONVERT() IFNULL() NULLIF()

JSON_ARRAY()
Information Encryption and Compression
JSON_ARRAY_APPEND()
BENCHMARK() CHARSET() AES_DECRYPT()
JSON_ARRAY_INSERT()
COERCIBILITY() COLLATION() AES_ENCRYPT()
JSON_CONTAINS()
CONNECTION_ID() CURRENT_ROLE() COMPRESS()
JSON_CONTAINS_PATH()
CURRENT_USER() DATABASE() MD5()
JSON_DEPTH()
FOUND_ROWS() ICU_VERSION() RANDOM_BYTES()
JSON_EXTRACT()
LAST_INSERT_ID() ROLES_GRAPHML() SHA1(), SHA()
JSON_INSERT()
ROW_COUNT() SCHEMA() SHA2()
JSON_KEYS()
SESSION_USER() SYSTEM_USER() STATEMENT_DIGEST()
JSON_LENGTH()
USER() VERSION() STATEMENT_DIGEST_TEXT()
JSON_MERGE() (deprecated)
UNCOMPRESS()
JSON_MERGE_PATCH()
UNCOMPRESSED_LENGTH()
JSON_MERGE_PRESERVE()
VALIDATE_PASSWORD_STRENGTH()
JSON_OBJECT()

JSON_OVERLAPS() (introduced 8.0.17)

Locking Bit

JSON_PRETTY()
GET_LOCK() & >>
JSON_QUOTE()
IS_FREE_LOCK() << ^
JSON_REMOVE()
IS_USED_LOCK() BIT_COUNT() |
JSON_REPLACE()
RELEASE_ALL_LOCKS() ~
JSON_SCHEMA_VALID() (introduced 8.0.17)
RELEASE_LOCK()
JSON_SCHEMA_VALIDATION_REPORT() (introduced
8.0.17)
Miscellaneous
JSON_SEARCH()
ANY_VALUE() BIN_TO_UUID()
JSON_SET()
DEFAULT() GROUPING()
JSON_STORAGE_FREE()
INET_ATON() INET_NTOA()
JSON_STORAGE_SIZE()
INET6_ATON() INET6_NTOA()
JSON_TABLE()
IS_IPV4() IS_IPV4_COMPAT()
JSON_TYPE()
IS_IPV4_MAPPED() IS_IPV6()
JSON_UNQUOTE()
IS_UUID() MASTER_POS_WAIT()
JSON_VALID()
NAME_CONST() SLEEP()
JSON_VALUE() (introduced 8.0.21)
UUID() UUID_SHORT()
MEMBER OF() (introduced 8.0.17)
UUID_TO_BIN() VALUES()

# Also see