Skip to content

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
CONNECT 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 TABLE t;Show create table sql
TRUNCATE TABLE t;Remove all data in a table
DROP TABLE t;Delete table

Proccess

--
show processlist;List processes
kill pid;kill process

Other

--
exit or \qExit MySQL session

Backups

Create a backup

sql
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

MySQL Examples

Managing tables

Create a new table with three columns

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

Delete the table from the database

sql
DROP TABLE t ;

Add a new column to the table

sql
ALTER TABLE t ADD column;

Drop column c from the table

sql
ALTER TABLE t DROP COLUMN c ;

Add a constraint

sql
ALTER TABLE t ADD constraint;

Drop a constraint

sql
ALTER TABLE t DROP constraint;

Rename a table from t1 to t2

sql
ALTER TABLE t1 RENAME TO t2;

Rename column c1 to c2

sql
ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all data in a table

sql
TRUNCATE TABLE t;

Querying data from a table

Query data in columns c1, c2 from a table

sql
SELECT c1, c2 FROM t

Query all rows and columns from a table

sql
SELECT * FROM t

Query data and filter rows with a condition

sql
SELECT c1, c2 FROM t
WHERE condition

Query distinct rows from a table

sql
SELECT DISTINCT c1 FROM t
WHERE condition

Sort the result set in ascending or descending order

sql
SELECT c1, c2 FROM t
ORDER BY c1 ASC [DESC]

Skip offset of rows and return the next n rows

sql
SELECT c1, c2 FROM t
ORDER BY c1 
LIMIT n OFFSET offset

Group rows using an aggregate function

sql
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1

Filter groups using HAVING clause

sql
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition

Querying from multiple tables

Inner join t1 and t2

sql
SELECT c1, c2 
FROM t1
INNER JOIN t2 ON condition

Left join t1 and t1

sql
SELECT c1, c2 
FROM t1
LEFT JOIN t2 ON condition

Right join t1 and t2

sql
SELECT c1, c2 
FROM t1
RIGHT JOIN t2 ON condition

Perform full outer join

sql
SELECT c1, c2 
FROM t1
FULL OUTER JOIN t2 ON condition

Produce a Cartesian product of rows in tables

sql
SELECT c1, c2 
FROM t1
CROSS JOIN t2

Another way to perform cross join

sql
SELECT c1, c2 
FROM t1, t2

Join t1 to itself using INNER JOIN clause

sql
SELECT c1, c2
FROM t1 A
INNER JOIN t1 B ON condition

Using SQL Operators Combine rows from two queries

sql
SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2

Return the intersection of two queries

sql
SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2

Subtract a result set from another result set

sql
SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2

Query rows using pattern matching %, _

sql
SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern

Query rows in a list

sql
SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list

Query rows between two values

sql
SELECT c1, c2 FROM t
WHERE  c1 BETWEEN low AND high

Check if values in a table is NULL or not

sql
SELECT c1, c2 FROM t
WHERE  c1 IS [NOT] NULL

Using SQL constraints

Set c1 and c2 as a primary key

sql
CREATE TABLE t(
    c1 INT, c2 INT, c3 VARCHAR,
    PRIMARY KEY (c1,c2)
);

Set c2 column as a foreign key

sql
CREATE TABLE t1(
    c1 INT PRIMARY KEY,  
    c2 INT,
    FOREIGN KEY (c2) REFERENCES t2(c2)
);

Make the values in c1 and c2 unique

sql
CREATE TABLE t(
    c1 INT, c1 INT,
    UNIQUE(c2,c3)
);

Ensure c1 > 0 and values in c1 >= c2

sql
CREATE TABLE t(
  c1 INT, c2 INT,
  CHECK(c1> 0 AND c1 >= c2)
);

Set values in c2 column not NULL

sql
CREATE TABLE t(
     c1 INT PRIMARY KEY,
     c2 VARCHAR NOT NULL
);

Modifying Data

Insert one row into a table

sql
INSERT INTO t(column_list)
VALUES(value_list);

Insert multiple rows into a table

sql
INSERT INTO t(column_list)
VALUES (value_list), 
       (value_list), …;

Insert rows from t2 into t1

sql
INSERT INTO t1(column_list)
SELECT column_list
FROM t2;

Update new value in the column c1 for all rows

sql
UPDATE t
SET c1 = new_value;

Update values in the column c1, c2 that match the condition

sql
UPDATE t
SET c1 = new_value, 
        c2 = new_value
WHERE condition;

Delete all data in a table

sql
DELETE FROM t;

Delete subset of rows in a table

sql
DELETE FROM t
WHERE condition;

Managing Views

Create a new view that consists of c1 and c2

sql
CREATE VIEW v(c1,c2) 
AS
SELECT c1, c2
FROM t;

Create a new view with check option

sql
CREATE VIEW v(c1,c2) 
AS
SELECT c1, c2
FROM t;
WITH [CASCADED | LOCAL] CHECK OPTION;

Create a recursive view

sql
CREATE RECURSIVE VIEW v 
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part

Create a temporary view

sql
CREATE TEMPORARY VIEW v 
AS
SELECT c1, c2
FROM t;

Delete a view

sql
DROP VIEW view_name;

Managing triggers

Create or modify a trigger

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

WHEN

--
BEFOREinvoke before the event occurs
AFTERinvoke after the event occurs

EVENT

--
INSERTinvoke for INSERT
UPDATEinvoke for UPDATE
DELETEinvoke for DELETE

TRIGGER_TYPE

--
FOR EACH ROW
FOR EACH STATEMENT

Managing indexes

Create an index on c1 and c2 of the t table

sql
CREATE INDEX idx_name 
ON t(c1,c2);

Create a unique index on c3, c4 of the t table

sql
CREATE UNIQUE INDEX idx_name
ON t(c3,c4)

Drop an index

sql
DROP INDEX idx_name;

MySQL Data Types

Strings

--
CHARString (0 - 255)
VARCHARString (0 - 255)
TINYTEXTString (0 - 255)
TEXTString (0 - 65535)
BLOBString (0 - 65535)
MEDIUMTEXTString (0 - 16777215)
MEDIUMBLOBString (0 - 16777215)
LONGTEXTString (0 - 429496­7295)
LONGBLOBString (0 - 429496­7295)
ENUMOne of preset options
SETSelection of preset options

Date & time

Data TypeFormat
DATE yyyy-MM-dd
TIME hh:mm:ss
DATETIME yyyy-MM-dd hh:mm:ss
TIMESTAMPyyyy-MM-dd hh:mm:ss
YEAR yyyy

Numeric

--
TINYINT xInteger (-128 to 127)
SMALLINT xInteger (-32768 to 32767)
MEDIUMINT xInteger (-8388608 to 8388607)
INT xInteger (-2147­483648 to 214748­3647)
BIGINT xInteger (-9223­372­036­854­775808 to 922337­203­685­477­5807)
FLOATDecimal (precise to 23 digits)
DOUBLEDecimal (24 to 53 digits)
DECIMAL"­DOU­BLE­" stored as string

MySQL Functions & Operators

Strings

Date and Time

Numeric

Aggregate

JSON

Cast

Flow Control

Information

Encryption and Compression

Locking

Bit

Miscellaneous

Also see