SQL

Structured Query Language

A standard of ANSI.

Almost all relational database management systems use SQL.

This reference focuses on MySQL.

What Can SQL Do

  • Query database
  • Retrieve data
  • Insert records
  • Update records
  • Delete records
  • Create new databases
  • Create new tables
  • Create stored procedures
  • Create views
  • Config permissions for tables, stored procedures and views

Versions and Invariants

Unfortunately, there are different versions of SQL.

In addition, the majority of DBMS have their own features or special functionalities. And those features are usually implemented using SQL.

But the basic grammar is shared.

SELECT
UPDATE
DELETE
INSERT
WHERE
...

SQL Categories

Data Definition Language (DDL)

  • Change the database, usually structure
CREATE
ALTER
DROP
RENAME

Data Manipulation Language (DML)

  • Change the content of database
SELECT
INSERT
UPDATE
DELETE

Data Control Language (DCL)

  • Privileges
GRANT
REVOKE

Transaction Control (TCL)

  • Transaction
COMMIT
SAVEPOINT
ROLLBACK

Relational

Data are stored using tables (row and column) with specified relationships between them.

Database Management

Create

CREATE DATABASE aaa;

Delete

DROP DATABASE aaa;

Table

Each column is an attribute

Each row is a record

Common Data Types

Data Type Usage
integer(size)/int(size), smallint(size), tinyint(size) store integers (exact value)
decimal(size,d), numeric(size,d) fixed-point (exact value)
float, double floating-point (approximate value)
char(size) fixed-length
varchar(size) variable-length
date date

MySQL Data Types

char(10) means a string with length 10.

varchar(10) where 10 is the max length. Usually 100 or 255.

Must provide data type when creating the tables.

Create a table

CREATE TABLE some_table_name
(
some_col_name some_data_type,
some_col_name some_data_type,
some_col_name some_data_type,
...
);

Check table

DESCRIBE aaa;
DESC aaa;

In the output of DESC,

  • Field means the column names, i.e. the attributes
  • Type is type
  • Null is if the cells in this column can be Null
  • Key is whether this column will be used as a key column
  • Default is the default value

Delete table

DROP aaa;

Modify table

ALTER TABLE aaa RENAME bbb;

ALTER TABLE aaa ADD ccc some_type;

ALTER TABLE aaa DROP COLUMN ccc;

ALTER TABLE aaa MODIFY ccc some_new_type;

ALTER TABLE aaa CHANGE COLUMN ccc some_new_name some_new_type;

Table Data

Insert Records

  • either provide all columns
  • or specify columns to insert
    • might have some concern with Null
    • prefer the later way that specifies the columns since no need to rewrite if adding new columns (can have NULL) in the future
INSERT INTO table_name VALUES (val_0, val_1, ...);
INSERT INTO table_name (col_a, col_b) VALUES (val_a, val_b);

Lookup Records

SELECT col_1, col2, ... FROM table_name;
SELECT * FROM table_name;
SELECT * FROM table_name\G

SELECT col FROM table_name WHERE col operator val;
SELECT * FROM course WHERE course_level = 1;
SELECT * FROM course WHERE course_name = 'SQL';

Aliases

SELECT salary / 1000.0 AS Salary_in_k
FROM usr_total_salary_cumulative_table AS sa_table;

Common WHERE operators

WHERE conditions can be connected using AND, OR.

For numerical values:

operator meaning
= equal
<>,!= not equal
> larger than
< smaller than
>= larger than or equal to
<= smaller than or equal to
(NOT) BETWEEN ... AND ... between a range
(NOT) IN (...) appear in a list

For string values:

operator meaning
= equal (case-sensitive)
<>,!= not equal (case-sensitive)
(NOT) LIKE pattern matching (case-insensitive)
(NOT) IN (...) in a list

Generally:

operator meaning
IS (NOT) NULL NULL check

Can use % to match zero or more characters and _ to match one character in LIKE:

LIKE "%A%B%"
LIKE "HEA_T"

MySQL :: MySQL 8.0 Reference Manual :: 12.3 Operators

NULL

Note NULL values are generally more tricky to tackle than some “empty” values, like 0 or “”. Use NULL with caution. But if “no data” is a data in specific cases, NULL is a good option.

MySQL :: MySQL 8.0 Reference Manual :: 3.3.4.6 Working with NULL Values

Process Results

SELECT DISTINCT name FROM user;

SELECT * FROM user ORDER BY uid ASC/DESC;

SELECT * FROM user LIMIT 5;                   -- return first 5 user
SELECT * FROM user LIMIT 5 OFFSET 0;          -- return first 5 user (equivalent to above)
SELECT * FROM user LIMIT 5 OFFSET 2;          -- return 3th to 8th user

SELECT * FROM user ORDER BY age DESC LIMIT 5; -- return top 5 oldest user

JOIN

Try to understand JOINs using venn diagrams.

INNER JOINS:

  • INNER JOIN is like (A ∩ B)

OUTER JOINs:

  • LEFT JOIN is like A ∪ (A ∩ B)
  • RIGHT JOIN is like (A ∩ B) ∪ B
  • FULL JOIN is like A ∪ B

For LEFT or RIGHT or FULL JOINs, NULL values may be appended to the result if there is no matching column in “right” or “left” table or “one of” the tables.

Note the OUTER keyword is optional.

...
FROM table_1
INNER JOIN table_2               -- INNER JOIN is default JOIN
    ON table_1.id = table_2.id
...

See end of this page for examples.

This page has some good discussions on JOINs: MySQL FULL JOIN? - Stack Overflow

Aggregate and Group Results

Group function generally ignore NULL values.

GROUP BY will group rows based on unique values in the specified columns.

