1 교시
** self join
=> 동일한 테이블을 가지고 join
=> 하나의 테이블에 동일한 의미를 갖는 열이 2개 이상 존재하는 경우에 가능
=> 동일한 의미를 갖는 컬럼을 이용해서 다른 정보를 조회하고자 할 때 사용
=> from 절에 동일한 이름의 2개의 테이블이 설정되게 되는데 이렇게 되면 이름에 문제가 발생하기 대문에 이름 뒤에 별칭을 부여해서 테이블을 구분
=> from 절에서 설정한 별명은 이후 모든 절에 적용이 되어야 한다.
=> select 절에서 열 이름에 별명을 부여하는 것은 실제 별명이 아니고 출력하기 위해서 사용하는 개념이라서 별명을 써도 되고 원래의 이름을 입력해도 된다.
=> from 절에서 부여한 별명은 데이터를 구분하기 위해서 부여한 별명이기 때문에 이후에는 별명으로 사용해야 한다.
=> emp 테이블 구조
EMPNO(사원번호) - 정수
ENAME(사원이름) - 문자열
JOB(담당업무) - 문자열
MGR(관리자 사원번호) - 정수
HIREDATE(입사일) - 날짜
SAL(급여) - 실수
COMM(상여금) - 실수
DEPTNO(부서번호) - 정수
=> DEPT 테이블
DEPTNO(부서번호) - 정수
DNAME(부서명) - 문자열
LOC(지역) - 문자열
EX1) EMP 테이블에서 ENAME이 SCOTT인 사원의 관리자 이름을 조회
SELECT e2.ename
FROM EMP e1, EMP e2
WHERE e1.ENAME = 'SCOTT' and e1.mor = d2.empno;
ex2) EMP 테이블에서 EMPNO가 7369인 사원의 ENAME과 관리자의 ENAME 그리고 JOB을 조회
select e1.ename, e2.ename, e2.job
from emp e1, emp e2
where e1.empno = '7369' and e1.mgr = e2.empno;
--샘플데이블 삭제
DROP TABLE sns;
-- 샘플데이터만들기
CREATE TABLE SNS(
id varchar2(50),
friend varchar2(50));
--샘플 데이터 삽입
INSERT INTO SNS values('ggangpae1', 'jessica72');
INSERT INTO SNS values('jessica72', 'hunt12');
INSERT INTO SNS values('jessica72', 'max99');
INSERT INTO SNS values('jessica72', 'ggangpae1');
-- 샘플 데이터 확인
SELECT *
FROM SNS;
-- 내 친구의 친구 중에서 나를 제외한 데이터를 가져오기
SELECT S2.FRIEND
FROM SNS S1, SNS S2
WHERE s1.id = 'ggangpae1' AND s1.friend = =s2.id AND s1.id != s2.friend;
2~3 교시
** ANSI JOIN
=> 미국 표준 협회가 제시한 JOIN 문법
1. CROSS JOIN - Cartesina Product
=> 특별한 조인 조건 없이 2개 테이블의 모든 조합을 만들어 내는 것
SELECT
FROM 테이블이름1 CROSS JOIN 테이블이름2;
=> 열의 개수는 앙쪽 테이블의 영 개수의 합이 되고 행의 개수는 앙쪽 테이블 행개수의 곱이 된다.
EMP (8열 14행), DEPT (3열 4행)
--EMP 테이블과 DEPT 테이블의 CROSS JOIN
SELECT *
FROM EMP CROSS JOIN DEPT;
2. ANSI INNER JOIN
=> 양쪽 테이블에 동일한 의미를 갖는 열의 값이 양쪽 테이블 모두에 존재하는 경우에만 결합하는 JOIN
SELECT *
FROM 테이블이름1 INNER JOIN 테이블이름2
ON 테이블이름1.열이름 = 테이블이름2.열이름;
EMP 테이블의 DEPTNO는 부서번호이고 DEPT테이블의 DEPTNO도 부서번호
--EMP 테이블과 DEPT 테이블을 INNER JOIN
SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO; -- 이 줄을 USING DEPTNO; 로 작성해도 동일하다
=> 동일한 의미를 갖는 열 이름이 양쪽 테이블에서 동일하다면 ON 대신에 USING 사용 가능
ON 대신 USING(공통된 열이름)
=> 양쪽 테이블의 공통된 의미를 갖는 열 이름이 같을 때는 INNER JOIN 대신에 NATURAL JOIN이라고 설정해서 공통된 열 이름을 생략할 수 있다.
--EMP 테이블과 DEPT 테이블을 INNER JOIN
SELECT *
FROM EMP NATURAL JOIN DEPT;
3. ANSI OUTER JOIN
=> OUTER JOIN : 한쪽 테이블에만 존재하는 데이터도 JOIN에 참여
SELECT *
FROM 테이블이름1[LEFT | RIGHT | FULL] OUTER JOIN 테이블이름2
ON 테이블이름1.열이름 = 테이블이름2.열이름;
EMP 테이블에는 DEPTNO가 10,20,30이 있고
DEPT 테이블에는 DEPTNO가 10,20,30,40이 존재
INNER JOIN을 하게 되면 DEPT 테이블의 DEPTNO가 40인 데이터는 조회되지 않는다.
40인 데이터를 조회할려면 OUTER JOIN을 이용해야 한다.
--EMP 테이블과 DEPT 테이블의 FULL OUTER JOIN
SELECT *
FROM EMP FULL OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
** SET OPERATION
=> 동일한 구조를 갖는 테이블 끼리 연산
1. UNION, UNUNION ALL
=> 테이블 2개를 합치는 것
2. INTERSACT
=> 양쪽 테이블에 공통으로 존재하는 행만 추출해서 새로운 테이블을 만드는 것
3. MINUS
=> 한쪽 테이블에만 존재하는 행만 추출해서 새로운 테이블을 만드는 것
** JOIN의 단점
=> JOIN은 2개 테이블의 조합이라서 시간이 많이 걸리는 작업이므로 JOIN을 하지 않고 해결할수 있다면 JOIN은 사용하지 않는 것이 좋다.
** SUB QUERY
=> QUERY 안에 다른 QUERY가 포함되는 것
=> SQL에서는 SUB QUERY는 반드시 ( ) 안에 포함해야 한다.
=> SUB QUERY는 MAIN QUERY가 수행되기 전에 1번만 실행된다.
=> 보통은 SELECT 구문의 WHERE 절이나 FROM 절에서 사용된다.
1. 단일행 서브쿼리
=> 서브 쿼리의 결과가 하나의 행이라서 단일행 연산자를 사용할 수 있는 경우
=> 단일 행 연산자 - =. !=, >, >= , <, <=
EX1) EMP 테이블에서 ENAME이 SCOTT인 사원과 동일한 LOC에 근무중인 사원의 ENAME과 SAL의 값을 조회
=> ENAME과 SAL은 EMP 테이블에 존재
=> LOC는 DEPT 테이블에 존재
SELECT ENAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME = 'SCOTT';
SELECT LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME = 'SCOTT';
-- SCOTT인 사원과 부서번호(DEPTNO)가 동일한 부서번호를 가진 사원의 ENAME과 SAL 조회
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME = 'SCOTT');
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME = 'SCOTT') AND ENAME != 'SCOTT'; -- SCOTT 빼고 조회
EX2)EMP 테이블에서 DEPT 테이블의 LOC가 DALLAS인 사원의 ENAME과 DEPTNO를 조회
=> 여러 테이블의 정보를 이용하더라도 조회하고자 하는 열이 하나의 테이블에 전부 존재한다면 서브 쿼리로 해결이 가능하다.
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'DALLAS');
4 교시
EX3) EMP 테이블에서 SAL의 값이 1250 이상인 데이터의 ENAME 과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= 1250;
EX4) EMP 테이블에서 SAL의 값이 ENAME이 MILLER인 사원의 ENAME 과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT SAL
FROM EMP
WHERE ENAME = 'MILLER');
EX5) EMP 테이블에서 SAL의 값이 평균이상인 데이터의 ENAME 과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT AVG(SAL)
FROM EMP);
EX6) DNAME이 SALES 인 사원의 ENAME과 JOB 그리고 SAL을 조회
=> DNAME은 DEPT 테이블에 존재하고 ENAME과 JOB 그리고 SAL은 EMP 테이블에 존재
=> 테이블 이름이 2개가 나오면 JOIN이나 SUB QUERY로 해결 ( 그 기준은 조회하고자 하는 컬럼이 하나의 테이블에 존재하면 SUB QUREY로 해결 가능, 조회하고자 하는 컬럼이 2개의 테이블에 나누어 존재하면 JOIN으로 해결)
=> 2개의 테이블을 가지고 작업을 할 때는 동일한 의미를 갖는 컬럼을 이용해야 한다.
SELECT ENAME, JOB, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'SALES');
EX7) ENAME이 SCOTT 인 사원과 동일한 LOC에 근무하는 사원의 ENAME과 SAL을 조회
=> LOC는 DEPT 테이블에 존재
=> EMP 테이블과 DEPT 테이블은 DEPTNO를 같이 소유
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC = (SELECT LOC
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME='SCOTT'))) AND ENAME != 'SCOTT';
=> SCOTT 자신은 빼야한다.
 |
 |
