230113 TIL

Today I learned

SQL

MySQL Query

Select * from table

Good syntax to use with where clause.

1. where column != value

• find except the value

2. where column Between A and B

• find the values Between A and B

3. where column in -> where week in (1,2)

• find various values from column by in

4. where column like '%google.com' -> % mean is skip prefix of value.
   4-1 where column like 'a%t' -> You want to find including first letter 'a' and last letter 't'

• % Excludes the value before % and finds the value after it.

Limit: Bring only some data.
limit 5 -> find only five values from table.

Distinct : Excluding redundant data is imported.
select distinct(column) from table
select distinct(payment_method) from order

Count : Data can be counted in numbers.
select count(*) from order where payment_method = 'CARD'

Use Distinct and count together
1. select distinct(lastname) from users -> find remove redundant data from column

2. select count(distinct(lastname)) from users -> find count numbers which remove redundant data from column

Statistics

Group by
select lastname,count(*)
from users
where email = '%gmail.com'
group by lastname

To define GROUP BY in one word:

"showing non-duplicated information"
is.

Looking at the usage example first, (usually used with group functions)

1. I want to draw a melon chart. (→ Descending order of streaming count by music source)

2. I want to know the best product among the products sold. (→ Order quantity by product in descending order)

3. Show the number of comments on the free bulletin board list. (→ number of comments per post)

4. I would like to know the number of real estate listings by region. (→ Number of listings by region)

-SELECT week, MIN(likes) from checkins c group by week
-SELECT week, MAX(likes) from checkins c group by week
-SELECT week, round(AVG(likes),2) from checkins c group by week
-SELECT week, SUM(likes) from checkins c group by week

Order by

ORDER BY is a SQL statement to sort data by a specified column. If 'ORDER BY column name' is used, the result is output by sorting the column in ascending order.

default = asc
reverse = desc

select payment_method ,COUNT(*)  from orders o 
where course_title = "웹개발 종합반"
group by payment_method
order by COUNT(*)