KA's Regalo

1. 더미테이블

2. 단일 함수 : 값을 하나만 리턴

3. 그룹 함수 : 여러개의 값을 리턴

1. 더미 테이블

> select * from dual;

--DB를 통해서 기본적으로 제공해주는 테이블

> select sysdate from dual;

--sysdate는 컬럼명이 아닌  함수. 현재의 시간, 날짜 들을 반환해준다.  

> select '안녕하세요' as hello from dual;

--hello라는 필드는 '안녕하세요'라는 뜻을 갖게 됨

> select 1+2 as "1+2=" from dual;

> select 1+2 from dual;

> select 5/3 from dual;

--정수형으로 반환x  

--쿼리문으로 처리해서 반환받는것도 가능하지만 자원소모가 심하다

> select kor+eng+math from student;

--더미테이블을 이용하는 것은 DB가 연산작업을 하는 것이므로 자원소모가 심하다

--반드시 필요한 경우가 아니라면 사용하는 것 자제

> select no,name,kor+eng+math as sum from student;

--sum값을 데이터에 저장한다는 것은 데이터 공간을 차지한다는 의미

2. 단일함수

> select * from student;

> select sum(kor),sum(eng),sum(math) from student;

--반전체의 국어, 영어, 수학 점수

--리턴값이 하나 

> select sum(kor),sum(eng),sum(math), kor, eng, math from student;

--단일과 다중 둘다 달라고 하면 동시 수행이 되지 않는다

> select max(kor),min(eng), avg(math) from student;

--최대점수, 최소점수, 평균

--단일 점수끼리는 서로 처리가 가능

> select count(*) from student;

> select count(kor) from student;

--총 몇개인지

> select stddev(eng) from student;

--표준편차

3. 그룹함수(다중 함수)

--그룹함수는 컬럼을 주면 컬럼 내용들이 해당의 명령으로 수행되서 리턴결과를 볼 수 있다

> select 'javaWorld' from dual;

--컬럼명이 자동으로 대문자로 나옴

--오라클 명령어는 대소문자 구분x, value값은 대소문자 구분

--문자열 제어하는데 함수 제공

> select 'javaWorld', lower('javaWorld')  from dual;

--소문자로 리턴

> select 'JavaWorld',lower('JavaWorld'),upper('JavaWorld') from dual;

--대문자로 리턴

> select 'JavaWorld',lower('JavaWorld'),upper('JavaWorld'), initcap('JavaWorld') from dual;

--첫글자만 대문자로 반환

> select 'JavaWorld', length('JavaWorld'),length('자바') from dual;

--길이 반환(9,2)

> select 'JavaWorld', lengthb('JavaWorld'),lengthb('자바') from dual;

--lengthb는 실제 메모리에 할당되는 갯수이다(결과:9,6)

--영문자는 1byte,한글은 3byte. 디폴트값으로 영문자 외의 것들을 3byte이다

--오라클은 세계 모든 문자를 사용하기 위해 4byte를 사용

/* 

> desc student; 해서 보면 

name값으로 20byte를 할당

글자 하나당 한국어3byte라서 21이 아니기에 오류가 날 수 있다. 

> insert into student values(5, 10, 20, 30, '이사람은사람임');

넣는 순간 -> 값이 너무 큼(실제:21, 최대값 20)

*/

그룹함수(다중 함수)

==문자열==

--substr

> select 'JavaWorld',substr('JavaWorld', 1,4) from dual;

--1번째부터 4번째

--주의: 인덱스 0번부터가 아니라 1번부터

> select 'JavaWorld',substr('JavaWorld', -5,5) from dual;

--끝에서부터 5번째인 W부터 5개 d까지 나온다: World가 나옴. 

> select 'JavaWorld',substrb('JavaWorld', 1,4) from dual;

> select 'JavaWorld',substrb('자바월드', 1,6) from dual;

--한글자당 3byte므로 자바까지 나오려면 6byte필요 

> select 'JavaWorld',substrb('자바월드', -6,6) from dual;

--값은 월드

--instr

> select 'JavaWorld',instr('JavaWorld', 'W') from dual;

--특정위치에서 찾아서 반환할때 (대소문자 구분)

--a라면 첫번째 위치인 2개 추출됨

--두번째 a를 구하려면

> select 'JavaWorld',instr('JavaWorld', 'a',3) from dual;

--3부터 'a'를 구하는 것을 시작하겠다

> select 'JavaWorld',instr('JavaWorld', 'a',1,2) from dual;

--첫번째부터 시작하지만 2번째값을 얻겠다

