1
2
3
4
5
6
7
8
9
10
--문자 함수 퀴즈 1)
--Student 테이블을 참조해서 아래 화면과 같이 1 전공이(deptno1 컬럼) 
--101번인 학생의 이름과 전화번호와 지역번호를 출력하세요. 
--단 지역번호는 숫자만 나와야 합니다.
select * from student;
select name, tel, substr(tel, 1, instr(tel, ')' ) -1 ) as "지역번호"
    from student 
    where deptno1 = 101;
--substr(tel, 시작위치, 뽑을갯수);
--substr(tel, 시작위치);
 cs

결과

'Database > Basic' 카테고리의 다른 글

concat  (0) 2021.02.09
문자열함수-upper,lower,initcap,length와lengthb,substr과substrb,instr,lpad,Rpad,Itrim,rtrim  (0) 2021.02.08
숫자함수-round(), ceil(), floor(), mod(), trunc()  (0) 2021.02.08
집합(그룹) 함수-sum,avg,max,min,count,rank + GROUP BY , HAVING  (0) 2021.02.08
between, order by(오름차순과 내림차순)  (0) 2021.02.08
1
2
3
4
--Professor 테이블에서 교수들의 이름과 직급을 출력
select concat(name, position)
    from professor
    where deptno=101;
cs

'Database > Basic' 카테고리의 다른 글

문자함수-substr, instr  (0) 2021.02.10
문자열함수-upper,lower,initcap,length와lengthb,substr과substrb,instr,lpad,Rpad,Itrim,rtrim  (0) 2021.02.08
숫자함수-round(), ceil(), floor(), mod(), trunc()  (0) 2021.02.08
집합(그룹) 함수-sum,avg,max,min,count,rank + GROUP BY , HAVING  (0) 2021.02.08
between, order by(오름차순과 내림차순)  (0) 2021.02.08

1.upper 모두 대문자

2.lower 모두 소문자

1
2
3
4
5
6
7
--<문자열 함수>
--1.upper(문자열) => 모두 대문자
--2.lower(문자열) => 모두 소문자
---Student 테이블에서 1 전공이 201 번인 학생들의 ID 를 이름과 함께 소문자 , 대문자로 출력
select name 이름, id, lower(id) 소문자,  upper(id) 대문자 --lower, upper
    from student
    where deptno1 = 201;
 cs

3.initcap 단어의 첫글자를 대문자

1
2
3
4
5
--3.initcap(문자열)=> 단어의 첫 글자 대문자로 표현
---Student 테이블을 사용하여 1전공이 201 번인 학생들의 ID 를 첫 글자만 대문자로 출력
select initcap(id) --첫 글자만 대문자로 출력, 나머지는 소문자로 출력 
    from student
    where deptno1 = 201;
 cs

4.length 와 lengthb 

  • length: 문자열의 길이 
  • lengthb: 한글 한글자는 3byte
1
2
3
4
5
6
7
8
9
--4.length(문자열) => 문자열의 길이
--영어는 length와 lengthb는 길이 동일 
select length('CANDDIE') from dual;
select lengthb('CANDDIDE') from dual; 
 
--한글 length와 lengthb 차이 O 
select dname, length(dname) from dept2; --(length)한글 한글자 1
select dname, lengthb(dname) from dept2; --(lengthb=byte로설정)한글 한글자 3byte
select dname, length(dname), lengthb(dname) from dept2;
 cs
1
2
3
4
--Student 테이블에서 1 전공이 201 번인 학생들의 이름과 이름의 글자수, 이름의 바이트 수를 출력
select name 이름, length(name), lengthb(name) --length, lengthb
    from student
    where deptno1 = 201;
 cs
결과

5.substr 와 substrb

select substr(name, 1, 2)  --인덱스1부터 2자리를 추출
    from student 
    where deptno1=101;
    
select substrb(name, 1, 2) --인덱스1부터 2바이트를 추출
    from student
    where deptno1=101;
 cs
 

select dname, substr(dname, 2, 3) from dept2;

