3-3 SQL

SQL(Structured Query Language) 명령어

1. SQL의 개념

• 관계형 데이터 모델을 따르는 DBMS 표준 데이터베이스 언어
• 관계 대수를 기초로 만들어진 DB 관련 명령어 형식
• 입력 릴레이션으로부터 원하는 출력 릴레이션 얻어내는 명령어 형식
• 내장 SQL
  • 프로그래밍 언어 COBOL, C, Visual Basic, ASP, PHP 등에 포함하여 사용
  • 응용 프로그램 내 데이터 정의/질의하는 SQL 문장 내포하여 프로그램 실행 시 함께 실행
  • 변수/데이터베이스 필드(속성)의 이름은 다르거나 같아도 되지만 데이터 타입은 반드시 일치

• SQL 명령어의 분류
  • 데이터 정의어(DDL: Data Definition Language)
    • DB 정의/수정 목적으로 사용하는 언어
    • 데이터 저장하는 테이블 구조 생성, 변경 위해 사용
    • 논리적, 물리적 데이터 구조 정의
    • DBA(데이터베이스 관리자)가 사용하는 언어
    • 명령어 수행 후 복귀 불가하므로 신중히 사용
  • 데이터 조작어(DML: Data Manipulation Language)
    • 데이터 검색, 삽입, 삭제, 갱신 지원하는 언어
    • 사용자-데이터베이스 관리 시스템 간 인터페이스 제공
    • 일반 사용자/응용 프로그래머 사용
    • 트렌젝션 제어어(Transaction Control Language) 활용하여 실행 전 상태로 복귀 가능
  • 데이터 제어어(DCL: Data Control Language)
    • 여러 사용자가 데이터 공유하여 병행 수행 제어
    • 무결성 유지하며 내부적으로 여러 규정/제약 조건 등 기술
    • 사용자별로 DB 접근 권한 부여/회수
    • DBA가 사용하는 언어
    • 데이터 보안 유지

2. 데이터 정의어(DDL)

 

CREATE 신규 생성	CREATE TABLE 테이블명( 필드명 데이터 타입 [DEFAULT 값] [NOT NULL] [PRIMARY KEY (필드 리스트)] {[FOREIGN KEY (필드 리스트) REFERENCES 테이블명 [필드명] [ON DELETE 옵션] [ON UPDATE 옵션]]} [CHECK (조건식) | UNIQUE(필드명)]);
다른 테이블 정보 이용한 테이블 생성	CREATE TABLE 테이블명 AS SELECT 문;
ALTER(테이블 변경)	필드 추가 ALTER TABLE 테이블명 ADD 필드명 데이터 타입 [DEFAULT 값]; 맨 뒤에 필드 추가 ALTER TABLE 테이블명 ADD 필드명 데이터타입; 맨 앞에 필드 추가 ALTER TABLE 테이블명 ADD 필드명 데이터타입 FIRST; 지정 필드 뒤에 필드 추가 ALTER TABLE 테이블명 ADD 필드명 데이터타입 AFTER 앞 필드명; 필드 데이터 타입 변경 ALTER TABLE 테이블명 MODIFY 필드명 데이터타입 [DEFAULT 값]; 필드 삭제 ALTER TABLE 테이블명 DROP 필드명;
테이블 삭제와 변경	테이블 삭제 DROP TABLE 테이블명 [CASCADE/RESTRICT]; 테이블 내용 삭제 TRUNCATE TABLE 테이블명; 테이블명 변경 RENAME TABLE L이전 테이블명 TO 새로운 테이블명; ALTER TABLE 이전 테이블명 RENAME 새로운 테이블명; 뷰 삭제 DROP VIEW 이름;

 

• 삭제, 갱신 제약조건(CONSTRAINT)

참조된 기본키 값이 삭제될 경우	CASCADE : 참조한 테이블에 있는 외래키와 일치하는 모든 행 삭제 RESTRICT: 참조한 테이블에 있는 외래키에 없는 것만 삭제 가능 NULLIFY: 참조한 테이블에 정의된 외래키와 일치하는 것을 NULL로 수정
참조된 기본키의 값이 수정될 경우	CASCADE: 참조한 테이블에 있는 외래키와 일치하는 모든 행 수정 RESTRICT: 참조한 테이블에 있는 외래키에 없는 것만 수정 가능
NULLIFY	참조한 테이블에 정의된 외래키와 일치하는 것을 NULL로 수정(해당 컬럼 NULL 허용 시)

 

• 제약 조건 유형

PRIMARY KEY(기본키)	기본으로 NOT NULL, UNIQUE 제약 포함
FOREIGN KEY(외래키)	참조 대상을 테이블명(필드명)으로 명시 참조 무결성 위배 상황 발생 시 처리 방법으로 옵션 지정 가능 옵션: NO ACTION, SET DEFAULT, SET NULL, CASCADE
UNIQUE(유일키)	동일한 값 가져서는 안되는 항목에 지정
NOT NULL	테이블 내 관련 필드의 값 NULL 불가 필수 입력 항목에 대해 제약 조건 설정
CHECK	개발자가 정의하는 제약 조건 상황에 따라 다양한 조건 설정 가능
DEFAULT	데이터 INSERT 할 때 해당 컬럼의 값 넣지 않는 경우 기본값

 

• 제약 조건 변경 SQL 명령문

제약조건 추가	ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건(필드명);
제약조건 삭제	ALTER TABLE 테이블명 CONSTRAINT 제약조건명 | 테이블명 DROP PRIMARY KEY | FOREIGN KEY(필드명) | UNIQUE(필드명);
제약조건 비활성화	ALTER TABLE 테이블명 DISABLE CONSTRIANT 제약조건명;
제약조건 활성화	ALTER TABLE 테이블명 ENABLE CONSTRAINT 제약조건명;

 

 

