6. SQL 기본 문법

SQL 소개

SQL

SQL (Structured Query Language)

• RDB (관계 데이터베이스)를 위한 표준 질의어, 비절차적 데이터 언어
• 대화식 SQL: 데이터베이스 관리 시스템에 직접 접근해 질의를 작성하여 실행
• 삽입 SQL: 프로그래밍 언어로 작성된 응용 프로그램에 삽입

SQL의 분류

• 데이터 정의어(DDL): 테이블을 생성, 변경, 제거하는 기능 제공
• 데이터 조작어(DML): 테이블에 데이터를 삽입하거나, 저장된 데이터를 수정, 삭제, 검색하는 기능 제공
• 데이터 제어어(DCL): 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자 별로 부여하거나 취소하는 기능 제공

SQL을 이용한 데이터 정의

테이블 생성: CREATE TABLE

• [ ] 안의 내용은 생략 가능
• 세미콜론으로 문장의 끝을 표시, 대소문자 구분 X

CREATE TABLE 테이블_이름 (
    속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본값] // 1, 테이블을 구성하는 속성 정의
    [PRIMARY KEY (속성_리스트)] // 2. 기본키 정의
    [UNIQUE (속성_리스트)] // 3. 대체키 정의
    [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)] // 4. 외래키 정의
      [ON DELETE 옵션] [ON UPDATE 옵션]
    [CONSTRAINT 이름] [CHECK(조건)] // 5. 데이터 무결성을 위한 제약조건 정의
);

• UNIQUE 속성 값은 기본키 속성 값과 달리 널 값이 허용됨
• 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 투플 삭제 시 처리 방법을 지정하는 옵션
  • ON DELETE/UPDATE NO ACTION: 투플을 삭제하지 못하게 함
  • ON DELETE/UPDATE CASCADE: 관련 투플을 함께 삭제함
  • ON DELETE/UPDATE SET NULL: 관련 투플의 외래키 값을 NULL로 변경
  • ON DELETE/UPDATE SET DEFAULT: 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경
• CHECK: 특정 속성에 대한 제약조건을 지정, CONSTRAINT 키워드와 함께 고유한 이름을 부여할 수 있음
  • ex. CHECK(재고량 >= 0 AND 재고량 <= 10000)

속성의 데이터 타입

 

테이블 변경: ALTER TABLE

새로운 속성 추가

ALTER TABLE 테이블_이름
ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];

 

기존 속성 삭제

ALTER TABLE 테이블_이름
DROP COLUMN 속성_이름;

• 만약 삭제할 속성과 관련된 제약 조건이 존재한다면? 관련된 제약조건을 먼저 삭제해야 함

새로운 제약 조건 추가

ALTER TABLE 테이블_이름
ADD CONSTRAINT 제약조건_이름 제약조건_내용;

 

기존 제약 조건 삭제

ALTER TABLE 테이블_이름
DROP CONSTRAINT 제약조건_이름;

 

테이블 삭제: DROP TABLE

DROP TABLE 테이블_이름;

• 삭제할 테이블을 참조하는 테이블이 있다면? 관련된 외래키 제약조건을 먼저 삭제해야 함

---

SQL을 이용한 데이터 조작

데이터 검색: SELECT

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트;

• ALL: 기본 값 / DISTINCT: 중복 제거 옵션

DISTINCT: NULL 값 포함

NULL 값도 제거하고 싶다면? WHERE 절을 추가해서 NOT NULL 조건을 부여하는 방법 사용

 

모든 속성을 검색하고 싶을 때

SELECT * FROM 테이블

 

ALIAS: AS 키워드를 사용해 결과 테이블에서 속성의 이름을 바꾸어 출력 가능

• 새로운 이름에 공백이 포함되어 있으면 큰따옴표 or 작은따옴표로 묶어주어야 함
• AS 키워드 생략 가능

SELECT 제품명, 단가 AS 가격 
FROM 제품;

 

산술연산자

• (), *, /, +, -
• 산술식을 이용한 검색 -> 속성값이 실제로 변경되는 것이 아니라 결과 테이블에서만 계산된 결과 값이 출력됨

