ORACLE 이론 및 코딩하는방법

DBMS( Data Base Management System)- 오라클, My SQL(오라클에서 인수), SQL server
: 데이터를 효과적으로 이용할 수 있도록 정리,보관하기 위한 소프트 웨어

SQL ( Structed Query Language)
: 구조화된 질의어

DB : 데이터를 관리하는 방법의 하나 ( 기존 데이터 결함 => 중복된 데이터를 없애서 관리)

SQL용어 :  1. DDL ( Data Definition Language)-CREATE, ALTER, DROP
    2. DML ( Data Manipulation Language)-SELECT, INSERT, UPDATA, DELETE
    3. DCL

데이터 형식의 종류
1. 숫자 - NUMBER
2. 문자 - '  '      1. VARCHAR2(50) - 최대 50개 가변길이의 문자
                       2. NVARCHAR2 - 가변길이의 유니코드 문자
                       3. CHAR -고정길이의 문자
                       4. NCHAR - 고정길이의 유니코드 문자
3. 날짜 - DATE
4. 날짜+시간 - TIMESTAMP

"    "  용도  1. 띄어쓰기 할때 "AB CD"
                2. 허락되지 않은 컬럼명을 사용하기 위해

메타 데이터 : 사용자 데이터를 저장하기 위한 데이터, 데이터를 위한 데이터

Dictionary : 메타 데이터의 저장소

NVL(AVG(AGE),0) NULL값을 0으로 바꿔준다,

CREATE TABLE STUDENTS(
"SID" VARCHAR2(15),
"PWD" VARCHAR2(15),
"NAME" VARCHAR2(20),
"GENDER" VARCHAR2(10),
"AGE" NUMBER,
"BIRTHDAY" CHAR(10),
"MAJOR" VARCHAR2(50) ,
"ADDRESS" VARCHAR2(100),
"PHONE" VARCHAR2(13),
"SSN" CHAR(14),
"IP" VARCHAR2(15),
"REGDATE" DATE
)

컬럼을 추가할 경우
ALTER TABLE "STUDENTS" ADD "CITY" VARCHAR2

컬럼을 삭제할 경우
ALTER TABLE "STUDENTS" DROP COLUMN"CITY"

컬럼의 스타일 변경할 경우
ALTER TABLE "STUDENTS" MODIFY "CITY" TIMESTAMP

테이블 생성
CREATE TABLE "STUDENTS"

테이블 삭제
DROP TABLE "STUDENTS"

칼럼에 값 넣기
INSERT INTO STUDENTS (SID,PWD) VALUES ('김창훈','777')

데이터 조회하기
SELECT * FROM STUDENTS : STUDENTS에 있는 데이터를 전부 조회.
                         조회하고 싶은것을 선택하고 싶은때는 * 대신 SID,PWD를 쓰면 된다.

DELETE STUDENTS WHERE SID='NA'
:SID의 NA가 포함된것 삭제

KCH가 있는 곳에 이름이랑 성별 업뎃
UPDATE STUDENTS SET NAME='창훈',GENDER='남' WHERE SID='KCH'

데이터 조회하기 이것들은 로그에 임시로 저장해 놓은 상태이다
ROLLBACK하면 쉬소, COMMIT 하면 완전 저장

SQL 연산자
나이에 +10  : SELECT AGE+10 FROM STUDENTS
원하는 컬럼만 조회 : SELECT SID,NAME FROM STUDENTS
원하는 컬럼을 별칭줘서 조회 : SELECT SID AS ID FROM STUDENTS
AS는 생략해도 된다

테이블 컬럼이 없을때 계산 방법
SELECT '3'+10 FROM DUAL : DUAL은 임의의 테이블
-> +는 무조건 숫자만 연산하려 하기 때문에
'3'을 숫자 3으로 변경
'3'을 'A'로 바꾸면 수치가 부족하다는 에러가 뜸

문자열을 더해주는 연산자 ||
SELECT 'A'||'BC' FROM DUAL  : 계산결과 ABC

