MySQL

MySQL is a relational database management system. It is the most popular database system in the world.

Getting started

Connect MySQL

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]

Commons

Database

create database db ;	Create database
show databases;	List databases
use db;	Switch to db
drop database db;	Delete db

Table

show tables;	List tables for current db
show fields from t;	List fields for a table
desc t;	Show table structure
show create tablet;	Show create table sql
truncate tablet;	Remove all data in a table
drop tablet;	Delete table

Proccess

show processlist;	List processes
kill pid;	kill process

Other

exit or \q

Exit MySQL session

Backups

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

Examples

Managing tables

Create a new table with three columns

CREATE TABLE t (
     id INT PRIMARY KEY,
     name VARCHAR NOT NULL,
     price INT DEFAULT 0
);

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;

Querying data from a table

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

Querying from multiple tables

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

Using SQL constraints

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

Modifying Data

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;

Managing Views

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;

Managing triggers

Create or modify a trigger

CREATE OR MODIFY TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;

WHEN

BEFORE	invoke before the event occurs
AFTER	invoke after the event occurs

EVENT

INSERT	invoke for INSERT
UPDATE	invoke for UPDATE
DELETE	invoke for DELETE

TRIGGER_TYPE

FOR EACH ROW
FOR EACH STATEMENT

Managing indexes

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;

Data Types

Strings

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 - 429496­7295)
LONGBLOB	String (0 - 429496­7295)
ENUM	One of preset options
SET	Selection of preset options

Date & time

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

Numeric

TINYINT x	Integer (-128 to 127)
SMALLINT x	Integer (-32768 to 32767)
MEDIUMINT x	Integer (-8388608 to 8388607)
INT x	Integer (-2147­483648 to 214748­3647)
BIGINT x	Integer (-9223­372­036­854­775808 to 922337­203­685­477­5807)
FLOAT	Decimal (precise to 23 digits)
DOUBLE	Decimal (24 to 53 digits)
DECIMAL	“­DOU­BLE­” stored as string

Functions & Operators

Strings

Function	Description
ASCII()	Return numeric value of left-most character
BIN()	Return a string containing binary representation of a number
BIT_LENGTH()	Return length of argument in bits
CHAR()	Return the character for each integer passed
CHARACTER_LENGTH()	Synonym for CHAR_LENGTH()
CHAR_LENGTH()	Return number of characters in argument
CONCAT()	Return concatenated string
CONCAT_WS()	Return concatenate with separator
ELT()	Return string at index number
EXPORT_SET()	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD()	Index (position) of first argument in subsequent arguments
FIND_IN_SET()	Index (position) of first argument within second argument
FORMAT()	Return a number formatted to specified number of decimal places
FROM_BASE64()	Decode base64 encoded string and return result
HEX()	Hexadecimal representation of decimal or string value
INSERT()	Insert substring at specified position up to specified number of characters
INSTR()	Return the index of the first occurrence of substring
LCASE()	Synonym for LOWER()
LEFT()	Return the leftmost number of characters as specified
LENGTH()	Return the length of a string in bytes
LIKE	Simple pattern matching
LOAD_FILE()	Load the named file
LOCATE()	Return the position of the first occurrence of substring
LOWER()	Return the argument in lowercase
LPAD()	Return the string argument, left-padded with the specified string
LTRIM()	Remove leading spaces
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH	Perform full-text search
MID()	Return a substring starting from the specified position
NOT LIKE	Negation of simple pattern matching
NOT REGEXP	Negation of REGEXP
OCT()	Return a string containing octal representation of a number
OCTET_LENGTH()	Synonym for LENGTH()
ORD()	Return character code for leftmost character of the argument
POSITION()	Synonym for LOCATE()
QUOTE()	Escape the argument for use in an SQL statement
REGEXP	Whether string matches regular expression
REGEXP_INSTR()	Starting index of substring matching regular expression
REGEXP_LIKE()	Whether stringmatches regular expression
REGEXP_REPLACE()	Replace substrings matching regular expression
REGEXP_SUBSTR()	Return substring matching regular expression
REPEAT()	Repeat a string the specified number of times
REPLACE()	Replace occurrences of a specified string
REVERSE()	Reverse the characters in a string
RIGHT()	Return the specified rightmost number of characters
RLIKE	Whether string matches regular expression
RPAD()	Append string the specified number of times
RTRIM()	Remove trailing spaces
SOUNDEX()	Return a soundex string
SOUNDS LIKE	Compare sounds
SPACE()	Return a string of the specified number of spaces
STRCMP()	Compare two strings
SUBSTR()	Return the substring as specified
SUBSTRING()	Return the substring as specified
SUBSTRING_INDEX()	Return a substring from a string before the specified number of occurrences of the delimiter
TO_BASE64()	Return the argument converted to a base-64 string
TRIM()	Remove leading and trailing spaces
UCASE()	Synonym for UPPER()
UNHEX()	Return a string containing hex representation of a number
UPPER()	Convert to uppercase
WEIGHT_STRING()	Return the weight string for a string