> select 'JavaWorld',instr('한글로 자바를 자바라 칭함', '자바') from dual;

> select 'JavaWorld',instr('한글로 자바를 자바라 칭함', '자바',1,2) from dual;

--9가 나옴. 띄어쓰기 포함

> select 'JavaWorld',instrb('한글로 자바를 자바라 칭함', '자바') from dual;

--instrb-> 띄어쓰기값 1 포함(값 11)

--trim 

> select 'JavaWorld',trim('      한글로 자바를 자바라 칭함    ') from dual;

--앞뒤 공백 자르기. 자원을 관리하기 위해 

> select 'JavaWorld',trim('a' from 'aaaaaa한글로 자바를 자바라 칭함aaaaa') from dual;

--그냥 trim은 공백자르기. 'a' from을 쓰면 a문자를 잘라준다. 

> select '###100' from dual;

> select trim('#' from '###100') from dual;

--lpad,rpad

> select lpad('100', 6,'0') from dual;

--총 6개의 문자로 만들고 부족한 것은 앞(left)에 0으로 채워라 

> select rpad('100', 6,'#') from dual;

--뒤(right)에 채워라

--concat,replace

> select concat('java','world')from dual;

-- +

> select replace('javaworld','w','W')from dual;

--문자열 치환(대상, 바꿀내용)

> select replace('javaworld','w','')from dual;

--삭제

==숫자==

> select 1 from dual;

> select 1+3 from dual;

> select 1-3 from dual;

> select abs(1-3) from dual;

--절대값으로 반환 

> select 5/3 from dual;

> select floor(5/3) from dual;

--소수점 이하 절삭

> select round(5/3,1) from dual;

--반올림. 소수첫째자리까지 

> select round(5/3,0) from dual;

--반올림. 정수로만

> select round(12345)from dual;

> select round(12345,-1)from dual;

--첫째자리 반올림

> select round(12345,-3)from dual;

--셋째자리 반올림

> select trunc(155555,2)from dual;

--절삭하고 싶다면 

> select trunc(15,55555,1)from dual;

--나머지 함수

> select mod(5,3)from dual;

--나머지 2

> select * from student where mod(kor+eng+math,2)=0;

--* 기억해두기! 전체에서 찾는 의미

--성적의 합이 짝수인 학생

* 연산이 수월할지 IO로  일일이 쏘는것이 더 수월할지 생각해야한다. 

==날짜==

> select sysdate from dual;

> select sysdate-1 from dual;

--(+1: 내일 날짜,-1: 어제 날짜)

> select ename,sysdate-hiredate from emp;

--입사한 날로부터 얼마나 지났는지.소숫점은 시간기준 , 소수점 절삭하면 지나온 날짜 

> select next_day(sysdate,'금') from dual;

--금요일 날짜 

> select next_day(sysdate,5) from dual;

--1: 일요일 2: 월요일 3: 화요일 4: 수요일 5: 금요일 6.토요일

> select last_day(sysdate) from dual;

--해당의 마지막 날짜 (16/8/31출력)

> select round(sysdate,'MONTH') from dual;

--반올림처리(8/1출력)

> select TRUNC(sysdate,'MONTH') from dual;

--버림 //항상 1일 얻어냄. 

> select substr(sysdate,1,2) from dual;

--1~2글자를 가져옴으로 년도출력

> select replace(sysdate,'/','.') from dual;

--치환 (/ -> .)

--타입이 문자열타입이 아니다

> select sysdate,to_char(sysdate,'YYYY-MM-DD') from dual;

--포맷을 원하는 모양으로 바꿀 수 있다. 

> select sysdate,to_char(sysdate,'YY-MON-DD DY') from dual;

--요일도 알 수 있다

> select sysdate,to_char(sysdate,'YY-MM-DD DY,HH:MI:SS') from dual;

--현재 시간

> select sysdate,to_char(sysdate,'YY-MM-DD DY,HH24:MI:SS') from dual;

--24시간 형태로

> select sysdate,to_char(sysdate,'YY-MM-DD DY, PM:MI:SS') from dual;

--AM 이나 PM 둘중 하나로 쓰면 된다. 상관없음

> select 1000, to_char(1000,'9,999') from dual;

--실제로 숫자는 그대로 넣되, 콤마로 표현(값 1,000) 

> select 1000.00, to_char(100000,'L999,999,999') from dual;

--소수점으로 해봤을때. 마찬가지로 동일한 퍼포먼스 나옴.

--L 원

> select 1000.00, to_char(100000,'$999,999,999') from dual;

