[19일] Database (3) - 정렬, 시퀀스, sysdate, 제약조건, distinct, group by, 집계함수

Sort 정렬

 

데이터베이스에서 정보를 가져올 때에는 정렬을 해서 가져오는 것이 좋다.

 

• 특정값(keyfield)을 기준으로 순서대로 재배치
• 오름차순 Ascending Sort (ASC)     생략하면 오름차순이 기본값   (1->10 A->Z a->z ㄱ->ㅎ)
• 내림차순 Descending Sort (DESC)
• 형식) order by 칼럼명1 (1차 정렬), 칼럼명2 (2차 정렬), 칼럼명3 (3차 정렬), ...

-- 전체 레코드를 이름 순으로 정렬해서 조회하시오
select uname
from sungjuk
order by uname asc;  --오름차순 정렬

select uname
from sungjuk
order by uname;  --asc 생략 가능

select uname
from sungjuk
order by uname desc;  --내림차순 정렬

 

오름차순

내림차순

 

--국어 점수 순으로 정렬해서 조회하시오
select uname, kor
from sungjuk
order by kor;

 

--1차 정렬 : 국어 점수 순으로 정렬
--2차 정렬 : 국어 점수가 같다면 이름을 기준으로 내림차순 정렬
select uname, kor
from sungjuk
order by kor, uname desc;

 

-- 1차(kor), 2차(eng), 3차(mat) 정렬
select uname, kor, eng, mat
from sungjuk
order by kor desc, eng desc, mat desc;

 

--문제) 평균 70점 이하 행을 이름 순으로 조회하시오
select uname, aver
from sungjuk
where aver<=70
order by uname;

select uname, aver
from sungjuk
order by uname
where aver>=70;    -- ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

위와 같이 order by와 where의 순서를 바꾸면 에러가 발생한다. 따라서 where로 먼저 원하는 데이터를 선별한 후 order by로 정렬해야 한다.

 

 

alter 문

테이블의 구조를 수정하거나 변경할 때 사용한다.

 

1. 컬럼 추가 

형식) alter table table명 add (컬럼명 데이터타입);

--music 칼럼 추가
alter table sungjuk add(music int null);

 

2. 컬럼명 수정

형식) alter table table명 rename column 원래 컬럼명 to 바꿀 컬럼명;

-- 국어 칼럼 kor을 korea 칼럼명으로 수정하시오
alter table sungjuk rename column kor to korea;

as는 1회성인 반면 alter로 컬럼명을 수정하면 완전히 바뀐다.

 

3. 컬럼 데이터 타입 수정

형식) alter table table명 modify(컬럼명 데이터타입);

-- music 칼럼의 자료형을 varchar 수정하시오
alter table sungjuk modify(music varchar(5));

 

 4. 컬럼 삭제 

형식) alter table table명 drop(컬럼명);

-- music 칼럼을 삭제하시오
alter table sungjuk drop(music);

 

 

시퀀스

자동으로 일련번호 부여

- Oracle Database : sequence 생성
- M*SQL Database : identity 제약조건

 

기존 sungjuk 테이블을 삭제하고 다시 만들자

-- sungjuk 테이블 삭제
drop table sungjuk;
commit;

-- sungjuk 테이블 생성
create table sungjuk(
     sno    int          not null  --일련번호
    ,uname  varchar(50)  not null  
    ,kor    int          not null
    ,eng    int          not null
    ,mat    int          not null
    ,tot    int
    ,aver   int
    ,addr   varchar(50)            -- 주소
    ,wdate  date                   -- 등록일(연월일시분초)
);

-- 행 추가 테스트
insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values (1, '홍길동', 89, 85, 90, 'Seoul', '2022-08-22');

날짜 데이터는 수동으로 넣을 수 있는데 문자열 형태로 넣어야 한다. 위 코드의 경우 날짜 구분 기호로 - 를 사용했는데 ' ' 이 없으면 연산이 되기 때문이다.

 

자동으로 시퀀스를 부여할 것이기 때문에 테스트로 추가한 행은 다시 지워주자

 

- 시퀀스 생성 : create sequence 시퀀스명;
- 시퀀스 삭제 : drop sequence 시퀀스명;

- 시퀀스 생성 형식) create sequence 시퀀스명
                  increment by 증가값 start with 시작값;

-- sungjuk 테이블에서 사용할 시퀀스 생성
create sequence sungjuk_seq;

-- sungjuk_seq 시퀀스 삭제
drop sequence sungjuk_seq;

