SQL.md

SQL cheatsheet

• Data types
• Conditional operators
• Constraints
• Search
• Select
  • Join
  • Union
  • Sort
  • Group
  • Limit
  • Filter
• Insert row
• Update rows
• Delete rows
• Alter table
• Create table
• Drop table
• Drop database
• Subquery

---

Naming rules:

• Can contain up to 64 chars.
• Can contain chars, numbers and underscore.
• Can begins from number, but can not contain only numbers.
• Names of tables and columns are case-sensitive.
• SQL operators and statements are case-insensitive.

---

-- can be used to define a comment.
With whitespaces before and after.
Any text between -- and the end of the line will not be executed.

---

Parentheses can be used to group operators.

---

Multiple queries must be separated by ;.

=========================================================================

Data types

Integer

INT from -2 147 483 648 to 2 147 483 647
BIGINT from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807

---

Fractional

UNSIGNED
FLOAT
DOUBLE
REAL synonym for DOUBLE
DECIMAL fractional number stored as string
NUMERIC synonym fro DECIMAL

Strings

VARCHAR up to 255 chars
TEXT up to 65 535 chars
LONGTEXT up to 4 294 967 295 chars

Binary

BLOB up to 65 535
LONGBLOB up to 4 294 967 295

Date and time

DATE date in format YYYY-MM-DD
TIME time in format HH:MM:SS
DATETIME date and time in format YYYY-MM-DD HH:MM:SS
TIMESTAMP date and time as timestamp. But when retrieved it represents as YYYYMMDDHHMMSS

=========================================================================

Conditional operators

Compare

= -- e.g., WHERE id=3
<
>
<=
>=
!= or <>

Nullity

IS NOT NULL -- e.g., WHERE id IS NOT NULL
IS NULL

Range

BETWEEN m AND n -- in range [m, n]
NOT BETWEEN m AND n -- e.g., WHERE id NOT BETWEEN 2 AND 4

Logical

OR -- e.g., WHERE id>3 OR name='name'
AND
IN(values...) -- equal to any of provided values, e.g., WHERE catid IN(1, 3, 4)

=========================================================================

Constraints

CHECK (condition) -- control the column value, e.g., CHECK(discount < 50), CHECK(catid BETWEEN 1 AND 10)
NOT NULL
UNIQUE (colname, ...)

PRIMARY KEY (colname, ...) -- column (or group of columns) contains unique identifier of the row
FOREIGN KEY (colname, ...) -- values corresponds to a value from another table

=========================================================================

Search

LIKE someval
NOT LIKE someval

Wildcards:
% -- any sequence
_ -- any single char

E.g.:

	... WHERE col LIKE 'lo%'	-- will find "London", but not "slow"
	... WHERE col LIKE '_low'	-- will find "slow", but not "clown"

=========================================================================

Select

SELECT col1, ...
FROM table
WHERE condition;

Select from multiple tables

SELECT table1.id, ...,
FROM table1, ...;

---

Join

To combine rows from multiple tables based on a related column between them. Retrieving related data in a single query.

SELECT col, ...
FROM table1
JOIN_TYPE JOIN table2 ON condition

where JOIN_TYPE is:
INNER rows that have matching values in both tables.
LEFT all from first table + matching from second table.
RIGHT matching from first + all from second.

E.g.:

SELECT table1.fld1, table2.fld2
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
-- Returns all rows from table1 with associated values from table2 (NULL for non-existing values).

SELECT table1.fld1, table2.fld2
FROM table1
INNER JOIN table2 ON table1.id = table2.id
-- Rows with appropriate id.

SELECT DISTINCT u.id, firstname, lastname, p.item_id
FROM users u
	JOIN purchase p ON u.id = p.user_id
	LEFT JOIN ban_list bl ON u.id = bl.user_id
WHERE bl.user_id IS NULL                  
	OR bl.date_from > p.date               
ORDER BY lastname, firstname, u.id, p.item_id  

---

Union

To combine the result set of multiple SELECT statements into single result set.

SELECT colname, ...
FROM table1
UNION
SELECT colname, ...
FROM table1

Selects only distinct values by default. UNION ALL allows duplications.

---

Sort results

ORDER BY colname ASC|DESC, ...

where:
ASC -- ascending order (from low to high)
DESC -- descending order (from high to low)

---

Group by specific column

GROUP BY colname

---

Limit results

LIMIT n

Offset

OFFSET n

E.g.:

... WHERE filed='value' LIMIT 3	-- first 3 rows
... LIMIT 10 OFFSET 5
	-- 10 rows from 5

---

Filter

WHERE condition  -- filter individual row
HAVING condition  -- filter by aggregation

E.g.:

SELECT ...
FROM purchases p
GROUP BY p.id
HAVING SUM(p.price) > 5000

=========================================================================

Insert row

INSERT INTO tablename
(
	col1,
	...
)
VALUES(
	value1,
	...
);

=========================================================================

Update rows

UPDATE tablename
SET col1=value1, ...
WHERE condition;

=========================================================================

Delete rows

DELETE FROM tablename
WHERE condition;

=========================================================================

Alter table

Rename column:

ALTER TABLE tablename
CHANGE col_old col_new TEXT NULL DEFAULT NULL;

Change column type:

ALTER TABLE tablename
MODIFY COLUMN colname datatype_new

Add column:

ALTER TABLE tablename
ADD col_new datatype

Set charset for database:

ALTER DATABASE dbname
CHARACTER SET utf8;

=========================================================================

Create table

CREATE TABLE tablename
(
	columnname type constraints,
	...
)
attributes

E.g.:

CREATE TABLE products
(
	id INT NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (id),
	category_id INT NOT NULL,
	FOREIGN KEY (category_id) REFERENCES product_category(id),
	seller VARCHAR(30) DEFAULT 'no seller',
	price INT NOT NULL,
	CHECK(price BETWEEN 1, 100000)
)
DEFAULT CHARSET=utf8;

=========================================================================

Drop table

DROP TABLE tablename

=========================================================================

Drop database

DROP DATABASE dbname

=========================================================================

Subquery

Query can contain subqueries:

SELECT
item_id,
(SELECT price from item where id=follower.item_id) AS price,
(SUM(paid) * 100 / (SELECT price from item where id=follower.item_id)) AS percents
FROM follower
WHERE (
	(SELECT complete
	FROM item
	WHERE id=follower.item_id)=false
)
GROUP BY item_id
ORDER BY percents DESC;

WHERE condition will be processed before other parts,
so the AS-definitions are not accessible in WHERE condition.

---

INSERT, UPDATE, DELETE, DROP returns true or false.