Study Web Development

11월 18일

이전으로

Oracle SQL

1. Database 개요

데이터베이스(Database)
- 지속적으로 저장되는 연관된 정보의 모음
DBMS(Database Management System)
- 데이터를 효율적으로 관리할 수 있는 시스템
  - 데이터베이스에 추가, 삭제, 변경, 검색 기능을 제공
  - 접근 제어, 보안 및 권한 관리 기능을 제공
관계형 데이터베이스(Relational Database)
- 데이터를 행과 열 구조를 갖는 표(=관계) 형식으로 표현
  - 표는 물리적 포인터를 갖지 않음
  - 표에 데이터를 행 단위로 저장하고, 열을 통해 식별함
- 표 형식의 데이터를 조작하여 다른 관계를 생성할 수 있는 관계 연산자 집합을 제공
  - SQL문을 실행하여 데이터 접근 및 수정
- 데이터 무결성(=데이터의 정확성과 일관성을 유지, 보증하는 것)을 제공

2. SQL(Standard Query Language)

데이터베이스에서 데이터를 검색, 삽입, 갱신, 삭제할 수 있는 표준 언어
SQL문은 대소문자를 구분하지 않음
- 일반적으로 키워드는 대문자로, 테이블 및 컬럼 이름은 소문자로 입력
SQL문은 여러 줄에 걸쳐 입력할 수 있지만, 키워드는 한 줄에 입력해야 함
- 일반적으로 절은 별도의 줄에 입력

2-2 SQL문의 종류

DDL(Data Definition Language)
- 데이터베이스 객체를 생성, 삭제, 재정의 등 관리하는 명령어
DML(Data Manipulation Language)
- 데이터를 입력, 삭제, 수정, 검색 등 조작하는 명령어
DCL(Data Control Language)
- 데이터베이스 사용자의 권한을 부여, 해제 등 제어하는 명령어
TCL(Transaction Control Language)
- 데이터베이스 트랜잭션을 제어하는 명령어

2-3 SELECT문

데이터베이스 객체(=테이블)로부터 행 단위로 데이터를 검색하는 데 사용

구문

테이블에 저장된 데이터 읽기

-- 1) 컬럼명 명시
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;
-- 2) * : 모든 컬럼을 의미
SELECT * FROM emp;
-- 3) 특정 컬럼 선택
SELECT ename, sal -- SELECT문
FROM emp; -- FROM절-- FROM절

주석

-- SQL 문장 안에 명시하는 것이 원칙
SELECT * /* 주석 */ FROM emp;

SELECT empno, -- 사번
ename, job, 
mgr, -- 관리자 사번
comm, -- 커미션 금액, 판매 실적에 따른 보상
deptno -- 부서 번호
FROM emp; -- FROM 앞에는 , 올 수 없음

SELECT deptno, -- 부서 번호
dname, -- 부서명
loc -- 부서 위치
FROM dept;

dual

-- 함수 및 계산의 결과를 볼 때 사용할 수 있는 공용(public) 테이블
-- 특정 테이블로부터 비롯된 결과가 아닌 경우에 SELECT문 형식을 맞춰주기 위해 사용
SELECT SYSDATE FROM dual; -- 내부적으로는 시, 분, 초 정보도 가지고 있지만 출력하지 않음
SELECT 7+10 FROM dual;
SELECT ASCII(0) FROM dual; -- 인자의 ASCII 코드값을 반환하는 함수

연산

-- SELECT문은 테이블의 데이터를 메모리로 읽어오는 것
-- 연산 결과는 가상의 컬럼을 메모리에 만들어 출력
SELECT ename, sal, sal+300 FROM emp; -- sal 컬럼의 값에 300 더한 연산 결과를 가상의 컬럼 sal+300을 출력
SELECT ename, sal, (sal+300)*12 FROM emp;

NULL

-- 사용할 수 없거나, 할당되지 않았거나, 알 수 없거나, 적용할 수 없는 값
-- 0이나 공백과 다르며, NULL을 포함한 산술식은 NULL로 계산됨
SELECT ename, comm, comm+100 FROM emp;

컬럼 alias 정의

