스키마수정

ALTER TABLE ADD COLUMN 컬럼명 컬럼타입

ALTER TABLE DROP COLUMN 컬럼명

ALTER TABLE CHANGE 원컬럼명 새로운컬럼명 컬럼타입

ALTER TABLE MODIFY 컬럼명 컬럼타입

ALTER TABLE ADD PRIMARY KEY (컬렴명)

ALTER TABLE DROP PRIMARY KEY 컬럼명

ALTER TABLE ADD UNIQUE (컬럼명)

ALTER TABLE ADD CONSTRAINT 제약명 UNIQUE (컬럼명1, 컬럴럼명2)

ALTER TABLE DROP UNIQUE

ALTER TABLE ADD FOREIGHN KEY 컬럼명 REFERENCES 원컬럼명

ALTER TABLE DROP FOREIGHN KEY

ALTER TABLE 테이블명 RENAME 새로운 테이블명

ALTER TABLE ADD CHECK 조건

ALTER TABLE ADD CONTRAINT 조건명 CHECK 조건

ALTER TABLE DROP CHECK

ALTER TABLE 테이블명 ALTER 컬럼명 SET DEFAULT 기본값

ALTER TABLE 테이블명 ALTER 컬럼명 DROP DEFAULT

스키마 삭제

DROP DATABASE 데이터베이스명

테이블삭제

1)DROP TABLE 테이블명 

-> 전체(스키마와 내용) 삭제

2)DELETE * FROM 테이블명 

-> 스키마는 유지 내용은 삭제(레코드를 하나씩 지운다 = 범용명령어)

3)TRUNCATE TABLE 테이블명

-> 내용만 지음 스키마는 유지(전용명령어=빠름)

DCL

-권한 및 역할 설정하는 언어

-GRANT(부여)와 REVOKE(해제)

MYSQL의 유저관련 정보는

USE MYSQL

SELECT USER, HOST FROM USER;

에서 정보가 있다

MYSQL 유저등록하는 방법

CREATE USER  아이디@호스트주소 IDENTIFIED BY 비번

MYSQL 사용자 삭제

DROP USER 아이디@호스트주소

사용자 삭제 반영하기

FLUSH PRIVILEGES;

권한 부여 해제관련

예시)

특정유저의 모든 권한을 보기

SHOW GRANT FOR 아이디@LOCALHOST;

LOCALHOST에서만 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

로컬랜(공유기내 방화벽내)에서만 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@공유기주소 IDENTIFIED BY 비밀번호;

GRANT ALL ON 데이터베이스명.* TO 아이디@192.168.0.% IDENTIFIED BY 비밀번호;

인터넷전체에서 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@% IDENTIFIED BY 비밀번호;

검색/추가 권한만 부여

GRANT SELECT, INSERT ON 데이터베이스명.* TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

특정테이블의 특정컬럼의 업데이트 권한만 부여

GRANT UPDATE(특정컬럼) ON 데이터베이스명.테이블명 TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

모든 권한을 삭제

REVOKE ALL ON *.* FROM 아이디@LOCALHOST;

역할관련

MYSQL은 ROLE(역할) 관련된 내용이 없음

GRANT의 문제점을 해결하기 나온 개념이 ROLE(역할)이다

GRANT의 문제점

테이블수*사용자수의 조합*CRUD 만큼의 갯수만큼 종류가 굉장히 많아진다

즉, 관리 포인트가 많다

ROLE(역할)의 개념

역할별로 권한을 설정하고 사용자에게 역할을 부여하는 형태로 사용

사용자가 여러개의 롤을 가지는 것이 가능함

예시)

CREATE ROLE 역할명

GRANT INSERT, DELETE ON 테이블명 TO 역할명

GRANT 역할 TO 사용자명

MYSQL 원격접속 설정

1)사용자를 원격사용자로 등록

GRANT ALL PRIVILEGES ON 데이터베이스명.테이블명 TO 아이디@% IDENTIFIED BY 비밀번호

2)MY.INI 또는 MY.CNF파일 수정(BIND-ADDRESS부분 주석처리)

3)MYSQL 서버 재시작

4)방화벽 3306 포트 열기

검색성능개선을 위한 INDEX

인덱스개념

-검색을 빠르게 하기 위한 자료구조(주로 B트리 계열)

레코드 추가/수정/삭제시 해당 컬럼으로 다시 정렬한 후 검색에 필요한 값들을 미리 계산해 검색속도를 높임

-기본키는 자동으로 인덱스 설정

-조인에도 인덱스가 영향을 줌

빠른검색을 위해서는 정렬(쿽정렬, 힙정렬 주로 사용)이 되어있어야 함

인덱스 종류

-이진탐색

-B트리(상용DBMS에서 가장 일반적으로 많이 사용됨)

인덱스 설정

예시)

CREATE INDEX 인덱스명 ON 테이블명(컬럼명)

CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2,...)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명)

ALTER TABLE 테이블명 DROP INDEX 인덱스명

메타데이터

:DB, 테이블의 스키마에 대한 정보를 저장하는 테이블

메타데이터의 종류

1)데이터 사전(INFORMATION_SCHEMA)

일반적으로 읽기전용

데이터베이스의 정보저장

데이터의 데이터(DB스키마)가 저장되는 데이터 베이스

2)데이터 디렉토리

DBMS의 모든 데이터가 저장되는 디렉토리

DB저장, 상태 및 로그저장

****my.ini에서 디렉토리를 변경할 수 있음

예시)

SHOW DATABASES;

SHOW TABLES;

SHOW TABLE STATUS;

SHOW COULUMNS FROM 테이블명;

SHOW INDEX FROM 테이블명;

SHOW TABLES IN INFORMATION_SCHEMA;

DESCRIBE INFORMATION_SCHEMA.CHARACTER_SET

USE INFROMATION_SCHEMA;

SHOW TABLES;

1)CHARACTER_SETS

2)COLLATION

3)COLLATION_CHARATER_SET_APPLICABILITY

4)COLUMNS

5)COLUMN_PRIVILEGES

6)KEY_COLUMN_USAGE

7)REFERENTIAL_CONSTRAINTS(외래키)

8)ROUTINES

9)SCHEMATA

10)STATISTICS

11)TABLES

12)TABLE_CONSTRAINTS

13)TRIGGERS

14)USER_PRIVILEGES

15)VIEWS

캐릭터셋

:문자인코딩 정보/메타데이터의 일종

:데이터베이스/테이블별로 별도 설정 가능

예시)

ASCII/ISO-8859-1   아스키계열

EUC-KR/KSC_5601    한글 완성형 계열

UTF-8/UNICODE      유니코드계열

UTF-8(기본)

콜레이션

:데이터를 정렬(문자간의 비교)할때 사용하는 정보

:정렬시에 대소문자를 구분(CASE SENSITIVE)/비구분여부(CASE INSENSITIVE) 설정

예시)

UTF8-GENERAL-CI(기본값)

여기서 CI가 CASE INSENSITIVE임

예시)

MYSQL 기본 캐릭터셋 확인

STATUS;

OR

SHOW VARIABLES LIKE CHARACTER_SET_SYSTEM;

예시)

WORLD/CITY 캐릭터셋/콜레이션을 각각 UTF8/UTF8_GENERAL_CI로 설정

