Oracle SQL(2)

✅Create

sequence

• 자동으로 일련번호 부여
• 시퀀스 생성: CREAT SEQUENCE [시퀀스명];
• 시퀀스 사용: [시퀀스명].nextval → value값에 입력
• 시퀀스 삭제: DELETE SEQUENCE [시퀀스명];

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

-- 시퀀스와 sysdate함수를 이용한 행 추가
insert into sungjuk(sno, uname, wdate)
values(sungjuk_seq.nextval, '홍길동', sysdate);

 

✅Read

데이터 조회의 제한, 데이터 조회 방법

⭐키워드 오는 순서 알고있기

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

 

between ~ and ~ (AND)

BETWEEN [조건1] AND [조건2]

-- 국어점수가 80 ~ 89점 사이 행을 조회하시오
select * from sungjuk where korea between 80 and 89;

 

in() (OR)

IN( [조건1], [조건2], …)

-- 이름이 '무궁화', '봉선화'를 조회하시오
select * from sungjuk where uname in ('무궁화', '봉선화');

 

like

LIKE [조회할 문자]

• 문자열 데이터에서 비슷한 유형을 검색할 때
• 문자열만 가능
• % : 글자갯수와 상관이 없음
• _ : 글자갯수까지 일치해야 함

-- 이름에서 '홍'으로 시작하는 이름을 조회하시오
select * from sungjuk where uname like '홍%';

-- 이름에서 '화'로 끝나는 이름을 조회하시오
select * from sungjuk where uname like '%화';

-- ⭐이름에 '나' 글자가 있는 이름을 조회하시오
select * from sungjuk where uname like '%나%';

-- 세글자 이름에서 '동'으로 끝나는 이름을 조회하시오
select * from sungjuk where uname like '__동';

 

order by (asc or desc)

ORDER BY [칼럼명] ASD / DESC

• 특정값(keyfield)을 기준으로 순서대로 재배치
• 키워드 생략하면 기본 오름차순 정렬
• 오름차순 : Ascending Sort (ASC)
• 내림차순 : Descending Sort (DESC)

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

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

 

distinct

DESTINCT [칼럼명]
칼럼에 중복 내용이 있으면 대표값 1개만 출력

select distinct(addr) from sungjuk;

왼쪽 테이블 전체 값 / 오른쪽 distinct로 중복값 제거된 쿼리

 

group by

[참고] https://extbrain.tistory.com/56
select [그룹컬럼], [집계함수] from [테이블명] group by [그룹컬럼];

SELECT type, COUNT(name) AS cnt FROM hero_collection GROUP BY type;

--hero_collection 테이블에서 type 컬럼을 기준으로 그룹핑한 결과를 보여주는 쿼리

[예제 테이블]

idx	type	name
1	1	안중근
2	1	윤봉길
3	2	김유신
4	2	이순신
5	3	이성계
6	3	왕건
7	4	반갑수

[결과]

type	cnt
1	2
2	2
3	2
4	1

 

✅테이블 제약조건

• 데이터 무결성을 위해 강제성을 테이블의 칼럼에 부여함
• 효율적인 데이터관리에 중요한 역할을 함

 

primary key

• 테이블의 내용에서 각각의 레코드를 대표하는 유일한 컬럼
• where 조건절에 걸릴 수 있는 대표적인 컬럼을 미리 강제함
• 테이블당 한개씩 반드시 존재해야함
• 값이 무조건 존재해야함 (not null)

create table sungjuk (
  sno int primary key
);
insert into sungjuk(sno) values(1); --두번 실행했을 경우

-- ORA-00001: 무결성 제약 조건(SYSTEM.SYS_C007314)에 위배됩니다
-- primary key 로 인해 에러 발생함

 

not null

빈값(null)을 허용하지 않음을 의미

create table sungjuk (
  sno    int         primary key
 ,uname  varchar(10) not null
);
insert into sungjuk(sno) values(2);

-- ORA-01400: NULL을 ("SYSTEM"."SUNGJUK"."UNAME") 안에 삽입할 수 없습니다
-- uname 칼럼은 not null 이므로 반드시 값을 입력해야 함

 

varchar(제한할 문자byte 수)

create table sungjuk (
  sno    int         primary key
 ,uname  varchar(10) not null
);
insert into sungjuk(sno, uname) values(4, 'ABCDEFGHIJK');
insert into sungjuk(sno, uname) values(5, '대한민국');

-- ORA-12899: "SYSTEM"."SUNGJUK"."UNAME" 열에 대한 값이 너무 큼(실제: 11, 최대값: 10)
-- 문자제한 갯수가 넘어가는 문자를 입력하여 오류 발생함

 

default

• 사용자가 값을 넣지 않았을경우 기본값으로 사용할 값을 설정함
• insert 되는 시점에 자동으로 default 값이 들어감

create table sungjuk (
  uname  varchar(50) not null
 ,tot    int         default 0
 ,aver   int         default 0
 ,wdate  date        default sysdate
);
insert into sungjuk(uname)
values('홍길동');

-- tot와 aver는 0 입력, wdate는 현재 날짜가 입력됨

 

check

• 해당 컬럼에 들어갈 수 있는 값의 범위를 제한함
• 조건을 만족하지 않은 값은 들어가지 않음

create table sungjuk (
  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(50) check(addr in ('Seoul', 'Jeju', 'Busan', 'Suwon'))
);
insert into sungjuk(kor, eng, mat)
values(-10, 20, 300);
insert into sungjuk(kor, eng, mat, addr)
values(10, 20, 30, 'Incheon')

-- ORA-02290: 체크 제약조건(SYSTEM.SYS_C007323)이 위배되었습니다
-- 조건을 만족하지 않은 값을 입력하여 오류 발생함

 

unique

• primary key 기능을 대체할 수 있음
• 해당 컬럼에 동일한 값이 들어가지 않도록 함
• null값 허용 (primary key와 차이점)

 

foreign key

• 다른 테이블의 primary key를 참조하여 사용하는 테이블의 컬럼
• 참조되는 테이블의 primary key 값을 그대로 사용함
• 다른 테이블의 primary key 값의 변경 및 삭제가 불가능하도록 제약함