잡지식창고

CREATE TABLE은 DB에 새로운 테이블을 생성할 때 사용된다.

기본적인 사용법은 다음과 같다.

CREATE TABLE (TableName)
(
FirstColumnName INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
SecondColumnName INT NULL
REFERENCES RelatedTable(FirstColumn)
ON DELETE SET NULL,
ThirdColumnName VARCHAR(10) NULL,
FourthColumnName FLOAT NULL DEFAULT (10)
)

첫번째 열은 기본키로 정의되었기때문에 반드시 NOT NULL체크를 해주어야만 한다.

그리고 identity(자동 증가 열)을 정의해준다(1부터 1씩 증가). 

두번째 열은 외래 키로 정의했다. RelatedTable이라는 이름을 가진 테이블의 FirstColumn이름을 가진 열과 연관을 시켰다.

나머지 열은 각자 열의 용도에 맞는 데이터 유형과 크기를 지정해주었다.

만들어진 테이블을 수정하려면 ALTER TABLE을 사용하면된다.

테이블을 삭제할때는 DROP TABLE을 사용한다.

CREATE INDEX를 통해 테이블에 인덱스를 생성할 수 있다.

CREATE INDEX IndexName
ON newTable(ColumnName)

DROP INDEX를 통해 인덱스를 삭제할 수 있다.

DROP INDEX IndexName
ON newTable

이 책에서는 클러스터 인덱스, 넌클러스터 인덱스, 다중컬럼 인덱스에 대해 다루지 않았다.
나중에 따로 공부해보자.

 INSERT INTO

INSERT INTO (테이블 명)
(열 이름1, 열 이름2, ...)
VALUES
(넣을 데이터1, 넣을 데이터2, ...),
...

기본 형태는 위와 같다.

INSERT에서 명시하지 않은 열의 데이터는 NULL로 처리된다.

위와 같이 테이블을 삽입할수도 있다.

DELETE

데이터를 삭제할때는 DELETE를 사용한다.

TRUNCATE TABLE로도 테이블을 삭제할 수 있다.

UPDATE

UPDATE로는 원래 있던 값을 지정한 값으로 업데이트 할 수 있다.

ID가 1인 데이터에 한해 LastName과 State값을 바꿔주는 예시이다.

WHERE절을 사용하지 않으면 모든 행에 대해서 업데이트가 적용되니 주의해야한다.

임시 테이블

- 기본적으로 로컬 임시 테이블의 이름은 #으로 시작된다.(##가 붙으면 전역 임시 테이블이다.)

- 임시 테이블은 세션이 종료되었을 때 삭제되지 않고 남아있으면 자동으로 삭제된다.

- 임시 테이블은 현재 설정된 데이터베이스에 저장되지 않고 시스템 데이터베이스 tempDB에 저장된다.
단, 데이터 건수가 작을때는 메모리에만 존재하고, tempDB 사이즈가 증가하지 않는다.(데이터가 29K이하 일때는 메모리에 존재)

- 로컬 임시 테이블은 테이블을 만든 세션에서만 사용 가능하고, 전역 임시 테이블은 외부 세션에서도 사용이 가능하다.

테이블 변수

- 테이블 변수의 이름은 @로 시작된다.

- 테이블 변수는 테이블 변수가 정의된 함수 및 저장프로시저 및 일괄 처리가 끝나면 자동으로 정리된다.

- 테이블 변수와 관련된 트랜잭션은 테이블 변수가 업데이트 되는 동안만 지속된다. 따라서 테이블 변수를 사용하면 리소스 잠금과 로깅에 대한 요구가 줄어든다.

임시 테이블과 테이블 변수

필요한 경우

1. 여러 단계를 거쳐 동일한 데이터에 반복작업을 실행할 때.

2. 복잡한 쿼리를 단순화 할때.

3. 서버상의 커서 사용으로 인한 부하를 줄일 때.

차이점

대용량의 데이터를 처리할때 임시 테이블이 유리, 소용량의 데이터를 처리할때는 테이블 변수가 유리하다.

저장 프로시저를 사용하면 여러 SQL문장을 하나의 프로시저로 저장할 수 있고, SQL 문장과 함께 파라미터를 사용할 수 있게 된다.

저장 프로시저를 사용하는 법에 대해 간략히 알아보자.

저장 프로시저 생성과 실행

저장 프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 실행해야한다.

기본적인 형태는 

CREATE PROCEDURE (프로시저이름)
AS (파라미터 선언)
BEGIN
(SQL 문장)
END

이다.

위 예시는 CustID 파라미터를 받아 Customers 테이블에서 해당 ID를 가지고 있는 행을 출력하게 하는 프로시저이다.

