KangKoon's Made ʕ•͡.̮•ʔ: SQL 정리

SQL 사용법에 대해 알아 보자

1. SQL 기초
1) SELECT 문의 이해
- RDB(관계형 데이터베이스 : Relational Database)의 가장 중요한 목적은 저장된 데이터를 사용자가 원하는 형태로 검색하는 것, SQL에서 검색을 담당하는 것이 SELECT 문임.
이렇게 SELECT 문은 테이블로부터 원하는 데이터를 검색하는 RDB의 가장 기본적인 구문임.
또한 SELECT 문은 실행되는 SQL 구문 중에서 사용빈도가 99%에 이를 정도로 빈번히 사용.
테이블의 이름과 칼럼의 이름, 구조를 확인 하는 방법 및 SELECT 문을 통해서 테이블에서 행을 검색하는 가장 기본적인 방법들에 대해 설명

컬럼 : 속성에 대한 값을 갖는 특정 속성
테이블 : 속성 값들이 모여 만들어낸 하나의 범주

> SELECT 문을 이용한 테이블 검색

SQL> SELECT [컬럼, 컬럼, .... | *]
2 FROM 테이블;
* 테이블로부터 지정한 컬럼을 검색
* '*'는 모든 컬럼을 검색 할 때 사용
* '*'을 통해 검색되는 컬럼의 순서는 DESC(내림차순)명령으로 검색한 컬럼 순
* '*'는 컬럼명과 같이 사용 할 수 없고, 반드시 단독으로 사용
* SELECT 절이나 FROM절은 생략 불가
* SELECT 문은 프리포맷으로 여러 줄에 걸쳐 기술 가능

SQL> DESC 테이블;
* 테이블의 구조를 검색
* 컬럼의 이름, 데이터 타입을 확인

SQL> SELECT * FROM tab;
* 현재 스키마(schema)의 모든 테이블 목록을 검색
* tab은 실제 테이블이 아니라 테이블의 목록을 보여주기 위한 가상 테이블
* 오라클에서 스키마는 유저와 같은 의미를 갖음

SQL 문은 다음과 같은 공통적인 특징을 가짐
* SQL 문은 반드시 ';'으로 끝남
* SQL 문은 대소문자를 구별하지 않음
* SQL 문은 반드시 결과(출력)가 존재

-예제1. 현재 스키마에 포함된 모든 테이블을 검색
SQL> SELECT * FROM tab;

위 명령어는 매우 전형적인 구문으로 현재 스키마에 포함된 모든 테이블이나 뷰를 검색하는 데 이용됨, 여기서 사용된 tab은 가상 테이블로 물리적으로 존재하는 개체는 아님

사용자가 자신의 스키마에 포함된 개체를 검색하기 위해서는 딕셔너리라고 불리는 메타정보를 검색해야 하는 데 이것은 매우 어렵고 까다로운 SELECT 문을 이용 해야 함, 이에 오라클은 관리기능을 하지 않는 일반 사용자를 위해 자신의 테이블을 간단히 조회할 수 있는 tab과 같은 가상 테이블을 제공
▲ 딕셔너리(Dictionary)
- 딕셔너리(Dictionary)는 오라클을 관리하기 위한 정보를 담고 있는 테이블과 뷰를 총칭하는 용어로 딕셔너리 정보는 오라클이 스스로 갱신 관리하며, 관라자나 사용자는 단지 검색만 가능, 오라클 버전에 따라 다르지만 일반 사용자의 딕셔너리는 대략 800여개 이상

-예제2. Emp 테이블과 dept 테이블의 구조와 내용을 검색하라.
SQL> DESC emp;

SQL> SELECT * FROM emp;

▲ SELECT 문을 사용해 출력을 하다 보면 화면이 다를 때가 있음
이 때는 화면을 설정하여 출력을 설정함
SQL>set line 값(라인의 전체 넓이를 지정)
SQL>set pages 값(헤더 간격 지정, 페이지당 출력 라인 설정)
SQL>col 칼럼 format a값(컬럼별로 출력 넓이 지정)
예제에서 사용하는 아래 값을 이용해 보고 각 값을 조정해가며 본인 시스템에 맞는 값을 찾아서 사용하길 ~~리디컬~~ 빔, set이나 col 명령은 SQL문이 아니라 ';'이 없어도 됨

-예제3. 사원의 이름과 업무를 검색
SQL> SELECT eno, ename, job FROM emp;

