[MS SQL]3.GROUP BY,INSERT,DELETE,UPDATE,서브 쿼리

INSERT INTO salaries(yearID,teamID,playerID,lgID)
VALUES(2020,'KOR','amugae2','NL')

GROUP BY

이 명령어는 특정 정보를 이용해서 그룹화하는 기능을 가지고있다.

가량 2004년도의 팀별 홈런 수를 알고싶으면 우리에게 주어진 정보는 선수 + 소속팀 ... 이기때문에 우선 같은 소속팀인 선수의 2004년도 홈런기록을 모두 더 한다음 팀별로 홈런수를 표시해야한다.

 

그렇다면 우선적으로 테이블에 표시할때 teamID로 그룹화를 진행한다음 데이터를 처리해야하는데 이때 사용하는것이 GRUOP BY이다.

 

아래 코드는 2004년도에 teamID를 기준으로 그룹화를 진행한후 홈런 개수를 계산한다.

SELECT teamID, SUM(HR) AS homeRUNS
FROM batting
WHERE yearID = 2004 
GROUP BY teamID
ORDER BY homeRuns DESC

 

 

여기서 추가 조건으로 200개이상의 홈런을 기록한 팀만 출력하고 싶다고 한다면?

단순 WHERE조건을 추가하면 되지않냐고 할 수도있겠지만 좀만 생각해 보면 말이 안되는 소리이다.

 

실행순서를 잘생각해보면 

FROM -> battring에서

WHERE -> 2004년도인 데이터만 골라서

GROUP BY -> teamID를 기준으로 그룹화시키고

SELECT -> 그룹별로 홈런을 전부 더해서

ORDER BY 내림차순으로 정렬해라

 

WHERE은 그룹화가 진행되기전에 데이터를 선별하는 작업이기 때문에 그룹화를 한 이후에 할 수있는 행동에는 간섭을 못한다.

그렇기때문에 HAVING이라는 명령어를 제시해서 그룹화한 이후 조건을 추가할수있게해준다,

--Q) 2004년도에 200홈런 이상을 날린 팀의 목록은?
SELECT teamID, SUM(HR) AS homeRUNS
FROM batting
WHERE yearID = 2004 
GROUP BY teamID
HAVING SUM(HR) >= 200
ORDER BY homeRuns DESC

 

작성 순서와 실행순서가 다르다는것을 잘 기억하기위해 여기에 적어놓겠다. 

--작성 순서
--SELECT
--FROM
--WHERE
--GROUP BY
--HAVING
--ORDER BY


--실제 실행 순서
--FROM
--WHERE
--GROUP BY
--HAVING
--SELECT
--ORDER BY

 

2가지 이상의 속성으로 그룹화를 진행하는것도 가능하다.

단순이 GROUP BY에 속성을 추가해주면된다. 이렇게되면 (팀명, 년도)로 그룹화를 진행했을때 BOS 2004라는 데이터와 BOS 2005데이터는 서로 다른데이터로 인식하게된다.

--Q) 단일 년도 팀별 홈런 수를 내림차순으로 정렬한다면?
SELECT teamID,yearID, SUM(HR) AS homeRUNS
FROM batting
GROUP BY teamID, yearID
ORDER BY homeRuns DESC

INSERT,DELETE,UPDATE

salaries 테이블을 가지고 INSERT,DELETE,UPDATE를 배워보자

SELECT *
FROM salaries
ORDER BY yearID DESC

 

 

 

특정 테이블에서 값을 추가하고싶으면

INSERT INTO [테이블 명] 

VALUES(값...) 

으로 데이터를 넣어줄 수있다. 테이블의 열에 들어갈 값을 하나라도 적어주지않으면 값이 들어가지않는다.

INSERT INTO salaries
VALUES (2020,'KOR','NL','amugae',9000000)

맨 위에 값이 잘들어간 모습

 

프로그래밍 언어에서 명시적으로 함수 매개변수를 지정해주듯이 열을 지정해서 넣을 수도있다.

