Last active
April 1, 2021 13:00
-
-
Save bradsi/42f4116dd1758f9717dba2719dd2c471 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- select all data | |
| SELECT * FROM users; | |
| -- select first two users | |
| SELECT * FROM users LIMIT 2; | |
| -- select specific columns | |
| SELECT first_name from users; | |
| -- alias columns | |
| SELECT id AS 'ID', first_name AS 'Name' FROM users; | |
| -- order how data is returned | |
| -- ordered by ascending order by default but can specify descending as below | |
| SELECT * FROM users ORDER BY name DESC; | |
| -- distinct select, returns only unique values | |
| SELECT DISTINCT last_name FROM users; | |
| -- select statement with where clause, returns all rows where user has | |
| -- last name of smith | |
| SELECT * FROM users | |
| WHERE last_name = 'Smith'; | |
| -- select statement with like, returns all rows where first name includes sh | |
| SELECT * FROM users | |
| WHERE first_name LIKE '%sh%'; | |
| -- can also use AND, OR with WHERE | |
| SELECT * FROM users | |
| WHERE first_name LIKE '%sh%' OR last_name = 'Smith'; | |
| -- BETWEEN | |
| SELECT * FROM posts | |
| WHERE post_id BETWEEN 1 AND 10; | |
| -- JOIN (INNER JOIN by default, only returns the values that have a match) | |
| -- combines the users and posts table | |
| SELECT * FROM users | |
| JOIN posts ON users.id = posts.user_id; | |
| -- LEFT JOIN (lists everything on the left i.e. first table) | |
| -- lists all the users (even if they have no posts) and their posts | |
| SELECT * FROM users | |
| LEFT JOIN posts ON users.id = posts.user_id; | |
| -- RIGHT JOIN (lists everything on the right i.e. second table) | |
| -- lists all the posts (even if they are not linked to a user) and the user | |
| -- probably won't be used very often | |
| SELECT * FROM users | |
| RIGHT JOIN posts ON users.id = posts.user_id; | |
| -- AGGREGATE FUNCTIONS | |
| SELECT AVG(age) FROM users; | |
| SELECT COUNT(?) FROM users; | |
| SELECT user_id, COUNT(user_id) FROM posts | |
| GROUP BY user_id; | |
| -- ALIAS | |
| SELECT f.first_name AS first_name, COUNT(p.id) AS num_posts | |
| FROM users AS f | |
| LEFT JOIN posts AS p ON f.id = p.user_id | |
| GROUP BY f.id; | |
| -- ALIAS with HAVING, returns only results that have exactly 1 post | |
| SELECT f.first_name AS first_name, COUNT(p.id) AS num_posts | |
| FROM users AS f | |
| LEFT JOIN posts AS p ON f.id = p.user_id | |
| GROUP BY f.id | |
| HAVING num_posts = 1; | |
| -- ALIAS with HAVING AND WHERE | |
| SELECT f.first_name AS first_name, COUNT(p.id) AS num_posts | |
| FROM users AS f | |
| LEFT JOIN posts AS p ON f.id = p.user_id | |
| WHERE f.id = 3; | |
| GROUP BY f.id | |
| HAVING num_posts = 1; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment