# 별칭, 조건, 조인, 정렬
SELECT A.FLAVOR FROM FIRST_HALF A, ICECREAM_INFO B
where A.FLAVOR = B.FLAVOR and A.TOTAL_ORDER > 3000 AND B.INGREDIENT_TYPE = 'fruit_based'
ORDER BY A.TOTAL_ORDER DESC;
# 이중정렬, ifnull
SELECT PT_NAME, PT_NO, GEND_CD, AGE, ifnull(TLNO, 'NONE') as TLNO
from patient where age<=12 and gend_cd='W' order by age desc, pt_name asc
# date_format(%H=시간 %i=분 %S=초)
SELECT dr_name, dr_id, mcdp_cd, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') from doctor
where (mcdp_cd='cs' or mcdp_cd='gs') order by hire_ymd desc, dr_name asc
# date_format(year - %y는 뒤에 두글자 %Y는 네글자), where절안에서 date활용
SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH,'%Y-%m-%d')
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3 AND TLNO IS NOT NULL AND GENDER ='W' ORDER BY MEMBER_ID;
# like (%아무거나 _한글자)
SELECT factory_id, factory_name, address from food_factory
where address like '강원도%' order by factory_id asc
# round, group by
SELECT I.rest_id, I.rest_name, I.food_type, I.favorites, I.address,
round(avg(R.review_score),2) as SCORE
from rest_info I, rest_review R where I.rest_id = R.rest_id and I.address like '서울%'
group by R.rest_id order by SCORE desc, I.favorites desc
# 2컬럼 group by, having, count
SELECT user_id, product_id from ONLINE_SALE group by user_id, product_id
having count(*)>1 order by user_id asc, product_id desc # group by 묶인 row 2개 이상
# union all ( 중복도 포함 ), union( 중복 제거 ) 여러 테이블의 같은 성격의 값 한번의 쿼리로 추출
# 대응하는 필드의 이름이 같아야함 아니면 AS 를 사용 같게 만듬. 대응되는 각 필드의 타입도 같아야 함.
select date_format(sales_date, '%Y-%m-%d') as sales_date, product_id, user_id, sales_amount
from online_sale where sales_date like '2022-03%' #date format 문자열처럼 like 사용 가능
union all
select date_format(sales_date, '%Y-%m-%d') as sales_date, product_id, NULL as user_id, sales_amount
from offline_sale where sales_date like '2022-03%'
order by sales_date, product_id, user_id #union 된거 정렬
# != 사용 가능 (== not intake_condition='aged')
SELECT animal_id, name from animal_ins where intake_condition != 'aged' order by animal_id
# limit 1 ( 맨위 1줄, 2면 2줄, 2,6이면 3~8줄)
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME LIMIT 1
# between( 이상 이하 )
SELECT count(*) as users from user_info where joined like '2021%' and age between 20 and 29]
# subquery, max - 최대, min - 최소, avg - 평균,
select * from food_product where price = (SELECT max(price) from food_product)
# distinct, is not null
SELECT count(distinct(name)) from animal_ins where name is not null
# in, subquery, max / (group by 쓰고 select에 다른 컬럼 사용 못함)
select S.CATEGORY, S.MAX_PRICE, A.PRODUCT_NAME from food_product A,
(SELECT category,max(price) as max_price from food_product
where category in ('과자', '국', '김치', '식용유') group by category) S
where S.category=A.category and S.max_price = A.price order by max_price desc
# sum
SELECT ingredient_type,sum(total_order) as total_order from first_half A, icecream_info B
where A.flavor = B.flavor group by ingredient_type order by total_order
#
SELECT date_format(datetime, '%H') as Hour,count(*) from animal_outs
group by Hour having Hour between 9 and 19 order by Hour
#
SELECT Year(B.sales_date), Month(B.sales_date), A.gender, count(distinct(A.user_id))
from user_info A, online_sale B where A.user_id = B.user_id and A.gender is not null
group by Year(B.sales_date), Month(B.sales_date), A.gender
order by Year(B.sales_date), Month(B.sales_date), A.gender
# 지역변수
SET @hour := -1; -- 변수 선언
SELECT (@hour := @hour + 1) as HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS WHERE @hour < 23
#truncate, case when then else end
SELECT (
CASE
WHEN PRICE < 10000 THEN 0
ELSE TRUNCATE(PRICE, -4)
END
) AS PRICE_GROUP , COUNT(PRODUCT_ID)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
# order by select에 없는거 사용 가능
SELECT A.flavor
from (select flavor, sum(total_order) as total_order from first_half group by flavor) A,
(select flavor, sum(total_order) as total_order from july group by flavor) B
where A.flavor=B.flavor order by (A.total_order+B.total_order) desc limit 3
# subquery
SELECT member_name, review_text, date_format(review_date, '%Y-%m-%d')
from member_profile, rest_review,
(select member_id,count(*) from rest_review group by member_id order by count(*) desc limit 1) A
where member_profile.member_id=rest_review.member_id and rest_review.member_id = A.member_id
order by date_format(review_date, '%Y-%m-%d'), review_text
# select에서 계산
SELECT A.product_id, product_name, price * sum(amount) as total_sales
from food_product A, food_order B where A.product_id=B.product_id
and PRODUCE_DATE like '2022-05%'
group by A.product_id order by total_sales desc, A.product_id asc
# outer join ( left 왼쪽 기준, right 오른쪽 기준 맞는거없으면 나머지는 null )
SELECT B.animal_id, B.name
from animal_ins A right outer join animal_outs B on A.animal_id= B.animal_id
where A.animal_id is null
# level 5
SELECT year(C.sales_date), month(C.sales_date),count(distinct(A.user_id)),
round(count(distinct(A.user_id))/c_total,1)
from (select * from user_info where joined like '2021%') A, online_sale C,
(select count(*) as c_total from user_info where joined like '2021%') B
where A.user_id= C.user_id group by year(C.sales_date), month(C.sales_date)
order by year(C.sales_date), month(C.sales_date)
# case(select에서 변수조건달아 컬럼 추가) - case when then else end as
SELECT order_id, product_id, date_format(out_date, "%Y-%m-%d") as out_date,
case when out_date <= "2022-05-01" then "출고완료" when out_date > "2022-05-01" then "출고대기"
else "출고미정" end as 출고여부 from food_order
#datediff(date차이)
SELECT I.ANIMAL_ID, I.NAME from ANIMAL_INS I, ANIMAL_OUTS O where I.ANIMAL_ID = O.ANIMAL_ID
and O.DATETIME is not null order by datediff(I.DATETIME, O.DATETIME) limit 2
#left(str, n) 왼쪽 문자n개 -> 문자열
SELECT left(PRODUCT_CODE,2) as CATEGORY, COUNT(*) as PRODUCTS
FROM PRODUCT GROUP BY CATEGORY ORDER BY CATEGORY
studying