1
2
3
4
5
6
7
select substr(name, 1, 2)  --인덱스1부터 2자리를 추출
    from student 
    where deptno1=101;
    
select substrb(name, 1, 2) --인덱스1부터 2바이트를 추출
    from student
    where deptno1=101;
 cs
  • substr: 문자열에서 특정길이 문자를 추출
  • substrb: 문자열에서 특정byte 만큼의 문자를 추출

cf)복합함수 - 함수안에 함수 

1
2
3
--복합함수 
select rpad(dname, 10, substr('1234567890', length(dname)+1)) "복합함수연습"
    from dept2;
 cs

6.instr

1
2
3
--6.instr(문자열, 찾을문자열) => 찾을 문자열의 출현 위치 알려줌
select instr('abcd', 'b') from dual;  --인덱스번호로 출력됨
 
 cs

결과) 2

1
2
3
4
--6.instr(문자열, 찾을문자열, 시작번지수, 몇번지 위치)
--  ex)instr(job,'A',-1) => -1은 뒤에서 부터 검색.
select instr('abcdefghiabd', 'b', -1) from dual; -- -1은 뒤에서부터 찾음
select instr('abcdefghiabd', 'b', -3) from dual; -- -1은 뒤에서부터 찾음 
 cs

결과) 11

        2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select instr('A*B*C*', '*', 1, 2) --인덱스1부터 찾았을 때, 2번째 * 찾기
    from dual; --=>4
    
select instr('A*B*C*', '*', 3, 2) --인덱스3부터 찾았을 때, 2번째 * 찾기
    from dual; --=>6
    
select instr('A*B*C*', '*', -4, 1) --인덱스-4부터 찾았을 때, 1번째 * 찾기
    from dual; --=>2
    
select instr('A*B*C*', '*', -4, 2) --인덱스-4부터 찾았을 때, 2번째 * 찾기
    from dual; --=>0
    
select instr('A*B*C*', '*', -2, 2) --인덱스 -2부터 찾았을 때, 2번째 * 찾기 
    from dual; --=>2
 cs

7.lpad

1
2
3
--7.lpad(문자열, 전체자리수, 특정문자) 
--=>오른쪽 정렬 후 왼쪽 빈 공백에 특정문자로 채움
select lpad('abcd', 20, '#') from dual;
 cs

결과) ################abcd

8.Rpad

1
2
3
4
5
6
--8.- Rpad(문자열, 전체자리수, 특정문자)
--=>왼쪽 정렬 후 오른쪽 빈 공백에 특정문자로 채움)
select rpad('abcd', 20, '@') from dual;
 
select * from dept2;
select rpad(dname, 10, '*') "RPAD 연습" from dept2;
 cs

결과) abcd@@@@@@@@@@@@@@@@

9.ltrim

1
--9.ltrim() => 왼쪽 공백제거
 cs

10.rtrim

1
2
3
4
5
6
7
--10.rtrim()=> 오른쪽 공백제거
select * from student 
    where depotno1 = 103;
    
select name, rtrim(Regexp_replace(name, '(.)', '\-1'), '-') "변경후"
    from student
    where deptno1 = 103;
 cs
결과

cf) REGEXP_REPLACE

1
2
3
4
5
6
7
8
9
10
11
12
--비교해보기
select name,position, rtrim(Regexp_replace(position, '(.)', '\-1'), '-') "변경후"
    from professor
    where deptno = 103;
    
select name,position, rtrim(Regexp_replace(position, '()', '\-1'), '-') "변경후"
    from professor
    where deptno = 103;
 
select name,position, rtrim(Regexp_replace(position, ' ', '\-1'), '-') "변경후"
    from professor
    where deptno = 103;
 cs

'Database > Basic' 카테고리의 다른 글

문자함수-substr, instr  (0) 2021.02.10
concat  (0) 2021.02.09
숫자함수-round(), ceil(), floor(), mod(), trunc()  (0) 2021.02.08
집합(그룹) 함수-sum,avg,max,min,count,rank + GROUP BY , HAVING  (0) 2021.02.08
between, order by(오름차순과 내림차순)  (0) 2021.02.08

