【My Study Note】Codes of MySQL
Code
Get Dara from the database
Select name, price FROM purchases;In this case, they are getting the date from the “name” and “price” column, and that table name is “purchases”.
Get more specific data from the database
SELECT *
FROM purchases
WHERE category = "food";In this case, they are getting the data where the value of “food column” is “food”.
Data types of date
"2018-10-30"The format is year, month, day.
Get the data containing some key words
// Get the keywords containing "pudding"
WHERE name LIKE "%pudding%";
// Get the value that starts with keywords "pudding"
WHERE name LIKE "pudding%";
// Get the value that ends with keywords "pudding"
WHERE name LIKE "%pudding";Get the data that doesn’t satisfy the condition
WHERE NOT price > 10;
WHERE NOT name LIKE "%pudding%";NULL
// Get rows with null value
WHERE price IS NULL;
// Get rows without null value
WHERE price IS NOT NULL;
// THIS CANNOT BE USED
WHERE price = NULL;NULL means there’s no date stored in there.
Add 2 conditions
AND
WHERE name = "harry"
AND gender = "man";OR
WHERE name = "harry"
OR name = "Taylor";Put the collected data in order
// desending such as from 100 to 1
SELECT * FROM purchases WHERE condition
ORDER BY price DESC;
// ascending such as from 1 to 100
ORDER BY price ASC;Also, it can be used with WHERE Since “ORDER BY” is used at the end of an SQL statement.
Setting the maximum number of results
SELECT * FROM purchases WHERE condition
LIMIT 5;You can set the number of data to get such as there are 10 data which satisfies the condition, but you use “limit” when you only need a limited number of results but not the whole.
Omitting duplicate data
SELECT DISTINCT (name)
FROM purchases;In here, they omit the duplicated name.
Arithmetic Operator
SELECT name, price, price * 1.09
FROM purchases;SUM
SELECT SUM (price)
FROM purchases
WHERE character_name = "Ken the Ninja";AVG
SELECT AVG (price)
FROM purchases
WHERE character_name = "Ken the Ninja";COUNT
SELECT COUNT(*)
FROM purchases
WHERE character_name = "Ken the Ninja";You can count the number of rows by using “COUNT”. But remember, they don’t count the null value. If you want to count including the null value, you should use “COUNT(*)”.
MAX & MIN
SELECT MAX (price)
FROM purchases
WHERE character_name = "Ken the Ninja";You can get the maximum value and minimum value by using them.
Grouping the acquired rows
// THIS CAN BE USED
SELECT SubmitEvent(price), purchased_at
FROM purchases
GROUP BY purchased_at;
// THIS CANNOT BE USED
SELECT name, purchased_at
FROM purchases
GROUP BY purchased_at;When “GROUP BY” is used, only the column name specified in GROUP BY and the aggregate function can be used in SELECT.
You can use it with multiple columns
SELECT SubmitEvent(price), purchased_at, character_name
FROM purchases
GROUP BY purchased_at, character_name;HAVING
SELECT SUM (price), purchased_at
WHERE purchases
GROUP BY purchased_at
HAVING SUM (price) > 10;For this, you cannot use “WHERE” but you have to use “HAVING”.
WHY? (Because of the order that codes would be run)
| Search | WHERE |
| Grouping | GROUP BY |
| Function | COUNT, SUM, AVG, MAX, MIN |
| Having | HAVING |
THE CODE WOULD BE RUN FROM THE TOP TO BOTTOM. That’s why, you cannot specify the condition with using “WHERE”. Because “WHERE” would be conducted before the “GROUP BY”.
Subqueries
SELECT name
FROM players
WHERE goals > (
SELECT goals
FROM players
WHERE name = "Will"
);When an SQL statement includes subqueries, the outer SQL statement will be executed after the subqueries.
Making the Data Easier to Read
// Use AS to label the column "180 cm or taller"
SELECT name as "180 cm or taller"
FROM players
WHERE height >= 180;Combining Tables
SELECT *
FROM players
// Add the table name after the JOIN to combine the table
JOIN countries
// Add a join condition
ON country_id = countries.id;When an SQL statement contains a JOIN clause, the JOIN is performed first. Next, the SELECT is performed on the joined table. Additionally, you can combine more than 2 tables by using “JOIN ON”.
Including the NULL value when Combining Tables
When you combine the table with using the “JOIN ON”, if the column was the null value (the condition part), that column wouldn’t be joint and skipped. However, there’s a way to combine the NULL value column as well. That’s the “LEFT JOIN ON”.
Between A AND B
WHERE price BETWEEN 20 AND 30;Get the value between A and B.