SELECT COUNT(order_id), /*행의 개수가 몇개인가를 알려줌?*/ SUM(stock), MIN(stock), MAX(stock) AS max_stock, SUM(stock) /COUNT(1), AVG(stock) FROM order_mst GROUP BY product_name HAVING max_stock >49; SELECT product_name, price * stock AS total_price FROM order_mst; 각 행안에서 가격과 주문량을 곱한다. SELECT product_name, SUM(price * stock) AS total_price /*상품가격에 대한 총합?.*/ FROM order_mst GROUP BY product_name; SELECT COUNT(..

groupby "중복되지 않은 정보를 보여주는 것" 상품 기준으로 SELECT product_name, COUNT(order_id) /*행의 개수가 몇개인가? 를 알려줌 소괄호 안에 뭐가 들어가던지 상관없다.*/ FROM order_mst GROUP BY product_name; SELECT product_name, COUNT(order_id), SUM(stock), MIN(stock), MAX(stock) FROM order_mst GROUP BY product_name; SELECT product_name, COUNT(order_id), /*행의 개수가 몇개인가를 알려줌?*/ SUM(stock), MIN(stock), MAX(stock), SUM(stock) /COUNT(1), AVG(stock) F..

subject_mst INSERT INTO subject_mst VALUE (0, 'java'), (0, 'c'), (0, '영어'), (0, '수학'); student_mst INSERT INTO student_mst VALUE (0, 'a'), (0, 'b'), (0, 'c'), (0, 'd'); major_mst INSERT INTO major_mst VALUE (0,'컴공'), (0,'디자인'), (0,'경영'), (0,'전자'); major_dtl INSERT INTO major_dtl VALUE (0,500), (0,400), (0,300), (0,350); student_number_dtl INSERT INTO student_number_dtl VALUE (0,1), (0,2), (0,3),..

학생 이승아 추가, 과목 web 추가 /*학생 기준*/ SELECT sm.id AS '순번', sbm.name AS '과목명', sm.name AS '수강생명', em.name AS '담당자명', stm.name AS '상태' FROM student_mst sm LEFT OUTER join course_mst cm ON(cm.student_id = sm.id) LEFT OUTER JOIN subject_mst sbm ON(sbm.id = cm.subject_id) LEFT OUTER JOIN student_dtl sd ON(sd.id = sm.id) LEFT OUTER JOIN emp_mst em ON(em.id = sd.emp_id) LEFT OUTER JOIN status_mst stm ON(stm...

테이블 만들기 course_mst INSERT INTO course_mst VALUES (0,1,1,1), (0,2,1,2), (0,3,1,2), (0,1,2,2), (0,3,2,1), (0,1,3,1), (0,1,4,2); status INSERT INTO status_mst VALUES (0,'수강'), (0,'수료'); emp_mst INSERT INTO emp_mst VALUES (0,'문성현'), (0,'윤대휘'), (0,'문자영'), (0,'정규민'); student_mst INSERT INTO student_mst VALUES (0,'신경수'), (0,'고희주'), (0,'장건녕'), (0,'문승주'); subject_mst INSERT INTO subject_mst VALUES (0,'ja..

Update UPDATE student_mst SET score = 80 WHERE `name` = '박준현'; update 실습 /*2학년 중에서 성적이 80~90 사이인 학생들의 점수를 100으로 바꿔라*/ UPDATE student_mst SET score = 100 WHERE student_year = 2 AND score BETWEEN 80 AND 90; 결과 Delete 손떨리는 작업. DELETE FROM student_mst WHERE score < 80; 결과