정보처리기사(필기) 📝 03. 데이터베이스 구축 (1)

📌 1. SQL 응용

✅ 1) SQL 개념

♦️ EMPLOYEE

사원번호	이름	직급	급여	지역번호
1	짱구	과장	150	02
2	맹구	차장	300	031
3	훈이	인턴		061

• 사원번호는 Primary Key(unique, NotNull)
• 지역번호는 Foreign Key (AREA 테이블의 Primary Key)

♦️ AREA

지역번호	지역명
02	서울
031	경기
061	전남

• 지역번호는 Primary Key(unique, NotNull)
• 맹구의 지역은? 경기

관계(Relation)가 있는 테이블 간에 내가 원하는 정보를 찾아내는 언어가 SQL이다!

Select *
From employee;
-- 다 보여줌

Select 이름, 직급
From employee;
-- 짱구 과장, 맹구 차장, 훈이 인턴

Select 이름, 급여
From employee;
Where 급여 <= 200;
-- 짱구 150

✅ 2) DDL (Data Define Language, 데이터 정의어)

♦️ CREATE

create table emp -- emp라는 이름의 테이블 만듦
(
	empno  number(4, 0) -- 첫 번째 컬럼, 네 자리 숫자만 들어감
	dname  varchar2(14) -- 두 번째 컬럼, 문자형 변수만 14자까지 들어감
	loc    varchar3(13) -- 세 번째 컬럼, 문자형 변수만 13자까지 들어감
	deptno number(2, 0)
	constraint pk_emp primary key (empno) -- deptno가 primary key다
	constraint pk_deptno foreign key (deptno) references dept (deptno)
	-- dept 테이블의 primary key인 deptno를 참조 > foreign key다
);

• UNIQUE KEY(UK)
  • 반드시 유일해야 함
• NOTNULL(NN)
  • 반드시 값이 있어야 함
• CHECK(CK)
  • 성별에 많이 사용(둘 중에 하나만 반드시 선택)
• DEFAULT
  • 아무것도 기입하지 않을 때의 기본 값

🔏 SUBQUERY를 이용한 CREATE TABLE

• 서브 쿼리를 이용해서 SELECT의 조회 결과로 테이블을 생성하는 방법
• 컬럼명과 데이터 타입, 값이 복사되고 제약 조건은 NOT NULL만 복사됨

CREATE TABLE EMP_DEPTNO_10 -- EMP_DEPTNO_10이라는 테이블을 만들자
AS SELECT EMPNO, ENAME, SAL, DEPTNO -- 가져올 항목 (NOT NULL)
	FROM EMPLOYEE -- EMPLOYEE 테이블에서
	WHERE DEPTNO = 10 -- DEPTNO가 10인 애들만

🔏 CREATE VIEW

• 하나 이상의 BASE TABLE을 기반으로 생성은 되었으나 물리적으로 존재하지 않고 DATA DICTIONARY에 SELECT 구문 형태로 정의만 되어 있는 **가상의 논리적인 테이블**
  • 데이터 접근 제어로 보안을 제공한다.
  • 삽입, 삭제 갱신 연산에 제약사항이 따른다.

CREATE VIEW WHO_IS_MANAGER
AS SELECT *
	FROM EMP
	WHERE JOB = 'MANAGER'
	
SELECT *
	FROM WHO_IS_MANAGER

♦️ ALTER

• 테이블에 정의된 내용을 수정할 때 사용

ALTER TABLE DEPT
ADD CNAME VARCHAR2(20) -- 컬럼 추가

ALTER TABLE DEPT
ADD CONSTRAINT UNIQUE(DNAME) -- 제약조건 추가

ALTER TABLE DEPT
MODIFY CNAME CHAR(3) -- 컬럼 수정

ALTER TABLE DEPT
DROP COLUMN CNAME -- 컬럼 삭제

• VIEW는 실제로 존재하는 테이블이 아니므로 ALTER로 변경이 불가능
• DROP 후 다시 CREATE 해야 함

♦️ DROP

• 데이터베이스 객체를 삭제하는 구문

DROP TABLE EMP -- EMP 테이블 자체를 제거
DROP TABLE EMP CASCADE -- EMP를 참조하는 다른 모든 개체를 함께 제거
DROP TABLE EMP RESTRICTED -- 참조하는 뷰나 제약조건이 있으면 실행 실패
DROP VIEW EMP_VIEW -- VIEW 삭제

✅ 3) DCL (Data Control Language, 데이터 제어어)

♦️ COMMIT

• 삽입한 데이터를 영구히 저장

START TRANSACTION;
INSERT INTO employees (name, position, salary) VALUES ('Alice', 'Developer', 5000);
UPDATE employees SET salary = 5500 WHERE name = 'Alice';

COMMIT;  -- 변경 사항 확정 (저장)

♦️ ROLLBACK

• COMMIT한 데이터를 제외하고 메모리에 있는 데이터를 날림

START TRANSACTION;
DELETE FROM employees WHERE name = 'Alice';  -- Alice 삭제
ROLLBACK;  -- 삭제 취소

♦️ GRANT

• 특정 사용자에게 데이터베이스 객체(테이블, 뷰, 프로시저 등)에 대한 권한을 부여할 때 사용
• WITH GRANT OPTION: 해당 사용자가 다른 사용자에게도 같은 권한을 부여할 수 있도록 허용

GRANT 사용자등급 TO 사용자_ID (WITH GRANT OPTION)

♦️ REVOKE

• 사용자에게 부여된 권한을 철회할 때 사용
• WITH GRANT OPTION을 가진 사용자의 권한을 철회하면, 그 사용자가 부여했던 권한도 함께 철회됨
• REVOKE를 사용하면 부여된 권한만 철회되며, 사용자 계정 자체가 삭제되지는 않음

REVOKE 사용자등급 FROM 사용자_ID

✅ 4) DML (Data Manipulation Language, 데이터 조작어)

♦️ INSERT

• 테이블에 새로운 튜플을 삽입

insert into DEPT
values(50, '총무부', '서울')

♦️ UPDATE

• 테이블의 값 갱신

update DEPT
set LOC = '대구'
where deptno = 60

commit

♦️ DELETE

• 튜플 삭제

delete
from DEPT -- 다 지워짐

rollback -- 다시 살려

delete
from DEPT
where deptno = 50

✅ 5) DML - select

♦️ SELECT

• select, from 제외 중간중간 생략은 가능하지만 순서를 절대 바꿀 수 없음 (SF영화는왠쥐혼자보고싶다..)

-- select 속성명
-- from 테이블명
-- where 조건
-- group by 속성명
-- having group by에 대한 조건
-- order by 정렬 (asc/desc)

select EMPNO, ENAME, SAL
from EMP
where SAL BETWEEN 1000 AND 2000 -- 1000 <= SAL <= 2000

select EMPNO, ENAME
from EMP
where ENAME LIKE 'S$' -- ENAME이 S로 시작하는 모든 사람
-- 'S%'면 S로 끝나는 사람
										  
select EMPNO, ENAME, DEPTNO
from EMP
where DEPTNO IN (10, 20) -- DEPTNO가 10 or 20

♦️ DISTINCT

• 같은 값이 중복돼서 나오지 않게 방지

select DISTINCT MGR
from EMP