데이터베이스 이론 총정리
d+45 2020/11/04 (데이터베이스 관련 프로그램 설치)
<데이터베이스(DataBase)>
- 참고할 사이트 카페 : 900번 문서
- 참고할 이론 파일 : java oracle 교안 pdf
- 설치할 것
1) 오라클(아래 링크 다운로드)
www.oracle.com/database/technologies/oracle-database-software-downloads.html
- 순서 : [Express Edition] - [12버전 다운로드] - [11g버전 다운로드] - setup 파일 설치하기
- 관리자용 비밀번호 : oracle
- 접속테스트 : [Run SQL Command Line] 실행 (cmd창과 같음)
> conn / as sysdba
> show user
2) sql developer(아래 링크 다운로드)
www.oracle.com/tools/downloads/sqldev-downloads.html
다운로드 후 c드라이브에 복사후 압축해제 하기
하위 폴더에 sqldeveloper.exe 파일 클릭하시면 실행됩니다.
2020/11/05 (데이터베이스 진도 시작)
<데이터베이스(DataBase)>
- 정의 : 연관된 데이터를 유기적으로 묶어둔 객체를 의미합니다.
<데이터베이스 객체>
- 데이터베이스의 90%이상이 table 입니다.
- 테이블, 인덱스, 시퀀스, 뷰 등을 말합니다.
| 객체 이름 | 설명 |
| 테이블 | 행과 열로 구성된 2차원적인 표 |
| 시퀀스 | 정수형 번호표 생성기 |
| 인덱스 | 데이터 검색을 빨리 하기 위해 만들어둔 개념 |
| 프로시저 | 반환타입이 없는 객체 |
| 함수 | 반환타입이 있는 객체 |
<데이터베이스의 목표>
1) 데이터 지속적으로 관리/보호
2) 안전성 보장
3) 무결성 보장
<데이터베이스의 특징>
1) 실시간 접근 가능해야 함
2) 동적인 변화
3) 동시에 공용 가능
4) 내용에 의한 참조 가능
<DBMS(DataBase Management System)>
– DBMS의 정의 : 데이터베이스 관리하는 시스템/프로그램
- ex. mySql, Oracle, MS-SQL, DB2 등등
<DBMS의 주요기능>
1) 데이터의 추가/조회/변경/삭제
2) 데이터의 무결성(신뢰도) 유지되어야 함
3) 데이터의 백업/복원 가능
4) 데이터의 보안 기능
5) 트랜잭션 관리 기능 구현
<DBMS의 필수기능>
1) 정의 기능 : 데이터베이스의 논리적/물리적 구조 정의
2) 조작 기능 : 검색/갱신/삽입/삭제
3) 제어 기능 : 정확성/안전성을 유지하는 제어
<SQL(Structured Query Language, 구조적 질의 언어)>
- SQL : 데이터베이스로부터 데이터를 조회/삭제/수정 등의 작업을 수행할 때 사용하는 질의/질문 언어
- 질의(쿼리) : 데이터베이스에게 질문을 던진다. ---> 특정 결과 도출 / 특정 작업 수행
ㄴex. 100번 사원의 급여는 얼마인가요? ---> 데이터 3건을 수정하세요.
<SQL 구성>
- SQL = DDL + DML + DCL +DQL + TCL + ... .
| 구문 | 이름 | 키워드/설명 | 주체 | 트랜잭션 |
| DDL | 데이터 정의어 Data Definition Language |
Create / Alter / Drop / Rename / Comment | Object단위 | Auto Commit : 테이블을 만들었는데 만들지 않은 상태로 돌아가겠다. * rollback 한다고 해서 테이블이 삭제 되지 않음!! |
| DML | 데이터 조작어 Data Manipulation Language |
Insert / Update / Delete | 행(row)단위 | commit; rollback; |
| DCL | 데이터 제어문 Data Control Language |
Grant / Revoke (사용자 관리/권한) | 사용자(user) 단위 |
Auto Commit |
| DQL | 데이터 질의어 | Select 문장 (조회) | - | - |
| TCL | 트랜잭션 Transaction Control |
Commit(영구저장) <-> Rollback(이전작업취소) |
-rollback의 기준은 원소?데이터 -DML의 시작과 동시에 함께 시작됨 |
<SQL의 데이터>
- 컬럼 column의 타입 정하기 : 문자는 Varchar2, 날짜는 Date, 숫자는 number 등.
- 한글은 한 글자가 3byte(바이트)
- NUMBER, NUMBER(숫자), NUMBER(숫자1, 숫자2)
ㄴex. 급여 : number(11, 2)
ㄴ전체 11자리, 소수점 2자리까지를 가리킵니다.
- default (디폴트)는 36자입니다.
<Primary Key(PK, 레코드 고유 식별자)>
*카페900번 - 첨부파일 엑셀파일 참고
- 프라이머리 키 : 레코드(행)를 구별하기 위한 칼럼들을 말합니다.
- not null : 반드시 필수로 입력해야 합니다.
- unique : 중복되어서는 안 됩니다.
- 내부적으로 index가 생성됩니다.
- ex. 사원테이블의 사번, 병원의 환자코드 등.
<인덱스(Index)>
- 인덱스 : 레코드를 순서대로 정렬한 정보를 저장하고 있습니다.
- 인덱스의 역할 : 검색 속도의 향상을 위하여 특정 필드를 사용하여 정렬/저장합니다.
<테이블>
- 테이블 정의 : 행과 열로 구성된 2차원형태의 자료구조
- 테이블 구성 : 행(Row, 레코드), 열(Column, 컬럼)
- 스키마 : 테이블 구조에 대한 정보저장(메타데이터)
- null값 : 비교판단이 불가능한 데이터
- 테이블 이름 : 일반적으로 가독성을 위해 복수로 작성합니다.
ㄴex. employees / students / members ... 등 상품명이나 게시물
- 자바) 한건의 데이터를 불러오기 위해서 bean클래스가 필요합니다.
- 게시물의 경우, 순서를 따져서 list컬렉션 사용합니다.
- 데이터베이스 ---> 테이블작성(자료형필요) ---> bean클래스
<Oracle 사용자 생성>
- 1. 관리자 접속을 위한 아이콘 생성하기
- 2. 신규사용자 '오라맨' 계정 생성해 주기
- 3. '오라맨' 사용자에 대한 권한 주기
- 4. '오라맨' 접속을 위한 아이콘 생성하기
- 5. '오라맨' 워크시트에서 실습하기
1-1. 관리자 접속을 위한 아이콘 생성하기
1) <+> 클릭
2) 사용자 생성하기
- Name : 관리자
- 사용자이름 : sys
- 비밀번호 : oracle (설치시 입력한 비밀번호와 동일합니다.)
- 롤(L) : SYSDBA 선택 (시스템 데이터베이스 관리자)
3) <테스트> 클릭 후 <상태 : 성공> 확인하기
1-2. 관리자 접속을 위한 아이콘 생성하기
* 워크시트 : sql실습을 위한 메모장 형식의 파일
* 세션 : 사용자가 접속한 이후부터 종료하기 까지의 기간
* 파일 저장 시 확장자 : <.sql>
* script : 워크시트에 저장돼 있는 구문들을 의미합니다.
* 오라클 주석 : <--> 빼기 두개를 적으시면 됩니다.
- 보유한 테이블리스트 확인 문장 : select * from tab;
- 실행 : ctrl + enter
- 저장 경로 : c드라이브 - dbjieun
*정상적으로 작동하는지 재확인 : 종료 - 오라클 재시작 - 파일열기 - 실행 - 내가 소유하고 있는 관리자 파일리스트
2. 신규사용자 'oraman' 계정 만들어 주기
* 관리자 워크시트에서 실습 시작
--새로운 사용자 생성 후 계정 풀어줍니다. (oracle : 비밀번호)
--account unlock : 바로 로그인 가능하게 계정을 풀어줌
1) create user oraman
identified by oracle
account unlock;
--tablespace 변경 (oraman : 아이디)
2) alter user oraman default tablespace users;
3. '오라맨' 사용자에 대한 권한 주기
--oraman에게 접속할 수 있는 권한을 줍니다.
3) grant connect, resource to oraman;
--ex. grant connect, resource to oraman, soshi, bigbang;
- grant : 권한을 수여/부여해주다.
- to : ~에게, from : ~로부터
- role : 역할
- sysdba : 데이터베이스 관리자를 의미하는 롤
- connect : 데이터베이스에 접속할 수 있는 권한
- resource : 테이블 생성할 수 있는 능력
- 현재 사용자 목록을 정렬하여 보여주세요.
- 사용자가 제대로 생성됐는지 확인
4) select username from dba_users order by username;
--사용자계정 삭제
5) drop user oraman cascade;
4. 사용자 접속을 위한 아이콘 생성하기
* 사용자의 예시는 오라맨, 소시, 빅뱅.
1) 새 접속 <+> 클릭
2) 사용자 생성하기
- Name : 오라맨 / 소시 / 빅뱅
- 사용자이름 : oraman / soshi / bigbang
- 비밀번호 : oracle / omygod / abc123
- 롤(L) : 기본값 선택
3) <테스트> 클릭 후 <상태 : 성공> 확인하기
5. '오라맨' 세션에서 계속 실습하기 (핵심은 테이블)
1) 테이블 문법 작성
- Oracle pdf p.25~ / 오라맨 세션에 테이블 문법에 따라 유형을 작성합니다.
- 해당 작업은 java에서의 bean 클래스 작성과 같습니다.
- 기본값 : default + 설정하려는 값
ㄴex. salary number defalut 100;
- sysdate : 현재시각을 의미하는 오라클 내장함수
- 간략한 조회 : select * from tab;
desc employees;
select * from employees;
- 자세한 조회 : 오라맨 > 테이블 > EMPLOYEES > 열 tab
ㄴNULLABLE : null 허용 여부,
ㄴCOLUMN_ID : 만든 순서
2) 행추가
ex. insert into employees(id, name, password, gender, birth, marriage, salary, address, manager)
values('yusin', '김유신', 'abc1234', '남자', '1990/12/25', '결혼', 220, '용산', null);
3) 테이블 복사
- 구조 부분 복사하여 테이블 생성 : create table 뉴테이블이름 as select 컬럼1, 컬럼2 from 현테이블이름;
ㄴex. create table Ex01 as select name, age from Example;
- 구조 전체 복사하여 테이블 생성 :
4) 테이블수정
- 컬럼 추가 : alter table 테이블_이름 add(추가내용);
ㄴex. alter table employees add (hphone varchar2(15));
- 컬럼 수정 : alter table 테이블_이름 modify(구조변경);
ㄴex. alter table employees modify (hphone number);
- 컬럼 이름 변경 : alter table 테이블_이름 raname column 전이름 to 뉴이름;
ㄴex. alter table employees rename column hphone to handphone;
- 컬럼 삭제 : alter table 테이블_이름 drop column 컬럼이름;
ㄴex. alter table employees drop column handphone;
- 테이블 이름 수정 : rename 전테이블_이름 column 뉴이름;
ㄴex. rename employees to emp;
<오류발생>
- 오류 보고(자릿수)
ORA-12899: value too large for column "ORAMAN"."EMPLOYEES"."ID" (actual: 30, maximum: 20)
글자수 최대 20자까지인데 이를 초과함.
- 방법 : alter ~ modify 수정 > 구조변경(자릿수)
- 오류 보고(타입변환)
데이터가 비어있지 않아서 숫자로 바꿀 수 없음.(java의 NumberformatException)
ORA-01439: column to be modified must be empty to change datatype
01439. 00000 - "column to be modified must be empty to change datatype"
- 방법 : alter table employees drop column handphone;
2020/11/06 (DML)
<데이터 조작어 (DML)>
- DML : 테이블의 행(row)에 대해 추가(Insert)/수정(Update)/삭제(Delete) 등을 수행하는 언어
- Insert : 테이블에 새로운 행 추가
- Update : 테이블의 내용 수정
- Delete : 테이블의 특정 행 삭제
★DML의 트랜잭션인 commit 으로 꼭 마무리하기
<Insert (행추가)>
- 내용 : 해당 테이블에 행(row)을 추가합니다.
- 사용문법 : insert into 테이블이름 (컬럼1, 컬럼2, ..., 컬럼n) values
- 특징 :
1) 하나의 행만 삽입
2) 컬럼목록을 지정하지 않으면 만든 순서대로 데이터 추가됨
3) 기술된 컬럼 순서대로 values에 지정된 값이 추가됨
- 주의사항 :
1) 열거하는 값의 개수 및 데이터 타입은 반드시 동일해야 함.
2) 문자와 날짜 값은 ' ' 외따옴표 사용
3) 최종 작성 후 반드시 커밋(commit)을 수행
4) 수행결과를 취소하려면 롤백(rollback)을 수행
<Insert 예시>
- 추가 방법1 : 컬럼이름 명시하지 않고 추가하기
ㄴex. insert into emp values ('yusin', '유신, 'abc1234', '남자', '1990/12/25', '결혼', 220, '용산', null);
- 추가 방법2 : 컬럼이름 명시하는 경우, 개수와 타입 맞추기
ㄴex. insert into emp (id, name, password, gender, birth, marriage, salary, address, mgr)
values('yoon', '윤봉길', 'abc123', '남자', '1990/12/25', '미혼', 230, '용산', 'yusin');
- 추가 방법3 : 필요없는 컬럼을 명시하지 않는 경우(mgr없이)
ㄴex. insert into emp (id, name, password, gender, birth, marriage, salary, address)
values('soon', '유관순', 'abc123', '여자', '1990/12/25', '미혼', 240, '마포');
- 추가 방법4 : 컬럼이름을 임의로 명시하는 경우
ㄴex. insert into emp (id, salary, mgr, address, name, password, gender, birth, marriage)
values('shin', 215, 'kim9', '서대문', '신사임당', 'abc123', 여자', '1990/12/25', '미혼');
<insert ... select구문>
insert select
- 기존 테이블에서 필요한 데이터만을 갖고 새로운 테이블을 생성 & 복사해감
- select구문에 의하여 조회된 결과를 다른 테이블에 추가하는 구문
- 사용문법 :
create table 뉴테이블이름 as select 값1, 값2, ... 값n from 기존테이블 where 1 = 2;
insert into 뉴테이블이름(값1, 값2, ... 값n) select 값1, 값2, ... 값n from 기존테이블 where 값4;
ㄴ ex. insert ... select 구문으로 '여자'만 'emp06' 테이블에 insert 하겠습니다.
create table emp06
as select name, salary, address
from emp
where 1 = 2;
insert into emp06(name, salary, address)
select name, salary, address
from emp
where gender = '여자';
---> 결과적으로 성별이 '여자'인 사람들의 이름, 급여, 주소 까지만.
<UPDATE (행수정)>
- 내용 : 이미 저장된 행(ROW)의 데이터 수정하기
- 사용문법 : update 테이블이름 set 컬럼1=값1, 컬럼2=값2, ... 컬럼n=값n [where조건식] ;
- where절(clause) : 전체데이터에서 일부 행(row)을 선택하고자 할 때 사용하는 구문입니다.
- 연산자 :
1) 관계(비교)연산자, <=>같음, <>다름, >, >=, <, <= 등.
2) and<&&> / or<||> / not<!>
3) <null>은 연산을 해도 결과가 <null>입니다.
- <in> 키워드 :
1) <or>연산자 대신 사용합니다.
2) 간략히 표현할 목적으로 만든 키워드(~를 포함하는)
3) 문법 : <where> 컬럼이름 <in> <값1, 값2, ... , 값n>
- 주의사항 :
1) 문자와 날짜 값은 ' ' 외따옴표 사용
2) where절 지정하지 않으면 모든행 수정됨
<Update 예시>
- 수정 방법1 : 컬럼 1개 변경하기
- 예시1 : 모든 사원들의 급여를 100으로 변경합니다.
ㄴex. update employees set salary = 100;
- 수정 방법2 : 여러개의 컬럼 변경하기
- 예시1 : 모든 사원들의 급여를 500으로, 비밀번호는 'abc1234'로 변경합니다.
ㄴex. update employees set salary = 100, password = 'abc1234';
- 예시2 : '안중근'의 급여를 400으로 변경합니다.
ㄴex. update employees set salary = 400 where id = 'an';
- 예시3 : '황진이'의 급여를 50더하고, 주소를 '마포'로 변경합니다.
ㄴex. update employees set salary = (salary+50), address = '마포' where id = 'hwang';
- 예시4 : 모든 직원들의 급여를 10% 인상합니다.
ㄴex. update employees set salary = (salary * 1.1);
- 수정 방법3 : and / or / not 활용하여 컬럼 변경하기
- 예시1 : 관리자가 '김유신'이고, 급여가 250이상인 사원들의 급여를 700으로 변경합니다.
ㄴex. update employees set salary = 700 where mgr = 'yusin' and salary >= 250;
- 수정 방법4 : in키워드를 활용하여 컬럼 변경하기
- 예시1 : 'id'가 '김구' 이거나 '황진이'인 경우
ㄴex. where id in ('kim9', 'hwang');
ㄴex. where id = 'kim9' or id = 'hwang';
- 예시2 : 관리자가 김유신 이거나 유관순인 사원들의 급여를 555로 변경합니다.
ㄴex. update employees set salary = 555 where mgr in ('yusin', 'soon');
- 예시 3 : ???
ㄴex. update employees set salary=50, hiredate=add_months(sysdate, -3) where mod(sabun, 2) = 1;
<Insert ... select구문>
- select 구문에 의하여 조회된 결과를 다른 테이블에 추가하는 구문
- 문법 : insert into 뉴테이블(값1, 값2, ... , 값n)
select 값1, 값2, ... , 값n
from 기존테이블 where 추가할 값;
- 예시1 : 성별 '남자'만 emp05테이블에 insert합니다.
ㄴex. insert into emp05(id, name, salary, gender)
select id, name, salary, gender
from emp where gender = '남자';
- 예시2 : 성별 '여자'만 emp06테이블에 insert합니다.
ㄴex. insert into emp06(name, salary, address)
select name, salary, address
from emp where gender = '여자';
<DELETE>
- 행삭제
- 사용문법 : delete from 테이블이름;
- ex1. 사원 '안중근'을 삭제
ㄴdelete from emp where id = 'an';
- ex2. 관리자가 김유신인 사원들 삭제
ㄴdelete from emp where mgr = 'yusin';
<DELETE (행삭제)>
- 내용 : 테이블의 특정 행을 삭제합니다.
- 사용문법 : delete from 테이블이름 [where 조건식];
ㄴex. delte from students where id = 'hong';
- 주의사항 :
1) 실제내용인 데이터만 삭제되고 테이블 자체는 삭제되지 않습니다.
2) delete 명령어로 컬럼 column은 삭제 불가능합니다.
컬럼의 삭제는 alter table 명령어를 사용해야 합니다.
- 예시1 : 모든 사원을 삭제합니다.
ㄴex. delete from emp;
- 예시2 : 관리자가 김유신인 사원 중에서 급여가 200이상인 사원을 삭제합니다.
ㄴex. delete from emp where mgr in ('yusin') and salary >= 200;
- 예시3 : 주소가 '서대문'이거나 '강남'인 사람을 제외하고 삭제
ㄴex. delete from emp where address not in ('서대문', '강남');
2020/11/09 (DQL)
pdf p.62
<데이터 질의어 (DQL)>
- <*> : all columns
- 알리야스(alias) : 별칭
- 문자열 결합 연산자 <||>
<null>
- null : 비교판단이 불가능한 정의할 수 없는 어떤 값
- null != 값이 없는 것
- be동사 사용
ㄴ예시1 : 급여 책정이 안 된 사원들을 출력해보세요.
ㄴex. select * from emp where salary is null;
- null 데이터의 연산 결과는 null
ㄴex. select name, salary, 12*salary from emp; ---> null
<pdf p.62 SQL 구문 작성 지침>
- 여러 라인에 걸쳐 작성 가능
- 절과 절은 나뉘어 쓰기
- 들여 쓰기는 가독성을 향상 시킴
- 단독 라인은 세미콜론(;) 대신 기호</> 사용 가능
<비교연산자>
*pdf p.72
- mod(a, b) : 나머지 연산자, a를 b로 나눈 나머지
- between A and B : A, B 양쪽 값을 포함, 이 사이에 존재하는 모든 값 추출
- in(set) : set 목록 중 하나와 일치, <or>연산자와 조합
- like : 문자 패턴 일치, Wildcard인 <_>나 <%> 사용
- is null : null 데이터 항목만 조회
- is not null : null 데이터 아닌 항목만 조회
<between A and B>
- 예시1 : select * from emp where salary >= 220 and salary <=400;
---> between구문으로 대체
ㄴex. select * from emp where salary between 220 and 400;
- 가나다순 정렬
ㄴex. select * from emp where name between '사' and '자';
<like 연산자>
*pdf p.73
- like : 검색하고자 하는 값을 명확히 모르는 경우 사용, 유사패턴방식으로 검색 가능
- Wlidcard(%, _) : <%>는 0개 이상의 문자, <_>는 반드시 1개 이상의 문자
- 예제1 : 사람이름의 마지막이 '동' : <_동>
ㄴex. select * from emp where name like '_동';
- 예제2 : 이름에 '신'이 포함된 사원을 조회해 보세요.
ㄴex. select * from emp where name like '%신%';
- 예제3 : 이름의 두번째 글자에 '순'이 포함된 사원
ㄴex. select select * from emp where name like '_순%';
<order by 정렬>
*pdf p.78
- ASC : 오름차순(날짜는 가장 먼 시점부터 정렬)
- DESC : 내림차순(날짜는 가장 가까운 시점부터 정렬)
- 날짜는 내부적으로 숫자로 계산합니다.
- 예시1 : alias를 사용한 데이터 정렬, 이름과 연봉을 출력하되 연봉이 높은 순으로 정렬하세요.
ㄴex. select name, 12*salary as annsal from emp order by annsal desc;
- 예시2 : 컬럼번호를 사용한 데이터 정렬
ㄴex. select name, 12*salary as annsal from emp order by 2 desc;
ㄴ1번 컬럼 : name, 2번 컬럼 : annsal ---> 2번 컬럼의 내림차순 = 연봉이 높은 순
<부정어>
- not in / not between / not like / is not null
- 예시1 : (not in) '김구'와 '김유신'을 제외한 모든 사원들
ㄴex. select * from emp where id not in ('kim9', 'yusin');
- 예시2 : (not between) 급여가 220과 230 사이에 포함되지 않은 사원들
ㄴex. select * from emp where salary not between 220 and 230;
- 예시3 : (not like) 성씨가 '김'씨가 아닌 모든 사원들
ㄴex. select * from emp where name not like '김%';
- 예시3 : (is not null) 연봉 협상이 완료된 사원들
ㄴex. select * from emp where salary is not null;
<함수(function)>
<DUAL 테이블>
*pdf p.85~
- dual 테이블 : sys 관리자가 소유하고 있는 1행 1열의 테이블
- 필요한 이유 : 함수의 결과물, 산술연산의 결과를 점검할 때 필요한 듀얼 테이블.
ㄴex. select 4 * (3+2) from dual; ---> 출력값 : 20
ㄴex. select power(2, 10) from dual; ---> 출력값 : 1024
www.oracle.com/pls/db102/homepage
<문자열 함수>
- lower / upper / initcap / concat / dubstr / instr / lpad / rpad / replace / ltrim / rtrim
<substr() 문자열 부분 추출>
- substr() : ~번째부터 ~번째까지 추출하기
- 사용형식 : select substr(데이터이름, N, M) from 테이블이름;
- 예시1 : 1번째부터 끝까지 추출하기
ㄴex. select substr(password, 1) from emp;
- 예시2 : 1번째부터 5번째까지 추출하기
ㄴex. select substr(password, 1, 5) from emp;
- 예시3 : 뒤에서부터 역순으로 5번째까지, 2개만 추출하기
ㄴex. select substr(password, -5, 2) from emp; ---> 출력값 : c1 (abc1234)
<length() 문자열 길이 파악>
- length() : 문자열의 길이
- 사용형식 : select length(데이터이름) from 테이블이름;
- 예시1 : 사원의 아이디와 이름은 각각 몇 글자인가요?
ㄴex. select id, length(id), name, length(name) from emp; ---> id와 name의 글자 수
<instr() 리턴>
- instr() : 지정한 문자 위치를 숫자값으로 리턴
- 사용형식 : select instr(데이터이름, '요소') from 테이블이름;
- 예시1 : 사원의 아이에 'o'가 들어있나요?
ㄴex. select id, name, instr(id, 'o') from emp;
<replace() 문자열 치환함수>
- replace() : 문자열 치환함수
- 사용형식 : select replace(데이터이름, '원래값', '바꿀값') from 테이블이름;
ㄴex. select replace(time, 'am', 'pm') from emp;
ㄴex. select id, replace(id, 'o', 'x') from emp;
<L/Rtrim() 왼쪽/오른쪽 특정문자 삭제>
- L/Rtrim() : 왼/오른쪽 특정문자 삭제
- 사용형식 : select L/Rtrim('특정문자...문자열...특정문자', '특정문자') from dual;
- select Rtrim('xxxabc123xxx', 'x') from dual;
---> 출력값 : xxxabc123 (Right 'x' 삭제)
- select Ltrim('xxxabc123xxx', 'x') from dual;
---> 출력값 : abc123xxx (Left 'x' 삭제)
* select trim(' abc 123 ') from dual;
<reverse 역순으로 조회(뒤집기)>
- reverse() : 역순으로 조회
- 사용형식 : select reverse(데이터이름) from 테이블이름;
- 예시1 : 사원의 id와 뒤집어진 id를 조회해 보세요.
ㄴex. select id, reverse(id) from emp;
* select reverse(name) from emp;
---> 한글은 깨져서 출력됨.
<숫자 함수>
-round / trunc / mod
<round 함수, 반올림>
- round() : 숫자값을 반올림
- 예시1 : 양수 또는 0인 경우, 소수점 오른쪽 지정 자리수까지 반올림,
그리고 음수인 경우, 소수점 왼쪽 지정 자리수에서 반올림
ㄴex. select salary, round(salary), round(salary, 1), round(salary, -1) from emp;
* round(salary), round(salary, 1) 둘은 오버로딩 관계
| salary | round(salary) | round(salary, 1) | round(salary, -1) |
| 671.55 | 672 | 671.6 | 670 |
<trunc, 절삭 함수>
- trunc() : 숫자값을 절삭함
- trunc() 함수는 round() 함수와 동일한 방식으로 작용하되, 무조건 절삭해 버립니다.
| salary | round(salary) | trunc(salary) |
| 671.55 | 672 | 671 |
<mod 나머지 반환 함수>
- mod(n, m) : n을 m으로 나눈 나머지를 구해 줍니다.
- 예시1 : 급여가 10의 배수이면...
ㄴex. where mod(salary, 10) = 0;
- 예시2 : 사원의 id 길이가 5의 배수인 사원들만 출력해 보세요.
ㄴex. where mod(length(id), 5) = 0;
- 기타 예시
ㄴex. select mod(14, 5) from dual;
ㄴex. select mod(1700, 500) from dual;
- abs / ceil & floor / sqrt
<abs, 절대값 함수>
*java의 math 참고
- abs() : 절대값 구하기
ㄴex. select id, name, salary, abs(salary) from emp;
<ceil 올림, floor 버림 함수>
- ceil() : 올림 기능
- floor() : 버림 기능
ㄴex. select id, name, salary, ceil(salary), floor(salary) from emp;
<sqrt() 함수>
- sqrt() : root를 구해주는 함수
*root 루트 : 거듭 제곱근
ㄴex. √9 = √3*√3 = 3
- 예시1 : 모든 급여에 대하여 절대값으로 변경하고, 루트를 씌운 결과를 출력하세요.
ㄴex. select name, sqrt(abs(salary)) from emp;
* ex. select round(sqrt(5), 3) from dual;
<power(a, b) 제곱 함수>
- power(a, b) : a의 b제곱을 수행
ㄴex. select power(2, 3) from dual;
<sign(n) 부호출력 함수>
- sigh(n) : 숫자 n의 부호를 출력
ㄴex. select name, salary, sign(salary) from emp;
2020/11/10 (날짜 함수)
<날짜 함수>
* pdf p.89
- months_between / add_months / next_day / last_day / round / trunc
- 예제
select sysdate from dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH';
select sysdata from dual;
*날짜 변환 예시
| 변환전 | 변환값 |
| 1970/01/01 | 0 |
| 1970/01/02 | 1 |
| 1970/01/06 | 5 |
| 저녁11시 - 오전11시 | 0.5 |
| 하루 | 1.0 |
| 반나절 | 0.5 |
| 1시간 | 1/24 |
| 1분 | 1/24/60 |
| 1초 | 1/24/60/60 |
<months_between>
- months_between : 두 날짜 사이의 월수를 반환
- round, months_between
ㄴex. round(months_between(sysdate, birth), 3)
<변환 함수>
* pdf p.90~
- TO_CHAR / TO_DATE / TO_NUMBER
<to_char 문자변환>
- to_char : 날짜/숫자 ---> 문자
- 종류 : YYYY, YY, MM, MON, DAY ... 등등
- 형식 : select sysdate, to_char(sysdate, 'yyy-mm-dd') from dual;
- 형변환 : 암시적 / 명시적 형변환
- 암시적 예시 :
ㄴex. 'abc' || 123 ---> abc123
ㄴex. 100 + '100' ---> 200
ㄴex. 100 + to_number('100') ---> 200
- 명시적 예시 :
ㄴex. 'abc' || to_char(123) ---> abc123
* 형식요소에 없는 항목은 ""쌍따옴표를 사용합니다.
- 예시1 : 생일은 'yyyy年mm月dd日' 형식으로 출력해야 합니다.
ㄴex. select name, to_char(birth, 'yyyy"年"mm"月"dd"日"') as "생일 정보" from emp;
<to_date 날짜변환>
- to_date : 문자 ---> 날짜
<to_number 숫자변환>
- to_number : 문자 ---> 숫자
<NVL 일반함수>
- nvl / nvl2
1. nvl
- 사용형식 : nvl(식1, 식2)
- 설명 : 식1이 null이라면 식2, 식1이 not null이라면 식1
- 자바의 <if>구문과 비슷합니다.
- 예시1 : select nvl ('가나다', '하하') from dual;
ㄴ식1('가나다')을 보고 따짐. 식1은 not null ---> 출력값 : 식1인 '가나다'
- 예시2 : select nvl (null, '하하') from dual;
ㄴ식1(null)은 null ---> 출력값 : 식2인 '하하'
2. nvl2
- 사용형식 : nvl2 (null여부, null아닐 때 수행, null일 때 수행)
- 설명 : 식1이 null이면 식3, not null이면 식2
- 자바의 <if ... else>구문과 비슷합니다.
- 예시1 : 모든 사원들의 이름과 급여 조회, 급여 존재여부에 대해 코멘트 작성
ㄴex. select name, salary, ★nvl2(salary, '급여존재', '급여null') from emp;
3. nullif ♣
- 사용형식 : nullif(데이터, null로 대체할 특정값)
- 예시1 : 모든 사원들의 이름과 결혼여부 조회, '미혼'은 null 값으로 대체
ㄴex. select name, marriage, ★nullif(marriage, '미혼') as result from emp;
4. coalesce ♣
- 사용형식 : coalesce(데이터1, 데이터2, '어떤값')
데이터1 null이면 데이터2로 대체, 데이터2도 null이면 null로 대체
- 예시1 : 데이터1이 null 이면 데이터2로 대체, 데이터2도 null 이면 ' '로 대체
ㄴex. select name, ★coalesce(mgr, to_char(salary), '모두null') as result from emp;
<case 표현식>
- case 조건식 : sql문장에서 제어문 역할을 수행
- 사용형식 :
case 조건식
when 비교_표현식1 then 리턴표현식1
[ when 비교_표현식2 then 리턴표현식2
when 비교_표현식n then 리턴표현식n
else end 표현식 ] ---> else는 default와 같음
from employees;
- 예시1
ㄴex. select name, managerid,
case managerid
when 1 then '소녀시대'
when 2 then '빅뱅'
when 3 then '원더걸스'
else '관리자' end as result
from employees;
2020.11.11
<집합연산자>
- 합집합 / 중복허용 / 교집합 / 교환법칙
- union / union all / intersect / minus
<예제 테이블 set_a 와 set_b>
1. 테이블 set_a와 set_b 생성하기
create table set_a (val number); ♣
create table set_b (val number);
2. 테이블 set_a와 set_b 데이터 추가하기
- set_a insert
insert into set_a(val) values(1);
insert into set_a(val) values(2);
insert into set_a(val) values(3);
insert into set_a(val) values(4);
- set_b insert
insert into set_b(val) values(1);
insert into set_b(val) values(2);
insert into set_b(val) values(3);
insert into set_b(val) values(4);
insert into set_b(val) values(5);
3. 집합연산자 출력하기
- union : 중복 행 제거하고 결과 출력 (합집합)
ㄴex. select * from set_a union select * from set_b;
- union all : 중복허용, 모든 결과 출력 (중복허용)
ㄴex. select * from set_a union all select * from set_b;
- intersect : 두개 쿼리를 모두 만족하는 결과 중 중복행 제거 후 출력 (교집합)
ㄴex. select * from set_a intersect select * from set_b;
- minus : A에서 B를 제외한 A의 결과를 출력 (교환법칙 미성립, A-B != B-A)
ㄴex. select * from set_a minus select * from set_b;
ㄴex. select * from set_b minus select * from set_a;
<집합연산자를 활용한 테이블 조회>
- 예시 : emp테이블에서 '남자'만 추출하여 table01 생성,
단, id가 'kim9'인 사원은 제외, 추가로 '여자' 중에서 '미혼자'를 삽입
-- 테이블 생성
create table01
as
select * from emp
where gender = '남자' and id <> 'kim9';
-- 데이터 추가
insert into table01
select * from emp
where gender = '여자' and marriage = '미혼';
- 합집합
- 예시1 : table01과 table02 의 합집합
ㄴex. select id, name, address from table01
union
select id, name, address from table02;
- 교집합
- 예시1 : table01과 table02 의 교집합
ㄴex. select id, name, address from table01
intersect
select id, name, address from table02
ordey by name desc;
- 교환법칙 성립에 대한 확인
- 예시1 : table01과 table02 의 교환법칙 성립
ㄴex. select id, name, address from table01
minus
select id, name, address from table02
order by name desc;
ㄴex. select id, name, address from table02
minus
select id, name, address from table01
order by name desc;
- 파생컬럼도 가능, 별칭은 첫 번째 구문에만 작성함
ㄴex. select id, name, address, '구사원' as remark from table01
union
select id, name, address, '신사원' from table02
order by name desc;
* 두번째 신사원 구문 작성 시 컬럼 순서는 위와 동일한 순서로 작성 지향.
<그룹집계함수>
* pdf p.99
<그룹집계함수>
- 총합 / 중복제거 / 평균 / 총점 / 최소 / 최대
- count / distinct / avg / sum / min / max
<그룹집계함수를 활용한 조회 예제>
1. count
- count(*) : 모든 사원의 수
ㄴex. select count(*) from emp;
- count(컬럼) : 급여가 null이 아닌 사원의 수
ㄴex. select count(salary) from emp; -- (salary의 행수)
ㄴex. select count(marriage) from emp; -- (컬럼은 총 몇 행인지)
- count(*) : 급여가 null인 사원의 수
ㄴex. select ( count(*) - count(salary) ) from emp; -- (salary의 null 행수)
- count(*) : 모든 사원의 수
ㄴex. select count(*) from emp;
<count(*) 오류 및 수정>
- 아래와 같이 실행하면 에러,
ORA-00923 : 오류발생
ㄴex. select gender
count(*)
from emp;
- 해결방법 : group by
ㄴex. select gender,
count(*)
from emp
group by gender;
<where조건절은 group함수에서 사용 불가능>
- 아래와 같이 실행하면 에러,
ORA-00934 : 오류발생
ㄴex. select gender,
count(*)
from emp
where count(*) > 10
group by gender;
- 해결방법 : having
ㄴex. select gender,
count(*)
from emp
group by gender
having count(*) > 5;
2. distinct
- 범주형 데이터에 적합
- ex. marriage의 경우, 결혼/미혼/이혼 등의 카테고리 개수
DISTINCT를 이용한 중복 제거
중복된 레코드를 제거하고 유일한 값에 대해서만 결과를 출력하려면 DISTINCT를 사용한다. DISTINCT를 사용하지 않은 [결과 1] 에서는 동일한 값을 가지는 레코드가 포함되어 있지만, DISTINCT를 사용한 [결과 2]에서는 중복된 레코드가 제거되었다.
즉, DISTINCT를 사용하는 경우, DBMS엔진 내부에서는 데이터에 대해 정렬 연산을 수행하므로 출력된 순서가 알파벳순으로 정렬된 것을 확인할 수 있다.
예제3
CODE 테이블에서 중복을 제거하여 nation_code, address 컬럼만 조회하라.
|
1 2 3 |
SELECT DISTINCT nation_code, address FROM stadium WHERE nation_code='KOR'; |
결과 테이블에서 유일한 레코드만 출력되게 하려면 컬럼 리스트 앞에 DISTINCT 키워드를 사용한다.
- count(distinct 컬럼이름) : 해당 컬럼은 몇 가지 유형의 데이터가 있나요?
ㄴex. select count(distinct marriage) from emp;
3. avg / avg nvl
- avg(컬럼이름) : 해당 컬럼의 평균은?
ㄴex. select avg(salary) from emp; -- (salary의 평균값)
- avg( nvl(컬럼이름, 치환값) ) : 급여의 평균을 구하되, 급여가 null인 사원은 100으로 치환
ㄴex. select avg( nvl(salary, 100) ) from emp; -- (salary의 평균값, null은 100으로 계산)
4. sum
- sum(컬럼이름) : 해당 컬럼의 총합은?
ㄴex. select sum(salary) from emp; -- (salary의 총금액)
5. max
- max(컬럼이름) : 해당 컬럼의 최대값은?
ㄴex. select max(salary) from emp; -- (salary의 최대값, 최대급여)
ㄴex. select max(id) from emp; -- (id의 오름차순, 가장 먼저 조회됨)
6. min
- min(컬럼이름) : 해당 컬럼의 최소값은?
ㄴex. select min(salary) from emp; -- (salary의 최소값, 최소급여)
ㄴex. select min(id) from emp; -- (id의 내림차순, 가장 나중에 조회됨)
<그룹집계함수를 활용한 예제2>
- 남녀 각각 급여의 총합과 최소 급여를 조회,
단, 급여가 null이면 100으로 치환하여 계산함
select gender,
sum( nvl(salary, 100) ) as sumsal,
min( nvl(salary, 100) ) as minsal
from emp
group by gender;
- 결혼 유형은 각각 몇명인가요?
select marriage,
count(*) as cnt
from emp
group by marriage;
- 성별, 결혼유무 각각 몇명인가요?
* group by 컬럼과 select 컬럼 동일하게 작성
select gender, marriage,
count(*) as cnt
from emp
group by gender, marriage
order by gender, marriage desc;
<having>
- having은 조건절이자 group by에 종속됨
- 예시1 : 성별, 결혼유무 각각 몇 명인지 조회하되, 3명 이상만 조회
ㄴex. select gender, marriage,
count(*) as cnt
from emp
group by gender, marriage
having count(*) >= 3
order by gender, marriage desc;
- 예시2 : 생일이 존재하는 기혼자의 남녀별 급여의 총액을 조회
ㄴex. select gender,
sum(salary) as sumsal
from empwhere birth is not null and marriage = '결혼'
group by
gender;
- 예시3 : 결혼 유무별 최대급여와 최소급여의 차이가 100이상인 데이터 조회
ㄴex. select marriage, max(salary), min(salary)
from emp
group by marriage
having ( max(salary) - min(salary) ) >= 100;
<부조회(서브쿼리)>
서브쿼리를 사용하는 경우
- 구절의 순서를 바꿔야 하는 경우
select * from member where rownum between 1 and 10
최신 등록순으로 정렬한 결과에서 상위 열명을 원하는 경우라면?
- 아래와 같이 입력하면 오류가 나타남(ORA-00933: SQL명령어가 올바르게 종료되지 않았습니다.
- 왜냐면 순서가 잘 못 되었기 때문.(정렬이 최우선이지만 아래는 오류임..그래서 서브쿼리 사용)
select * --(4) 조회함
from notice --(1)데이터를 가져 옴
order by regdate desc --(2)데이터를 정렬함
where rownum between 1 and 5; --(3)정렬된 결과물(데이터)을 가지고
select *
from ( select * from member order by regdate desc)
where rownum between 1 and 10
--from절에는 항상 테이블만 오는 것이 아님
격자형 데이터나 결과물이 있으면 from절에서 사용가능함
--위에서 소괄호가 나타내는 것이 서브쿼리가 됨
ex.나이가 30이상인 회원목록을 조회하시오.
select
* from member
where age >= 30;
ex.평균나이 이상인 회원
select
* from member
where age >= ( select avg(age) from member ) --먼저 평균나이 구하는 쿼리
<join>
<참조 무결성 제약 조건>
<게시물>
<시퀀스 Sequence>
ex. 은행 번호표, 1씩 증가
<시퀀스 초기화 방법>
1. 시퀀스의 현재값을 확인합니다.
select last_number from user_sequences where sequence_name = '시퀀스명';
2. 시퀀스의 increment를 현재값만큼 빼도록 설정합니다.
(아래는 현재값이 999일 경우)
alter sequence 시퀀스명 increment by -999;
3. 시퀀스에서 다음값을 가져 옵니다.
select 시퀀스명.nextval from dual;
4. 현재값을 확인해보면 -999 만큼 증가 했습니다.
select 시퀀스명.currval from dual;
5. 시퀀스의 increment를 1로 설정합니다.
alter sequence 시퀀스명 increment by 1;
2020.11.12
<join하기>
<Ansi join / oracle join>
- 예제1 : 게시물을 작성한 사람의 이름과 글제목 출력
select emp.name, boards.subject
from emp, boards
where emp.id = boards.writer ;
- Ansi join
select emp.name, boards.subject
from emp join boards
on emp.id = boards.writer;
- Oracle join : 별칭 사용 시
select e.name, b.subject
from emp e, boards b
where e.id = b.writer;
2020.11.13
<뷰 view>
- 정의 : 다른 테이블/뷰에서 파생된 논리적 가상테이블
- 특징 : 쿼리(조회를 위한 select문)문장을 저장하고 있는 객체
1) 테이블 전체가 아닌, 일부분만 보여주기 위해 뷰를 사용하여 보여줌
2) 제한성 : 직접전인 데이터 엑세스 제한
3) 용이성 : 복잡한 질의를 쉽게 만들기 위함, (1개 테이블 - 뷰의 개수 제약 없음)
<권한>
- 시스템 권한 : 관리자 -> 오라클
- 객체(테이블, 뷰) 권한 : 오라클 -> 빅뱅
<권한 grant / revote>
- 시스템 권한 부여 (관리자 세션)
ㄴex. grant create view to oraman;
- 시스템 권한 회수 (관리자 세션)
ㄴex. revoke create view to oraman;
- 객체 권한 부여 (오라클 세션)
ㄴex. grant select on view01 to bigbang;
- 객체 권한 회수 (오라클 세션)
ㄴex. revoke select on view01 from bigbang;
<view 생성>
- 사용문법 :
create or replace view <viewname>
as
select ~
from ~ ;
**이미 생성된 뷰에 대해선 replace로 초기화(?) 가능함
- 예시 1 : view01 생성
create or replace view view01
as
select id, name, gender, birth
from emp;
- 예시 2 : view04 생성
(주소가 '용산'인 사람들의 데이터(view_name, text)만 view04로 생성)
(bigbang사용자에 권한을 부여해서 조회할 수 있음)
create or replace view view04 ---> view04에 담아 생성
as
select id, name, birth, gender ---> 4개의 컬럼들만
from emp ---> emp 테이블에서
where address = '용산' ---> 주소가 '용산'인 사람들 중에서
order by name desc;
<데이터 사전 내용 조회하기>
1. 전체 뷰 내용 조회하기
select view_name, text
from user_views
order by view_name;
2. 특정 뷰 내용 조회하기 (view02)
select view_name, text
from user_views
where view_name = ' VIEW01 ' ---> 뷰이름은 대문자로!
order by view_name;
<데이터 추가>
1. 뷰를 사용하여 간접적으로 데이터 추가하기
- 예시1 : view02를 이용한 데이터 인서트하기
ㄴex. insert into view02 values('sim', '심형래', sysdate, '마포') ;
** 추가 후 뷰 조회 : desc VIEW02;
<2020/11/16>
<제약조건 constraint>
* 제약조건을 확인하기 위한 딕셔너리 뷰
| 컬럼 이름 | 설명 | ||
| constraint_name | 제약 조건 이름 | ||
| constraint_type | 제약 조건 유형 |
constraint_type | 의미 |
| P | Primary key | ||
| R | Foreign key | ||
| U | Unique | ||
| C | Check, not null | ||
| table_name | 제약 조건이 속한 테이블명 | ||
<const.sql 스크립트 파일 내용>
select t.table_name, t.constraint_name, t.constraint_type, t.status, c.column_name,
t.search_condition from user_constraints t, user_cons_columns c
where t.table_name = c.table_name
and t.constraint_name = c.constraint_name
and t.table_name = upper('&table_name')
order by t.table_name;
---> 위 스크립트 문장을 실행후,
대체변수입력 창(table_name에 대한 값 입력 : )에서 조회할 테이블 이름 입력.
<제약 조건의 이름 구체화 하기>
- Sys_oxx : 사용자가 명시적으로 지정 요망, 차후 문제발생 시 유지보수의 용이성을 위해 필요함
- 제약 조건 유형 5가지 : PK / FK / UK / NN / CK
- 신규 제약 조건 이름 지정 규칙 : 테이블명_컬럼명_제약조건의유형
- 제약 조건의 이름 변경 : alter
- 사용형식 : alter table 테이블명 rename constraint 이전 제약 조건 이름 to 신규 제약 조건 이름;
ㄴex. alter table emp rename constraint sys_C007010 to emp_id_pk;
ㄴex. alter table emp rename constraint sys_C007009 to emp_password_nn;
ㄴex. alter table emp rename constraint sys_C007008 to emp_name_nn;
* sys_CXXX : 해당 부분은 이전 제약 조건의 이름으로, 사용자가 명시적으로 지정 요망.
* 윗 문장과 같이 리네임하는 이유는, 차후 문제발생 시 유지보수의 용이성을 위해 필요.
<테이블 생성과 동시에 제약 조건의 이름 지정>
- 테이블을 생성하면서 동시에 컬럼과 그 컬럼의 제약 조건 이름을 명시적으로 지정 가능함.
ㄴex. create table emp07 (
empno number constraint emp07_empno_pk primary key,
ename varchar2(10) constraint emp07_ename_nn not null,
job varchar2(9) constraint emp07_job_uk unique,
deptno number constraint emp07_deptno_fk references dept(deptno)
) ; -> deptno는 FK / deptno는 dept테이블의 PK
* 테이블 생성 시 고려할 것 3개 : 테이블명 / 컬럼명 / 컬럼타입 (+제약조건)
<다른 유저의 테이블 접근>
* pdf p.134
- 유저 soo와 hee가 서로의 emp 테이블을 조회하려고 합니다.
- 신입사원(유저) soo와 hee는 관리자가 생성해야 합니다.
- 접근 절차 :
1) 신규 사용자 생성
2) 신규 사용자가 로그인 가능하도록 권한 부여
3) hr.emp 테이블 조회 권한을 신규 사용자에게 부여
<예시 : 신규 사용자 철수, 영희 생성 후에 dcl 실습을 진행할 예정입니다.>
1) 신규 사용자 생성 : soo, hee
(관리자세션)
- soo user 생성
create user soo
identified by soo123
quota unlimited on users
account unlock;
- hee user 생성
create user hee
identified by hee465
quota unlimited on users
account unlock;
2) 신규 사용자에게 로그인 가능 권한 부여
- create session 권한 부여
grant create session to soo, hee;
3) 신규 사용자에게 테이블 생성 권한 부여
grant create table to soo, hee;
4) 신규 사용자 세션에서 테이블 생성 및 데이터 삽입
- emp 테이블 생성
create table emp(
id varchar2(20),
name varchar2(30),
salary number
);
- 데이터 삽입
insert into emp values( 'hong', '홍길동', 100 );
insert into emp values( 'kim', '김유신', 100 );
insert into emp values( 'lee', '이순신', 100 );
commit;
5) 신규 사용자가 서로의 emp 테이블 조회 권한 부여
- soo가 hee에게 조회 권한 부여
(soo세션) grant select on emp to hee;
- hee가 soo에게 조회 권한 부여
(hee세션) grant select on emp to soo;
6) 각자 세션에서 서로의 emp 조회 실행
- soo가 hee의 emp 테이블 조회
(soo세션) select * from hee.emp;
- hee가 soo의 emp 테이블 조회
(hee세션) select * from soo.emp;
<view 생성 / soo와 hee의 view 각각 생성>
1. 신규 사용자에게 뷰 생성 권한 부여
(관리자세션) grant create view to soo, hee;
2. 사용자 세션에서 뷰 생성 및 조회 (이름만 조회 가능)
(soo세션)
create or replace view soo_view
as
select name from emp;
select * from hee.hee_view;
(hee세션)
create or replace view hee_view
as
select name from emp;
select * from hee.hee_view;
3. 다른 사용자의 뷰, 서로 조회 가능한 권한 부여
(soo세션) grant select on soo_view to hee;
(hee세션) grant select on hee_view to soo;
<사용자 sample 생성 / 삭제 실습>
1. 사용자 sample 생성 및 권한 부여
(관리자세션)
create user sample
identified by sample
quota unlimited on users
account unlock;
grant connect, create table to sample;
2. 사용자 sample 로그인 및 temp테이블 생성
(sample세션)
create table temp (
id number,
name varchar2(10)
);
insert into temp values(1, '호호');
insert into temp values(2, '하하');
commit;
3. 관리자가 sample 사용자 삭제
- 아래 문장은 오류 : 현재 접속된 사용자는 삭제 불가
drop user sample;
- 아래 문장은 오류 : 삭제하려면 cascade 지정해야 함
drop user sample;
- cascade 지정 : 사용자가 보유한 모든 object를 그냥 지워버릴 것
drop user sample cascade;
----------------user sample 생성부터 삭제까지 / 실습 끝
<권한 Priv : Privilege>
* pdf p.143
- 권한 : 사용자에 대하여 특정 sql문장이나 객체에 대하여 접근/실행할 수 있는 능력
- sql 구문 등에 대한 사용상의 제한
- 예를 들어, 사용자에게 특정 객체에만 접근 가능하게 하거나 막거나 하는 것
1. 시스템 권한
- 시스템 권한 : 데이터베이스에 엑세스하기 위한 권한
- create table : 테이블 생성 가능
- create session : 접속이 가능한 권한
2. 객체 권한
- 객체 권한 : 객체들에 대한 조작 관련 권한
- 예시 : 사용자oraman은 bigbnag사용자의 emp테이블에 대한 조회(select)권한을 갖고 있음
<시스템 권한>
*with admin option : 시스템 권한 제거
*with grant option : 객체 권한 제거
- 시스템 권한은 100개 이상의 권한
- 시스템 권한 조회하기 (관리자세션에서 아래 쿼리 실행)
-> select privilege from role_sys_privs
where role = 'DBA' order by privilege;
- 권한의 종류 : create user / any table / session / view / sequence / procedure, drop user
- 내가 부여받은 시스템 권한 조회 : select * from user_sys_privs;
- 사용형식 : grant 시스템권한 [, 권한2, ... ]1 to 사용자 [, 사용자 | 롤 | public];
ㄴex. grant create session, create table, ... to oraman;
ㄴex. grant create session, create table to bigbang;
<시스템 권한 부여 과정>
- 관리자가 soo에게 create table 권한 부여
grant create table to soo;
- soo에서 자신의 가능 권한 조회
select * from user_sys_privs;
- soo에서 시스템 권한 추가 (NO -> YES)
: +with admin option 권한 부여
grant create table to soo;
grant create table to soo with admin option;
* with admin option을 위 문장에서 추가해 수행함으로써
soo가 hee에게 creat table 권한을 부여할 수 있게 되었음
- soo가 hee에게 create table 권한 부여
grant create table to hee;
<객체 권한>
* with grant option
- 컬럼 수정 가능한 객체 권한 부여
- 예시1 : soo가 hee에게 soo의 emp테이블 salary컬럼을 수정하게끔 객체 권한 부여
<객체 권한 부여 과정>
- soo가 자신의 컬럼 수정 권한을 hee에게 부여
(soo세션) grant update(salary) on emp to hee;
- 권한 받은 hee가 soo의 컬럼 수정
(hee세션) update soo.emp set salary = 1234;
- 내가(soo) 부여해준 객체 권한 확인하기
(soo세션) select * from user_col_privs_made;
- soo의 컬럼을 hee가 조회 가능한 객체 권한 부여
: +with grant option 권한 부여
(soo세션) grant select on emp to hee;
(soo세션) grant select on emp to hee with grant option;
* hee가 soo에게 받은 grant option을 hr에게 또 줌.
* hr : human resource의 줄임말로 오라클 설치시 기본으로 생성되는 사용자
- 컬럼 변경 내용 조회
(hee세션) select * from soo.emp;
(soo세션) select * from emp;
- 권한 회수
revoke select on emp from hee;
* soo에 대한 hee의 grant option권한까지 회수
* soo가 권한 회수 후, 다음 문장들은 연쇄적으로 사용 불가
ㄴ select * from soo.emp; (권한없음)
ㄴ grant select on soo.emp to hr; (권한없음)
<role 롤 생성>
<사용자 생성 시 용어 정리>
1. quota
- tablespace에 할당받은 공간
- 테이블을 생성하면 파일의 크기가 커지는데, 테이블 개수를 무제한 생성하게 두지 않음
- quota unlimited는 이를 무제한 생성하게 해주겠다는 의미임
2. users 테이블 스페이스
- default 테이블 스페이스 이름
3. tablespace
- table이나 index 등을 저장하기 위한 데이터 저장소
4. account unlock (<-> lock)
- 로그인 바로 가능하게끔 계정을 풀어 주겠다.
<예시>
- 사용자 철수, 영희 생성 후에 dcl 실습을 진행할 예정입니다.
1) 신규 사용자 생성 : user soo
create user soo
identified by soo123
quota unlimited on users
accout unlock;
DCL : 능력을 주고 뺐는..
grant / revoke
ROLE : 1개 이상의 권한을 모아놓은 소쿠리와 같음.
--pdf p.134
--사용자 철수, 영희 생성 후에 dcl 실습을 진행할 예정입니다.
--관리자 세션
user_tables; 내가 갖고 있는(user) 테이블들
dba_tables; 관리자(dba)가 갖고 있는 사용자 리스트, 얘가 갖고 있는 테이블 스페이스.
테이블이 저장되는 공간 : tablespace
--철수 세션
철수에 대한 접속 아이콘 생성
테이블 생성 & 컬럽 추가
철수가 영희의 emp테이블 조회
--영희 세션
영희에 대한 접속 아이콘 생성
테이블 생성 & 컬럽 추가
--뷰
- 철수와 영희에 대하여 각각 emp테이블에 대하여 사원들의 이름만 보여주는 view를 생성하세요.
- 관리자에서 soo, hee에게 뷰 권한 및 서로 조회할 수 있는 권한 등 부여
- 뷰 생성 및 조회
--김철수가 비번을 잊어버렸습니다.
관리자는 비번을 바꿔주고, 로그인 시 비번을 수정하도록 요구해야 합니다.
사용자는 바뀐 비번을 이용하여 로그인하고, 신규 비번을 입력해야 합니다.
--pdf p.143 풀이
--실습 사용자 sample를 생성하고 삭제하기 테스트
--cascade 옵션 : 보유하고 있던 객체까지 포함하여 삭제합니다
--시스템 권한 조회
--시스템 권한 제거(with admin option : 받은걸 또 다른 사람한테 넘겨줄 수 있음)
ㄴex. A B C, B 박탈해도 C는 유지됨
--객체권한 p.149
--soo가 hee에게 soo의 salary컬럼에 대한 변경 권한을 부여하기
--내가 부여해준 객체 권한 확인하기(부여한 주체의 객체 세션에서 조회)
--또다시 hee가 해당 권한을 hr에게 주기 ---> 불가능.
--롤 role : 소쿠리..? 여러개의 권한을 한번에 모아놓음
--신규사용자 '곰돌이' 생성하고, 롤을 이용해 권한 부여하기(관리자세션)
--곰돌이 아이콘 만들기