ALTER DATABASE WORLD CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

ALTER TABLE CITY CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

예시)

SAMPETESTDB를 UTF8/UTF8_GENERAL_CI로 지정하고 생성하시오

CREATE DATABASE SAMPLETESTDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

스토리지 엔진

:인덱스와 유사

MYSQL의 여러엔진중에 스토리지엔진이라는 것이 있음

외래키를 이용하려면 엔진의 기능중에 외래키 지원하는 엔진을 써야함

대표적인 엔진은 MyISAM과 InnoDB가 있음

데이터 접근속도/안정성/트랜잭션의 지원 여부 등의 차이가 있음

기본값은 InnoDB 임

InnoDB - 트랜잭션 지원 / 업데이트 위주 / 줄단위락 / 복구용이 / 동시처리성 높음

MyISAM - 상대적으로 높은 성능 / 읽기 위주 / 테이블단위 락

예시)

MySQL 기본 스토리지 엔진 확인

SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE SUPPORT='DEFAULT';

예시)

기본 스토리지 엔진을 변경

SET DEFAULT_STORAGE_ENGINE=MyISAM;(리부팅시 리셋)

OR

my.ini변경(리부팅시에도 계속 적용)

[mysqld]default-storate-engine=MyISAM

데이터베이스 백업(콘솔창에서)

1)전체 데이터베이스 백업

mysqldump -u아이디 -p --all-databases > 덤프파일명.sql

2)특정 데이터베이스 백업

mysqldump -u아이디 -p --databases DB명 > 덤프파일명.sql

3)특정 테이블 백업(데이터포함)

mysqldump -u아이디 -p DB명 테이블명 > 덤프파일명.sql

4)스키마만 백업

mysqldump -u아이디 -p --no-data world(DB명) country(테이블명) > country_schema.sql

5)데이터만 백업

mysqldump -u아이디 -p --no-create-info world country > country_data.sql

데이터베이스 백업(워크벤치에서)

1)Data Export -> Export Options -> Export to Self-Contained File

2)Tables to Export

3)테이블 선택

4)Dump Structure and Data/Structure Only/Data Only

5)Export 버튼

데이터베이스 복원(콘솔)

mysql -u아이디 -p DB명 < 파일명

mysql -u아이디 -p < 파일명

mysql -u아이디 -p DB명 테이블명 < 파일명

데이터베이스 복원(prompt)

SQL>source 파일명

데이터베이스 복업(워크벤치)

1)Data Import -> Import from Self-Contained File

2)Default Schema to be imported to DB명

3)Data Structure and Data/Dump Data Only/Dump Structure only

4)Import 버튼

데이터베이스 로그(데이터베이스 운영 상황을 별도의 파일에 저장)

1)에러로그(일반적으로 사용하는 로그)

Mysql 구동과 모니터링, Query 에러에 관련된 메시지를 포함

2)일반로그(일반적으로 사용하는 로그)

전체 쿼리에 대하여 General log를 활성화 시켜서 저장 가능

일반적으로 성능상의 문제로 남기지 않는다

3)슬로우 쿼리 로그(일반적으로 사용하는 로그)

long_query_time에 설정된 시간 이상을 소요한 쿼리를 기록

4)이진 로그/릴레이 로그(리플리케이션 복제에서 사용하는 특정 로그임)

-mysql쿼리를 수행하면서 쌓는 로그, 시점 복구 등을 수행하는 역할

-replication에서 사용

-바이너리 로그(마스터-CUD를 남기게 된다)/릴레이 로그(슬레이브)에서 바이너리 로그를 사용해서 입력한다(내용동일하게)

2018년 9월 

이코라이텍

실습과 함께하는 데이터베이스 MYSQL편

검색에 최적화된 시스템은 RDBMS계열

쓰기에 최적화된 시스템은 Nosql(Not only SQL)계열

모델링

-정규화:중복정보를 최소화시킴

이진검색은 비교하는 값이 하나라서 작다 크다만 가능

B-Tree(비트리)는 이진검색에서 비교하는 값이 2개라서 작다 크다 중간에 있다까지 가능

상용디비에서는 비트리를 쓴다고 생각하면 됨

DML(CRUD)

DDL(스카마 정의 수정) - CREATE / DROP / 

DCL(접근권한 정의) - GRANT(권한부여) / REVOKE(권한취소)

MYSQL은

INNER JOIN

LEFT JOIN

RIGHT JOIN

까지만 지원하며

FULL JOIN은 지원하지 않는다 ->각각 JOIN해서 UNION으로 합쳐야 함

VIEW는 내부 컬럼의 조건에 따라 변하기도 하고 변하지 않기도 한다

컬럼이 바로 맵핑된 경우는 원테이블이 변경되면 변경되고

가공을 거쳤다면 변경되지 않는다

LIKE검색 %와 _가 있다

NULL함수

IFNULL(MYSQL)

NVM(오라클)

집합함수

SUM

COUNT

등등

다중행 서브쿼리(다중행 연산자)

ALL -> 가장 큰값보다 큰(maximum효과)

ANY -> 가장 작은 값보다 큰(minimum효과)

IN - OR효과

EXISTS - if문효과

MYSQL은 합집합만 지원함

교집합과 차집합은 지원하지 않음

UNION은

두 쿼리의 결과 형식이 동일해야 함

다른테이블이라도 결과값의 형식만 잁치하면 됨

UINION ALL->중복허용

MYSQL 자료형

1)정수형

TINYINT(1BYTE)

INT(4BYTE)

BIGINT

2)실수형

FLOAT(4BYTE)

DOUBLE(8BYTE)

DECIMAL(십진법)

3)문자열

CHAR 고정길이 문자열(최대 255자)

VARCHAR 가변길이 문자열(최대 65,535자)

4)TEXT문자열

TEXT(2BYTE)

MEDIUMTEXT(3BYTE)

LONGTEXT(4BYTE)

5)BLOB(이미지 및 파일을관리)

BLOB(2GBYTE)

MEDIUMBLOB(3GBYTE)

LARGEBLOB(4GBYTE)

6)시간관련

DATE(YYYY-MM-DD)

TIME(HH:MI:SS)

DATETIME(YYYY-MM-DD HH:MI:SS)

TIMESTAMP(YYYY-MM-DD HH:MI:SS)

입력데이터 제약조건

NOT NULL

UNIQUE(테이블에 동일한 값 받아들여지지않음)

PRIMARY KEY(UNIQUE + NOT NULL)

FOREIGN KEY

CHECK(해당 조건일때만 입력 가능 - MYSQL지원안함)

DEFAULT(컬럼값이 입력되지 않으면 기본값 입력)

AUTO INCREMENT(MYSQL만 있음)

정규화(중복성 제거-무결성 유지)

->그리고 조인을 한다

정규형(중복을 제거하기 위한 테이블 정의 규칙)

1정규형:나눌 수 있을만큼 쪼개라

2정규형:테이블의 컬럼들이 기본키와 직접 연관되는 컬럼만으로 구성하라

3정규형:컬럼들간의 종속관계가 있으면 안됨

14강

트랜젝션

-데이터 베이스 내에서 하나의 그룹으로 처리되어야 하는 명령문들을 모아 놓은 작업 단위

