[DataBase] 데이터베이스 언어 SQL

데이터베이스 언어 SQL

 

 

 

 

SQL (Structured Query Language)

---

SQL은 관계 데이터베이스를 위한 표준 질의어로 많이 사용되는 언어다. 사용자가 처리를 원하는 데이터가 무엇인지만 제시하고 데이터를 어떻게 처리해야 하는지 언급할 필요가 없어, 비절차적 데이터 언어의 특징을 띈다.

데이터베이스 관리 시스템에 직접 접근하여 대화식으로 질의를 작성 할 수 있다. C++, Java 같은 언어로 작성한 응용프로그램에 삽입하여 사용할 수 도 있다.

 

[SQL기능에 따른 분류]

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

 

 

 

SQL을 이용한 데이터 정의

---

[SQL의 데이터 정의]

• 테이블 생성 : CREATE TABLE
• 테이블 변경 : ALTER TABLE
• 테이블 삭제 : DROP TABLE

---

[테이블 생성 정의]

* [] - 생략 가능, 세미콜론(;)으로 문장 끝을 의미

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

[속성의 정의]

테이블을 구성하는 각 속성의 데이터 타입을 선택 후, 속성의 널 값 허용 여부와 기본 값 필요 여부 결정

• 기본적으로 널 값 허용한다.
• 널값을 허용하지 않을려면 NOT NULL 키워드 포함해야 한다.
• 기본키를 구성하는 모든 속성은 널 값을 가질 수 없다.(개체 무결성 제약조건)

 

[키의 정의]

기본키 - PRIMARY KEY

• 투플을 식별할 수 있는 기본키, 속성 여러개 지정 가능
• 예 : PRIMARY KEY(고객 아이디) 
• 예 : PRIMARY KEY(주문고객, 주문제품)

대체키 - UNIQUE

• 기본키와 같이 각 투플을 식별, 유일성을 가져야하고 널 값을 허용
• 예 : UNIQUE(고객이름) 

외래키 - FOREIGN KEY 

외래키가 어떤 테이블이 속성을 참조하는지, REFERENCES 키워드 다음 명확히 제시(참조 무결성 제약조건 유지)

• ON DELETE or UPDATE NO ACTION : 투플을 삭제 or 변경하지 못하게 함
• ON DELETE or UPDATE CASCADE : 관련 투플도 함께 삭제 or 변경  
• ON DELETE or UPDATE SET NULL : 관련 투플의 외래키값을 NULL로 변경
• ON DELETE or UPDATE SET DEFAULT  : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경

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

CHECK 테이블에 정확하고 유효한 데이터를 유지하기 위해 데이터 무결성을위한 제약조건을 표현하는 방법

• CONSTRAINT 키워드와 함께 고유 이름을 부여할 수 있다.
• 예 : CHECK(재고량 >= 0 AND 재고량 <= 10000) 
• 예 : CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')

---

[테이블 변경]

ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다.

 

[새로운 속성 추가]

ALTER TABLE 테이블이름 ADD 속성이름 데이터타입;

 

[기존 속성 삭제]

ALTER TABLE 테이블이름 DROP COLUMN 속성이름;
-- 오라클 : CASCADE CONSTRAINTS 옵션을 추가하여 제약조건을 함께 삭제할 수 있다. --

 

[새로운 제약조건 추가]

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

 

[새로운 제약조건 삭제]

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

---

[테이블 삭제]

DROP TABLE 테이블이름;
-- 오라클에서는 [CASCADE CONSTRAINTS] 옵션을 추가하여 관련된 외래키 제약조건을 함게 삭제할 수 있다.

• 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않는다.
• 삭제하고나 하는 테이블을 참조하는 외래키 제약조건을 먼저 삭제해야 한다.

 

 

 

SQL을 이용한 데이터 조작

---

[SQL의 데이터 조작 기능]

• 데이터 검색 : SELECT
• 데이터 삽입 : INSERT
• 데이터 수정 : UPDATE
• 데이터 삭제 : DELETE

---

[데이터 검색 ]

테이블에 원하는 데이터를 검색하기 위해 필요한 SELECT 문은 다양한 검색 유형을 지원한다.

 

[기본검색]

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

• SELECT 키워드와 함께 검색하고 싶은 속성의 이름을 콤마(,)로 구분 차례로 나열한다.
• FROM 키워드와 함께 검색하고 싶은 테이블의 이름을 콤마(,)로 구분 차례로 나열한다.
•  ALL 키워드 : 테이블 중복허용 검색
•  DISTINCT 키워드 : 테이블 중복제거 검색