합성 연산자

• 문자와 문자를 연결: +, CONCAT(str1, str2)

SELECT name + '학생은 ' + dept_name + '학과에 재학중입니다.' 
FROM student;

 

WHERE 조건 검색

• WHERE 키워드와 함께 검색 조건 제시
  • 숫자뿐만 아니라 문자나 날짜 값 비교 연산도 가능 ex. 'A' < 'C'
  • 조건에서 문자나 날짜 값은 작은따옴표로 묶어서 표현
• 비교 연산자: =, <>(다르다), <, >, <=, >=
• 논리 연산자: AND, OR, NOT

SELECT 주문제품, 수량
FROM 주문
WHERE 주문고객 = 'suji' AND 수량 > 15;

 

LIKE를 이용한 조건 검색

• 부분적으로 일치하는 데이터 검색, 문자열을 이용하는 조건에만 LIKE 사용 가능
• %: 0개 이상의 문자 (문자 내용과 개수 상관 없음), _ : 1개의 문자 (문자 내용은 상관없음)
• ex. LIKE '%데이터%': 데이터가 포함된 문자열 , LIKE '데이터_' : 데이터로 시작하는 4자 길이의 문자열

NULL을 이용한 조건 검색

• IS NULL / IS NOT NULL
• 검색 조건에서 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 됨

ORDER BY 정렬 검색

• 정렬 기준이 되는 속성과 정렬 방식을 지정해 결과 테이블을 사용자가 원하는 순서로 출력
• ASC: 오름차순 (디폴트값), DESC: 내림차순
• 여러 기준에 따라 정렬하는 것도 가능

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[ORDER BY 속성_리스트 [ASC | DESC]];

 

집계 함수를 이용한 검색

• 집계 함수(aggregate function) == 열 함수(column function)
  • NULL인 속성 값은 제외하고 계산
  • WHERE절에서는 사용할 수 없고, SELECT 절이나 HAVING 절에서만 사용 가능

 

SELECT AVG(단가) FROM 제품;

 

GROUP BY 그룹별 검색

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건]]
[ORDER BY 속성_리스트 [ASC | DESC]];

• 특정 속성의 값이 같은 투플을 모아 그룹을 만들고 그룹별로 검색
  • GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성 지정
• HAVING 키워드를 함께 이용해 그룹에 대한 조건을 작성
• 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋음

// 제품을 3개 이상 제조한 제조업체별로 그룹을 나눠 검색
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*) > 3;

 

JOIN 검색

• 조인 검색: 여러 개의 테이블을 연결하여 데이터를 검색하는 것
• 조인 속성: 조인 검색을 위해 테이블을 연결해주는 속성 - 일반적으로 외래키를 조인 속성으로 이용, 도메인이 같아야함

SELECT 제품.제품명
FROM 제품, 주문
WHERE 제품.제품번호 = 주문.주문제품

SELECT 제품.제품명
FROM 고객, 제품, 주문
WHERE 고객.고객이름 = 'suji' AND 고객.고객아이디 = 주문.주문고객 AND 제품.제품번호 = 주문.주문제품;

 

부속 질의문을 이용한 검색

• SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
  • 상위 질의문(주 질의문) / 부속 질의문(서브 질의문)
  • 부속 질의문: 괄호로 묶어서 작성, ORDER BY 사용 불가
  • 부속 질의문을 먼저 수행하고, 그 결과를 이용해서 상의 질의문 수행

SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (SELECT MAX(적립금) FROM 고객);

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품
                 FROM 주문
                 WHERE 주문고객 = 'suji');

 

데이터 삽입: INSERT

INSERT
INTO 테이블_이름[(속성_리스트)]
VALUES (속성값_리스트);

• 속성 키워드 생략하면 테이블 정의할 때 지정한 속성의 순서대로 값 삽입
• 전달하지 않은 속성 값은 NULL 삽입

부속 질의문을 이용한 데이터 삽입

• SELECT 문을 이용해 다른 테이블에서 검색한 데이터 삽입

  INSERT 
  INTO 한빛제품(제품명, 재고량, 단가)
  SELECT 제품명, 재고량, 단가
  FROM 제품
  WHERE 제조업체 = '한빛제과';

 

