고정 헤더 영역
상세 컨텐츠
본문
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ study계정이 사용할 수 있는 데이터 사전 조회;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ study계정이 가지고있는 객체의 정보를 확인
ㅁㅁㅁㅁㅁㅁ study계정에 dba권한이 있기 때문에 sys가 소유하고 있는 테이블도 사용할 수 있다.;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 데이터베이스 시스템 관련 정보들;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ ;
세션을 kill해서 접속 끊어버리는 쿼리;
세션을 kill해서 접속 끊어버리는 쿼리;
ㅁㅁㅁㅁㅁㅁㅁㅁ 락걸린 객체를 확인하는 쿼리
ㅁㅁㅁㅁㅁㅁㅁㅁ 락걸린 세션 조회
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ student테이블에 sname을인덱스로 생성(비 고유 인덱스);
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ professor 테이블에 pno, pname 을인덱스로 생성(비 고유 인덱스);
ㅁㅁㅁㅁㅁ 스튜던트 테이블에 평점을 4.5로 환산한 평점 인덱스로 지정
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 생성된 인덱스 확인;
ㅁㅁㅁㅁㅁㅁㅁㅁ 뷰의 생성, 과목별 학과별 기말고사의 평균을 저장하는 뷰 생ㅇ성 cno cname major avgresult;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 1학년만 들어오게 할수 있도록 sno, sname, syear avr이 들어있는 뷰를 만들어라
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 인라인 뷰와 rownum을 이용해서 최상위 n 데이터 조회
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ 학과별 기말고사 성적의 평균이 가장 높은 3개 학과 조회;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ major별로 평균점수가 큰것부터 매기고 가장 큰것중 세개를 가져와라
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 학생번호를 row로 매기고 5 이상을 가져와라
ㅁㅁㅁㅁㅁㅁ rownum, eno, enma, sal이 있는걸 rownum 매겨서 3까지 가져와라
ㅁㅁㅁㅁㅁㅁ rownum, eno, enma, sal이 있는걸 sal 순으로 rownum 매겨서 3까지 가져와라
ㅁㅁㅁㅁㅁㅁ rownum, eno, enma, sal이 있는걸 sal 순으로 rownum 매겨서 어딧에서나 가져올 수 있게
ㅁㅁㅁㅁㅁㅁㅁ전공별 평균점수가 높은것부터 rownum을 매겨라
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ rownum을 매겨서 전공별로 평균 점수가 제일 높은걸 3순위까지 가져와라 근데 아래위로 다 가져올수 있게;
ㅁㅁㅁ table emp_copy2 생성
ㅁㅁㅁ table emp dept_copy2 생성
ㅁㅁㅁ table emp_copy2 생성
create table emp_copy1
(
eno number,
ename varchar2(20),
job varchar2(10),
mgr number,
hdate date,
sal number,
comm number,
dno number
);
ㅁㅁㅁ table emp dept_copy2 생성
create table dept_copy1
(
dno number,
dname varchar2(10),
loc varchar2(10),
director number
);
create table emp_copy1
(
eno number,
ename varchar2(20),
job varchar2(10),
mgr number,
hdate date,
sal number,
comm number,
dno number
);
ㅁㅁㅁ table emp dept_copy2 생성
create table dept_copy1
(
dno number,
dname varchar2(10),
loc varchar2(10),
director number
);
ㅁㅁㅁㅁ 옵션 추가한 시퀀스 생성 1로 시작 2씩 증가 maxvalue10 nominvalue cycle nocache;
ㅁㅁㅁㅁㅁㅁㅁㅁ 옵션을 하나도 추가하지 않은 시퀀스를 생성;
ㅁㅁㅁㅁㅁㅁㅁ 시퀀서 넣은곳에 values를 넣어본다.;
insert into emp_copy1
values (emp_co_eno_seq1.nextval, 'u', '개발', 0, sysdate, 3000, 100, 0);
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 생성된 시퀀스를 조회하는 쿼리;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 현재 값을 조회;
ㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 다음 값을 조회;;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 수정 5씩증가, maxvalue100, cycle;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 삭wp;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 현지 접속한 사용자에게 계속 사용할 수 있는 별칭 주기;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 모든 사용자가 계속 사용할 수 있는 테이블의 별칭 추가
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ synonym 삭제;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁcreate table emp_pk1 만들고 primarykey를 number에. 두가지방법;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ pk 추가하는법 dept에 dno로 primarykey 추가해라
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁdept에서 primarykey를 삭제해라;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ score 테이블에 cno, sno pk 추가
오라클 객체
데이터 사전
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ study계정이 사용할 수 있는 데이터 사전 조회;
select *
from dict;
select *
from dictionary;
select *
from CDB_KGLLOCK;
select *
from dict
where TABLE_NAME like '%USER%';
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ study계정이 가지고있는 객체의 정보를 확인
;
select TABLE_NAME
from USER_TABLES;
study계정이 권한을 가지고 사용할 수 있는 모든 객체 정보;
ㅁㅁㅁㅁㅁㅁ study계정에 dba권한이 있기 때문에 sys가 소유하고 있는 테이블도 사용할 수 있다.;
select owner, table_name
from ALL_TABLES;
owner 컬럼이 없음
현재 접속한 사용자가
select *
from USER_TABLES;
owner 컬럼이 존재. 다른 사용자가 소유하고 있는 테이블의 권한만 가지고 있을 수도 있기 때문에
select *
from ALL_TABLES
;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 데이터베이스 시스템 관련 정보들;
select *
from dba_tables;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ ;
세션을 kill해서 접속 끊어버리는 쿼리;
alter system kill session '128, 33497' immediate;
락세션을 날리는 역할을 한다.
;
ㅁㅁㅁㅁㅁㅁㅁㅁ 락걸린 객체를 확인하는 쿼리
select OBJECT_ID, SEssion_id, oracle_username, os_user_name
from V$LOCKED_OBJECT;
select sid, osuser, serial#, program
from v$session S
join V$LOCKED_OBJECT L on s.sid = l.SESSION_ID;
insert into kikiki(WEQG)
values (13);
commit;
ㅁㅁㅁㅁㅁㅁㅁㅁ 락걸린 세션 조회
;
select l.object_id, s.sid, l.oracle_username, s.serial#, s.osuser, s.program
from v$session s
join v$locked_object l on s.sid = l.session_id;
인덱스
인덱스 생성
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ student테이블에 sname을인덱스로 생성(비 고유 인덱스);
create index student_sname_idx on student (sname);
commit;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ professor 테이블에 pno, pname 을인덱스로 생성(비 고유 인덱스);
create index professor_pno_pname_idx
on PROFESSOR (pno, pname);
--수식으로 인덱스 형성
테이블에 여러개의 인덱스 생성 가능
ㅁㅁㅁㅁㅁ 스튜던트 테이블에 평점을 4.5로 환산한 평점 인덱스로 지정
수식으로 만든 인덱스는 컬럼명이 표시되지 않고 시스템에서 자동으로 생성한 수식의 이름을 사용한다.
;
create index student_convert_avr_index on STUDENT (avr * 4.5 / 4.0);
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 생성된 인덱스 확인;
select uic.index_name, uic.column_name, uic.column_position, ui.uniqueness
from USER_INDEXES ui
join USER_IND_COLUMNS uic
on ui.INDEX_NAME = uic.INDEX_NAME and ui.TABLE_NAME in ('STUDENT', 'PROFESSOR')
order by ui.TABLE_NAME, ui.INDEX_NAME, uic.COLUMN_POSITION;
;
drop index student_sname_idx;
drop index STUDENT_CONVERT_AVR_INDEX;
drop index professor_pno_pname_idx;
;
ㅁㅁㅁㅁㅁㅁㅁㅁ 뷰의 생성, 과목별 학과별 기말고사의 평균을 저장하는 뷰 생ㅇ성 cno cname major avgresult;
create or replace view V_AVG_SCORE
(
cno, cname, major, avgresult
--컬럼명을 커스터마이징해서 사용할 수 있기 때문에 원천 테이블의 컬럼명을 노출하지 않을 수 있다.
)
as
(
--위에서 지정한 컬럼의 순서와 개수가 일치해야 한다.
select cno, c.cname, st.major, round(avg(sc.result), 2)
from course c
natural join score sc
join student st
on sc.SNO = st.SNO
group by c.cname, cno, st.major
);
select *
from V_AVG_SCORE;
update course
set cname = '일반화학1'
where cname = '일반화학';
update student
set major = '유공1'
where major = '유공';
select *
from V_AVG_SCORE
order by cno, cname;
원천데이터를 바꾸면 뷰도 자동적으로 바뀐다;
단순뷰 생성;
(하나의뷰만 사용하기 때문에 dml이 사용 가능하다)
dml을 사용하면 우너천 테이블의 데이터가 추가/
삭제/
수정되는데
조회쿼리가 1학년만 가져오는 쿼리라서 우너천테이블 추가된 2, 3, 4학년의 데이터는 표출되지 않는다.
;
create or replace view st_ch
(
sno, sname, syear, avr
)
as
(
select sno, sname, syear, avr
from student
where syear = 1);
select *
from st_ch
where sname = '홍길동';
;
commit;
2학년을 넣으면 안됌. 1학년만 넣을수 있게 제약조건이 걸려버린거니까
dml도 syear가 1인 데이터만 추가/
수정 가능
단순 뷰에서 dml의 사용;
insert into st_ch
values ('9001', '홍길동', 1, 4.0);
select *
from STUDENT;
check option 추가하면 제약조건이 성성되어 조회해온 조건에 맞는 데이터만 추가할 수 있도록 변경
1학년 데이터만 조회해오기 때문에 1학년 데이터만 입력할 수 있도록 변경
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 1학년만 들어오게 할수 있도록 sno, sname, syear avr이 들어있는 뷰를 만들어라
create or replace view st_ch
(
sno, sname, syear, avr
)
as
(
select sno, sname, syear, avr
from student
where syear = 1)
with check option constraint view_st_ch_ck;
;
위의 조건에 1학년만 들어갈수 있게 설정해서 3학년은 들어갈 수 없다. 즉 아래거는 안됨.;
insert into st_ch
values ('9001', '홍길동', 3, 4.0);
인라인 뷰
;
select e.eno, e.ename, e.dno, b.sal
from emp e
join (
-- 인라인 뷰
select dno, min(sal) as sal
from emp
group by dno) B on e.sal = b.sal;
뷰의 삭제
;
drop view st_ch;
drop view v_avg_score;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 인라인 뷰와 rownum을 이용해서 최상위 n 데이터 조회
급여 최상위 3명 데이터 조회;
select rownum, a.eno, a.ename, a.sal
from (select eno, ename, sal
from emp
order by sal desc) a
where rownum <= 3;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ 학과별 기말고사 성적의 평균이 가장 높은 3개 학과 조회;
근데 여기는 작다밖에 안됨;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ major별로 평균점수가 큰것부터 매기고 가장 큰것중 세개를 가져와라
select rownum, a.major, A.b
from (select major, avg(result) b
from STUDENT
natural join score
group by major
order by avg(result) desc) A
where rownum <= 3;
여기는 크다도 가능;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 학생번호를 row로 매기고 5 이상을 가져와라
select A.*
from (select rownum as rn, st.*
from student st) a
where a.rn > 5;
select ass, a.major, A.b
from (select rownum ass, major, avg(result) b
from STUDENT
natural join score
group by major
order by avg(result) desc) A
where ass <= 3;
잘못된 rownum, rownum을 붙인다음 정렬이 일어나서.;
ㅁㅁㅁㅁㅁㅁ rownum, eno, enma, sal이 있는걸 rownum 매겨서 3까지 가져와라
select rownum, eno, ename, sal
from emp
where rownum <= 3
order by sal desc;
ㅁㅁㅁㅁㅁㅁ rownum, eno, enma, sal이 있는걸 sal 순으로 rownum 매겨서 3까지 가져와라
select rownum, a.eno, a.ename, a.sal
from (select eno, ename, sal from emp order by sal desc) a
where rownum <= 3;
이건 잘된거;
ㅁㅁㅁㅁㅁㅁ rownum, eno, enma, sal이 있는걸 sal 순으로 rownum 매겨서 어딧에서나 가져올 수 있게
select B.*
from (select rownum as rn, a.eno, a.ename, a.sal
from (select eno, ename, sal from emp order by sal desc) a) B
where B.rn > 3;
select major, avg(result) b
from STUDENT
natural join score
group by major
order by avg(result) desc;
////////////////////////////
여기 아래 왜 안돼냐????;
SELECT arr, major, b
FROM (SELECT ROWNUM AS arr, major, AVG(result) AS b
FROM STUDENT
NATURAL JOIN score
GROUP BY major
ORDER BY AVG(result) DESC)
WHERE arr <= 3;
SELECT azz, major, b
FROM (SELECT ROWNUM AS azz, major, AVG(result) AS b
FROM STUDENT
NATURAL JOIN score
GROUP BY major
ORDER BY AVG(result) DESC)
WHERE azz <= 3;
///////////////////////////////////////
여기 왜 안되
ㅁㅁㅁㅁㅁㅁㅁ전공별 평균점수가 높은것부터 rownum을 매겨라
SELECT ROWNUM AS azz, major, b
FROM (SELECT major, AVG(result) AS b
FROM STUDENT
NATURAL JOIN score
GROUP BY major
ORDER BY AVG(result) DESC) temp;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ rownum을 매겨서 전공별로 평균 점수가 제일 높은걸 3순위까지 가져와라 근데 아래위로 다 가져올수 있게;
SELECT azz, major, b
FROM (SELECT ROWNUM AS azz, major, b
FROM (SELECT major, AVG(result) AS b
FROM STUDENT
NATURAL JOIN score
GROUP BY major
ORDER BY b DESC) temp)
WHERE azz >= 3
-- order by azz
;
이건 된다!!!!!!!!!!!!!!!!!!!!!!!!!!!;
왜냐하면 rownum은 groupby로 묶일수가 없으니까 그런다;
여기부터 다시 매기기 ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ
여기부터 다시 매기기 ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ
여기부터 다시 매기기 ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ
여기부터 다시 매기기 ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ
시퀀스ㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐㅐ;
ㅁㅁㅁ table emp_copy2 생성
create table emp_copy1
(
eno number,
ename varchar2(20),
job varchar2(10),
mgr number,
hdate date,
sal number,
comm number,
dno number
);
ㅁㅁㅁ table emp dept_copy2 생성
create table dept_copy1
(
dno number,
dname varchar2(10),
loc varchar2(10),
director number
);
시퀀스 2개 생성
ㅁㅁㅁㅁ 옵션 추가한 시퀀스 생성 1로 시작 2씩 증가 maxvalue10 nominvalue cycle nocache;
create sequence emp_co_eno_seq1
start with 1
increment by 2
maxvalue 10
nominvalue
cycle
nocache;
ㅁㅁㅁㅁㅁㅁㅁㅁ 옵션을 하나도 추가하지 않은 시퀀스를 생성;
create sequence dept_co_dno_seq1;
nextval : 시퀀스가 가리키는 값을 다음 값으로 옮긴다.;
;
ㅁㅁㅁㅁㅁㅁㅁ 시퀀서 넣은곳에 values를 넣어본다.;
insert into emp_copy1
values (emp_co_eno_seq1.nextval, 'u', '개발', 0, sysdate, 3000, 100, 0);
select *
from emp_copy1;
delete
from emp_copy1
where ename = 'u';
currval : 시퀀스가 현재 가리키고 있는 값을 가져온다.;
insert into emp_copy1
values (emp_co_eno_seq1.currval, 'u', '개발', 0, sysdate, 3000, 100, 0);
ㄸㄷ한번 바뀌었으니까 지금밸류임 넥스트바는 다음바 currval은 현재 발임
이제는 제약 없는 시퀀스를 만들어보자. 옵션이 없는 시퀀스의 사용
;
insert into dept_copy1
values (dept_co_dno_seq1.nextval, '개발', '서울', 0);
select *
from dept_copy1;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 생성된 시퀀스를 조회하는 쿼리;
select sequence_name,
max_value,
min_value,
increment_by
,
cache_size,
last_number,
cycle_flag
from user_sequences;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 현재 값을 조회;
select dept_co_dno_seq1.currval
from dual;
ㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 다음 값을 조회;;
nextval 호출 시 무조건 시퀀스가 다음값으로 세팅됨 시퀀스가 가리키는 값이 다음값으로 넘어감. ;;
select dept_co_dno_seq1.nextval
from dual;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 수정 5씩증가, maxvalue100, cycle;
alter sequence dept_co_dno_seq1
increment by 5
maxvalue 100
cycle;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 삭wp;
drop sequence dept_co_dno_seq1;
1-5 synonym 객체에 별칭주기;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 현지 접속한 사용자에게 계속 사용할 수 있는 별칭 주기;
create synonym dc for dept_copy1;
synonym의 사용
select *
from dc;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 모든 사용자가 계속 사용할 수 있는 테이블의 별칭 추가
;
create public synonym ec for C##EUNSUK.EMP_COPY1;
select *
from ec;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ synonym 삭제;
drop synonym dc;
drop public synonym ec;
1. 제약조건 constraint
;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁcreate table emp_pk1 만들고 primarykey를 number에. 두가지방법;
create table emp_pk1
(
--constraint 제약조건을 생략하면 제약조건을 system 에서 자동으로 생성해준다.
eno number primary key,
ename varchar2(20),
job varchar2(10),
mgr number,
hdate date,
dno number
);
create table dept_pk1
(
dno number,
dname varchar2(10),
loc varchar2(10),
director number,
constraint pk_dept_dno primary key (dno)
);
중복허용 되는지 안되는지 확인;
이거 하려고하면 무결성 제약조건에 ㅇ위배됨. 1은 이미 있는데 왜 1을 넣으려고하냐
;
insert into dept_pk1
values (1, '개발2', '서울2', 2);
--pk에 null 저장
insert into emp_pk1
values (2, null, null, null, null, null);
select *
from dept_pk1;
select *
from emp_pk1;
다중 칼럼 pk 지정;
이렇게는 할수없다. 한번에 다 몰아서 넣어야함
create table score_pk1
(
cno number primary key,
sno number primary key,
result number
);
다중칼럼 pk 지정시 맞는 방법은;
create table score_pk1
(
cno number,
sno number,
result number,
constraint pk_score_cno_sno primary key(cno, sno)
--두개의 컬럼이 하나의 키가 되는거
-- 두개 모두 중복이 되야함
);
다중컬럼 pk는 다중 컬럼이 pk쌍이 된다.
pk로 지정된 모든 컬럼의 값이 중복돼야 중복으로 인식;
모든 컬럼의 값이 중복돼야 중복으로 인식됌
ex ) 게시판만들데 다충첨부파일 파일을 1234 여러개 올릴 때 활용하기 유리한 형식의 데이터
insert into score_pk1 values (1,1,100);
insert into score_pk1 values (1,2,99);
insert into score_pk1 values (1,1,97);
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ pk 추가하는법 dept에 dno로 primarykey 추가해라
;
alter table dept add constraint pk_dep_dno primary key (dno);
pk 삭제;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁdept에서 primarykey를 삭제해라;
alter table dept drop primary key ;
alter table dept drop constraint pk_dep_dno;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ score 테이블에 cno, sno pk 추가
alter table score add constraint pok_score_cno_sno primary key (cno, sno);
alter table score drop primary key ;
댓글 영역