728x90
반응형
-- 오늘날짜 조회
select sysdate from dual;
select '안녕' from dual;
/*
컬럼이나 where 조건문에 산술연산이 가능.
연산자
연결연산자 : ||
산술연산자 : + - * /
비교연산자 : =, >, <, >=, <=, !=
SQL연산자 :
컬럼명 between A and B
컬럼명 in (값1,값2,...)
컬럼명 not in (값1,값2,...)
컬럼명 like, not like
% : 임의의 문자 0개이상
_ : 임의의 문자 1개
order by 구문 : 정렬. select 구문의 마지막에 기술
acs : 오름차순 정렬. 생략 가능. 기본값 (default값).
desc : 내림차순 정렬. 생각 불가.
집합연산자 : 두개의 select 구문 결과를 합해서 출력
union : 정렬. 결과의 중복허용 안함. 합집합.
union all : 두개의 결과를 물리적으로 연결
desc 테이블명 : table 구조 조회
selct 구문 형식
select 컬럼명1,컬럼명2,... || *
from 테이블명
[where 조건문] : 생략 될 경우는 모든 레코드 조회함
[group by 컬럼] : 컬럼으로 그룹화
[having 그룹함수] : 그룹함수의 조건문
[order by 컬럼|순서|별명] : 정렬(sort)
리터널로 컬럼 생성. ' '
컬럼의 alias(별명) :
컬럼명 별명
컬럼명 "별 명"
컬럼명 as "별명"
distinct : 중복제거. 첫번째 컬럼에 한번만 사용 가능
where : 레코드 선택의 조건문
/*
단일행 함수 : 한개의 행을 기준으로 한개의 컬럼의 값을 변형
--문자함수 : 문자열 관련 함수
*/
-- 대소문자 변환함수
-- initcap : 첫번째 문자만 대문자. 나머지는 소문자
-- upper : 대문자로 변경
-- lower : 소문자로 변경
-- 학과번호가 101학과 학생의 이름,id 출력하기
-- 단, id는 첫문자만 대문자, 대문자, 소문자로 출력하기
select name,id, initcap(id), upper(id), lower(id) from student where deptno1 = 101;
-- emp테이블에서 사원의 이름이 smith인 사원의 이름,급여,부서코드 출력하기
-- smith이름은 대소문자에 상관없이 출력되어야 한다.
select ename,sal,deptno from emp where ename = 'SMITH';
select ename,sal,deptno from emp where ename = upper('Smith');
select ename,sal,deptno from emp where lower(ename) = 'smith';
/*
문자열의 길이
*/
-- length : 문자열의 길이
-- lengthb : 문자열에 저장에 필요한 바이트수
-- NAME NOT NULL VARCHAR2(10)
-- 홍길동 : 문자열의 길이 : 3
-- 저장바이트 수 : 9
-- 한글을 한자 저장하기위한 필요 바이트 수는 3바이트.
select name,id,length(id),lengthb(id),length(name),lengthb(name) from student;
desc student;
/*
부분문자열
substr(컬럼명|문자열, 1부터시작하는 시작인덱스, 글자수)
*/
-- 학생 중 생일이 3월인 학생의 이름,생년월일 출력하기
-- 단, 생일은 주민등록번호 기준임.
select name,jumin,substr(jumin,1,6) from student where substr(jumin,3,2) = '03';
-- 학생의 이름,생일을 생일의 월을 기준으로 정렬하여 출력하기.
-- 단, 생일은 주민등록번호 기준임.
select name,substr(jumin,1,6) from student order by substr(jumin,3,2);
-- 학생의 이름이 3번째 글자가 '진'문자인 학생의 학번, 이름, 학과코드를 조회하기
select studno,name,deptno1 from student where name like'__진%';
select studno,name,deptno1 from student where substr(name,3,1) = '진';
/*
instr : 문자열중 문자의 위치값
instr(컬럼|문자열,'문자',[시작인덱스],[몇번째])
*/
-- instr('A*B*C','*',1,2) : 'A*B*C' 문자열 중 1번인덱스부터 2번째 위치한 *문자의 위치리턴.
select instr('A*B*C','*',1,2) from dual;
select instr('A*B*C','*',1) from dual;
select instr('A*B*C','*',2) from dual;
select instr('A*B*C','*',3) from dual;
select instr('A*B*C','*') from dual;
-- 학생 테이블에서 이름, 전화번호(tel), tel 컬럼의 ')'의 위치를 출력하기
select name,tel,instr(tel,')') from student;
-- 문제 1 : 학생의 이름, 전화번호, 지역번호(02,031,...)를 출력하기
select name,tel,substr(tel,1,instr(tel,')')-1) from student;
/*
문자 추가함수
lpad : 왼쪽에 문자 추가
rpad : 오른쪽에 문자추가
lpad|rpad(컬럼|문자열,전체자리수,채울문자)
*/
-- lpad(id,10,'$') : 전체 10자리로 id를 출력. 남은 자리수는 $로 채움
select name, lpad(id,10,'$'),grade from student;
/*
문자 제거하기
trim : 양쪽 공백 제거
ltrim : 왼쪽 공백 제거 ltrim(컬럼|문자열,'제거할문자')
rttim : 오른쪽 공백 제거 rtrim(컬럼|문자열,'제거할문자')
*/
select trim(' AAAAA ')a from dual;
select ltrim(' AAAAA ')b from dual;
select rtrim(' AAAAA ')c from dual;
-- 교수테이블에서 hpage컬럼의 http://문자를 제거하여 url만 출력하기
select name,hpage,ltrim(hpage,'http://') url from professor where hpage is not null;
select name,hpage,substr(hpage,8) url from professor where hpage is not null;
/*
숫자관련 함수
round 함수 : 반올림함수. round(컬럼|숫자,출력할 소수점이하 자리수).
trunc 함수 : 버림함수. trunc(컬럼|숫자,출력할 소수점이하 자리수).
*/
select round(12.5123) r1,round(12.5123,0) r2,round(12.5123,1) r3,round(12.5123,2) r4,round(12.5123,-1) r5 from dual;
select trunc(12.5123) t1,trunc(12.5123,0) t2,trunc(12.5123,1) t3,trunc(12.5123,2) t4,trunc(12.5123,-1) t5 from dual;
/*
mode 함수 : 나머지 함수
*/
select mod(12,10) from dual;
/*
근사정수 : 가까운 정수
ceil 함수 : 큰 근사정수
floor 함수 : 작은 근사정수
*/
select ceil(12.345),floor(12.345),ceil(-12.345),floor(-12.345) from dual;
--power 함수 : 제곱함수
select power(3,3) from dual; -- 3 * 3 * 3
-- 문제 2 : 교수의 급여를 15% 인상하려고 함. 교수이름, 현재급여, 반올림 예상급여, 버림예상급여를 출력하기
-- 단, 예상급여는 정수로 표시함
select name,pay,round(pay*1.15) 예상급여1,trunc(pay*1.15) 예상급여2 from professor;
-- abs(숫자) : 절대값
-- sign(숫자) : 부호. 양수=1, 음수=-1, 0=0
select sign(1),sign(-1),sign(0) from dual;
select round(11.1),ceil(11.1),floor(11.1) from dual;
/*
날짜 함수
months_between : 두 날짜 사이의 개월 수 리턴
months_between(날짜1,날짜2) : 날짜1 - 날짜2의 개월수 차이
*/
select sysdate from dual; --오늘 날짜
select months_between(sysdate,'22/01/23') from dual;
select months_between('22/01/23',sysdate) from dual;
-- 문제 3 : 학생의 이름, 생일(birthday), 개월, 나이를 출력하기
-- 개월 : 현재 날짜에서 생일까지의 개월수를 반올림하여 정수로 출력
-- 나이 : 개월/12로 나누어서 버림으로 정수 출력
select name,birthday,round(months_between(sysdate,birthday)) 개월수,trunc(months_between(sysdate,birthday)/12) 나이 from student;
-- last_day : 해당월의 마지막 날짜 리턴
select last_day(sysdate) from dual;
select last_day('22/03/02') from dual;
select last_day('20/02/02') from dual;
-- 형변환 함수
-- 묵시적형변환 : 오라클이 자동으로 형변환
select 1 + '1' from dual; --실행됨. 성능이 안좋음.
select 1 + 'A' from dual; --'A'문자는 숫자로 묵시적형변환 불가. 오류 발생
desc student
select * from student where studno=9411;
select * from student where studno='9411';
-- to_char : 숫자나 날짜형을 형식에 맞도록 문자열로 변환하는 함수
select sysdate, to_char(sysdate,'yyyy-mm-dd hh:mi:ss am') from dual;
select to_char(sysdate, 'yyyy') 년도1,
to_char(sysdate, 'rrrr') 년도2,
to_char(sysdate, 'yy') 년도3,
to_char(sysdate, 'year') 년도4,
to_char(sysdate, 'rr') 년도5,
to_char(sysdate, 'mm') 월1,
to_char(sysdate, 'mon') 월2, --약자표시
to_char(sysdate, 'month') 월3,
to_char(sysdate, 'dd') 일1, -- 일자
to_char(sysdate, 'day') 일2, --요일
to_char(sysdate, 'ddth') 일3, --
to_char(sysdate, 'hh') 시간1, -- 1 ~ 12
to_char(sysdate, 'hh24') 시간2, -- 0 ~ 23
to_char(sysdate, 'mi') 분1, -- 분
to_char(sysdate, 'ss') 초1, -- 초
to_char(sysdate, 'am') AM -- 오전,오후
from dual;
-- 내일 2022/02/24 요일 출력하기
select to_char(to_date('22/02/24','yy/mm/dd'), 'day') from dual;
-- emp 테이블에서 사원의 이름, 직업, 입사일, 입사요일을 출력하기
select ename,job,hiredate,to_char(hiredate,'day') 입사요일 from emp;
-- 숫자형식
-- 숫자1 : 정수형 5자리
-- 숫자2 : 정수형 6자리. 빈자리는 0으로 채움.
-- 숫자3 : 정수형 6자리. 앞에 $표시
-- 숫자4 : 자리수 7자리. 소수점이하 2자리 표시
-- 숫자5 : 자리수 5자리. 소수점이하 1자리, 세자리마다 ',' 출력
select to_char(1234.56,'99999') 숫자1,to_char(1234.56,'099999') 숫자2,
to_char(1234.56,'$99999') 숫자3,to_char(1234.56,'99999.99') 숫자4,
to_char(1234.56,'9,999.9') 숫자5,to_char(1234.56,'$99,999') 숫자6
from dual;
-- 10만 숫자를 세자리마다 ','로 표시하기
select to_char(100000,'999,999') from dual;
-- to_date() : 형식화된 문자열을 날짜형으로 변환 함수
select '2022-02-23',to_date('2022-02-23','yyyy/mm/dd'),to_char(to_date('2022-02-23','yyyy/mm/dd'),'yyyy/mm/dd day') from dual;
-- to_char() : 날짜, 숫자를 문자열로 변형
-- to_date() : 형식화된 문자열을 날짜형으로 변환
-- to_number() : 문자열을 숫자로 변환
/*
기타함수
*/
-- 교수의 이름, 연봉 출력하기
-- 연봉 : 급여*12 + 보너스
-- 보너스가 없는 경우는 보너스는 0으로 처리함
select name,pay*12+bonus from professor where bonus is not null
union
select name,pay*12 from professor where bonus is null;
-- nvl() : nvl(컬럼값,기본값)
-- 컬럼의 값이 null인경우 기본값으로 대체.
select name,pay*12 + nvl(bonus,0) 연봉 from professor;
-- 문제 4 : 학생의 이름과 지도교수번호를 출력하기.
-- 단, 지도교수가 없는 학생의 지도교수번호는 9999로 출력하기
select name,nvl(profno,9999) from student;
/*
그룹 함수 : 레코드를 그룹화한 결과 리턴
count(컬럼명) : 컬럼의 값이 null이 아닌 레코드의 건수 리턴
count(*) : 모든 컬럼을 기준으로 건수 리턴
group by 컬럼명 : 컬럼명을 기준으로 레코드를 그룹화 함.
group by 구문이 없는 경우는 모든 레코드를 한개의 그룹으로 그룹화 함.
그룹함수와 컬럼을 함께 조회하기 위해서는 반드시 해당 컬럼을
group by에서 설정해야 함.
*/
-- 전체 학생 수 조회하기
select count(*) from student;
-- 1학년 학생 수 조회하기
select count(*) from student where grade = 1;
-- 각 학년의 학생 수 조회하기
select grade, count(*) from student group by grade;
-- 각 강의실의 학생수 조회하기
select deptno1, count(*) from student group by deptno1;
-- 학년별 학생의 인원수, 지도교수를 배정받은 인원수 조회하기
select grade, count(*), count(profno) from student group by grade order by grade;
-- 부서별 교수의 인원수를 조회하기
select deptno, count(*) from professor group by deptno order by deptno;728x90
반응형