데이터베이스 9강

# user 생성관리
  1.create user
  2. 권한 or Role부여(1. create role
                           2. role(권한부여)
                           3. role user.role부여)
  3. alter user(패스워드, 계정 풀거나 잠금)

# 조건부여
  alter table add constraint

# not null추가
  alter table dept
  modify a constraint dept_a_nn not null;

# table 생성
1. table 구조 만들기(컬럼 결정)
2. 무결성 보존(제약조건 부여)

insert into table()
values ();
1. insert 명시된 column 수 = values 수
2. insert 문장 1개가 1row
3. 문자, 날짜 값 insert 시 ' '
4. 무결성 지켜라(data type, length 맞는 data, 제약조건 지킬 것)

 

 

 

  DataBase : COLUMN을 오른쪽으로 작성해 나가면서 조건한정

  숫자 자리수 설정                  :  COLUMN point  format 0000000
  숫자형식 설정                      :  COLUMN department_id format 9999999
  문자형식 길이 설정 (길이 : 10)  :  COLUMN depart_name format a16

  값이 숫자 : ' ' 없이 값만 작성
  값이 문자 : '06%', 앞뒤 대체문자 표현해줘야 함
  값이 날짜 : '00/00/00 ' 년 월 일

  NOT
  BETWEEN AND
  IN ( , , , ,)
  LIKE ' '

  DESC
  ASC

  AVG( 컬럼 )

  테이블 2개 연결 : JOIN, 외래키와 기본키의 연결
  alias활용 : SELECT ( .컬럼 )으로 컬럼명 모두 설정

  e.hire_date가 빠름 : e.hire_date < m.hire_date

  WHERE : 일반 COLUMN의 조건
  HAVING : 그룹함수의 조건
  그룹함수 쓰임 : 그룹함수가 아닌 값들은 GROUP BY 적용


12. 이름에 a 및 e 글자가 있는 사원의 이름을 조회하시오.

  SELECT last_name
  FROM employees
  WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';


14. 모든 사원들의 이름, 부서 이름 및 부서 번호를 출력하시오.

 SELECT e.employee_id, e.last_name, d.department_id, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id;


15. 30번 부서내의 모든 직업들을 유일한 값으로 출력하시오.
     90번 부서 또한 포함하고, 직업을 오름차순으로 출력하시오.

  SELECT DISTINCT job_id, department_id 
  FROM employees
  WHERE department_id IN (30, 90)
  ORDER BY job_id;


19. 각 직업별, 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력하시오.
     단 최대 급여는 MAX, 최소 급여는 MIN, 급여 총 합은 SUM 및
     평균 급여는 AVG로 출력하고, 직업을 오름차순으로 정렬하시오.

  SELECT job_id, MAX(salary) MAX, MIN(salary) MIN, SUM(salary) SUM, AVG(salary) AVG
  FROM employees
  GROUP BY job_id
  ORDER BY job_id


20. 동일한 직업을 가진 사원들의 총 수를 출력하시오.

  SELECT job_id, COUNT(employee_id)
  FROM employees
  GROUP BY job_id
  ORDER BY job_id


21. 매니저로 근무하는 사원들의 총 수를 출력하시오

  SELECT COUNT(DISTINCT manager_id)
  FROM employees



# SQL
  데이터 검색 SELECT
  데이터 조작 INSERT, UPDATE, DELETE, MERGE
  데이터 정의 CREATE, ALTER, DROP, RENAME, TRUNCATE
  트랜잭션 제어 COMMIT, ROLLBACK, SAVEPOINT
  데이터 제어 GRANT, REVOKE


# PARSE(파싱)
  1. 검증 : 검색하는 열이 테이블에 있는가 체크
  2. 실행계획 : 테이블에서 데이터를 어떻게 가져올 것인지 최적 경로를 설정
  3. 실행 : 데이터를 가져오기


# 정렬만 보고 문자, 숫자 판단
  컬럼헤딩 : 컬럼명은 대문자로 출력된다
  문자, 날짜값 : 왼쪽 정렬
  숫자값 : 오른쪽 정렬


# 그룹함수
  SELECT COUNT(*)
  FROM employees
  WHERE commission_pct IS NOT NULL;


# 그룹 함수는 NULL값을 인식 못함 : AVG 구할 때 NULL을 제외하고 계산
  SELECT AVG (nvl( commission_pct, 0)), SUM(commission_pct)/107
  FROM employees;


# WHERE 서브쿼리
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id
                      FROM employees
                      WHERE employee_id = 141)
AND salary > (SELECT salary
                  FROM employees
                  WHERE employee_id =143);


