SELECT 문으로 특정 데이터를 추출하기

1.데이터를 조회하기 위한 SELECT

SELECT [DISTINCT]{*, column[Alias],...}

FROM 테이블명;

 

1) SELECT 컬럼, 컬럼 FROM 테이블 => 테이블로부터 컬럼값을 조회해라.

--: 쿼리문 주석

 

2) 칼럼 이름을 명시해서 특정 칼럼만 보기

DEPARTMENTS  테이블에서 부서번호와 부서명만 출력 (역순)

SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

--사원의 이름과 급여와 입사일자만을 출력하는 SQL문을 작성
SELECT FIRST_NAME, LAST_NAME, SALARY, HIRE_DATE FROM EMPLOYEES;

 

3) 칼럼 이름에 별칭 지정하기

AS로 컬럼에 별칭 부여하기: 칼럼을 기술한 바로 뒤에 AS라는 키워드를 쓴 후 별칭을 기술

별칭에 공백문자나 $,_,# 등 특수 문자를 표현하고 싶거나 대소문자를 구별하고 싶으면 ""을 사용한다. AS를 생략하고 ""를 사용하여 별칭부여가 가능. 한글이라서 묶는게 아니라 공백이 있으면 묶는다.

자바

-문자형: ''

-문자열:""

오라클

문자:''(대소문자 구분)//작은 따옴표

숫자: 그대로

 

4)Concatenation 연산자의 정의와 사용(연결 연산자)

-오라클에서는 여러 개의 컬럼을 연결할 때 사용하기 위해서 Concatenation 연산자를 제공해 준다. 컬럼과 쿤자열 사이에 연산자를 기술

--EMPLOYEES 테이블에서 여러 컬럼을 하나의 문자열로 출력

SELECT FIRST_NAME||'의 직급은'||JOB_ID||'입니다.' AS직급FROM EMPLOYEES;
SELECT FIRST_NAME||'의 직급은'||JOB_ID||'입니다.' "직급"FROM EMPLOYEES;

SELECT FIRST_NAME||'의 직급은'||JOB_ID||'입니다.' 직급 FROM EMPLOYEES;

SELECT FIRST_NAME||'의 입사일은'||HIRE_DATE||'입니다.' 입사일 FROM EMPLOYEES;
SELECT FIRST_NAME||' '||LAST_NAME 이름, SALARY 급여, HIRE_DATE 입사일 FROM EMPLOYEES;

 

5)중복된 데이터를 한번씩만 출력하게 하는 DISTINCT

 

EMPOLOYESS 테이블에서 칼럼 JOB_ID를  표시하되 중복된 값은 한번만 표시

SELECT JOB_ID FROM EMPLOYEES;

=>

SELECT DISTINCT JOB_ID FROM EMPLOYEES;

 

--직원들이 어떤 부서에 소속되에 있는지 소속 부서번호 출력하되 중복되지 않고 한번씩 출력
SELECT DISTINCT DEPARTMENT_ID "소속 부서번호" FROM EMPLOYEES;

 

 

6)WHERE 조건과 비교 연산자

 

--급여를 10000 이상 받는 직원을 대상
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY>=10000;

--급여를 3000 미만 받는 작원을 대상
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY<3000;

--사원에 급여를 1000 인상하여 사원번호, 사원명, 급여, 인상급여, 입사일을 출력하시오
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME||' '|| LAST_NAME 사원명, SALARY 급여, SALARY+1000 인상급여, HIRE_DATE 입사일
FROM EMPLOYEES;

--EMPLOYEES 테이블에서 부서 번호가 110번인 직원에 관한 모든 정보만 출력하라
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=110;

--EMPLOYEES 테이블에서 급여가 5000미만이 되는 직원의 정보 중에서 사번과 이름, 급여를 출력
SELECT EMPLOYEE_ID 사번, FIRST_NAME 이름, SALARY 급여 
FROM EMPLOYEES 
WHERE SALARY<5000;

 

문자 데이터 조회