데이터 수정: UPDATE

UPDATE 테이블_이름
SET 속성_이름1 = 값1, 속성_이름2 = 값2, ...
[WHERE 조건];

• WHERE 절에 제시된 조건을 만족하는 투플에 대해서만 속성 값 수정, 생략하면 모든 투플 수정

부속 질의문을 이용한 UPDATE 문

UPDATE 주문
SET 수량 = 5
WHERE 주문고객 IN (SELECT 고객아이디
                 FROM 고객
                 WHERE 고객이름 = 'suji');           

 

데이터 삭제: DELETE

DELETE
FROM 테이블_이름
[WHERE 조건];

• WHERE 절에 제시한 조건을 만족하는 투플만 삭제, 생략하면 테이블에 존재하는 모든 투플을 삭제해 빈 테이블이 됨

부속 질의문을 이용한 DELETE 문

DELETE
FROM 주문
WHERE 주문고객 IN (SELECT 고객아이디
                 FROM 고객
                 WHERE 고객이름 = 'suji');     

DELETE FROM 주문; vs DROP TABLE 주문;

DELETE 문은 DROP TABLE과는 달리 빈 테이블이 남는다.

---

뷰

뷰(View)

• 다른 테이블을 기반으로 만들어진 논리적으로만 존재하는 가상 테이블
• 뷰를 통해 기본 테이블(뷰를 만드는 기반이 되는 물리적인 테이블)의 내용을 쉽게 검색할 수 있지만, 기본 테이블의 내용을 변화시키는 작업은 제한적으로 이루어짐
• 기본 테이블을 들여다 볼 수 있는 창의 역할

뷰의 장점

• 질의문을 더 쉽게 작성할 수 있다.
  • 미리 뷰를 만들어 놓으면, 복잡한 SQL 문을 작성하지 않아도 SELECT 절과 FROM 절만으로도 원하는 데이터 검색이 가능
• 데이터 보안 유지에 도움이 된다.
  • 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한 설정 가능
• 데이터를 좀 더 편리하게 관리할 수 있다.
  • 제공된 뷰와 관련이 없는 다른 내용에 대해 사용자가 신경 쓸 필요가 없음

 

뷰 생성: CREATE VIEW

CREATE VIEW 뷰_이름[(속성_리스트)]
AS SELECT 문
[WITH CHECK OPTION];

• 속성 리스트를 생략하면 SELECT 절에 나열된 속성 이름을 그대로 사용
• AS 키워드와 함께 기본 테이블에 대한 SELECT 문 작성: 생성하려는 뷰의 정의를 표현, ORDER BY는 사용 불가
• WITH CHECK OPTION: 뷰에 삽입, 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약 조건 지정

CREATE VIEW 업체별제품수(제조업체, 제품수)
AS SELECT 제조업체, COUNT(*)
FROM 제품
GROUP BY 제조업체
WITH CHECK OPTION;

 

뷰 활용: SELECT, INSERT, UPDATE, DELETE 문

SELECT 문

• 일반 테이블과 동일한 방법으로 데이터 검색
• 뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행
• 검색 연산은 모든 뷰에 수행 가능

INSERT, UPDATE, DELETE 문

• 뷰에 대한 삽입, 수정, 삭제 연산은 실제로 기본 테이블에 수행되므로 결과적으로는 기본 테이블이 변경됨
• 뷰에 대한 삽입, 수정, 삭제 연산은 제한적으로 수행 됨: 변경 가능한 뷰 vs 변경 불가능한 뷰
• 변경 불가능한 뷰의 특징
  • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
  • 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함하는 뷰
  • DISTINCT 키워드를 포함하여 정의한 뷰
  • GRPUP BY 절을 포함하여 정의한 뷰
  • 여러 개의 테이블을 조인하여 정의한 뷰는 변경이 불가능한 경우가 많음

 

뷰 삭제: DROP VIEW

DROP VIEW 뷰_이름;