Date and Time

Function	Description
ADDDATE()	Add time values (intervals) to a date value
ADDTIME()	Add time
CONVERT_TZ()	Convert from one time zone to another
CURDATE()	Return the current date
CURRENT_DATE()	Synonyms for CURDATE()
CURRENT_TIME()	Synonyms for CURTIME()
CURRENT_TIMESTAMP()	Synonyms for NOW()
CURTIME()	Return the current time
DATE()	Extract the date part of a date or datetime expression
DATE_ADD()	Add time values (intervals) to a date value
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract a time value (interval) from a date
DATEDIFF()	Subtract two dates
DAY()	Synonym for DAYOFMONTH()
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Return the day of the month (0-31)
DAYOFWEEK()	Return the weekday index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
EXTRACT()	Extract part of a date
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format Unix timestamp as a date
GET_FORMAT()	Return a date format string
HOUR()	Extract the hour
LAST_DAY	Return the last day of the month for the argument
LOCALTIME()	Synonym for NOW()
LOCALTIMESTAMP()	Synonym for NOW()
MAKEDATE()	Create a date from the year and day of year
MAKETIME()	Create time from hour, minute, second
MICROSECOND()	Return the microseconds from argument
MINUTE()	Return the minute from the argument
MONTH()	Return the month from the date passed
MONTHNAME()	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD()	Add a period to a year-month
PERIOD_DIFF()	Return the number of months between periods
QUARTER()	Return the quarter from a date argument
SEC_TO_TIME()	Converts seconds to ‘hh:mm:ss’ format
SECOND()	Return the second (0-59)
STR_TO_DATE()	Convert a string to a date
SUBDATE()	Synonym for DATE_SUB() when invoked with three arguments
SUBTIME()	Subtract times
SYSDATE()	Return the time at which the function executes
TIME()	Extract the time portion of the expression passed
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the argument converted to seconds
TIMEDIFF()	Subtract time
TIMESTAMP()	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()	Add interval to datetime
TIMESTAMPDIFF()	Subtract datetime expressions
TO_DAYS()	Convert a date to days
TO_SECONDS()	Convert a time value to seconds
UNIX_TIMESTAMP()	Return the current Unix timestamp
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
WEEK()	Return the week number
WEEKDAY()	Return the weekday index (0 = Monday, 6 = Sunday)
WEEKOFYEAR()	Return the calendar week of the date (0-53)
YEAR()	Return the year from a date or datetime expression
YEARWEEK()	Return the year and week number for a date or datetime

Numeric

