악덕고용주의 개발 일기

[    테이블 수정하기 Alter Table    ]

테이블을 생성하고... DROP하는건 다 하실 수 있다는 전제하에 Alter table에 관해 포스팅을 진행하겠습니다.

1. 테이블에 새로운 컬럼(필드)를 추가하기

ex)

Alter Table 테이블명 ADD name varchar2(10) Not null

2. 테이블의 특정 필드의 데이터 타입을 변경하기

ex)

Alter Table 테이블명 MODIFY name varchar2(30)

3. 테이블의 특정 컬럼을 삭제하기

ex)

Alter Table 테이블명 DROP COLUMN 컬럼명;

4. 테이블의 컬럼명 변경하기

ex)

Alter Table 테이블명 RENAME COLUMN 변경전컬럼명 TO 변경할컬럼명;

5. 테이블의 특정 컬럼에 제약조건 추가하기

ex)

Alter Table 테이블명 ADD CONSTRAINTS 제약조건별칭 Primary key (col3);

Alter Table 테이블명 ADD CONSTRAINTS 제약조건별칭 Foreign key (bno) references 참조할테이블(참조할컬럼);

6. 테이블의 특정 컬럼에 걸려있는 제약조건 삭제하기

ex)

Alter Table 테이블명 DROP CONSTRAINTS 제약조건별칭;

참고)

제약조건 별칭은

select * from user_constraints where table_name='테이블명' 으로 조회해서 제약조건 별칭을 알 수 있습니다.

[    Oracle DB 대표적으로 많이 쓰이는 데이터 타입 정리    ]

1. 문자

-    char(n)

: n Byte 고정 크기

-    varchar2(n)

: 최대 n Byte 크기지만 사용한 Byte에 맞게끔 저장됨 

ex) varchar2(20) 으로 20Byte로 생성했는데, "남자" 라고 2글자 4Byte만 저장했다면 4Byte로 저장되게 됨.

될 수 있으면 char보단 varchar2 사용이 권장된다.

참고)

Mysql에서는 varchar2가 아닌 => varchar(n) 을 사용하고, 긴 문자열을 다룰 때 text 라는 데이터 타입도 자주 사용됩니다.

2. 숫자

-    Number(p,s) 

숫자에서 가장 대표적으로 사용되는 데이터 타입입니다. 정수, 실수 다 사용 가능하며 p는 유효 숫자 개수, s는 소수점 이하 자리수를 가리킵니다.

ex) Number(5,1) 로 지정하고 데이터 234.15를 insert하면 5개 숫자가 유효하고 소수 첫번째 자리까지 표현함으로 반올림되어 234.2가 저장됩니다. 

만약, Number(3.1)로 해놧는데 234.15를 저장하면 소수점 이하 첫째 자리까지해서 234.2가 되더라도 총 4개의 숫자를 저장해야하는데 p를 3으로 입력했기 때문에

오류가 발생합니다.

p와 s는 생략 가능합니다.

참고)

Mysql에서는 Number 속성이 없습니다.

숫자를 사용할 때는

- int(n)

- bigInt(n)

- float 

등을 사용합니다. 이때 주의할 점은 bigInt의 경우 자바에서 String 타입으로 받아야 한다는 점입니다.!!

3. 날짜

-    Date

: 연/월/일 시/분/초 까지 표현할 수 있는 많이 사용되는 날짜 타입입니다.

default 값으로 현재 날짜를 입력하고자 할 때는 sysDate를 사용해 입력합니다.

ex) insert into table명(regdate) values( sysdate );

-    Timestamp

: Date가 시/분/초 까지만 표현 가능하다면 timestamp는 연/월/일 시/분/초 + ms(밀리세컨드) 까지 더 정밀하게 표현할 수 있습니다.

default 값으로 현재 날짜를 입력하고자 할 때는 systimestamp를 사용합니다.

ex) insert into table명(컬럼명) values( systimestamp );

참고)

Mysql에서는 now()를 이용해서 현재 시간을 입력할 수 있습니다.

4. NULL

: 값이 없음을 의미하는 데이터 타입입니다. 보통 테이블 생성시 컬럼에 별도로 설정하지 않으면 Null값을 허용하도록 default로 만들어지게 됩니다.

 따라서 insert시 컬럼에 값을 입력하지 않으면 Null이 들어가게 됩니다. 하지만

Null을 허용하고 싶지 않다면

제한자 객체인 "Not Null"을 사용하면 됩니다.

ex) regdate Not Null default systimestamp;

5. 대용량 데이터(LOB : Large Object Block)

: 대용량 데이터를 담기 위해 사용되는 타입입니다.

- 문자 : => CBLOB 을 사용합니다.

- 그 외(이미지,동영상 등) => BLOB을 사용합니다.

- ** 오늘 날짜 기준으로 7일이 지나지 않은 게시글만 뽑아오고 싶을 때

select * from 테이블명 where 날짜컬럼 > to_char(sysdate - 7, 'YYYYMMDD')

- 날짜 함수(sysdate, now())

sysdate : 시스템의 현재 날짜를 반환함

ex) select sysdate from dual; // dual은 가상의 테이블...

mysql에서는 now()를 사용함

ex) select now() from dual;

- to_char(날짜, 출력형식)

select to_char(sysdate, 'yyyy-MM-dd am HH:mi:ss day') from dual;

// mm : 2자리 월, mi: 2자리 분, HH : 24시간제, hh : 12시간제

