[데이터베이스] SQL 입문 - Group, 추가 삭제 갱신, subquery

GROUP BY

그룹을 묶어서 처리를 하면 더 편할 것을 우리는 당연히 알고 있다.

 

만약 2004년에 가장 많은 홈런을 날린 팀은?? 이라는 질문에 어떤 Qurey를 날려야할까.

지금까지 배운 지식으로는 해결할 수 없다. 그렇다면 그룹을 묶는 Group By에 대해 알아보자.

SELECT *
FROM batting
WHERE yearID = 2004
GROUP BY teamID;

이렇게 하면 팀 소속별로 데이터가 묶일 수 있을까?? 안된다. 그 이유는 * 때문이다. teamID를 기준으로 하나로 묶는것까진 괜찮은데 그 중 playerID 등 어떤 값을 출력해야하는지 모르기 때문에 에러가 난다.

그룹을 묶었을 때는 집계함수 등 만 사용가능하다.

또한 Group By 절이 없기 때문에 사용할 수 없다.

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

이렇게 처리할 수 있을것이다.

 

그럼 2004년도에 200 홈런 이상 날린 팀의 목록은??? 이라는 질문을 해보자.

SELECT teamID, SUM(HR) AS homeRuns
FROM batting
WHERE yearID = 2004
GROUP BY teamID
HAVING SUM(HR) >= 200
ORDER BY homeRuns DESC;

GROUP BY 를 사용하면서 조건을 추가 하고싶다면 Having을 이용해서 조건을 걸 수 있다.

Insert Delete Update

insert는 다음과 같은 형식을 가진다.

INSERT INTO [테이블명] VALUES [값, ...]

또는 

INSERT INTO [테이블명](열, ...) VALUES[값, ...] 을 사용한다.

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

INSERT INTO salaries(yearID, teamID, lgID, playerID, salary)
VALUES (2020, 'KOR', 'NL', 'minseok2',9000000);

물론 테이블 설계에 따라 모든 데이터를 넣을 필요는 없다.

 

delete는 다음과 같이 실행된다.

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

DELETE FROM salaries
WHERE playerID = 'minseok'

 

update는 다음과 같다.

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

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

SUBQUREY

서브쿼리라고 하거나 하위쿼리라고 부른다.

간단히 얘기하면 SQL 명령어 안에 지정하는 하부 SELECT라고 말할 수 이싿.

 

연봉이 역대급으로 높은 선수의 이름을 추출한다고 해보자.

salary 테이블에는 선수의 이름이 존재하지 않는다. 그렇다면 playerID를 추출한 다음에 그 값을 다시 players 테이블에 넣어야한다.

이 것을 하나로 합칠 수가 있다.

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

이것은 단일행이였다. 만약 다중행을 하위쿼리에서 뽑아오면 어떻게 될까 당연히 에러가 난다.

IN을 사용하면 다중 행을 처리할 수 있다. 

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

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

SELECT (SELECT COUNT(*) FROM players) AS playrCount, (SELECT COUNT(*) FROM batting) AS battingCount;

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

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

위에 버전이 더 넓은 범위의 값을 찾을 수 있다.

 

데이터베이스 설계 및 작성

우리가 맨 처음부터 데이터 베이스를 만드는 방법에 대해 알아보자. 언제까지 GUI를 통해 작업할 수 없기에 이걸 sql를 통해 처리하는 방법을 알아보자.

CREATE DATABASE GameDB;

USE GameDB;



-- 테이블 생성(CREATE)/삭제(DROP)/변경(ALTER)
-- CREATE TABLE 테이블명(열이름 자료형 [DEFAULT 기본값] [NULL | NOT NULL], ...)

CREATE TABLE accounts(
	accountID INTEGER NOT NULL,
	accountName VARCHAR(10) NOT NULL,
	coins INTEGER DEFAULT 0,
	createdTime DATETIME
);


-- 테이블 삭제
DROP TABLE accounts;

-- 테이블 변경(ALTER)
-- 열 추가(ADD/DROP/ALTER)
ALTER TABLE accounts
ADD lastEnterTime DATETIME;

ALTER TABLE accounts
DROP COLUMN lastEnterTime;

ALTER TABLE accounts
ALTER COLUMN accountName VARCHAR(20) NOT NULL;

-- 제약(CONSTRAINT) 추가 / 삭제
-- NOT NULL , 이 데이터는 null이면 안된다.
-- UNIQUE , 같은 값이 겹치면 안된다.
-- PRIMARY KEY , 다음 index를 다루면서 설명/간단히 설명하면 Dictionary 의 키 처럼 테이블의 값을 찾기위한 키
-- FOREIGN KEY

ALTER TABLE accounts
ADD PRIMARY KEY (accountId);

ALTER TABLE accounts
ADD CONSTRAINT PK_Account PRIMARY KEY (accountId);

ALTER TABLE accounts
DROP CONSTRAINT PK_Account;