-- 시퀀스를 이용한 행 추가
insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values (sungjuk_seq.nextval, '홍길동', 89, 85, 90, 'Seoul', '2022-08-22');

위 코드 3번 실행

sno 열에 자동으로 1, 2, 3의 일련번호가 부여됐다.

 

 

sysdate 함수

시스템의 현재 날짜 정보를 입력

insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values (sungjuk_seq.nextval, '홍길동', 89, 85, 90, 'Seoul', sysdate);

 

-- 문제) sno=2행을 삭제하시오
delete from sungjuk where sno=2;

 

문제

-- sungjuk 테이블 생성
create table sungjuk(
     sno    int          not null  --일련번호
    ,uname  varchar(50)  not null  
    ,kor    int          not null
    ,eng    int          not null
    ,mat    int          not null
    ,tot    int
    ,aver   int
    ,addr   varchar(50)            -- 주소
    ,wdate  date                   -- 등록일(연월일시분초)
);

[sungjuk 테이블 입력 데이터]
insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values(sungjuk_seq.nextval,'솔데스크', 90, 85, 95, 'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',40,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'진달래',90,50,90,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'개나리',20,50,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'봉선화',90,90,90,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'나팔꽃',50,50,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'선인장',70,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'소나무',90,60,90,'Busan',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'참나무',20,20,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'홍길동',90,90,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',80,80,90,'Suwon',sysdate);

-- 문1) 전체행에 대해서 총점(tot), 평균(aver)을 구하시오
update sungjuk set tot=kor+eng+mat, aver=(kor+eng+mat)/3;

-- 문2) 이름에 '나' 글자가 있는 행을 조회하시오
select uname
from sungjuk
where uname like '%나%'

-- 문3) 주소가 서울, 제주 행을 조회하시오
select uname, addr
from sungjuk
where addr in ('Seoul', 'Jeju');
-- where addr='Seoul' or addr='Jeju';

-- 문4) 국영수 세과목 모두 90점이상 행이 몇개인지 조회하시오
select count(*)
from sungjuk
where kor>=90 and eng>=90 and mat>=90;

-- 문5) 주소별로 정렬해서 조회하시오. 주소가 같다면 일련번호 순으로 다시 정렬해서 조회하시오
select sno, uname, addr
from sungjuk
order by addr, sno;

 

자료형

1. 문자형

 - 가변형 varchar(5)  'SKY'        5칸을 주어도 남는 2칸은 버림   예) 아이디, 비밀번호, 이름, 주소...

 - 고정형 char(5)       'SKY  '      남는 칸도 가져감                        예) 주민등록번호, 우편번호...

 

2. 숫자형

 - int 정수형

 

3. 날짜형

 - 연월일시분초
 - 구분 기호 - 와 / 기호를 사용한다
 - 문자열 타입으로 입력
 - date

 

테이블 제약조건

1. primary key

  - 기본키. 유일성
  - where 조건절에 걸릴 수 있는 대표적인 칼럼을 강제할 수 있다.

  - 중복을 허용하지 않음

  - null값 허용하지 않음 (빈 값을 허용하지 않음. 반드시 입력해야 함)
    예) 주민번호, 핸드폰 번호, 계좌번호, 아이디, 이메일...

 

2. not null

  - 빈 값을 허용하지 않음

 

3. check

  - 입력할 값을 특정 범위로 제한하는 조건

 

4. default

  - 사용자가 값을 입력하지 않으면 해당 칼럼이 정의한 기본값을 자동으로 입력해준다

 

5. unique

  - 중복을 허용하지 않음

  - null 값은 한 번만 허용

 

6. foreign key

  - 외래키

  - 테이블을 조인하는 경우 부모와 자식 관계를 설정

 

-- sungjuk 테이블 생성
CREATE TABLE sungjuk(
    sno     int          primary key    --기본키(유일성)
    ,uname  varchar(50)  not null
    ,kor    int          check(kor between 0 and 100)   --국어 점수 0~100 사이만 입력 가능
    ,eng    int          check(eng between 0 and 100)   --영어 점수 0~100 사이만 입력 가능
    ,mat    int          check(mat between 0 and 100)   --수학 점수 0~100 사이만 입력 가능
    ,addr   varchar(20)  check(addr in ('Seoul', 'Jeju', 'Busan', 'Suwon'))
    ,tot    int          default 0
    ,aver   int          default 0
    ,wdate  date         default sysdate                --현재 날짜 함수
);

 

제약조건 에러 메시지

primary key 제약조건

