SQl 기본 문법

-- SELECT ~ FROM ~ WHERE


USE market_db;

USE sys;
SELECT addr, height, debut_date
FROM member

-- 별칭을 부여 해보자
USE market_db;

USE sys;
SELECT height 키, debut_date "데뷔 일자", addr
FROM member

-- 키가 165 이상이고 member 6이상 초과인 데이터들

USE market_db;

USE sys;
SELECT * FROM member;

USE market_db;
SELECT * FROM member;

SELECT * FROM market_db.member;

SELECT mem_name FROM member;

SELECT mem_name, height, mem_number
	FROM member
    WHERE height >= 165 AND mem_number > 6;

-- OR을 써보자

SELECT mem_name, addr
		FROM member
        WHERE addr = '경기' OR addr = '전남' OR addr = '경남';
        
        
'에이핑크', '경기'
'블랙핑크', '경남'
'잇지', '경남'
'마마무', '전남'
'여자친구', '경기'



-- IN을 쓰면?
SELECT mem_name, addr
		FROM member
        WHERE addr IN('경기','전남','경남');
        
        
-- 앞에 '우'자 들어가는 회원은?
SELECT *
		FROM member
        WHERE mem_name LIKE '우%';
        
        
-- xx핑그인 회원은?
SELECT *
		FROM member
        WHERE mem_name LIKE '__핑크';

 

SELECT 문을 좀 더 깊게 알아보자

-- ORDER BY 절

SELECT 열_이름
	FROM 테이블_이름
    WHERE 조건식
    GROUP BY 열_이름
    HAVING 조건식
    ORDER BY 열_이름
    LIMIT 숫자
    
    
    
-- 데뷔 일자 신입부터
SELECT mem_id, mem_name, debut_date
			FROM member
            ORDER BY debut_date DESC;
            
            
            
-- 에러
SELECT mem_id, mem_name, debut_date, height
	FROM member
    ORDER BY height DESC
    WHERE height >= 164; -- 오류 발생
    
    
    
    
    
-- ORDER BY 조건문 여러개
SELECT mem_id, mem_name, debut_date, height
	FROM member
    WHERE height >= 164
    ORDER BY height DESC, debut_date ASC;
    
    
    
    
    
-- member중에서 앞에 3개를 보자
SELECT * 
	FROM member
    LIMIT 3;
    


-- 데뷔일자가 빠른 회원 3명을 보자.
SELECT mem_name, debut_date
	FROM member
    ORDER BY debut_date
    LIMIT 3;
    
    
-- 데뷔일자가 3등 부터 2개를 볼려면
SELECT mem_name, debut_date
	FROM member
    ORDER BY height DESC
    LIMIT 3, 2;

 

-- 중복된거 하나만 보여지게 하는거

SELECT DISTINCT addr FROM member;

-- GROUP BY - mem_id를 그룹별로 묶어서 amount합계를 내면
SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;

-- 별칭을 주면
SELECT mem_id "회원 아이디", SUM(amount) "총 구매 개수"
	FROM buy GROUP BY mem_id;
    
    
    
-- 총 구매 금액

SELECT mem_id "회원 아이디", SUM(price * amount) "총 구매 금액"
	FROM buy GROUP BY mem_id;

 

-- member id 별로 평균 구매 개수

SELECT mem_id, AVG(amount) "평균 구매 개수"
	FROM buy
    GROUP BY mem_id;
    
    
-- member 행의 개수를 세어보면
SELECT COUNT(*) FROM member;


-- 연락처가 있는 회원
SELECT COUNT(phone1) "연락처가 있는 회원" FROM member;


-- 총 구매 금액이 1000이 넘는 회원
SELECT mem_id "회원 아이디", SUM(price * amount) "총 구매 금액"
	FROM buy
    GROUP BY mem_id
    HAVING SUM(price * amount) > 1000;
    
  
-- SUM() 기준 내림차순
SELECT mem_id "회원 아이디", SUM(price * amount) "총 구매 금액"
	FROM buy
    GROUP BY mem_id
    HAVING SUM(price * amount) > 1000
    ORDER BY SUM(price * amount) DESC;

 

 

 

 

 

 

 

 

 

 

 

 

참고: <출처 한빛미디어: [SQl 기초 강의] 6강. SQL 기본 문법() >

https://www.youtube.com/watch?v=_JURyg_KzHE&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=7 

<출처 한빛미디어: [SQl 기초 강의] 7강. SQL, SELECT 절의 형식(ORDER BY 절과 GROUP BY 절) >

https://www.youtube.com/watch?v=6qkPy7RfLqQ&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=8