231128 6일차(오라클)

※사용자 관리

*사용자:  접속하여 데이터를 관리하는 계정(user)

*데이터베이스 스키마(schema)

 :데이터베이스에서 데이터 간 관계, 데이터 구조, 제약 조건 등 모든 객체 데이터베이스 구조의 범위

*오라클 데이터베이스에서는 스키마와 사용자를 구별하지 않고 사용하기도 한다.

 

*사용자 생성

CREATE USER 사용자 이름(필수)

IDENTIFIED 패스워드(필수);

 

--SCOTT계정으로 사용자 생성하기
CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;

->권한이 부족

 

--SYSTEM 사용자로 접속 후 사용자 생성

CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;
-->계정을 만들었다고 바로 접속되지 않음, 접속할 권한을 줘야한다.

 

--SYSTEM 사용자로 접속 후  ORCLSTUDY 사용자에게 권한 부여하기

GRANT CREATE SESSION TO ORCLSTUDY;

 

*사용자 정보 조회
SELECT * FROM ALL_USERS WHERE USERNAME='ORCLSTUDY';

--사용자 정보(패스워드) 변경하기(SYSTEM)
ALTER USER ORCLSTUDY IDENTIFIED BY ORCL;

ALTER USER ORCLSTUDY IDENTIFIED BY ORCL REPLACE ORACLE;

--사용자 삭제하기(SYSTEM)
DROP USER ORCLSTUDY;

--사용자와 객체 모두 삭제하기(SYSTEM)
DROP USER ORCLSTUDY CASCADE;

 

 

※권한관리

*시스템 권한 : 데이터베이스 관리 권한이 있는 사용자가 부여할 수 있는 권한

*객체 권한: 특정 사용자가 생성한 테이블/인덱스/뷰/시뭔스 등과 관련된 권한

 

--시스템 권한 부여(SYSTEM)

GRANT[시스템 권한] TO [사용자 이름/롤(ROLE]이름/PUBLIC  [WITH ADMIN OPTION];

GRANT CREATE SESSION TO ORCLSTUDY;

 

--사용자 권한 부여하기 (SYSTEM)
GRANT RESOURCE, CREATE SESSION, CREATE TABLE TO ORCLSTUDY;

 

--시스템 권한 취소 (SYSTEM)
REVOKE [시스템 권한] FROM[사용자 이름/롤이름/PUBLIC]; 

 

※객체 권한 : 특정 사용자가 생성한 테이블/인덱스/뷰/시퀀스 등과 관련된 권한

예) SCOTT 소유 테이블에 ORCLSTUDY 사용자가 SELECT나 INSERT등의 작업이 가능하도록 허용할 수 있음

-GRANT문을 사용

 

*객체 권한 부여

GRANT [객체권한/ ALL PRIVILEGES]

ON [스키마, 객체 이름]

TO [사용자 이름/롤(Role)이름/PUCLIC]

[WITH GRANT OPTION];

 

--

 

 

※ROLE관리

*롤은 여러 종류의 권한을 묶어 놓은 그룹을 뜻합니다. 롤을 사용하면 여러 권한을 한번에 부여하고 해제할 수 있으므로 권한 관리 효율을 높일 수 있다.

 

*사전정의된 롤

-CONNECT 롤 CREATE SESSION

-RESOURCE 롤

-DBA 롤

 

*사용자 정의 롤

1.CREATE ROLE문으로 롤을 생성한다.

2.GRANT 명령어로 생성한 롤에 권한을 포함시킨다.

3.GRANT 명령어로 권한이 포함된 롤을 특정 사용자에게 부여한다.

4.REVOKE 명령어로 롤을 취소시킨다.

 

--SYSTEM계정으로 ROLESTUDY 롤 생성 및 권한 부여(23P)
CONN SYSTEM/oracle
CREATE ROLE ROLE_STUDY;
GRANT CONNECT,RESOURCE, CREATE VIEW, CREATE SYNONYM TO ROLE_STUDY;

 

--ORCLSTUDY 사용자에게 롤( ROLESTUDY) 부여하기

GRANT ROLESTUDY TO ORCLSTUDY;

-ORCLSTUDY 권한 취소
REVOKE CREATE SESSION FROM ORCLSTUDY; 
GRANT ROLE_STUDY TO ORCLSTUDY;

 

※SQL-99 표준 문법의 조인

SQL-99 방식 조인은 FROM에 조인 키워드를 사용

 

SELECT* FROM EMP E, DEPT D 
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL >=3000;

 

