Jam's story
[오라클]days03 본문
-- SCOTT 계정 접속 --
-- 문제) emp 테이블에서 comm 이 null 인 모든 사원 정보를 조회(출력)하는 쿼리 작성하세요.
SELECT *
FROM emp
WHERE comm IS NULL;
-------------------------------------------------------------------------------------
--1. 용어정리
-- ㄱ. Data : 정보(자료)
-- ㄴ. DataBase : Data + Base( 저장소 ) , 데이터 집합
-- ㄷ. DBMS : 데이터베이스 관리 시스템, 소프트웨어 예) 오피스의 엑셀 오라클( Oracle )
-- DBMS 중에 하나 : [오라클], MySQL , MS SQL 등등
-- ㄹ. DBA : DB 관리자 [SYS], SYSTEM ss123$
-- 계정 생성 등등 + 권한 필요
-- 테이블 생성 + 권한 필요
-- [SCOTT] scott.sql 검색 -> emp, dept, salgrade, bonus 테이블 생성
-- GRANT 권한,,,롤 TO 계정 또는 롤
-- ㅁ. 롤(ROLE) :
권한 부여, 제거 DCL - GRANT, REVOKE 문
롤을 회수(제거)
REVOKE 롤이름
FROM 사용자명 또는 롤이름 또는 PUBLIC;
ㄱ. 롤 생성 ⇒ ㄴ. 롤에 권한 부여 ⇒ ㄷ. 사용자에게 롤 부여
【형식】
CREATE ROLE 롤이름
[NOT IDENTFIED 또는 IDENTIFIED
{BY PASSWORD 또는 EXTERNALLY 또는 GLOBALLY 또는 USING 패키지}];
다수 사용자 <- 부여, 제거 -> 다양한 권한 효율적으로 관리( 권한 부여, 제거 )
영업부역할(ROLE) <- 50개의 권한 부여
CREATE USER 계정명
ALTER USER 계정명 INDENTIFIED BY NEW비밀번호 ACCOUNT UNLOCK;
DROP USER 계정명 CASCADE;
CREATE TABLE 테이블명
ALTER TABLE 테이블명 ~
DROP TABLE 테이블명
CREATE ROLE 롤명;
ALTER ROLE 롤명 ~
DROP ROLE 롤명
생산부역할(ROLE) <- 100개의 권한 부여
신입사원역할(ROLE) <- 권한1.... 권한30
GRANT 50개의 권한 TO 롤명
예) 신입사원 <- 신입사원 30개의 권한
50명
A <- 신입사원역할(ROLE)
B <- 신입사원역할(ROLE), 영업부역할(ROLE)
X <- 신입사원역할(ROLE), 생산부역할(ROLE)
B <- 신입사원역할(ROLE), 영업부역할(ROLE)
A50 <- 권한1.... 권한30
select *
from role_sys_privs
where role='RESOURCE' ;
SCOTT : RESOURCE, CONNECT 롤 부여
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE
문제1) SCOTT 계정이 소유하고 있는 롤을 확인하고
CONNECT 롤 제거
CONNECT 롤 부여
1)
SELECT *
FROM dba_roles;
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
79행, 18열에서 오류 발생
FROM user_role_privs;
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
FROM role_sys_privs; -- 권한 2가지 : 시스템 권한, 객체 권한
2)
REVOKE CONNECT FROM scott;
3)
GRANT CONNECT TO scott;
ORA-01932: [ ADMIN option ] not granted for role 'CONNECT'
01932. 00000 - "ADMIN option not granted for role '%s'"
*Cause: The operation requires the admin option on the role.
*Action: Obtain the grant option and re-try.
-- ㅂ. SID( 전역 데이터베이스 이름 ) XE
11:07 수업 시작~
설치된 오라클 DB의 고유한 이름
오라클 무료 버전을 설치하면 자동으로 SID=> XE
" 은 1개 만 설치 가능.
-- ㅅ. 데이터 모델
-- ㅈ. R+DBMS
컴퓨터에 데이터를 저장하는 방식을 정의해 놓은 개념 모델.
관계형 데이터 모델 + DBMS = RDBMS 오라클
-- ㅇ. 스키마(Schema)
1) DB에서 어떤 목적을 위하여 필요한 여러 개로 구성된 테이블들의 집합을 Schema라 한다.
2) DATABASE SCHEMA ( DB 스키마 )
- scott 계정 생성 -> 모든 OBJECT 생성 모음 -> 스키마
- 특정 USER와 관련된 OBJECT( 테이블 )의 모음
- emp 테이블 ( 객체 ) 생성
FROM 스키마.테이블명
FROM scott.emp;
3) 용어 정리
인스턴스(instance) : 오라클서버 -> 시작(startup) ~~ 종료(shutdown)
세션(session) : 사용자 로그인-> 로그아웃
스키마(schema) : 특정 USER와 관련된 OBJECT( 테이블 )의 모음
--2. 설치된 [오라클을 삭제]하는 절차를 [검색]해서 상세히 적으세요. ***
ㄱ. 서비스앱( services.msc ) -> 오라클 관련 서비스 중지
ㄴ. uninstall.exe / 프로그램 추가 및 삭제 - 제거
ㄷ. 탐색기 - 오라클 폴더( oraclexe ) 삭제
ㄹ. 레지스트리편집기( regedit.exe ) -> 오라클 관련 레지스트리 삭제
--3. SYS 계정으로 접속하여 모든 사용자 정보를 조회하는 쿼리(SQL)을 작성하세요.
SELECT *
FROM dba_tables;
FROM all_tables; -- SCOTT 계정이 소유하고 있는 테이블 + 권한 부여 받아서 사용할 수 있는 테이블 View
FROM tabs;
FROM user_tables; -- SCOTT 계정이 소유하고 있는 테이블 정보 View
FROM all_users;
FROM user_users;
FROM dba_users;
-- ORA-00942: table or view does not exist
--4. SCOTT 계정 + 소유 객체(테이블) 를 삭제하는 쿼리(SQL)을 작성하세요.
ㄱ. 계정 생성
ㄴ. 계정 수정
ㄷ. 계정 삭제
--5. 위의 4번에 의해서
-- SCOTT 계정이 삭제되었으면 SCOTT 계정을 tiger 비밀번호로 생성하는 쿼리(SQL)을 작성하세요.
--6. 오라클이 제공하는 기본적인 롤(role)의 종류를 적으시고,
--6-2. SCOTT 계정에게 권한을 부여하는 쿼리(SQL)을 작성하세요.
--7-1. SCOTT 계정에게 scott.sql 파일을 찾아서 emp, dept, bonus, salgrade 테이블을 생성 및 데이터 추가한 과정을 작성하세요.
--7-2. 각 테이블에 어떤 정보를 저장하는지 컬럼에 대해 정보( 컬럼명, 자료형 )를 설명하세요.
-- ㄱ. emp
-- ㄴ. dept
-- ㄷ. bonus
-- ㄹ. salgrade
SELECT *
FROM tabs;
DESC emp;
--8. SCOTT 계정이 소유하고 잇는 모든 테이블을 조회하는 쿼리(SQL)을 작성하세요.
SELECT *
FROM user_users;
FROM all_users;
--9. SQL*Plus 를 사용하여 SYS로 접속하여 접속한 사용자 확인하고, 모든 사용자 정보를 조회하고
-- 종료하는 명령문을 작성하세요.
REM 한줄 주석처리
-- 한줄 주석처리
/* 블럭 주석처리
>_
>sqlplus /?
<logon> is: {<username>[/<password>][@<connect_identifier>] | / }
[AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]
*/
--10. 관계형 데이터 모델의 핵심 구성 요소
ㄱ. 개체
ㄴ. 속성
ㄷ. 관계
--11. Oracle SQL Developer 에서 쿼리(SQL)을 실행하는 방법을 모두 적으세요.
ㄱ. F9
ㄴ. F5
ㄷ. Ctrl + Enter
--12. 오라클 주석처리 방법 3가지를 적으세요.
ㄱ. --
ㄴ. REM
ㄷ. /* */
--13. 자료 사전( Data [Dictionary] ) 이란?
메타 데이터( META DATA ) == Data Dictionary의 정보
ㄱ. Data Dictionary = 테이블 + 뷰(View) 의 집합
ㄴ. 데이터베이스의 정보를 제공하는 역할.
예) scott 소유하고 있는 테이블 정보
FROM user_users; // Data Dictionary . 뷰
ㄷ. DB 생성시 SYS 계정 생성 -> SYS 스키마 생성 내부에 자료 사전 생성
ㄹ.
SCOTT 계정
emp 테이블 생성
12:01 수업시작~
FROM user_tables; // 자료사전 = 뷰 정보
dba_
all_
user_
V$ DB의 성능분석/통계 정보 제공하는 뷰
--14. SQL 이란 ? 서버 <-질의/응답> 클라이언트
구조화된 질의 언어
SQL
--15. SQL의 종류에 대해 상세히 적으세요.
ㄱ. DQL select
ㄴ. DDL create / alter / drop
ㄷ. DML insert / update / delete + COMMIT, ROLLBACK
ㄹ. DCL grant / revoke
ㅁ. TCL COMMIT, ROLLBACK, savepoint
--16. select 문의 7 개의 절과 처리 순서에 대해서 적으세요. *****
WITH 절 1
SELECT 절 6
FROM 절 2
WHERE 절 3
GROUP BY 절 4
HAVING 절 5
ORDER BY 절 7
--17. emp 테이블의 테이블 구조(컬럼정보)를 확인하는 쿼리를 작성하세요.
DESC emp
NLS
날짜 형식 : RR/MM/DD
YY/MM/DD
*** [ RR 년도 / YY 년도 기호 차이점 ]
--18. employees 테이블에서 아래와 같이 출력되도록 쿼리 작성하세요.
FIRST_NAME LAST_NAME NAME
-------------------- ------------------------- ----------------------------------------------
Samuel McCain Samuel McCain
Allan McEwen Allan McEwen
Irene Mikkilineni Irene Mikkilineni
Kevin Mourgos Kevin Mourgos
Julia Nayer Julia Nayer
SELECT FIRST_NAME, LAST_NAME
, FIRST_NAME || ' ' || LAST_NAME AS "NAME"
, FIRST_NAME || ' ' || LAST_NAME "NAME"
, FIRST_NAME || ' ' || LAST_NAME NAME
FROM employees;
ORA-00942: table or view does not exist
--19. 아래 뷰(View)에 대한 설명을 적으세요.
-- ㄱ. dba_tables
-- ㄴ. all_tables
-- ㄷ. user_tables == tabs
--20. HR 계정의 생성 시기와 [잠금상태]를 확인하는 쿼리를 작성하세요.
SELECT *
FROM dba_users;
FROM all_users; -- HR 43 14/05/29
FROM user_users;
DESC dba_users;
SELECT username, account_status
FROM dba_users
WHERE username = 'HR';
--21. emp 테이블에서 잡, 사원번호, 이름, 입사일자를 조회하는 쿼리를 작성하세요.
SELECT job, empno, ename, hiredate
FROM emp;
--22. emp 테이블에서 아래와 같은 조회 결과가 나오도록 쿼리를 작성하세요.
( sal + comm = pay )
-- ORA-00904: "PAY": invalid identifier
-- COMM 컬럼의 값을 NULL 처리를 했더라.
-- NVL( ? , ? ) 커미션이 NULL 인경우에는 0 값으로 처리하겠다.
-- NVL2( ? , ? , ? )
SELECT empno, ename, sal
--, NVL( comm , 0 ) COMM, sal + NVL(comm, 0) pay
, NVL2( comm , comm , 0 ) COMM, sal + NVL2(comm, comm , 0) pay
FROM emp;
EMPNO ENAME SAL COMM PAY
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 0 800
7499 ALLEN 1600 300 1900
7521 WARD 1250 500 1750
7566 JONES 2975 0 2975
7654 MARTIN 1250 1400 2650
7698 BLAKE 2850 0 2850
7782 CLARK 2450 0 2450
7839 KING 5000 0 5000
7844 TURNER 1500 0 1500
7900 JAMES 950 0 950
7902 FORD 3000 0 3000
EMPNO ENAME SAL COMM PAY
---------- ---------- ---------- ---------- ----------
7934 MILLER 1300 0 1300
12개 행이 선택되었습니다.
--23. emp테이블에서
-- 각 부서별로 오름차순 1차 정렬하고 급여(PAY)별로 2차 내림차순 정렬해서 조회하는 쿼리를 작성하세요.
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp
ORDER BY deptno ASC, pay DESC;
-- ORDER BY deptno ASC, sal + NVL(comm, 0) DESC
--24. 계정을 생성하는 쿼리 형식에 대해 적으세요.
--25. 생성된 계정의 비밀번호 , 잠금을 해제하는 쿼리 형식에 대해서 적으세요.
--26. DB에 로그인할 수 있는 권한을 부여하는 쿼리 형식에 대해서 적으세요.
--27. 특정포트( 1521 Port )를 방화벽 해제하는 방법에 대해서 상세히 과정을 적으세요.
--28. SQL*PLUS 툴을 사용해서
-- ㄱ. SYS로 로그인하는 방법(형식)을 적으세요
-- ㄴ. SCOTT로 로그인하는 방법(형식)을 적으세요
--29. SQL의 작성방법
--30. 아래 에러 메시지의 의미를 적으세요.
ㄱ. ORA-00942: table or view does not exist FROM 테이블명 또는 뷰명
enp 코딩 X
접근 권한 X
ㄴ. ORA-00904: "SCOTT": invalid identifier
식별자
ㄷ. ORA-00936: missing expression 표현식( 수식 ) 이 잘못된 경우
ㄹ. ORA-00933: SQL command not properly ended
WHERE score >= 40 ||
SQL ? 구조화된 질의 언어
PL/SQL = SQL + (절차적인 언어(Procedural Language)) 확장
절차적 언어 문법
if
for
제어문
변수
[ SQL 작성 방법 ]
1. 대소문자 구분 X
select *
from emp;
Select *
From emp;
SELECT *
FROM EMP;
WHERE username = 'hr';
WHERE username = 'HR';
2) sqlplus
SQL> select
2 *
3 from
4 emp
5 ;
3)
SELECT empno, ename , deptno
FROM emp;
4) 절 별로 라인 구분
5) 키워드 대문자, 그외 문자( 테이블명, 컬럼명 등등 ) 소문자 - 권장.
6) 탭, 공백 사용 - 권장
-- 31. emp 테이블에서 부서번호가 10번이고, 잡이 CLERK 인 사원의 정보를 조회하는 쿼리 작성.
SELECT *
FROM emp
WHERE depnto = 10 AND job = 'CLERK';
-- 31-2. emp 테이블에서 잡이 CLERK 이고, 부서번호가 10번이 아닌 사원의 정보를 조회하는 쿼리 작성.
SELECT *
FROM emp
WHERE NOT( depnto = 10 ) AND job = 'CLERK';
WHERE depnto <> 10 AND job = 'CLERK';
WHERE depnto ^= 10 AND job = 'CLERK';
WHERE depnto != 10 AND job = 'CLERK';
-- 32. 오라클의 null의 의미 와 null 처리 함수에 대해서 설명하세요 .
ㄱ. null 의미? 미확인된 값
예) 사람의 몸무게 null
ㄴ. null 처리 함수 2가지 종류와 형식을 적고 설명하세요 .
NVL() , NVL2()
-- 33. emp 테이블에서 부서번호가 30번이고, 커미션이 null인 사원의 정보를 조회하는 쿼리 작성.
( ㄱ. deptno, ename, sal, comm, pay 컬럼 출력, pay= sal+comm )
( ㄴ. comm이 null 인 경우는 0으로 대체해서 처리 )
( ㄷ. pay 가 많은 순으로 정렬 )
SELECT deptno, ename, sal, comm, sal + NVL( comm, 0 ) pay
FROM emp
WHERE deptno = 30 AND comm is not null;
WHERE deptno = 30 AND comm is null;
WHERE deptno = 30 AND comm = null;
[NOT] IN ( list )
[NOT] BETWEEN a AND b
IS [NOT] NULL
null
1. 미확인된 값
2. '' 0 다른 값
3. null 유무를 확인할 때는 = 비교연산자를 사용하지 않고
SQL 연산자 중에 is null, is not null 연산자를 사용한다.
-- 34. insa 테이블에서 수도권 출신의 사원 정보를 모두 조회하는 쿼리 작성 ( 오름차순 정렬 )
ㄱ. OR 연산자 사용해서 풀기
SELECT *
FROM insa
WHERE city = '서울' OR city = '경기' OR city = '인천'
ORDER BY city ASC ;
ㄴ. IN ( LIST ) SQL 연산자 사용해서 풀기
SELECT *
FROM insa
WHERE city IN ( '서울' , '경기' , '인천' )
ORDER BY city ASC ;
-- 35. insa 테이블에서 수도권 출신이 아닌 사원 정보를 모두 조회하는 쿼리 작성 ( 오름차순 정렬 )
ㄱ. AND 연산자 사용해서 풀기
SELECT *
FROM insa
WHERE city != '서울' AND city != '경기' AND city != '인천'
ORDER BY city ASC ;
ㄴ. NOT IN ( LIST ) SQL 연산자 사용해서 풀기
SELECT *
FROM insa
WHERE city NOT IN ( '서울' , '경기' , '인천' )
ORDER BY city ASC ;
ㄷ. OR, NOT 논리 연산자 사용해서 풀기
SELECT *
FROM insa
WHERE NOT ( city = '서울' OR city = '경기' OR city = '인천' )
ORDER BY city ASC ;
-- 36. 오라클 비교 연산자를 적으세요.
ㄱ. 같다 : =
ㄴ. 다르다 : != <> ^=
-- 37. emp 테이블에서 pay(sal+comm)가 1000 이상~ 2000 이하 받는 30부서원들만 조회하는 쿼리 작성
조건 : ㄱ. pay 기준으로 오름차순 정렬 --ename을 기준으로 오름차순 정렬해서 출력(조회)
ㄴ. comm 이 null은 0으로 처리 ( nvl () )
-- SQL 실행 순서 *****
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp
WHERE sal + NVL(comm, 0) BETWEEN 1000 AND 2000 AND deptno = 30
--WHERE pay BETWEEN 1000 AND 2000 AND deptno = 30 -- pay를 인식하지 못해서 에러 발생.
ORDER BY pay ASC;
ORA-00904: "PAY": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
477행, 7열에서 오류 발생
-- 두 번째 방법으로 풀이 : with 절 사용.
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp
WHERE deptno = 30;
30 ALLEN 1900
30 WARD 1750
30 MARTIN 2650
30 BLAKE 2850
30 TURNER 1500
30 JAMES 950
위의 결과물을 가지고 WITH 절 사용.
WHERE pay BETWEEN 1000 AND 2000;
--------------------------------------------------------------------
WITH temp AS (
-- 서브쿼리( subquery )
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp
WHERE deptno = 30
)
SELECT t.*
FROM temp t -- 테이블의 별칭
WHERE t.pay BETWEEN 1000 AND 2000;
--------------------------------------------------------------------
-- 세 번째 방법 - 인라인뷰( inline view )
-- 인라인뷰( inline view ) ? FROM 절 안에 있는 서브쿼리를 "인라인뷰" 라고 한다.
SELECT t.*
FROM (
-- 서브쿼리( subquery )
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp
WHERE deptno = 30
) t
WHERE t.pay BETWEEN 1000 AND 2000;
[질문]
강사님
아까 첫번째 방법에서
SELECT 절 사용할 수 있는 키워드 : AS, DISTINCT, ALL
WHERE sal+nvl(comm,0) AS PAY BETWEEN 1000 AND 2000 AND deptno = 30 ;
이렇게 하면 오류나는데 그럼 WHERE에는 별칭들어가면 안되는 건가요?
-- 38. emp 테이블에서 1981년도에 입사한 사원들만 조회하는 쿼리 작성.
LIKE SQL 연산자
REGEXP_LIKE() 함수
-- '80/12/17'
SELECT hiredate, ename
FROM emp
WHERE hiredate LIKE '81%'
--WHERE TO_CHAR( hiredate, 'YYYY' ) = '1981'
--WHERE SUBSTR( hiredate , 0 , 2 ) = '81'
--WHERE hiredate BETWEEN '1981.1.1' AND '1982.1.1'
--WHERE hiredate BETWEEN '1981.1.1' AND '1981.12.31'
ORDER BY hiredate;
-- '80/12/17'
SELECT hiredate
,SUBSTR( hiredate , 0 , 2 ) YY
,SUBSTR( hiredate , 1 , 2 ) YY_1
-- , substr( char, position, length )
-- , substr( char, position [, length] ) 문자열 끝까지 잘르겠다.
FROM emp;
-- 3:05 수업시작~
-- 오라클 날짜에서 년/월/일 얻어오는 방법 ?
TO_CHAR( 날짜형 ) 함수는 날짜형 인자값을 내가 원하는 값(년도, 월, 일, 요일 등등)을 문자열로 반환하는 함수
[ 오라헬프 ] YY와 RR의 차이점:
SELECT hiredate
, TO_CHAR( hiredate, 'RR' )
, TO_CHAR( hiredate, 'YY' )
, TO_CHAR( hiredate, 'RRRR' )
-- 두 함수의 차이점 : '1981' 문자열 1981 숫자
, TO_CHAR( hiredate, 'YYYY' )
, EXTRACT( YEAR FROM hiredate ) "YEAR"
, TO_CHAR( hiredate, 'YEAR' )
, TO_CHAR( hiredate, 'SYYYY' )
FROM emp;
DESC emp;
HIREDATE DATE
Oracle : DATE '1998.01.12'
JAVA : 날짜형 Date, Calendar, LocalDate, LocalTime, LocalDateTime
year 년도만 얻어올 수 있었어요..
Date d = new Date();
int year = d.getYear() + 1900
c.get(Calendar.YEAR)
문제) insa 테이블에서
주민등록번호로 부터
앞자리 6자리 출력,
뒷자리 7자리 출력,
년도 2자리 출력,
월
일
주민번호 마지막 검증 1자리 출력
하는 쿼리를 작성하세요.
771212-1******
SELECT name, ssn
, SUBSTR( ssn, 0 , 8 ) || '******' RRN
, CONCAT( SUBSTR( ssn, 0 , 8 ) , '******') RRN
-- , SUBSTR( ssn, 0 , 6 ) RRN6
-- , SUBSTR( ssn, 1 , 6 ) RRN6
-- , SUBSTR( ssn, 8 , 7 ) RRN7
-- , SUBSTR( ssn, -7 ) RRN7
-- , SUBSTR( ssn, 0 , 2 ) YY
-- , SUBSTR( ssn, 3 , 2 ) MM
-- , SUBSTR( ssn, 5 , 2 ) DD
-- , SUBSTR( ssn, 14 , 1 ) RRN14
-- , SUBSTR( ssn, 14 ) RRN14
-- , SUBSTR( ssn, -1, 1 ) RRN14
FROM insa;
-- 39. emp 테이블에서 직속상사(mgr)가 없는 사원의 정보를 조회
SELECT empno, ename, mgr
FROM emp
WHERE mgr IS NULL; -- SQL 연산자 사용해야 된다. ***
WHERE mgr = null ;
-- 40. emp테이블에서 각 부서별로 오름차순 1차 정렬하고 급여(PAY)별로 2차 내림차순 정렬해서 조회하는 쿼리 작성
--41. Alias 를 작성하는 3가지 방법을 적으세요.
SELECT deptno, ename
, sal + comm (ㄱ) AS "PAY"
, sal + comm (ㄴ) AS PAY
, sal + comm (ㄷ) PAY
FROM emp;
--42. 오라클의 논리 연산자를 적으세요.
ㄱ. && AND
ㄴ. || OR
ㄷ. ! NOT
-- 43. 어제 배운 오라클의 SQL 연산자를 적으세요.
ㄱ. NOT IN ()
ㄴ. NOT BETWEEN a AND b
ㄷ. IS NULL , IS NOT NULL
ANY, SOME, ALL SQL연산자는 WHERE 조건절의 서브쿼리를 사용할 때 쓰이는 연산자이다.
WITH temp AS
(
서브쿼리
)
INLINE VIEW( 인라인뷰 )
FROM (
서브쿼리
)
WHERE SOME, ANY, ALL + (서브쿼리)
-------------------------------------------------------------------------------------
-- LIKE SQL 연산자 설명 --
4:02 수업시작~
-- Java char '' / String ""
-- Oracle ''
-- 문제) insa 테이블에서 성이 "김"씨 인 사원 정보 조회.
-- Java : name.startsWith("김") / endsWith()
SELECT name
-- , SUBSTR( name , 1, 1 )
, ibsadate
FROM insa
WHERE name LIKE '_김_' ;
WHERE name LIKE '_김%' ;
WHERE name LIKE '%김' ;
WHERE name LIKE '%김%' ; -- 이름 속에 '김' 한 문자를 포함하느냐 ?
WHERE name LIKE '김_' ;
WHERE name LIKE '김%' ;
-- WHERE SUBSTR( name , 1, 1 ) = '김';
LIKE 연산자 기호 : 와일드카드( % _ ) wild card
정규표현식 기호 : * _ {1} 반복횟수 0~ 여러번 반복
[ 설명 추후 ] wildcard를 일반 문자처럼 쓰고 싶은 경우에는 ESCAPE 옵션을 사용
-- REGEXP_LIKE() 함수 : 정규표현식을 사용하는 LIKE() 함수
【형식】
regexp_like ( search_string , pattern [,match_option])
[,match_option]
i 대소문자 구분 없음
c 대소문자 구분 있음
n period(.)를 허용함
m source string이 여러 줄인 경우(multiple lines)
x whitespace character(공백문자) 무시
-- 문제) 김 씨 사원
-- 문제) 김씨 또는 이씨 사원 출력.
-- 1) LIKE
-- 2) REGEXP_LIKE()
SELECT name, ssn
FROM insa
-- 1) LIKE 연산자 사용
-- WHERE name LIKE '김%' OR name LIKE '이%'
-- 2) REGEXP_LIKE() 함수 사용
-- WHERE REGEXP_LIKE( name, '^김' ) OR REGEXP_LIKE( name, '^이' )
-- WHERE REGEXP_LIKE( name, '^[김이]' )
WHERE REGEXP_LIKE( name, '^(김|이)' )
ORDER BY name ASC;
WHERE REGEXP_LIKE( name, '남$' ) ;
WHERE REGEXP_LIKE( name, '^김' ) ;
-- 문제)emp 테이블에서 이름(ename) 속에 'la' 문자열을 포함하는 사원 정보를 출력.
-- 2) REGEXP_LIKE()
SELECT ename
FROM emp
-- 1) LIKE
-- WHERE ename LIKE '%LA%';
-- JAVA 문자열을 대문자로 변환 메서드 : TOUPPERCASE()
-- ORACLE
-- WHERE ename LIKE '%' || UPPER( 'la' ) || '%';
-- 2) REGEXP_LIKE()
WHERE REGEXP_LIKE( ename , 'la' , 'i' ) ; -- i 대소문자 구분 X
WHERE REGEXP_LIKE( ename , UPPER('la') ) ;
WHERE REGEXP_LIKE( ename , 'LA' ) ;
WHERE REGEXP_LIKE( ename , '[a-zA-Z]*LA[a-zA-Z]*' ) ;
WHERE REGEXP_LIKE( ename , 'LA$' ) ;
WHERE REGEXP_LIKE( ename , '^LA' ) ;
-- 문제) insa 테이블에서 성이 김씨, 이씨는 제외한 모든 사원 정보 조회(출력)
-- 1) LIKE 연산자.
-- 2) REGEXP_LIKE() 함수
SELECT name, ssn
FROM insa
-- 1) [NOT] LIKE 연산자 사용
-- WHERE NOT ( name LIKE '김%' OR name LIKE '이%' )
-- WHERE name NOT LIKE '김%' AND name NOT LIKE '이%'
-- 2) [NOT] REGEXP_LIKE() 함수 사용
WHERE REGEXP_LIKE( name , '^[^김이]' )
-- WHERE NOT REGEXP_LIKE( name , '^[김이]' )
--WHERE NOT REGEXP_LIKE( name , '^(김|이)' )
ORDER BY name ASC;
--days04
SQL
DQL [SELECT]
dba_접두사 = 오라클 관리자 데이터베이스 내의 모든 사용지정보 뷰 view
user_ 접두사 = 현재 접속자가 접근할 수 있는 모든 사용자정보를 가져올 수 있는 뷰
all_접두사 =모든 사용자정보를 가져오는 뷰
SYS는 최고 관리자니깐 모든 정보를 다가져옴
포트번호 바꾸기
--with절 사용한 코딩
WITH temp AS(
--서브쿼리
SELECT deptno, ename, sal+NVL(comm,0) pay
FROM emp
WHERE deptno=30
)
SELECT t.*
FROM temp t --테이블의 별칭
WHERE t.pay BETWEEN 1000 AND 2000;
인라인뷰
from절 안에 있는 서브쿼리
SELECT t.*
FROM (
SELECT deptno, ename, sal+NVL(comm,0) pay
FROM emp
WHERE deptno=30
)
WHERE t.pay BETWEEN 1000 AND 2000;
대문자로 바꾸는 함수 UPPER
'Java' 카테고리의 다른 글
| 오라클 days06 (0) | 2022.04.11 |
|---|---|
| [오라클 ] days0 (0) | 2022.04.07 |
| days02 오라클 (0) | 2022.04.06 |
| 순서도 그리기 (0) | 2022.04.02 |
| 28일차 -입출력 IO (0) | 2022.03.29 |
'Java' Related Articles
more
Comments