Managment

# backup
mysqldump -u Username -p dbNameYouWant > databasename_backup.sql 

# restore
mysql -u Username -p dbNameYouWant < databasename_backup.sql

# local login
mysql -u Username -p

# remote login 
mysql -u Username -p -h HostnameOrIP Database

-- show status
STATUS;

-- show port
SHOW VARIABLES WHERE Variable_name = 'port';

-- show current user
 SELECT USER();

Databases

-- create database
CREATE DATABASE database;

-- show databases
SHOW DATABASES;

-- select databases
USE database;

-- delete database
DROP DATABASE database;

Tables

-- create table
CREATE TABLE table (column type [constraint ...], ...);

-- show tables
SHOW TABLES;

-- show table schema
DESC table;

-- add column
ALTER TABLE table ADD column type [constraint ...];

-- modify column type
ALTER TABLE table MODIFY column type [constraint ...];

-- change column name and type
ALTER TABLE table CHANGE column name type [constraint ...];

-- delete column
ALTER TABLE table DROP column;

-- delete table
DROP TABLE table;

Records

-- create record
INSERT INTO table (column, ...) VALUES (value, ...);
INSERT INTO table (column, ...) SELECT (column, ...) FROM table' WHERE condition;

-- query records
SELECT 
[DISTINCT]                              -- remove duplicate
<*|aggregate|column [[AS] alias], ...>  -- aggregate use with GROUP BY
FROM <table|query>
[<JOIN|,> <table|query> [ON condition]] -- all combinations between two tables
[WHERE condition]                       -- filter
[GROUP BY column, ...]                  -- use with aggregate
[HAVING condition]                      -- can not use aggregate in WHERE's condition
[ORDER BY <column [ASC|DESC], ...>]     -- ascending as default
[<INTERSECT|UNION|MINUS> query];        -- algebra of sets

-- update records
UPDATE table SET column = value, column = value... WHERE condition;
UPDATE table SET (column, ... ) SELECT (column, ...) FROM table' WHERE condition;

-- delete records
DELETE FROM table WHERE condition;

Views

-- create
CREATE VIEW view AS query;

-- show all views
SHOW FULL TABLES IN database WHERE TABLE_TYPE LIKE 'VIEW';

-- update 
CREATE OR REPLACE VIEW view AS query;

-- delete
DROP VIEW view;

Constraints

NOT NULL, UNIQUE, AUTO_INCREMENT, PRIMARY KEY(NOT NULL and UNIQUE), FOREIGN KEY(other table’s PRIMARY KEY), CHECK condition(check before insert), DEFAULT value(set default value), INDEX

aggregate functions

MAX, MIN, COUNT, AVG, SUM

condition operator

=, <>, >, <, >=, <=, AND, OR, NOT, IS IN ( value, … ), IS NULL, EXISTS, ANY, ALL, BETWEEN value AND value , LIKE regex

Regex

  • | or
  • ^ begine of line
  • $ end of line
  • % for zero, one, or mutiple times
  • _ for one character
  • [] for charlist [ a - z ] for from a to z [^a-c ] for characters not in a to c

other functions

IFNULL(expression, alt_value): return an alternative value IF the expression is NULL. COALESCE(expr1, expr2, …., expr_n): returns the first non-null expression in a list.

Examples

-- rank of subject
SELECT *, RANK() OVER(PARTITION BY subject ORDER BY score DESC) as subject_rank;

-- cumulative sum
SELECT *, SUM(revenue) OVER(ORDER BY year DESC) AS total_asset FROM annual_report;

-- delta with last year (assume every year report exist)
SELECT year, revenue, revenue - LAG(revenue, 1) OVER(ORDER BY year ASC) delta FROM annual_report;

-- moving average
SELECT year, revenue, AVG(revenue) OVER(ORDER BY year ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) moving_average FROM annual_report;
SELECT year, revenue, AVG(revenue) OVER(ORDER BY year ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) moving_average FROM annual_report;

-- percentage
WITH x AS (
    SELECT *,
    SUM(score) OVER(PARTITION BY name) AS total
    FROM grade
)
SELECT *, score/total*100 AS percentage FROM x;

References