데이터베이스 언어 SQL

SQL의 소개

• SQL : 관계 데이터베이스를 위한 표준 질의어. 비절차적 데이터 언어

1. 데이터 정의어 (DDL): 테이블을 생성하고 변경, 제거
2. 데이터 조작어 (DML): 테이블에 데이터 삽입, 수정, 삭제, 검색
3. 데이터 제어어 (DCL): 보안을 위해 테이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소

SQL을 이용한 데이터 정의

• 테이블 생성, 변경, 제거

1. CREATE TABLE
2. ALTER TABLE
3. DROP TABLE

테이블의 생성 : CREATE TABLE

CREATE TABLE 테이블이름(
(1) 속성이름 데이터타입 [NOT NULL][DEFAULT 기본값]
(2) [PRIMARY KEY (속성리스트)]
(3) [UNIQUE (속성리스트)]
(4) [FOREIGN KEY (속성리스트) REFERENCES 테이블이름(속성 리스트)]
[ON DELETE 옵션] [ON UPDATE 옵션]
(5) [CONSTRAINT 이름][CHECK(조건)]
)

1. 테이블을 구성하는 각 속성의 이름과 데이터 타입과 기본적인 제약 사항 정의
2. 기본키로 테이블에 하나만 존재
3. 대체키로 테이블에 여러 개 존재할 수 있음
4. 외래키로 테이블에 여러 개 존재할 수 있음
5. 테이블 무결성을 위한 제약조건, 테이블에 여러개 존재 가능
   ([ ]는 생략이 가능)

속성의 정의

• 속성의 데이터 타입 : INT(INTEGER), SMALLINT, CHAR, VARCHAR, NUMERIC(p, s), DECIMAL(p, s), FLOAT, REAL DATETIME(DATE), TIME
• ex. 고객아이디 VARCHAR(20) NOT NULL : 고객 아이디 속성을 길이가 최대 20인 가변길이의 문자열 데이터, 널 값 허용하지 않음
• ex. 적립금 INT DEFAULT 0 : 적립금 속성을 정수 데이터로 구성하고, 입력하지 않으면 0이 기본으로 저장

키의 정의

• 기본키, 대체키, 외래키를 지정할 수 있음

1. 기본키 : PRIMARY KEY, 반드시 하나, 여러개의 속성으로 구성 가능
   • ex. PRIMARY KEY(고객 아이디) / PRIMARY KEY(주문고객, 주문 제품)
2. 대체키 : UNIQUE KEY, 여러개 지정 가능, 널 값 가질 수 있음
   • ex. UNIQUE(고객이름)
3. 외래키 : FOREIGN KEY, 출처 표시 분명히(참조 무결성 제약조건을 유지)
   • 참조되는 테이블에서 튜플을 삭제하거나 변경할 때 처리하는 방법
     • 지정하지 않으면 ON DELETE(UPDATE) NO ACTION
     1. ON DELETE(UPDATE) NO ACTION : 튜플을 삭제(변경)하지 못하게 함
     2. ON DELETE(UPDATE) CASCADE : 관련 튜플을 함께 삭제(변경)함
     3. ON DELETE(UPDATE) SET NULL : 관련 튜플의 외래키 값을 NULL로 변경
     4. ON DELETE(UPDATE) SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경함
     • ex. FOREIGN KEY(소속부서) REFERENCES 부서(부서번호)
       ON DELETE CASCADE ON UPDATE CASCADE

데이터 무결성 제약조건의 정의

• CHECK : 특정 속성에 대한 제약 조건을 지정할 수 있음
• 테이블에는 CHECK 조건을 만족하는 튜플만 존재 가능, 삽입/삭제/수정 시에도 제약조건 지켜야함
• ex. CHECK(재고량 >= 0 AND 재고량 <=10000)
• CONSTRAINT 와 함께 고유의 이름 부여
  • ex. CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')

테이블 생성의 예

CREATE TABLE 제품(
제품번호 CHAR(3) NOT NULL,
제품명 VARCHAR(20),
제고량 INT,
단가 INT,
제조업체 VARCHAR(20),
PRIMARY KEY(제품 번호),
CHECK(재고량 >= 0 AND 재고량 <=10000)
)

테이블의 변경

• CASCADE : 관련된 제약조건이나 참조하는 다른 속성을 함께 삭제
• RESTRICT : 관련된 제약조건이나 참조하는 다른 속성이 존재하면 삭제가 수행되지 않음

새로운 속성의 추가

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

• ex. ALTER TABLE 고객 ADD 가입날짜 DATETIME;

기존 속성의 삭제

ALTER TABLE 테이블이름 DROP 속성이름 CASCADE|RESTRICT;

• ex. ALTER TABLE 고객 DROP 등급 CASCADE;

새로운 제약조건의 추가

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

• ex. ALTER TABLE 고객 ADD CONSTRAINT CHK_AGE CHECK(나이 >= 20);

기존 제약조건의 삭제

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

• ex. ALTER TABLE 고객 DROP CONSTRAINT CHK_AGE;

