5. SQL의 기본 (1)

1. CRUD를 위한 SQL 구문

(1) CRUD(Create, Read, Update, Delete) 

• CRUD의 개념 
  • 데이터베이스에서 데이터를 관리하는 4가지의 기본 연산인 Create, Read, Update, Delete의 약어 
    • Create: 데이터 생성, 새로운 데이터를 추가할 때 사용 
      • INSERT INTO users (name, email) VALUES (‘ruby’, ‘ruby@dev.com’)
    • Read: 데이터 읽기, 저장된 데이터를 조회/검색할 때 사용
      • SELECT * FROM users WHERE name = ‘ruby’
    • Update: 데이터 수정, 저장되어 있는 데이터를 수정할 때 사용
      • UPDATE users SET email =‘ruby00@dev.com’ WHERE name = ‘ruby’
    • Delete: 데이터 삭제, 저장되어 있는 데이터를 삭제할 때 사용
      • DELETE FROM users where name = ‘ruby’ 
• CRUD Matrix 
  • 데이터베이스에서 데이터에 대해 수행할 수 있는 CRUD 작업을 시각적으로 표현한 도구
  • 사용자 역할이나 프로세스가 특정 엔터티에 대하여 어떤 CRUD 작업을 수행할 수 있는지 표 형식으로 표현
  • 행(Row): 시스템에서 수행되는 프로세스 혹은 기능
  • 열(Column): 시스템에서 다루는 엔티티 
  • 값(Value):CRUD 연산

 

 

 

 

(2) SQL과 관계 대수

• SQL(Structured Query Language)의 개념 
  • 관계 대수에 기초하여 RDBMS의 데이터를 관리하기 위한 프로그램 언어 
  • RDBMS에 저장된 데이터를 생성, 조작, 제어하기 위한 프로그램 언어 
• 관계 대수(Relational Algebra)와 관계 대수식(Relational Algebra Expression)
  • 관계 대수: 관계형 데이터베이스에서 데이터를 조회하고 조작하기 위한 수학적 언어 
    • ➔ SQL의 이론적 기초
• • 관계 대수식: 관계 대수의 연산을 수행하기 위한 일련의 수식 
  • 관계 연산자의 구분
    • 단항 연산자
    • 이항 연산자
    • 일반 집합 연산자
    • 순수 관계 연산자 

 

 

(3) SQL 문의 종류 

DDL	- Data Definition Language  - 데이터베이스의 논리적 구조를 정의하기 위한 언어, 데이터 사전에 저장  - 기본 Auto Commit
DML	- Data Manipulation Language  - 데이터베이스에 저장된 데이터를 조작하기 위해 사용하는 언어  - 검색, 추가, 삭제, 갱신 작업 수행
DCL / TCL	- Data Control Language  - Transaction Control Language  - 데이터에 대한 접근 권한 부여, 트랜잭션 등을 관리하기 위한 언어

 

 

 

2. DDL(Data Definition Language) 구문과 활용 

(1) DDL(Data Definition Language) 

• DDL의 개념과 특징 

DDL(Data Definition Language)이란? 
데이터베이스의 구조를 정의, 수정하는 SQL 언어
➔ 데이터 사전 (Data Dictionary, Data Schema)에 저장

DDL SQL명령어	설명
CREATE	데이터베이스, 테이블, 인덱스, 뷰 등을 생성하는 명령어
DROP	테이블과 같은 데이터베이스 내 객체를 삭제하는 명령어
ALTER	데이터베이스 내 객체의 정의 혹은 속성을 변경하는 명령어

 

• CREATE TABLE - 테이블 생성 
  • 명령어 형식: CREATE TABLE 테이블명 (칼럼명 데이터 타입 제약 조건…) 

1. 테이블 명, 컬럼명 규칙	알파벳, 숫자, _ (언더바), $ (달러), # (샵) 대소문자 구분하지 않음
2. 데이터 타입	CHAR(L)	고정길이 문자열, 할당된 값의 길이나 L 보다 작을 경우 공백으로 채워짐
	VARCHAR2(L), VARCHAR(L)	가변길이 문자열, 최대 L의 길이의 값을 저장  ※ VARCHAR2는 ORACLE이 지원하는 형식 (권장사항)
	NUMBER(L,D)	숫자형, L (전체 자리 수), D (소수점 자리 수) ※ MariaDB는 DECIMAL(L,D) 형식 및 FLOAT, INT 등 명확한 타입 지정
	DATE : 날짜형 (시간까지 저장)	※ ORACLE(TIMESTAMP), MariaDB(DATEIME, TIMESTAMP)로 더 정밀한 시간 지정
	BLOB	Binary Large Object
