07월 19일 금 | SQL활용 02 - SQL제약조건

기본 SQL 작성하기

제약조건

1. 테이블생성

어제 만들었던 테이블을 삭제하고 새로운 테이블을 생성하였다.

create table c_emp(     id           NUMBER(5)         ,name        VARCHAR2(25)      ,salary      NUMBER(7, 2) check(salary between 100 and 1000)     ,phone       VARCHAR2(15)      ,dept_id     NUMBER(7)     );

 

2. 데이터 insert

insert into c_emp(id,name,salary) values(1, '개나리', 100); insert into c_emp(id,name,salary) values(1, '진달래', 1000); insert into c_emp(id,name,salary) values(1, '진달래', 10000);

세번 째 행에서 오류가 생긴다. 제약조건에 벗어났기 때문이다.

이러한 메시지가 뜬다.

 

 

 

3. name 컬럼에 unique 제약조건을 건 테이블 새로 생성

create table c_emp(     id           NUMBER(5)         ,name        VARCHAR2(25) constraints c_emp_name_un unique     ,salary      NUMBER(7, 2) check(salary between 100 and 1000)     ,phone       VARCHAR2(15)      ,dept_id     NUMBER(7)     );

데이터를 삽입하려고 한다.

insert into c_emp(name) values('진달래'); insert into c_emp(name) values('진달래');

2행의 데이터삽입이 실패한다. 왜냐하면 제약조건을 어겼기 때문이다.

unique는 중복된 데이터가 두 개이상 들어갈 수 없다.

 

id데이터를 삽입해보았다.

insert into c_emp(id) values(1); insert into c_emp(id) values(2);

현재의 테이블 상태이다.

 

 

4. dept_id컬럼에 foreign key 제약조건

 

1) 테이블 새로 생성

create table c_emp(     id           NUMBER(5)         ,name        VARCHAR2(25)     ,salary      NUMBER(7, 2)     ,phone       VARCHAR2(15)      ,dept_id     NUMBER(7) constraint c_emp_dept_id_fk references dept(deptno)     );

dept_id에 dept(deptno)를 참조하는 외래키 제약조건을 걸었다.

 

 

2) 데이터 삽입

insert into c_emp(name,dept_id) values('김지호', 10); insert into c_emp(name,dept_id) values('오민우', 40); insert into c_emp(name,dept_id) values('안지용', 90);

정해놓은 범위를 벗어났기 때문에 오류 발생하였다.

왜냐하면 dept_id가 참조하는 dept테이블의 deptno의 범위가 50까지이기 때문이다.

 

 

 

 

4. 최종 테이블 새로 생성

 

1) 테이블 새로 생성

create table c_emp(     id           NUMBER(5) primary key     ,name        VARCHAR2(25) not null     ,salary      NUMBER(7, 2) check (salary between 0 and 100)     ,phone       VARCHAR2(15) null     ,dept_id     NUMBER(7) references dept(deptno) );

 

 

2) 데이터사전에서 제약조건 확인하기

select * from user_constraints where table_name='c_emp'

 

 

 

관계형 DB에서 테이블의 핵심기능

1. CRUD

C Create, R Read, U Update, D Delete

C Create	insert문
R Read	select문
U Update	update문
D Delete	delete문

 

 

 

정의서로 테이블 생성

1. 정의서 작성

정의서 템플릿을 토대로 새로운 성적 정의서를 작성하였다.

 

 

 

2. 테이블생성

정의서를 토대로 테이블을 생성하였다.

create table SUNGJUK(      sno          NUMBER(5) primary key         ,uname     VARCHAR2(50)       ,kor          NUMBER(3) check(kor between 0 and 100)      ,eng         NUMBER(3) check(eng between 0 and 100)     ,mat         NUMBER(3) check(mat between 0 and 100)     ,tot          NUMBER(3) default 0     ,aver        NUMBER(5,2) default 0     ,addr        VARCHAR2(30) check(addr in ('SEOUL','JEJU', 'SUWON','BUSAN'))     ,wdate      DATE default sysdate     );

 

이렇게 테이블이 생성되었다.

 

 

자동으로 일련번호 부여

Oracle: Sequence

MySQL: identify

 

 

시퀀스생성

create sequence sungjuk_seq increment by 1                       --시퀀스이름 start with 103                         --증가값 maxvalue 1000000000              --최대값   nocache                                --캐시사용여부 nocycle;                                --순환여부

 

 

시퀀스 목록조회

select * from user_objects where object_type ='sequence';

 

 

시퀀스 호출함수

주의: 시퀀스 생성 후 nextval을 호출해야 시퀀스에 초기값이 설정됨

nextval : 다음값을 반환함. 다음번호 발급

select sungjuk_seq.nextval from dual;

 

currval: 현재값을 반환함. 최근 발급된 번호

select sungjuk_seq.currval from dual;

 

 