나이가 20이상인것만 조회하시오
SELECT * FROM STUDENTS WHERE AGE>20;

주소를 입력하지 않을것을 조회하시오
SELECT * FROM STUDENTS WHERE ADDRESS IS NULL

주소 입력한 것을 조회하시오
SELECT * FROM STUDENTS WHERE ADDRESS IS NOT NULL

나이가 20,21,22인 회원을 조회하시오
SELECT * FROM STUDENTS WHERE AGE=20 OR AGE=21 OR AGE=22;
SELECT * FROM STUDENTS WHERE 20<=AGE AND AGE<=22;
SELECT * FROM STUDENTS WHERE AGE BETWEEN 20 AND 22;

나이가 20,25,27인 회원을 조회하시오
SELECT * FROM STUDENTS WHERE AGE=20 OR AGE=25 OR AGE=27;
SELECT * FROM STUDENTS WHERE AGE IN (20,25,27);

나이가 20,25,27이 아닌 회원을 조회하시오
SELECT * FROM STUDENTS WHERE AGE NOT IN (20,25,27);
회원중에 김씨 성을 조회하시오
SELECT * FROM STUDENTS WHERE NAME LIKE '김%';

회원중에 김씨성을 제외한 회원을 조회하시오
SELECT * FROM STUDENTS WHERE NAME NOT LIKE '김%';

회원중에 박이 들어간 사람들 조회하시오
SELECT * FROM STUDENTS WHERE NAME LIKE '%박%';  
: 앞뒤 어떤 길이의 문자가 와도 박이 들어가면 조회
SELECT * FROM STUDENTS WHERE NAME LIKE '박_'; 
: 박뒤에 한글자만 있는 사람을 조회(자릿수를 결정)

회원의 나이를 조회하고, 동일한 나이는 생략
SELECT DISTINCT AGE FROM STUDENTS;

모든 회원을 조회하시오. 단 레코드 번호를 함께 출력하시오
SELECT ROWNUM, STUDENTS.* FROM STUDENTS
ROWNUM 은 오라클에서 제공하는 행넘버

1번째 회원부터 5번째 회원까지만 조회하시오.
SELECT * FROM STUDENTS WHERE ROWNUM BETWEEN 1 AND 5;
SELECT ROWNUM, STUDENTS.* FROM STUDENTS WHERE ROWNUM BETWEEN 1 AND 5;

문자열 덧셈 함수
SELECT CONCAT('홍','길동')FROM DUAL

문자와 코드
SELECT ASCII('Q')FROM DUAL

73번째 문자
SELECT CHR(73) FROM DUAL;

문자열 추출함수 SUBSTR(문자열,시작위치,길이)
SELECT SUBSTR('HELLO',1,3)FROM DUAL   : 결과 HEL
SELECT SUBSTR('HELLO',0,3)FROM DUAL : 0에서부터 글자를 만나는 순간부터 3개 뽑아냄
SELECT SUBSTR('HELLO',3)FROM DUAL :결과 LLO

이름과 생년월일에서 월만 조회
SELECT NAME,SUBSTR(BIRTHDAY,6,2) FROM STUDENTS 

문자열을 소문자,대문자로 변경하기
SELECT LOWER('NeWIEC')FROM DUAL; : 다 소문자로 변경
SELECT UPPER('neWLec')FROM DUAL; : 다 대문자로 변경

회원의 아이디가 NEWLEC인 회원을 조회하시오(대소문자 안가림)
SELECT * FROM STUDENTS WHERE UPPER(SID)=UPPER('NEWLEC');
--다 대문자로 바꿔놓고 조회 (근데 왜 대문자로 안바뀌지??)

문자열 대치 함수 REPLACE(문자열,찾는문자열,대치할 문자열)/TRANSLATE()
SELECT REPLACE ('WHERE WE ARE','WE','YOU') FROM DUAL;
:WHERE YOU ARE (WE 를 YOU로 바꿔줌)
SELECT TRANSLATE ('WHERE WE ARE','WE','YOU') FROM DUAL;
:YHORO YO ARO (W를 Y로,E를 O로)