1.round 반올림

1
2
3
4
--<숫자함수>
--1.round(숫자, 자리수)=> 반올림
select round(12.345678, 0), round(12.345648, 2), round(12.548789, 0) 
    from dual;
 cs

결과) 12   12.35   13

2.ceil 올림 후 정수반환

1
2
3
--2.ceil(숫자) => 올림 한 후 정수반환
select ceil(12.34), ceil(-12.34) 
    from dual;
 cs

결과) 13   -12

3.floor 내림 후 정수반환

1
2
3
--3.floor(숫자)=>내림 한 후 정수 반환
select floor(12.34), floor(-12.34) from dual;
 
 cs

결과) 12   -13

4.mod 나머지

1
2
3
--4.mod(숫자, 나눌수) => 나머지
select mod(5, 2) from dual;
select mod(pay, 10000000) from emp2;
 cs

결과) 1

5.trunc 버림

1
2
3
--5.trunc(숫자, 자리수)=> 버림
select trunc(12.3456, 2), trunc(12.3756, 2), trunc(12.7456, 0) 
    from dual;
 cs

결과) 12.34   12.37   12

'Database > Basic' 카테고리의 다른 글

concat  (0) 2021.02.09
문자열함수-upper,lower,initcap,length와lengthb,substr과substrb,instr,lpad,Rpad,Itrim,rtrim  (0) 2021.02.08
집합(그룹) 함수-sum,avg,max,min,count,rank + GROUP BY , HAVING  (0) 2021.02.08
between, order by(오름차순과 내림차순)  (0) 2021.02.08
조건연산자(like, or, and, in, between, any, all, not, null)  (0) 2021.02.08

1.SUM(컬럼명) -> 합계

1
2
3
--1)sum(컬럼명) => 합계
select * from gift;
select sum(g_start) "시작값 합계", sum(g_end)  "최대값 합계"from gift;
 cs

2.AVG(컬럼명) ->평균

3.MAX(컬럼명) ->최대값

4.MIN(컬럼명) ->최소값

1
2
select max(g_start), min(g_start), avg(g_start) from gift;
 
 cs

5.COUNT(컬럼명) -> null값 제외한 총 레코드 수 

6.COUNT(*) -> null값 포함한 총 레코드 수 

1
2
3
4
5
6
7
--5)count(컬럼명) => 총 레코드수(null값은 제외함)
--6)count(*) => null을 포함한 총 레코드수
select * from professor;
 
select count(*) from professor; --null 포함 O
select count(hpage) from professor; --null 포함 X
select count(gno) from gift;
 cs

7. RANK( ) WHITHIN GROUP(ORDER BY 컬럼명 ASC또는 DESC) -> 등수

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--7)rank(expr) whithin group(order by 컬럼명 asc | desc )
/*
ex) --급여가 3000의 등수 구하기
     SELECT RANK(3000) within GROUP(ORDER BY sal desc)
     FROM EMP;
*/
select * from gift;
select rank(600000) within group(order by g_end desc) from gift;
--==>600000이라는 값이 g_end를 내림차순(desc)로 봤을 때, 몇등인가?
--==>null값이 우선순위로 포함됨
 
select rank(600000) within group(order by g_end desc) 
    from gift
    where g_end is not null;
--==>null제외하고 등수 메기기
 cs

GROUP BY 

  • 그룹(집합)함수와 일반필드는 같이 쓸수 없다
  • 특정컬럼을 기준으로 group by하여 각 그룹별 집계함수를 사용 가능
  • =>일반필드를 그룹화 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--<집계함수 사용시 Group by>
select* from student;
 
--그룹(집합)함수와 일반필드(grade)는 같이 쓸수 없다 
select grade, sum(height), avg(height), max(height), min(height)
    from student;
--=>특정컬럼 기준으로 group by 를 하여 각 그룹별 집계함수를 사용 수 있다.
--=>일반필드를 그룹화 시킨다. 
 
