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 ...