[DB] SQL

szlee·2023년 11월 29일

데이터베이스

목록 보기

6/8

DDL

데이터 정의어
스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 삭제할 때 사용하는 언어.
데이터베이스 관리자나 데이터베이스 설계자가 사용.

CREATE 정의
ALTER 변경
DROP 삭제 : 디스크에서 테이블을 완전히 제거

CREATE TABLE 학생 (
	이름 VARCHAR(15) NOT NULL,
	학번 CHAR(8),
	전공 CHAR(5),
	성별 SEX,
	생년월일 DATE,
	PRIMARY KEY(학번),
	FOREIGN KEY(전공) REFERENCES 학과(학과코드)
	ON DELETE SET NULL
	ON UPDATE CASCADE,

	CONSTRAINT 생년월일제약 CHECK(생년월일) >= '1990-01-01')
);

NOT NULL : 값이 비어있으면 안된다.
PRIMARY KEY : 기본키로 사용
FOREIGN KEY ~ REFERENCES ~ : 참조할 테이블과 그 테이블을 참조할 때 사용할 외래키 속성
- 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용된다.
  - 참조 무결성의 CASCADE 법칙 : 참조 무결성 제약이 설정된 기본 테이블의 어떤 데이터를 삭제할 경우 그 데이터와 밀접하게 연관 되어 있는 다른 테이블의 데이터들도 도미노처럼 자동으로 삭제된다.
- ON DELETE : 참조 테이블의 튜플 삭제되었을 때 기본 테이블에 취해야할 사항 지정 -- NO ACTION, CASCADE, SET NULL, SET DEFAULT
- ON UPDATE : 참조 테이블의 속성 값 변경되었을 때 기본 테이블에 취해야할 사항 지정 -- NO ACTION, CASCADE, SET NULL, SET DEFAULT
CONSTRAINT : 제약 조건의 이름 지정
CHECK : 속성 값에 대한 제약 조건 정의

CHAR 와 VARCHAR의 차이?

CHAR : 항상 지정된 크기만큼 기억 장소가 확보
VARCHAR : 기억 장소의 크기가 지정되어도 필드에 저장된 데이터만큼 기억 장소가 확보됨.
ex) 이름 속성의 자료형을 CHAR(15)로 지정하면 이름에 한글자가 저장되도 항상 15바이트가 확보되지만, VARCHAR(15)로 지정하면 저장된 한글자 크기만큼만 확보된다.

다른 테이블을 이용한 테이블 정의

CREATE TABLE 테이블명 AS SELECT 속성명 FROM 기존테이블명;

기존 테이블에서 추출되는 속성의 데이터 타입과 길이가 신규 테이블에 그대로 적용
기존 테이블의 NOT NULL 정의는 신규 테이블에 그대로 적용
기존 테이블의 제약 조건은 신규 테이블에 적용되지 않음

DML

데이터 조작어
데이터베이스 사용자가 데이터를 실질적으로 처리하는 데 사용.

SELECT 검색. SELECT FROM WHERE
INSERT 삽입. INSERT INTO VALUES
UPDATE 변경. UPDATE SET WHERE
DELETE 삭제 : 모든 레코드를 삭제하더라도 테이블 구조는 남아있다. DELETE FROM WHERE

집계성 SQL

집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결과를 반환

GROUP BY : 특정 속성을 기준으로 그룹화하여 검색할 때 사용.
HAVING : GROUP BY와 함께 사용되며 그룹에 대한 조건 지정
집계 함수 종류 : COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN

그룹 함수 : 중간 합계 분석 데이터를 산출

-> GROUP BY 절에 함께 쓴다.

ROLLUP : 중간 집계 값 산출
- GROUP BY ROLLUP(컬럼1, 컬럼2..) : 컬럼1, 2에 대한 중간집계와 총 집계
CUBE : 결합 가능한 모든 값에 대해 다차원 집계 생성
- GROUP BY CUBE(컬럼1, 컬럼2..)
GROUPING SETS : 집계 대상 컬럼들에 대한 개별 집계. 컬럼 간 순서와 무관한 결과 얻을 수 있다.
- GROUP BY GROUPING SETS(컬럼1, 컬럼2..)

윈도 함수 : 온라인 분석 처리 용도로 사용

GROUP BY 절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계.
함수의 인수로 지정한 속성이 대상 레코드의 범위가 되는데 이를 윈도우라고 한다.
SELECT 절에 쓴다.

순위함수

ROW_NUMBER() : 순위 계산. 동일 순위 값이 존재해도 연속 번호 부여
- SELECT ~ ROW_NUMBER() OVER (ORDER BY 컬럼 DESC) 이름
RANK() : 순위 계산. 동일 순위 존재 시 후순위는 넘어감
- SELECT ~ RANK() OVER (ORDER BY 컬럼 DESC) 이름
DENSE_RANK() : 순위 계산. 동일 순위 존재해도 무관하게 연속 번호 부여
- SELECT ~ DENSE_RANK() OVER (ORDER BY 컬럼 DESC) 이름

행순서함수

FIRST_VALUE : 파티션별 윈도에서 가장 먼저 나오는 값 찾는다. (MIN과 동일)
LAST_VALUE : 파티션별 윈도에서 가장 늦게 나오는 값 찾는다.(MAX와 동일)
LAG : 파티션별 윈도에서 이전 로우의 값 반환
LEAD : 파티션별 윈도에서 이후 로우의 값 반환

그룹 내 비율 함수

RATIO_TO_REPORT : 합을 기준으로 각 로우의 상대적 비율 반환
PERCENT_RANK : 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율 구함

집합 연산자

두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야함.

UNION : 합집합. 두 SELECT문의 조회 결과를 통합하여 모두 출력.
중복된 행은 한번만 출력
UNION ALL : 합집합. 두 SELECT문의 조회 결과를 통합하여 모두 출력.
중복된 행도 그대로 출력
INTERSECT : 교집합. 두 SELECT문의 조회 결과 중 공통된 행만 출력.
EXCEPT : 차집합. 첫번째 SELECT문의 조회 결과에서 두번째 SEELCT문의 조회 결과를 제외한 행을 출력.

JOIN

INNER JOIN : 공통 속성을 기준으로 '=' 비교해서 같은 값을 가지는 행 연결하여 결과 생성
OUTER JOIN : JOIN 조건에 만족하지 않는 튜플도 결과로 출력.
- LEFT OUTER JOIN : 좌측 테이블에 맞춰서 우측 테이블에 없는 값도 NULL붙여서 결과에 추가
- RIGHT OUTER JOIN : 우측 테이블에 맞춰서 좌측 테이블에 없는 값도 NULL붙여서 결과에 추가
- FULL OUTER JOIN : 위 둘을 합쳐 놓은 것.

DCL

데이터 제어어
데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용
데이터베이스 관리자가 데이터 관리를 목적으로 사용

COMMIT 명령에 의해 수행된 결과를 실제 물리적 디스크에 저장. 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려준다.
ROLLBACK 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
GRANT 데이터베이스 사용자에게 사용 권한 부여
- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여
- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
- CASCADE: 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소
REVOKE 데이터베이스 사용자의 사용 권한을 취소
SAVEPOINT : 트랜잭션 내에 롤백할 위치인 저장점을 지정하는 방법.