--$ 달러

> select ename,hiredate from emp where hiredate='80/12/17';

--입사날짜가 80/12/17인

> select ename,hiredate from emp where hiredate='801217';

--오류

> select ename,hiredate from emp where hiredate=to_date(19801217,'YYYYMMDD');

> select sysdate-'2016/01/01' from dual;

> select sysdate-to_date('2016/01/01','YYYY/MM/DD') from dual;

--날짜 데이터로 변환 to_date // 포맷 설정

> select trunc(sysdate-to_date('2016/01/01','YYYY/MM/DD')) from dual;

--시간알고 싶지 않으면. 절삭. 

> select '1,000'+'5,000' from dual;

--오류 

> select to_number('1,000','999,999')+to_number('5,000','999,999') from dual;

> select to_char(to_number('1,000','999,999')+to_number('5,000','999,999')) from dual;

오라클 설치 후

cmd창에서 실행

>sqlplus system/oracle

--sql 연결 id/pw 로서 여기서 pw는 오라클 설치시 내가 정해준 pw이다.

>SELECT  DBMS_XDB.GETHTTPPORT( ) FROM DUAL;

--포트번호 확인

>EXEC DBMS_XDB.SETHTTPPORT(9090);

--포트번호 변경

>ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;

--HR계정 잠김 풀기(SYSTEM이상의 권한필요)

--첫번째 HR은 아이디, 두번째 HR은 비밀번호

>sqlplus scott/tiger

--scott계정은 교육용이다

--주석이다

1. 테이블 보기 (;꼭 붙이기)

>SELECT * FROM DEPT ;

2. 원하는 것만 보고자 할때는 SELECT 컬럼명, 컬럼명...FROM DEPT; 이런식으로 쓰면 된다

>SELECT * FROM EMP ;

>SELECT ENAME,HIREDATE,SAL FROM EMP;

--테이블 간에 연결이 되어 있음을 볼 수 있다. (관계형 데이터모델)

3. 컬럼명 변경하기

>SELECT ENAME AS "사원명", HIREDATE AS "입사일", SAL AS "연봉" FROM EMP;

--SELECT ENAME AS 사원명, HIREDATE AS 입사일, SAL AS 연봉 FROM EMP; 이렇게도 되지만 띄어쓰기를 쓰면 명령어로 인식하므로 문제 발생

--" "를 쓰면 대소문자, 특수문자 등을 쓸 수 있다

4. 중복된 데이터 걸러내기

>SELECT DISTINCT JOB FROM EMP;

>SELECT DISTINCT JOB ENAME FROM EMP;

>SELECT DISTINCT DEPTNO FROM EMP;

5. WHERE 조건과 비교연산자

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE SAL>=2000;

--SAL이 2000보다 크거나 같은 경우

--  = , > , < , >=,<=

--  NOT의 의미 3가지 !=, <>, ^= 

6. 문자 데이터 조회

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE ENAME='SMITH';

>SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE >='1985/01/01';

// 오라클에서 문자열은 ' ' . 그리고  밸류값이므로 대소문자 구분

--또는 '85/01/01'

7. 다중조건 

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE SAL>=2000 AND SAL<2500;

-- AND , OR 

8. 연산자가 아닌 키워드 NOT

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE NOT SAL>=2000;

--NOT이 붙었으므로 2000보다 작은 

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE NOT JOB='MANAGER';

--JOB이 MANAGER가 아닌

9. BETWEED AND 

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE SAL BETWEEN 1000  AND 2000;

--1000에서 2000사이의 값

10. IN

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE JOB IN('CLERK','MANAGE');

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE SAL IN(1300,1250); 

--정해진 두개의 값만

11. LIKE, 와일드카드

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE JOB LIKE '%A%';

--JOB에 대문자 A가 포함된것들이 나옴

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE JOB LIKE '_A%';

--두번째 자리에 A가 나오게 함. 첫번째, 세번째가 와일드. 무엇이 와도 상관없음

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE JOB LIKE 'A%';

--A 와일드 

>SELECT EMPNO,ENAME,JOB, HIREDATE,SAL FROM EMP WHERE JOB LIKE '_______';

--7자로 된 모든 것

12. NULL

>SELECT * FROM EMP WHERE COMM IS NULL;

--값이 없는 것들을 찾을때

>SELECT * FROM EMP WHERE COMM IS NOT NULL;

--값이 있는 것들을 찾을때. 0도 있는 것이다.

13. ORDER BY: 오름,내림차순 정렬하기