3. 제약 사항	PRIMARY KEY	기본키 정의(기본키 생성 시 DBMS가 자동으로 인덱스 생성, NULL 불가)
	FOREIGN KEY	다른 테이블의 기본키를 외래키로 지정 (참조 무결성 제약조건) ※ MariaDB : InnoDB 형식일 경우 ON DELETE CASCADE 지원
	UNIQUE	고유한 속성 값 (NULL 가능)
	NOT NULL	NULL이 될 수 없음 ※ NULL : 아직 정의되지 않은 값 또는 현재 데이터를 입력하지 못하는 값을 의미
	CHECK	입력 값의 종류 및 범위 제한 확인
	DEFAULT	데이터 입력 시, 지정 값이 없을 경우 기본값 사용
	SEQUNCE, TRIGGER	자동 증가 ※ MariaDB : AUTO_INCREMENT 지원
CREATE TABLE의 예	CREATE TABLE employees (         employee_id NUMBER(6) NOT NULL,         first_name VARCHAR2(50),         last_name VARCHAR2(50) NOT NULL,         email VARCHAR2(100) NOT NULL,         job_id VARCHAR2(10) NOT NULL,         hire_date DATE DEFAULT SYSDATE NOT NULL,         CONSTRAINT pk_employees PRIMARY KEY (employee_id),         CONSTRAINT fk_employees_job FOREIGN KEY (job_id)                 REFERENCES jobs(job_id) ON DELETE SET NULL,         CONSTRAINT uk_employees_email UNIQUE (email) );

                      

 

• DROP TABLE - 테이블 제거 
  • 명령어 형식: DROP TABLE 테이블명 
    • 테이블의 데이터와 구조 모두를 삭제, 복구 불가 
      • Oracle: FLASHBACK을 이용하여 복구 가능 (단, PURGE 옵션으로 삭제 시 완전 삭제)
• TRUNCATE TABLE - 테이블의 데이터 제거 
  • 명령어 형식 : TRUNCATE TABLE 테이블명 
    • 테이블의 전체 데이터(만) 삭제, ROLLBACK 불가 (로그를 기록하지 않음)
      •  MariaDB: AUTO_INCREMENT 초기화됨
• ALTER TABLE - 테이블 수정
  • 명령어 형식
    • ALTER TABLE 테이블명 ADD (컬럼명 데이터타입) 
    • ALTER TABLE 테이블명 DROP COLUMN 컬럼명
    • ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 제약조건)
      • 칼럼명 변경 시 RENAME TABLE ~ 혹은 ALTER TALBLE ~ RENAME TO 사용
      • Oracle : 다중 컬럼 삭제 지원
  • MODIFIY 시 제약사항
    • 칼럼의 크기 축소 시 NULL 혹은 값이 없는 경우에만 가능
      • MariaDB는 컬럼 크기 감소 시 데이터 손실이 발생할 수 있음
    • 데이터 유형 변경 시 NULL만 있는 경우에 가능
    • NOT NULL 설정은 NULL이 없을 경우에 가능 
• RENAME (TABLE) - 테이블 이름 변경
  • 명령어 형식
    • RENAME 테이블명 TO 새로운 테이블명
      • MariaDB : RENAME TABLE 테이블명 TO 새로운 테이블명
    • ALTER TABLE 테이블명 RENAME TO 새로운 테이블명
• DESCRIBE / DESC - 테이블 구조 확인
  • 명령어 형식 : DESCRIBE 테이블명
    • SQL Server : sp_help dbo.테이블명

 

 

3. DCL (Data Control Language) 구문과 활용

(1) DCL(Data Control Language)

• DLC의 개념과 특징

DCL (Data Control Language)란?
데이터에 대한 접근 권한 부여 등 관리를 위한 SQL 언어

DCL SQL명령어	설명
GRANT	데이터베이스 사용자에게 특정 권한을 부여하는 명령어
REVOKE	데이터베이스 사용자의 특정 권한을 제거하는 명령어

 

• CREATE / ALTER / DROP USER 
  • 사용자 생성: CREATE USER 사용자명 IDENTIFIED BY 패스워드
    • MariaDB: CREATE USER '사용자명'@'호스트명' IDENTIFIED BY ‘패스워드’ 
  • 사용자 변경: ALTER USER 사용자명 IDENTIFIED BY 패스워드
    • MariaDB: ALTER USER '사용자명'@'호스트명' IDENTIFIED BY ‘패스워드’
  • 사용자 삭제: DROP USER 사용자명
    • Oracle: CASCADE 옵션 사용 가능 (사용자의 모든 객체도 함께 삭제) 
    • MariaDB: '사용자명'@'호스트명' 형식 사용 (% : 전체)
• GRANT - 권한 부여 
  • 명령어 형식 : GRANT 권한 ON 대상 TO 사용자

• REVOKE - 권한 회수(제거) 
  • 명령어 형식 : REVOKE 권한 ON 대상 FROM 사용자 
    • GRANT로 부여된 권한 회수
      • Oracle: CASCADE CONSTRAINTS 옵션으로 관련된 참조 무결성 제약조건도 함께 삭제 
      • WITH GRANT OPTION (해당 사용자가 다른 사용자에게 부여한 권한도 함께 회수)