INSERT INTO sungjuk(sno, uname) VALUES(1, '홍길동');

위 코드를 두 번 실행했을 때 아래와 같은 에러가 발생한다. sno 칼럼은 기본키(primary key)이므로 중복된 값을 허용하지 않기 때문이다.

 

not null 제약조건

INSERT INTO sungjuk(sno) VALUES(1);

uname 칼럼에 not null을 제약조건으로 걸었기 때문에 에러가 발생한다. 따라서 uname 칼럼에 반드시 값을 입력해야 한다.

 

check 제약조건

INSERT INTO sungjuk(sno, uname, kor, eng, mat)
VALUES (1, '홍길동', -10, 20, 300);

kor, eng, mat에 0에서 100 사이의 값만 가능하다는 check 조건을 걸었기 때문에 -10, 300은 입력될 수 없다.

 

INSERT INTO sungjuk(sno, uname, kor, eng, mat, addr)
VALUES (1, '홍길동', 10, 20, 30, 'Incheon');

addr에는 Seoul, Jeju, Busan 만 올 수 있도록 check 제약조건을 걸었기 때문에 마찬가지로 에러가 난다.

 

default 제약조건

INSERT INTO sungjuk (sno, uname, kor, eng, mat, addr)
VALUES (2, '무궁화', 10, 20, 30, 'Seoul');

tot와 aver은 0 , wdate는 현재 날짜가 입력된다.

 

 

distinct

칼럼에 중복 내용이 있으면 대푯값 1개만 출력

distinct 칼럼명

SELECT DISTINCT(addr) FROM sungjuk;

distinct 전

distinct 후

 

group by

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

GROUP BY 칼럼1, 칼럼2, 칼럼3 ...

--주소가 동일한 값을 그룹화시키고 주소를 조회
SELECT addr
FROM sungjuk
GROUP BY addr;   --distinct와 결과값 동일

-- ORA-00979: GROUP BY 표현식이 아닙니다.
SELECT addr, uname   -- 그룹시키고 나올 수 있는 값은 1개만 가능한 값만 조회
FROM sungjuk
GROUP BY addr;

addr은 동일 내용을 하나로 그룹화하여 1개의 값만 조회할 수 있지만 uname은 동일 addr 안에서도 값이 각각 다르기 때문에 조회가 불가능하다.

 

--문1) 주소별 인원수를 조회하시오
SELECT addr, count(*)
FROM sungjuk
GROUP BY addr;

 

--주소별 오름차순 정렬해서 조회
SELECT addr
FROM sungjuk
GROUP BY addr
ORDER BY addr;

--주소별 인원수를 내림차순 정렬해서 조회
SELECT addr, count(*)
FROM sungjuk
GROUP BY addr
ORDER BY count(*) desc;

SELECT addr, count(*) AS cnt     --해석 순서3)
FROM sungjuk                     --해석 순서1)
GROUP BY addr                    --해석 순서2)
ORDER BY cnt desc;               --해석 순서4)

 

 

 

집계 함수(그룹 함수)

--문2) 주소별 국어점수에 대해서 집계하시오
SELECT addr, COUNT(*), MAX(kor), MIN(kor), SUM(kor), AVG(kor)  --갯수, 최대값, 최소값, 합계, 평균
FROM sungjuk
GROUP BY addr;

 

-- 주소 순으로 정렬
SELECT addr, COUNT(*), MAX(kor), MIN(kor), SUM(kor), AVG(kor)
FROM sungjuk
GROUP BY addr
ORDER BY addr;

 

--round(값, 0) 소수점 이하 값에서 반올림하고 소수점은 없음
SELECT addr, COUNT(*), MAX(kor), MIN(kor), SUM(kor), ROUND(AVG(kor), 0)
FROM sungjuk
GROUP BY addr
ORDER BY addr;

 

--국어 평균을 소수점없이 반올림하고 내림차순 정렬해서 조회
SELECT addr, COUNT(*), MAX(kor), MIN(kor), SUM(kor), ROUND(AVG(kor), 0)
FROM sungjuk
GROUP BY addr
ORDER BY ROUND(AVG(kor), 0) DESC;

--칼럼명 부여
SELECT addr
    , COUNT(*) AS cnt
    , MAX(kor) AS max_kor
    , MIN(kor) AS min_kor
    , SUM(kor) AS sum_kor
    , ROUND(AVG(kor), 0) AS avg_kor
FROM sungjuk
GROUP BY addr
ORDER BY ROUND(AVG(kor), 0) DESC;