select sum(height), avg(height), max(height), min(height)
    from student; --전체데이터의 부분합과 평균,,
    
select grade, sum(height), avg(height), max(height), min(height)
    from student
    group by grade ; --부분합(소계), 학년별의 부분합과 평균,,
    
select grade, sum(weight), avg(weight), max(weight), min(weight)
    from student
    group by grade ;
 cs

HAVING -> group by의 조건절 

1
2
3
4
5
6
7
8
9
10
11
12
--조건: where, having 절 이용한다
--group by 조건을 사용하는 것은 having절 이용 
select grade, sum(height), avg(height), max(height), min(height)
    from student
    group by grade 
    having avg(height) > 173; --조건추가 
    
select grade, sum(weight), avg(weight), max(weight), min(weight)
    from student
    group by grade 
    having avg(weight) > 58;
    
 cs

'Database > Basic' 카테고리의 다른 글

문자열함수-upper,lower,initcap,length와lengthb,substr과substrb,instr,lpad,Rpad,Itrim,rtrim  (0) 2021.02.08
숫자함수-round(), ceil(), floor(), mod(), trunc()  (0) 2021.02.08
between, order by(오름차순과 내림차순)  (0) 2021.02.08
조건연산자(like, or, and, in, between, any, all, not, null)  (0) 2021.02.08
연산자(mod, nvl)  (0) 2021.02.07

between

1
2
3
4
5
--문제]professor 테이블에서 교수들의 이름을 조회하여 성 부분에 'ㅈ'이 포함된 사람의 명단출력
select name 
    from professor 
    where name between '자%' and '차%'
    order by 1; --정렬 -> 1은 필드 번호(인덱스는 1부터시작)를 의미 
 cs

order by 

1
2
3
select * from gift order by gno desc, gname desc; --필드명 이용
select * from gift order by 4 desc, 1 desc; --필드의 인덱스 번호 이용
 
 cs
  • asc = 오름차순 ==> 1,2,3,4,,, ABC,,,
  • desc = 내림차순 ==> 9,8,7,6,,,

'Database > Basic' 카테고리의 다른 글

숫자함수-round(), ceil(), floor(), mod(), trunc()  (0) 2021.02.08
집합(그룹) 함수-sum,avg,max,min,count,rank + GROUP BY , HAVING  (0) 2021.02.08
조건연산자(like, or, and, in, between, any, all, not, null)  (0) 2021.02.08
연산자(mod, nvl)  (0) 2021.02.07
null과 ' '  (0) 2021.02.07

1.like -> %와 _ 함께 쓴다 

1
2
3
4
5
6
7
8
--1)컬럼명 like ‘와일드카드문자열’
select name, id from professor;
 
select name, id from professor where id like '%n' ; -- %, _ <==like와 같이 씀
select name, id from professor where id like '%n%' ;
select name, id from professor where id like 'a_%' ;
select name, id from professor where id like 'i_o%' ;
select name, id from professor where name like '__' ;
 cs

2.or 또는

3.and 그리고

4.in -> in(~,~,,,)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--2)Or 또는(양쪽 중 한 개라도 true이면 만족)
--3)And 그리고 (양쪽모두 true일 때 만족)
--4)컬럼명 In ( 값, 값, ..) ex) id in (1,3,5) => id가 1 또는 3또는 5 
select * from emp2 where position='과장' or position='부장';
select  * from emp2 where position in('과장', '부장'); --권장
 
select * from emp2 where position='과장' and position='부장'; --error!!
select * from emp2 where position='과장' and name like '천%';
 
select * from gift;
select * from gift where gno=1 or gno=7 or gno=9;
select * from gift where gno in(1, 7, 9); --권장 
 
select * from gift where gno !=1 or gno=7 or gno=9;
select * from gift where gno not in(1, 7, 9); --권장 
 cs

5.between -> between ~ and ~