Function	Description
ABS()	Return the absolute value of a number
ACOS()	Return the arc cosine of a number
ASIN()	Return the arc sine of a number
ATAN2()	Return the arc tangent of the two arguments
ATAN()	Return the arc tangent of a number
AVG()	Return the average value of a group of values
BIT_AND()	Return bitwise AND
BIT_OR()	Return bitwise OR
BIT_XOR()	Return bitwise XOR
CEIL()	Return the smallest integer value not less than a number
CEILING()	Synonym for CEIL()
COS()	Return the cosine of a number
COT()	Return the cotangent of a number
COUNT()	Return the number of rows in a group
DEGREES()	Convert radians to degrees
DIV	Integer division
EXP()	Raise to the power of e
FLOOR()	Return the largest integer value not greater than a number
GREATEST()	Return the largest argument
LEAST()	Return the smallest argument
LN()	Return the natural logarithm of a number
LOG10()	Return the base-10 logarithm of a number
LOG2()	Return the base-2 logarithm of a number
LOG()	Return the natural logarithm of a number
MAX()	Return the maximum value in a group of values
MIN()	Return the minimum value in a group of values
MOD()	Return the remainder of a division
PI()	Return the value of pi
POW()	Return the result of raising a number to a power
POWER()	Synonym for POW()
RADIANS()	Convert degrees to radians
RAND()	Return a random floating-point value
ROUND()	Round a number to a specified number of decimal places
SIGN()	Return the sign of a number (1 for positive, 0 for zero, -1 for negative)
SIN()	Return the sine of a number
SQRT()	Return the square root of a number
SUM()	Return the sum of values in a group
TAN()	Return the tangent of a number
TRUNCATE()	Truncate to specified number of decimal places
UUID()	Return a UUID value

Aggregate Functions

Function	Description
AVG()	Return the average value of a group of values
BIT_AND()	Return bitwise AND
BIT_OR()	Return bitwise OR
BIT_XOR()	Return bitwise XOR
COUNT()	Return the number of rows in a group
GROUP_CONCAT()	Concatenate string values from a group
MAX()	Return the maximum value in a group of values
MIN()	Return the minimum value in a group of values
STD()	Return the population standard deviation
STDDEV()	Synonym for STD()
STDDEV_POP()	Return the population standard deviation
STDDEV_SAMP()	Return the sample standard deviation
SUM()	Return the sum of values in a group
VAR_POP()	Return the population variance
VAR_SAMP()	Return the sample variance
VARIANCE()	Synonym for VAR_SAMP()

Control Flow Functions

Function	Description
CASE	Case expression
IF()	Return a value based on a condition
IFNULL()	Return the first non-NULL argument
NULLIF()	Return NULL if two expressions are equal

String and Text Functions

Function	Description
ASCII()	Return the ASCII value of a character
BIN()	Return a binary representation of a number
BIT_LENGTH()	Return the length of a string in bits
CHAR()	Return a character from an integer ASCII value
CHAR_LENGTH()	Return the number of characters in a string
CHARACTER_LENGTH()	Synonym for CHAR_LENGTH()
CONCAT()	Concatenate strings
CONCAT_WS()	Concatenate with separator
CONV()	Convert numbers between different bases
CONVERT()	Cast a value as a different type
ELT()	Return the N-th element of a list
EXPORT_SET()	Return a string with the set bits in integer values converted to words
FIELD()	Return the index of a value in a list
FIND_IN_SET()	Return the index position of a value in a set
FORMAT()	Format a number
FROM_BASE64()	Decode from base64
HEX()	Return a hexadecimal string
INSERT()	Insert a substring at a specified position
INSTR()	Return the index of the first occurrence of a substring
LCASE()	Convert a string to lowercase
LEFT()	Return the leftmost characters from a string
LENGTH()	Return the length of a string
LOCATE()	Return the position of the first occurrence of a substring
LOWER()	Synonym for LCASE()
LPAD()	Left-pad a string
LTRIM()	Remove leading spaces from a string
MAKE_SET()	Return a set of comma-separated strings
MATCH()	Perform full-text search
MID()	Return a substring from a string
OCT()	Return a string representation of a number in octal
OCTET_LENGTH()	Return the length of a string in bytes
ORD()	Return the Unicode code point for the leftmost character of a string
POSITION()	Synonym for LOCATE()
QUOTE()	Quote a string to produce a result that can be used as a properly escaped data value
REPEAT()	Repeat a string a specified number of times
REPLACE()	Replace occurrences of a substring
REVERSE()	Reverse a string
RIGHT()	Return the rightmost characters from a string
RPAD()	Right-pad a string
RTRIM()	Remove trailing spaces from a string
SOUNDEX()	Return a soundex string
SPACE()	Return a string consisting of the specified number of spaces
STRCMP()	Compare two strings
SUBSTRING()	Return a substring from a string
SUBSTRING_INDEX()	Return a substring from a string before the first occurrence of a delimiter
TRIM()	Remove leading and trailing spaces from a string
UCASE()	Convert a string to uppercase
UNHEX()	Convert a hexadecimal string to a binary string
UPPER()	Synonym for UCASE()
WEIGHT_STRING()	Return the weight string for a string

