SQL

Database > Tables > Columns > Rows

SHOW DATABASES;
 
USE dbname;

Types

INT / INTEGER
CHAR
CHAR(32)
VARCHAR
VARCHAR(32)
TEXT
ENUM('Python','Go')
DOUBLE
DECIMAL
BOOL / BOOLEAN
DATE
TIME
DATETIME
TIMESTAMP
FLOAT

Operators

<> -- not equal
 
AND
OR
XOR

Directives

CREATE

CREATE DATABASE dbname;
CREATE DATABASE IF NOT EXISTS dbname;

CREATE TABLE IF NOT EXISTS users (
	id INT PRIMARY KEY,
	name VARCHAR(255),
	age INT,
	height FLOAT,
	gender ENUM('male', 'female')
)

INSERT

-- Specify a subset of fields
INSERT INTO users (id, name) VALUES (1, "Mike");
-- Specify all the fields
INSERT INTO users VALUES (2, 'John', 78, 180);
-- Multiple rows
INSERT INTO users VALUES
(3, "Walter", 18, 190),
(4, "Scarlet", 19, 170),
(5, "Britta", 22, 168);

SELECT

SELECT * FROM users;
SELECT name, age FROM users WHERE age > 40;
SELECT * FROM users WHERE age IS NOT NULL;
SELECT name FROM users WHERE height > 180 AND age > 40;
SELECT name FROM users WHERE height > 180 OR age > 40;
 
-- No duplicates
SELECT DISTINCT age FROM users WHERE age > 30;
 
SELECT * FROM users
WHERE age IN (55, 65);
 
SELECT * FROM users
WHERE name LIKE "M%"; -- starts with "M"
SELECT * FROM users
WHERE name LIKE "%a"; -- ends with "a"
SELECT * FROM users
WHERE name LIKE "%n%"; -- with "n" in the middle
 
-- Use aliases
SELECT name AS "Name", age AS "Age" FROM users
WHERE name LIKE "%n%";
 
-- Use aggregate functions
SELECT SUM(age) AS "Age Sum" FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age) FROM users;
SELECT COUNT(*) FROM users;
 
-- Grouping
SELECT gender, AVG(height) FROM people
GROUP BY gender;
-- Multiple grouping
SELECT gender, age, AVG(height) FROM people
GROUP BY gender, age;
 
-- Order
SELECT * FROM users
ORDER BY age;
SELECT * FROM users
ORDER BY name, age;
SELECT * FROM users
ORDER BY age DESC; -- descending
 
-- Limit
SELECT * FROM users
LIMIT 5;

UPDATE

UPDATE users SET age = 30 WHERE id = 1;

DELETE

DELETE FROM users WHERE age > 50;

SET SQL_SAFE_UPDATES=0 was needed on the video I’ve watched to enable delete operations.

ALTER

ALTER TABLE users ALTER COLUMN height INT; 
ALTER TABLE users RENAME COLUMN height TO size;
ALTER TABLE users ADD COLUMN weight FLOAT;
ALTER TABLE users DROP COLUMN weight;

TRUNCATE

TRUNCATE TABLE users; -- removes all rows from the table

DROP

DROP TABLE users;
DROP TABLE IF EXISTS users;

Constraints

CREATE TABLE users (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	age INT,
	ssn CHAR(32) UNIQUE, -- social security number
	int_with_default INT DEFAULT 21,
	CONSTRAINT age_constraint CHECK (age >= 0 AND age < 200)
)
 
create TABLE users (
	id INT PRIMARY KEY AUTO_INCREMENT,
	firstname VARCHAR(255),
	lastname VARCHAR(255),
	-- Combination should be unique)
	CONSTRAINT name_constraint UNIQUE(firstname, lastname)
)

Constraints can be added to existing tables as well:

ALTER TABLE users ADD CONSTRAINT unique_lastname UNIQUE(lastname)

Relationships

CREATE TABLE IF NOT EXISTS people (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255),
	age INT,
	height FLOAT,
	gender ENUM('male', 'female')
)
 
-- 1:Many
CREATE TABLE IF NOT EXISTS things (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	description VARCHAR(255),
	owner INT,
	FOREIGN KEY (owner) REFERENCES people (id)
)
 
-- 1:1
CREATE TABLE IF NOT EXISTS things (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	description VARCHAR(255),
	owner INT UNIQUE,
	FOREIGN KEY (owner) REFERENCES people (id)
)
 
-- Many:Many requires a third table to track relationships
CREATE TABLE IF NOT EXISTS things (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	description VARCHAR(255),
	owner INT,
	FOREIGN KEY (owner) REFERENCES people (id)
)
 
CREATE TABLE IF NOT EXISTS ownership (
	owner INTEGER,
	thing INTEGER,
	PRIMARY KEY(owner, thing),
	FOREIGN KEY (owner) REFERENCES people (id),
	FOREIGN KEY (thing) REFERENCES things (id)
)

Joins

SELECT p.name, t.name 
FROM people p 
INNER JOIN things t ON p.id == t.owner;

Will include only entries where the relationship exists. JOIN == INNER JOIN

SELECT p.name, t.name FROM people p 
LEFT JOIN things t ON p.id == t.owner;
 
SELECT p.name, t.name FROM people p 
RIGHT JOIN things t ON p.id == t.owner;

OUTER JOIN CROSS JOIN

Union combines results of two queries:

SELECT p.name, t.name 
FROM people p
LEFT JOIN things t ON p.id = t.owner
UNION 
SELECT p.name, t.name 
FROM people p
RIGHT JOIN things t ON p.id = t.owner

UNION ALL allows duplicate values.

Intersections will return common results from two queries:

SELECT p.name, t.name 
FROM people p
LEFT JOIN things t ON p.id = t.owner
INTERSECT
SELECT p.name, t.name 
FROM people p
RIGHT JOIN things t ON p.id = t.owner

Except will return only unique results from the first query that are not in the second:

SELECT p.name, t.name 
FROM people p
LEFT JOIN things t ON p.id = t.owner
EXCEPT
SELECT p.name, t.name 
FROM people p
RIGHT JOIN things t ON p.id = t.owner

References

• https://www.youtube.com/watch?v=7cIG41gjHB4