문자 데이터는 반드시 단일 따옴표 안에 표시. 대소문자를 구분 쿼리문은 구분 안하지만 데이터는 구분

 

날짜 데이터 조회

반드시 단일 따옴표 안에 표시 년/월/일 형식으로 기술

 

--문자 데이터 조회, 날짜 데이터 조회
--이름이 JOHN인 사람의 사원번호와 직원명과 업무ID을 출력, 문자 데이터는 대소문자를 구분하므로
--따옴표 안에 대소문자 구분
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME 직원명, JOB_ID 업무ID 
FROM EMPLOYEES
WHERE FIRST_NAME='John';

--2008년 이후에 입사한 직원, 2008만 입력하면 에러 발생. 리터럴이 형식 문자열과 일치하지 않음
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE>='2008/01/01';

 

7)논리 연산자

• AND 연산자: 조건 모두 만족
• OR 연산자: 두 가지 조견 중에서 한가지만 만족하더라도 검색할 수 있도록 하기 위해 사용
• NOT 연산자: 반대되는 논리값

--급여가 5000에서 10000이하 직원 정보 출력
SELECT *
FROM EMPLOYEES 
WHERE SALARY>=5000 AND SALARY<=10000;

--부서번호가 100번이거나 직급이 FI_MGR인 직원
SELECT EMPLOYEE_ID, FIRST_NAME, PHONE_NUMBER, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100 OR JOB_ID='FI_MGR';
--사원 번호가 134이거나 201이거나 107인 직원 정보 출력
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID=134 OR EMPLOYEE_ID=201 OR EMPLOYEE_ID=107;

BETWEEN AND 연산자

특정 범위 내에 속하는 데이터를 알아보려고 할 때 BETWEEN AND 연산자를 사용

column_name BETWEEN A AND B

컬럼>=값 AND 컬럼<=값

SELECT EMPLOYEE_ID 직원번호, FIRST_NAME 이름, SALARY 급여
FROM EMPLOYEES
WHERE SALARY BETWEEN 2500 AND 4500;

 

IN 연산자

동일한 칼럼이 여러 개의 값 중에 하나인지를 살펴보기 위해서 간단하게 표현할 수 있는 IN연산자 사용

column_name IN(A, B, C);

 

WHERE EMPLOYEE_ID=177 OR EMPLOYEE_ID=101 OR EMPLOYEE_ID=184

WHERE EMPLOYEE_ID IN(177, 101, 184)

--부서 번호가 10,20,30 중 하나에 소속된 직원의 직원번호, 이름, 급여를 출력
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=10 OR DEPARTMENT_ID=20 OR DEPARTMENT_ID=30;

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30);

--사원 테이블에서 JOB_ID가 'SA_MAN', 'ST_MAN', 'PU_MAN', 'AC_MGR'인 
--사원번호, 사원명, 직무번호를 출력
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('SA_MAN', 'ST_MAN', 'PU_MAN', 'AC_MGR');

8)LIKE 연산자

검색하고자 하는 값을 정확히 모를 경우 와일드카드와 함께 사용하여 원하는 내용을 검색하는 연산자

column_name LIKE pattern

%	문자가 없거나, 하나 이상의 문자가 어떤 값이 오든 상관없다
_	하나의 문자가 어떤 값이 오든 상관없다.

 

%: 검색하고자 하는 값을 정확히 모를 경우 사용. %는 몇 개의 문자가 오든 상관없다는 의미

%자바: 자바로만 끝나면

자바%: 자바로만 시작하면

%자바%: 무조건 자바가 포함되면

_ _ _:3글자

_D%: 이름의 두번째 글자D

LOWER(FIRST_NAME): 소문자 변환해서 검색