-데이터 일관성과 무결성을 보장하기 위해서는 반드시 트랜젝션관리가 필요함

트랜젝션 제어 명령어

-commit

모든 작업을 정상적으로 처리하겠다고 확정하는 명령어

트랙잭션의 처리과정을 데이터베이스에 반영하기 위해서, 변경된 내용을 모두 영구 저장

commit수행하면, 하나의 트랜잭션 과정을 종료하게 됨

-savepoint

-rollback

작업중 문제가 발생했을때, 트랜젝션의 처리과정에서 발생한 변경사항을 취소하고, 트랜잭션 과정을 종료시킴

트랜잭션으로 인한 하나의 묶음 처리가 시작되기 이전의 상태로 되돌림 즉, 최근 commit상태로 복귀 시킴

sqlplus가 정상 종료되면 자동 commit / 비정상 종료되면 자동 rollback

ddl과 dcl 명령문이 수행된 경우 자동 commit

정전이나 컴퓨터 down시에 자동 rollback 됨

ddl문

-create, alter, drop, rename, truncate

-자동 commit됨

-해당 명령이 에러가 나도 자동commit됨

트랜젝션을  작게 분할하는 savepoint

-현재 트랜잭션을 작게 분할하는 명령어

-저장된 savepoint는 rollback to savepoint문을 사용하여 표시한 곳까지 rollback 할수 있음

-여러개의 sql문 실행을 수반하는 트랜젝션의 경우, 사용자가 트랜잭션 중간 단계에서 savepoint를 지정할 수 있음

-차후 rollback과 함게 사용하여 현재 트랜젝션 내의 특정 savepoint까지 rollback 할 수 있게 됨

savepoint c1;

rollback to c1;

15강 - 16강

무결성 제약 조건

-무결성 : 데이터베이스 내에 있는 데이터의 정확성 유지

-제약 조건 : 바람직하지 않은 데이터가 저장되는 것을 방지하는 것

ex)기본키 : 반드시 입력되게하고, 유일한 값을 갖게 함

제약조건 5가지를 지원함

not null : null을 허용하지 않음

unique : 중복된 값을 허용하지 않고, 항상 유일한 값을 갖도록 함

primary key : null을 허용하지 않고, 중복된 값도 허용하지 않음, not null조건과 unique 조건을 결합한 형태

foreign key : 참조되는 테이블의 칼럼의 값이 존재하면 허용

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

컬럼명 컬럼타입 not null 로 제약조건 추가함

컬럼명 컬럼타입 unique 로 제약조건 추가함(null값은 예외로 간주해서 여러번 입력가능하다)

의미있게 제약조건명을 명시하면 위배되었는지 바로 알수 있음(user_contraint딕셔너리를 검색해도 알수 있음)

컬럼명 컬럼타입 constraint 제약조건명 제약조건타입

제약조건명은 테이블명_컬럼명_제약조건타입

기본키제약조건 = unique 조건 + not null 조건

컬럼명 자료형 primary key (제약조건이 걸리게 된다)

참조무결성 : 테이블 사이의 관계에서 발생하는 개념

사원테이블에 부서번호를 입력할때, 부서 테이블에 존재하는 부서번호만 입력하도록 지정(참조 무결성)

-> 사원 테이블의 부서번호 컬럼에 외래키 제약조건을 명시해야 함

참조 무결성은 두 테이블 사이의 주종관계에 의해서 결정됨

부모테이블 :  주체가 되는 테이블 

자식 테이블 : 종속이 되는 테이블

사원 회사 내에 존재하는 부서에 소속되어 있어야 합니다

부모테이블 : 부서

자식테이블 : 사원

주종관계가 애매한 경우

어느 테이블의 데이터가 먼저 정의되어야 하는가?

먼저 정의되어야 하는 테이블 -> 부모테이블

나중에 정의되어야 하는 테이블 -> 자식 테이블

회사를 설립하고, 어떤 부서를 구성하여 운영할지 정한 후에 그 부서에서 일할 사원을 뽑아야 소속이란 관계가 성립

부모 테이블(부서)  자식테이블(사원)

부모테이블(기본키) -> 자식테이블의(외래키)

부모키가 되기 위한 컬럼은 반드시 부모 테이블의 기본키나 유일키로 설정되어 있어야 함

참조 무결성 제약조건

사원 테이블의 부서 번호는 부서 테이블에 저장된 부서번호중의 하나와 일치하거나 null값만 입력이 가능해야 한다는 조

건

제약조건으로 foreign key를 지정

오라클에서 제공하는 EMP테이블과 dept테이블의 제약조건

select table_name, constraint_type, constraint_name, R_constraint_name(외래키인경우 어떤 primary key를 참조했는지

에 대한 정보를 가짐) from user_constraints where table_name in(‘dept’, ‘emp’);

왜래키 설정하는 법

deptno number(2) constraint emp06_deptno_pk references dept(deptno);

참조무결성이 걸려있을땐느 자식테이블 삭제 -> 부모테이블 삭제 해야함

check 제약조건

입력되는 값을 체크하여, 설정된 값 이외의 값이 들어오면, 오류 메시지와 함께 명령이 수행되지 못하게 하는 것

-조건으로 데이터 값의 범위나 특정 패턴의 숫자나 문자값을 설정할 수 있음

ex) sal number(7,2) contstraint emp07_sal_ck check(sal between 500 and 5000)

ex) gender varchar(1) constraint emp07_gender_ck check(gender in(‘M’, ‘F’))

default제약조건

아무런 값을 입력하지 않았을때, 디폴트로 설정한 값이 입력되도록 하는 것

제약조건 변경

alter table을 이용해서 제약조건을 추가,삭제, 변경 가능

ex)alter table emp01 add constraint emp01_empno_pk primary key(empno)

alter table emp01 add constraint emp01_deptno_fk foreign key(deptno) references dept(deptno)

alter table emp01 modify ename constraint emp01_ename_nn not null

alter table emp01 drop constraint emp01_ename_nn

제약조건의 비활성화

alter table emp01 disable constraint emp01_ename_nn

alter table emp01 enable constraint emp01_ename_nn

cascade 옵션

-부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을대

부모 테이블의 제약조건을 비활성화화면, 이를 참조하고 있는 자식 테이블의 제약 조건까지 함께 비활성화시켜 주는 옵션

-제약 조건의 삭제에도 활용됨(테이블의 제약 조건을 삭제하면, 이를 참조하고 있는 자식테이블의 제약조건도 같이 삭제

됨)

cascade옵션을 지정하여 기본키 제약조건을 비활성화하면, 이를 참조하는 외래키 제약조건도 연속적으로 비활성화되는지

를 확인

ex)alter table delpt01 disable primary key cascade;

17강

뷰

테이블 - 데이터가 있음

뷰 - 데이터가 없음(sql만 저장)

사용자가 해당 view에 접근하면, 그때 view에 들어있던 sql이 수행되어 결과를 가져오는 것

기본테이블 : 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 필요한 데 그것이 기본 테

이블이다

뷰를 생성할때 사용하는 옵션

create or replace view : 같은 이름의 view가 있을 경우 삭제후 다시 생성

force : 기본 테이블의 존재 여부에 상관없이 view를 생성