Use HAVING to filter grouped rows (think it as the WHERE of GROUP BY).

SELECT region, SUM(salary) AS salary_sum FROM usr
GROUP BY region
HAVING region LIKE "US-%";  -- assume regions are like US-AL, US-AK, US-CA, ..., CA-ON, ...

Common group functions:

function usage
AVG() average
COUNT() count
COUNT(DISTINCT) count distinct values
MAX() max
MIN() min
STD() standard deviation
SUM() sum
VARIANCE() variance

Group function generally ignore NULL values.

MySQL :: MySQL 8.0 Reference Manual :: 12.20.1 Aggregate (GROUP BY) Function Descriptions

SELECT Overview

From MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Syntax:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]]

Delete Records

DELETE FROM table_name WHERE col operator val;
DELETE * FROM table_name;

Update Records

UPDATE table_name SET col_to_change = new_value WHERE col_identify = val;
UPDATE course SET lecture = 'Math' WHERE id = 1;

Create Procedure

MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax

delimiter //

CREATE PROCEDURE loop_insert (N int, theName VARCHAR(20), theData MEDIUMTEXT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE (i < N) DO
    INSERT INTO `theTable` (name, data) VALUES (theName, theData);
    SET i = i + 1;
  END WHILE;
END//

delimiter ;

CALL loop_insert(100, "Tom", "is a cat");

Example Query

Example for JOINs and some constrains:

Table usr:

uid name
1 "u1"
2 "hello"
3 "root"
4 "admin"
5 "ha-ha"
6 "legend" 
CREATE TABLE usr (
  uid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (uid)
);
INSERT INTO usr (name) VALUES ("u1");
INSERT INTO usr (name) VALUES ("hello");
INSERT INTO usr (name) VALUES ("root");
INSERT INTO usr (name) VALUES ("admin");
INSERT INTO usr (name) VALUES ("ha-ha");
INSERT INTO usr (name) VALUES ("legend");

Table records:

id uid content
1 1 "test"
2 1 "test"
3 1 "test"
4 1 "test"
5 2 "abc"
6 3 "cloud"
7 2 "train"
8 4 "room"
9 1 "noodle"
10 1024 "hacked" 
CREATE TABLE records (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  content VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO records (uid, content) VALUES (1, "test");
INSERT INTO records (uid, content) VALUES (1, "test");
INSERT INTO records (uid, content) VALUES (1, "test");
INSERT INTO records (uid, content) VALUES (1, "test");
INSERT INTO records (uid, content) VALUES (2, "abc");
INSERT INTO records (uid, content) VALUES (3, "cloud");
INSERT INTO records (uid, content) VALUES (2, "train");
INSERT INTO records (uid, content) VALUES (4, "room");
INSERT INTO records (uid, content) VALUES (1, "noodle");
INSERT INTO records (uid, content) VALUES (1024, "hacked");

Table constrained_records:

id uid content
1 1 "test"
2 1 "test"
3 1 "test"
4 1 "test"
5 2 "abc"
6 3 "cloud"
7 2 "train"
8 4 "room"
9 1 "noodle" 
CREATE TABLE constrained_records (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  content VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (uid) REFERENCES usr(uid)
);
INSERT INTO constrained_records (uid, content) VALUES (1, "test");
INSERT INTO constrained_records (uid, content) VALUES (1, "test");
INSERT INTO constrained_records (uid, content) VALUES (1, "test");
INSERT INTO constrained_records (uid, content) VALUES (1, "test");
INSERT INTO constrained_records (uid, content) VALUES (2, "abc");
INSERT INTO constrained_records (uid, content) VALUES (3, "cloud");
INSERT INTO constrained_records (uid, content) VALUES (2, "train");
INSERT INTO constrained_records (uid, content) VALUES (4, "room");
INSERT INTO constrained_records (uid, content) VALUES (1, "noodle");

Queries:

SELECT *
FROM usr
  JOIN records
  ON usr.uid = records.uid;
uid name id uid content
1 u1 1 1 test
1 u1 2 1 test
1 u1 3 1 test
1 u1 4 1 test
2 hello 5 2 abc
3 root 6 3 cloud
2 hello 7 2 train
4 admin 8 4 room
1 u1 9 1 noodle 
SELECT *
FROM usr
  RIGHT JOIN records
  ON usr.uid = records.uid;
uid name id uid content
1 u1 1 1 test
1 u1 2 1 test
1 u1 3 1 test
1 u1 4 1 test
2 hello 5 2 abc
3 root 6 3 cloud
2 hello 7 2 train
4 admin 8 4 room
1 u1 9 1 noodle
NULL NULL 10 1024 hacked 
SELECT *
FROM usr
  LEFT JOIN records
  ON usr.uid = records.uid;
uid name id uid content
1 u1 1 1 test
1 u1 2 1 test
1 u1 3 1 test
1 u1 4 1 test
2 hello 5 2 abc
3 root 6 3 cloud
2 hello 7 2 train
4 admin 8 4 room
1 u1 9 1 noodle
5 ha-ha NULL NULL NULL
6 legend NULL NULL NULL

Note MySQL does not (yet) support FULL JOIN

SELECT *
FROM usr
  FULL JOIN records
  ON usr.uid = records.uid;
uid name id uid content
1 u1 1 1 test
1 u1 2 1 test
1 u1 3 1 test
1 u1 4 1 test
2 hello 5 2 abc
3 root 6 3 cloud
2 hello 7 2 train
4 admin 8 4 room
1 u1 9 1 noodle
NULL NULL 10 1024 hacked
5 ha-ha NULL NULL NULL
6 legend NULL NULL NULL 
INSERT INTO constrained_records (uid, content) VALUES (1323, "random");

This will fail the foreign key constrain.