INSERT INTO salaries(yearID,teamID,playerID,lgID,salary)
VALUES(2020,'KOR','amugae2','NL',80000000)

 

이런 방식으로 데이터 타입이 nullable일때 값을 넣지 않았을시 null을 넣어준다.

 

 

salary는 Allow Nulls가 체크 되어있음

 

DELETE도 사용법은 비슷하다

 

DELETE FROM [테이블 명] -> 테이블의 모든 값을 날림

 

DELETE FROM [테이블 명] WHERE [조건]

playerID 가 'amugae2'인 행을 지워버림

DELETE FROM salaries 
WHERE playerID = 'amugae2'

 

UPDATE

 

UPDATE [테이블명] SET [열 = 값,] 

WHERE [조건]

UPDATE salaries
SET salary = salary * 2
WHERE teamID = 'KOR'

 

어떤 데이터를 지우려고할때 DELETE는 진짜 물리적으로 삭제하는것이고 UPDATE는 논리적으로 삭제된 데이터라고 표기해놓고 사용하는 용도로도 사용한다.

 

서브 쿼리

우리는 위에서 역대 연봉이 가장높은선수를 추출할때 이런 코드를 사용했다.

SELECT TOP 1 *
FROM salaries
ORDER BY salary DESC;

 

 

2009년 NYA 소속 rodrial01이라는 playerID를 가진 선수가 역대 연봉이 가장 높았다.

 

지금까지 우리가 배운 지식으로 이 선수의 정보를 조회하고싶으면

SELECT *
FROM players
WHERE playerID = 'rodrial01'

이런식으로 playerID를 하드코딩해서 조회해야한다,

 

이걸 한번에 처리 할수있는게 SubQuery의 개념이다. 쿼리안에 또다른 작은 쿼리를 넣어놓는거다.

SELECT *
FROM players
WHERE playerID = (SELECT TOP 1 playerID FROM salaries ORDER BY salary DESC)

 

그렇다면 역대 연봉 상위 20명의 정보를 가져오고싶다면? = 구문은 한명의 정보만 대조하기때문에 다른 방식을 써야한다.

IN을 사용함으로써 다중 행처리도 가능해진다.

SELECT *
FROM players
WHERE playerID IN (SELECT TOP 20 playerID FROM salaries ORDER BY salary DESC)

 

하지만 행이 9개까지밖에 없는데 이건 중복값을 제외 시켰기 때문이다. 즉 역대 연봉 상위 20위는 9명의 선수가 전부 차지하고있다는거다.

 

서브 쿼리는 WHERE구문에서 가장 많이 사용되지만 다른 구문에서도 가능하다.

SELECT *
FROM salaries
ORDER BY yearID DESC

INSERT INTO salaries
VALUES (2020,'KOR','NL','amugae4',(SELECT MAX(salary) FROM salaries))

 

INSERT하는 값을 subquery를 이용해서 넣어주는 코드이다. 

 

상관 관계 서브 트리, EXIST, NOT EXIST

예시로 "포스트 시즌 타격에 참여한 선수들 목록"을 추출하고싶을때 서브 쿼리를 이용하면

 

SELECT *
FROM players
WHERE playerID IN(SELECT playerID FROM battingpost)

이런식으로 코드를 짤것이다.

 

하지만 조금더 유연성을 고려하여 EXIST를 이용해서

SELECT *
FROM players
WHERE EXISTS(SELECT playerID FROM battingpost WHERE battingpost.playerID = players.playerID)

이렇게 코드를 짤수있다. 첫번째 방식이 좀더 가독성이 좋아보일순 있겠으나 유연성은 낮다. 첫번째는 단순히 그 값이 있는지만 알수있지만 두번째는 여러 비교구문, 사칙연산등을 추가로 가능하게 해준다.

 

SELECT *
FROM players
WHERE EXISTS(SELECT playerID FROM battingpost WHERE battingpost.playerID > players.playerID)

 

 

ctrl + L을 눌러서 성능비교를 해봐도 거의 일치한다. 그러므로 두 번째 방식에 익숙해 지도록하자.