with check option : 주어진 제약조건에 맞는 데이터만 입력 및 수정을 허용

with read only : select만 가능한 읽기 전용 뷰를 생성

뷰는 번거로운 select문을 매번 입력하는 대신 보다 쉽게 원하는 결과를 얻고자 하는 바람에서 출발한 개념

ex) create view emp_view30

as select empno, enmae, deptno

from emp_copy

where deptno=30;

뷰는 보안에 유리

-사용자에게 특정객체만 조회할 수 있도록 권한 부여

-동일한 테이블에 접근하는 사용자들마다 서로 다른 뷰에 접근하도록 여러 개의 뷰를 정의

-특정 사용자만 해당 뷰에 접근 가능

뷰는 물리적으로 데이터를 저장하지 않고 create view 명령어로 뷰를 정의할때

as절 다음에 기술한 쿼리 문장 자체를 저장함

뷰의 쿼리를 확인하려면

ex)select view_name, text from user_views;

뷰를 삭제 = user_views 데이터 딕셔너리에 저장되어 있는 뷰의 정의를 삭제하는 것

뷰를 삭제해도 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않음

단순뷰 : 하나의 테이블로 생성

그룹함수의 사용이 불가능

distinct 사용이 불가능

dml(insert/update/delete) 사용이 가능

***단순뷰에서 dml 명령어 사용이 불가능한 경우

뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 컬럼이 not null 제약 조건이 지정되어 있는 경우 insert문 사용이 불

가능

->뷰에 대한 insert문은 기본 테이블에 null값을 입력하는 형태가 되기 때문임

sal*12와 같이 산술표현식으로 정의된 가상 컬럼이 뷰에 정의되면,insert나 update가 불가능

distinct를 포함한 경우에도 dml 명령 사용이 불가능

그룹 함수나 group by 절을 포함한 경우 dml 명령 사용이 불가능

복합뷰:여러개의 테이블로 생성

그룹 함수의 사용이 가능

distinct 사용이 가능

dml 사용이 불가능

with check option : 조건 컬럼값을 변경하지 못하게 하는 옵션(조건에 사용한 컬럼의 값을 수정못하게 함)

ex)update view_read30 set comm=2000; -> 불가능함

with read only : 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용 수정을 불가능하게 만드는 옵션(테이블의 모든컬

럼값을 수정 ㅁ소하게)

뷰를 활용하여 top-n을 구하기

-예) 사원 중에서 입사일이 빠른 사람 5명(top-5)만을 추출한느 것

-rownum 사용

테이블 구조를 생성하면 제공되는 컬럼

-rowid(테이블의 특정 레코드를 랜덤하게 접근하기 위한 논리적인 주소값)

-rownum(각 행에 대한 일련번호)

create or replace view view_hire

as select empno, ename, hiredate from emp

order by hiredate;

select rownum, empno, ename. hiredate from view_hire

where rownum <=5;

18강

인덱스 : SQL 명령문의 처리속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체

인덱스의 장점

-검색속도가 빨라짐

-시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킴

인덱스는 내부구조는 b-트리 형식으로 구성

인덱스의 단점

-인덱스를 생성하는 데 시간이 걸림

-인덱스를 위한 추가적인 공간이 필요함

-데이터의 변경작업(insert/update/delete)이 자주 일어날 경우에는 오히려 성능이 저하됨

인덱스는 기본키나 유일키와 같은 제약조건을 지정하면 자동으로 생성

인덱스 객체에 대한 정보 확인

user_indexes, user_ind_columns 데이터 딕셔너리 뷰에서 확인

-> 사용자가 정의한 인덱스 모두 조회 가능

ex) select indx_name, table_name, column_name from user_ind_columns where table_name in(‘emp’, ‘dept’); //테이블명은 대문자로 입력해야 함

인덱스의 종류

고유 인덱스(unique index) - 유일한 값을 갖는 컬럼에 대해서만 인덱스를 설정할 수 있음

비고유 인덱스(nonunique index) - 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스

단일 인덱스(single index)  - 한개의 컬럼으로 구성한 인덱스

결합 인덱스(composite index) - 두개 이상의 컬럼으로 구성한 인덱스

함수 기반 인덱스(function based index) - 수식이나 함수를 적용하여 만든 인덱스

인덱스 생성

create index

인덱스 삭제

drop index

인덱스를 사용해야 하는 경우

테이블에 행의 수가 많을때

where 문에 해당 컬럼이 많이 사용될때

검색 결과가 전체 데이터의 2%~4%정도 일때

join에 자주 사용되는 컬럼이나 null을 포함하는 컬럼이 많은 경우

인덱스를 사용하지 말아야 하는 경우

테이블에 행의 수가 적을때

where문에 해당 컬럼이 자주 사용되지 않을때

검색결과가 전체 데이터의 10%~15% 이상일때

테이블에 dml작업이 많은 경우 즉, 입력 수정 삭제 등이 자주 일어 날때

인덱스를 가끔 한번씩 재생성을 해주어야만 빠른 효율을 누릴 수 있음

ex)alter index idx_emp01_deptno rebuild;

인비저블 인덱스 - 11g new feature

인덱스를 삭제하기전에 사용안함 상태로 만들어서 테스트해 볼수 있는 기능을 제공하는 인덱스

권한의 역할과 종류

시스템 권한 - 사용자의 생성과 제거, DB접근 및 각종 객체를 생성할수 있는 권한

ex) 사용자 생성/삭제, 테이블 삭제, 함수기반 인덱스 생성, 테이블 백업, 접속권한, 테이블생성권한, 뷰생성권한, 시퀀스생성권한, 함수생성권한

객체 권한 - 객체를 조작할 수 있는 권한 

ex)테이블, 뷰, 인덱스, 시퀀스, 동의어등

시스템 권한 부여 grant

사용자에게 시스템 권한을 with admin option과 함께 부여하면, 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고 부여받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받게 됨

현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려줌

user_tab_privs_made

자신에게 부여된 사용자 권한을 알려줌

user_tab_privs_recd

권한을 회수하기 위해 명령어 revoke

사용자에게 객체 권한을 with grant option과 함께 부여하면, 그 사용자는 객체를 접근할 권한을 부여받으면서 그 권한을 다른 사용자에게 부여할 수 있는 권한

11강

서브쿼리

-하나의 select 문장 내에 포함된 또 하나의 select 문장

-비교연산자의 오른쪽에 기술해야 하고, 반드시 괄호안에 넣어야 함

-메인쿼리가 실행되기 이전엔 한번만 실행됨

단일행 서브쿼리

-수행 결과가 오직 하나의 행만을 반환하는 것

-단일행 비교 연산자 : =, <>, >, >=, <, <=

다중행 서브 쿼리

-수행 결과가 두건 이상 출력되는 것

-다중행 연산자 : in, any or some, all, exists

in -> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과가 하나라도 일치하면 참

all -> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과가 모든 값이 일치하면 참

그래서 <all 최소값을 반환, >all 최대값을 반환

ex) select ename, sal from emp where sal > all(select sal from emp where deptno = 30);

30번 부서의 급여보다 모든 값이 큰 경우만 출력

단일행 서브쿼리로도 가능(max이용)

any -> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과와 하나 이상이 일치하면 참

ex) select ename, sal from emp where sal > any(select sal from emp where deptno = 30);