테이블의 행을 출력하는 경우, 출력 되는 행은 특별한 경우가 아니라면 반드시 서로 구별돼야 함, ENO를 출력하여 동일한 ENAME이 있어도 구분이 가능하도록 출력해야 함, 이렇게 데이터가 중복되지 않도록, 올바른 의미를 갖도록 요구 사항을 해석하는 것은 매우 중요함
테이블 내에서 모든 행들은 반드시 구별되야 한다는 것은 RDB의 기본 원칙 중 하나로 이를 개체 무결성이라고 하며 PK(주키:Primary Key)에 의해서 구현

-예제4. 사번, 이름, 급여를 검색하고 검색된 데이터에 따라 출력 형식을 비교하기
SQL> SELECT eno, ename, sal FROM emp;

결과를 자세히 보면 ename은 왼쪽에 붙어 검색된 반면 sal은 오른쪽에 붙어 검색됨 이유는 DESC 문을 통해 확인 할 수 있음. eno와 ename 컬럼의 데이터 타입은 각각 CHAR와 VARCHAR2로 모두 문자형인데 반해 sal 컬럼은 NUMBER 타입으로 숫자형이기 때문. 우리가 사용하는 sqlplus 프로그램은 화면에 데이터를 출력할 때 숫자형 데이터는 오른쪽에 문자형 데이터는 왼쪽에 정렬해서 출력, 이것은 평소에 우리가 문서를 작성하는 것과 동일한 정렬 방식

▲오라클이 지원하는 데이터 타입
오라클에는 매우 많은 종류의 데이터 타입을 제공, 이중에 일반적으로 많이 사용되는 데이터 타입은 CHAR(), VARCHAR2(), NUMBER, DATE가 있음, CHAR는 고정 길이 문자형 데이터 타입이고 VARCHAR2는 가변 길이 문자형 데이터 타입으로 이들은 대표적인 문자형 데이터 타입, 그리고 NUMBER는 숫자형, DATE는 날짜 형 데이터 타입

> SELECT 절을 이용한 수식 검색
SQL> SELECT 수식
2 FROM dual;
* SELECT 절에 컬럼과 무관한 수식을 사용
* SELECT 절에 컬럼을 포함하지 않는 경우 FROM 절에는 더미 테이블 명인 dual을 기입
- dual과 같은 더미 테이블 명을 사용하는 이유는 SELECT 문에 FROM 절을 생략 할 수 없기 때문
SQL> SELECT 2+3 FROM dual;

SELECT 구문에서 FROM 절은 생략이 불가능한 부분이므로 단순 수식의 경우 FROM 절에 dual이라는 더미 테이블 명을 입력함, SELECT 이후에 아무런 칼럼의 이름도 기술되지 않았음으로 FROM절에 쓸 테이블이 없기 때문. 이 때 사용하는 dual은 SELECT 절의 문법을 위해서 사용하는 내용이 없는 문법상의 용어

> 테이블은 2차원 배열과 다름
SQL 언어를 처음 배우는 사람들의 가장 일반적인 오해는 테이블을 2차원 배열처럼 생각하는 것, 테이블이 배열과 다른 가장 큰 차이점은 동일한 행에 포함된 각 컬럼의 데이터가 분리 되지 못한다는 것
배열에서는 필요한 배열의 요소만을 열 단위로 검색하는 것이 가능, 이것은 각 배열에 저장된 데이터들이 독립적으로 존재하고 데이터를 찾기 위해 배열의 킷값이 이용되는 것이기 때문

<문제>
1. 각 과목의 학점수와 담당교수 번호를 검색
SELECT cno, cname, st_num, pno from course;

2. 각 학생들이 속한 학과와 학년을 검색
SELECT sno, sname, syear, major FROM student;

3. 모든 학생의 평균 평점을 검색
SELECT sno, sname, avr FROM student;

4. 학생들의 성별을 검색
SELECT sno, sname, sex FROM student;

5. 각 과목번호와 과목명을 검색
SELECT cno, cname FROM course;

6. 교수의 이름과 소속 학과 및 직위를 검색
SELECT pname, section, orders from professor;

7.(개인 출제 문제) *을 사용하지 않고 학생 테이블의 모든 컬럼을 검색
SELECT sno, sname, sex, syear, major, avr FROM student;

2). 다양한 SELECT 문의 사용
> 별명을 이용한 검색
SQL> SELECT 컬럼 as "별명", 컬럼 as "별명", ...
2 FROM 테이블;

