3. 관계 데이터베이스 언어(SQL)

❏ SQL 정의어 (DDL)

 - 정의, 수정, 제거. CREATE , ALTER, DROP 

 

 

 

❏ CREATE

 - 테이블, 스키마, 도메인, 인덱스, 뷰 등을 정의, 생성

 

 1. 테이블 정의

  - 학번, 성명, 학과, 학년 학점으로 구성된 학생 테이블을 만들어라

  - 학번과 학년은 숫자형 자료이며, 나머지는 문자형이다

  - 학번을 기본키로 지정한다

  - 성명 속성은 공백이 있을 수 없다

  - 학과 항목을 이용하여 수강 테이블의 학과를 참조하도록 외래키를 지정하며

    참조 테이블에서 삭제가 일어나면 NULL로 채우고, 수정이 발생하면 연쇄적으로 수정하도록 한다

  - 학년의 속성값은 4 이하의 값을 갖도록 'hak' 이름으로 제약한다

 

CREATE TABLE 학생(

 학번 INT,

 성명 CHAR(5) NOT NULL,

 학과 CHAR(10),

 학년 INT,

 학점 CHAR(1)

 PRIMARY KEY(학번),

 FOREIGN KEY(학과) REFERENCES 수강(학과)

  ON DELETE SET NULL

  ON UPDATE CASCADE,

 CONSTRAINT hak CHECK(학년<=4)

);

 

 

 

 2. 스키마 정의

  - 시스템 관리자가 일반 사용자에게 스키마에 대한 권한을 주기 위한 명령문

  - CREATE SCHEMA 스키마이름 AUTHORIATION 사용자;

 

 

 

 3. 도메인 정의

  - 한 속성값의 범위를 지정하기 위한 도메인은 CREATE DOMAIN 문에 의해 생성된다

  - 이렇게 안해도 테이블 정의할 때 CHECK 옵션 쓰는거랑 같은 뜻. 

 

속성의 값으로 T 와 F 로만 구성되는 success 라는 이름의 도메인을 정의한다.

속성값이 입력되지 않을 경우 기본값은 T 로 한다.

 

CREATE DOMAIN success CHAR(1)

 DEFAULT 'T'

 CONSTRAINT v-success CHECK(VALUE IN('T', 'F'));

 

 

 

 4. 인덱스 정의

  - DB 내의 자료를 효율적으로 검색하기 위해 인덱스를 만들고 사용한다

  

학생 테이블의 학과 속성값을 오름차순 정렬하여

중복을 허용하지 않도록 'stud_idx'라는 이름의 인덱스를 정의하시오

 

CREATE UNIQE INDEX stud_idx

 ON 학생(학과 ASC);

 

 

 

❏ ALTER

 - 기존에 만들어진 테이블에 새로운 속성을 추가하거나 변경, 삭제 할때 사용하는 명령어

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

 - ALTER TABLE 테이블이름 ALTER/DROP 속성이름;

 

 

 

 

❏ DROP

 - 기존에 사용되던 테이블, 스키마, 도메인, 인덱스, 뷰, 제약조건 등을 제거할 때 사용한다. 삭제 시 전체삭제 가 된다

 - DROP TABLE/SCHEMA/DOMAIN/VIEW/INDEX/CONSTRAINT 이름;

 

: RISTRICT 옵션 

 - 삭제할 요소가 사용중이면 삭제가 안됨

: CASCADE 옵션

 - 삭제할 요소가 사용중이여도 몰라 그냥 삭제시킬래

 

 

 

 

❏ SQL 조작어 (DML)

 - SELECT(검색), INSERT(삽입), UPDATE(갱신), DELETE(삭제)

 

 

 

 

❏ SELECT 

  SELECT 속성이름

  FROM 테이블이름

  WHERE 조건

  GROUP BY 속성이름 HAVING 그룹조건

  ORDER BY 속성이름

 

 

 

* BETWEEN A AND B

 - A 이상 B 이하

 

 

 

* DISTINCT

 - 중복 제거

 

 

 

* 함수 이용

 - SUM / AVG / MAX / MIN / COUNT

 

SELECT SUM(점수)

FROM 학생

WHERE 학년=1;

 

 

 

* IN 

 SELECT 학번, 성명

 FROM 학생

 WHERE 학년 IN(3,4);

 

IN : 3 이거나 4. OR 이랑 같은 뜻

 

 

 

* 검색된 결과를 정렬해서 표현할 때

 SELECT 성명

 FROM 학생

 WHERE 점수>=85

 ORDER BY 학번 ASC;

 

 테이블에서 점수가 85점 이상인 학생을 학번의 오름차순으로 성명을 검색

 

ASC : 오름차순

DESC :  내림차순

 

 

 

* 그룹 분류 질의문

SELECT 학년

FROM 학생

GROUP BY 학년

HAVING COUNT(*) >= 2;

 

학생 테이블에서 2명 이상인 학년을 검색

 

 

 

* 부속 질의문

 - 질의문 안에 질의문

 

학생정보 테이블과 학과인원 테이블을 이용하여 오승윤 학생이 속한 학과의 학생수를 검색하시오

 

SELECT 학생수

FROM 학과인원