30번 부서의 급여의 최소값보다 크면 출력

단일행 서브쿼리로도 가능(min이용)

exists-> 메인 쿼리의 비교조건이 서브 쿼리의 검색결과가 하나라도 일치하면 참

exists는 해당 행이 존재하는지의 여부만 확인

in은 실제 존재하는 데이터들의 모든값까지 확인

exists를 쓸때는 메인쿼리와 서브쿼리의 테이블간의 이퀄조인을 써야 한다는 것을 알고 있어야 한다

ex) select ename, deptno, sal from emp e where exists(select * from dept d where d.deptno in (10,20) and e.deptno 

= d.deptno);

ex) select ename, deptno, sal from emp e where exists(select * from dept d where d.deptno =40 and e.deptno = 

d.deptno);

12강

DDL(생성, 변경, 삭제)

create table 할때 사용할 수 있는 데이터형

char(size) - 고정길이 문자 데이터 / varchar2와 동일한 형태의 자료를 저장할 수 있고, 입력된 자료의 길이와는 상관없

이 정해진 길이만큼 저장 영역을 차지함 / 최소크기 1

varchar2(size) - 가변 길이 문자 데이터 / 실제 입력된 문자열의 길이만큼 저장 영역을 차지 / 최대 크기는 명시해야 하

며 최소 크기는 1

number - internal number format 최고 40자리까지의 숫자를 저장할 수 있음 / 이때 소수점이나 부호는 길이에 포함되지 

않음

number(w) - w자리까지의 수치로 최대 38자리까지 가능함(38자리가 유효숫자)

number(w,d) - w는 전체길이, d는 소수점 이하 자릿수 / 소수점은 자릿수에 포함되지 않음

date - BC 4712년 1월 1일~ AD 4712년 12월 31일까지의 날짜

long - 가변 길이의 문자형 데이터 타입 최대 2GB

lob - 2GB까지의 가변길이 바이너리 데이터를 저장시킬 수 있음, 이미지 문서, 실행파일을 저장할수 있음

rowid - tree-piece format을 가짐, DB에 저장되어 있지 않으며, DB data도 아님

bfile - 대용량의 바이너리데이터를 파일 형태로 저장함 최대 4GB

timestamp(n) - date형의 확장된 형태

interval year to month - 년과 월을 이용하여 기간을 저장

interval day to second - 일,시,분,초 를 이용하여 기간을 저장 / 두 날짜값의 정확한 차이를 표현

식별자 

-반드시 문자로 시작

-1~30자까지 가능

-A-Z까지의 대소문자, 0-9까지의 숫자, 특수 기호는 (_, $, #)만 포함가능

-오라클에서 사용되는 예약어나 다른 객체명과 중복이 불가함

-공백 허용을 안함

DESC로 테이블 구조 파악

서브쿼리로 테이블 생성하기(테이블의 구조와 내용을 복사)

동일한 구조와 내용을 갖는 테이블을 생성할 수 있음

ex) create table emp02 as select * from emp;

일부의 구조와 내용을 갖는 테이블을 생성할 수 있음

ex) create table emp03 as select empno, ename from emp;

기존 테이블의 일부의행만 복사

ex) create table emp04 as select * from emp where deptno =10;

서브쿼리로 테이블 생성하기(테이블의 구조만 복사) 

-위의 서브쿼리를 where절에 항상 거짓이 되는 조건을 지정하면 됨

ex)create table emp05 as select * from emp where 1=0;

***********제약조건은 복사되지 않음(서브쿼리로 테이블 생성시)

가상 컬럼을 가지는 테이블 생성

create table vt001

(no1 number, no2 number, no3 number generated always as (no1+no2) virtual);

no3는 no1과 no2의 더한값이 저장됨

no1과 no2만 insert할수 있고 해당 컬럼의 값이 변경되면 no3값도 자동으로 변경됨

테이블 구조 변경하는 alter table

-add column절

ex)alter table emp01 add(job varchar(9));

새로운 컬럼은 테이블 맨 마지막에 추가됨

이미 이전에 추가해 놓은 행이 존재한다면, 그 행에도 컬럼이 추가되지만 컬럼값은 null값으로 입력됨

-modify column절

ex)alter table emp01 modify(job varchar(30));

해당 컬럼에 자료가 없을 경우는

데이터 타입과 컬럼의 크기를 변경할 수 있음

해당 컬럼의 자료가 있을 경우는 

데이터 타입 변경할수 없고 크기는 늘릴수는 있지만 현재 가지고 있는 데이터 크기보다 작은 크기로는 변경할 수 없음

***데이터가 존재하는데도 데이터의 타입을 변경할 수 있는 경우는 오직 char와 varchar2사이의 타입을 변경하는 경우^^(

같거나 크게만 변경)

-drop column절

ex) alter tabe emp01 drop column job; 

set unused 옵션 사용하기

-컬럼을 삭제하지 않지만, 컬럼의 사용을 논리적으로 제한할 수 있음

-테이블에 저장된 내용이 많을 경우 해당 테이블에서 컬럼을 삭제하려면 꽤 오랜시간 소용됨

-그 시간동안 lock 걸려서 다른 작업을 할 수 없음 -> 이때 set unused 옵션 사용(락 걸리지 않음)

ex) alter table emp02 set unused(job);

사용빈도가 가장 낮은 시간에 삭제 작업을 진행

ex)alter table emp02 drop unused columns;

drop table

기존 테이블의 존재를 제거(구조와 내용을 함께)

테이블의 모든 로우를 제거하는 truncate table

내용만 제거함 / 구조는 남아있음

테이블명 변경하는 rename

ex) rename emp05 to test;

delete과 truncate와 drop의 차이점

delete는 테이블 용량은 줄어들지 않음 / 내용만 지워짐 / 원하는 데이터만 골라서 삭제 / 되돌릴수 있음

truncate 최초 테이블이 만들어진 상태로 만듦 / 용량 줄어들고 index모두 지워짐 / 데이터만 삭제 / 되돌릴 수 없음

drop 는 공간/ 객체(index, 제약조건등) / 내용과 구조 삭제 / 되돌릴수 없음

데이터 딕셔너리와 데이터 딕셔너리 뷰

데이터 딕셔너리 : 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블

-테이블을 생성하거나 변경하는 때에 자동으로 갱신되는 테이블

-사용자가 직접 수정삭제 할 수 없음

데이터 딕셔너리 뷰:데이터 딕셔너리의 내용을 사용자가 이해할 수 있는 내용으로 변환하여 제공

-user_데이터 딕셔너리 뷰(user_tables 데이터 딕셔너리 뷰, …..)

자신의 계정이 소유한 객체 등에 관한 정보를 조회

ex) desc user_tables;

ex) select table_name from user_tables order by table_name desc;

-all_데이터 딕셔너리 뷰

자신 계정 소유 또는 권한을 부여 받는 객체 등에 관한 정보를 조회

ex) desc all_tables;

ex) select owner, table_anme from all_tables;

-dba_데이터 딕셔너리 뷰

데이터 베이스 관리자만 접근 가능한 객체 등의 정보를 조회(DB의 모든 객체 접근 가능)

시스템 권한 가진 사람만 접근가능

ex)select owner, table_name from dba_tables;

