030920

TOP - N 구하기

 

rowsum 한계

--rownum은 컬럼처럼 사용 가능!
--rownum은 orderby 에 먹히지 않는다. 하기 전에 먼저 측정된다.
select rownum, e.*
from emp e
order by sal desc
;

 

해결책1 ) 서브쿼리

select rownum, ex.*
    from (select * from emp order by sal desc) ex
    where rownum < 3

;
-- 서브쿼리를 이용해야 내가 원하는 섞이지 않는 rownum이 나타난다.

 

해결책2) 뷰

create or replace view emp_view_sal
as
select empno, ename, nvl(sal, 0) sal
from emp
order by sal desc;


select rownum, empno, ename, sal
from emp_view_sal
;

--서브쿼리 대신에 view를 사용했다

select rownum, empno, ename, sal
from emp_view_sal
where rownum = 2
;
-- where이 언제 동작하는지를 알아야 =5 가 안나오는 이유를 알 수 있어
--순서상 하나하나 하면서 조건체크를 하는데, 1,2,3,4,5 하나씩 체크하는거라 갑자기 5를 찾아내라하면 몰라. 1은 나타낼 수 있는데 2부터 안돼
--이걸 해결하려면 한 번 더 감싸야해 rownum을 모두 작동시키는 테이블을 하나 만들고 그 다음에 3번을 찾아내게끔

 

해결책의 해결책

select *
from (select rownum r, ex.*
        from (select *
                from emp
                order by sal desc) ex) a
where r = 3
;
--이렇게,,, rownum 그대로는 못 쓰는 이유는,, 괄호로 친 곳까지 생명력을 다하니까,,,?

 

 

<시험 대비>

데이터베이스란 - 공용/통합/저장/운영 데이터

1. 필요한 데이터를 공동으로 사용할 수 있도록 중복을 최소화하여 통합 저장한 데이터 집합체

2. 응용프로그램과 데이터베이스의 중재자로서 모든 응용프로그램들이 데이터베이스를 공용할 수 있게 관리해주는 시스템.

3. 특징

• 실시간접근성

• 지속적인 변화

• 동시 공유

• 내용에 의한 참조 (주소나 위치에 의한 참조가 아닌 가지고 있는 값에 따라 참조)

4. 계층형(트리형,자식-하나부모) / 네트워크형(트리형 - 그물,여러부모) / 관계형(키-값,테이블) / NoSql(관계형보다 덜 제한적일관성, 빅데이터,실시간웹어플)

1. DB 주요 사용자 계정 정의 및 종류

   1. db생성, 관리, 유지보수를 담당하는 계정, 모든 권한과 책임을 가지고 있음.

   2. sys - super 모든 문제를 처리할 수 있는 권한을 가짐

   3. system - 관리, 유지보수에 사용되는 사용자 계정.

   1. 해당 계정이 작업을 할 수 있는 최소한의 권한을 갖는 것이 원칙

   2. 데이터베이스에 select/insert.update 주업. ddl의 일부 / dml  

   3. -- resource 

   4. 사용자계정 조회

      1. 참고- table 관리 테이블이 있음.

   1. select * from dba_users;  - 관리자계정 접속해서 확인

1. 관리자용 계정

2. 사용자 계정

2. 권한 Role

   1. grant role1, role2, … to 계정1;

   1. revoke role1, role2, … from 계정1;

   1. connect - db 연결할 수 있는 권한. 

   2. resource- 개체를 생성, 변경제거할 수 있는 DDL / DML 가능하게하는 권한.  -  create table/alter/drop, insert/delete/update

   1. grant connect, resource to 이름;

   2. grant 이름 to 사용자계정;

1. 권한 부여

2. 권한 해제

3. role (교재 71, 22)

4. create role 이름;

3. 테이블

1. 교재 33

2. 데이터 저장 단위

3. 사용자가 접근가능한 데이터를 보유하며 레코드와 컬럼으로 구성됨.

4. 데이터 집합을 표현, 데이터 집합간의 관계를 표현할 수 있다. (ex) foreign key)

5. DDL을 이용하여 테이블을 create / alter/  drop / truncate 수행

4. 제약조건 - Constraint 

   1. 옵션

      1. 데이터 삭제시 해당 데이터를 참조하는 테이블의 데이터 까지 모두 삭제하는 옵션

      1. ...

      1. ...

   1. on delete cascade

   2. on delete set null

   3. on update cascade

1. 무결성을 지키기 위해 설정하는 조건

