[정처기] PART 07 : SQL 응용 정리노트

구분	설명	명령어
데이터질의어(DQL)	데이터베이스에 저장된 데이터를 검색하는데 사용하는 정의어	SELECT
데이터조작어(DML)	데이터베이스에 저장된 데이터를 수정, 삭제, 추가하는 명령어	INSERT, UPDATE, DELETE
데이터정의어(DDL)	데이터베이스 객체를 생성하고 수정, 삭제 추가하는 명령어	CREATE, ALTER, DROP
데이터제어어(DCL)	데이터베이스의 규정이나 기법을 정의하고 제어하는 언어	GRANT, REVOKE, COMMIT, ROLLBACK

1. 데이터 검색하기

SELECT * FROM employee; <!--모든 사원들의 정보를 검색-->

SELECT empname,dno FROM employee; <!--모든 사원의 이름과 부서번호를 검색-->

SELECT DISTINCT empname,dno FROM employee; <!--중복된 데이터를 제거하고 검색 결과를 검색-->

SELECT empname,dno,salary FROM employee WHERE salary >= 30000;  <!--급여가 30000이상인 사원만-->

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

_ : 하나이상의 어떤 문자

SELECT empname, salary FROM employee WHERE empname like '이%'; <!--이씨 성을 가진 사원의 이름과 급여 검색-->

AND, OR, NOT 사용가능

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

BETWEEN A AND B : 특정 컬럼의 값이 A이상 B이하에 포함되는 데이터를 검색한다.

SELECT empname,salary,dno FROM employee WHERE salary between 2000000 AND 3000000;

C IN(list) : 특정 컬럼의 값이 검색하고자 하는 값 리스트에 해당되는지 검사하고 리스트에 해당되는 데이터를 검색하고자 할 때 사용

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

NULL 표기

SELECT * FROM employee WHERE dno=NULL; <!-- 잘못된 예 -->
SELECT * FROM employee WHERE dno IS NULL; <!-- 잘된 예 -->
SELECT * FROM employee WHERE dno IS NOT NULL; <!-- 잘된 예 -->

DESC : 내림차순으로 정렬하여 보기

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

 

2. 데이터 조작하기

INSERT

INSERT INTO DEPT(DNO, DEPTNAME, FLOOR) VALUES(4,'HR',7);

여러개의 레코드를 한번에 삽입하고자 할 때

INSERT INTO HIGH_SAL(EMPNO,EMPNAME,SALARY,DNO) SELECT*FROM EMPLOYEE WHERE SALARY>=4000000;

DELETE

DELETE FROM DEPT WHERE DNO=4; <!--WHERE절이 없으면 전부 삭제되므로 주의-->

UPDATE

UPDATE EMPLOYEE SET DNO=3 WHERE EMPNO=1234;

DCL 

명령어	기능설명
COMMIT	수행된 결과를 실제 물리적 디스크로 저장(변경 작업이 완료되었음을 알림)
ROLLBACK	명령 수행 실패를 의미하며 수행된 결과를 원복시킴(변경사항 취소)
SAVEPOINT	저장점 지정, 지정된 저장점부터 현재까지 일부만 ROLLBACK 가능(트랜젝션을 작은 부분으로 나눔)
GRANT	데이터베이스 사용자에게 사용 권한 부여
REVOKE	데이터베이스 사용자에게 부여된 사용 권한 취소

GRANT

GRANT SELECT ON EMPLOYEE TO LEE; <!--EMPLOYEE 테이블에 대한 SELECT 권한을 LEE 사용자에게 부여.-->

GRANT REFERENCES (EMPNO) NO EMPLOYEE TO CHOI;<!--EMPLOYEE 테이블의 EMPNO에 대한 참조 권한을 CHOI에게 부여-->

GRANT SELECT, INSERT ON EMPLOYEE TO LEE WITH GRANT OPTION<!--부여받은 권한을 다른 사람에게 부여할 수 있다-->​

REVOKE

REVOKE SELECT, INSERT ON EMPLOYEE FROM LEE;<!--LEE에게 부여한 EMPLOYEE에 대한 SELECT, INSERT 권한을 회수-->

PUBLIC 키워드

GRANT SELECT, INSERT ON EMPLOYEE TO PUBLIC;<!--모든 사용자에게 부여-->

트랜젝션 : 한번에 수행되어야 하는 데이터베이스 연산들의 논리적인 모임을 말하며, 데이터베이스 동시성 제어와 회복을 위한 기본 개념

동시성제어 : 다수 사용자가 데이터베이스를 동시에 접근하도록 허용하면서 데이터베이스의 일관성을 유지하는 DBMS 기능

회복 : 데이터베이스를 갱신하는 도중에 시스템이 고장 나도 데이터터베이스의 일관성을 유지하는 DBMS 기능이다.

 

SELECT DNO,AVG(SALARY) AS "평균 급여",MAX(SALSRY) AS "최대 급여",MIN(SAL) AS "최소 급여" 
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY)>=2500000;
<!--부서별로 사원의 평균 급여, 최대 급여, 최소 급여를 출력하되 평균급여가 "2500000"이상인 부서만 출력하시오-->

 

ROLLUP

SELECT JOB,SUM(SALARY)
FROM EMPLOYEE
GROUP BY JOB;
<!--직무별로 사원의 급여합계를 출력하시오-->

SELECT JOB,SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(JOB);
<!--직무별로 사원의 급여합계와 전체 사원의 급여합계를 출력하시오-->

CUBE

SELECT DNO,JOB,SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DNO,JOB)
<!--부서번호별,직무별로 사원의 급여 합계와 직무별 급여합계를 같이 출력하시오.-->

GRORPING SETS

SELECT DNO,JOB,SUM(SALARY) FROM EMPLOYEE GROUP BY GROUPING SETS(DNO,JOB)
<!-- 부서별 사원의 급여합계와 직무별 사원의 급여합게를 동시에 출력하시오-->

절차형 SQL : SQL문의 연속적인 실행이나 조건에 따른 분기, 반복 등의 제어를 활용하여 다양한 기능을 수행하는 데이터베이스 저장모듈이다.(프로시저, 사용자정의함수, 트리거)

기본구성

-DECLARE

-BEGIN

-END

 

프로시저 : 매개변수를 받을 수 있고 반복해서 사용할 수 있는 이름이 있는 SQL 블록으로 연속실행 또는 구현이 복잡한 트랜잭션을 수행하는 SQL블록을 데이터베이스에 저장하기 위해 생성한다.

 

트리거 : 데이터 베이스의 특정한 변경이 가해졌을 때 명시된 이벤트가 발생할 때마다 DBMS가 이에 대응해서 자동적으로 호출하는 일종의 프로시저