비교연산자
= 같다
다르다 !=, <>, ^= 다를때 true
>,>=,<,<= 조건이 맞을때 true
함수 기술법
select 함수(컬럼명) from 테이블명;
컬럼을 연산자로 연산하여 새로운 결과를 얻어낼 수 있다.
컬럼+컬럼 or 컬럼+숫자
작성순서
select->from -> where-> group by-> having-> order by
집계함수는 group by 전에 쓸수 있다
특정 컬럼에null이 들어있는 데이터를 찾을때 = 연산자를 사용해서 찾을수 없다 (결과값 null)
null+0=null
null값을 다른값으로 변경해서 처리할때 함수
nvl, nvl2, decode
nvl(컬럼명,0) 컬럼명이 null일때 0을 출력함
nv12(컬럼명, 'y', 'n')컬럼명이 null이 아니면 y가 null이면 n이 출력된다
decode(컬럼명,null,0,컬럼명) 컬럼명이 가지고 있는 값이 null이면 0 아니면 컬럼명이 가지고 있는 값이 출력됨
특정 컬럼에 null이 들어있는 데이터를 찾고 싶다면
'where 컬럼명 is null' null이 아닌 데이터를 찾고 싶다면
'where 컬럼명 is not null'를 사용하면 됨
ex)
관리자가 없는 사원의 정보를 출력할때
select from employees where manager_id=null; 동작하지 않음
select from employees where manager_id is null; null인경우 선택
select * from employees where manager_id is not null null이 아닌경우 선택
커미션이 있는 사원의 정보를 출력할때
select * from employees where commission_pct is not null;
부서를 배정받지 못한 사원을 출력할때
select * from employees where department_id is null;
별명을 이용해 컬럼명을 변경할 수 있다
'select 컬럼명 별명' 'select 컬럼명 as 별명' 컬럼명 옆에 스페이스로 한칸 띄우고 별명을 기술하거나 as 다음에 별명을 기술하면 컬럼명이 별명으로 변경됨
concatenation(||)를 사용하여 문자열을 더한 결과로 만들수 있다
'select 컬럼명 || '과' || 컬럼명 as 합친칼럼명 from테이블
ex) 홍길동(hong) 이라고 출력하고 싶을때
select 컬럼 || '('|| ID ||')' from 테이블;
연산된 컬럼에 별칭 사용하기
select 컬럼 || '('|| ID ||')' 별명 from 테이블;
(조건문/1:1매칭)
decode(컬럼명,값1,변경값1,값2,변경값2~,'나머지경우변경값');
(조건문/ 범위매칭)
select 컬럼명
,case when 조건식1 then 값1
when 조건식2 then 값2
when 조건식3 then 값3
else 값4 end as 별명
from employees;
- decode같은 경우 1:1맵핑이지만 case 같은 경우는 범위 맵핑이 가능하다(조건문)
DECODE 함수는 프로그래밍에서의 if else 와 비슷한 기능을 수행한다. 간단한 사용방법은 아래와 같다.
예) DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3..........) where절에 논리 연산을 이용해서 여러 비교조건을 한번에 검색할 수 있다
and 연산은 여러 조건들이 모두 참일때 해당데이터를 읽어오는데 사용한다
or 연산자는 여러조건들 중에 하나라도 참이면 해당 데이터를 읽어 오는데 사용
not 조건에 만족하지 않는 데이터만 검색
distinct 중복데이터를 없애줌 범위 데이터를 구하기 쉽게 하기 위해서 between을 사용할 수 있다
컬럼 between a and b a,b를 포함한 사이값을 찾는데 사용
컬럼 not between a and b a,b를 포함한 사이값을 제외한 모든 데이터를 찾는다
between 대신 in을 사용하는게 더 편리
ex) in (a,b)
in 여러개의 값중 하나의 값이 존재하는지 확인할 수 있다
컬럼 in(값1,값2,값3) 해당 컬럼이 값1,2,3을 가지고 있으면 선택됨
컬럼 not in(값1,값2,값3) 해당 컬럼이 값1,2,3을 가지고 있지 않으면 선택됨
- 여러 테이블을 합쳐서 select
SELECT
r.review_idx, r.review_comment, u.user_idx, u.user_name, i.review_img_uri
FROM review AS r //메인테이블인 review테이블을 r로 치환
JOIN user AS u// 합칠 (user)테이블 선택 => 'JOIN', 합칠 테이블도 치환 => 'AS u'
ON u.user_idx = r.user_idx//user 테이블 안에서 어떤 유저를 선택할 것인가 => 'ON (조건)'
JOIN reviewImg AS i //reviewImg테이블을 i로 치환해서 합칠 것이다
ON i.review_idx = r.review_idx //메인인 review 테이블과 review_idx가 같은 reviewImg들을 말이다!
WHERE r.review_idx = ?
//또는
select name, product_name,price,amount,price*amount money
//회원의 이름과, 상품의 이름,물건 한개의 가격, 장바구니에 담은 개수, 총 물건의 가격을 검색한다.
from member m, product p, cart c
//member테이블과, product테이블과, cart테이블로부터 검색
where m.userid=c.userid and p.product_id=c.product_id;
//member의 userid와 cart의 userid가 같고, product의 product_id와 cart의 product_id가 같을때
//즉, 회원의 아이디와 상품을 사려는 회원이 같고, 상품의 번호와 카트의 번호가 같을때 - 조회 후 insert
INSERT INTO 들어갈테이블명
(컬럼명1, 컬럼명2, 컬럼명3)
SELECT 컬럼명1, 컬럼명2, 컬럼명3
FROM 조회할테이블명
WHERE 조건like로 특정문자를 뽑아냄
j로 시작하는 이름을 출력하라
select from 테이블 where 컬럼 like 'j%';
j가 들어가있는 이름을 출력하라
select from 테이블 where 컬럼 like '%j%';
j가 세번째에 있는 이름을 출력하라
select from 테이블 where 컬럼 like '__j%';
j가 들어가지 않은 이름을 출력하라
select from 테이블 where 컬럼 not like '%j%';
date 자료형은 연산을 통해서 시간차를 구할수 있다
현재시간은 sysdate 1 하루를 의미
select sysdate+1 from dual; 현재시간 +1은 내일
select sysdate-1 from dual; 현재시간 -1은 어제
sysdate-7; 일주일 전
하루를 24로 나누면 1/24가 되어 1시간이 된다
select sysdate+1/24 from dual; 현재시간 +1/24는 1시간 후
select systete-1/24 from dual; 현재시간 -1/24는 1시간 전
1/24/60 : 1분
1/24/60/60 : 1초
select firstDateType-secondDateType from dual;
두시간 firstDateType,secondDateType의 차이가 1이면 두 시간의 차이는 하루이고 1.5이면 1일하고 12시간 차이가 난다
select add_months(sysdate,1) from dual; 1달후를 의미
select add_months(sysdate,-1) from dual; 1달전을 의미
select add_months(sysdate,12) from dual; 1년후를 의미
- 1달후를 30 더해 계산하면 문제가 발생할수 있다
1달이 31 29 28인 경우가 있음 - 1년후를 365일로 더하면 문제가 발생할 수 있다
1년이 366일 경우가 있음
round
숫자뿐 아니라 날짜에도 반올림 가능
round(날짜,'month'): 15일을 기준으로 반올림
round(날짜,'year') : 6개월을 기준으로 반올림
trunc
숫자뿐 아니라 날짜에도 기준에 따라 잘라내기
trunc(날짜,'month') : 모든 날짜 데이터를 해당 월의 1일로 반환
trunc(날짜,'year') : 모든 날짜데이터를 해당연도의 1월 1일로 반환
to_char(숫자,문자) 숫자->문자로 변환
to_char('1234,9999 or 9,999') || 'hello' from dual;
출력-> 1234 or 1,234 hello
9->출력할 숫자수만큼 작성해야함, 9보다 숫자수가 적으면 공백이 생김, 9보다 숫자수가 적을때 9앞에 0을붙이면(0999)적은수 만큼 앞에 0이 생긴다
tO_number(문자) 문자->숫자로 변환
to_date(문자 or 숫자) 문자 or 숫자->날짜로 변환
주민번호로 나이구하기
방벙1)
주민번호에서 7번째 자리수를 뽑아서 그것이 1,2이면 1900년생, 그 외(3,4)이면 2000년생
select extract(year from sysdate) - (decode(substr(regist_no,7,1),'1','19','2','19','20') ||
substr(regist_no,1,2))+1 as "현재나이"
from (select regist_no from dual);
방법2)
select name, regist_no,
to_char(sysdate,'yyyy') -
to_number(19 || substr(regist_no,1,2)) as "나이", from dual;
주민번호 앞 2자리가 숫자가 크면 더 어림
sysdate에서 주민번호 앞자리 추출한 date를 뺄셈해서 그 차이가 살아온 날의 수
extract : 원하는 데이터 형식을 추출
extract(year from sysdate) : 현재년도를 구함
---날짜함수 (sysdate)
select sysdate, exract(year from sysdate) as "현재년도" from daul;
select sysdate, exract(month from sysdate) as "현재월" from daul;
select sysdate, exract(day from sysdate) as "현재일" from daul;
년수 구하는 방식
select trunc((months_between(sysdate,hiredate))/12 from emp;
월수 구하는 방식
select trunc(months_between(sysdate,hiredate)) from emp;
(months_between함수를 쓰면 두날짜간의 월수를 반환)
select last_day(sysdate)from dual;
오늘의 마지막 달의 일자가 출렴됨. 1월이면 31 2월이면 28이나 29가 출력됨
- 사원이 회사에서 일한 총 일수를 구하려면 입사날에서 현재시간을 빼기
select trunc (sysdate-hiredate) from emp;
---시간함수(systimestamp) 우리나라 시간을 구하려면 +9
select systimestamp, exract(hour from systimestamp) as "현재시간" from daul;
select systimestamp, exract(minute from systimestamp) as "현재분" from daul;
select systimestamp, exract(second from systimestamp) as "현재초" from daul;
날짜 + 숫자(일수) -> 날짜
날짜 - 숫자(일수)-> 날짜
group by (집계함수)
특정 컬럼의 값을 기준으로 값이 같은 결과물들을 하나의 그룹으로 처리하는방법
select 그룹의기준컬럼, 그룹함수를 사용한 컬럼 from 테이블명 where 조건 group by 컬럼 order by 컬럼 ;
group by 다음에 오는 컬럼들을 기준으로 그룹지어서 from 다음에 기술한 테이블에서 where절 조건에 맞는 데이터중 order by 컬럼을 기준으로 정렬하여 select 다음에 오는 컬럼을 출력함
이 경우 select문 다음에 오는 컬럼은 그룹의 기준이거나 그룹함수만 가능
집계함수는 where절을 사용할수 없음
ex) 회원별 게시글 수를 조회하라.단 게시글 수가 2 이하인 레코드만 출력
(x) select 컬럼1, count(id)from 테이블
where count(id)<=2
group by 컬럼1;
(o) select 컬럼1, count(id) from 테이블
group by 컬럼1
having count(id)<=2;
having (집계함수)
group by로 그룹진 컬럼의 값을 비교하는 연산
select 그룹 결과를 가지는 컬럼 from 테이블명 where 조건 group by 컬럼 order by 컬럼;
group by 다음에 오는 컬럼들을 기준으로 from 다음에 기술한 테이블에서 order by 컬럼을 기준으로 정렬하여 select 다음에 오는 컬럼을 출력
where절은 집계이후 기술
order by 정렬 (asc, desc) / 어센딕, 디센딕 / 오름차순, 내림차순
select from 테이블명 order by 컬럼 desc;
컬럼 뒤에 desc 을 기술 하지 않으면 기본적으로 asc 이 적용된다
1차 정렬후에 같은숫자가 나오면 2차정렬도 가능
select from 테이블명 order by 컬럼 desc,컬럼2 desc;
을 기술하면 1차정렬 후에 2차정렬까지 돼서 출력됨
To_char(숫자,문자) 숫자->문자, 날짜->무자
T0_Number(문자) 문자->숫자
To_Date(문자or숫자) 문자or숫자 -> 날짜
Regular expresion function
오라클에서 제공하는 함수가 아니라서 regexp_like로 써야한다
패턴숫자 ^(시작) 사용
출력하는 숫자 앞뒤로 숫자나 문자가 없을경우 ^와');
트랜잭션 : 하나의 단위로 수행되는 쿼리의 묶음
일괄 공개
update notice set pub = 1 where id in (2,4,6,7,9);
일괄 비공개
update notice set pub = 0 where id in (1,3,5,8,10);