# HAVING 서브쿼리
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
                             FROM employees
                             WHERE department_id = 50);


# 10 20 30 40 50 60 
  (30, 40) 기준 출력

  IN (30, 40)
 >
  30, 40

  > ANY(30, 40) : 최소값보다 큰값 (30보다 큰)
 >
  40, 50 ,60
 
  < ANY(30, 40) : 최대값보다 작은값
 >
  10, 20 ,30

  > ALL(30, 40) : 전부보다 큰 값
 >
  50, 60

  < ALL(30, 40) : 전부보다 작은값
 >
  10, 20

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
                          (SELECT salary
                           FROM employees
                           WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

# 트랜잭션 : SQL만 트랜잭션 작동할 수 있음
             COMMIT, ROLLBACK


# 트랜잭션 작동
DML : insert, update, delete
        문장 전체를 하나의 트랜잭션으로 인식
DDL : create
       실행과 동시 OP COMMIT
DCL : grant
       실행과 동시 OP COMMIT


# 오늘 날짜
select sysdate from dual;


# 행 추가, 정렬은 안해줌
INSERT INTO departments(department_id, department_name)
VALUES(280, 'tyktyk');

select * from departments

rollback;


# 동시 다수 스크립트 파일 실행
save empinsert.sql
Created file empinsert.sql

C:\oraclexe\app\oracle\product\11.2.0\server\bin
empinsert.txt 실행


deadlock : 서로의 자원을 요구

트랜잭션을 작게 나누어서 사용하는 것을 권장 : 동시성 향상


char(5) : 미리 5칸 저장공간 확보, 성능 향상, 저장공간 낭비, 돈 많이 필요
varchar() : 크기를 보고 천천히 저장공간 할당, 성능저하, 저장공간 좋음


number - 안 씀
number(p) - 정수만 가능
number(p,s) - 최대 9자리, 두번쨰 자리는 소수점자리


# 서브쿼리를 활용하여 employees값 가져오는 테이블 생성
CREATE TABLE empsal80
AS SELECT last_name, salary
FROM employees
WHERE department_id = 80;

# 테이블 설정확인
DESC empsal80;

# 테이블 모든 값 확인
SELECT *
FROM empsal80;

#테이블 삭제
DROP TABLE empsal80;


# 컬럼 추가
ALTER TABLE test5
ADD ( c char(5));


# 데이터 추가
INSERT INTO test5(c)
VALUES('bb');


# 데이터의 변경
UPDATE test5
SET c='bb'
WHERE a = 10;


# 데이터 타입변경
ALTER TABLE test5
MODIFY (c number(5));


# 테이블 COLUMN 삭제
ALTER TABLE test5
DROP COLUMN c;


# 테이블 삭제
# 참조받는 테이블은 삭제불가
DROP TABLE test5;

# 삭제 명령
delete : data value삭제, data 저장구조 유지, table저장구조 유지, rollback으로 복구
    WHERE 사용
truncate : data value삭제, data 저장구조 삭제, table저장구조 유지, rollback으로 복구불가
    WHERE 없음
drop : data value삭제, data 저장구조 삭제, table저장구조 삭제, rollback으로 복구불가


# 제약조건
CREATE TABLE test5
(a number(3),
b number(3) NOT NULL,
c number(3),
CONSTRAINT test5_a_pk primary key(a),
CONSTRAINT test5_c_uk unique(c)
)


# 제약조건 추가
ALTER TABLE test5
ADD CONSTRAINT test5_d_uk unique(d);


# NOT NULL조건 추가는
MODIFY를 사용, ADD 안씀
MODIFY e CONSTRAINT test_e_nn NOT NULL;


DROP TABLE dept CASCADE constraints;
DROP TABLE emp CASCADE constraints;

CREATE table dept
(
deptno number(2),
dname varchar2(14) NOT NULL,
loc varchar2(13),
CONSTRAINT dept_deptno_pk primary key(deptno)
)
;

CREATE TABLE emp
(
empno number(4),
ename varchar2(10) NOT NULL,
job varchar2(9) NOT NULL,
mgr number(4),
hiredate date,
sal number(7, 2) NOT NULL,
comm number(7, 2),
deptno number(2),
CONSTRAINT emp_empno_pk primary key(empno)
)
;

ALTER TABLE emp
ADD CONSTRAINT emp_deptno_fk foreign key(deptno)
REFERENCES dept(deptno);

INSERT INTO dept
VALUES (10, 'Administration', 1700);

INSERT INTO dept
VALUES (20, 'Marketing', 1800);

INSERT INTO dept
VALUES (30, 'Purchasing', 1700);

INSERT INTO dept
VALUES (40, 'Human Resource', 2400);

INSERT INTO dept
VALUES (50, 'Shipping', 1500);



INSERT INTO emp
VALUES (100, 'king', 'AD_PRES', null, '1987-01-17', 24000, null, 10);

INSERT INTO emp
VALUES (101, 'Kochar', 'AD_VP', 100, '1989-09-21', 17000, null, 50);

INSERT INTO emp
VALUES (102, 'DE HAEN', 'AD_VP', 100, '1993-01-13', 17000, null, 50);

INSERT INTO emp
VALUES (103, 'Hunold', 'IT_POG', 102, '1990-07-03', 9000, null, 40);

INSERT INTO emp
VALUES (104, 'Ernst', 'IT_PROG', 103, '1997-07-25', 4800, null, 40);

COMMIT;

# 스크립트실행
@empdept.sql


# 뷰 : 테이블의 데이터 엑세스 제한

# 뷰 생성
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;



# 테이블과 뷰의 차이점
테이블 : 테이블을 생성하면 저장공간 할당, 실제 데이터를 공간에 저장하여 처리
뷰 : 뷰를 생성하는 서브쿼리의 문장을 저장


# 인라인 뷰
SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal
                          FROM employees
                          GROUP BY department_id b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal



# TOP-N 분석
# rownum column : 출력이 되는 행의 순서번호를 출력
                         처음부터만 출력가능
SELECT rownum, last_name, salary
FROM employees;

# 정렬을 DESCENDING으로 연봉설정하고 rownum으로 상위 연봉 출력
# WHERE절이 ORDER BY보다 먼저 출력

SELECT rownum, last_name, salary
FROM (SELECT last_name, salary
FROM employees
ORDER BY salary)
WHERE rownum <4


SELECT rownum, last_name, annsal
FROM (SELECT last_name, 12*salary annsal
FROM employees
ORDER BY annsal)
WHERE rownum <4/


# 컬럼 이름을 작성해야 할 때 함수를 이름으로 포함하면 안됨
SELECT rownum, department_id, avgsal
FROM (SELECT department_id, AVG(salary) avgsal
         FROM employees
         GROUP BY department_id
         ORDER BY avgsal)
WHERE rownum < 6



# 정렬하지 않고 순위처리 (시험 나옴)
SELECT last_name, salary, rank() over  (order by salary)
FROM employees


# 인덱스가 설치되면 table full stack이 아닌 인덱스를 탐색(인덱스 스캔)
# 검색 속도향상(4% 미만의 검색일 때 좋음)
# 범위 검색할 때 인덱스가 좋음
# SELECT가 좋음
# DML은 안 좋음
# 고급 프로그래머는 인덱스로 데이터베이스 튜닝



# 시퀀스
번호를 중복되지 않게 생성
공유 가능
primary key를 생성할 때 할당
한 번 추출된 시퀀스는 다시 추출 안됨, 간격이 발생해서 연속 숫자가 아님

# 시퀀스의  start with는 바꿀 수 없음, 이미 생성한 시퀀스의 중복 발생
  새로 추출하는 값부터 INCREMENT BY 적용

# 시퀀스 제거  : DROP을 해도 추출한 시퀀스는 다시 안 나옴


# 1. 테이블 생성
table : 데이터를 저장할 수 있는 창고
CREATE TABLE test (a numbe(7), b char(7), CONSTRAINT test_a_pk primary key(a));

# 2. 데이터 삽입
INSERT INTO test
VALUES(10, 'b');

INSERT INTO test
VALUES(60, null);


# 데이터 딕셔너리는 오라클 서버가 관리해서 내용을 못 지움

# DBA로 접속 (시험)
conn system/12345

# 최 상위 DBA로 접속
CONN /as sysdba

# 유저 생성 (시험)
CREATE USER test
identified by test;

# 패스워드 변경 (시험)
alter user test
identified by test1;

# 잠긴패스워드 풀어주기 (시험)
alter user test
account unlock;

# 패스워드 잠금
alter user test
account lock;

# 회원가입
grant create session to test;

# 권한부여
grant create table to test;
grant connect, resource to test;

# 권한 회수
revoke create session from test;
revoke connect, resource from test;

# 유저 삭제
drop user test cascade;



# with grant option : DBA로부터 권한을 받은 사용자가 다른 사용자에게 옵션의 권한을 이양할 수 있게 함
# with admin option : 시스템으로부터 권한 이양받음
# 권한은 종속적으로회수됨