* 별명은 헤더에 출력되는 내용을 바꿔서 검색된 데이터의 해석을 도움
* 예약어 'as'는 생략 가능
* 다음에 해당하는 별명을 사용하는 경우 반드시 " (이중인용부호)를 사용
- 공백이 포함된 경우
- 특수 문자가 포함된 경우(#,$)
- 대소문자를 구별하고자 할 경우
* 다음 두 개의 SQL문은 동일함
- SELECT eno AS "사원번호", ename AS "이름", job AS "담당업무" FROM emp;
- SELECT eno "사원 번호", ename 이름, job 담당업무 FROM emp;

-예제1. 각 사원의 이름과 담당업무를 검색해서 결과를 보고
SQL> SELECT eno 사번, ename 이름, job 업무 FROM emp;

> 수식을 이용한 검색
SQL> SELECT 수식 [as 별명], ...
2 FROM 테이블;
* 수식에는 여러 컬럼을 이용 가능
* 수식이 사용된 경우 헤더에 수식이 출력됨으로 되도록 별명을 이용하는 것이 좋음
* 수식을 이용하는 컬럼에 NULL이 존재하는 경우 결과 값이 NULL이므로 주의

-예제2. 각 사원의 급여와 1년간 수급하는 급여를 검색
SQL> SELECT eno 사번, ename 이름, sal*12 "연간 급여"
2 FROM emp;

SQL 문은 컬럼의 데이터를 원하는 형태로 연산해 출력 가능, 연산은 각 행 단위로 계산, 여러 행에 걸쳐서 연산을 수행하기 위해서는 그룹 함수를 사용

-예제3. 별명에 반드시 이중 인용 부호가 필요한 경우를 확인
SQL> SELECT ename 10name FROM emp;

SQL> SELECT ename "10name" FROM emp;

-예제4. 사원의 연봉을 검색 (연봉 = 12개월분 급여(sal) + 보너스(comm))
SQL> SELECT eno 사번, ename 이름, sal 급여, comm 보너스, sal*12+comm 연봉
2 FROM emp;

NULL을 '0'으로 오해하는 경우가 많은데 NULL은 아직 결정되지 않은 값이라는 의미로 '0'과는 의미가 다름. 그리고 컬럼에 NULL이 존재하는 경우 저장된 정보의 무결성이 손상될 가능성이 있음으로 반드시 꼭 필요한 경우를 제외하고 절대 NULL이 저장되지 않도록 해야 함. 일반적으로 테이블 내에 NULL을 갖는 경우는 대부분 초기 입력에서 제외 된 컬럼인 경우
▲ NULL(널 값)과 부정 연산
- RDBM에서 널 값은 0이나 공백과는 달리 매우 값이 아직 정해지지 않은 특별한 값을 의미
- 널 값이 포함된 경우 보통 연산에 절대 참여해서는 안됨
- 보통 널 값을 포함한 컬럼이 연산에 참여하는 경우는 프로그램상의 실수이거나 데이터베이스 구조상의 문제이기 보다는 외부적인 요인 즉 부정 연산을 위한 경우가 대부분
- 회계상의 부정을 저지르는 경우와 같이 기술적인 문제인 경우보다 운영상의 문제일 가능성이 높다는 것
- 통계 정보를 계산하는 그룹 함수에서 널 값이 포함된 컬럼을 이용하는 경우에 보다 심각한 부정이 나타 날 수 있음

> NULL 연산을 위한 NVL 함수의 사용
NVL(컬럼, 치환 값)
* 컬럼의 NULL을 [치환 값]으로 치환
* NULL이 아닌 경우 원래 값을 반환
* 단일 행 함수 중 일반 함수에 속함
* [치환 값]의 데이터 타입은 반드시 컬럼의 타입과 일치
NVL() 함수는 NULL을 다른 값으로 치환해주는 단일 행 함수

-예제5. 사원의 연봉을 검색
SQL> SELECT eno 사번, ename 이름, sal*12+NVL(comm, 0) 연봉
2 FROM emp;

컬럼에 NULL이 포함되어 있을 때 NVL() 함수를 이용해서 대상 값을 치환하면 올바른 계산을 수행 가능 Emp 테이블의 경우 실습을 위해서 comm 컬럼에 NULL을 허용했으나 sal, comm 컬럼처럼 수식이나 연산에 이용되는 컬럼에 NULL을 허용하는 것은 매우 심각한 문제를 일으킬 수 있음 NULL은 부정확한 통계 정보 및 응용 개발의 지연 등에 매우 좋지 않은 영향을 끼치기 때문 그러므로 이 NULL 값을 가진 컬럼들을 제약조건(constraint)과 같은 RDBMS의 부가적인 기능을 이용해서 NULL이 입력되지 않도록 해야 함

<문제>
1. 급여가 10% 인상될 경우 각 사원의 연봉을 검색
SQL> SELECT eno 사번, ename 사원이름, (sal*(1.1))*12+nvl(comm,0) "10%인상 연봉"
2 FROM emp;