[산술식를 이용한 검색]

SELECT 키워드와 함꼐 산술식을 제시할 수 있다. 산술식은 속성의 이름과  +, -, * , / 등의 산술 연산자, 상수로 구성된다.

SELECT 제품, 제품가격 + 500  AS "조정가격"
FROM   제품;

 

[조건 검색]

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

• WHERE 키워드와 함께 조건에서는 비교 연산자를 이용해 숫자뿐 아니라, 문자나 날짜 값을 비교할 수 있다.
• 단 조건에서는 문자, 날짜 값은 작은 따옴표(')로 묶어 속성이름과 구별해줘야한다.
• 논리 연산자는 조건을 여러 개 결합하거나 조건을 만족하지 않는 데이터를 검색한다.

[LIKE를 이용한 검색]

• 검색 조건을 부분적으로만 알고 있다면 LIKE 키워드를 이용해 검색할 수 있다.
• 단, LIKE 키워드는 문자열을 이용하는 조건에만 사용할 수 있다.

[NULL을 이용한 검색]

SELECT 고객이름
FROM 고객
WHERE 나이 IS NULL;

• 검색 조건에서 특정 속성이 값이 널 값인지 비교하려면 IS NULL 키워드를 사용
• 널 값이 아닌지 비교하라면 IS NOT 키워드 사용

[정렬 검색]

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

• SELECT 문의 검색 결과 테이블은 DBMS가 정한 순서로 출력된다.
• 결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다.
• ORDER BY 키워드와 함께, 오름차순 정렬이면 ASC, 내림차순 정렬이면 DESC로 표현한다.

[집계 함수를 이용한 검색]

• 집계 함수는 널인 속성 값은 제외하고 계산한다.
• 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있다.

[그룹별 검색]

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

• 테이블 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색하기 위해 GROUP BY 키워드를 사용한다.
• 그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용한다.

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

SELECT 속성_리스트
FROM   테이블_리스트
[ WHERE 검색조건, 조인조건 ]

SELECT 주문.주문제품, 주문.주문일자
FROM   고객, 주문
WHERE  고객.나이 >= 30 AND 고객.고객아이디 = 주문.주문고객;

• 조인 검색을 하려면 테이블을 연결해주는 속성이 필요하고, 이 속성을 조인 속성이라고 한다.
• 조인 속성의 이름은 달라도 되지만, 도메인은 반드시 같아야한다. 
• 일반적으로 테이블의 관계를 나태는 외래키를 조인 속성으로 이용한다.

[표준 SQL 조인검색]

표준 SQL에서는 INNER JOIN과 ON 키워드를 이용해 작성하는 방법도 있다.

SELECT 속성_리스트
FROM   테이블1 INNER JOIN 테이블2 ON 조인조건
[ WHERE  검색조건 ]

SELECT 주문.주문제품, 주문.주문일자
FROM   고객 INNTER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객
WHERE  고객.나이 >= 30;

 

[외부 조인 검색]

조인 조건을 만족하지 않는 투플에 대해서도 OUTER JOIN과 ON 키워드를 이용해 검색이 가능하다.

SELECT 속성_리스트
FROM   테이블1 LEFT | RIGHT | FULL OUTER JOIN 테이블2 ON 조인조건
[ WHERE 검색조건 ]

SELECT 고객.고객이름, 주문.주문제품, 주문.주문일자
FROM   고객 LEFT OUTER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객;

 

[부속 질의문을 이용한 검색]

SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의문이며, 서브 질의문이라 한다.

• 상위 질의문(주 질의문) - 다른 SELECT 문을 포함하는 SELECT 문
• 부속 질의문(서브 질의문) -  다른 SELECT 문 안에 들어 있는 SELECT 문
• 괄호()로 묶어 작성하고 ORDER BY 절을 사용할수 없다.
• 상위 질의문과 부속 질의문 연결하는 연산자가 필요하다.
• 단일 행 부속 질의문 : 하나의 행을 결과로 반환하며, 일반 비교 연산자를 사용할 수 있다
• 다중 행 부속 질의문 : 하나 이상의 행을 결과로 반환하며, 일반 비교 연산자를 사용할 수 없다.

단일 행 부속 질의문

• 1번은 SELECT 문은 행 하나를 결과로 반환하는 단일  부속 질의문이다. 따라서 일반 비교연산자(=) 를 사용해 연결해 , SELECT 문이 먼저 수행되고
• 2번 SELECT 문(상위 질의문)이 수행되어 최종 결과 테이블을 반환한다.

다중행 부속 질의문

• 위 결과 값을 여러개 반환하는 다중 행 부속 질의문은 일반 비교 연산자(=) 대신 IN 연산자를 함께 사용해야한다.
• IN 연산자는 부속 질의문의 결과 값들 중에서 하나라도 일치하는 것이 있으면 검색 조건이 참이 되게하는 연산자다.

다중 행 부속 질의문에 사용가능한 연산자

---

[데이터의 삽입]

테이블에 투플을 직접 삽입하는 필요한 SQL 명령어는 INSERT 이다.

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

INSERT
INTO	고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES  ('STRAWBERRY', '최유경', 30, 'VIP', 공무원, 100);

INSERT
INTO	고객
VALUES  ('STRAWBERRY', '최유경', 30, 'VIP', 공무원, 100);

• INTO 키워드와 투플을 삽입할 테이블의 속성 이름을 나열, 나열한 순서대로 VALUES 속성값에 차례대로 삽입
• INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일로 대응된다.
• INTO 절에서 속성 이름을 생략할 수 있으며, 생략한 경우 지정한 속성 순서대로 VALUES 속성값이 삽입된다.

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

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

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

---

[데이터 수정]

테이블에 저장된 데이터를  수정하기위해 필요한 SQL 멸령어는 UPDATE이다.

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

-- p03 제품의 제품명 속성 값 통근파이로 변경 --
UPDATE	제품
SET		제품명 = '통근파이'
WHERE	제품번호 = 'p03';

-- 제품 테이블 모든 제품의 단가를 10% 인상 --
UPDATE	제품
SET		단가 = 단가 * 1.1;

• UPDATE 문은 테이블에 저장된 투플에서 특정 속성의 값을 수정한다.
• SET 키워드 다음에 속성_이름을 지정하여 값을 수정한다.
• WHERE 절에 제시된 조건을 만족하는 투플만 속성 값을 수정한다.
• WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 대상을 하여, SET 절에서 지정한 대로 속성 값을 수정한다.

---

[데이터 삭제]

테이블에 저장된 데이터를 삭제하기위해 필요한 SQL 명령어는 DELETE 이다.

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

-- 제품 테이블 제품명 통근파이 삭제 --
DELETE 
FROM	제품
WHERE	제품명 = '통큰파이';

-- 제품 테이블 존재하는 모든 투플 삭제 --
DELETE
FROM	제품;

• DELETE 문은 WHERE 절에 제시한 조건을 만족하는 투플만 삭제한다.
• WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 삭제하여, 빈 테이블이 된다.
• DROP TABLE 문은 테이블 자체를 삭제하는 것과는 다르다.

 

 

 

 

뷰 (View)

---

[뷰 개념]

뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다. 

• 뷰를 가상 테이블이라 하는 이유는 일반 테이블과 달리 데이터를 실제로 저장하지 않고 있다.
• 뷰는 논리적으로 존재하면서 일반 테이블(물리적 존재)과 동일한 방법으로 사용할 수 있다.
• 다른 뷰를 기반으로 새로운 뷰도 만들 수 있다.
• 뷰를 통해 기본 테이블의 내용을 쉽게 검색할수 있지만, 내용을 바꾸는 작업은 제한적이다.

[뷰 생성]

뷰를 생성하기 위해 필요한 SQL 명렁어는 CREATE VIEW 이다. 

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

• CREATE VIEW 명령어와 함께 새로 생성할 뷰 이름과 뷰를 구성하는 속성 이름을 괄호안에 나열한다.
• AS 키워드와 기본 테이블에 대한 SELECT 문을 제시한다.
• SELECT 문을 생성하고자 하는 뷰의 정의를 담고, ORDER BY를 사용할 수 없다.
• WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 WHERE 키워드와 함께 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 제약조건을 의미한다.

오라클 같은 일부 DBMS 에서는 뷰를 생성하기 위한 SELECT 문에서 ORDER BY 사용을 허용하기도 한다.

 

[뷰 활용]

CREATE VIEW 문으로 생성된 뷰에서도 일반 테이블 처럼 원하는 데이터를 검색할 수 도 있다.

• 뷰에 대한 SELECT 문은 내부적으로 기본 테이블에 대한 SELECT 문으로 변환되어 수행된다.
• INSERT 문, UPDATE 문, DELETE 문도 뷰를 대상으로 수행할 수 있으며, 뷰에 대한 삽입, 수정, 삭제 연산도 기본 테이블에 수행되어 결과적으로 기본 테이블이 변한다.
• 다만 변경이 가능한 뷰와 가능하지 않는 뷰로 나뉘어, 연산이 제한적으로 수행된다.

변경이 불가능한 뷰 특징

• 기본 테이블의 기본키를 구성하는 속성이 포함되지 않은 뷰
• 기본 테이블에서 NOT NULL로 지정된 속성이 포함되지 않은 뷰
• 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함한 뷰
• DISTINCT 키워드를 포함하여 정의한 뷰
• GROUP BY 절을 포함하여 정의한 뷰
• 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다.

뷰 장점

질의문을 보다 쉽게 작성할 수 있다.

• GROIP BY, 집계 함수, 조인등을 이용해 미리 뷰를 만들어 놓으면, 복잡한 SQL 문 대신 SELECT 절과 FROM 절만으로 원하는 데이터 검색이 가능하다.

데이터의 보안이 유지에 도움이된다.

• 뷰에 포함되지 않는 사용자로부터 보호할 수 있다.

데이터를 좀 더 편리하게 관리할 수 있다.

• 제공된 뷰에 포함되지 않는 기본 테이블의 다른 부분은 사용자가 신경 쓸 필요가 없다.

[뷰의 삭제]

뷰를 삭제하기 위해 필요한 SQL 명령어는 DROP VIEW 이다.

DROP VIEW 뷰_이름;
-- 오라클에서 CASCADE CONSTRAINTS 옵션을 추가하여 삭제할 뷰와 관련된 모든 참조 무결성을 함께 삭제할 수 있다. --

 

 

 

 

삽입 SQL

---

[삽입 SQL의 개념과 특징]

삽입 SQL(ESQL : Embedded SQL) : 프로그램 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL문

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

[커서가 필요 없는 삽입 SQL]

CRATE TABLE 문, INSERT 문, UPDATE 문 결과로 행 하나만 반환하는 SELECT문은 커서가 필요없다.

int main() {
	//1
	EXEC SQL BEGIN DECLARE SECTION;
    	 char p_no[4], p_name[21];
         int price;
    EXEC SQL END DECLARE SECTION;
    //2
    printf("제품번호를 입력하세요 : ");
    scanf("%s", p_no);
    //3
    EXEC SQL SELECT 제품명, 단가 INTO :p_name, :price
    FROM 제품
    WHERE 제품번호 = :p_no;
    //4
    printf("\n 제품명 =%s", p_name);
    printf("\n 단가 = %d", price);
    
    return 0;
}

1. 삽입 SQL문에서 사용할 변수 미리 선언
2. 검색하고자 하는 제품의 제품번호를 사용자로부터 입력받는 부분
3. 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품명과 단가를 검색하여 대응되는 각각의 변수에 저장하는 삽입 SQL 문 
4. 검색된 제품명과 단가를 화면에 출력

[커서가 필요한 삽입 SQL] 

SELECT 문의 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한 행식 차례로 접근할 수 있게해 주는 커서가 필요하다.

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

-- 제품 테이블에서 제품명과 단가를 모두 검색하는 SELECT문을 위한 커서를 product_cursor 이름선언 --
EXEC SQL DECLARE product_cursor CURSOR FOR
		 SELECT 제품명, 단가 FROM 제품;

• 프로그램 안에서 커서를 여러개 사용할 수 있으므로 커서는 이름으로 구분한다.

---

EXEC SQL OPEN 커서_이름;

-- product_cursor 라는 이름의 커서에 연결된 SELECT 문을 실행함
EXEC SQL OPEN product_curosr;

• 커서를 선언했따고 SELECT문이 실행되는것은 아니다. 커서를 선언한 후 SELECT 문을 실행하는 명령이 별도로 필요하다.

---

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

-- product_curosr 커서를 이동해 결과 테이블의 다음 행에 접근하여 제품명 속성의 값을 p_name 변수에 저장하고 단가 속성의 값을 price 변수에 저장함 --
EXEC SQL FETCH product_curosr INTO :p_name, :price;

• 결과 테이블에는 여러 행이 존재하므로 FETCH 문을 반복해서 여러분 수행해야 한다.

---

EXEC SQL CLOSE 커서_이름;

-- product_cursor 커서를 더는 사용하지 않음 --
EXEC SQL CLOSE product_cursor;

• 커서를 더 사용하지 않을 때는 CLOSE 명령어를 사용한다.

 

 

 

 

출처 : 데이터베이스 개론  3판, 김연희 저자 한빛아카데미

데이터베이스 개론 | 김연희 - 교보문고