[Oracle] WITH/LIKE__개발공부 34일차
권한 부여 제거 방법
권한 부여, 제거 => DCL문 => GRANT, REVOKE
1단계) 우선 계정이 가지고 있는 롤 확인
SELECT *
FROM user_role_privs
2단계 ) 권한 부여
GRANT CONNECT TO scott;
3단계) 권한 제거
REVOKE CONNECT FROM scott;
오라클 주석처리 방법 3가지를 적으세요.
ㄱ. --
ㄴ. REM
ㄷ. /* */
자료 사전( Data Dictionary )이란 ?
자료사전 = 뷰 정보를 이믜한다.
dba_ 접두사 : 오라클 관리자 데이터베이스 내의 모든 사용자의 정보 뷰( View )
all_ 접두사 : 모든 사용자 정보 View
user_ 접두사 : 현재 접속자가 접근할 수 있는 접속한 사용자 정보 뷰( View )
SELECT *
FROM dba_tables;
FROM all_tables; -- SCOTT 계정이 소유하고 있는 테이블 + 권한 부여 받아서 사용할 수 있는 테이블 View
FROM tabs;
FROM user_tables; -- SCOTT 계정이 소유하고 있는 테이블 정보 View
(tabs와 같은코딩)
FROM all_users;
FROM user_users;
FROM dba_users;
-- ORA-00942: table or view does not exist
=>dba 는 SYS계정에서만 사용 가능하다.
*SYS 계정으로 접속하여 모든 사용자 정보를 조회하는 쿼리(SQL)
SELECT *
FROM all_users; all_ 접두사 : 모든 사용자 정보 View
FROM user_users; user_ 접두사 : 현재 접속자가 접근할 수 있는 접속한 사용자 정보 뷰( View )
FROM dba_users; dba_
에러 메세지 의미
ㄱ. 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 작성 방법 특징
1) 대소문자 구분하지 않는다.
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) 절 별로 라인 구분
4) 키워드 대문자, 그외 문자( 테이블명, 컬럼명 등등 ) 소문자 - 권장.
5) 탭, 공백 사용 - 권장
with절
사용될 서브쿼리 블록을 미리 선언하여 반복사용 하는 방법이다.
1) 형식
WITH 쿼리이름1 AS ( WITH 절 X , SELECT 문 서브쿼리 ),
쿼리이름2 AS ( 서브쿼리 ),
쿼리이름3 AS ( 서브쿼리 ),
:
하나의 with절에 여러 개의 query block 사용이 가능하다
2) 서브쿼리를 사용하면 성능 저하된다.(단점) + 코딩 간결, 쉽게 ( 장점)
문제) emp 테이블에서 pay(sal+comm)가 1000 이상~ 2000 이하 받는 30부서원들만 조회하는 쿼리 작성
조건 : ㄱ. pay 기준으로 오름차순 정렬 --ename을 기준으로 오름차순 정렬해서 출력(조회)
ㄴ. comm 이 null은 0으로 처리 ( nvl () )
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(인라인 뷰)
FROM 절 뒤에 사용되는 서브쿼리를 의미한다.
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;
LIKE
문자 포함 여부를 알고 싶을때 사용한다.
LIKE 연산자 기호 (=와일드카드( wild card)) : % _
% : 갯수 상관없이 어떤 문자가 와도 상관없음
_ : 최소 1개이상의 어떤 문자가 와야됨
SELECT name , 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 ) = '김';
문제)emp 테이블에서 이름(ename) 속에 'la' 문자열을 포함하는 사원 정보를 출력.
SELECT ename
FROM emp
WHERE ename Like UPPER('%la%')
ESCAPE 옵션
와일드카드 %가 아니라 부서명을 나타내는 일반 문자로 처리할때 사용하는 방법이다.
(%가 들어가는지 검색용으로 사용하고 싶은 경우를 의미한다.)
WHERE dname LIKE '%\%%' ESCAPE '\';
REGEXP_LIKE()
REGEXP_LIKE() 함수 : 정규표현식을 사용하는 LIKE() 함수
【형식】
regexp_like ( search_string , pattern [,match_option])
[,match_option]
i 대소문자 구분 없음
c 대소문자 구분 있음
n period(.)를 허용함
m source string이 여러 줄인 경우(multiple lines)
x whitespace character(공백문자) 무시
문제) 김씨 또는 이씨 사원 출력
SELECT name, ssn
FROM insa
WHERE REGEXP_LIKE( name, '^[김이]' )
-- WHERE REGEXP_LIKE( name, '^김' ) OR REGEXP_LIKE( name, '^이' )
-- WHERE REGEXP_LIKE( name, '^(김|이)' )
문제) emp 테이블에서 이름(ename) 속에 'la' 문자열을 포함하는 사원 정보를 출력.
SELECT ename
FROM emp
WHERER REGEXP_LIKE( ename , 'la', i ) --가장 많이 사용됨 **중요
--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' ) ;
참고) AS, DISTINCT, ALL 은 SELECT 절 사용할 수 있는 키워드이다.