주소를 띄어쓰기 없이 출력
SELECT REPLACE (ADDRESS,' ','') FROM STUDENTS ;

첫글자를 대문자로 바꿔주는 역할
SELECT INITCAP('the most important thing is..')FROM DUAL;
: The Most Important Thing Is..

문자열 패딩 함수(5글자지만 10개로 늘리고싶다)
SELECT LPAD('HELLO',10,'0')FROM DUAL;
:00000HELLO

문자열의 길이를 얻는 함수
SELECT LENGTH('WHERE WE ARE')FROM DUAL;
: 12

모든 회원의 핸드폰 번호와 번호의 문자열 길이를 조회
SELECT PHONE,LENGTH(PHONE)FROM STUDENTS

문자열 검색함수 INSTR(문자열,검색문자열,위치,찾을수)
SELECT INSTR('ALL WE NEED TO IS JUST TO..','TO')FROM DUAL
: 13
SELECT INSTR('ALL WE NEED TO IS JUST TO..','TO',15)FROM DUAL
:여기서 15는 15번째부터 찾아라.
SELECT INSTR('ALL WE NEED TO IS JUST TO..','TO',1,2)FROM DUAL
:1번째부터 검색하고,2번째 TO를 찾아라

회원의 전화번호에서 두번째 -가 존재하는 위치를 조회
SELECT PHONE,INSTR(PHONE,'-',1,2)FROM STUDENTS

회원의 전화번호에서 첫번째-와 두번째- 문자 간격?
SELECT PHONE,INSTR(PHONE,'-',1,2)-INSTR(PHONE,'-',1,1)-1 FROM STUDENTS

회원의 전화번호에서 첫번째와 두번째 사이의 번호를 출력하시오.
SELECT PHONE,SUBSTR(PHONE,5,INSTR(PHONE,'-',1,2)-INSTR(PHONE,'-',1,1)-1) FROM STUDENTS;

회원중에서 앞번호가 016인거
SELECT * FROM STUDENTS WHERE PHONE LIKE'016%';

현재 시간을 얻는 함수
SELECT SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP FROM DUAL
:SYSDATE 현재 날짜
 CURRENT_DATE 접속자의 시간
 SYSTIMESTAMP 시간까지 나오는거
 CURRENT_TIMESTAMP 시간,지역

날짜 추출함수 EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM)
SELECT EXTRACT(YEAR FROM SYSDATE)FROM DUAL
:년도만 출력
SELECT EXTRACT(DAY FROM SYSDATE)FROM DUAL
:월만 출력

가입 회원중에 2,3,11,12월달에 가입한 회원을 조회
SELECT * FROM STUDENTS WHERE EXTRACT(MONTH FROM BIRTHDAY)IN (2,3,11,12) ;

가입일이 현재로부터 6개월이 안된 회원 조회
SELECT * FROM STUDENTS WHERE EXTRACT(MONTH FROM SYSDATE)-EXTRACT(MONTH FROM REGDATE)<6;
: 현재 날짜에서 가입날짜를 빼고 6보다 작으면 출력
SELECT * FROM STUDENTS WHERE REGDATE>ADD_MONTHS(SYSDATE,-6);

 

구절문
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
순서가 바뀌면 안된다.

원하는 행을 필터링: WHERE

그룹으로 묶어서 조회하기 위한 구절
:GROUP BY/HAVING

정렬을 위한 구절
:ORDER BY

정렬순서
ASC(ascending,오름차순)DESC(descending,내림차순)

이름을 기준으로 역순으로 정렬해서 조회
SELECT*FROM STUDENTS ORDER BY NAME DESC

