sowon's Programming

Data Integrity(데이터 무결성)

(1) 무엇을 지킬것인가? 어떻게 지킬것인가

1) 실제(Entity) 무결성

 - 중복된 데이터 방지

 - primary Key, Unique

2) 영역(domain) 무결성

 - Check 

3) 참조(Reference) 무결성

 - Foreign Key /*자식 테이블에 참조 당함*/

(1) 칼럼(필드)의 속성

1) NN(Not Null) 속성

- Primary Key, not null

2) ND(Not Diplicated) 속성

- Primary Key, Unique

3) NC(Not Change) 속성 //변경 할수 없는 것

(3) Primary Key

1) 하나의 테이블에 단 한개만 설정

2) 여러 개의 필드르 ㄹ묶어서 설정 가능

 =============실습=============

1) testuser1으로 접속

create table tblExam()

/*nn속성을 하게하는 방법 not null*/

create table tblExam(

 id    number        not null,

 name  varchar2(10)   null

)

insert into tblExam(name) values('임꺽정'); 

/*nn속성으로 id 없이 넣기 불가능하다*/

/*id는 중복값 안됨*/

/*pimary key 넣기*/

/*1. 테이블 만들때 처음에 생성, 

2 이미 테이블 만들어진 상태에서 기본키 수정으로 넣기*/

create table tblExam(

 id    number         null,

 name  varchar2(10)   null

);

select * from tblExam;

alter table tblExam ADD constraint pk_id primary key(id);

insert into tblExam(id, name) values(null, '홍길동');

insert into tblExam(id, name) values(1, '홍길동');

insert into tblExam(id, name) values(1, '임꺽정'); 

/*임꺽정 무결성 제약조건에 위배됨 */

desc user_constraints;

select constraint_name, table_name from user_constraints;                                 /*어떤 제약이 걸려있는지 보고싶으면 이 쿼리문 이용*

(4) Uuique

1) 하나의 테이블에 여러 개 설정 가능

2) 중복 방지

create table tblExam(

 id    number    ,

 name  varchar2(10),

 constraints uk_id unique(id)

);

select constraint_name, table_name from user_constraints;

 /*어떤 제약이 걸려있는지 보고싶으면 이 쿼리문 이용*/

purge recyclebin; /*휴지통 비우기*/

/*절대로 중복을 허락하지 않음 unique 하지만 null값은 안막아짐*/

insert into tblExam(id,name) values(1, '홍길동'); 

insert into tblExam(name) values('임꺽정'); 

select * from tblExam;

(5) 필드 수정 , 추가, 삭제

1) 필드 수정

alter table tblExam modify name varchar2(20);

 /*말로만 수정이지 원래는 삭제했다가 다시만드는것*/

/*varchar 가변길이 문자열의 길이가 일정하지 않을때 

최대길이를 기준*/

2) 필드 추가

alter table tblExam ADD zipcode char(7);

/*char 고정길이 문자열의 길이가 일정할때 

한글자든 두글자를 입력하던 메모리를 7글자로 고정 성능상 좋음*/

3) 필드 삭제

alter table tblExam Drop column zipcode; 

/*삭제할때는 어떤것을 삭제할것인지 써야됨*/

(6) Default

create table tblExam(

 id    number   default 0 ,

 name  varchar2(10),

point number(3,2)

);

insert into tblExam  ( point) values( 3.14);

/*name에 값을 입력하지 않으면 '무명씨'라고 자동입력되게 처리*/

alter table tblExam modify name default '무명씨';

select * from tblExam;

(7) SEQUENCE

- 자동으로 카운팅하여 일련번호를 리턴함

- 중복값 들어갈 수 없음

===========================================

Create sequence seq_num;

-SEQUENCE를 사용하기 위해서는 정수형만 가능하다.

insert into tblExam(id, name, point) values(seq_num.nextVal, '박소원');

(8) Check

drop table tblExam;

/*city에 입력할 수 있는 도시는 단 서울, 경기, 인천 뿐이다 .*/

create table tblExam(

id number,

name varchar2(10),

city varchar2(10),

constraints ck_city check(city = '서울' or city = '경기'

 or city = '인천')

)

insert into tblExam(id, name, city) values(seq_num.nextVal, '박소원', '서울');

insert into tblExam(id, name, city) values(seq_num.nextVal, '홍길동', '제주'); 