프로시저를 실행하려면 위의 EXEC을 사용한다.

파라미터가 존재하는경우 명시해주고, 없는 경우에는 쓰지 않으면 된다.

저장 프로시저의 수정/삭제

이미 저장되어 있는 프로시저를 수정하려면 ALTER PROCEDURE를 사용하면 된다.

ALTER PROCEDURE를 사용하는것 이외엔 처음 생성할떄와 같다. 

프로시저를 삭제할때엔 DROP PROCEDURE를 사용한다.

저장 프로시저가 함수와 다른 점은 크게 두가지가 있다.

1. 저장 프로시저는 출력 파라미터의 개수에 제한이 없다. 반면 함수는 항상 하나의 출력 파라미터를 갖는다.

2. 저장 프로시저는 콜링 프로그램에 의해 실행된다(SELECT 문장에서 직접 부를 수 없다). 반면, 함수는 문장에서 호출할 수 있다. 

서브쿼리는 하나의 SQL문에 포함되어 있는 또 다른 SQL문을 말한다.

서브쿼리는 SELECT 문장의 어디든 들어가도 상관 없다. 하지만 어디에 들어가느냐에 따라 사용법이 조금씩 달라진다. 

데이터 소스(FROM)

위 예시는 데이터 소스(FROM 절) 부분에 적용된 예시이다.

서브쿼리가 FROM절의 일부로 명시되면 새로운 데이터 소스가 생성된다. 이는 뷰의 역할과 비슷한데, 뷰는 데이터베이스에 영구히 저장되고, 서브쿼리는 그렇지 않다는 차이점이 존재한다.

위 문장에서 서브쿼리는 FROM절 다음에 나타나는 () 소괄호로 둘러싸인 SELECT 문장이다.

조인해줄 테이블을 서브쿼리로 그 자리에서 생성해준것을 볼 수 있다.

선택 기준(WHERE, HAVING)

서브쿼리는 선택 기준(WHERE, HAVING)에도 적용될 수 있다.

해당 문장에서는 서브쿼리로 IN에 들어갈 조건을 지정해주었다.

서브쿼리가 Cash 타입을 가진 UserID를 반환하고, 반환된 값을 WHERE절에서 조건으로 사용하는 예시이다.

상관 서브쿼리

위에서 본 두 예시는 모두 비상관 서브쿼리였다.

이는 서브쿼리와 쿼리가 연관되어있지 않아 서로 독립적임을 의미한다.

이번에 볼 예시는 상관 서브쿼리이다. 비상관 서브쿼리와는 반대로 서브쿼리와 쿼리가 연결되어있는 경우이다.

위 예시가 상관 서브쿼리인 이유는 마지막 문장의 User.UserID 때문이다.

만약 위의 서브쿼리를 독립적으로 실행하려고 시도하면 에러가 출력될것이다. User.UserID가 서브쿼리의 구문 안에 없기 때문이다.

EXISTS 연산자

EXISTS 연산자는 상관 서브쿼리에 데이터가 있는지 없는지 알 수 있게 해준다.

서브쿼리에서 UserID가 일치하는 열을 반환하고, 반환된 열을 출력했다.

계산된 열 생성

서브쿼리로 계산된 열을 생성할 수 있다.

Number of Transactions이라는 열을 서브쿼리를 통해 생성한 예시이다.

해당 열은 유저마다 실행된 거래 수 나타내는 열이다. 해당 계산을 서브쿼리를 통해 실행한 후, 그대로 그 결과를 열로 생성할 수 있다. 

공통 테이블 식

공통 테이블 식은 서브쿼리는 미리 서술하는것이다.

이에 사용되는 키워드는 WITH이다. WITH 키워드 뒤에 서브쿼리의 별칭을 지정하고, AS 뒤에 서브쿼리를 작성한다.

공통 테이블 식을 사용하면 서브쿼리의 세부사항이 주 쿼리 앞에 독립체로 분리되어있기 때문에 주 쿼리의 가독성이 높아진다. 

교차 조인은 외부 조인도 아니고 내부 조인도 아니다.

교차 조인은 테이블간의 관계를 나타내지 않고 조인하는 방법이다.

교차조인은 테이블들에게서 나올 수 있는 행의 모든 조합을 다 만들어낼 수 있다. 이를 Cartesian product라고 부른다.

 위 예시처럼 3가지 size와 4가지 color의 조합으로 12개 행을 가진 테이블을 만들 수 있다.

 또한 교차조인은 위 예시처럼 원하는 행을 선택해서 보는 용도로도 사용할 수 있다.