[Oracle] SQL Class 4 - [단일행함수] Constraint(제약조건) 및 문법

2023.10.30

## Constraint(제약조건): 컬럼(인덱스)단위 속성 --> 데이터무결성 구현 목적  (이해만 하면 됨, 외우지 않아도 됨)

다양한조건의 영향을 받는 무결점

 

1. 개체 무결성

- not null : null 허용X

- unique : 유일한 제약 (중복값 안됨, null은 허용됨)                        ex. unique + not null 가능 주로 주민번호값

V primary Key(기본키): not null과 unique 안됨, 1테이블당 1개만 존재 --> 테이블 식별자

 

2. 영역 무결성

- check : 조건식 영역 내의 유효한 값 체크                                    ex.남자/여자 

- default(기본값): 입력시 제외되는 컬럼의 기본값 지정. oracle SQL Plus만 사용. mssql은 미사용                 ex. 날짜

 

3. 참조 무결성

V foreign key(참조키) : 다름 컬럼의 값을 참조, 반드시 기본키여야 됨(기본키 아니면 안됨)

- 부모 컬럼 외에는 다른 컬럼을 취할 수 없음.

 (foreigen key의 옵션)

- delete cascade: 참조되는 테이블의 기본키인 primary key. 변경되면 모두 다 삭제됨.

- update cascade: 변경되면 모두 다 업데이트 됨.

##  Constraint(제약조건)의 문법 --> 기존 테이블 문법을 보고 이해하는 정도만 해도 됨.

1. 컬럼 단위

- 컬럼정의와 함께

ex. SQL>  create table info(id number(10) [constraint 제약명-생략가능, 사용자정의로 만들어도 됨, 안그러면, 시스템에서 자동으로 부여됨 ex. SYSY_C00000 ] primary key, ....);

사용자 정의로 주로 table_col_제약으로 만듬 ex. info_id_pk

 --> not null은 컬럼단위 밖에 안됨.

--> contraint(C1,C2,C3) 처럼 테이블의 컬럼을 여러개 묶어서 지정할 수 있음.

 

연습1)

create table student(
    hakbun number(10) constraint st_hakbun_pk primary key,
    sname varchar2(10) constraint st_sname_nn not null,
    age    number(3) constraint st_age_ck check(age>=20 and age<=30),
    addr   varhcar2(30) default '서울시 구로구',
);

create table lib(
   sno number(10) constraint lib_sno_fk references student(hakbun) [on delete cascase],
   book varchar2(100) constraint lib_book_nn not null,
   loan_date date default sysdate
);

 

 

 

2. 테이블 단위

-컬럼 추가 하듯이 복합컬럼 제어단위 임.

ex. SQL> create table info(id number(10), name varchar2(10), .... constraint info_id_pk primary key(id-제약조건을 걸어둘 컬럼명을 사용함));

 

연습2)

create table student(
    hakbun number(10),
    sname varchar2(10) constraint st_sname_uq unique,
    age    number(3),
    addr   varchar2(30) default '서울시 구로구',
    constraint st_hakbun_pk primary key(hakbun),
    constraint st_age_ck check(age>=20 and age<=30)
);

create table lib(
   sno number(10),
   book varchar2(100) constraint lib_book_nn not null,
   loan_date date default sysdate,
  constraint lib_sno_fk foreign key(sno) references student(hakbun)
);

--제약조건 수정
alter table table_name
modify book varchar2(100) [ constraint lib_book_nn not null | null ];                        // not null/null만 modify로!

alter table table_name
--add constraint st_hakbun_pk primary key(hakbun);                                              // 제약추가
--drop constraint st_hakbun_pk;         // 제약삭제

 

연습3) 명단 넣기

--Dept Table 만들기
create table dept(
                deptno  Number(3) ,
                dname  Varchar2(10) , 
                loc  Varchar2(10),
                constraint dept_deptno_pk primary key(deptno),
                constraint dept_dname_uq unique(dname)
);

insert into dept values(10, '총무부','서울');
insert into dept values(20, '영업부','대전');
insert into dept values(30, '전산부','부산');
insert into dept values(40, '관리부', '광주');

--Sawon Table 만들기
create table sawon(
         sabun number(3), 
         saname varchar2(10) constraint sawon_saname_nn not null, 
         deptno number(3), 
         sajob varchar2(10),  
         sapay number(10), 
         sahire date default sysdate, 
         sasex varchar2(4), 
         saMgr number(3), 
 constraint sawon_sabun_PK primary key(sabun), 
 constraint sawon_deptno_FK foreign key(deptno) references dept(deptno), 
 constraint sawon_sasex_ck check(sasex in ('남자','여자')), 
 constraint sawon_saMgr_FK foreign key(samgr) references sawon(sabun)) ;

