Cheatsheets - SQL

Last Updated: 2023-01-15

Count

SELECT count(*) from classics;

Distinct

select distinct author from classics;

Delete

DELETE FROM classics WHERE title='Little Dorrit';

Query

SELECT author,title FROM classics;

Exact Match:

SELECT author,title FROM classics WHERE author="Mark Twain";

Approx Match:

SELECT author,title FROM classics WHERE author LIKE "Charles%";
SELECT author,title FROM classics WHERE title LIKE "%Species";
SELECT author,title FROM classics WHERE title LIKE "%and%";

Limit the number of results:

SELECT author,title FROM classics LIMIT 3;
SELECT author,title FROM classics LIMIT 1,2;
SELECT author,title FROM classics LIMIT 3,1; (omit first 3, starting from 4, select 1)

MATCH...AGAINST: search in FULLTEXT, could contain multiple words return nothing since 'and' is a stopword:

SELECT author,title FROM classics
WHERE MATCH(author,title) AGAINST('and');
SELECT author,title FROM classics
WHERE MATCH(author,title) AGAINST('old shop');
SELECT author,title FROM classics
WHERE MATCH(author,title) AGAINST('tom sawyer');

SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST('+charles -species' IN BOOLEAN MODE);
SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST('"origin of"' IN BOOLEAN MODE);

Order by:

SELECT author,title FROM classics ORDER BY author;
SELECT author,title FROM classics ORDER BY title DESC;
SELECT author,title,year FROM classics ORDER BY author,year DESC;
SELECT author,title,year FROM classics ORDER BY author ASC,year DESC;

Group by:

SELECT category,COUNT(author) FROM classics GROUP BY category;

Join:

SELECT name,author,title from customers,classics
WHERE customers.isbn=classics.isbn;

SELECT name,author,title FROM customers
JOIN classics ON customers.isbn=classics.isbn;

SELECT name,author,title FROM customers NATURAL JOIN classics;

As:

SELECT name,author,title from
customers AS cust, classics AS class WHERE cust.isbn=class.isbn;

SELECT author,title FROM classics WHERE
author LIKE "Charles%" AND author LIKE "%Darwin";
SELECT author,title FROM classics WHERE
author LIKE "%Mark Twain%" OR author LIKE "%Samuel Langhorne Clemens%";
SELECT author,title FROM classics WHERE
author LIKE "Charles%" AND author NOT LIKE "%Darwin";

Find MIN

select MIN(DATE) from SomeTable;

Join array to string

SELECT ARRAY_TO_STRING(arr, ",")
FROM ...