CREATE TABLE
CREATE TABLE table_name (
column_1 data_type,
column_2 data_type,
column_3 data_type
);
INSERT
The INSERT statement inserts a new row into a table.
INSERT INTO celebs (id, name, age)
VALUES (1, 'Justin Bieber', 22);
SELECT
SELECT statements are used to fetch data from a database. In the statement below, SELECT returns all data in the name column of the celebs table.
SELECT statements always return a new table called the result set.
SELECT name FROM celebs;
SELECT column1, column2
FROM table_name;
AS
AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes. Here we renamed the name column as Titles.
SELECT name AS 'Titles'
FROM movies;
- Although it’s not always necessary, it’s best practice to surround your aliases with single quotes.
- When using AS, the columns are not being renamed in the table. The aliases only appear in the result.
Distinct
DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).
SELECT tools
FROM inventory;
// output might be
tools
Hammer
Nails
Nails
Nails
SELECT DISTINCT tools
FROM inventory;
// output might be
tools
Hammer
Nails
where
We can restrict our query results using the WHERE clause in order to obtain only the information we want.
WHERE clause filters the result set to only include rows where the following condition is true.
like (1)
LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.
The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.
SELECT *
FROM movies
WHERE name LIKE 'Se_en';
lile (2)
% is a wildcard character that matches zero or more missing letters in the pattern. For example:
A% matches all movies with names that begin with letter ‘A’
%a matches all movies that end with ‘a’
SELECT *
FROM movies
WHERE name LIKE 'A%';
// any movie that contains the word ‘man’ in its name will be returned in the result.
SELECT *
FROM movies
WHERE name LIKE '%man%';
LIKE is not case sensitive. ‘Batman’ and ‘Man of Steel’ will both appear in the result of the query above.
SELECT *
FROM movies
WHERE name LIKE 'The %';
There is also a space in between because we don’t want words like ‘There’, ‘They’, etc.
SELECT *
FROM nomnom
WHERE cuisine = 'Italian'
AND price LIKE '%$$$%';
SELECT *
FROM nomnom
WHERE cuisine = 'Italian'
AND price = '$$$';
NULL
It is not possible to test for NULL values with comparison operators, such as = and !=.
Instead, we will have to use these operators:
- IS NULL
- IS NOT NULL
SELECT name
FROM movies
WHERE imdb_rating IS NOT NULL;
BETWEEN
The BETWEEN operator is used in a WHERE clause to filter the result set within a certain range. It accepts two values that are either numbers, text or dates.
// BETWEEN two numbers is inclusive of the second number.
SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;
In this statement, BETWEEN filters the result set to only include movies with names that begin with the letter ‘A’ up to, but not including ones that begin with ‘J’.
BETWEEN is case-sensitive
SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';
AND
AND operator displays a row if all the conditions are true.
OR
OR operator displays a row if any condition is true.
Order By
We can sort the results using ORDER BY, either alphabetically or numerically
SELECT *
FROM movies
ORDER BY name;
SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;
- DESC is a keyword used in ORDER BY to sort the results in descending order (high to low or Z-A).
- ASC is a keyword used in ORDER BY to sort the results in ascending order (low to high or A-Z).
### Limit
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. This saves space on our screen and makes our queries run faster.
LIMIT always goes at the very end of the query. Also, it is not supported in all SQL databases.
SELECT *
FROM movies
LIMIT 10;
ALTER
The ALTER TABLE statement adds a new column to a table. You can use this command when you want to add columns to a table. The statement below adds a new column twitter_handle to the celebs table.
NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL (∅) values for twitter_handle.
ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;
Update
use the UPDATE statement when you want to change existing records.
UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id = 4;
Delete
The DELETE FROM statement deletes one or more rows from a table
DELETE FROM celebs
WHERE twitter_handle IS NULL;
Constraints
They can be used to tell the database to reject inserted data that does not adhere to a certain restriction
- PRIMARY KEY columns can be used to uniquely identify the row.(具備唯一性)
- UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
- NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.
- DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.
sum up (1)
A statement is a string of characters that the database recognizes as a valid command.
- CREATE TABLE creates a new table.
- INSERT INTO adds a new row to a table.
- SELECT queries data from a table.
- ALTER TABLE changes an existing table.
- UPDATE edits a row in a table.
- DELETE FROM deletes rows from a table.
Case
- Each WHEN tests a condition and the following THEN gives us the string if the condition is true.
- The ELSE gives us the string if all the above conditions are false.
- The CASE statement must end with END.
SELECT name, CASE WHEN imdb_rating > 8 THEN 'Fantastic' WHEN imdb_rating > 6 THEN 'Poorly Received' ELSE 'Avoid at All Costs' END FROM movies;
SELECT name,
CASE
WHEN genre = 'romance' THEN 'Chill'
WHEN genre = 'comedy' THEN 'Chill'
ELSE 'Intense'
END AS 'Mood'
FROM movies
sum up (2)
- SELECT is the clause we use every time we want to query information from a database.
- AS renames a column or table.
- DISTINCT return unique values.
- WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
- LIKE and BETWEEN are special operators.
- AND and OR combines multiple conditions.
- ORDER BY sorts the result.
- LIMIT specifies the maximum number of rows that the query will return.
- CASE creates different outputs.
AGGREGATE FUNCTIONS
- COUNT(): count the number of rows
- SUM(): the sum of the values in a column
- MAX()/MIN(): the largest/smallest value
- AVG(): the average of the values in a column
- ROUND(): round the values in the column
Count
COUNT() is a function that takes the name of a column as an argument and counts the number of non-empty values in that column.
we want to count every row, so we pass * as an argument inside the parenthesis.
SELECT COUNT(*)
FROM table_name;
Sum
SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column.
SELECT SUM(downloads)
FROM fake_apps;
Max / Min
MAX() takes the name of a column as an argument and returns the largest value in that column. Here, we returned the largest value in the downloads column.
MIN() works the same way but it does the exact opposite; it returns the smallest value.
SELECT MAX(downloads)
FROM fake_apps;
Average
The AVG() function works by taking a column name as an argument and returns the average value for that column
SELECT AVG(downloads)
FROM fake_apps;
Round
By default, SQL tries to be as precise as possible without rounding. We can make the result table easier to read using the ROUND() function.
ROUND() function takes two arguments inside the parenthesis:
- a column name
- an integer
SELECT name, ROUND(price, 0)
FROM fake_apps;
// 小數點第二位
SELECT ROUND(AVG(price), 2)
FROM fake_apps;
Group By I
GROUP BY is a clause in SQL that is used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.
The GROUP BY statement comes after any WHERE statements, but before ORDER BY or LIMIT.
SELECT AVG(imdb_rating)
FROM movies
WHERE year = 1999;
SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2000;
SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2001;
We can use GROUP BY to do this in a single step:
SELECT year,
AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;
Group By II
Sometimes, we want to GROUP BY a calculation done on a column.
SQL lets us use column reference(s) in our GROUP BY that will make our lives easier.
- 1 is the first column selected
- 2 is the second column selected
- 3 is the third column selected
SELECT category,
price,
AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;
Having
SQL also allows you to filter which groups to include and which to exclude.
HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can be used with HAVING.
HAVING statement always comes after GROUP BY, but before ORDER BY and LIMIT.
- When we want to limit the results of a query based on values of the individual rows, use WHERE.
- When we want to limit the results of a query based on an aggregate property, use HAVING
SELECT year,
genre,
COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;
SELECT price,
ROUND(AVG(downloads)),
COUNT(*)
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 10;
sum up 3
- COUNT(): count the number of rows
- SUM(): the sum of the values in a column
- MAX()/MIN(): the largest/smallest value
- AVG(): the average of the values in a column
- ROUND(): round the values in the column
Aggregate functions combine multiple rows together to form a single value of more meaningful information.
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
- HAVING limit the results of a query based on an aggregate property.
note:how to hack hacker news 可以多做幾次
MULTIPLE TABLES
JOIN
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
- The third line uses JOIN to say that we want to combine information from orders with customers.
- The fourth line tells us how to combine the two tables. We want to match orders table’s customer_id column with customers table’s customer_id column.
The WHERE clause goes after the JOIN!
SELECT *
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';
Inner Joins
When we perform a simple JOIN (often called an inner join) our result only includes rows that match our ON condition.
SELECT COUNT(*)
FROM newspaper;
SELECT COUNT(*)
FROM online;
SELECT COUNT(*)
FROM newspaper
JOIN online
ON newspaper.id = online.id
Left Joins
What if we want to combine two tables and keep some of the un-matched rows?
A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table.
codecademy-LEFT JOINS
SELECT * FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id;
SELECT * FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id
WHERE online.id IS NULL;
Primary Key vs Foreign Key
Primary keys have a few requirements:
- None of the values can be NULL.
- Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table).
- A table can not have more than one primary key column.
When the primary key for one table appears in a different table, it is called a foreign key.
So customer_id is a primary key when it appears in customers, but a foreign key when it appears in orders.
Why is this important? The most common types of joins will be joining a foreign key from one table with the primary key from another table. For instance, when we join orders and customers, we join on customer_id, which is a foreign key in orders and the primary key in customers.
Cross Join
Sometimes, we just want to combine all rows of one table with all rows of another table.
SELECT shirts.shirt_color,
pants.pants_color
FROM shirts
CROSS JOIN pants;
Notice that cross joins don’t require an ON statement. You’re not really joining on any columns!
A more common usage of CROSS JOIN is when we need to compare each row of a table to a list of values.
Union
Sometimes we just want to stack one dataset on top of the other. Well, the UNION operator allows us to do that.
SELECT *
FROM table1
UNION
SELECT *
FROM table2;
SQL has strict rules for appending data:
- Tables must have the same number of columns.
- The columns must have the same data types in the same order as the first table.
With
Often times, we want to combine two tables, but one of the tables is the result of another calculation.
- The WITH statement allows us to perform a separate query (such as aggregating customer’s subscriptions)
- previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause
- We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table)
Essentially, we are putting a whole first query inside the parentheses () and giving it a name. After that, we can use this name as if it’s a table and write a new query using the first query.
WITH previous_results AS (
SELECT ...
...
...
...
)
SELECT *
FROM previous_results
JOIN customers
ON _____ = _____;
Do not include ; inside of the () of your WITH statement.
WITH previous_query AS (
SELECT customer_id,
COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id
)
SELECT customers.customer_name, previous_query.subscriptions
FROM previous_query
JOIN customers
ON previous_query.customer_id = customers.customer_id;
sum up (3)
- JOIN will combine rows from different tables if the join condition is true.
- LEFT JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
- Primary key is a column that serves a unique identifier for the rows in the table. Foreign key is a column that contains the primary key to another table.
CROSS JOIN lets us combine all rows of one table with all rows of another table. - UNION stacks one dataset on top of another.
- WITH allows us to define one or more temporary tables that can be used in the final query.
LEARNING FROM
codecademy Learn SQL