*NATURAL JOIN을 사용하여 조인하기
SELECT E.EMPNO, E.ENAME, DEPTNO 
FROM EMP E NATURAL JOIN DEPT D;
--기준 열인 DEPTNO를 SELECT절에 명시할 때 테이블 이름을 붙이면 안되는 특성

*JOIN~USING을 사용하여 조인하기
--FROM TACLE1 JOIN TACLE2 USING(조인에 사용한 기준열)
SELECT E.EMPNO, E.ENAME, DEPTNO 
FROM EMP E JOIN DEPT D USING(DEPTNO);

*JOIN~ON 을 사용하여 조인하기
SELECT E.EMPNO, E.ENAME,E.DEPTNO, D.DEPTNO 
FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);

 

 

*OUTER JOIN(왼쪽 외부 조인, 오른쪽 외부 조인, 전체 외부조인)
-SQL-99 왼쪽 외부 조인
SELECT * 
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO(+);

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO);
SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D USING(DEPTNO);

--다음 SQL문은 JOIN~USING 키워드를 사용한 등가 조인입니다.

조건1. EMP 테이블과 DEPT테이블의 조인 조건은 부서 번호(DEPTNO)가 같을 때 입니다.

조건2. 급여는 3000 이상이며 직속상관이 반드시 있어야 합니다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE,E. SAL, E.COMM

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE,E.SAL, E.COMM, DEPTNO
FROM EMP E JOIN DEPT D USING(DEPTNO)  
WHERE SAL>=3000 AND E.MGR IS NOT NULL ;

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE,E.SAL, E.COMM, E.DEPTNO, D.DEPTNO
FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO)  
WHERE SAL>=3000 AND E.MGR IS NOT NULL ;

 

--ㅡ급여 (SAL)가 2000 초과인 사원들의 부서 정보, 사원 정보를 출력하라
SELECT DEPTNO, D.DNAME,E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D USING(DEPTNO)
WHERE SAL>=2000
ORDER BY DEPTNO;

--각 부서별 평균 급여 ,최대 급여, 최소 급여, 사원수를 출력하라
SELECT DEPTNO, D.DNAME,TRUNC(AVG(SAL),0),MAX(SAL),MIN(SAL),COUNT(*)
FROM EMP E JOIN DEPT D USING(DEPTNO)
GROUP BY DEPTNO,D.DNAME;

--모든 부서 정보와 사원 정보를 부서번호, 사원 이름순으로 정렬하여 출력하라
SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E JOIN DEPT D USING(DEPTNO)
ORDER BY DEPTNO, E.ENAME;

※madang

SQLPIUS에서 실행

 

C:\Users\admin>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on 화 11월 28 14:19:34 2023
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

사용자명 입력: system
비밀번호 입력: orcl

다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> @C:\Users\admin\Desktop\오라클\demo_madang_1.txt

사용자가 삭제되었습니다.
사용자가 생성되었습니다.
권한이 부여되었습니다.
권한이 부여되었습니다.
사용자가 변경되었습니다.
ERROR:
ORA-01017: invalid username/password; logon denied

경고: 이제는 ORACLE에 연결되어 있지 않습니다. >>실패

 

다시시도

 

SQL> CONN SYSTEM
비밀번호 입력:
연결되었습니다.
SQL> @C:\Users\admin\Desktop\demo_madang.sql

사용자가 삭제되었습니다.
사용자가 생성되었습니다.
권한이 부여되었습니다.
권한이 부여되었습니다.
사용자가 변경되었습니다.
연결되었습니다.
테이블이 생성되었습니다.
테이블이 생성되었습니다.
테이블이 생성되었습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
.

.

.
테이블이 생성되었습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
커밋이 완료되었습니다.

>sqldeveloper로 사용

 

접속>+

>Name  madang

사용자이름 madang

비밀번호 madang

SID orcl

>테스트>성공> 접속

 

select*from book;
select*from CUSTOMER;
select*from ORDERS;

--도서번호가 1인 도서의 이름
SELECT BOOKNAME FROM BOOK
WHERE BOOKID=1;

--가격이 20,000원 이상인 도서의 이름
SELECT BOOKNAME FROM BOOK
WHERE PRICE>=20000;

--박지성이 주문한 책의 이름
SELECT B.BOOKNAME FROM BOOK B, ORDERS O, CUSTOMER C
WHERE B.BOOKID=O.BOOKID
AND O.CUSTID=C.CUSTID
AND C.NAME='박지성';

SELECT B.BOOKNAME FROM ORDERS O JOIN CUSTOMER C USING(CUSTID)
JOIN BOOK B USING(BOOKID)
WHERE NAME='박지성';