-- 컬럼의 이름을 바꾸는 것
-- 1) 컬럼 이름 바로 다음에 별칭을 표시
SELECT sal*12 ASal FROM emp; -- 컬럼의 이름은 대소문자 구분하지 않음
-- 2) 컬럼 이름과 별칭 사이에 AS 키워드 표시
SELECT sal*12 AS ASal FROM emp;
SELECT ename AS name, comm AS commission FROM emp;
-- 3) 별칭을 ""로 감싸기
	-- 대소문자 구분이 필요한 경우
	-- 공백을 포함하는 경우
	-- _, # 등 특수문자 사용하는 경우
	-- 숫자로 시작하는 경우
SELECT sal*12 "Annual Salary" FROM emp; -- 자바와 연동시에는 별칭을 ""로 지정하지 않음

연결 연산자 ||

-- 열이나 문자열을 다른 열에 연결하여 결과 열로 문자식을 생성
-- 문자열에 NULL값을 연결하는 경우 결과는 문자열이 됨
SELECT ename||' has $'||sal FROM emp; -- SQL문은 문자와 문자열을 구분하지 않고 '' 사용
SELECT ename||job AS "Employees" FROM emp;
SELECT ename||comm FROM emp; -- comm 값이 NULL인 행들은 NULL이 사라지고 ename의 문자열만 남음

DISTINCT

-- 중복 행 삭제
SELECT DISTINCT deptno FROM emp;
SELECT DISTINCT(deptno) FROM emp; -- DISTINCT는 함수가 아니라 명령어이며, 소괄호는 최우선 연산자를 의미

실습

-- 1) emp 테이블에서 사원 번호, 사원 이름, 월급을 출력하시오.
SELECT empno, ename, sal FROM emp;
-- 2) emp 테이블에서 사원 이름과 월급을 출력하는데 컬럼명은 "이 름", "월 급"으로 변경하여 출력하시오.
SELECT ename AS "이 름", sal "월 급" FROM emp;
-- 3) emp 테이블에서 사원 번호, 사원 이름, 월급, 연봉을 구하고 각각 컬럼명을 "사원번호", "사원이름", "월급", "연봉"으로 변경하여 출력하시오.
SELECT empno 사원번호, ename 사원이름, sal 월급, sal*12 연봉 FROM emp; -- 자바와 연동시에는 별칭을 한글로 지정하지 않음
-- 4) emp 테이블에서 업무를 중복되지 않게 표시하시오.
SELECT DISTINCT job FROM emp;
-- 5) emp 테이블의 사원명과 업무를 쉼표(,)로 연결해서 표시하고 컬럼명을 Employee and Job으로 표시하시오.
SELECT ename||','||job "Employee and Job" FROM emp;

WHERE절

선택을 사용하여 행 제한

-- 숫자
SELECT * FROM emp WHERE deptno=10; -- 비교 연산자에서 같다는 의미로 ==가 아닌 =를 사용
-- 문자열
SELECT ename, job, deptno FROM emp WHERE ename='SMITH'; -- 데이터는 대소문자 구분
-- 날짜
SELECT * FROM emp WHERE hiredate>'81-12-03'; -- 날짜 구분자는 -와 /를 모두 허용
SELECT * FROM emp WHERE hiredate!='80/12/17'; -- 비교 연산자에서 같지 않다는 의미로 !=, <>, ^=를 사용
SELECT * FROM emp WHERE hiredate<>'80/12/17';
SELECT * FROM emp WHERE hiredate^='80/12/17';

구간

-- 1) 논리 연산자 AND로 비교 연산자 2개를 결합
-- 2) BETWEEN ... AND ... : 지정한 값 포함 두 값 사이
SELECT ename, sal FROM emp WHERE sal>=1000 AND sal<=3000;
SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 3000;
SELECT ename FROM emp WHERE ename BETWEEN 'KING' AND 'SMITH'; -- 문자열 구간은 사전순으로 비교하여 판단

-- 다음에 오는 키워드를 부정
SELECT ename, sal FROM emp WHERE sal NOT BETWEEN 1000 AND 3000;
SELECT ename FROM emp WHERE ename NOT BETWEEN 'KING' AND 'SMITH';

-- 값 목록 중의 값과 일치
-- 소괄호로 값 목록을 감싸야 함
SELECT * FROM emp WHERE sal IN (1300, 2450, 3000);
SELECT * FROM emp WHERE sal NOT IN (1300, 2450, 3000);
SELECT * FROM emp WHERE ename IN ('ALLEN', 'FORD');
SELECT * FROM emp WHERE ename NOT IN ('ALLEN', 'FORD');