Date and Time Functions

Function	Description
ADDDATE()	Add a date value and an interval
ADDTIME()	Add a time value and an interval
CONVERT_TZ()	Convert a datetime from one time zone to another
CURDATE()	Return the current date
CURRENT_DATE()	Synonym for CURDATE()
CURRENT_TIME()	Return the current time
CURRENT_TIMESTAMP()	Return the current date and time
CURTIME()	Return the current time
DATE()	Extract the date part of a datetime expression
DATE_ADD()	Add an interval to a date
DATE_FORMAT()	Format a date value
DATE_SUB()	Subtract an interval from a date
DATEDIFF()	Subtract two dates and return the difference in days
DAY()	Extract the day of the month from a date
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Synonym for DAY()
DAYOFWEEK()	Return the index of the weekday (1 = Sunday, 7 = Saturday)
DAYOFYEAR()	Return the day of the year
EXTRACT()	Extract a part from a datetime expression
FROM_DAYS()	Convert a day number to a date
HOUR()	Extract the hour from a time
LAST_DAY()	Return the last day of the month
LOCALTIME()	Synonym for NOW()
LOCALTIMESTAMP()	Synonym for NOW()
MAKEDATE()	Create a date from the year and day of year
MAKETIME()	Create a time from the hour, minute, and second
MICROSECOND()	Extract the microseconds from a time value
MINUTE()	Extract the minute from a time
MONTH()	Extract the month from a date
MONTHNAME()	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD()	Add a period to a year-month value
PERIOD_DIFF()	Subtract two period values and return the difference
QUARTER()	Return the quarter of the year
SEC_TO_TIME()	Convert seconds to a time value
SECOND()	Extract the second from a time
STR_TO_DATE()	Convert a string to a date value
SUBDATE()	Synonym for DATE_SUB()
SUBTIME()	Subtract a time value and an interval
SYSDATE()	Synonym for NOW()
TIME()	Extract the time part of a datetime expression
TIME_TO_SEC()	Convert a time value to seconds
TIMEDIFF()	Subtract two time values and return the difference
TIMESTAMP()	Convert a date and time to a timestamp
TO_DAYS()	Convert a date to a day number
TO_SECONDS()	Convert a date and time to seconds
UNIX_TIMESTAMP()	Return a Unix timestamp
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
WEEK()	Return the week number for a date
WEEKDAY()	Return the index of the weekday (0 = Monday, 6 = Sunday)
WEEKOFYEAR()	Synonym for WEEK()
YEAR()	Extract the year from a date
YEARWEEK()	Return the year and week number for a date

JSON Functions

Function	Description
JSON_ARRAY()	Create a JSON array
JSON_ARRAYAGG()	Aggregate values into a JSON array
JSON_CONTAINS()	Check if a JSON document contains a specific value
JSON_CONTAINS_PATH()	Check if a JSON document contains a specific path
JSON_EXTRACT()	Extract a value from a JSON document
JSON_INSERT()	Insert a value into a JSON document
JSON_MERGE()	Merge two or more JSON documents
JSON_MERGE_PATCH()	Merge a JSON patch into a JSON document
JSON_OBJECT()	Create a JSON object
JSON_QUOTE()	Quote a JSON string
JSON_REMOVE()	Remove a value from a JSON document
JSON_REPLACE()	Replace a value in a JSON document
JSON_SEARCH()	Search for a value in a JSON document
JSON_SET()	Set a value in a JSON document
JSON_TYPE()	Return the JSON type of a value
JSON_UNQUOTE()	Unquote a JSON string

Also see

• Regex in MySQL