• 뷰를 삭제해도 기본 테이블은 영향을 받지 않는다.
• 삭제할 뷰를 참조하는 제약조건이 존재한다면? 관련된 제약조건을 먼저 삭제해야 함

---

삽입 SQL

삽입 SQL(ESQL; Embedded SQL)

• 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문

특징

• 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
• 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙임
• 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 때는 이름 앞에 콜론(:)을 붙여서 구분

커서(cursor)

• 수행 결과로 반환된여러 행을 한 번에 하나씩 가리키는 포인터
• 여러 개의 행을 결과로 반환하는 SELECT 문을 프로그램에서 사용할 때 필요

삽입 SQL 문에서 사용할 변수 선언 방법

• BEGIN DECLARE SECTION과 END DECLARE SECTION 사이에서 선언

커서가 필요 없는 삽입 SQL

• CREATE TABLE, INSERT, DELETE, UPDATE
• 결과로 행 하나만 반환하는 SELECT

 

C 언어에서의 삽입 SQL

int main() {
    EXEC SQL BEGIN DECLARE SECTION;
        char p_no[4], p_name[21];
        int price;
    EXEC SQL END DECLARE SECTION;

    printf("제품번호를 입력하세오: ");
    scanf("%s", p_no);

    EXEC SQL SELECT 제품명, 단가 INTO :p_name, :prie
        FROM 제품
        WHERE 제품번호 = :p_no;

    printf("\n 제품명 = %s", p_name);
    printf("\n 단가 = %d", price);
}

 

커서가 필요한 삽입 SQL

• 커서를 선언

 

EXEC SQL DECLARE 커서_이름 CURSOR FOR SELECT 문;

EXEC SQL DECLARE product_cursor CURSOR FOR
SELECT 제품명, 단가 FROM 제품;

 

 

• 커서에 연결된 SELECT 문 실행

EXEC SQL OPEN 커서_이름;

EXEC SQL OPEN product_cursor;

 

• 커서를 이동
  • 커서를 이동하여 처리할 다음 행을 가리키도록 하고, 커서가 가리키는 행으로부터 속성값을 가져와 변수에 저장
  • 결과 테이블에는 여러 행이 존재하므로 FETCH 문을 반복해서 여러 번 수행해야 함

EXEC SQL FETCH 커서_이름 INTO 변수_리스트;

EXEC SQL FETCH product_cursor into :p_name, :price;

 

• 커서 사용 종료

EXEC SQL CLOSE 커서_이름;

EXEC SQL CLOSE product_cursor;

 

---

JOIN

EQUI JOIN

EQUI JOIN

• 두 개의 테이블 간에 특정 열(칼럼) 값이 서로 동일한 경우에 해당 행을 결합하는 방법

SELECT PLAYER_NAME AS 선수명, TEAM_NAME AS 소속팀명
FROM PLAYER JOIN TEAM
ON TEAM.TEAM_ID = PLAYER.TEAM_ID

• JOIN 키워드는 내부적으로 INNER JOIN이 수행된다.

 

Non EQUI JOIN

SELECT A.ENAME 사원명, A.SAL 급여, B.GRADE 급여등급
FROM EMP A, SAL_GRADE B
WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
ORDER BY 급여등급;

 

INNER JOIN vs OUTER JOIN

INNER JOIN

• 두 테이블 간의 공통된 값을 가진 행만 반환 -> 즉, 조인 조건에 맞는 행들만을 결과로 가져옴

OUTER JOIN

• 두 테이블 간에 일치하는 행이 없어도 결과를 반환 -> 즉, 조인 조건에 맞지 않는 행들도 결과에 포함
• LEFT OUTER JOIN

    SELECT *
    FROM 테이블A
    LEFT OUTER JOIN 테이블B ON 테이블A.ID = 테이블B.ID;

  • 테이블 A의 모든 행을 결과에 포함하고, 테이블 B에서 일치하는 행이 없는 경우에는 NULL 값
• RIGHT OUTER JOIN
• FULL OUTER JOIN

    SELECT *
    FROM DEPT A FULL OUTER JOIN DEPT_TEMP B
    ON B.DEPTNO = A.DEPTNO