[SQLD] SQL기본&활용 : 관계형 데이터베이스 개요, DDL, DML, DCL, TCL

 

https://lotuus.tistory.com/42

[SQLD] 21.11.20 제43회 SQL개발자 후기, 공부방법, 정리본, 43회 출제문제

 

[목차]

💚관계형 데이터베이스 개요

     데이터베이스

     DBMS

     SQL

     TABLE 테이블

💚데이터 타입 및 제약조건

💚DDL

💚DML

💚DCL

     ROLE : 권한들의 집합

     Oracle 기본제공 ROLE

💚TCL

    트랜잭션의 특성 ACID

    트랜잭션 이전/이후 데이터 상태

    Oracle과 SQL Server의 DDL, DML 트랜잭션 처리방식

---

💚관계형 데이터베이스 개요

데이터베이스

특정 기업, 조직이 필요로하는 데이터를 일정한 형태로 저장해 놓은 것

 

DBMS

데이터 많아지고 공유되면서 단순 소프트웨어만(ex. 엑셀)으로 관리하는데에 한계 발생.

효율적인 데이터 관리를 위한 전용 소프트웨어이다.

 

현재 대부분의 데이터베이스는 관계형 데이터베이스이다.

 

SQL

관계형 데이터베이스에서 데이터를 정의, 조작, 제어할 수 있는 언어

데이터 정의 DDL	데이터 구조(테이블)를 정의한다.	create, alter, drop, rename, truncate
데이터 조작 DML	데이터 자체를 다룬다.	select, insert, update, delete
데이터 제어 DCL	데이터의 접근권한을 다룬다.	grant, revoke
트랜젝션 제어어 TCL	트랜잭션을 제어한다.	commit, rollback

 

TABLE 테이블 (데이터 구조)

데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본단위이다.

테이블은 특정 주제와 목적으로 만들어지는 집합(표)이다.

테이블은 반드시 하나 이상의 칼럼(속성)을 가져야 한다.

가로 = 행 = ROW(로우) = 속성
세로 = 열 = Column(칼럼) = 데이터 1줄
식별자	기본키 Primary key	각 행을 특정할 수 있는 칼럼(속성)
	외부키 Foreign key	다른 테이블의 기본키와 내 테이블을 연결하는 칼럼(속성)

 

 

💚 데이터 타입 및 제약조건

데이터타입 DATA TYPE

데이터는 특정 칼럼(속성)이 받아들일 수 있는 자료의 유형을 저장해야한다.

	Oracle	SQL Server
고정 길이 문자열	CHAR(길이)
가변 길이 문자열	VARCHAR2(길이)	VARCHAR(길이)
정수, 실수 등의 숫자	NUMBER	int, float
날짜, 시간	DATE	DATETIME

CHAR은 공백을 체크하지 않는다. => 'AA' = 'AA '

VARCHAR2는 공백을 체크한다. => 'AA' ≠ 'AA '

 

제약조건 CONSTRAINT

특정 칼럼에 사용자가 원하는 조건의 데이터만 저장하기 위한 제약이다.

PRIMARY KEY (기본키)	UNIQUE + NOT NULL
FOREIGN KEY (외래키)	테이블 간의 관계를 표현한다.
UNIQUE	중복된 값이 저장되면 안된다. + NULL 가능
CHECK(조건식)	입력할 수 있는 값의 범위 등을 제한한다.
DEFAULT 값	값이 입력되지 않은 경우, 기본값을 사전에 설정한다.
NOT NULL	NULL 금지

CREATE TABLE 테이블명(
	칼럼명 데이터유형 PRIMARY KEY,                       -- PRIMARY KEY★
	칼럼명 데이터유형 REFERENCES 다른테이블명(칼럼명),     -- FOREIGN KEY◆
	칼럼명 데이터유형 UNIQUE,                            -- UNIQUE
	칼럼명 데이터유형 CHECK(칼럼명>=100 AND 칼럼명<300),  -- CHECK
	칼럼명 데이터유형 DEFAULT 'Y',                       -- DEFAULT
	칼럼명 데이터유형 NOT NULL,                          -- NOT NULL
    
	칼럼명 데이터유형 CHECK(칼럼명>200) PRIMARY KEY,      -- 제약조건 혼합가능    
    
	[CONSTRAINT 제약조건명] PRIMARY KEY(칼럼명, 칼럼명..),                       -- PRIMARY KEY★
	[CONSTRAINT 제약조건명] FOREIGN KEY(칼럼명) REFERENCES 다른테이블명(칼럼명)   -- FOREIGN KEY◆
);

 

생성된 테이블 구조 확인

Oracle	DESCRIBE 테이블명 or DESC 테이블명
SQL Server	exec sp_help 'dbo.테이블명'

 

반응형

 

💚DDL Data Definition Language

데이터베이스의 기본단위인 테이블의 구조를 생성, 변경, 삭제할 수 있는 명령어이다.

🔥 SQL Server에서는 여러 컬럼을 동시에 수정하는 구문은 지원하지 않는다.

 

다음 중 아래설명과 같은 테이블 및 PK제약조건을 생성하는 DDL문장으로 올바른 것은? (ORACLE 기준)
PRODUCT테이블
PROD_ID : VARCHAR2(10) NOT NULL
PROD_NM : VARCHAR2(100) NOT NULL
REG_DT : DATE NOT NULL
REGR_NO : NUMBER(10) NULL