/*체크 제약조건이 위배 왜냐면 서울,경기, 인천만 해놈*/

(9) Foreign key

Create table tblDept(

deptno char(3),

dname varchar2(10)

);

insert into tblDept(deptno, dname) values('100', '영업부');

insert into tblDept(deptno, dname) values('101', '마케팅부');

create table tblEmp(

empno number,

ename varchar2(10),

hiredate date,

deptno char(3)

);

insert into tblEmp (empno, ename, hiredate, deptno) 

valuse(1, '홍길동', sysdate, '100');/*deptno를 기본키로 설정해줘야됨*/

alter table tblDept add constraints pk_deptno Primary key(deptno);

alter table tblemp add constraints pk_deptno_emp Primary key(deptno);

/*테이블 안에 조건이 안맞는 것이 있으면 테이블 변경이 안됨*/

alter table tblEmp add constraints fk_dept_emp_deptno foreign                 key(deptno) references tblDept(deptno);    

update tblEmp set deptno = '101' where empno=2;

Transaction

(1) "All Or Nothing"

(2) 명령어

commit // 완전히 완료 되었을때

roolback // 원상태로 돌리기  

(3) DML

(4) 동기화 //동시사용을 못하게 

*table을 실수로 지웠을때, roolback과 비슷한 관계 

 table 삭제시, 휴지통개념으로 들어간다.

 show recyclebin 으로 목록 볼수 있음

 flashback table 테이블명 to before drop; 지우기 전으로 되돌리고싶다

 단, oracle 에서만 사용 가능

1. 브랜치 목록을 보고 싶다

git branch

-a 원격 브랜치를 포함한 모든 브랜치 목록을 확인 가능

2. 브랜치를 만들고 싶다   

git branch <branchname>

3. 브랜치 이름을 변경하고 싶다.

git branch -m <oldbranch> <newbranch>

4. 브랜치를 삭제하고 싶다

git branch -d <dranchname>

병함되어 있지 않은 커밋이 잇는 경우, 브랜치를 삭제 할 수 없다.

강제로 삭제 하려면, -D 옵션을 붙여 실행한다.

5. 브렌치를 전환하고 싶다.

git checkout <branch>

-b 브랜치의 작성과 작성한 브랜치로의 정환을 명령어로 실행 할 수 있다.

6. 브랜치를 병합

git merge <branch>

--no-ff fast-forward의 병합인 경우라도 병합 명령어를 작성한다.

==> 브랜치가 존재한다는 정보를 남기고 싶을 때 유용한 옵션이다.

출처 : https://backlogtool.com/git-guide/kr/reference/branch.html

1. 저장소 만들기

git init

2. 파일과 폴더를 인덱스에 등록

git add <filepattern>

-filepattern 파일명을 직접 지정

-p 파일 변경 부분의 일부만을 등록

-i 인덱스에 등록하는 파일을 대화식으로 선택

3. 인덱스에 추가된 파일을 커밋

git commit

-a 변경된 파일을 검출하여 인덱스에 추가, 그것들을 커밋하는 동작을 명령어로 실행 할 수 있음.

-m 커밋 메시지를 지정하여 커밋

만일, -m 옵션을 붙이지 않은 경우 커밋 메시지를 편집하기 위한 에디터가 실행됨

4. 변경된 파일의 목록을 확인

git status

-s 설명문을 표시하지 않을 수 있음

-b 설명문 표시 x 브랜치 명을 표시 할 수 있음

5. 파일의 변경 내용 확인

git diff

옵션 지정 하지 않은 상태에서는 작업 트리와 인덱스의 변경 사항을 표시

-cached 인덱스와 GEAD의 변경 사항을 표시

6. 커밋 로그

git log

특정 파일의 커밋 로그를 참조하려면 파일명을 지정한다.

7. 커밋의 상세내용을 확인하고 싶다

git show <commit>

show 명령어의 인수에는 log 명령어에서 참조한 커밋이나 HEAD 를 지정할 수 있다.

8. 파일과 폴더명을 변경, 이동하고 싶다.

git mv <oldfilename> <newfilename>

9. 파일을 삭제하고 싶다.

git rm <file>

10. 관리대상이 아닌 파일을 삭제하고 싶다

git clean

-n 삭제되는 파일을 확인

-f 실제로 파일을 삭제