13강

insert

ex)insert into dept03 (컬럼명들) values (값들);

ex)insert into dept03 select * from dept;

insert할때 컬럼명을 생략가능하기고 가능함

다중 테이블에 다중행 입력

insert all 서브쿼리의 결과를 조건없이 여러테이블에 입력 가능(서브쿼리의 컬럼명과 테이블의 컬럼명이 동일해야 함)

ex)insert all 

into emp_hir values(empno, ename, hiredate)

into emp_mgr values(empno, ename, mgr)

select empno, ename, hiredate, mgr  from emp where deptno = 20;

ex)insert all

when hiredate>’1982/01/01’ then into emp_hir01 values(empno, ename, hiredate)

when sal >= 2000 then into emp_sal values(empno, ename, sal)

select empno, ename, hiredate, sal from emp;

update

update문에 where절을 주지않으면 모든행의 값이 변경되므로 주의하기

ex) update emp01 set deptno =40 where deptno = 10;

ex) update dept01 set loc=(select loc from dept01 where deptno=40) where deptno =20;

delete

delete문에 where절을 주지않으면 모든행이 삭제되므로 주의하기

ex) delete from emp01 where deptno =30;

ex) delete from emp01 where deptno = (select deptno from dept where dname=’sales’);

테이블을 합병하는 merge 문

구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능

기존 테이블에 자료가 존재하는 경우 - 새로운 값으로 갱신(update)

기존 테이블에 자료가 존재하지 않는 경우 - 새로운 행으로 추가(insert)

ex) merge into emp01

using emp02

on(emp01.empno = emp02.empno)

when matched then

update set

emp01.ename=emp02.ename

emp01.job=emp02.job

emp01.mgr = emp02.mgr

emp01.hiredate = emp02.hiredate

when not matched then

insert values(emp02.empno, emp02.ename, emp02.job, emp02.mgr, emp02.hiredate);

8강 그룹함수(하나 이상의 행을 그룹으로 묶어 연산하여, 하나의 결과를 나타내는 함수)

종류 sum, avg, max, min, count

특징 :컬럼값이 null인 경우는 제외하고 계산한다

단일행 함수는 

각행에 대해서 함수의 결과가 구해지기 때문에 결과가 여러개의 로우로 구해짐

sum(해당 컬럼 값들에 대해 총합을 구하는 함수)

avg(해당 컬럼의 평균값을 구하는 함수)

max(해당 컬럼의 최대값을 구하는 함수)

min(해당 컬럼의 최소값을 구하는 함수)

count(테이블에서 조건을 만족하는 행의 개수를 반환하는 함수)

->중복되어 있는 것까지 카운트 되니 -> distinct로 중복해 제거를 해서 구해야함

ex)select count(distinct job) 업무수 from emp;

****select문에 그룹함수를 사용하는 경우

그룹 함수를 적용하지 않은 단순컬럼은 올수 없음

ex) min함수를 쓰고 ename를 쓰면 오류가 발생 -> 서브쿼리를 이용해서 둘다 나오도록 한다

group by절

특정 컬럼을 기준으로 테이블에 존재하는 행들을 그룹별로 구분하기 위해 사용

형식 : select 컬럼명, 그룹함수 from 테이블명 where 조건 group by 컬럼명;

즉, 그룹 함수를 쓰되 어떤 컬럼값을 기준으로 그룹 함수를 적용할지 기술

***컬럼의 별칭을 사용할 수 없고 반드시 컬럼명을 기술

사원 테이블을 부서번호로 그룹 짓는 경우

ex)select deptno from emp group by delptno;

부서별 평균 급여를 구하는 경우

ex)select deptno, avg(sal) from emp group by deptno;

*****그룹함수를 쓸때 단순컬럼을 올수 없다고 했는데 group by와 동일한 컬럼은 매치가 되서 가능하다

부서별 급여 총액과 평균 급여를 구하는 경우

select delptno, sum(Sal), avg(sal) from emp group by deptno;

부서별 최대 급여와 최소 급여를 구하는 경우

select delptno, max(Sal), min(sal) from emp group by deptno;

부서별 사원의 수와 커미션을 받는 사원의 수를 계산하는 경우

select delptno, count(*), count(comm) from emp group by deptno;

having 절

group by 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 보고자 할때 사용

즉, 그룹의 결과를 제한

그룹함수을 적용해서 나온 결과값 중에서 원하는 조건에 부합하는 자료만 산출 할때 사용

그룹함수

where절은 조건을 사용하여 결과를 제한

테이블에서 특정 조건에 부합하는 자료만을 검색할때 사용

단순컬럼

ex) 그룹 지어진 부서별 평균 급여가 200이상인 부서의 번호와 부서별 평균 급여를 출력하는 경우

select deptno, avg(Sal) from emp group by deptno, having avg(sal) >= 2000;

ex) 부서의 최대값과 최소값을 구하되, 최대 급여가 2900이상인 부서만 출력하는 경우

select deptno, max(sal), min(sal) from emp group by deptno having max(sal) > 2900;

9장

자동으로 소계/합계를 구해주는 함수

-rollup(주어진 데이터들의 소계를 구해주는 함수)

-cube(주어진 테이터들의 천체 총계까지 구해주는 함수)

select deptno, job, count(*), sum(sal) from emp group by rollup(deptno, job);

select deptno, job, count(*), sum(sal) from emp group by job, rollup(deptno);

select deptno, job, count(*), sum(sal) from emp group by deptno, rollup(job);

select deptno, job, count(*), sum(sal) from emp group by cube(deptno, job);

계층형 쿼리 : 데이터를 조회할 때 상위와 하위 관계를 표시할때 사용

-> connect by 절 이용(계층 구조에서 각 행의 연결관계를 설정 -> emp

-> start with가 시작위치이며 보통 사장님

ex) select level, lpad(empno, level*4, ‘*’) as empno01, substr(sys_connect_by_path(ename,’-’),2) ename02, 

ename, mgr from emp

start with mgr is null

connect by prior empno = mgr;

***lpad(empno, level*4, ‘*’) : level*4 바이트 길이로 출력하되, 왼쪽 빈자리는 *로 채우라는 뜻

***level은 현재테이블에는 존재하지 않는 컬럼, 오라클의 모든 sql에서 사용할 수 있는 것으로 해당 데이터가 몇번째 단

계이냐를 의미

level별로 급여 합계, 사원의 명수 구하는 쿼리

ex)select level, sum(sal) total, count(empno) cnt from emp

start with mgr is null

connect by prior empno = mgr

groub by level

order by level;

오라클 11g에 새롭게 추가된 함수

-listagg(정규화된 테이블을 비정규화해서 출력하는 함수 -> 세로로 출력되는 행을 가로로 보기를 원하는 경우)

ex)set lines 200

col listagg for a40

select delptno, listagg(ename, ‘**’) within group (order by hiredate) “listagg” from emp group by deptno;

**는 구분자, order by는 규칙을 적어주면 됨(여기는 hiredate)

-pivot(row단위를 column단위로 변경해주어, 사용자가 한눈에 보기 편한 레포트를 만들어 주는 함수)

ex) select deptno, count(0) from emp group by deptno order by deptno;