Insert Into sawon Values(1,'홍길동',10,'회장',5000,'1980/01/01','남자',null);
Insert Into sawon Values(2,'한국남',20,'부장',3000,'1988/11/01', '남자',1);
Insert Into sawon Values(3,'이순신',20,'과장',3500,'1985/03/01','남자', 2);
Insert Into sawon Values(5,'이순라',20,'사원',1200,'1990/05/01','여자', 3);
Insert Into sawon Values(7,'놀기만',20,'과장',2300,'1996/06/01','여자', 2);
Insert Into sawon Values(11,'류별나',20,'과장',1600,'1989/12/01','여자', 2);
Insert Into sawon Values(14,'채시라',20,'사원',3400,'1993/10/01','여자', 3);
Insert Into sawon Values(17,'이성계',30,'부장',2803,'1984/05/01','남자', 1);
Insert Into sawon Values(13,'무궁화',10,'부장',3000,'1996/11/01','여자', 1);
Insert Into sawon Values(19,'임꺽정',20,'사원',2200,'1988/04/01','남자', 7);
Insert Into sawon Values(20,'깨똥이',10,'과장',4500,'1990/05/01','남자', 13);
Insert Into sawon Values(6,'공부만',30,'과장',4003,'1995/05/01','남자', 17);
Insert Into sawon Values(8,'채송화',30,'대리',1703,'1992/06/01','여자', 17);
Insert Into sawon Values(12,'류명한',10,'대리',1800,'1990/10/01','남자', 20);
Insert Into sawon Values(9,'무궁화',10,'사원',1100,'1984/08/01','여자', 12);
Insert Into sawon Values(4,'이미라',30,'대리',2503,'1983/04/01','여자', 17);
Insert Into sawon Values(10,'공부해',30,'사원',1303,'1988/11/01','남자', 4);
Insert Into sawon Values(15,'최진실',10,'사원',2000,'1991/04/01','여자', 12);
Insert Into sawon Values(16,'김유신',30,'사원',400,'1981/04/01','남자', 4);
Insert Into sawon Values(18,'강감찬',30,'사원',1003,'1986/07/01','남자', 4);

--Gogek Table 만들기
create table gogek(

        gobun number(3), 
        goname varchar2(10), 
        gotel varchar2(20), 
        gojumin varchar2(14), 
        godam number(3));

ALTER TABLE GOGEK 
ADD constraint gogek_gobun_PK primary key(gobun);

ALTER TABLE GOGEK 
ADD constraint gogek_gojumin_UQ unique(gojumin);

ALTER TABLE GOGEK 
ADD constraint gogek_godam_FK foreign key(godam) 
references sawon(sabun);

insert into gogek values(1,'류민', '123-1234', '700113-1537915',3);
insert into gogek values(2,'강민', '343-1454', '690216-1627914',2);
insert into gogek values(3,'영희', '144-1655', '750320-2636215',null);
insert into gogek values(4,'철이', '673-1674', '770430-1234567',4);
insert into gogek values(5,'류완', '123-1674', '720521-1123675',3);
insert into gogek values(6,'캔디', '673-1764', '650725-2534566',null);
insert into gogek values(7,'똘이', '176-7677', '630608-1648614',7);
insert into gogek values(8,'쇠돌', '673-6774', '800804-1346574',9);
insert into gogek values(9,'홍이', '767-1234', '731225-1234689',13);
insert into gogek values(10,'안나','767-1677', '751015-2432168',4);
commit; (보조장치(HDD) 저장, 메모리에 저장 안하면 삭제되므로 계정에 저장하는 용도)

 

## 테이블 생성내역 확인

 

## 단일행 함수: 각 값을 별도의 함수로 추출하는 명령어

: DB에서는 반드시 리턴값이 1개 존재함.

- 단일행: 각 행별로 결과값을 추출(함수의 종류가 많음)

  다중행: 행이 여러개 들어가서 결과값을 1개 추출(함수의 종류가 정해져 있음)

 

기존 수학 함수도 이용할 수 있으나, 현업에선 사용할 일  거의 없음.

1. 반올림 관련 함수: round는 자리수 지정한 반올림(절상)

2. 반올림 관련 함수: trunc는 자리수 지정한 이하 버림

 

3. ceil: 소수이하 무조건 올림

    floor: 소수이하 무조건 버림  --> 개월수 구할때 주로 사용

 

연습4)

사원명, 급여, 월급여(급여/12), 세금(급여의 3.3%)를 추출

단, 월급은 십단위에서 반올림 하고, 세금은 일단위에서 절삭

 

4. length, lengthbm trim, ltrim, rtrim 함수

5. lower, upper, initcap 함수

6. replace 함수

 

7. 채우기 함수

lpad & rpad(데이터, 전체 크기, '채울 문자') --> lpad는 왼쪽에 채움    / rpad는 오른쪽에 채움.

연습5) 사원명, 급여, 급여현황(급여 100단위에 * 하나) 함수를 만드시오.

박길동 1500 ***************

이길동 1000 **********

왼쪽부터 빈공간 채우는 형태

## 문자열 추출 함수

substr(문자열, N번째, N개)

 

연습6)

고객명, 주민번호1(******-3182565), 주민번호2(041201-********)를 추출

 

검색문자의 위치값 

instr(문자열, '찾을문자', 1 혹은 -1(뒤), N번째)          // 대문자, 소문자 구분

 

## 조건함수

decode함수: decode(데이터, 조건1, 결과1, 조건2, 결과2,........, 그외 결과) "컬럼명"

case문 : case [데이터] when 조건1 then 결과1 when 조건2 then 결과2 ....... else 그외 결과 end "컬럼명"

연습7)

고객명, 전화번호, 성별을 추춘

 

(decode)

 

(case)

## 남상윤 교수님 메모참고.

단일행함수.txt

0.00MB