4-3강. SQL 활용-기본 SQL 작성(3)

과목4. 데이터베이스 구축, 3강. SQL 활용-기본 SQL작성(3)

 

[ 목차 ]

1. 데이터 정의어 DDL

2. DDL 조작 방법

3. DDL문 특징

4. 테이블

5. 테이블의 특징

6. 테이블의 구조

7. 테이블의 용어

8. 테이블 생성 방법

9. 테이블 변경

10. 테이블 삭제, 절단, 이름 변경

11. 제약조건 유형

12. 데이터 사전 = 시스템 카탈로그 = 시스템 테이블 = 메타 데이터

13. 분산 데이터베이스와 목표

14. 분산 데이터베이스의 특징

15. 데이터 사전 내용

16. 데이터 사전 용도

17. 오라클에서 데이터 사전 검색

18. mySQL에서 데이터 사전 검색

 

1. 데이터 정의어 DDL

1) DB 객체를 정의하는 언어

2) DBMS가 관리하는 테이블과 뷰, 인덱스 등을 데이터베이스 객체라 지칭

3) 객체 유형

  1] 스키마 : DBMS 특성과 구현 환경을 감안한 데이터 구조로 직관적으로 하나의 데이터베이스로 이해 가능

  2] 도메인 : 속성의 데이터 타입, 크기, 제약 조건 등을 지정한 정보로 속성이 가질 수 있는 값의 범위로 이해 가능

  3] 테이블 : 데이터의 저장 공간

  4] 뷰 : 하나 이상의 물리 테이블에서 유도되는 가상의 논리 타이틀

  5] 인덱스 : 검색을 빠르게 하기 위한 데이터 구조

개체 : 관계형 데이터베이스에서는 테이블을 의미 / 학생, 교수 등

객체 : 데이터베이스를 구성하는 모든 것 / 스키마, 도메인, 테이블, 뷰, 인덱스 등

2. DDL 조작 방법

1) 객체를 생성, 변경, 제거하기 위한 명령어를 사용

2) 생성 : CREATE, DB 객체 생성

3) 변경 : ALTER, DB 객체 변경

4) 삭제 : DROP, 객체 삭제(구조와 데이터 모두) / TRUNCATE, 객체 내용 삭제(데이터만)

 

3. DDL문 특징

1) DB 스키마를 컴퓨터가 이해할 수 있도록 기술하는 데 사용

2) 주로 DB 관리자나 DB 설계자가 사용

3) DB의 메타데이터로 데이타사전에 저장

4) DB 스키마를 컴퓨터가 이해할 수 있도록 기술하는데 사용

5) 논리적 데이터 구조를 정의

6) 물리적 데이터 구조를 정의

7) 명령 실행 후 기본적으로 Auto Commit(트랜잭션의 끝으로 간주) 진행

 

4. 테이블

1) 데이터를 저장하는 객체로서 관계형 db의 기본 단위

2) 행과 열의 2차원 표 형태로 동일한 형식을 갖는 개체들을 저장

 

5. 테이블의 특징

1) 특정한 주제와 목적으로 제작

2) 반드시 하나 이상의 칼럼을 보유해야 함

3) 테이블에는 등록된 자료들이 있으며 이 자료들은 삭제하지 않는 한 지속해서 유지

 

6. 테이블의 구조

1) 칼럼과 행의 2차원 구조

2) 세로 방향을 칼럼, 가로 방향을 행이라 지칭

3) 칼럼과 행이 겹쳐지는 하나의 공간을 필드라 지칭

7. 테이블의 용어

1) 테이블 : 행과 칼럼의 2차원 구조를 가진 데이터 저장 장소

2) 칼럼/열 : 2차원 구조를 가진 테이블에서 세로 방향으로 이루어진 하나하나의 특정 속성

3) 행 : 2차원 구조를 가진 테이블에서 가로 방향으로 이루어진 연결된 데이터

 

8. 테이블 생성 방법

1) 신규 생성 : CREATE TABLE 테이블명

                       (속성명1 데이터타입 NULL,

                        속성명2   '' ,

                        속성명3   '' , 

                        UNIQUE(속성명)            <- 대체키                                     

                        PRIMARY KEY(속성명)

                        FOREIGN KEY(속성명)

                               REFERENCES 참조테이블(기본키, 속성명)

                        CONSTRAINT 제약조건명

                                CHECK(조건식) );

2) 다른 테이블의 정보를 이용한 생성 : CREATE TABLE 테이블명 AS SELECT문

 

9. 테이블 변경

1) ALTER를 이용하여 테이블 구조를 변경하는 문법

2) 열 추가 : ALTER TABLE 테이블이름 ADD 열이름 데이터타입;

3) 열 데이터 타입 변경 : ALTER TABLE 테이블이름 MODIFY 열이름 데이터타입;

4) 기본값 변경 : ALTER TABLE 테이블이름 ALTER 열이름 SETDEFAULT'변경값 ';

5) 열 삭제 : ALTER TABLE 테이블이름 DROP 열이름;

 

10. 테이블 삭제, 절단, 이름 변경

1) 테이블 삭제 : DROP TABLE 테이블 이름

2) 테이블 내용 삭제 : TRUNCATE TABLE 테이블 이름

3) 테이블 이름 변경 : RENAME TABLE 이전테이블이름 TO 새로운테이블이름

                             ALTER TABLE 이전테이블이름 RENAME 새로운테이블이름

 

11. 제약조건 유형

1) 테이블 생성 시 칼럼 또는 테이블에 대한 제약조건을 설정할 수 있음

