ㅁㅁㅁㅁ 옵션 추가한 시퀀스 생성 1로 시작 2씩 증가 maxvalue10 nominvalue cycle nocache;
ㅁㅁㅁㅁㅁㅁㅁㅁ 옵션을 하나도 추가하지 않은 시퀀스를 생성;
ㅁㅁㅁㅁㅁㅁㅁ 시퀀서 넣은곳에 values를 넣어본다.;
insert into emp_copy1
values (emp_co_eno_seq1.nextval, 'u', '개발', 0, sysdate, 3000, 100, 0);
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 생성된 시퀀스를 조회하는 쿼리;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 현재 값을 조회;
ㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 다음 값을 조회;;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 시퀀스의 삭wp;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ 현지 접속한 사용자에게 계속 사용할 수 있는 별칭 주기;
ㅁㅁㅁㅁㅁㅁㅁㅁㅁ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 ;
댓글 영역