Tables in database (mysql)
- member table
id | name | height | weight | age | job_id |
---|---|---|---|---|---|
1 | sato | 170.2 | 65.2 | 60 | 5 |
2 | suzuki | 151.5 | 50.3 | 53 | 6 |
3 | takahashi | 182.1 | 85.1 | 31 | 8 |
4 | tanaka | 163.5 | 70.6 | 36 | 3 |
5 | watanabe | 157.8 | 55.8 | 62 | 3 |
6 | ito | 173 | 65.3 | 75 | 7 |
7 | yamamoto | 166.4 | 49.1 | 25 | 2 |
8 | nakamura | 144.1 | 56.9 | 45 | 4 |
9 | kobayashi | 168.7 | 90.1 | 38 | 7 |
10 | kato | 178.6 | 78.5 | 26 | 1 |
- job table
id | name | salary |
---|---|---|
1 | doctor | 1232 |
2 | lawyer | 1028 |
3 | engineer | 515 |
4 | accountant | 1024 |
5 | pharmacist | 542 |
6 | Childminder | 341 |
7 | teacher | 1050 |
8 | bus driver | 361 |
π Group By
I need to know how many members belong to each job_id
SELECT job_id, count(*) FROM members GROUP BY job_id;
job_id | count(*) |
---|---|
1 | 1 |
2 | 1 |
3 | 2 |
4 | 1 |
5 | 1 |
6 | 1 |
7 | 2 |
8 | 1 |
By the way, You can't use "as" to column which is used by GROUP BY
-- Error !!
SELECT job_id as 'This is job id', count(*) FROM members GROUP BY job_id;
π Why? Because SELECT SQL order is like that
FROM
β
GROUP BY
(I don't know what is 'This is job id', nobody defined it yet)
β
SELECT
(Here define job_id "This is job id")
βΌ I wrote article about SELECT ORDER
π Having
We created this record by GROUP BY
job_id | count(*) |
---|---|
1 | 1 |
2 | 1 |
3 | 2 |
4 | 1 |
5 | 1 |
6 | 1 |
7 | 2 |
8 | 1 |
now We need to know job_id which has two members
SELECT job_id, COUNT(*) FROM members GROUP BY job_id HAVING COUNT(*) = 2;
π€© Tadaaam
job_id | count(*) |
---|---|
3 | 2 |
7 | 2 |
β But it is a little hard to see right? Because there is no "job name"
π Join
If you know job name as well, you need to integrate job table
SELECT job_id, jobs.name, COUNT(*) FROM members INNER JOIN jobs ON jobs.id=members.job_id GROUP BY job_id HAVING COUNT(*) = 2;
job_id | name | count(*) |
---|---|---|
3 | engineer | 2 |
7 | teacher | 2 |
(We will use new table from here)
- products table
id | name | category | selling_price | cost_price | registration_date |
---|---|---|---|---|---|
1 | t-shirt | cloth | 1500 | 500 | 2018-4-5 |
2 | ballpoint pen | office | 100 | 30 | 2018-6-3 |
3 | knife | kitchen | 1200 | 400 | 2018-3-30 |
4 | y-shirt | cloth | 2300 | 300 | 2018-7-23 |
5 | copy paper | office | 500 | 200 | 2018-2-19 |
6 | pot | kitchen | 5900 | 2000 | 2018-11-26 |
7 | box cutter | office | 130 | 50 | 2018-5-11 |
8 | printer | office | 9800 | 2800 | 2019-1-12 |
π View
Save SELECT SQL which we often use and we can use the same as table, like virtual table.
for example, maybe you would often search number of products of category group
SELECT category, COUNT(*) FROM Products GROUP BY category;
π But all the time you have to write in down, it's not weird if your fingers burn out.
in like this case, you should use VIEW!!
CREATE VIEW ProductSum (category, count_product)
AS
SELECT category, COUNT(*) FROM Products GROUP BY category;
How should you get record? it's so simple
SELECT category, count_product FROM ProductSum;
π Difference between View and Table
view | table |
---|---|
save SELECT SQL | save real records |
- reduce data because view has just SELECT SQL
- flexible for changing database content
π Sub query
Single use version of view
view | sub query |
---|---|
save SELECT SQL | put SELECT SQL directly into FROM |
SELECT category, count_product
FROM (SELECT category, COUNT(*) AS count_product
FROM Products GROUP BY category)
AS ProductSum;
π Scala sub query
π What is scala?
single value like char,int,float.
Not like Array,Object
π For example
SELECT name, selling_price FROM Products
WHERE selling_price > (SELECT AVG(selling_price) FROM Products);
SELECT AVG(selling_price) FROM Products
= 2678.75, so it is scala value.
π Case
We know sale product's price informations now.
βΌ conditions
more than 5000 | more than 1000 | other |
---|---|---|
20% discount | 10% discount | not change |
SELECT name,
CASE WHEN price_on_sale >= 5000 THEN price_on_sale * 0.8
WHEN price_on_sale >= 1000 THEN price_on_sale * 0.9
ELSE price_on_sale
END AS "sales price"
FROM Products;
βΌ result
name | sales price |
---|---|
t-shirt | 1350 |
ballpoint pen | 100 |
knife | 1080 |
y-shirt | 2070 |
copy paper | 500 |
pot | 4720 |
cutter | 130 |
printer | 7840 |
π Thank you for reading
Top comments (0)