WHERE 학과 =

 (SELECT 학과

  FROM 학생정보

  WHERE 이름 = '오승윤');

 

저기에 = 대신 IN, EXISTS 등을 사용할 수 있다.

EXISTS 는 부속 질의문의 검색 결과가 없으면 수행 안하고 있으면 수행한다.

 

 

 

* 부분 매치 질의문

 - 와일드카드 사용해서 헷갈리는 문자 찾을 때

 

학생 테이블에서 연락처의 번호가 7588로 끝나는 학생의 성명을 검색하시오

 

SELECT  성명

FROM  학생

WHERE 연락처 LIKE '%7588';

 

 

 

* NULL 값과 비교 - IS NULL / IS NOT NULL. SELECT 학번 FROM 학생WHERE 연락처 IS NULL;

 

 

* JOIN 질의문

 - 두 테이블을 JOIN 연산한 뒤 자료를 검색한다

 

학생정보 테이블과 학과인원 테이블 에서 학과명이 같은 행을 JOIN 하고

이름, 학과, 학생수를 출력해라

 

SELECT 이름, 학과, 학생수

FROM 학생정보 JOIN 학과인원 ON (학생정보.학과 = 학과인원.학과);

 

JOIN ~ USING 도 같은 말.

 

 

 

❏ UPDATE 

 - 기존 테이블에 새로운 튜플을 추가

 

INSERT INTO 학생(학번, 성명, 학년, 수강과목, 연락처)

VALUES (951115, '김정미', 4, '데이터베이스', '243-0707');

 

 

 

❏ UPDATE 

 - 테이블의 튜플 중에서 값을 변경하고자 할 때

 

UPDATE 학생

SET 점수=92

WHERE 성명='오준석';

 

학생 테이블에서 오준석 학생의 점수를 92점으로 수정하시오

 

 

 

❏ DELETE

 - 테이블의 튜플을 삭제할 때 사용

 

DELETE FROM 학생

WHERE 학년=2;

 

 

 

 

❏ SQL 제어어 (DCL)

 - COMMIT / ROLLBACK / GRANT / REVOKE

 

1. COMMIT

 - 다 제대로 연산 성공적으로 했으니까 적용시켜서 지속적으로 유지해라

 

2. ROLLBACK

 - 연산이 비정상으로 종료되거나 이전상태로 돌리기 위해 연산 내용을 취소할 때

 

3. GRANT

 - 관리자가 사용자에게 데이터베이스에 대한 권한을 부여할 때

   GRANT 권한기능 ON 테이블이름 TO 사용자 [WITH GRANT OPTION];

 

4. REVOKE

 - 관리자가 사용자에게 데이터베이스에 대한 권한을 준 것을 취소할 때

   REVOKE 권한기능 ON 테이블이름 FROM 사용자 [CASCADE];

 

 

 

 

❏ 뷰(VIEW)

 - 가상 테이블. 실제 물리적으로 이루어진 테이블은 아니다. 접근제어로 보안성을 향상시킨다

  

1. 뷰의 생성

 - 학생 테이블에서 3학년 학생의 학번과 성명, 연락처 속성을 이용하여 

   학번, 이름, 전화번호 속성으로 구성된 3학년연락처 뷰를 생성하세요

 

CREATE VIEW 3학년연락처(학번, 이름, 전화번호)

AS SELECT 학번, 성명, 연락처

FROM 학생

WHERE 학년=3;

 

2. 뷰의 삭제

 

DROP VIEW 3학년연락처 CASCADE

 

 

 

 

❏ 뷰(VIEW) 의 특징

 - 뷰가 정의된 기본 테이블이 제거되면, 뷰도 자동적으로 제거된다!

 - 뷰에 대한 검색(SELECT) 은 일반 테이블과 하는것처럼 똑같이 하면 된다.

 - 뷰에 대한 삽입, 삭제, 갱신은 제약이 따른다. 뷰의 속성에 기본테이블의 기본키가 포함되어있지 않으면 삽입, 삭제, 갱신이 안된다.

 - 보안 측면에서 쓰면 좋다

 - 뷰는 ALTER 문을 이용하여 수정이 안된다

 - 한번 정의된 뷰는 변경할 수 없으며, 삭제한 후 다시 써야한다

 

 

 

 

❏ 시스템 카탈로그

 - 데이터베이스에 저장되어 있는 테이블, 인덱스, 뷰, 제약조건, 등... 

   개체들에 대한 정보와 정보와 정보들간의 관계를 저장한 것.

   어떻게 보면 하나의 작은 데이터베이스 라고 볼 수도 있다.

 

 - 시스템 카탈로그는 데이터 사전(Data Dictionary) 라고도 불린다.

 - 시스템 카탈로그에 저장된 데이터를 메타 데이터(Meta Data)라고 한다.

 - 시스템 카탈로그는 일반 테이블과 똑같이 시스템 테이블로 구성된다.

 - 일반 사용자도 시스템 카탈로그의 내용을 검색을 할수는 있는데, 삽입 삭제 수정 은 안된다.

 - 시스템 카탈로그의 갱신은 사용자가 SQL 문을 하나 실행할때마다 자동적으로 이루어진다