1
2
3
4
5
6
--5)컬럼명 Between 최소 and 최대 
--ex) age between 10 and 20=> age의 값이 10~20 사이
select * from gift where gno between 6 and 9;
select * from gift where gno between 9 and 6; --error!! -> 앞의수가 뒤의수보다 항상 작아야함
 
select * from gift where g_end between 600000 and 800000;
 cs

between A and B ==> 항상 A < B 

6.any -> any(~,~,,,)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--6)any
/*
컬럼명 < any(100, 200, 300) => 최대값보다 작다
컬럼명 > any(100, 200, 300) => 최소값 보다 크다
*/
--6-1)필드명 < any(최대값) 보다 작은 값들을 찾음 
select profno, id, name from professor
    where profno < any(1000, 2000, 3000); 
--==>3000보다 작은 값들 찾기 
 
--6-2)필드명 > any(최소값) 보다 큰 값들을 찾음 
select profno, id, name from professor
    where profno > any(2000, 3000);
--==>2000보다 큰 값들 찾기 
 cs

7.all -> all(~,~,,,)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--7)all
/*
검색결과의 모든 값이 일치하면 참.
ex) 컬럼명 < all(100, 200, 300) => 최소값보다 작다
     컬럼명 > all(100, 200, 300) => 최대값보다 크다
*/
 
--7-1)필드 < all(최소값) 보다 작은 값
select profno, id, name from professor
    where profno < all(2002, 3000); 
 
--7-2)필드 > all(최대값) 보다 큰 값
select profno, id, name from professor
    where profno > all(2002, 3000);
 cs

8.not 반대조건 

1
2
3
4
5
6
7
--8)not
/*
not => 현재상태의 반대값
ex) id not in (1,3,5)
    age not between 10 and 20
    name not like ‘j%’
*/
 cs

김씨가 아닌 수습직원 찾기 
select  * from emp2 where name not like '김%' and emp_type='수습직';

select * from emp2 where name not like '나%';
select * from emp2;

9.null -> is null    /     is not null

1
2
3
4
5
--9)null 값 찾기 
/*
is null => null인 값 찾기
is not null => null 아닌 값 찾기
*/
 cs

'Database > Basic' 카테고리의 다른 글

집합(그룹) 함수-sum,avg,max,min,count,rank + GROUP BY , HAVING  (0) 2021.02.08
between, order by(오름차순과 내림차순)  (0) 2021.02.08
연산자(mod, nvl)  (0) 2021.02.07
null과 ' '  (0) 2021.02.07
중복행제거 distinct  (0) 2021.02.07

1.mod 나머지를 알려줌 -> mod(값, 나눌 수)

1
2
3
4
5
--1)mod: 나머지 -> mod(값, 나눌 수)
select mod(7, 3) from dual;
 
select * from professor;
select pay, mod(pay, 100) as "100으로 나눈 급여" from professor;
 cs

2.nvl 값에 null이 있으면 대체값으로 변경 -> nvl(값, 대치값)

1
2
3
4
5
6
7
8
9
10
11
--2)nvl 함수: nvl(값, 대치값) - 값에 null이 있으면 대치값으로 변경한다
select name, bonus, bonus + 100 from professor; --null연산은 연산해도 null
 
update professor set bonus  = bonus + 100; --null연산은 update해도 null
select name, bonus, bonus + 100 from professor;
 
--해결)
select name, bonus, nvl(bonus, 0) from professor;
select name, bonus, nvl(bonus, 0)+5000 from professor;
 
update professor set bonus  = nvl(bonus, 0) + 3000;
 cs

cf)null은 연산이나 update해도 변하지 않고 null값임

'Database > Basic' 카테고리의 다른 글

between, order by(오름차순과 내림차순)  (0) 2021.02.08
조건연산자(like, or, and, in, between, any, all, not, null)  (0) 2021.02.08
null과 ' '  (0) 2021.02.07
중복행제거 distinct  (0) 2021.02.07
레코드 삽입, 수정, 삭제  (0) 2021.02.07

+ Recent posts

티스토리툴바