// day : 요일

// am : 오전/오후

- 의사컬럼(모조컬럼)

// rownum : 레코드의 출력 번호

// rowid : 레코드의 주소값

select rownum, rowid, empno, ename from emp;

- SQL의 실행순서 : from -> select -> order

- add_months(날짜, 개월수)

select ename,hiredate,add_months(hiredate,3) from emp;

// 입수일자 + 3개월 뒤의 날짜를 뽑아냄

// 입사일자를 기준으로 3개월 후의 날짜

- last_day(날짜) : 날짜가 속한 달의 마지막 날

select sysdate, last_day(sysdate) from dual; => 17/03/31

select last_day('2017-02-01') from dual; => 17/02/28

- months_between(A,B) : A-B의 개월수

select empno, ename, months_between(sysdate,hiredate) from emp;

// round(실수값, 소수이하자리수)

select empno, ename, round(months_between(sysdate,hiredate), 2) as 근무개월수 from emp order by 근무개월수 desc;

- trunc() : 버림

- ceil() : 올림

- round() : 반올림

select ceil(months_between(sysdate,hiredate)) 근무개월수 from emp;

select trunc(months_between(sysdate,hiredate)) from emp;

- to_number(문자열) : 문자열을 날짜로 변경

- to_date(문자열, '날짜출력형식') : 문자열을 날짜형식으로 변경

select '2017-03-22' + 100 from dual; => 에러 발생

select to_date('2017-03-22','YYYY-mm-dd") + 100 from dual;

select to_number('100') + 1 from dual;

[ select문 ]

- 중복 배제 대표값 검색 : distinct

select distinct job from emp order by job;

- 정렬 : order by

select * from emp order by job, sal desc;

- 별칭 : as(생략 가능)

select ename as 이름, job 직급, sal 급여 from emp;

- 튜플수, 최대, 최소, 평균, 합계 함수

select count(*) 튜플수, sum(sal) 합계, avg(sal) 평균, max(sal) 최대값, min(sal) 최소값 from emp;

- 반올림 함수 : round()

select round( avg(sal) ) from emp;

- 범위 검색

급여가 300 이상 400이하인 직원

select * from emp where sal >= 300 and sal <= 400;

=

select * from emp where sal between 300 and 400;

급여가 300이상 400이하인 직원을 제외하고 출력

select * from emp where sal < 300 or sal > 400;

=

not : 결과 뒤집기( 급여가 300이상 400이하인 튜플을 제외하고 출력)

select * from emp where not(sal >= 300 and sal <= 400)

- 다르다. : !=  or  <>

select * from emp where job <> '부장'

select * from emp where job != '부장'

- ~로 시작하는을 검색하는 : Like

이름이 박 ~~로 시작하는 직원

select * from emp where ename like '박%';

이름이 ~~박으로 끝나는 직원

select * from emp where ename like '%박';

가운대 박이 들어가는 직원

select * from emp where ename like '%박%';

- 계산

select empno, ename, (sal * 12 + comm) as 연봉 from emp;

문제점 : sal이나 comm 두 항목 중 하나의 값이라도 null값이 들어 있으면 결과도 null이 나온다.

-> null과 연산시 무조건 결과는 null !!

해결 방법 : nvl() 함수 사용  nvl(A,B) : A가 null이 아니면 A를 사용하고 null이면 B값으로 사용

select empno, ename, (sal * 12 + nvl(comm,0)) as 연봉 from emp;

- null인지 아닌지를 비교 : is

    is not

: 문제점 : null값을 = 로 비교시 null과의 연산은 무조건 null이기 때문에 제대로 된 결과를 얻을 수 없음

ex) job이 null인 직원을 출력하라

select * from emp where job = null => 이렇게하면 아무것도 안나옴 null이 되어버려서 결과가

따라서,

select * from emp where job is null => 이렇게 해 주어야 함 "IS"를 사용 !! null인지 비교할 때는

<->

select * from emp where job IS NOT null => job이 null이 아닌 직원을 검색

- 날짜 비교

입사일이 2005년 1월 1일 이전인 사원을 검색하라

select * from emp where hiredate <= '2005-01-01';

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

[ 자주사용되는 내장 함수 ]

1. 문자를 합치는 concat("문자열1","문자열2") : 문자열1문자열2

select concat(ename,'의 직책은), concat(job, '입니다.') from emp;

2. 첫 글자를 대문자로 변환 : initcap('문자열')

select initcap('abcdef') from dual; => Abcdef

3. 소문자로 변환 : lower('문자열')

   대문자로 변환 : upper('문자열')

select lower('ABCDE') from dual; =>abcde

select upper('abcde') from dual; => ABCDE

4. ~~에서 ~~까지 잘라내는 substr('문자열','시작인덱스','몇글자') : ~부터 몇글자를 잘라낸다.

주의!) oracle은 시작 인덱스가 0부터가 아니라 1부터임!

예를들어, 주민등록번호에서 남자인지 여자인지 잘라내서 알아보려는 경우

select substr('900317-1081717',8,1) from emp; 가 되어야 함 시작인덱스가 1부터임으로 8이라는 것!

5. 문자열의 길이를 반환하는 : length('문자열') 

select length('hello') from dual; => 5

6. 문자열의 일부를 대체하는 : replace('원본문자열','변환하고싶은 문자열','바꿀 문자열')

select replace('java programming funny', 'funny', 'no funny') from emp;