-> select deptno, count(decode(job, ‘clerk’,0)) “cleark”,count(decode(job, ‘manager’,0)) “manager”,count

(decode(job, ‘president’,0)) “president”,count(decode(job, ‘analyst’,0)) “analyst”,count(decode(job, 

‘salesman’,0)) “salesman”  from emp group by deptno order by deptno;

->select * from (select deptno, job, empno from emp)

pivot ( 

count(empno) 

for job in(‘clerk’ as “clerk”, ‘manager’ as “manager”, ‘president’ as “president”, ‘analyst’ as 

“analyst”, ‘salesman’ as “salesman”)

)

order by deptno;

부서별/담당업무별 인원수와 급여 합계 출력

select * from (select deptno, job, empno, sal from emp)

pivot ( 

count(empno) as cnt, sum(nvl(sal, 0)) as sal

for job in(‘clerk’ as “clerk”, ‘manager’ as “manager”, ‘president’ as “president”, ‘analyst’ as 

“analyst”, ‘salesman’ as “salesman”)

)

order by deptno;

-rank

주어진 컬럼값의 그룹에서 값의 순위를 계산한 후 순위를 출력해 주는 함수

->집계용 문법 rank(조건값) within group(order by 조건컬럼명 asc|desc )

->분석용 문법 rank() over(order by 조건컬럼명 asc|desc )

ex) 이름 오름차순 정렬시 순위

select rank(‘smith’) within group (order by ename) “RANK” from emp;

ex) 10번 부서에 속한 직원들의 사번과 이름, 급여 해당부서내의 급여 순위를 출력하는 경우

 select empno, ename, sal, rank() over (order by sal desc) “순위” from emp where deptno = 10;

10강

조인 : 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서 결과를 보

여줌

조인의 종류

-cartesian product(카티션 곱)

where절에 공통되는 컬럼에 의한 조인이 발생되지 않아 모든 데이터가 검색 결과로 나타나는 경우

아무런 의미를 가지지 못함-> 그냥 보여만 주기 때문에

ex)select * from emp, dept;

-equi join(이퀄조인)

가장 많이 사용

조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 기법

ex)select * from emp,dept where  emp.deptno = dept.deptno;

->select ename, dname from emp,dept where emp.deptno = dept.deptno;(컬럼앞에 어느테이블인지 명시해야 함)

->select ename, dname from emp,dept where emp.deptno = dept.deptno and ename=’scott’;

->select ename, dept.dname, emp.deptno from emp,dept where  emp.deptno = dept.deptno and ename=’scott’;(컬럼앞

에 어느테이블인지 명시해야 함, 단 중복되지 않는 컬럼일경우는 테이블명 안써도 된다)

->select e.ename, d.dname, e.deptno. d.deptno from emp e, dept d where  e.deptno = d.deptno and e.ename=’scott’

;(테이블의 별칭을 부여한 후, 컬럼앞에 소속테이블을 지정할 경우는 반드시 별칭을 붙어야 함-원래 테이블명을 쓰면 안

됨)

-no-equi join(non이퀄조인)

동일 컬럼이 없이 다른 조건을 사용하여 조인(크거나 작거나 등의 경우)

->이퀄조인이 서로같은 조건을 가진 데이터를 조인해오는 방식이라면 non이퀄조인은 크거나 작거나 하는 경우 조인 방법

사원의 급여가 몇등급인지 살펴보는 경우( salgrade에는 등급별로 low와 hign가 입력되어 있음)

select ename, sal, grade from emp, salgrade where sal >= losal and sal<=hisal;

select ename, sal, grade from emp, salgrade where sal between losal and hisal;

한명의 사원 이름과 소속 부서명, 급여의 등급을 출력하는 경우

(사원 테이블에는 부서이름과 급여 등급은 없고, 부서번호와 급여만 있음)

-> 부서명 이퀄조인, 급여등급은 non이퀄조인

select e.ename, d.dname. s.grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal between 

s.losal and s.hisal

-self join

자기자신과 조인

ex) 특정사원의 매니저가 누구인지 알아낼때 매니저 테이블은 중복이기 때문에 만들지 않고 사원테이블에서 모두 저장되

어 있음(별칭으로 두개의 테이블인 것처럼 사용해야 함)

select employee.ename ||’의 매너지는 ‘ || manager.ename ||’입니다.’ from emp emplyee, emp manager where 

employee.mgr = manager.empno;

-outer join

조인조건에 만족하지 않는 행도 나타내는 조인 기법

2개 이상의 테이블에 조인될때, 어느 한쪽의 테이블에는 해당하는 데이터가 존재함

-> 다른 쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터가 출력되지 않는 문제를 해결하기 위해서 사용되는 조인 

기법

self 조인에서 사장은 mgr가 null이므로 제외되어서 self join 결과값이 나온다

하지만 사장도 나오게 할때 사용하는 것이 outer join 임 

ex)select employee.ename ||’의 매너지는 ‘ || manager.ename ||’입니다.’ from emp emplyee, emp manager where 

employee.mgr = manager.empno(+);

정보가 부족한 컬럼뒤에 (+)를 붙여 줌

1강

데이터베이스 : 유용한 데이터의 집합(통합(똑같은 데이터가 중복되어있지 않음), 저장(컴퓨터가 접근할 수 있는 기억장

치에 저장된 데이터), 운영(조직의 존재 목적이나 기능을 수행하는데 없어서는 안되는 데이터), 공용(여러 사용자들이 서

로 다른 목적으로 사용하는 공유 가능한 데이터) 데이터의 집합)

검색에 용이하게 데이터를 저장하도록 한 것

검색, 수정, 삭제까지 용이

특징:수시적이고 비정형적인 질의 실시간 처리 -> 실시간 접근성

새로운 데이터 삽입, 삭제, 갱신으로 내용이 변하고 현재의 정확한 데이터를 유지->계속적인 변화

같은 내용의 데이터를 여러 사람이 서로 다른 방법으로 동시에 공유 ->동시공유

데이터 레코드들은 주소나 위치에 의해서가 아니라 사용자가 요구하는 데이터의 내용에 따라 참조해야 함-> 내용에 의한 

참조

파일시스템

중복성(한 시스템내의 데이터가 중복 저장, 관리되어 데이터 불일치->일관성, 보안성, 경제성, 무결성등의 문제 발생), 

종속성(응용프로그램과 데이터간의 상호의존 관계)

시퀀스 자동으로 증가되는 값을 만들 수 있는 기능 -> 보통 기본키로 사용함

start with

increment by

maxvalue or nomaxvalue

minvalue or nominvalue

cycle or nocycle

cache or nocache

select sequence_name, min_value, max_value, increment_by, cycle_flag from user_sequences;

시퀀스값을 알아보기 

nextval(시퀀스의 다음값을 알아내기 위해서 사용) -> currval(시퀀스의 현재 값을 알아내기 위해서 사용)

nextval, currval을 사용할 수 있는 경우

-서브쿼리가 아닌 select문

-insert문의 select절

-insert문의 value절

-update문의 set절

nextval, currval을 사용할수없는 경우

-view의 select절

-distinct 키워드가 있는 select문

-group by, having, order by 절이 있는 select문

-select, delete, update의 서브쿼리

-create table, alter table명령의 defalut 값