-- 패턴 검사
-- %는 0개 이상의 문자를 나타내며, _는 한 문자를 나타냄
SELECT * FROM emp WHERE ename LIKE 'S'; -- 가변 문자가 없는 경우 LIKE가 =로 처리됨
SELECT * FROM emp WHERE ename LIKE '%S%'; -- S가 처음, 중간, 끝에 있는 모든 이름
SELECT ename, hiredate FROM emp WHERE hiredate LIKE '%22';
SELECT ename, hiredate FROM emp WHERE hiredate LIKE '__/1_/%'; -- 월이 11 또는 12
SELECT * FROM emp WHERE ename LIKE 'FOR_'; -- FOR 이후 한 문자가 있는 이름
SELECT * FROM emp WHERE ename LIKE '_M%'; -- 두 번째 글자가 M인 이름

NULL 조건

-- IS, IS NOT 연산자 사용
SELECT * FROM emp WHERE comm=NULL; -- 에러는 발생하지 않지만, 데이터를 불러오지 못함
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm!=NULL; -- 에러는 발생하지 않지만, 데이터를 불러오지 못함
SELECT * FROM emp WHERE comm IS NOT NULL;

논리 연산자 AND, OR, NOT

SELECT empno, ename, job, sal FROM emp WHERE sal>=2000 AND job LIKE '%MAN%';
SELECT empno, ename, job, sal FROM emp WHERE sal>=2000 OR job LIKE '%MAN%';
SELECT ename, job FROM emp WHERE job NOT IN ('CLERK', 'SALESMAN');

실습 문제

-- 1) emp 테이블에서 사원 번호가 7698인 사원의 이름, 업무, 급여를 출력하시오.
SELECT ename, job, sal FROM emp WHERE empno=7698;
-- 2) emp 테이블에서 사원 이름이 SMITH인 사원의 이름, 월급, 부서 번호를 구하시오.
SELECT ename, sal, deptno FROM emp WHERE ename='SMITH';
-- 3) 월급이 2500 이상이고 3500 미만인 사원의 이름, 입사일, 월급을 구하시오.
SELECT ename, hiredate, sal FROM emp WHERE sal>=2500 AND sal<3500;
-- 4) 급여가 2000에서 3000 사이에 포함되지 않는 사원의 이름, 업무, 급여를 출력하시오.
SELECT ename, job, sal FROM emp WHERE sal NOT BETWEEN 2000 AND 3000;
-- 5) 81년 05월 01일과 81년 12월 03일 사이에 입사한 사원의 이름, 급여, 입사일을 출력하시오.
SELECT ename, sal, hiredate FROM emp WHERE hiredate BETWEEN '1981/05/01' AND '81/12/03';
-- 6) emp 테이블에서 사원 번호가 7566, 7782, 7934인 사원을 제외한 사람들의 사원 번호, 이름, 월급을 출력하시오.
SELECT empno, ename, sal FROM emp WHERE empno NOT IN (7566, 7782, 7934);
-- 7) 부서 번호 30에서 근무하며 월 2,000달러 이하를 받는 81년 05월 01일 이전 입사자의 이름, 급여, 부서 번호, 입사일을 출력하시오.
SELECT ename, sal, deptno, hiredate FROM emp
WHERE deptno=30 AND sal<=2000 AND hiredate<'81-05-01';
-- 8) 급여가 2,000달러와 5,000달러 사이고 부서 번호가 10 또는 30인 사원의 이름과 급여, 부서 번호를 출력하시오.
SELECT ename, sal, deptno FROM emp
WHERE (sal BETWEEN 2000 AND 5000) AND deptno IN (10, 30);
-- 9) 업무가 SALESMAN 또는 MANAGER이면서 급여가 1,600, 2,975, 2,850달러가 아닌 모든 사원의 이름, 업무 및 급여를 출력하시오.
SELECT ename, job, sal FROM emp
WHERE (job='SALESMAN' OR job='MANAGER') AND sal NOT IN (1600, 2975, 2850);
-- 10) 이름에 S가 포함되지 않은 사람들 중 부서 번호가 20인 사원들의 이름과 부서 번호를 출력하시오.
SELECT ename, deptno FROM emp
WHERE ename NOT LIKE '%S%' AND deptno=20;

다음으로