7. SQL 기본2 (SQLD 자격증 공부)

SQL(Structured Query Language) 종류

 1. DDL(Data Definition Language)

  ▷ 테이블 생성

• Create Table : 새로운 테이블을 생성, 생성시 기본키, 외래키, 제약사항 등을 설정할 수 있다.
• Alter Table : 테이블을 변경, 칼럼을 추가 및 변경,삭제 가능, 기본키 설정 또는 외래키 설정을 한다.
• Drop Table : 테이블을 삭제, 데이터 구조뿐만 아니라 저장된 데이터 모두 삭제

① 기본 테이블 생성 방법

CREATE TABLE EMP			-- EMP 테이블을 생성한다는 의미
(
    EMPNO NUMBER(10) PRIMARY KEY,	-- 기본키 지정
    ENAME VARCHAR2(20),			-- 칼럼 정보 및 데이터 타입 입력
    SAL NUMBER(6),			-- 칼럼 이름은 영문, 한글, 숫자 모두 가능
    DEPTNO NUMBER(4)			-- NUMBER은 숫자형 타입, VARCHAR2는 가변 길이 문자열로 지정
)

② 제약조건 사용

• 기본키, 외래키, 기본값, not null 등 테이블 생성시 지정가능
• constraint를 활용하여 기본키와 기본키의 이름을 지정할 수 있다.
• ex) 두개의 기본키 지정시 : constraint emp_pk primary key(empno, ename)
• 외래키 지정시 : constraint emp_fk foreign key(deptno) refrences dept(deptno) on delete cascade
• on delete cascade : 자신이 참조하고 있는 테이블(dept)의 데이터가 삭제될 경우 자신(emp)도 삭제하게 한다. 또한 참조의 무결성을 준수할 수 있다.

  ▷ 테이블 수정

1. 테이블명 변경 : ALTER TABLE ~ RENAME TO ~
2. 칼럼 추가 : ALTER TABLE ~ ADD (칼럼)
3. 칼럼 변경 : ALTER TABLE ~ MODIFY (칼럼)
4. 칼럼 삭제 : ALTER TABLE ~ DROP COLUMN 칼럼명
5. 칼럼명 변경 ALTER TABLE ~ RENAME COLUMN 변경전 칼럼명 TO 변경할 칼럼명

  ▷ 테이블 삭제

• DROP TABLE을 사용 하여 삭제를 한다
• 테이블 구조 및 데이터를 모두 삭제
• DROP TABLE EMP CASCADE CONSTRAINT; 를 사용하면 참조된 제약사항까지 모두 삭제

  ▷ 뷰 생성과 삭제

• 뷰는 테이블로부터 유도된 가상 테이블이다.
• 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 한다.
• CREATE VIEW를 사용하여 생성하며 참조할 테이블은 SELECT문으로 지정
• EX:) CREATE VIEW V_EMP AS SELECT EMPNP, ENAME FROM EMP;
• 뷰 조회시 SELECT문을 활용하여 일반 테이블 처럼 조회
• EX:) SELECT * FROM V_EMP;
• 뷰 삭제 또한 DROP VIEW를 활용하며, 뷰를 삭제 해도 참조했던 테이블은 삭제되지 않는다.

 ※ 뷰의 특징

• 참조한 테이블이 변경시 뷰도 변경된다.
• 입력, 수정, 삭제에는 제약이 있다.
• 보안성을 향상

 

≫ 뷰의 장단점

장점	단점
- 특정 칼럼 조회로 보안 기능이 있다. - 데이터 관리가 간단 - SELECT문이 간단 - 하나의 테이블에 여러개의 뷰 생성 가능	- 독자적인 인덱스 생성 불가 - 삽입, 수정, 삭제 연산이 제약 - 데이터 구조를 변경 불가

---

뷰에 대한 설명으로 올바르지 않은 것은? (답 : 3)

1. 개발자의 SELECT문이 간소해진다.
2. 데이터 구조를 변경할 수는 없다.
3. 보안 기능은 제공되지 않는다.
4. 데이터 관리가 간단해진다.

---

2. DML(Data Manipulation Language)

 ▷ INSERT문

• INSERT문은 테이블에 데이터를 입력한다.

INSERT INTO EMP(EMPNO, ENAME) 
	VALUES(2000,'장비');

• 데이터를 저장할때는 TCL문인 COMMIT을 실행해 줘야한다.
• AUTO COMMIT이 ON으로 설정된 경우 COMMIT을 실행하지 않아도 된다.
• SELECT문을 사용하여 테이블에 바로 삽입할 수 있다.

INSERT INTO SELECT_EMP 
	SELECT * FROM EMP;​

 ▷ UPDATE문

• 값을 수정할 때 사용
• UPDATE문에 조건문을 입력하지 않으면 모든 데이터가 수정되므로 유의

UPDATE EMP			-- 수정할 테이블
    SET ENAME = '여포'		-- 수정할 내용
    WHERE EMPNO = 1000;		-- 조건 입력

 ▷ DELETE문

• 행을 삭제할 때 사용
• 조건문을 입력하지 않을 경우 모든 데이터가 삭제
• 데이터가 삭제되도 테이블의 용량이 초기화 되지 않는다

DELETE FROM EMP			-- 삭제할 테이블
	WHERE EMPNO = 1000;	-- 조건지정

• 조건을 입력하지 않으면 해당 테이블의 모든 데이터가 삭제된다.

 ▷ SELECT문

• 데이터를 조회하기 위해 사용

SELECT * 			-- 전체 행을 조회
FROM EMP			-- 조회할 테이블 지정
WHERE EMPNO = 1000;		-- 조건 지정

 

• ' * ' 대신 칼럼명을 작성해도 된다
• ORDER BY를 활용하여 정렬을 할 수 있는데 ASC(오름차순) DESC(내림차순)으로 사용 가능하며 기본은 ASC로 되어있다.
• 정렬을 사용할 경우 메모리를 많이 사용하기에 대량의 데이터 정렬시 성능 저하가 발생할 수 있다.

SELECT *
FROM EMP
ORDER BY EMPNO DESC		-- 정렬하기

• 정렬은 데이터베이스에 부하를 주기에 인덱스를 사용을 하여 ORDER BY를 피할 수 있다.

SELECT /*+ INDEX_DESC(A) */		-- 힌트를 사용
FROM EMP A;

 ▷ 중복제거와 별칭

1. DISTINCT : 중복된 데이터를 한번만 조회

SELECT DISTINCT DEPTNO		-- DEPTNO의 중복을 제거해준다.
FROM EMP

 

2. ALIAS : 칼럼 또는 테이블 명을 간략하게 할 때 사용 (별칭)

SELECT EMPNO AS '사원번호'
    ENAME AS '사원명'		-- AS ' ' 가 별칭을 사용한 내용이다.
    SAL AS '급여'
    DEPTNO AS '부서번호'
FROM EMP E			-- 테이블 별칭 사용
WHERE E.ENAME = '장비'

---

EMP 테이블에서 DEPTNO 값이 10,  20, 10, 30, 20, 10이 있다면 다음 SQL문 실행시 나오는 건수는? (답 : 3)

SELECT DISTINCT DEPTNO FROM EMP​

① 1
② 2
③ 3
④ 4

---