테이블의 제거

DROP TABLE 테이블이름 CASCADE|RESTRICT;

• ex. DROP TABLE 고객 RESTRICT;

SQL을 이용한 데이터 조작

SQL의 데이터 조작 기능

• 원하는 데이터 검색, 삽입, 수정, 삭제

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

데이터의 검색

기본 검색

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

• ex. SELECT 고객아이디, 고객이름, 등급 FROM 고객;
• ex. SELECT * FROM 고객;
• ex. SELECT ALL 제조업체 FROM 제품; -> 중복을 허용을 명시적으로 표시
• ex. SELECT DISTINCT 제조업체 FROM 제품; -> 중복 제거
• ex. SELECT 제품명, 단가 AS 가격 FROM 제품;

산술식을 이용한 검색

• ex. SELECT 제품명, 단가+500 AS 조정단가 FROM 제품;

조건 검색

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

• ex. SELECT 제품명, 재고량, 단가
  FROM 제품
  WHERE 주문고객 = 'APPLE' AND 수령 >= 15;

• LIKE

  • % : 0개 이상의 문자 (문자의 내용과 개수는 상관 없음)
    • LIKE '데이터%', LIKE '%데이터', LIKE '%데이터%'
  • _ : 한 개의 문자 (문자의 내용은 상관 없음)
    • LIKE '데이터___', LIKE '__한%'

• ex. SELECT 고객이름, 나이, 등급, 적립금 FROM 고객
  WHERE 고객이름 LIKE '김%'

• ex. SELECT 고객이름, 나이, 등급 FROM 고객
  WHERE 고객아이디 LIKE '_____'

NULL을 이용한 검색

• ex. SELECT 고객이름 FROM 고객 WHERE 나이 IS NULL;
• ex. SELECT 고객이름 FROM 고객 WHERE 나이 IS NOT NULL;

정렬 검색

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

• ex. SELECT 주문고객, 주문제품, 수량, 주문일자
  FROM 주문
  WHERE 수량 >= 10
  ORDER BY 주문제품 ASC, 수량 DESC;

집계함수를 이용한 검색

• 집계함수 = 열함수

• 널인 속성 값은 제외하고 계산한다

• WHERE 절에서 사용 불가 / SELECT나 HAVING 절에서만 사용

  1. COUNT : 속성 값의 개수 / 모든 데이터
  2. MAX : 속성 값의 최대값 / 모든 데이터
  3. MIN : 속성 값의 최솟값 / 모든 데이터
  4. SUM : 속성 값의 합계 / 숫자 데이터
  5. AVG : 속성 값의 평균 / 숫자 데이터

• ex. SELECT AVG(단가) FROM 제품;

• ex. SELECT SUM(재고량) AS '재고량 합계' FROM 제품 WHERE 제조업체 > '한빛제과';

• ex. SLEECT COUNT(DISTINCT 제조업체) AS '제조업체 수' FROM 제품;

그룹별 검색

• GROUP BY : 그룹 만들기
• HAVING : GROUP BY로 묶인 그룹에 조건 추가

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

• ex. SELECT 주문제품, SUM(수량) AS 총주문수량
  FROM 주문
  GROUP BY 주문 제품

• ex. SELECT 제조업체, COUNT() AS 제품수, MAX(단가) AS 최고가
  FROM 제품
  GROUP BY 제조업체 HAVING COUNT()>=3;

• ex. SELECT 주문제품, 주문고객, SUM(수량) AS 총주문수량
  FROM 주문
  GROUP BY 주문 제품, 주문 고객;

여러 테이블에 대한 조인 검색

• 조인 검색 : 여러 개의 테이블을 연결하여 데이터를 검색하는 것
  • 조인 속성 : 연결해주는 속성 필요, 이름은 달라도 도메인은 반드시 같아야한다. 테이블 관계를 나타내는 외래키를 일반적으로 조인 속성으로 사용
• ex. SELECT 제품, 제품명 FROM 제품, 주문
  WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;
  • FROM 절에 검색에 필요한 모든 테이블 나열
  • WHERE 절에 조인 조건 제시 (조인 속성의 값이 같아야함)

부속 질의문을 이용한 검색

• '달콤비스켓'과 같은 제조업체에서 제조한 제품의 제품명과 단가를 검색

  • ex. SELECT 제품명, 단가 FROM 제품
    WHERE 제조업체 = (SELECT 제조업체 FROM 제품 WHERE 제품명 = '달콤비스켓')

• banana 고객이 주문한 제품의 제품명과 제조업체 검색

  • ex. SELECT 제품명, 제조업체 FROM 제품 WHERE 제품번호 IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana')

데이터의 삽입

데이터 직접 삽입

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