-x .gitignore에 지정된 파일도 삭제 대상이 됨

11. 변경한 인덱스에 등록되지 않은 파일을 되돌리고 싶다.

git checkout -- <file>

12. 인덱스에 등록한 파일을 취소하고 싶다.

git reset HEAD -- <file>

13. 이전에 커밋한 적이 있는 파일만을 모두 인덱스에 등록하고 싶다

git add -u

출처 : https://backlogtool.com/git-guide/kr/reference/basic.html

Explode 란?

문자열을 문자열로 나누는 것

array explode ( string $delimiter , string $string [, int $limit ] )

delimiter 문자열을 경계로 나누어진 string의 부분 문자열로 이루어지는 배열을 반환합니다.

출처 : php 메뉴얼

http://php.net/manual/kr/function.explode.php

예제

$Singer = "마마무&아이오아이&하이라이트&씨스타&싸이&아이유";

$Singers = explode("&", $Singer);

echo $Singers[0]; // 마마무

echo $Singers[1]; // 아이오아이

1. 커미션을 받는 직원에 대해 이름, 부서명, 근무지를 조회

select ename, dname, loc from emp INNER JOIN dept ON emp.deptno=dept.deptno where emp.comm IS NOT NULL;

2. 이름 중간에 L자가 있는 직원에 대해 이름, 업무, 부서명, 근무지 조회

select ename, job, dname, loc from emp INNER JOIN dept ON emp.deptno=dept.deptno where emp.ename like '%L%';

3. 각 직원들에 대해서 그들의 관리자보다 먼저 입사한 직원에 대해 이름, 입사일, 관리자이름, 관리자 입사일을 조회

select e1.ename, e1.hiredate as 직원입사, e2.hiredate as 관리자입사, e1.mgr, e2.ename from emp e1 INNER JOIN emp e2 ON e1.mgr = e2.empno where e1.hiredate <= e2.hiredate;

*4. 말단사원의 사번, 이름, 업무, 부서번호, 근무지를 조회

select empno, ename, job, emp.deptno, loc from emp right JOIN dept ON emp.deptno = dept.deptno where empno not in(select mgr from emp where mgr is not null);

*** 테이블을 하나 작성한다.(tblBook)

author title

-----------------------

최주현 하늘과 땅

최주현 바다

유은정 바다

박성우 문

최주현 문

박성우 천국

최지은 천국

최주현 천국

박성우 고슴도치

서금동 나

CREATE TABLE book(

author varchar2(15),

title varchar2(15)

);

INSERT INTO book VALUES('최주현','하늘과 땅');

INSERT INTO book VALUES('최주현','바다');

INSERT INTO book VALUES('유은정','바다');

INSERT INTO book VALUES('박성우','문');

INSERT INTO book VALUES('최주현','문');

INSERT INTO book VALUES('박성우','천국');

INSERT INTO book VALUES('최지은','천국');

INSERT INTO book VALUES('최주현','천국');

INSERT INTO book VALUES('박성우','고슴도치');

INSERT INTO book VALUES('서금동','나');

5. 한권의 책에 대해서 두명 이상의 작가가 쓴 책을 검색하시오.

예시) 책이름  작가명  작가명

select b1.title, b1.author 작가명1, b2.author 작가명2 from book b1 inner JOIN book b2 ON b1.title = b2.title and b1.author < b2.author;

6. 한권의 책에 대해서 세명의 작가가 쓴 책을 검색하시오.

예시) 책이름  작가명  작가명 작가명

select b1.title, b1.author 작가명1, b2.author 작가명2, b3.author 작가명3 from book b1 inner JOIN book b2 ON b1.title = b2.title inner join book b3 on b3.title = b2.title and b1.author < b2.author and b1.author > b3.author;

JOIN

(1) 여러 개의 테이블을 병합하여 하나의 결과를 도출하기 위한 방법

(2) 종류

1) Cartasian JOIN(데카르트 조인, 데카르트 곱)

2) Equi JOIN

- 공통 필드의 레코드를 가져오는 방법

- Inner Join(Natural Join)

3) Non Equi Join

- 공통 필드가 없을 때 사용가능

4) Outer JOIN

- Extended Join(Inner join + 공통되지 않은 레코드)

- 종류

Left Outer Join

Right Outer Join

Full Outer Join

5) Self Join

- 원하는 데이터들이 한 테이블에 있을 때 사용.

