Structured Query Language(구조적 질의 언어)
->관계형 데이터베이스 시스템에서 데이터를 관리 및 처리하기 위한 언어입니다.
DBMS
데이터베이스를 관리해주는 시스템
필요성?
DataBase : 데이터를 모아서 사용하자 / 중복된 것을 솎아내고 데이터를 단일하게 관리하게 하는 것 (중복을 제거해서 결함을 없앤다)
※여기서 sql로 db의 중복된 내용을 단일하게 하는데 중요한 것은 DML이다
[문자열함수]
문자열 추출함수 substr(문자열,시작위치, 길이)
select substr('hello',1,3) from dual;
select substr('helloworld',1,4) as string from dual;
-- 1번부터 4번까지
select substr('hello',3) as string from dual;
-- 3번째부터 끝까지 substring이랑 유사함
select SUBSTRB('ABCDEFG',3) from dual;
-- 바이트 단위로 자른다
Q. 문자열 추출함수 사용 : 모든 학생의 이름과 출생 월만을 조회하세요
//birth에서 3번째부터 2글자만 가져온다
select name, substr(birth,3,2) from members;
Q. 회원 중에서 전화번호가 011로 시작하는 회원의 모든 정보를 출력하세요
select from member where substr(phone,1,3) = '011';
select from member where phone LIKE '011%'; -> 데이터가 많으면 like가 낫다
Q. 회원 중에서 생년월이 7,8,9월인 회원의 모든 정보를 출력하세요
select * from mem where substr(pwd,1,1) between 1 and 2;
// 정수인 경우 between을 사용
select * from mem where substr(birth,6,2) in ('07','08','09');
// 문자인 경우 in을 사용
Q. 전화번호를 등록하지 않은 회원 중에서 생년 월이 7,8,9월인 회원의 모든 정보를 출력
// and 연산자로 묶어서 처리함
select * from mem where phone is null and substr(birth,6,2) in ('07','08','09');
<문자열 덧셈 함수>
select concat('홍','길동') from dual;
select '3' || 10 num from dual; -- 문자열 더하기는 || 으로 처리한다
결과 310
<문자열 트림 함수>
select ltrim (' -h- ') from dual; --> 왼쪽 공백
select rtrim (' -h- ') from dual; --> 오른쪽 공백
select trim (' -h- ') from dual; --> 양쪽 다
upper and lower
select lower('ERWEREW') from dual;
select upper('dfadadf') from dual;
Q. 회원 아이디가 newlec인 회원을 조회
select * from member where upper(id) = 'newlec';
<문자열 대치 함수>
// we를 you로 변환
select replace('where we are','we','you') from dual;
select translate('where we are','we','you') from dual;
yhoro yo aro
글자 순서를 매치해서 변경한다
Q. 회원의 이름과 주소를 조회(주소는 빈칸 없이 출력)
select name, replace(address, ' ', '') from member;
<문자열 패딩 함수>
select rpad('hello',10,0) from dual;
-- 문자열이 길이 5인데 5이상 빈칸은 0으로 채워라 문자열 오른쪽에
select lpad('hello',10,0) from dual;
-- 문자열의 길이가 5인데 5이상 빈칸은 0으로 채워라 왼쪽부터
Q. 회원의 이름을 조회하세요(단, 이름의 길이가 3자가 안되는 경우는 이름 오른쪽을 밑줄로 채우기)
select rpad(name,6,'0')from mem; -> 왜 6이냐? 한글은 1글자당 2바이트라서
<첫글자를 대문자로 바꾸는 함수>
select initcap('the most important thing is..') from dual;
-- 단어마다 첫글자가 대문자로 바뀌어진다
문자열 검색 함수 instr(문자열, 검색문자열, 위치, 찾을 수)
select instr('ALL we need to is just to..' 'to) from dual;
select instr('All we need to is just to ..','to',1,2) from dual;
-- 문자열의 첫번째부터 2번째 to가 오는 위치를 알려줘
Q. 회원의 전화번호에서 두번째 대시(-)문자가 존재하는 위치?
select instr(phone, '-',1,2)from mem;
Q. 회원의 전화번호에서 첫번째 대시문자와 두번쨰 대시 문자간의 간격은?
010-1234-1234
select instr(phone,'-',4)from mem;
select instr(phone,'-',1,2) - instr(phone,'-',1,1) -1 from mem;
Q. 회원의 전화번호에서 첫번쨰와 두번째 사이의 국번을 출력
010-1234-1234
select substr(phone,5, instr(phone,'-',1,2) - instr(phone,'-',1,1)-1) from mem;
-- 5부터 시작해서 4글자 가지고 오는 것
-- instr로 빼서 가지고 온다
<문자열 길이 얻는 함수>
select length('where we are')from dual;
모든 회원의 핸드폰 번호와 번호의 문자열 길이 조회
select phone,length(phone) from mem;
만약 회원의 전화번호 컬럼에 포함된 문자 '-'를 없앤 전화번호의 길이를 출력
select length(replace(phone,'-',''))from mem;
select ascll('a') from dual;
select chr(65) from dual; -- 아스키코드로 사용가능
[숫자함수]
절대값을 구하는 함수 abs(n)
select abs(35), abs(-35) from dual;
음수/양수를 알려주는 함수 sign(n)
select sign(35),sign(-90),sign(0) from dual;
-> 1 -1 0 이라는 결과가 나온다
숫자의 반올림 값을 알려주는 함수 Round(n,i)
select round(34.1234), round(31,678) from dual;
-> 34 32
select round(34.1234123,2), round(31.671318,1) from dual;
-> 34.12 31.7
-> 소수점 i번째 자리까지 반올림 하고 오른쪽 숫자를 확인하고 반올림한다
숫자의 나머지 값을 반환하는 함수 mod(n1,n2)
select trunc(16/5) 몫, mod(17,5) 나머지 from dual;
-> 몫 3 나머지 2
숫자의 제곱을 구하는 함수와 제곱근을 구하는 함수 power(n1,n2) / sqrt(n)
select power(5,3), sqrt(16) from dual;
-> 5^3 = 125, 16의 제곱근 : 4
[날짜함수]
select sysdate, current_date(세션에 설정된 시간을 가져온다 - current, 접속자의 시간), SYSTIMESTAMP(디테일한 시간), current_timestamp from dual;
세션 시간과 포맷 변경
alter session set time_zone='-1:0' -> 나라마다 시차가 다르기 때문에 사용함 -> 그럼 current 시간이 변경된다(session을 변경한것이기 때문이다)
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
-> 20201-09-25 16:36:23
날짜 추출함수 extract(year,month,day,hour,minute,second..)
select exctract (year from sysdate) from dual;
select exctract (month from sysdate) from dual;
select exctract (day from sysdate) from dual;
select exctract (hour from sysdate) from dual;
select exctract (minute from sysdate) from dual;
select exctract (second from sysdate) from dual;
회원 중 가입 월이 1,2,9,12월인 회원만 조회하기
select * from mem where EXTRACT(month from pegdate) in (1,2,9,12);
날짜를 누적하는 함수 add_month(날짜, 정수)
select add_months(sysdate,-1) from dual;
// 등록한지 6개월이 안된 회원 조회하기
// 데이터의 형태가 date로 되어있어야지 조회가능하다
select * from mem where add_months(sysdate,-6) < pegdate;
날짜의 차이를 알아내는 함수 months_between(날짜,날짜)
select MONTHS_BETWEEN(SYSDATE,TO_DATE('2021-01-22'))from dual;
// 등록한지 6개월이 안된 회원조회를 이렇게 할 수 있다
select * from mem where months_between(sysdate,pegdate) < 6
다음 요일을 알려주는 함수 next_day(현재날짜, 다음요일)
select NEXT_DAY(sysdate, '월요일')from dual;
-> 오늘이 토요일이면 다음 월요일은 언제인가를 확인하는 함수
월의 마지막 일자를 알려주는 함수 last_day(날짜)
select LAST_DAY(SYSDATE)from dual;
// 다음달 마지막 일자 확인하기
select last_day(add_months(sysdate,1)) from dual;
지정된 범위에서 날짜를 반올림하는 round(날짜,포멧) 날짜를 자르는 trunc(날짜,포멧)
select round(sysdate,'cc'), TRUNC(SYSDATE,'cc')from dual; -- cc 백년단위로 반올림
select round(sysdate,'year'), TRUNC(SYSDATE,'year')from dual; -- year 년단위
select round(sysdate,'q'), TRUNC(SYSDATE,'q')from dual; -- q 분기단위
select round(sysdate,'month'), TRUNC(SYSDATE,'month')from dual; -- month 단위
select round(sysdate,'w'), TRUNC(SYSDATE,'w')from dual; -- w 주단위
select round(sysdate,'day'), TRUNC(SYSDATE,'day')from dual; -- 일단위
select round(sysdate,'d'), TRUNC(SYSDATE,'d')from dual; --일단위
select round(sysdate,'hh'), TRUNC(SYSDATE,'hh')from dual; -- 시간단위
select round(sysdate,'mi'), TRUNC(SYSDATE,'mi')from dual; -- 분단위
// 현재 날짜와 to_date로 문자열을 날짜로 변환해서 반올림하고 날짜 자르기
select sysdate, round(to_date('2051-09-11'),'cc'), trunc(to_date('2051-09-11'), 'cc') from dual;
[형변환함수]
==format==
숫자 - to_char() -> 문자열 - to_date() -> 날짜
숫자 <- to_number() - 문자열 <- to_char() -날짜
number형식을 문자열(varchar2)로 변환 to_char(number)
select to_char(123456789,'$99,999,999,999.99') from dual;
-- $99,999,999,999.99가 숫자 길이보다 길어야한다| 포맷문자 | 설명 |
|---|---|
| 9 | 자릿수 표현 |
| 0 | 빈자리를 채우는 문자 |
| $ | 앞에$표시 |
| , | 천단위 구분자 |
| . | 소수점 표시 |
select to_char(1234) || 'geee' from dual;
select to_char(232324, '00999999')||'afdf' from dual;
select trim(to_char(1234567,'9,999,999,999')) ||'원' from dual;date 형식을 문자열(varchar2)로 변환 to_char(datetime)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
| 날짜 포맷문자 | 설명 |
|---|---|
| yyyy/rrrr/yy/year년도표시 | 4자리/y2k/2자리/영문 |
| mm/mon/month월 표시 | 2자리/영문3자리/영문전체 |
| dd/day/ddth일 표시 | 2자리/영문/2자리 |
| am/pm | 오전/오후 |
| mi분표시 | 0-59분 |
| ss초표시 | 0-59초 |
문자열을 날짜 형식으로 변환하는 함수 to_date(문자열, 날짜포멧)
select to_date('1994-10-11','yyyy-mm-dd')from dual;
문자열을 숫자형식으로 변환하는 함수 to_timestamp(문자열)
select to_timestamp('1992-12-12 12:12:12', 'yyyy-mm-dd hh24:mi:ss') from dual;
문자열을 숫자형식으로 변환하는 함수 to_number(문자열)
select to_number('1993') from dual;
[null관련 함수]
반환 값이 null일 경우에 대체 값을 제공하는 nvl(null, 대체 값)함수
select nvl(age,0) from mem;
nvl에서 조건을 하나 더 확장한 nvl2(입력값, notnull 대체값, null대체값) 함수
// not null이면 100/age를 넣고 null이면 0을 넣고
select nvl2(age, trunc(age/10)*10, 0) from mem;
두 값이 같은 경우 null 그렇지 않은 경우 첫번째 값 반환 nullif(값1, 값2) 함수
select nullif(age,19) from mem;
※조건에 따른 값 선택하기 decode(기준값, 비교값, 출력값, 비교값, 출력값)
// gender가 남성이면 1을 출력하고 아니면 2를 출력해라
select decode(gender,'남성',1,2) from mem;
[집계 함수]
sum, min, max, count, avg
// mem 테이블에서 name이 몇개인가?
select count(name) from mem;
-> null이 들어가 있는지 없는지 파악해야 한다. 즉 null값이 아닌 컬럼을 count를 해야한다
select avg(age) from mem;
select sum(score) from mem; 이런 방식으로 가능
select id, count(name) count from mem GROUP BY id;
-- name을 카운트 할때 id당 name의 개수를 카운트하고 싶다
-- 집계되었을 때 한 단위로 표현된다
-- 그룹명과 카운트되는 대상은 포함해도 되지만 나머지 요소가 들어오면 오류가 발생한다
select id, count(name) count from mem GROUP BY id order by count desc;
-- 집계되었을 때 한 단위로 표현된다
-- 그룹명과 카운트 되는 대상은 포함해도 되지만 나머지 요소가 들어오면 오류가 발생한다
-- 제일 먼저 실행되는 것은 from절이다
-- from -> connect by -> where -> group by -> having -> select -> order by 순서대로 작동된다
-- 그래서 select에서 별칭을 지정하면 having절에서 사용할 수 없다
-- 왜냐하면 구동되는 순서가 having이 먼저이기 때문에 having에선 select에서 정한 별칭이 없기 때문이다
회원별 게시글 수를 조회하시오
select id, count(title) count from member group by id having count(title) <= 2 order by count desc;
select, from, group by, having, order by 순서대로 작성해야 하지만
구동은 이런 순서로 된다 from, group by, having, order by, select
그래서 집계함수는 group by에서만 사용 가능하다
그래서 집계함수를 사용할 수 있는 것은 group by 이후에만 사용가능(구동되는 순서에서) 그래서 order by, having, select에서 사용가능
rownum은 where에서만 사용가능