2) 테이블 생성을 위한 CREATE 문에 제약조건을 명시하는 형태로 사용

3) ALTER를 통해 테이블의 제약조건 변경 가능

PRIMARY KEY
1) 테이블의 기본키를 정의
2) 기본으로 NOT NULL, UNIQUE 제약이 포함

FOREIGN KEY
1) 외래키를 정의
2) 참조 대상을 테이블이름(열이름)으로 명시해야 함
3) 참조 무결성 위배 상황 발생 시 처리 방법으로 옵션 지정 가능

UNIQUE 
1) 테이블 내에서 얻은 유일한 값을 가져야 함
2) 테이블 내에서 같은 값을 가져서는 안 되는 항목 지정

NOT NULL
1) 테이블 내에서 관련 열의 값은 NULL일 수 없음
2) 필수 입력 항목에 대해 제약조건으로 설정

CHECK
1) 개발자가 정의하는 제약조건
2) 상황에 따라 다양한 조건 설정 가능

FOREIGN KEY의 REFERENCES 옵션 네 가지
ON DELETE[UPDATE] CASCADE 
1) 부모 테이블이 삭제/수정되면 관련된 자식 테이블도 연쇄 삭제/수정

ON DELETE[UPDATE] SET NULL
1) 부모 테이블이 삭제/수정되면 관련된 자식 테이블 값을 NULL로 설정

ON DELETE[UPDATE] SET DEFAULT
1) 부모 테이블이 삭제/수정되면 관련된 자식 테이블 값을 DEFAULT로 설정

NO ACTION
1) 참조할 경우 실행 안됨

12. 데이터 사전 = 시스템 카탈로그 = 시스템 테이블 = 메타 데이터

1) 데이터베이스의 객체와 구조들에 관한 모든 데이터를 포함하는 시스템 데이터베이스

2) 데이터사전에 저장된 정보를 메타데이터라 지칭

3) 데이터사전은 사용자 및 질의 최적화 모듈 등 DBMS 자신의 구성요소에 의해서 사용

4) 데이터사전은 관계 DBMS마다 표준화되어 있지 않아서 관계 DBMS마다 서로 다른 형태로 시스템 카탈로그 기능 제공

5) 사용자가 SQL문으로 내용 검색이 가능

6) 데이터베이스 관리 시스템에 의해 생성 및 유지

7) 기본 테이블, 뷰, 인덱스 등이 변경되면 자동으로 데이터사전은 갱신(사용자가 SQL문으로 갱신 불가능)

8) 위치 투명성 및 중복 투명성을 제공하는데 필요한 모든 제어 정보 포함

 

13. 분산 데이터베이스와 목표

1) 정의 : 컴퓨터 네크워크상에 물리적으로 분산된 데이터베이스를 논리적으로는 1개로 인식하는 기법

2) 위치 투명성 : 사용자가 물리적으로 저장되어 있는 곳을 알 필요 없이 논리적인 입장에서 데이터가 모두 자신의 사이트에 있는 것 처럼 처리

3) 중복(복제) 투명성 : 트랜잭션이 데이터의 중복 개수나 중복 사실을 모르고도 데이터 처리가 가능

4) 병행 투명성 : 분산 데이터베이스와 관련된 다수의 트랜잭션들이 동시에 실현 되더라도 그 트랜잭션의 결과는 영향X

5) 장애 투명성 : 트랜잭션, DBMS, 네트워크, 컴퓨터 장애에도 불구하고 트랜잭션을 정확하게 처리

 

14. 분산 데이터베이스의 특징

1) 자료 공유 용이

2) 시스템 성능 향상

3) 점증적 시스템 용량 확장 용이

4) 설계가 어렵고 소프트웨어 개발 비용 증가

5) 오류 발생 가능성 높음

 

15. 데이터 사전 내용

1) 릴레이션의 정보

  1] 릴레이션에 대해 정의된 무결성 제약조건

2) 인덱스 관련 정보

3) 뷰 관련 정보

4) 사용자 관련 정보

  1] 계정

  2] 권한

5) 통계 관련 정보

 

16. 데이터 사전 용도

1) 사용자에게는 단순 조회의 대상

2) 데이터베이스 엔진을 이루는 컴파일러, 옵티마이저 등과 같은 구성요소에 데이터 사전은 작업을 수행하는 데 필요한 참조 정보일 뿐만 아니라 작업의 대상이기도 함

옵티마이저
1) 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할

17. 오라클에서 데이터 사전 검색

1) 뷰로 데이터 사전에 접근

2) 오브젝트(테이블, 뷰, 인덱스)에 접근할 수 있는 사용자 권한에 따라 영역이 구분

3) 영역 지시자 뒤에 오브젝트 명을 붙이는 형태로 뷰의 이름이 결정

4) DBA_ : 데이터베이스의 모든 객체 조회 가능

5) ALL_ : 자신의 계정으로 접근 가능한 객체와 타 계정의 접근 권한을 가진 모든 객체 조회 가능

6) USER_ : 자신의 계정이 소유한 객체 조회 가능

 

18. mySQL에서 데이터 사전 검색

1) 테이블 형태로 구성

2) Information schema라는 데이터베이스 안에 존재

3) 테이블을 조회하기 위해서는 해당 데이터베이스로 이동해서 테이블 목록을 요청해야 함

4) use Information schema;

   show tables;

5) 데이터 사전을 구성하는 테이블 이름을 확인하고 select문을 통해 해당 테이블의 내용을 조회