(3) 문법

1) 표준 문법

2) 자체 DBMS SQL문법

T-SQL : MS SQL Server

FL-SQL : Oracle

(4) SET 연산자

1) UNION

2) UNION ALL

3) INTERSECT

4) MINUS

=======================

SELECT deptno from emp union select deptno from dept;

SELECT deptno from emp union all select deptno from dept; //합집합

SELECT deptno from emp intersect select deptno from dept;

SELECT deptno from emp minus select deptno from dept;

/* 직원의 사번, 이름 업무 관리자 관리자 이름을 조회*/

select empno, ename, job, mgr, ename, from emp inner join emp on emp.mgr = emp.empno;

본 문제는, Oracle Database 11g 실습 계정에 대한 문제 입니다.

1. emp테이블에서 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균(보너스가 NULL이 아닌 평균, NULL포함한 평균),등록되어 있는 부서의 수(총개수와 중복을 제외한 개수)를 구하여 출력

select count(empno), count(comm), avg(comm), avg(nvl(comm,0)), count(deptno), count(distinct deptno) from emp;

2. 부서인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 조회

select deptno, count(*), sum(sal) from emp  group by deptno having(count(empno)>4);

3. 급여가 최대 2900 이상인 부서에 대해 부서번호, 평균 급여, 급여의 합을 조회

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

4. 최고급여와 최소급여의 차이는 얼마인가?

select max(sal), min(sal) , max(sal)-min(sal) from emp;

5. 예시)

년도 count min max avg sum

---------------------------------------------

80 1 800 800 800 800

81 10 950 5000 2282.5 22825

82 2 1300 3000 2150 4300

83 1 1100 1100 1100 1100

select substr(hiredate,1,2) as 년도, count(*) as count, min(sal) as min, max(sal) as max, avg(sal) as avg, sum(sal)as sum from emp group by substr(hiredate,1,2) having(substr(hiredate,1,2)>=80 and substr(hiredate,1,2)<=83 )order by substr(hiredate,1,2);

6. 예시)

TOTAL 1980 1981 1982 1983

---------------------------------------

14 1 10 2 1

select count(*)as total, count(decode(to_char(hiredate,'YYYY'),'1980',0)) as "1980",

count(decode(to_char(hiredate,'YYYY'),'1981',0)) as "1981",

count(decode(to_char(hiredate,'YYYY'),'1982',0)) as "1982",

count(decode(to_char(hiredate,'YYYY'),'1983',0)) as "1983" from emp;

7. 예시)

업무 10 20 30 Total

-------------------------------------------------

ANALYST 6000 6000

CLERK 1300 1900 950 4150

MANAGER 2450 2975 2850 8275

PRESIDENT 5000 5000

SALESMAN 5600 5600

select distinct job as 업무, sum(decode(deptno,10,sal)) as "10",

sum(decode(deptno,20,sal)) as "20", sum(decode(deptno,30,sal)) as "30",

sum(nvl(decode(deptno,10,sal),0))+sum(nvl(decode(deptno,20,sal),0))+sum(nvl(decode(deptno,30,sal),0)) Total from emp group by job;

---------- Sub Query 문제

8. BLAKE와 같은 부서에 있는 모든 직원의 사번, 이름, 입사일자 조회

select empno, ename, hiredate from emp where deptno = (select deptno from emp where ename='BLAKE');

9. SELECT empno, ename, deptno, sal, comm FROM emp WHERE (sal, comm) IN(SELECT sal, comm FROM emp WHERE deptno=30);

  이 쿼리를 수정하여 comm이 null인 직원도 출력할 수 있도록 하시오

select empno, ename, deptno, sal, comm from emp where (sal, nvl(comm,0)) in(select sal, nvl(comm,0) from emp where deptno=30);

10. 평균 급여 이상을 받는 직원들의 사번, 이름을 조회. 단, 급여가 많은 순으로 정렬

select empno, ename, sal from emp where sal>=(select avg(sal) from emp) order by sal desc;

11. 이름에 'T'자가 들어가는 직원이 근무하는 부서에서 근무하는 직원의 사번, 이름, 급여 조회

select empno, ename, sal from emp where deptno in(select deptno from emp where ename like'%T%');

12. 부서의 위치가 dallas인 모든 직원에 대해 사번, 이름, 급여, 업무를 조회 // dept테이블