3. 데이터 조작어(DML)

데이터 삽입	- 입력하고자 하는 테이블에 모든 컬럼 데이터 입력 시 컬럼명 명시하지 않아도 되지만 특정 컬럼만 입력하고자 할 시 반드시 컬럼명 명시 - 컬럼명 수와 VALUES 절의 수 반드시 동일 - 기존에 존재하는 테이블 데이터로부터 특정 테이블로 데이터 복사 가능 - 필드명 지정 시 INSERT INTO 테이블명 (필드명1, 필드명2, 필드명3,...) VALUES(값1, 값2, 값3,...); - 필드명 미지정 시 INSERT INTO 테이블명 VALUES (값1, 값2, 값3, ...); - 특정 테이블로 복사 시 INSERT INTO 사본 테이블명(필드명1, 필드명 2,...) SELECT 필드명1, 필드명2,… FROM 원본테이블명;
데이터 수정	UPDATE 테이블명 SET 필드1=값1, 필드명2=값2,… [WHERE 절];
데이터 삭제	DELETE FROM 테이블명 [WHERE 절];
데이터 조회	SELECT [OPTION] 필드명 FROM 테이블명 [WHERE 절];

 

4. SELECT 명령어

 

• SELECT 명령어 형식

SELECT [DISTINCT] {* | 필드명1… | 필드명1 AS 별명1… | 연산}

FROM 테이블명

{WHERE 조건 | GROUP BY 필드명 [HAVING 조건]}

[ORDER BY {정렬 필드명 [ASC | DESC]}];

• SELECT 명령어 옵션
  • DISTINCT: 중복되는 튜플(레코드, 행) 제거하여 조회
  • *: 모든 필드 조회
  • 필드명: 여러 개 지정 가능
  • 별명: 필드명이나 연산 결과에 제목 부여
  • 연산: 사칙 연산, SUM(), AVG() 등의 함수 사용
  • WHERE 조건: 조건 만족하는 튜플들만 조회 (조건: 비교, 범위, 집합, 패턴(컬럼 like 패턴), null, 복합 조건)

  • 와일드카드	설명
    %	0개 이상의 문자열과 일치
    [ ]	1개 문자와 일치
    [^]	1개 문자와 불일치
    _	특정 위치 1개 문자와 일치

  • GROUP BY 필드명: 그룹별로 묶을 필드명 지정
  • HAVING: 그룹별로 조건을 만족하는 튜플들만 조회
  • ORDER BY: 정렬 옵션으로 ASC, DESC, 1차 키 정렬 필드...지정 가능
  • UNION: 여러 SQL 문 결과 합집합. 중복행 제거
  • UNION ALL: 여러 SQL문 결과의 합집합. 중복행 미제거
  • INTERSECT: 여러 SQL 문 결과에 대한 교집합. 중복행 제거
  • EXCEPT: 앞의 SQL문 결과와 뒤 SQL문 결과 사이 차집합
  • MINUS: 데이터 집합 기준으로 다른 데이터 집합과 공통 항목 제외한 결과만 추출

  • 집계 함수	- COUNT(입력값): 해당 입력값 내 NULL이 아닌 튜플 수 - SUM(입력값): 해당 입력값 내 NULL 아닌 값의 합계 - AVG(입력값): 해당 입력값 내 NULL 아닌 값의 평균 - MAX/MIN(입력값): 해당 입력값 내 최대값/최솟값 출력. 문자열이나 날짜 형식에도 적용 가능 - STDDEV(입력값): 해당 입력값 내 표준편차 - VARIAN(입력값): 해당 입력값 내 분산
    순위 함수	SELECT RANK(파라미터) OVER ([PARTITION BY 컬럼1, ...] [ORDER BY 컬럼 A, …]) FROM 테이블명; - RANK: 특정 항목 순위 구하는 함수(동일 순위 인정) - DENSE_RANK: 레코드 순위 계산(동일 순위 인정) - ROW_NUMBER: 레코드 순위 계산(동일 순위 미 인정)
    윈도 함수	SELECT 함수명(파라미터) OVER ([PARTITION BY 컬럼칼럼 1,...] [ORDER BY 컬럼 A, …]) FROM 테이블명
    그룹 함수	SELECT 컬럼 FROM 테이블명 GROUP BY 컬럼 GROUPING SETS/ROLLUP/CUBE 컬럼명 [HAVING..][ORDER BY…] - ROLLUP: 소그룹 간 소계 - CUBE: GROUPBY 항목 간 다차원적 소계 - GROUPING SETS: 특정 항목에 대한 소계
    비율 함수	SELECT RATIO_TO_REPORT (컬럼1) OVER(PARTITION BY 컬럼A, …); - RATIO_TO_REPORT: 주어진 그룹 합을 기준으로 각 로우 상대적 비율 반환 - PERCENT_RANK: 제일 먼저 나오는 것 0, 제일 늦게 나오는 것 1로 하여 값이 아닌 순서별 백분율

 

• SELECT 부속, 복수 질의 명령
  • ANY 사용: OR+OR+OR
  • ALL 사용: AND+AND+AND

 

• SELECT 테이블 생성 명령
  • 새 테이블이 기존 테이블과 동일 구조일 시

               CREATE TABLE 신규 테이블 AS SELECT * FROM 기존 테이블;

• • 기존 테이블 컬럼 및 데이터 유형, 길이 그대로 적용
  • NOT NULL 정의 그대로 적용
  • 제약조건 미적용으로 ALTER TABLE 이용하여 제약조건 추가
  • 동일 컬럼들로 생성된 경우 '*' 사용
  • 필요한 컬럼만 지정하여 테이블 생성