시퀀스
-
SEQUENCE
-
일렬번호를 생성하는 데이터베이스 객체
-
자동으로 증가하는 번호를 생성
-
기본키(PK)에서 주로 사용 - 인공키
-
NEXTVAL를 이용하면 새로운 번호가 생성됨 - 번호하나 뽑는거라고 생각하기
-
CURRVAL를 이용하면 현재 번호를 확인 - 번호가 몇 번까지 갔는지 확인
네추럴 키 : 회원이름처럼 원래 갖고 있는거 인공키(:추가로 구현해 주는 데이터) : 회원번호같은거 최초 1회는 NEXTVAL해야 CURRVAL사용가능
시퀀스 생성 형식
CREATE SEQUENCE 시퀀스_이름
START WITH 시작값 -- 생략하면 1이다 ,생성이후 유일하게 시퀀스에서 수정이 안됨
INCREMENT BY 증가값 -- 생략하면 1
MINVALUE 최소값 --시퀀스가 가질 수 있는 최솟값
MAXVALUE 최대값 -- 시퀀스가 가질 수 있는 최댓값
CACHE 사용유무 -- NOCHACHE 권장 기억장소에서 자기가 쓸 번호를 미리 가져오겠다는 개념. 중간에 번호가 뜸(?)
-- CACHE 설정시 메모리에 시퀀스 값을 미리 할당하고
-- NOCACHE 설정시 시퀀스값을 메로리에 할당하지 않음
CYCLE 사용유무 -- 생략하면 NOCYCLE 번호가 순환. 1~11 돌고 다시 1번가는거 -PK에서 사용한다면 NOCYCLE
-- CYCLE 설정시 최대값에 도달하면 최소값부터 다시 시작
-- NOCYCLE 설정시 최대값 생성 시 시퀀스 생성중지
번호가 맘에안들면 다 지우고 다시 생성행함
시퀀스 다시 만드는 방법 : 시퀀스 지우고 처음부터 다시 시작
마이너스로 가는 것도 가능함.
DQL
-
DATA QUERY LANGUAGE
-
데이터 질의어
-
테이블의 데이터를 조회/검색
-
데이터베이스에 변화가 없으므로 COMMIT 없음
(트랜잭션의 대상이 아님) -
형식
SELECT 칼럼1, 칼럼 2, .... FROM 테이블 [WHERE 조건] 생략가능. [GROP BY 구룹화] [HAVING 그룹화_조건식] [ORDER BY 정렬] - 오름차순, 내림차순 -
실행 순서
⑤ SELECT 칼럼 ① FROM 테이블 ② WHERE 조건식 ③ GROP BY 그룹화 ④ HAVING 그룹화_조건식 ⑥ ORDER BY 정렬기준 (*정해진순서대로 읽음)(*2~3 생략가능)
-- ♣1. 사원테이블에서 사원 이름 조회하기
SELECT NAME
FROM EMPLOYEE;
--SELECT의 결과는 TABLE로 나옴
-- 1)테이블 오너(Owner) 명시 가능
SELECT NAME
FROM SCOTT.EMPLOYEE;
-- 2)칼럼에 테이블 명시
SELECT EMPLOYEE.NAME
FROM EMPLOYEE;
-- 3)테이블에 별명 지정 (자주쓸예정)
SELECT EMP.NAME
FROM EMPLOYEE EMP; -- 별명 EMP 지정
-- 4) 칼럼에 별명(ALIAS)지정 (자주쓸예정)
SELECT NAME AS 사원명 -- 별명 사원명 지정
FROM EMPLOYEE;
----------1~4번까지 동일한 쿼리문
-- ♣2. 사원테이블의 모든 칼럼 조회하기
-- 모든 칼럼 : (모든칼럼을 의미하는 에스터리크스?)
-- 중요 : 실무에서 사용금지 -- 감사에 걸림.또 성능을 떨어트림
SELECT *
FROM EMPLOYEE;
-- 모든 칼럼이 필요하면 모두 명시
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE;
중복제거 : DISTINCT
-- ♣3. 부서 테이블에서 지역명 조회하기
-- 단, 동일한 지역은 한번만 조회
SELECT DISTINCT LOCATION
FROM DEPARTMENT;
SELECT DISTINCT DEPT_NAME, LOCATION
FROM DEPARTMENT;
/ WHERE절 활용하기 /
-- ♣4. 사원테이블 에서 직급이 '과장' 인 사원 조회
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE POSITION = '과장';
-- ♣5. 사원 테이블에서 급여가 2000000~5000000인 사원 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 2000000
AND SALARY <= 5000000;
-- ♣6.사원 테이블에서 소속부서가 1,2인 사원 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE DEPART = 1
OR DEPART = 2;
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE DEPART IN(1, 2);
NULL유무
NULL이다(IS NULL), NULL(IS NOT NULL)이 아니다~ (★별도로 학습하기)
-- ♣7. 사원테이블에서 성별이 없는 사원 조회
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE GENDER IS NULL;
-- 1) 만능문자(wilf card)
-- ① % : 모든 문자, 글자 수 제한 없음
-- ② _ : 모든 문자, 한 글자로 제한
-- 2) 예시
-- ① 김으로 시작하는 이름 찾기 : 김%
-- ② 김으로 끝나는 이름 찾기 : %김
-- ③김을 포함하는 이름 찾기 : %김%
-- 3) 만능문자 연산자
-- LIKE, NOT LIKE
-- 김으로 시작하는 이름 찾기 : 김%
-- ♣8. 사원테이블에서 김씨 조회
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE NAME LIKE '김%'; --(★중중요요)
-- ♣9. 사원 테이블에서 사원번호가 1로 시작하는 사람 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE EMP_NO LIKE '1%'; --(EMP는 타입이 NUMBER임 지금 넘버랑 텍스트랑 조회중. 넘버랑 텍스트 섞여있으면 오라클은 스스로 캐스팅하여 처리한다.
ORDER BY 절
-- ASC : 오름차순 정렬, 생략 가능
-- DESC : 내침차순 정렬
-- ♣10. 사원 테이블에서 사원명의 가나다순으로 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
ORDER BY NAME ASC; -- 오름차순 △ // 가나다순은 오름차순 ASC 생략가능,,
-- ♣11. 사원 테이블에서 급여가 높은 순으로 사원을 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC; --내림차순▽ (★) 헷갈릴 것 같음
-- ♣12. 사원 테이블에서 성별의 가나다순으로 조회하기
-- 오름차순 정렬할 때 NULL값은 마지막에 배치
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
ORDER BY GENDER;
-- ♣13. 사원 테이블에서 먼저 고용된 순으로 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
ORDER BY HIRE_DATE;
-- ♣14. 사원 테이블에서 소속부서의 오름차순 정렬로 조회하되, 같은 소속부서내에서는 먼저 고용된 순으로 조회한다.
-- 1차 정렬기준 : 소속부서
-- 2차 정렬기준 : 고용일자
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
ORDER BY DEPART ASC, HIRE_DATE ASC;
WHERE절과 ORDER BY절 함께 사용
: WHERE절 먼저, ORDER BY절 나중
-- ♣15. 사원 테이블에서 급여가 500만원 이상인 사원들을 고용된순으로 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 5000000
ORDER BY HIRE_DATE;
SELECT문의 처리 순서
.
●별명주고 별명사용
FROM EMPLOYEE
WHERE SALARY <=500000
ORDER BY HD;
.
●안되는 이유 : 처리 순서에 의해서 된다.안된다.결정됨.
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY AS S
FROM EMPLOYEE
WHERE S <=500000
ORDER BY HIRE_DATE;
타입변환
/*
DUAL 테이블
1. DUMMY 칼럼에 'X' 값을 하나 가지고 있는 테이블
2. 아무 의미 없는 테이블
3. 오라클에서는 FROM절이 필수이기 때문에,
테이블이 필요 없는 조회문에서 DUAL 테이블을 사용함*/
DESCRIBE DUAL;
SELECT DUMMY
FROM DUAL;
타입 변환 함수
1. TO_NUMBER('문자열') : 문자열 형식의 숫자를 숫자 형식으로 변환
2. TO_CHAR(데이터, ['형식']): 지정된 데이터(주로 숫자나 날짜)를 형식에 맞는 문자열로 변환
3. TO_DATE('문자열', '형식'): 지정된 문자열을 날짜 형식으로 변환
-- 1. 숫자로 변환(TO_NUMBER)
SELECT '100', TO_NUMBER('100')
FROM DUAL;
SELECT '1.5', TO_NUMBER('1.5')
FROM DUAL;
-- 숫자와 '문자' 연산은 오라클에 의해서
-- 숫자와 숫자 연산으로 수정된 뒤 처리됨
-- '문자' -> TO_NUMBER('문자') 방식으로 자동으로 처리함
SELECT 1 + '1' -- SELECT 1 + TO_NUMBER('1')
FROM DUAL;
-- '문자'와 '문자' 연산도 모두 숫자로 바꿔서 처리
SELECT '1' + '1' -- SELECT TO_NUMBER('1') + TO_NUMBER('1')
FROM DUAL;
-- 2. 문자로 변환(TO_CHAR)
-- 1) 숫자 -> 문자로 변환
SELECT
TO_CHAR(1234) -- '1234'
, TO_CHAR(1234, '999999') -- ' 1234'
, TO_CHAR(1234, '000000') -- '001234'
, TO_CHAR(1234, '9,999') -- '1,234'
, TO_CHAR(12345, '9,999') -- ###### (4자리로 지정하였으나 값은 5자리이기 때문에 표시할 수 없음)
, TO_CHAR(12345, '99,999') -- '12,345'
, TO_CHAR(1.4, '9') -- '1', 형식은 정수 1자리 표기(소수 이하 반올림)
, TO_CHAR(1.5, '9') -- '2', 형식은 정수 1자리 표기(소수 이하 반올림)
, TO_CHAR(0.123, '0.00') -- '0.12', 소수 이하 2자리 표기(반올림)
, TO_CHAR(0.129, '0.00') -- '0.13', 소수 이하 2자리 표기(반올림)
FROM
DUAL;
-- 2) 날짜 -> 문자로 변환
-- 현재 날짜와 시간
-- DATE 타입의 SYSDATE
-- TIMESTAMP 타입의 SYSTIMESTAMP
SELECT
SYSDATE -- YY/MM/DD 형식으로 표시하지만 시간 데이터도 가지고 있음
, SYSTIMESTAMP
FROM
DUAL;
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
, TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM
DUAL;
-- 3. 날짜로 변환(TO_DATE)
-- '05/06/07' 날짜는 언제인가? 알려주기 전에는 모름
-- 지정된 형식으로 해석
-- 예시1) 'YY/MM/DD' : 05년 06월 07일
-- 예시2) 'MM/DD/YY' : 07년 05월 06일
-- 어떤 날짜를 어떻게 해석해야 하는지 알려주는 함수
SELECT
TO_DATE('05/06/07', 'YY/MM/DD')
, TO_DATE('05/06/07', 'MM/DD/YY')
FROM
DUAL;
-- 사원 테이블에서 90/01/01 ~ 99/12/31 사이에 입사한 사원 조회하기
SELECT EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE TO_DATE(HIRE_DATE, 'YY/MM/DD') BETWEEN TO_DATE('90/01/01', 'YY/MM/DD') AND TO_DATE('31/12/99', 'DD/MM/YY');
CREATE TABLE SAMPLE(
DT1 DATE,
DT2 TIMESTAMP,
DT3 VARCHAR2(10 BYTE) -- YYYY-MM-DD
);
-- DI1, DT2, DT3 칼럼에 현재 날짜 넣기
INSERT INTO
SAMPLE(DT1, DT2, DT3)
VALUES(SYSDATE, SYSTIMESTAMP, TO_CHAR(SYSDATE, 'YYYY-MM-DD'));
SELECT DT1,DT2,DT3 FROM SAMPLE;
-- 날짜 비교 연산은 TO_DATE 함수가 필요