select empno, ename, sal, job from emp where  deptno = (select deptno from dept where loc='DALLAS');

13. King에게 보고하는 모든 직원의 이름과 부서,업무,급여를 조회

select ename, deptno, job, sal from emp where mgr = (select empno from emp where ename='KING');

14. 월급이 30번 부서의 최저 급여보다 높은 직원의 사번, 이름, 급여를 조회

select empno, ename, sal from emp where sal >(select min(sal) from emp where deptno=30);

15. 10번 부서에서 30번 부서의 직원과 같은 업무를 하는 직원의 이름과 업무를 조회

select  ename, job from emp e where deptno=10 and job in(select job from emp where  deptno=30 and (e.job = job));

SubQuery 중첩 쿼리 

(1) 다른 query문에 포함된 query문

scott의 급여보다 더 많이 받는 직원의 이름 업무 급여를 조회

select sal from emp where ename='SCOTT';

select ename, sal from emp where sal>(select sal from emp where ename='SCOTT');

(2) 반드시 ()를 사용가능

(3) 연산자의 오른쪽에서 사용가능

(4) order by 사용 불가

(5) 결과값에 따른 유형

1) 단일행

2) 다중행

3) 다중열

(6) 연산자

1) 단일행

>, <, >=, <=, =, != ...

2) 다중행

in , any, all, exists not, ...

any 는 or의 뜻 하나만 만족

all 은 and의 뜻 모두 만족

(7) 종류

1) 연관 서브쿼리

2) 비연관 서브쿼리

문제 

/*사번이 7521의 업무와 같고 급여가 7934보다 많은 직원의 사번, 이름, 업무, 급여*/

/*서브쿼리 안썼을경우*/

select empno, ename, sal,job from emp;

select empno, ename, sal , job from emp where job='SALESMAN' and sal>1300;

/*서브쿼리 쓸때*/

select empno, ename, sal, job from emp where job=(select job from emp where empno=7521) and sal>(select sal from emp where empno=7934);

/*업무별로 최소 급여를 받는 직원의 사번, 이름, 업무, 급여, 부서번호 조회*/

select min(sal) from emp group by job;

select empno, ename, job, sal FROM EMP WHERE sal = 800 or sal = 1250 or sal = 5000 or sal 2450 or sal 3000;

select empno, ename, job, sal, deptno  from emp where sal in(select min(sal) from emp group by job);

/*업무별로 최소 급여보다 많은 급여를 받는 직원의 사번, 이름, 업무, 급여 , 부서번호 조회*/

select empno, ename, job, sal, deptno  from emp where sal > any(select min(sal) from emp group by job);

/*업무별로 최대 급여 이상 급여를 받는 직원의 사번, 이름, 업무, 급여, 부서번호 조회*/

select max(sal) from emp group by job;

select empno, ename, job, sal, deptno from emp where sal>= all(select max(sal) from emp group by job);

/*적어도 한명의 직원으로부터 보고를받을 수 있는 직원의 이름, 업무, 입사일자, 급여를 조회*/

select empno, ename, job, hiredate, sal from emp e where exists(select * from emp where e.empno=mgr);

다중열

/*사번이 7934인 직원의 급여를 1500으로 수정 보너스를 300으로 수정*/

update emp set sal=1500, comm=300 where empno=7934;

update emp set sal=1300, comm=null where empno=7934;

select sal, comm from emp where empno=7934;

/*급여와 보너스가 30번 부서에 있는 직원의 급여와 보너스가 같은 직원에 대해 사번, 이름, 부서번호, 급여, 보너스를 조회*/

select empno, ename, deptno, sal, comm from emp where (sal,comm) in(select sal,comm  from emp where deptno=30);

DML(Data Manipulation Language)

(1) INSERT, UPDATE, DELETE

(2) 실습

1) 계정 생성

create user 계정ID identified by 비밀번호;

2) 권한 주기

grant connect, resource to 계정ID;

- connect 접속, resource 수정같은것

3) 테이블 생성

create table tbltest(

id number,

name varchar2(10),

hiredate date

);

4) 데이터 입력

삽입 insert into 테이블명(필드명,...) values(실제값,...) 

5) 데이터 수정

update 테이블명 set 필드명 = 값...

6) 데이터 삭제

delect from 테이블명  where // 테이블안에있는 모든 데이터 삭제