동의어(synonym) 테이블의 소유자와테이블을 줄여서 쓰는 있는 기능

->비공개 동의어(개별사용자대상), 공개동의어(dual같은경우)

create synonym

drop synonym

파일시스템의 문제점

1. 중복성(한 시스템내의 데이터가 중복 저장, 관리되어 데이터 불일치->일관성, 보안성, 경제성, 무결성등의 문제 

발생)

2. 종속성(응용프로그램과 데이터간의 상호의존 관계)

데이터 베이스의 특징

1. 수시적이고 비정형적인 질의 실시간 처리 -> 실시간 접근성

2. 새로운 데이터 삽입, 삭제, 갱신으로 내용이 변하고 현재의 정확한 데이터를 유지->계속적인 변화

3. 같은 내용의 데이터를 여러 사람이 서로 다른 방법으로 동시에 공유 ->동시공유

4. 데이터 레코드들은 주소나 위치에 의해서가 아니라 사용자가 요구하는 데이터의 내용에 따라 참조해야 함-> 내용

에 의한 참조

sql*plus 명령어 정리

-편집실행 : list(버퍼의 내용을 나타내기 위한 명령), run(버퍼내용보여주고 실행), @(파일의 쿼리실행), /(버퍼저장된 

쿼리 실행)

-파일:edit, save, get, spool 갈무리(내용 모두 저장)

-데이터베이스:connect. host(도스프롬프트로), exit

-시스템조작 : column format(컬럼에 저장된 데이터의 출력 형식을 변경을 위한 명령어)

데이터 딕셔너리 : 데이터베이스와 관련된 모든 정보 제공

ex) select * from TAB(table의 약자);

데이블구조 확인 명령어 : DESC

날짜형은 where HIREDATE >=1982/08/01을 쓴다

조건 논리 연산자

and,or, not(조건을 만족하지 못하는 것만 검색)

ex)where not deptno = 10 

where deptno != 10

between and 연산자

where sal >= 2000 and sal <= 3000;

where sal between 2000 and 3000

where sal not between 2000 and 3000;

in 연산자

where comm not in (300,500,600);

like 검색

%:문자가 없거나, 하나 이상의 문자에 어떤 값이 와도 상관없음

_:하나의 문자가 어떤 값이 와도 상관없음

not like도 가능하다

escape

like연산자에게 사용하는 와일드문자(%,_) 자체를 포함한 문자열을 검색할 때, 와일드 문자를 일반문자처럼 취급하기 위

한 옵션

ex)where ename like ‘%\%%’ ESCAPE ‘\’;

null값 체크

where comm IS NULL;

where comm IS NOT NULL;

order by(정렬)

order by comm ASC | DESC(생략시 ASC기본)

DISTINCT 중복값들이 출력되지 않도록 사용

ex) select distict comm ~~~

연결연산자

ex) select ename || ‘is a’ || job  

함수

-단일행함수(여러건의 데이터를 한번에 한번씩만 처리), 그룹함수(여러건의 데이터를 동시에 입력받아서 결과값 한건을 

만들어 주는 함수)

단일행함수

-숫자함수

-문자처리 함수

-날짜

-형변환

-일반

DUAL 테이블

-한행으로 결과를 출력하기 위한테이블

-산술 연산이나 가상 컬럼 등의 값을 한번만 출력하고 싶을때 많이 사용

ex)select 24*60 form dual;

숫자함수

-ABS(절대값), FLOOR(소수점아래를 버림), ROUND(반올림), TRUNC(특정 자리숫에 잘라내는 함수), MOD(나머지 숫 구하는 

함수)

문자처리함수

-UPPER(대문자로변환), LOWER(소문자로변환), INITCAP(이니셜만대문자로), LENGTH(문자길이), LENGTHB(BYTE의길이), 

INSTR(특정문자의 위치 구함), SUBSTR(일부 문자열을 추출), LPAD(특정기호로 채우는 함수), RPAD, LTRIM(공백문자 

삭제), RTRIM, TRI<(첫번째와 마지막의 문자열 잘라내기)

입사년도 출력할때

select ename, 19||substr(hiredate,1,2) 년도, substr(hiredate, 4, 2) 달 from emp;

날짜함수 

날짜 + 숫자 : 그 날짜로부터 기간만큼 지난 날짜를 계산

날짜 - 숫자 : 그 날짜로부터 기간만큼 이전 날짜를 계산

날짜 - 날짜 : 두 날짜 사이의 기간을 계산

ex) sysdate-1, sysdate+1

-Date형에 사용되는 함수

111111sysdate : 시스템의 날짜를 반환하는 함수

ex)select system from dual;

22222months_between

두 날짜사이의 개월수를 구하는 함수

ex)months_between(sysdate, hiredate)

소수점이하 자리 나올때 round함수와 trunc함수 이용

333333add_months

개월수를 더하는 함수

ex)add_months(hiredate,4) : 4개월 추가

next_day

해당날짜부터 시작하여 명시된 요일을 만나면, 해당하는 날짜를 반환하는 함수

ex) next_day(Date, 요일) 

-한글요일 : 일, 월, 화,

-영문요일 : SUN, MON, TUE 

-문자대신 숫자로 요일을 표현가능 7=일요일, 1=월요일, 2=화요일 등등

‘금’이라고 요일 입력시 오류사항 발생시

alter session set nls_language=korean; 

last_day

해당 달의 마지막 날짜를 반환하는 함수

-형변환 함수

숫자, 문자, 날짜 데이터형을 다른 데이터형으로 변환하는 함수

number-> to_char() -> character -> to_date() -> date

date -> to_char() -> character -> to_number() -> number

ex) 입사일과 요일까지 함께 출력하는 경우

to_char(hiredate, ‘YYYY/MM/DD DAY’) -> 1980/12/12 수요일

to_char(hiredate, ‘YY/MON/DD DY’) -> 80/12월/12 수

to_char(hiredate, ‘YYYY/MM/DD, HH24:MI:SS’) -> 2013/12/12, 23:24:55

ex) 지역별 통화 기호를 붙이고, 천단위마다 콤마 붙여서 출력

to_char(sal, ‘L999,999’) -> 자릿수 채우지 않음

to_char(sal, ‘L000,000;) -> 자릿수 채움(0으로)

날짜 기본 형식-> YY/MM/DD 형식

ex) 올해 몇일이 지났나 계산

trunc(sysdate-to_date(‘2016/01/01’, ‘YYYY/MM/DD’))

to_number(‘20,000’,’99,999’) - to_number(‘10,000’,’99,999’) 

nvl 함수 :null을 0또는 다른 값으로 변환함수 

select sal*12+comm nvl(comm, 0), sal*12+nvl(comm,  0) from emp

decode함수 : 여러가지 경우에 대해서 선택할 수 있도록 하는 기능을 제공

형식 decode(표현식, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3 ….)

특징:조건이 일치하는 경우에만 적용

select ename, deptno, decode(deptno, 10, ‘accounting’, 20, ‘research’, 30, ‘sales’) AS DNAME from emp;

case함수 : 여러가지 경우에 하나를 선택하는 함수

case 표현식 when 조건1 then 결과1

when 조건2 then 결과2

….

else 결과 

end

특징 :다양한 비교연산자를 이용하여 조건 제시, 범위 지정