서브쿼리를 이용한 일련번호 발급

주의: 시퀀스와 혼합해서 사용하지 않도록 주의

 

임시테이블: dual

 

 

 

연습문제 1) 시퀀스 문제

c_emp 테이블에 데이터 입력시 sequence를 이용해서 id를 입력하도록

206에서 시작하여 1씩 증가되고, 최대값은 999로 설정하여 시퀀스를 생성하시오.

시퀀스 이름은 c_emp_seq

create sequence c_emp_seq increment by 1 start with 206 maxvalue 999;

 

insert into c_emp (id) values (c_emp_seq.nextval);

 

 

nvl() 함수

-- 레코드 전부삭제 delete from sungjuk; --국어점수의 최고점 조회 select max(kor) from sungjuk; --null값이 나오면 0으로 바꿈 select nvl(max(kor),0) from sungjuk;

 

서브쿼리를 이용한 데이터 삽입

--서브쿼리를 이용해 일련번호 구하는 식 select nvl(max(kor),0) from sungjuk;   --데이터 삽입 insert into sungjuk (sno, uname, kor, eng, mat, addr) values ((select nvl(max(kor),0)+1 from sungjuk),'김상중', 95,88,77,'SEOUL');

 

 

동일한 데이터
distinct : 컬럼에 중복내용이 있으면 대표값 1개만 나오게 함.
group by 절

select addr from sungjuk; select distinct(addr) from sungjuk; select distinct(addr) from sungjuk order by addr desc; --내림차순

 

 

group by

컬럼에 동일 내용끼리 그룹화 시킴

형식) group by 컬럼명1, 컬럼명2~~

 

select addr, uname from sungjuk group by addr; --오류생김

group by에 의한 결과값이 오로지 1개만 존재하는 값만 조회할 수 있다.

주로 집계함수와 많이 사용한다.

빅데이터 다룰 때 쓴다. 

 

 

SQL 연산

select count(*),        sum(kor),        avg(eng),        max(mat),        min(tot)        from sungjuk;

결과값:

 

 

 

연습문제 2) Group by 문제

각 주소별 인원수를 구하시오.

 

select addr, count(*) from sungjuk group by addr; --이건 오류 안생김 select addr, count(*) as cnt from sungjuk group by addr; -- as~ 생략가능

 

주소별 인원수를 구한 후, 주소순으로 정렬

select addr, count(*) from sungjuk group by addr order by addr;

 

주소별 인원수를 구한 후, 인원수 순으로 내림차순 정렬하시오.

select addr, count(*) as cnt from sungjuk group by addr order by cnt desc;

 

 

주소별 국어점수 평균을 구한 후 국어점수 평균순으로 내림차순 정렬해서 조회하시오.

select addr, avg(kor) as avg_kor from sungjuk group by addr order by avg_kor desc;

결과가 나오긴 했는데 너무 말도 안되게 자릿수가 길어서

select addr, round(avg(kor)) as avg_kor from sungjuk group by addr order by avg_kor desc;

이렇게 round 함수를 사용하였다. 

그랬더니 반올림된 값이 나와 깔끔해졌음.

 

 

 

지역별 국,영,수 최고점을 지역별로순으로 정렬해서 조회하시오.

select addr,max(kor),max(eng),max(mat) from sungjuk group by addr order by addr;

 

1차값이 동일하다면 

그 그룹내에서 2차 그룹이 가능하다.

2차값이 동일하다면

3차 그룹도 가능하다.

 

 

 

지역별로 그룹핑을 하고 만일 지역이 동일하다면 수학점수별로 그룹핑을 하시오.

select addr, mat

from sungjuk group by addr, mat;

 

select addr, mat, (count

from sungjuk group by addr, mat;

 

 

 

aver컬럼을 구한 후 aver컬럼값이 50이상인 레코드 대상으로 지역별 국영수 평균을 반올림해서 소수점 1자리까지 구현후 조회하시오.

update sungjuk set aver = (kor+eng+mat)/3; select aver from sungjuk where aver >= 50; select addr,aver from sungjuk where aver >= 50; select addr, round(avg(kor),1),round(avg(eng),1),round(avg(mat),1) from sungjuk where aver >= 50 group by addr order by addr;

 

 

 

조건절

where 조건절

having 조건절 -> group by와 함께 사용

on 조건절 -> 테이블 조인할 때 사용

 

 

 

having 조건절

select addr, count(*) from sungjuk group by addr having count(*)=3;

 

 

문) 지역별 국어점수 평균을 구한 후 그 평균이 80점 이상인 지역만 조회

select addr,avg(kor) from sungjuk group by addr having avg(kor)>=80;

 

80점 이하인 지역만 조회

select addr,avg(kor) from sungjuk group by addr having avg(kor)>=80;

 

60~79점 사이인 지역 조회

select addr,avg(kor) from sungjuk group by addr having avg(kor) between 60 and 79;