--사원테이블(EMPLOYEES)에서 직무ID에 3번째 _를 포함하고 4번째 자리의 값이 P인 레코드를 조회
SELECT *
FROM EMPLOYEES
WHERE JOB_ID LIKE '___P%';
--ESCAPE
--LIKE 연산으로'%'나 '_'가 포함된 문자를 검색하고자 할때 사용
--'%'나 '_'앞에 ESCAPE로 특수문자를 지정하면 검색할 수 있다.
--특수문자는 아무거나 상관없이 사용가능
--구문 마지막에 ESCAPE에 사용할 문자열만 지정해주면 '_'나'%'를 검색에 사용할 수 있게 도와준다.

SELECT * FROM EMPLOYEES
WHERE JOB_ID LIKE '__\_P%' ESCAPE '\';

SELECT * FROM EMPLOYEES
WHERE JOB_ID LIKE '__$_P%' ESCAPE '$';

9)NULL을 위한 연산자

오라클에서는 컬럼에 NULL값이 저장되는 것을 허용한다.

NULL은 미확정, 알 수없는 값을 의미한다. 0도 빈 공간도 아닌 어떤값이 존재하기는 하지만 어떤 값인지를 알아낼 수 없는 것을 의미. NULL은 연산, 할당, 비교가 불가능

100+NULL=NULL

NULL이 저장되어 있는 경우에는 = 연산자로 판단할 수 없다.

IS NULL, IS NOT NULL을 사용

 

--이름에 a를 포함하지 않은 직원의 직원번호, 이름 출력
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) NOT LIKE '%a%';

--커미션을 받지 않는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT = NULL;

--커미션을 받지 않는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL;

--커미션을 받는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

--자신의 직속상관이 없는 직원의 전체 이름과 직원번호, 업무ID를 출력하라
SELECT FIRST_NAME||' '||LAST_NAME 이름, EMPLOYEE_ID 직원번호, JOB_ID 업무ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

--커미션을 받는 사원만 출력하되 사원번호, 이름, 급여, 수당율, 
--수당금액(계산식 - 급여*수당율)을 출력하라
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT 수당금액
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

SELECT 컬럼, 컬럼

FROM 테이블

WHERE 조건(별칭X)

ORDER BY 컬럼명 ASC(DESC)

ASC: 오름차순//작은값부터 디폴트

DESC: 내림차순//큰값부터

 

ORDER BY 컬럼

--직원번호, 이름, 급여, 부서번호를 급여가 높은 순으로 출력, EMPLOYEE_ID가 높은 순으로
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY SALARY DESC, EMPLOYEE_ID DESC;

--입사일이 가장 최근인 직원 순으로 직원번호, 이름, 입사일을 출력하라ㅏ
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC;

--부서번호가 20, 50번 부서에서 근무하는 모든 사원들의 이름(FIRST_NAME), 부서 번호, 급여를 
--사원의 이름순(알파벳순)으로 출력하라
SELECT FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(20,50)
ORDER BY FIRST_NAME;

CREATE TABLE 테이블명

컬럼 자료형 NULL(DEFAULT)/NOT NULL 제약조건,

컬럼 자료형 NULL(DEFAULT)/NOT NULL 제약조건,

컬럼 자료형 NULL(DEFAULT)/NOT NULL 제약조건,...

 

기본 BYTE단위

CHAR	고정 길이 , 자리가 비면 공백으로 채움 우편번호, 주민등록 번호,...
VARCHAR2	가변 길이, 최대 크기:4000BYTE, 최소크기: 1BYTE(영), 3BYTE(한)//Char (3 Char):3글자 VARCHAR2(10): 가변 형식의 10자리 문자열 Hello->Hello
NUMBER NUMBER(p) NUMBER(p,s)	NUMBER(전체 자릿수(p, 정수), 소수점이하 자릿수(s, 실수))
BLOB	대용량의 바이너리 데이처를 저장하기 위한 데이터 타입 최대크기: 4GB
CLOB DATE	대용량의 텍스트 데이터를 저장하기 위한 데이터 타입 날짜 형식을 저장하기 위한 데이터 타입
TIMESTAMF	DATE 데이터 타입의 확장된 형태
ROWID	테이블 내 행의 고유 주소를 가지는 64진수 문자 타입

• 테이블 명과 칼럼명을 부여하기 위한 규칙