김씨성을 조회하고 나이는 오름차순으로 정렬
SELECT*FROM STUDENTS WHERE NAME LIKE '김%' ORDER BY AGE ASC
:1.FROM STUDENTS 가 먼저 실행
 2.WHERE NAME LIKE 가 실행
 3.ORDER BY 가 실행
 4.SELECT 실행

나이를 기준으로 역순 정렬, 나이가 같을 경우 아이디를 기준으로 오름차순 정렬
SELECT*FROM STUDENTS ORDER BY AGE DESC,NAME ASC

집계 함수
SUM, MIN, MAX, COUNT, AVG

가입한 회원수를 조회
SELECT COUNT(SID) FROM STUDENTS

가입한 회원의 성별 회원수를 조회
SELECT GENDER,COUNT(SID) FROM STUDENTS GROUP BY GENDER

가입한 회원의 나이대별 회원수를 역정렬
SELECT AGE,COUNT(SID) FROM STUDENTS GROUP BY AGE ORDER BY COUNT(SID) DESC

성별 평균 나이
SELECT GENDER,AVG(AGE) FROM STUDENTS GROUP BY GENDER

성씨 몇명이 있는지 조회
SELECT SUBSTR(NAME,1,1),COUNT(NAME) FROM STUDENTS GROUP BY SUBSTR(NAME,1,1);

박씨성의 회원수
SELECT COUNT(SID)FROM STUDENTS WHERE NAME LIKE '박%';
SELECT COUNT(SID)FROM STUDENTS WHERE SUBSTR(NAME,1,1)='박';

부조회(서브쿼리)

값을 얻는 종류 : 

단일형(스칼라, COUNT,SUM,MIN등. 서브 쿼리를 넣을 수 있는 공간 GROUP BY, ORDER BY), 

목록형(서브 쿼리를 넣을 수 있는 공간 WHERE,HAVING) 

격자형(서브 쿼리를 넣을수 있는 공간 FROM)

동적인 결과를 얻기위해 구절의 순서가 중요한경우
SELECT *FROM STUDENTS WHERE ROWNUM BETWEEN 1 AND 10;
이름 순으로 역정렬한 결과에서 상위 열명을 원하는 경우라면?
SELECT*FROM STUDENTS ORDER BY NAME DESC WHERE ROWNUM BETWEEN 1 AND 10
:정렬한후 10명을 뽑아라 근데, 명령어가 올바르게 종료되지 않는 에러
SELECT*FROM (SELECT*FROM STUDENTS ORDER BY NAME DESC)WHERE ROWNUM BETWEEN 1 AND 10;

단일 값을 반환하는 경우:비교연산에 사용
SELECT*FROM STUDENTS WHERE AGE>(SELECT AVG(AGE) FROM STUDENTS)
:평균 나이보다 높은 사람출력,(SELECT AVG(AGE) FROM STUDENTS)이게 단일값

목록을 반화하는 경우:목록형 연산에 사용
SELECT*FROM STUDENTS WHERE AGE IN(SELECT AVG(AGE) FROM STUDENTS GROUP BY ADDRESS)
:도시별 나이 평균을 구하기 위해

서브 쿼리 연산자
(), ANY,EXISTS,ALL

모든 성별의 평균 나이보다 큰 회원만 출력
SELECT*FROM STUDENTS WHERE AGE>ALL(SELECT AVG(AGE) FROM STUDENTS GROUP BY GENDER)
:ALL은 각 성별의 나이의 평균을 구하고 그 중 큰 값을 뽑아내서 그보다 큰 나이를 출력

하나 이상의 성별 평균키보다 큰 회원을 출력
SELECT*FROM STUDENTS WHERE AGE>ANY(SELECT AVG(AGE) FROM STUDENTS GROUP BY GENDER)
:ANY 는 각 성별의 나이의 평균을 구하고 그 중 작은 값을 뽑아내서 그보다 작은 나이를 출력

조인
INNER JOIN, OUTER JOIN, SELF JOIN, CORSS JOIN

