SQL Notes
SQL keywords are capatalized. SQL statements are terminated with a ;.
SELECT
Select all columns from a table:
SELECT * FROM Authors;
Select a single column from a table:
SELECT LastName FROM Authors;
Retrieve a list of all different values in a column:
SELECT DISTINCT LastName FROM Authors;
WHERE
Retrieve row where column matches:
SELECT * FROM Authors WHERE Language = 'English';
Retrieve a row where the column does not match:
SELECT * FROM Authors WHERE NOT Language = 'English';
Multiple conditions where all must match:
SELECT * FROM Authors
WHERE Language = 'English'
AND LastName = 'Hardinge';
Multiple conditions where either must match:
SELECT * FROM Authors
WHERE Language = 'English'
OR LastName = 'Garner';
ORDER BY
Order the result:
SELECT * FROM Authors ORDER BY LastName;
Return the result in reverse order:
SELECT * FROM Authors ORDER BY LastName DESC;
Order the result alphabetically by last name then language:
SELECT * FROM Authors ORDER BY LastName, Language;
INSERT
Insert a new row into a table:
INSERT INTO Authors (
LastName,
Language
)
VALUES (
'Jansson',
'Swedish'
);
NULL
Select a row with an empty column:
SELECT * FROM Authors WHERE DateOfDeath IS NULL;
Select a row where a specified column is not empty:
SELECT * FROM Authors WHERE DateOfDeath IS NOT Null;
UPDATE
Update a column in all rows:
UPDATE Authors SET Published = 'true';
Update only specific columns (I’m stretching the example a bit now):
UPDATE Authors SET Read = 'true' WHERE Published = 'true';
Update multiple columns:
UPDATE Authors
SET Read = 'true',
Published = 'true',
WHERE AuthorId = 23;
DELETE
Delete rows:
DELETE FROM Authors WHERE Published = 'false';
Delete all rows:
DELETE FROM Authors;
Functions
Select the smallest value:
SELECT MIN(Titles) FROM Authors;
Select the highest value:
SELECT MAX(Titles) FROM Authors;
Return the count of rows that have Titles set to 3:
SELECT COUNT(*) FROM Authors WHERE Titles = 3;
Calculate an average:
SELECT AVG(Books) FROM Authors;
Sum all the values of a column:
SELECT SUM(Books) FROM Authors;
Select all the records where the given column starts with a given letter:
SELECT Authors WHERE LastName LIKE 'A%';
Select all the records where the given column ends with a letter:
SELECT Authors WHERE LastName LIKE '%s';
Select all the records where a given column contains a given letter:
SELECT Authors WHERE LastName LIKE '%d';
Select all rows where a given column starts and ends with the given letters:
SELECT Authors WHERE LastName LIKE 'A%s';
Select all rows where a given column does not start with the given letter:
SELECT Authors WHERE LastName NOT LIKE `A%`;
Select all rows where the second letter of a column is a given letter:
SELECT Authors WHERE LastName LIKE ' l%';
Select all rows where the first letter of a column begins with one of the given letters:
SELECT Authors WHERE LastName LIKE '[ABC]%';
Select all rows where the first letter of a column falls within an inclusive range:
SELECT Authors WHERE LastName LIKE '[A-F]%';
Select all rows where the first letter of a column does begin with one of:
SELECT Authors WHERE LastName LIKE '[!A-F]%';
IN
Select rows where a column matches:
SELECT Authors WHERE LastName IN ('Aldiss', 'Ballard');
Select rows where a country does not match:
SELECT Authors WHERE LastName NOT IN ('Archer', 'Jonker');
Select all rows where column value falls within an numerical range:
SELECT * Authors WHERE Titles BETWEEN 5 AND 10
Select all rows where column value does not fall within a numerical range:
SELECT * Authors WHERE Titles NOT BETWEEN 5 AND 10
Select all rows where the column name falls within an alphabetical range:
SELECT * Authors WHERE LastName BETWEEN 'Cooper' AND 'Masefield';
ALIAS
Display column under a different name:
SELECT LastName, Books, Language AS Lang FROM Authors;
Display a table under a different name:
SELECT * FROM Authors AS Writers;
JOIN
A JOIN is done ON something.
Join two tables using the AuthorID field:
SELECT *
FROM Publishers
LEFT JOIN Authors
ON Publishers.AuthorID=Authors.AuthorID;
Select intersection:
SELECT *
FROM Publishers
INNER JOIN Authors
ON Publishers.AuthorID = Authors.AuthorID;
Select all of one table plus all that matches in another:
SELECT *
FROM Publishers
RIGHT JOIN Authors
ON Publishers.AuthorID = Authors.AuthorID;
GROUP BY
List the number of authors by language:
SELECT COUNT(AuthorID)
Language
From Authors
GROUP BY Language;
List number of authors by language, ordered by language with most authors first:
SELECT COUNT(AuthorID)
Language
FROM Authors
GROUP BY Language
ORDER BY COUNT(AuthorID) DESC;
SQL
Create a db:
CREATE DATABASE db;
Drop a db:
DROP DATABASE db;
Create a table:
CREATE TABLE Authors (
AuthorID int,
LastName varchar(255),
Language varchar(255)
);
Drop a table:
DROP TABLE Authors;
Delete all rows within a table:
TRUNCATE TABLE Authors;
Add a column:
ALTER TABLE Authors ADD Birthday DATE;
Delete a colum:
ALTER TABLE Authors DROP COLUMN Birthday;