어제 문제
-- 1. 제약조건 조인문제
-- dba_constraints, dba_cons_columns 뷰를 사용하여 scott 소유의
-- 제약조건 이름, 소유자, 테이블명, 제약조건타입, 제약조건컬럼명, 참조테이블명, 참조컬럼명을 함꼐 출력
-- 단, 참조테이블이 없는 제약조건도 모두 출력
select * from dba_constraints where owner = 'SCOTT';
select * from dba_cons_columns where owner = 'SCOTT';
select d1.CONSTRAINT_NAME as 제약조건명,
d1.OWNER as 소유자,
d1.TABLE_NAME as 테이블명,
d1.CONSTRAINT_TYPE as 제약조건타입,
c1.COLUMN_NAME as 제약조건컬럼명,
d2.TABLE_NAME as 참조테이블명,
d2.CONSTRAINT_NAME as 참조제약조건명,
c2.COLUMN_NAME as 참조컬럼명
from dba_constraints d1,
dba_constraints d2,
dba_cons_columns c1,
dba_cons_columns c2
where d1.owner = 'SCOTT'
and d1.R_CONSTRAINT_NAME = d2.CONSTRAINT_NAME(+)
and d1.CONSTRAINT_NAME=c1.CONSTRAINT_NAME
and d2.CONSTRAINT_NAME=c2.CONSTRAINT_NAME(+);-- 2. 아래를 수행한 뒤
-- empno 컬럼에 pk생성(만약 중복 데이터가 있다면 단 한 건을 남기고 나머지 모두 제외)
create table emp_test99 as select * from emp;
insert into emp_test99 select * from emp where ename = 'ALLEN';
insert into emp_test99 select * from emp where ename = 'ALLEN';
commit;
select rowid, rownum, e.* -- rownum은 출력때마다 달라지는 임의 값이다. 1부터 식별가능
from emp e; -- rowid는 고유값이라 식별 가능
select * from emp_test99 order by ename;
desc emp_test99;
alter table emp_test99 add constraint emp_test99_empno_pk primary key(empno);
-- rowid 한개만 구하기
select rowid
from emp_test99
where ename='ALLEN'
and rownum=1;
-- 구한 rowid만 빼고 다 삭제
delete from emp_test99
where ename='ALLEN'
and rowid not in (select rowid
from emp_test99
where ename='ALLEN'
and rownum=1);
rollback;
commit;
-- 강사님 방법 : max나 min 썼고, where절을 상호연관 서브쿼리로 처리함
delete emp_test99 e1
where ROWID != (select max(ROWID)
from emp_test99 e2
where e1.empno = e2.empno);
-- 중복 데이터 찾는 법 : 아래 쿼리 써서 count가 1이 아닌 값이 나오면 그게 중복된거임
select empno, count(empno)
from emp_test99
group by empno
having count(empno)>1;-- 3. SQL_코테1_실습1_스크립트.sql, SQL_코테1_실습2_스크립트.sql을 실행한 뒤
select count(*) from employee; -- 11건
select count(*) from sellings; -- 40건
select count(*) from places; -- 11건
select count(*) from place_reviews; -- 350건
-- [문제 설명]
-- employee 테이블은 보험 회사 직원의 정보를 담고 있는 테이블입니다.
-- employee 테이블 구조는 다음과 같다.
--name type nullable 설명
--id number FALSE 직원아이디
--name varchar FALSE 이름
--salary number FALSE 월급
--branch_id number FALSE 근무 대리점 아이디
--sellings 테이블은 자동차 보험 판매 기록을 담고 있는 테이블입니다.
--테이블 구조는 다음과 같습니다.
--name type nullable 설명
--car_id number FALSE 보험 대상 자동차 ID
--employee_name number FALSE 보험 설계사 사원 ID
--created_at date FALSE 계약일
--price int number FALSE 보험료
--employee 테이블과 sellings 테이블을 이용하여 대리점별로 보험을 몇 건이나 성사했는지 조회하는 SQL문을 작성.
select * from employee;
select * from sellings;
select e.branch_id as 대리점,
count(s.car_id) as 성사건수
from employee e,
sellings S
where e.id = s.employee_name(+) -- outer join 생각하기
group by e.branch_id;수업 내용
— 3. synonym
— 공식적으로 부여하는 테이블 별칭
— 보통 스키마.테이블명으로 조회하는 대신 테이블명만으로 조회되도록 설정하기 위해 사용
— 굉장히 자주 사용
— 예) 인사팀과 총무팅이 따로 있고 인사팀에 사원테이블이 존재할 때
— 총무팀이 권한있는 상태에서 select * from 사원; 하면 조회 안됨
— 그때 시노님을 ‘사원’으로 부여하면 select * from 사원;을 해도 ‘인사팀.사원’으로 취급돼서 조회됨.
— 1) 생성
create [or replace] [public] synonym 시노님명 for 원본이름;
— public : 시노님 생성자(소유자) 외에도 공용으로 사용 가능한 별칭.
— 생략 시 시노님 소유자만 별칭 사용 가능
— 2) 삭제
drop [public] synonym 시노님명;
— 생성 시 public으로 생성했다면 삭제할 때도 public을 붙여서 삭제해야됨.
-- [실습 - 시노님을 통한 테이블 접근]
-- SCOTT 계정이 DBA권한 가지고 있음
-- HR 계정 소유 테이블 조회 시도
select * from employees; -- error
select * from hr.EMPLOYEES; -- success
-- 시노님 생성
create public synonym employees for hr.EMPLOYEES;
create synonym countries for hr.COUNTRIES;
-- scott에서 시노님 조회
select * from employees; -- success
select * from countries; -- success
-- system에서 시노님 조회 시도
select * from employees; -- success
select * from countries; -- error (private이라)
-- 시노님 조회
select * from dba_synonyms -- public으로 하면 소유가 public이 됨
where owner = 'PUBLIC' and table_owner = 'HR';
select * from dba_synonyms where table_owner = 'HR';
select * from dba_synonyms where owner = 'SCOTT';
-- 시노님 삭제
drop public synonym employees;
drop synonym countries;— 4. DB링크
— DB와 DB를 연결하는 링크
— DB내에서 다른 DB의 데이터를 조회, 수정 가능
— 1) 생성
create database link 링크명
connect to 유저명 identified by 패스워드
using ‘tns_alias or description’;
— tns_alias : client 쪽의 tnsnames.ora에 기재된 이름
— description : tnsnames.ora에 추가한 전체 코드
— 2) 조회
select * from dba_db_links;
— [DB 원격 접속 관리] (접속시도하는 쪽은 client, 반대쪽은 server)
— step1) client에 oracle 설치
— step2) client->server 간 방화벽 해제
— step3) client에 tnsnames.ora 파일에 server 정보 기입
— tnsname.ora 파일은 oracle home 속에 NETWOR\ADMIN 위치에 존재
— oracle home 위치 찾기 : 검색 > 시스템 환경 변수 편집 > 환경변수 > 아래쪽에 Path 편집 > dbhome 확인
— D:\app\itwill\product\11.2.0\dbhome_3\NETWORK\ADMIN\tnsname.ora
— HONG 정보 생성(ip주소, port, sid(SERVICE_NAME))
— ip주소 확인 : cmd에서 ipconfig(windows), ifconfig(linux)
— port 확인 : cmd에서 lsnrctl status
— sid 확인 : cmd에서 lsnrctl status
-- [실습 - db link를 사용한 데이터 조회]
-- 테이블 조회 시도
select * from emp_test11; -- error (local db에 emp_test11없음)
-- db link 생성
create database link dblink_hong
connect to scott identified by oracle
using 'hong';
-- db link 조회
select * from dba_db_links;
-- db link를 사용한 target db 데이터 조회
select *
from emp_test11@dblink_hong;— [원격접속 방법]
— 1) orange를 통해서
— 2) sqlplus를 통해서
— cmd창에 ‘sqlplus scott/oracle@HONG’ 입력. 방금 HONG은 tns_alias(tnsnames.ora에 설정)
-- [연습문제]
-- HONG DB에 있는 emp_test11테이블을 local DB에서 조회할 수 있도록 하는 시노님 생성
create public synonym emp_test11 for emp_test11@dblink_hong;
drop public synonym emp_test11;
select * from emp_test11;
select *
from dba_synonyms
where db_link is not null;
-- where table_name='EMP_TEST11'; -- 강사님은 이거 썼는데 난 is not null로 한다!— 5. USER관리
— DB접속을 위해 생성하는 계정
— USER별로 객체에 대한 권한 관리
— 1) 생성
create user 유저명 identified by “패스워드”
[default tablespace 테이블스페이스명]
[temporary tablespace 임시테이블스페이스명]
[quota 사이즈 on 테이블스페이스명];
create user 유저명 identified by “패스워드”
quota unlimited on users;
— 2) 삭제
drop user 유저명 [cascade];
— cascade : 유저 소유의 모든 객체(스키마)까지 모두 삭제
— 3) 조회
select * from dba_users;
— 6. 권한
— 데이터를 조회, 수정할 수 있는 권한 관리
— 일반적으로 소유하지 않은 테이블에 대한 조회, 수정 권한이 없음 => 필요한 권한 부여 시 가능
— 1) 권한 종류
— 오브젝트 권한 : 각 테이블에 대한 조회, 입력, 삭제, 수정 권한
— 시스템 권한 : 테이블 생성, 삭제, 수정 권한
— 2) 권한 부여
— 테이블은 한 쿼리에 한 개밖에 지정 못함
— 권한과 유저는 한 쿼리에 여러 개 지정 가능
grant 권한 to 유저 [with grant/admin option];
— 오브젝트 권한 부여 예)
— 예) HR에게 emp테이블에 대한 select 권한을 줌
grant select on emp to HR;
— 예) HR과 OE에게 dept테이블에 대한 select, insert, update, delete 권한을 줌
grant select, insert, update, delete on dept to HR, OE;
— 시스템 권한 부여 예)
grant create table to hr;
grant alter any table, drop any table to hr;
grant create session to hr, oe; — 접속하려면 세션을 만들 수 있어야함. 즉, 접속을 허용하는 명령어
— 유저 만들기랑 create session이랑은 세트인 느낌
— 3) 권한 회수
revoke 권한 from 유저;
-- [연습문제]
-- 1. system 계정에서 수행) itwill user 생성
-- 2. SCOTT 소유의 student, exam_01, department테이블에 대해 조회권한 부여
-- 3. itwill user 접속
-- 4. SCOTT 소유의 student, exam_01, department 테이블 조회
-- 단, 조회 시 스키마 생략할 수 있도록 조치
-- 1.
create user itwill identified by "oracle"
quota unlimited on users;
grant create session to itwill;
-- 2.
grant select on SCOTT.STUDENT to itwill;
grant select on SCOTT.EXAM_01 to itwill;
grant select on SCOTT.DEPARTMENT to itwill;
-- 4.
create public synonym student for SCOTT.student;
create public synonym exam_01 for SCOTT.EXAM_01;
create public synonym department for SCOTT.DEPARTMENT;
-- itwill에서 수행
select * from student;
select * from exam_01;
select * from department;— 7. 인덱스
— [기타 SQL 문법]
— 1. 윈도우 함수
— 2. 정규식 표현식
— 3. pivot/unpivot
— 4. 계층형질의
— 5. 그룹함수