• INTO 절의 속성이름과 VALUES 절의 속성값은 일대일 대응, 개수 같아야 됨
• ex. INSERT
  INTO 고객[(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)]
  VALUES ('strawberry', '최유경, 30, 'vip', '공무원', 100);

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

INSERT
INTO 테이블이름[(속성리스트)]
SELECT 문;

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

데이터의 수정

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

• 제품번호가 p03인 제품의 제품명을 통큰파이로 수정
  • ex. UPDATE 제품
    SET 제품명 = '통큰파이'
    WHERE 제품번호 = 'p03'
• 제품 테이블의 모든 제품의 단가를 10% 인상
  • ex. UPDATE 제품
    SET 단가 = 단가 * 1.1
• 정소화 고객이 주문한 제품의 주문 수량을 5개로 수정
  • UPDATE 주문
    SET 주문수량 = 5
    WHERE 주문고객 IN (SELECT 고객아이디 FROM 고객 WHERE 고객이름 = '정소화')

데이터의 삭제

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

• ex. DELETE
  FROM 주문
  WHERE 주문일자 = '2013-05-22'

뷰

뷰의 개념

• 뷰 : 다른 테이블을 기반으로 만들어진 가상테이블
  • 일반 테이블과 달리 데이터를 실제로 저장하고 있지 않다
  • 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용
• 기본 테이블 : 뷰를 만드는 데 기반이 되는 물리적인 테이블
• 다른 뷰를 기반으로 새로운 뷰를 만들 수도 있다

뷰의 생성

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

• SELECT 문에서 ORDER BY 사용 불가능

• WITH CHECK OPTION : 생성한 뷰에 삽입이나 수정 연산 시 SELECT문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건

• ex. CREATE VIEW 우수고객[(고객아이디, 고객이름, 나이)]
  AS SELECT 고객아이디, 고객이름, 나이
  FROM 고객
  WHERE 등급 = 'vip'
  WITH CHECK OPTION;

뷰의 활용

• 생성된 뷰는 일반 테이블처럼 원하는 데이터 검색 가능
• ex. SELECT * FROM 우수고객 WHERE SKDL >= 25;
• INSERT, UPDATE, DELETE 문도 수행가능
  • 기본 테이블이 변함
  • 뷰를 통해 값을 제시하지 않은 속성은 널 값으로 들어가게 됨
    • 그런데 기본키는 널값을 가질 수 없기 때문에 기본키값을 제시하지 않으면 연산이 안됨
      • INSERT INTO 제품 1 VALUES ('p08', 1000, '신선식품'); -> (O)
      • INSERT INTO 제품 1 VALUES ('시원냉명', 1000, '신선식품'); -> (X)
  • 집계함수에 의해 계산된 값 포함하고 있는 것도 삽입, 삭제, 수정 연산 불가능

1. 기본 테이브르이 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.
2. 기본 테이블에 있던 내용이 아니라 집계함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수 없다.
3. DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다.
4. GROUP BY 절을 포함하여 정의한 뷰는 변경할 수 없다.
5. 여러 개의 테이블을 조인하여 정의한 뷰는 변결할 수 없는 경우가 많다.

• 뷰의 장점
  1. 질의문을 좀 더 쉽게 작성할 수 있다.
  2. 데이터의 보안 유지에 도움이 된다.
  3. 데이터를 좀 더 편리하게 관리할 수 있다.

뷰의 삭제

DROP VIEW 뷰이름 CASCADE | RESTRICT;

• 기본 테이블은 영향을 받지 않는다
• DISTINCT : 삭제할 뷰를 이용해 만들어진 다른 뷰 존재 시 삭제 수행되지 않음
• CASCADE : 삭제할 뷰를 이용하는 다른 뷰를 모두 함께 삭제

삽입 SQL

삽입 SQL의 개념과 특징

• 삽입 SQL : C, C++, JAVA 등과 같은 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문

1. 삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있다.
2. 프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙인다
3. 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 수 있다. 단, SQL문에서 일반 변수를 사용할 때는 앞에 콜론(:)을 붙여 이름이나 속성의 이름과 구분한다.

커서가 필요 없는 삽입 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, :price
    			FROM 제품
            	WHERE 제품번호 = :p_no;
        # 출력
    	printf("\n 제품명 = %s", p_name);
        printf("\n 단가 = %d", price);
        return 0;
}

커서가 필요한 삽입 SQL

• 커서 : SELECT 문의 실행 결과로 여러 행이 검색되는 경우 한번에 한행씩 차례로 접근할 수 있게 해줌

1. 커서 선언

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

• ex. EXEC SQL DELARE product_cursor CURSOR FOR
  SELECT 제품명, 단가 FROM 제품;

2. SELECT문 실행하는 명령

   EXEC SQL OPEN 커서이름;

• ex. EXEC SQL OPEN product_cursor; -> product_cursor 커서에 연결된 SELECT 문 실행

3. 커서 이동

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

• ex. EXEC SQL FETCH product_cursor INTO :p_name, :price -> 커서를 이동해 결과 테이블의 다음 행에 접근하여 제품명 속성의 값을 변수에 저장

4. 커서를 더 이상 사용하지 않을 때

   EXEC SQL CLOSE 커서이름;

• ex. EXEC SQL CLOSE product_cursor;