2) 다중행 서브 쿼리
=> 서브 쿼리의 결과가 0개나 2개 이상이어서 단일 행 연산자로 비교가 불가능한 서브쿼리
=> 이 때는 IN, NOT IN, ANY, ALL 등의 다중행 연산자를 사용해야 한다.
EX) LOC가 DALLAS이거나 CHICAGO인 곳에서 근무하는 사원들의 ENAME과 JOB을 조회
SELECT ENAME, JOB
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'DALLAS' OR LOC = 'CHICAGO'); --에러
이렇게 작성하면 DEPTNO가 2개 리턴되서 =로 비교할 수 없다고 에러가 발생
=> 서브쿼리를 이용해서 = 나 != 로 비교할 때 2개 이상의 행이 리턴되면 = 대신에 IN, != 대신에 NOT IN으로 바꿔야 한다.
EX2) EMP 테이블에서 DEPTNO가 30인 사원들 전체보다 SAL이 더 많은 사원의 ENAME과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE DEPTNO = 30); --이렇게 쓰면 서브쿼리의 결과가 2개 이상이라서 에러
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
FROM EMP
WHERE DEPTNO = 30);
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 30);
5 교시
** DDL(Data Definition Language)
=> 데이터 구조와 관련된 SQL - 관리자의 언어
1. 오라클의 기본 자료형
1) 숫자 : number(전체자릿수, 소수자릿수)
=> 소수 자릿수는 생략하면 0
=> 전체 자릿수를 생략하면 38
2) 문자열
=> char(자릿수) : 고정된 크기의 문자열
=> varchar2(자릿수) : 크기가 변하는 문자열
=> clob : 긴 문자열
=> 한글 1글자는 3자리를 확보해야함
=> 자릿수를 한번 정하면 이 자릿수보다 큰 데이터는 저장이 안됨
3) 날짜 및 시간
=> date
4) 파일의 내용 : BLOB
2. 테이블 생성
create table 테이블이름(
열이름 자료형 제약조건,
...
);
ex1) 게시판 테이블
=> 글번호 - 정수
=> 제목 - 문자열
=> 작성자 - 문자열
=> 작성일 - 날짜
=> 조회수 - 정수
create table board(
boardnum number(10),
boardtitle varchar2(300),
boarddate date,
boardreadcnt number(10));
=> sub query를 이용한 테이블 생성
create table 테이블이름
as
select 구문
ex2) emp 테이블의 데이터를 복사해서 새로운 emp01 테이블을 생성
create table emp01
as
select *
from emp;
--확인
select *
from emp;
ex3) emp 테이블의 구조만 복사해서 emp02 테이블을 생성
=> 구조만 복사할 때는 where 절에 무조건 거짓인 조건을 설정해서 데이터가 1개도 조회되지 않도록 한다.
create table emp02
as
select *
from emp
where 1=0;
--확인 - emp 테이블의 데이터가 하나도 보이지 않아야 함
select *
from emp02
6 교시
3. 테이블의 구조 변경
=> 테이블에 열을 추가하거나 열을 삭제하거나 열의 자료형을 변경하는 것
1) 열을 추가
alter table 테이블이름
add(컬럼이름 자료형 제약조건);
2) 열의 자료형을 변경
alter table 테이블이름
modify(컬럼이름 자료형 제약조건);
=> 기존의 데이터가 존재하는 경우 자료형이나 크기를 변경하는 것은 기존 데이터에 손실이 발생할 수 있다.
3) 열 삭제
alter table 테이블이름
drop column 열이름;
=> 문법적으로 이상이 없는 경우에도 삭제가 안되는 경우가 있을 수 있다.
다른 테이블에서 이 열의 값을 참조하는 경우에는 삭제가 안된다.
ex1) 기존 board 테이블에 문자 한글 100자를 저장할 수 있는 boardcontent라는 열을 추가
alter table board
add(boardcontent varchar2(300));
--확인
select *
from board;
ex2) 기존 board 테이블의 boardcontent 라는 열의 자료형을 char(300)로 수정
alter table board
modify(boardcontent char(300));
ex3) board 테이블에서 boardcontent 열을 삭제
alter table board
drop column boardcontent;
--열삭제 확인
SELECT *
FROM board;
4) 서비스 중 열을 삭제하는 경우
=> 데이터베이스가 서비스 되고 있는 상태에서 열을 삭제하는 것은 권장하지 않는데 데이터베이스에 데이터를 삽입하거나 삭제 및 갱신하는 동안은 Lock이 설정되서 다른 곳에서 이 데이터베이스에 삽입, 삭제, 갱신 작업을 수행할 수 없도록 하기 때문
=> 이런 경우에는 데이터베이스의 열을 사용하지 못하도록 해 놓고 나중에 한가해지면 그 때가서 삭제하는 방법을 권장
=> 사용중지
alter table 테이블이름
set unused(열이름);
=> 중지 중인 열을 삭제
alter table 테이블이름
drop unused columns;
4. 테이블을 삭제
drop table 테이블이름;
=> 삭제가 안되는 경우가 발생핼 수 있는데 이 경우는 이테이블의 데이터를 다른 테이블에서 참조하고 있는 경우이다.
--board 테이블 삭제
DROP TABLE BOARD;
5. 테이블에서 데이터만 삭제
=> 구조는 남겨두고 데이터만 삭제
truncate table 테이블이름;
--emp01 테이블의 데이터를 삭제
select *
from emp01;
truncate table emp01;
select *
from emp01;
6. 테이블 이름 변경
rename 예전이름 to 새로운이름;
7 교시
** DML - 데이터 조작 언어
=> 데이터를 삽입하고 삭제하고 갱신하는 명령어
1. 샘플 테이블 생성
=> 운동 선수 정보를 저장하는 테이블
번호 : 정수3자리
이름 : 영어 문자열 20자리 - 변하지 않음
소속팀 : 영어 문자열 30자리 - 변할 수 있다.
연봉 : 정수 5자리
데뷔연도 : 정수 4자리
create table NBAPlayer(
num number(3),
name varchar2(20),
team char (20),
salary number(5),
debue number(4));
-- 확인
select *
from NBAplayer;
2. 데이터 삽입
1) 컬럼이름을 기재해서 삽입
insert into 테이블이름(열이름 나열)
values(값을 나열);
=> 열 개수와 값의 개수가 반드시 일치해야 한다.
1번 James Harden Rockets 10000 2011 데이터 삽입
insert into NBAPlayer(num, name, team, salary, debue)
values(1, James Harden, Rockets, 10000, 2011);
-- 확인
select *
from NBAPlayer;
2) 열이름을 생략하고 삽입
=> 테이블을 만들 때 사용한 순서대로 데이터를 대입해야 한다.
insert into 테이블이름
values(값을 나열)
2번 Stephan Curry Warriors 34682 2009
insert into NBAPlayer
values(2, 'Stephan Curry', 'Warriors', 34682, 2009);
3) null 삽입
=> 값에 직접 null 이라고 입력해도 되고 ''만 해도 된다.
=> 데이터를 삽입할 때 열 이름을 제외하고 삽입하면 기본값이 삽입된다.
3번 LeBron James Lakers 33285 2003
insert into NBAPlayer
values(3, 'LeBron James', null, null, 2009);
3. 데이터 수정 - update
update 테이블이름
set 수정할 열이름 = 계산식이나 값,....
where 조건;
=> where를 생략하면 테이블의 모든 데이터가 수정된다.
update NBAPlayer
set debue = 2009
where name = 'James Harden';
--NBAPlayer 테이블의 num 값을 전부 100을 추가
update NBAPlayer
set num = num + 100;
4. 데이터 삭제 - delete
delete from 테이블이름
where 조건;
=> 조건이 있으면 조건에 맞는 데이터만 삭제하고 조건이 없으면 전체 삭제를 한다.
ex1) name이 Stephan Curry인 선수를 삭제
delete from NBAPlayer
where name = 'Stephan Curry';
ex2) 테이블의 모든 데이터 삭제
delete from NBAPlayer;
'Oracle' 카테고리의 다른 글
| 25일차 공부 Transaction, DTO&DAO, Procedure (0) | 2020.05.13 |
|---|---|
| 24일차 공부 select, procedure, JDBC (0) | 2020.05.12 |
| 23일차 공부 Transaction (0) | 2020.05.11 |
| 21일차 공부 select, like, NVL , GroupFunction, join (0) | 2020.05.07 |
| 20일차 공부 Datebase, Oracle, select (0) | 2020.05.06 |