>SELECT * FROM DEPT ORDER BY DEPTNO;

>SELECT * FROM DEPT ORDER BY DEPTNO DESC;

--내림차순

>SELECT * FROM DEPT ORDER BY DEPTNO ASC;

>SELECT * FROM EMP ORDER BY SAL ASC;

--오름차순

>SELECT * FROM EMP ORDER BY SAL ASC,ENAME DESC;

--연봉순서에서 연봉 중복이 나올때 이름순서로 보고 싶으면

1. 테이블 생성

-- CREATE TALBE TABLENAME( 

--  컬럼명 자료형,

--  컬럼명 자료형,

--  ...

-- );

-- 타입: 숫자 number, number(38), number(전체, 소수점이후)

--        문자열 varchar2(), char(), varchar()

--        날짜 date

--        문자열 (특수) long, lob

*varchar2(10) 인 경우는 10의 메모리 공간을 사용할 수 있되 값이 들어가지 않는한 용량차지 없다 (동적으로 할당)

char(10) 인 경우에 10의 메모리를 주어진다 (비어있더라도)

varchar() 인경우는 오라클에서 사용하지 말라고 권한다 (2와 기능 동일)

long 가변적이라 크기지정 없다(최대 2G까지 저장). 그러나 제어 어렵고 느리다

lob(2G),바이너리,,파일을 직접넣는다.(이미지,,등) 느리고 db부하가 크다

> CREATE TALBE STUDENT( 

  NO NUMBER,

  KOR NUMBER,

  ENG NUMBER,

  MATH NUMBER

  NAME VARCHAR2(20)  

  );

--여기까지 스키마가 만들어졌다.

2. 값 입력  

--INSERT INTO TABLENAME(필드들) VALUES (필드와 매칭되는 VALUE값들);

> INSERT INTO STUDENT(NO,KOR,ENG,MATH,NAME) VALUES (1,90,80,70,'홍길동'); 

> INSERT INTO STUDENT VALUES(2,80,70,60,'홍길자');

> INSERT INTO STUDENT(NAME,KOR,NO,MATH,ENG) VALUES('이길자',50,3,60,80);

> INSERT INTO STUDENT(NO,NAME) VALUES(4,'박길동');

> INSERT INTO STUDENT VALUES (5,50,60,80,NULL);

> INSERT INTO STUDENT(NO,KOR,ENG,MATH) VALUES (5,50,60,80);

--입력할때 타입이 달라지면 오류

--쓰고 싶지 않으면 NULL을 넣으면 된다. 

> INSERT INTO STUDENT VALUES (5,50,60,80);

--오류. 

3. 테이블 확인하기

>DESC STUDENT; 

--테이블 정보 확인 

>SET LINESIZE 100 

--보기좋기 하기 위해 넓이를 넓게

>SELECT * FROM STUDENT; 

--내가 만든 테이블 전체 확인

>SELECT * FROM TAB; 

--어떤것들이 있는지 모른다하면 이것을 통해 테이블들을 확인할 수 있다.

/*

> SELECT * FROM STUDENT WHERE KOR=NULL;

> SELECT * FROM STUDENT WHERE KOR IS NULL;

> SELECT * FROM STUDENT WHERE KOR IN(NULL);

> SELECT * FROM STUDENT WHERE NAME=NULL;

//얘도 안된다.

*/

4. 수정

--UPDATE TABLENAME SET 바꿀필드명=바꿀 값들 ~~~WHERE 조건;

> UPDATE STUDENT SET KOR=90, ENG=80, MATH=70 WHERE NO=4;

--조건이 없으면 전체가 다 바뀌어버린다. 반드시, 수정 삭제때는 WHERE 조건을 사용 

5. 삭제

--DELETE FROM TABLENAME WHERE 조건;

> DELETE FROM STUDENT WHERE NO=4;

--조건반드시 필요

/* INSERT INTO STUDENT(NO,KOR,ENG,MATH,NAME) VALUES('6', '22' , '33' , '44', '홍길동');

    숫자에 문자를 주는것은 문제가 되지 않는다 

 하지만, 문자를 숫자로 주는 것은 문제가 된다

*/

/*

SELECT * FROM STUDENT WHERE NO=1;

만일 조건이 중복되어버리면? 

내 컴퓨터에서 데이터에 입력값을 주는데 다른 컴퓨터에서 또 입력을 해줘서 중복이 되버린다면? 

동일 학번을 다른값으로 줘버리는 문제가 생김. 

그럴 경우에는 DB에서 아예 입력오류를 냄. 

*/