NOTICES 와 STUDENTS를 연결시켜주는거
SELECT SEQ,TITLE,WRITER,NAME FROM
STUDENTS INNER JOIN NOTICES ON STUDENTS.SID=NOTICES.WRITER
:각각에서 SEQ,TITLE,WRITER,NAME을 연결.여기서 SID와 WRITER이 동일해야한다.
필드 이름이 같을 경우(WRITEH가 NAME으로 변경됐다고 했을 경우)
SELECT NOTICES.SEQ, NOTICES.TITLE, STUDENTS.NAME FROM
STUDENTS INNER JOIN NOTICES ON STUDENTS.SID=NOTICES.WRITERH
필드 이름이 너무 길어서 귀찮다면 테이블의 별칭을 사용
SELECT N.SEQ, N.TITLE, S.NAME FROM
STUDENTS S INNER JOIN NOTICES N ON S.SID=N.WRITER
:주의-별칭만 사용을 해야한다

 회원별 작성한 게시글 수
SELECT S.SID,S.NAME,COUNT(N.SEQ)FROM
STUDENTS S INNER JOIN NOTICES N ON S.SID=N.WRITER GROUP BY S.SID,S.NAME

참조키를 기준으로 일치하지 않는 행도 포함시키는 조인
SELECT STUDENTS.SID,STUDENTS.NAME,NOTICES.SEQ FROM
STUDENTS LEFT OUTER JOIN NOTICES ON STUDENTS.SID=NOTICES.WRITER
:LEFT대신 RIGHT, FULL을 넣어도 된다.
LEFT면 STUDENTS에서 글을 남기지 않은 사람의 값도 출력(?)

회원수 게시글이 0인것도 같이 출력
SELECT S.SID,S.NAME,COUNT(N.SEQ)FROM
STUDENTS S LEFT OUTER JOIN NOTICES N ON S.SID=N.WRITER GROUP BY S.SID,S.NAME;

자기가 자신을 참조하는 조인
SELECT B.NAME BOSS, M.NAME "MEMBER" FROM
STUDENTS B JOIN STUDENTS M ON B.SID=M.BOSS ORDER BY B.NAME;

컬럼이 같은 테이블의 레코드를 합치는 연산
SELECT SEQ,CONTENT,WRITER FROM NOTICES UNION
SELECT SEQ,CONTENT,WRITER FROM COMMENTS;
합쳐서 검색했는데, 검색이 어디서 됐는지 위치 확인
SELECT*FROM(
SELECT SEQ,CONTENT,WRITER,'NOTICES'TBL FROM NOTICES UNION
SELECT SEQ,CONTENT,WRITER,'COMMENTS'TBL FROM COMMENTS
)
WHERE CONTENT LIKE '%d%';
:TBL의 별칭에서 COMMENTS와 NOTICES를 표시해준다

검색된 결과가 테이블당 몇개인지 확인
SELECT TBL, COUNT(SEQ) FROM (
  SELECT SEQ, 'NOTICES' TBL FROM NOTICES UNION
  SELECT SEQ, 'COMMENTS' TBL FROM COMMENTS
)
GROUP BY TBL;

VIEW

조인 문장을 VIEW로 만들어 놓기
CREATE VIEW NOTICESVIEW777 AS
SELECT N.SEQ,N.TITLE,N.WRITER,S.NAME,COUNT(C.SEQ)CNT FROM
STUDENTS S RIGHT OUTER JOIN NOTICES N ON S.SID=N.WRITER
LEFT OUTER JOIN COMMENTS C ON N.SEQ=C.NOTICESEQ
GROUP BY N.SEQ,N.TITLE,N.WRITER,S.NAME;
VIEW를 이용해 쿼리하기
SELECT*FROM NOTICESVIEW777 WHERE TITLE LIKE '%오%'ORDER BY SEQ DESC;

게시글 나눠서 보기
SELECT *
FROM (SELECT ROWNUM NUM,N.* FROM (SELECT * FROM NOTICES ORDER BY REGDATE DESC)N)
WHERE NUM BETWEEN 1 AND 15;