1) CREATE TABLE PRODUCT (    PROD_ID VARCHAR2(10) NOT NULL,    PROD_NM VARCHAR2(100) NOT NULL,    REG_DT DATE NOT NULL,    REGR_NO NUMBER(10) NULL ); ALTER TABLE PRODUCT ADD PRIMARY KEY PRODUCT_PK ON(PROD_ID);	2) CREATE TABLE PRODUCT(    PROD_ID VARCHAR2(10),    PROD_NM VARCHAR2(100),    REG_DT DATE,    REGR_NO NUMBER(10) ); ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY(PROD_ID);
3) CREATE TABLE PRODUCT(    PROD_ID VARCHAR2(10) NOT NULL,    PROD_NM VARCHAR2(100) NOT NULL,    REG_DT DATE NOT NULL,    REGR_NO NUMBER(10) NULL,    ADD CONSTRAINT PRIMARY KEY(PROD_ID) );	4) CREATE TABLE PRODUCT(    PROD_ID VARCHAR2(10) NOT NULL,    PROD_NM VARCHAR2(100) NOT NULL,    REG_DT DATE NOT NULL,    REGR_NO NUMBER(10), );

=> 정답은 4번

 

[기관분류] 테이블

분류ID : VARCHAR(10) PRIMARY KEY

분류명 : VARCHAR(10) NOT NULL -> VARCHAR(30) NOT NULL

등록일자 : VARCHAR(10) NULL -> DATE NOT NULL

위와 같이 테이블 구조를 변경하고자 할 때, 올바른 SQL문장은?(SQL Server기준)

 

1) ALTER TABLE 기관분류 ALTER COLUMN(분류명 VARCHAR(30), 등록일자 DATE NOT NULL);

2) ALTER TABLE 기관분류 ALTER COLUMN(분류명 VARCHAR(30) NOT NULL, 등록일자 DATE NOT NULL);

3) ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30); ALTER TABLE 기관분류 ALTER COLUMN 등록일자 DATE NOT NULL;

4) ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL; ALTER TABLE 기관분류 ALTER COLUMN 등록일자 DATE NOT NULL;

=> 정답은 4번

 

 

 

💚DML Data Manipulation Language

테이블 구조에 맞게 데이터를 조회, 삽입, 수정, 삭제할 수 있는 명령어이다.

** INSERT INTO 테스트테이블 VALUES("");

오라클은 ""을 NULL로 처리하지만 SQL Server는 "" 그대로 처리한다.

 

💚DCL Data Control Language

데이터베이스에 접근할 수 있는 유저를 생성하고 권한을 제어하는 명령어이다.

 

ROLE : 권한들의 집합

유저를 생성하면 기본적으로 SESSION, TABLE, PROCEDURE 등의 많은 권한을 부여해야한다.

유저와 권한 사이에 ROLE을 생성하고, 이 ROLE을 부여함으로써 많은 권한을 빠르고 정확하게 부여할 수 있게된다.

 

Oracle 기본제공 ROLE

CONNECT	로그인 권한 (EX. CREATE SESSION...)
RESOURCE	오브젝트 생성 권한 (EX. CREATE TABLE...)

 

💚 TCL Transaction Control Language

트랜잭션은 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다. 

분할할 수 없는 최소의 단위이기때문에 전부 적용되거나 전부 취소되어야 한다.

 

트랜잭션의 특성 ACID

원자성 Atomicity	트랜잭션은 분리되지 않고 전부 적용되거나 전부 취소되어야 한다.
일관성 Consistency	트랜잭션 실행 전에 오류가 없었다면 실행 후에도 오류가 없어야한다.
고립성 Isolation	트랜잭션 실행 도중에 다른 트랜잭션의 영향을 받지않는다.  * 잠금(LOCKING) : 트랜잭션 실행 도중 데이터에 다른 트랜잭션이 접근하지 못하도록 제한하는 것
지속성 Durability	트랜잭션에 성공한 내용은 DB에 영구적으로 저장된다.

 

트랜잭션 이전/이후 데이터 상태

이전	이후
- 메모리 버퍼에만 영향을 받았기때문에 이전상태로 복구 가능 - 현재 사용자는 select문장으로 결과 확인 가능 - 다른 사용자는 기존의 결과(변경이 반영되지 않은) 확인 가능 - 변경된 행은 잠금(LOCKING)이 설정되어 다른사용자가 접근 불가	- DB에 변경사항이 반영된다. - 변경 전의 데이터는 잃어버리게된다. - 모든 사용자가 변경이 반영된 결과를 확인할 수 있다. - 변경된 행의 잠금(LOCKING)이 풀리고 다른사용자가 접근할 수 있다.

 

Oracle 과 SQL Server의 DDL, DML 트랜잭션 처리방식

	Oracle	SQL Server
DDL (create, alter, rename, drop, truncate)	직접 데이터베이스에 영향을 미치기때문에 즉시 완료된다.(AUTO COMMIT) Auto Commit = false로 설정 불가하다.	직접 데이터베이스에 영향을 미치기때문에 즉시 완료된다.(AUTO COMMIT) Auto Commit = false로 설정 가능하다.
DML (insert, update, delete)	메모리 버퍼에 올려놓고 작업하기때문에 사용자가 COMMIT, ROLLBACK 명령어를 직접 입력해야만 반영된다.	DML도 즉시 완료된다. (AUTO COMMIT) 오류가 발생하면 자동 ROLLBACK 처리된다. Auto Commit = false로 설정 가능하다.

 

AUTO COMMIT	암시적 트랜잭션	명시적 트랜잭션
SQL Server의 기본방식 DDL, DML 수행 시 DBMS가 자동으로 COMMIT하고, 오류 발생 시 자동으로 ROLLBACK 수행한다.	Oracle의 기본방식 DDL은 AUTO COMMIT이지만 DML은 사용자가 COMMIT, ROLLBACK 명령어를 입력해야한다.	트랜잭션의 시작과 끝을 모두 명시한다. BEGIN TRANSACTION    명령문; COMMIT or ROLLBACK;