2. not null - 데이터에 null 허용 안함.

3. unique - 중복된 데이터 값을 허용하지 않음.

4. primary key - not null + unique, 고유 식별자로 사용함.

5. foreign key - 2 테이블의 데이터를 연결될 수 있도록 설정하고 외래키 테이블에 저장될 수 있는 데이터를 제한함.

6. check - 저장 가능한 데이터값의 범위, 조건을 지정하여 설정한 값만 허용함.

7. default - constraint는 아님

5. DDL 

1. 데이터정의어 (Data Definition Language)

2. 객체의 생성 / 수정 / 삭제 / 개체초기화 등 데이터의 구조를 정의하는 언어

3. 주로 DB관리자 설계자가 사용함. 

4. create - 개체생성

5. alter - 개체수정 - modify, add, drop, .. 가능한 하부 명령어 꼭 확인 - 명령어 뒤에 가로 쳐도 되고, 안쳐도 되는듯?

6. drop 개체삭제

7. truncate 개체초기화

6. DML

1. 데이터조작어 (

2. 응용프로그래머가 데이터를 조작함.

7. 데이터 딕셔너리 뷰~

1. 오라클 존재하고, 시스템의 테이블, 뷰, 트리거 등 객체가 변경됨을 관리하기위해 존재하는 테이블로 사용자가 업데이트(삽입, 수정, 삭제) 하지 않아도 dbms가 자동으로 업데이트해줌.

2. select * from user_tables;  -- 해당 계정이 가지고 있는 테이블의 정보를 조회할수 있는 딕셔너리 뷰~

3. select * from user_indexes; -- 해당 계정이 가지고 있는 인덱스의 정보를 조회할수 있는 딕셔너리 뷰~

4. select * from user_triggers;

5. select * from user_procedures;

8. index 서술

9. view 서술

10. sequence   서술

11. synonym  서술

 

---

 

문제해결 시나리오오

 

1. insert 구문에서 오류가 나는 이유

t1student 테이블.

 

선언된 데이터 타입의 범위를 넘어서는 크기의 데이터가 들어갈 때

 

Foreign Key에 묶여있는 값은 parent table에 그 값이 있는 것들만 데이터로 삽입이 된다. > parent table에 데이터 미리 삽입

 

숫자 데이터 삽입시 처음의 0값이 사라지는 이유? 해결책?

number 데이터 타입의 처음 0은 원래 사라짐 / number자료형을 varchar로 바꾼다(허나 char과 varchar2만 자료가 있을 때 변경 가능)

>

 

2. 

null값 대입 가능,

 

value에 안넣고 후에도 안넣으면 null 자동

 

CREATE TABLE T1STUDENT
    ( 
    SNO NUMBER(12),
    NAME VARCHAR2(60) NOT NULL,
    PHONE NUMBER(12),
    MAJORNO NUMBER(12),
    CONSTRAINT PK_SNO PRIMARY KEY (SNO),
    CONSTRAINT FK_MNO FOREIGN KEY (MAJORNO) REFERENCES T1MAJOR (MNO) ON DELETE SET NULL
    );
desc t1student;
insert into t1student
    values (1111, '홍길동이지요이거지요안녕하세요스무', 01012341234 , '55559999');
insert into t1student
    values (1112, '홍길동이지요이거지요안녕하세요스무', 01012341234 , 55559999);
insert into t1student
    values (113, '홍길동이지요이거지요안녕하세요스무', 01012341234 , null);
insert into t1student
    (sno, name, phone)
    values (114, '홍길동이지요이거지요안녕하세요스무', 01012341234 );


desc t1student; > description,,,

 

option 중요,,,,

not null

on delete set null 이랑 함께 생성은 되는데, 어불성설!

 

3.

id만들기

접속하기

table  만들기

 

 

SELECT * FROM DBA_SYS_PRIVS; - 모든 테이블 권한보기

 

 

 

primary key 는 table 당 하나

 

foreign key  거려면 parent table 에 애는 unique 속성 가지고 있어야해

 

제약조건이 아무것도 없는 순박한 아이는 modify로 제약 조건 줄 수 있다. (add처럼)

원래 unique였던 것을  not null로 바꾸고, 이런 것들이 안되는 거야.

 

create table fff
    (
    f1 number(12) primary key,
    f2 number(20),
    -- null값이면 sys 넣기,
    f3 date default sysdate,
    f4 varchar2(20) check (e4 in( 'homme', 'femme')) --형태 잘 확인하자
    );

 

 

char 과  varchar2 차이