• 반드시 문자(A-Z,a-z)로 시작해야 하며, 컬럼명은 최대 30바이트
• A~Z까지의 대소문자와 0~9까지의 숫자, 특수기호는 (_, $, #)만 가능하다
• 오라클에서 사용되는 예약어나 다른 객체명과 중복하여 생성할 수 없다.
• 공백 허용하지 않는다
• 대소문자 구별이 없다.
• 소문자로 저장하려면 ''로 묶어 주어야 한다.
• 한 테이블에 사용 가능한 컬럼은 최대 255개까지이다.

기존 테이블 복사

기존 테미블과 동일한 구조와 내용을 갖는 테이블을 생성 //기존 테이블의 구조+ 데이터를 그대로 복사하여 새로운 테이블 생성

CREATE TABLE EMPLOYEES02

AS

SELECT * FROM EMPLOYEES;

 

복사본으로 연습 후 반영

 

ALTER TABLE로 테이블 구조 변경

-ALTER TABLE 명령어는 테이블에서 칼럼의 추가, 삭제 , 칼럼의 타입이나 길이를 변경할 때 사용

• ADD(컬럼명 자료형)절을 사용하여 새로운 칼럼을 추가한다.
• MODIFY(컬럼명 자료형)절을 사용하여 기존 칼럼을 수정
• DROP COLUMN 컬럼명절을 사용하여 기존 칼럼을 삭제

ALTER TABLE table_name

ADD(column_name data_type)

 

ALTER TABLE로 기존 칼럼 수정

ALTER TABLE 명령어에 MODIFY절을 사용하여 칼럼을 수정. (데이터 타입, 크기를 변경가능)

ALTER TABLE table_name

MODIFY(column_name data_type expr,...);

 

-해당 칼럼에 자료가 없는 경우

칼럼의 데이터 타입을 변경할 수 있다.

칼럼의 크기를 변경할 수 있다.

-해당 칼럼에 자료가 있는 경우

칼럼의 데이터 타입을 변경할 수 없다.

크기를 늘릴 수는 있지만 현재 가지고 있는 데이터 크기보다 작은 크기로 변경할 수 없다.

 

--이미 존재하는 EMP01 테이블에 입사일 칼럼(CREDATE)을 날짜형으로 추가
ALTER TABLE EMP01
ADD(CREDATE DATE);

DESC EMP01;

--직급을 최대 30자까지 입력할 수 있도록 크기 수정
ALTER TABLE EMP01
MODIFY(JOB VARCHAR2(30));

DESC EMP01;

 

ALTER TABLE로 기존 칼럼명 변경

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

 

ALTER TABLE로 기존 칼럼 삭제

ALTER TABLE table_name

DROP COLUMN column_name;

 

DROP TABLE로 테이블 구조 삭제

DROP TABLE table_name;

 

휴지통(recyclebin)

--휴지통 비우기
PURGE RECYCLEBIN;

 

--실수로 지운 테이블이라 삭제를 취소하려면 다음과 같은 명령으로 다시 복구하면 된다.
--flashback table table_name to before drop;

 

--새로운 이름으로 복원하는 방법
FLASHBACK TABLE EMP01 TO BEFORE DROP
RENAME TO EMP02;

 

6) 테이블의 모든 로우를 제거해 TRUNCATE 문

TRUNCATE table table_name; //이 작업은 절대 복원 불가능 완전삭제

 

• 테이블을 Truncate하면 테이블의 모든 행이 삭제 되고 사용된 공간이 해제된다.
• TRUNCATE TABLE은 DDL명령이므로 롤백 데이터가 생성되지 않는다.
• DELETE명령으로 데이터를 지우면 롤백 명령어로 복구 할 수 있지만 TRUNCATE로 데이터를 삭제하면 롤백을 할 수 없다
• 외래키가 참조중인 데이블은 TRUNCATE 할 수 없다.
• TRUNCATE 명령을 사용하면 삭제 트리거가 실행되지 않는다.