함수
-
단일행함수 = > 통계처리함수
-
그룹함수
레코드갯수 => count(*) or count(필드명) = 필드에 들어가 있는 갯수를 체크 필드에 null은 배제(갯수에 포함X)
-
count(*)->null까지 계산에 포함시킨다.
-
min(필드명)->최소값,
-
max(필드명)=>최대값,
-
avg(필드명)=>평균,
-
sum(필드명)=>합계
<문1> 그룹함수의 종류
SQL>select count(*),trunc(avg(sal)),max(sal),
min(sal),to_char(sum(sal),'$999,999')
from emp;
sql>select count(*),trunc(avg(sal)),max(sal),
min(sal),to_char(sum(sal),'$999,999')
from emp;
COUNT(*) TRUNC(AVG(SAL)) MAX(SAL) MIN(SAL) TO_CHAR(S
---------- --------------- ---------- ---------- ---------
14 2073 5000 800 $29,025
<문2> 그룹함수의 성격
ed
SQL> ed
select ename,count(*),trunc(avg(sal)),max(sal),
min(sal),to_char(sum(sal),'$999,999')
from emp
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 select ename,count(*),trunc(avg(sal)),max(sal),
2 min(sal),to_char(sum(sal),'$999,999')
3* from emp
SQL> /
select ename,count(*),trunc(avg(sal)),max(sal),
*
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다=>그룹함수를 select와 from사이에 사용할때 주의할 점
기존의 필드와 같이 사용하지 못한다. (에러메세지)
=>일반필드와 그룹함수를 같이 select ~ from 사이에 사용?
형식) select 필드명(ex ename),,,그룹함수,,
from 테이블명
where 조건식
group by 그룹을 지어줄 필드명(ename),,,
order by 정렬할 필드명
SQL> ed
1 select ename,count(*),trunc(avg(sal)),max(sal),
2 min(sal),to_char(sum(sal),'$999,999')
3 from emp
4* group by ename
SQL> /
ENAME COUNT(*) TRUNC(AVG(SAL)) MAX(SAL) MIN(SAL) TO_CHAR(S
---------- ---------- --------------- ---------- ---------- ---------
ALLEN 1 1600 1600 1600 $1,600
JONES 1 2975 2975 2975 $2,975
FORD 1 3000 3000 3000 $3,000
CLARK 1 2450 2450 2450 $2,450
MILLER 1 1300 1300 1300 $1,300
SMITH 1 800 800 800 $800
WARD 1 1250 1250 1250 $1,250
MARTIN 1 1250 1250 1250 $1,250
SCOTT 1 3000 3000 3000 $3,000
TURNER 1 1500 1500 1500 $1,500
ADAMS 1 1100 1100 1100 $1,100
ENAME COUNT(*) TRUNC(AVG(SAL)) MAX(SAL) MIN(SAL) TO_CHAR(S
---------- ---------- --------------- ---------- ---------- ---------
BLAKE 1 2850 2850 2850 $2,850
KING 1 5000 5000 5000 $5,000
JAMES 1 950 950 950 $950
14 개의 행이 선택되었습니다.
<문5>emp테이블에서 급여가 3000이상인 사원수를 구하시오.=>갯수(count(*))
SQL> select count(*) from emp where sal>=3000;
COUNT(*)
----------
3
SQL> ed
1 select ename,count(*) from emp
2 where sal>=3000
3* group by ename
SQL> /
ENAME COUNT(*)
---------- ----------
FORD 1
SCOTT 1
KING 1
<문6> count(*),sum,min,max,avg=>숫자화된 데이터만 사용이 가능
=>** 날짜,문자데이터도 사용이 가능하다. **
-
min(hiredate) => 장기 근무자(1980년대) =>가장 오래된 사원의 날짜
-
max(hiredate) => 날짜가 크다->2020년대에 가까운 날짜 ->최근에 입사한 날짜
SQL>select min(ename),max(ename),
min(hiredate),max(hiredate),
min(sal),max(sal) from emp;
SQL>select min(ename),max(ename),
min(hiredate),max(hiredate),
min(sal),max(sal) from emp;
MIN(ENAME) MAX(ENAME) MIN(HIRE MAX(HIRE MIN(SAL) MAX(SAL)
---------- ---------- -------- -------- ---------- ----------
ADAMS WARD 80/12/17 87/05/23 800 5000문자 데이터(A~Z)=>알파벳의 나오는 순서(A에 가까운 경우 적다, Z에 가까운 경우 크다)
<문7>부서별로 사원의 수가 4명 이상인 사원의 정보를 찾아서 출력하되, 부서번호,급여의 합계를 구하시오.
(모든 직원 대상X)
SQL> select deptno,sum(sal) from emp where count(*)>=4 group by detpno;
SQL> select deptno,sum(sal) from emp where count(*)>=4 group by detpno;
select deptno,sum(sal) from emp where count(*)>=4 group by detpno
*
1행에 오류:
ORA-00934: 그룹 함수는 허가되지 않습니다
조건식의 종류
-
where 조건식=>일반적인 검색(default)
-
having 조건식=>모든 부서를 그룹별로 검색=>직원의 수가 4명(그룹만)
그룹에 대한 조건이 필요할때 사용
select 필드명,,그룹함수
from 테이블명
where 일반 조건식
group by 그룹필드명,,,
having 조건식(그룹에 대한 조건식)
order by 정렬 필드명
SQL> ed
1 select deptno,sum(sal) "급여합계"
2 from emp
3 group by deptno
4 having count(*)>=4
5* order by sum(sal) desc
SQL> /
DEPTNO 급여합계
---------- ----------
20 10875
30 9400
<문8> 그룹함수도 중첩이 가능하다.=>2단계까지
부서별로 평균값중에서도 최대 평균 급여?=>max(avg(sal))
1) 2)
부서별로 합계중에서 최대 급여?=> max(sum(sal))
부서별로 합계중에서 최소 급여?=> min(sum(sal))
SQL> select max(avg(sal)),max(sum(sal)),min(min(sal)),
max(max(sal)) from emp
group by deptno;
MAX(AVG(SAL)) MAX(SUM(SAL)) MIN(MIN(SAL)) MAX(MAX(SAL))
------------- ------------- ------------- -------------
2916.66667 10875 800 5000
<문9> emp테이블에서 업무가 MANAGER가 아닌 사원에 대한 부서별로 급여의 합이 2500이상인 부서의 정보를
출력하시오. (단 부서번호,급여의 합계순으로 출력, 급여의 합계가 높은 순으로 정렬)
업무가 MANAGER가 아닌 사원 => where job!='MANAGER' ^=,<>
부서별로 => group by deptno
급여의 합이 2500이상인 => 그룹의 조건식 = having 조건식
SQL> select deptno,sum(sal) "부서별 급여합계"
from emp
where job <>upper('manager')
group by deptno
having sum(sal)>=2500
order by sum(sal) desc;
DEPTNO 부서별 급여합계
---------- ---------------
20 7900
30 6550
10 6300하나의 테이블을 검색하는 모든 방법 => 한개이상의 테이블 검색(=>join)
SQL> conn test1/t1234
연결되었습니다.
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
B_PRTEST TABLE
B_PRTEST2 TABLE
PRTEST TABLE
=> select * from prtest;//(X)
select count(*) from prtest;
COUNT(*)
----------
4
DDL(테이블의 설계)=>필드(=컬럼) 누락,필드의 자료형 체크해서 관리
<문10> 데이터를 저장하는 방법=>명령어 DDL(create~,alter~,drop~) =>실수X =>복구X
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID (사번) NUMBER(3)=>숫자
NAME(사원이름) VARCHAR2(10)=>문자
형식) alter table 수정할t명 add (추가할 필드명 자료형 제약조건)
제약조건=>올바른 값만 입력을 받을 수 있도록 규칙을 정해주는것.
SQL> alter table b_prtest add (tel varchar2(15) not null);
SQL> alter table b_prtest add (tel varchar2(15) not null);
테이블이 변경되었습니다.
제약조건을 줄때 에러유발=>테이블에 이미 테이터가 들어가 있는 경우
구조를 마음대로 변경하기가 힘들기 때문
소----->대(O) varchar2(10)===>varchar2(30)
대----->소(X) varhcar2(30)==>varchar2(10) (X)
varchar2(10)<--->number(10)(X) =>데이터가 이미 있는 경우의 필드의 자료형X
varchar2(10)<--->number(10)(O) =>테이블에 데이터가 없다면 가능
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME VARCHAR2(10)=>varchar2(30)
TEL NOT NULL VARCHAR2(15)
SQL> select * from b_prtest; //마음대로 변경이 가능하다.
선택된 레코드가 없습니다.=>alter table b_prtest add (tel varchar2(15) not null); (O)
1 홍길동
2 임시
=>alter table b_prtest add (tel varchar2(15)); (O) =>not null 사용X
=>자동으로 null 이 된다.(필수 입력이 아니다.)
<문11> 필드의 변경(modify)
형식) alter table 수정t명 modify(수정할 필드명 데이터형 제약조건)
SQL> alter table b_prtest modify(name number(3));
테이블이 변경되었습니다.
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME NUMBER(3)=>varchar2(30)
TEL NOT NULL VARCHAR2(15)
<문12>불필요로하는 컬럼 존재=>삭제
형식) alter table 수정t명 drop column 삭제시킬 필드명;
SQL> alter table b_prtest drop column tel;
SQL> alter table b_prtest drop column tel;
테이블이 변경되었습니다.
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME VARCHAR2(30)
<문13> 필드명을 변경?
형식) alter table 수정t명 rename column 수정전 필드명 to 수정후 필드명
SQL> alter table b_prtest rename column name to irum;
SQL> alter table b_prtest rename column name to irum;
테이블이 변경되었습니다.
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
IRUM VARCHAR2(30)
<문14> 제약조건
DB관리
1.어떻게 올바른 값을 저장시킬것인가? (설계)목적=>제약조건이 필요한 이유
2.어떻게 하면 내가 원하는 자료를 내 마음대로 검색?=>통계처리=>출력
제약조건의 종류 및 설정
-
테이블을 작성할때 반드시 부여해야 한다.(필수)
-
primary key(기본 키)
-
테이블의 행을 구분하는 키워드로 사용 (개요)
-
중복되면 안되는 필드에 부여 (empno(사번),계좌번호,주민등록번호,,,)
-
not null+unique index키가 자동으로 부여된다. (필수입력) (유일한 인덱스키(=책갈피))
인덱스키 => 내부에서 먼저 검색->검색속도가 증가됨
중복허용 => name,성별,직책(X)
-
형식) alter table 수정t명 add constraint 제약조건이름 제약조건종류(적용필드명)
primary key(id)
SQL> desc b_prtest
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME VARCHAR2(30)SQL> alter table b_prtest add constraint
b_prtest_pk_id primary key(id);
테이블이 변경되었습니다.
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3)
=======
NAME VARCHAR2(30)
SQL> alter table b_prtest add constraint
b_prtest_pk_id primary key(id);
sql>alter table b_prtest add constraint
b_prtest_pk_id primary key(id);
테이블이 변경되었습니다.
SQL> desc b_prtest
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3)
=======
NAME VARCHAR2(30)
SQL> select * from b_prtest;
SQL> insert into b_prtest values(1,'홍길동');
SQL> insert into b_prtest values(1,'홍길동');
1 개의 행이 만들어졌습니다.
SQL> select * from b_prtest;
ID NAME
---------- ------------------------------
1 홍길동
SQL> insert into b_prtest values(1,'홍길동');
1* insert into b_prtest values(2,'홍길동')
SQL> /
1 개의 행이 만들어졌습니다.
SQL> select * from b_prtest;
ID NAME
---------- ------------------------------
1 홍길동
2 홍길동
<문15>unique 제약조건=>중복되면 안되는 필드에 부여=>pk와 기능이 동일
pk => not null + unique index key같이 부여
unique key => 중복허용 안되는 필드에 부여(not null 부여 X)
공통점 차이점
형식) alter table 수정t명 add constraint 제약조건이름 제약조건종류(필드명)
=====unique(id)
SQL> alter table b_prtest add constraint
b_prtest_uk_id unique(id);
SQL> alter table b_prtest add constraint
b_prtest_uk_id unique(id);
b_prtest_uk_id unique(id)
*
2행에 오류:
ORA-02261: 고유 키 또는 기본 키가 이미 존재하고 있습니다=> pk -> 이미 id에 unique키가 부여된 상태이기 때문에 또 부여할 수가 없다는 에러메세지
<문16> 제약조건을 삭제(drop constraint)
형식) alter table 수정t명 drop constraint 삭제시킬 제약조건이름
SQL> alter table b_prtest drop constraint b_prtest_pk_id;
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3) =>not null
NAME VARCHAR2(30)
<문제17> check 제약조건 => 데이터를 저장시킬때 과연 올바른 값이 들어왔는지
체크(확인) -> 숫자 -> 범위지정, 문자 -> 특정한 값 등록
SQL> alter table b_prtest add (age number not null);
SQL> alter table b_prtest add (age number not null)
*
1행에 오류:
ORA-01758: 테이블은 필수 열을 추가하기 위해 (NOT NULL) 비어 있어야 합니다.
=>데이터가 존재하면 not null은 불가능
alter table b_prtest add (age number)
alter table b_prtest add (addr varchar2(80));
테이블이 변경되었습니다.
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME VARCHAR2(30)
AGE NUMBER
ADDR VARCHAR2(80)
형식) ** ~ 제약조건이름 check(age >=20 and age <=65)
제약조건 규칙
check in ('사원','대리','계장','과장','부장','이사')
=>** 자바=>캡슐화(올바른 값만 입력을 받을 수 있도록 설계)
SQL> alter table b_prtest add constraint b_prtest_ck_age
check (age>=20 and age<=65);
~ check (age between 20 and 65);
SQL> insert into b_prtest values (3,'테스트',123,'서울시 강남구 대현빌딩');
SQL> insert into b_prtest values
(3,'테스트',123,'서울시 강남구 대현빌딩');
insert into b_prtest values
*
1행에 오류:
ORA-02290: 체크 제약조건(TEST1.B_PRTEST_CK_AGE)이 위배되었습니다
ed
SQL> select * from b_prtest;
ID NAME AGE
---------- ------------------------------ ----------
ADDR
--------------------------------------------------------------------------------
1 홍길동
2 홍길동
3 테스트 23
서울시 강남구 대현빌딩
<문제18> 제약조건 -> pk, check, unique, not null
default(디폴트) => 값을 저장시킬때 '저장할값', 23
언급하지 않아도 자동으로 값이 들어가는 경우 -> 발생
SQL> alter table b_prtest add (hiredate date default sysdate);
SQL> alter table b_prtest add (hiredate date default sysdate);
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME VARCHAR2(30)
AGE NUMBER
ADDR VARCHAR2(80)
HIREDATE DATE형식) insert into b_prtest values(4,'테스트2',23,'서울시 강남구');
insert into를 통해서 값을 저장 => 주의할 점 1. 반드시 필드의 갯수를 맞춰야 된다. insert into 테이블명 values(필드명,'저장할값',23,'2020-07-02',,,,)
SQL> insert into b_prtest values(4,'테스트2',23,'서울시 강남구');
insert into b_prtest values(4,'테스트2',23,'서울시 강남구')
*
1행에 오류:
ORA-00947: 값의 수가 충분하지 않습니다 =>갯수가 부족,넘치는 경우
ed
1* insert into b_prtest values(4,'테스트2',23,'서울시 강남구','2020-07-02')
SQL> / ===수동===
1 개의 행이 만들어졌습니다.
insert into b_prtest values(5,'테스트3',34,'대전시 중구',sysdate)
=====함수를 이용
insert into b_prtest values(6,'테스트44',44,'부산시',default);
===sysdate 실행
필수데이터가 아닌경우 저장 또는 저장X 선택(null)
insert into b_prtest values(7,'테스트55',43,'마산시',null); //날짜를 저장X
insert into 구문2
반드시 저장해야할 필드명(not null)
형식) insert into 테이블명(필드명1,필드명2,,,) values(값1,값2,,,,)
============필드명을 생략할 수 있다.
null을 허용해주는 필드 또는
default가 적용된 필드
8번 데이터를 입력=>addr(null), hiredate->default 적용=>2번째 방법이 편리
insert into b_prtest(id,name,age) values(8,'테스트66',27);
'KIC > ORACLE' 카테고리의 다른 글
| [ Oracle] 서브쿼리, 집합연산자 (0) | 2020.10.12 |
|---|---|
| [Oracle] 조인개요 및 작성법 (0) | 2020.10.12 |
| [Oracle] 함수활용 (0) | 2020.10.12 |
| [Oracle] select를 이용한 검색방법 및 SQLPlus 명령어 사용법 (0) | 2020.10.12 |
| [Oracle] (SQL 기초) (0) | 2020.09.21 |