정보처리기사 실기 - 7. SQL 응용(1) /DCL/DML/DDL

NCS 정보처리기사

1) 응용 SQL 작성하기

- SQL 특징 : 비절차적 언어, 대화식 언어 또는 호스트 언어에 내장된 혀태로 사용

 -> 프로그래밍 언어(호스트 언어)에 SQL을 내포

 

- SQL 종류

데이터를 검색하는 SELECT 명령문은 데이터 질의어(DQL)로 분류하거나 데이터 조작어에 포함하기도 한다.

• 데이터 질의어(DQL) : SELECT
• 데이터 조작어(DML) : INSERT, UPDATE, DELETE
• 데이터 정의어(DDL) : CREATE, ALTER, DROP
• 데이터 제어어(DCL) : GRANT, REMOVE, COMMIT, ROLLBACK

- 데이터 검색하기

 

- SELECT 문 형식

SELECT employee, dno FROM employee;

-> 모든 사원의 이름과 부서번호를 검색하시오.

 

SELECT DISTINCT dno FROM employee;

-> 중복을 제거한 결과를 검색하고자 할 때 DISTINCT 키워드 기술

 -> 모든 사원의 부서번호를 중복없이 검색하시오.

 

SELECT employee, salary, dno FROM employee WHERE salary >= 3000000;

-> 급여가 300만원 이상인 사원의 이름과 급여, 부서번호를 출력

 -> 특정 조건을 만족하는 튜플(행)을 검색

 

SELECT employee, salary FROM employee WHERE employee like '이%';

-> 이씨 성을 가진 사원의 이름과 급여를 검색하시오

 -> 와일드 카드 문자

• % : 문자가 없거나 하나 이상의 어떤 문자
• _  : 하나의 어떤 문자

 

SELECT employee, salary FROM employee WHERE dno = 1 AND salary >= 1500000;

-> 1번 부서에서 근무하고 급여가 150만원 이상인 사원의 이름과 급여를 검색

 -> 두개 이상의 조건식을 연결하고자 할 경우 AND, OR, NOT 연산자를 이용

 

SELECT employee, salary, dno FROM employee WHERE salary BETWEEN 2000000 AND 3000000;

-> 급여가 200만원 이상, 300만원 이하인 사원의 이름과 급여, 부서번호를 검색

 -> BETWEEN A AND B : 특정 컬럼의 값이 A 이상 B이하에 포함되는 데이터 검색

 

SELECT * FROM employee WHERE dno IN(1,3);

-> 부서번호 1번 또는 3번에 속한 사원의 모든 정보를 검색

 -> A IN(list) : A 컬럼의 값이 list에 포함된 경우 해당 레코드를 출력

 

SELECT employee, salary, salary*1.1 AS newsalary FROM employee WHERE dno=1;

-> 부서번호 1번에 속한 사원들에 대해 현재급여와 10% 인상한 급여를 검색

 -> 산술연산자(+,-,*,/)를 이용해 검색 결과에 연산을 수행한 결과를 확인할 수 있음

 

- 데이터베이스에서 아직 정해지지 않았거나 적용할 수 없어 비워둔 값을 표기할 때는 NULL을 사용

 

SELECT employee, salary FROM employee WHERE dno=1 ORDER BY salary desc;

-> 부서번호 1번에 속한 사원들의 이름과 급여를 검색하되 급여가 높은 순서대로 검색

 -> 검색결과 정렬은 ORDER BY, ASC는 오름차순(생략가능), DESC는 내림차순

 

 

- 데이터 조작하기

 

- INSERT 문

INSERT INTO dept(dno, deptname, foor) VALUES (4, 'HR', 7);

-> 컬럼명을 생략할 경우 테이블 생성 시 정의한 순서대로 값을 입력

-> 테이블에 새로운 레코드들을 한번에 여러개씩 삽입할 경우 서브쿼리 이용

 -> VALUE 절 대신 SELECT 절 기술

  -> 이 때 SELECT 절 컬럼의 개수와 타입이 INSERT INTO 절 컬럼 개수와 타입이 일치해야 함

INSERT INTO high_sal (empno, empname, salary, dno) SELECT * FROM employee WHERE salary >= 4000000;

-> 사원 테이블에서 급여가 400만원 이상인 사원의 정보를 high_sal 테이블에 삽입

 

- DELETE 문

DELETE FROM dept WHERE dno = 4;

-> 부서 테이블에서 부서번호 4번인 부서를 삭제

 

- UPDATE 문

한 테이블에 들어 있는 레코드(튜플)들의 컬럼(애트리뷰트) 값을 수정할 때 사용

UPDATE employee SET dno=3 WHERE empno = 1234;

-> 사원 테이블에서 사원번호 1234번의 부서번호를 3번으로 수정

 

- DCL 명령문 작성

- DCL(Data Control Language) 개요

데이터베이스의 규정이나 기법을 정의하고 제어하는 언어

-> 사용자 권한을 부여하거나 취소하는 명령어, 트랜잭션을 제어하는 명령어를 포함

-> 무결성, 보안, 회복, 동시성 제어

 

- DCL 종류 : COMMIT, ROLLBACK, SAVEPOINT, GRANT, REVOKE

 

- 사용자 권한 제어

GRANT SELECT, INSERT ON employee TO lee WITH GRANT OPTION;

-> with grant option : 부여받은 권한을 다른 사용자에게 허가 가능

-> 권한 취소시 취소 당한 사용자가 with grant option을 통해 다른 사용자에게 허가했던 권한들도 연대 취소(cascade)

REVOKE SELECT, INSERT ON employee FROM lee;

 

- 역할 : 여러 사용자들에게 대한 권한 관리를 단순화하기 위해

GRANT CREATE TABLE, ALTER TABLE, DROP TABLE TO PROGRAMMER;
GRANT PROGRAMMER TO choi;

 

- 시스템 권한 : 사용자가 데이터베이스에서 특정 작업을 수행할 수 있도록 하는 권한

- 객체 권한 : 사용자가 특정 객체(테이블, 뷰, 프로세서 등)에 대해 특정 연산을 수행할 수 있도록 함

 

- PUBLIC 키워드를 사용하여 권한 허가 시 모든 사용자에게 부여

GRANT SELECT, INSERT ON employee TO PUBLIC;

 

- 시스템 권한의 허가는 데이터베이스 관리자가 GRANT 문을 사용하여 허가

GRANT CREATE SESSION TO kim WITH ADMiN OPTION;

-> with admin option을 이용하여 권한을 받은 사용자가 다시 해당 권한을 다른 사용자에게 허가 가능

 -> 시스템 권한 취소 시 연쇄 취소 일어나지 않음

정보처리기사 실기 - 7. SQL 응용(2) /트랜잭션/집계성SQL/윈도우함수