TIL 3 | Oracle SQL

복기
rowid는 식별 할 수 있는 위치값
그루핑할때는 셀렉트문에 따라 제약되는 것이 있다.
그룹바이 그룹핑할 컬럼명이 오는데 여러개 올 수가 있고
셀렉트 구문에 올 수 있는 건 그룹함수와 그룹대상이 되는 것
쿼리를 수행하다보면 단일행함수 복수행함수가 있다.
그룹핑한 결과에 대해 쓸거니까 서브쿼리 활용
서브쿼리는 괄호로 감싸기.
서브쿼리가 먼저 수행한 결과를 외부커리에 반환해줌.
서브쿼리는 셀렉트/ 프롬/ 웨어절에 올 수 있다. 웨어절은 주로 조건을 비교할 때 온다.

• sql 구문 대소문자 구분하지 않음
  - 대소문자 구분함 : "keyName" => 대문자 자동변환되지 않음
  - db에서 자동으로 모두 대문자 처리해주기 때문에 대소문자를 구분하지 않는다고 하는 것
  - 데이터는 대소문자 구분함
  - 'MANAGER', 'manager', 'Manager'은 각각 다른 것.
  - upper(), lower() 대소문자 바꾸는거

• 컬럼명에 대한 alias(별명)
  - 대소문자구분, 공백존재, 특수문자가 존재하면 : ""로 감싸주기

---

테스트 테이블 : 대소문자 구분한 테이블 생성됨
create table "test" (
data1 varchar2(10),
"data2" varchar2(10)
) ;

조회 : desc "test";
삭제 : drop table "test";

table 변경
Database Modeling tools
--ER-Win : 상용
--EXErd : 국내, 상용, 1달간 무료사용

제약(constaint)

1. 식별키 (PK: primary key)

   • 단일컬럼: 컬럼레벨, 테이블 레벨
   • 다중 컬럼 : 테이블 레벨

2. 필수 : not null

   • 컬럼 레벨만 지정 가능

3. 중복불가 : unique

4. 참조키 : FK(Foreign key)

5. 기본값 : default

6. 검증: check

   부가적인 제약 : 테이블명, 컬럼명, 순서, 타입, 길이

제약 지정방법

1. 컬럼 레벨 : not null : 컬럼레벨만 가능
   create table 테이블명 (
   컬럼명 타입(길이) 제약지정,
   컬럼명 타입(길이) 제약지정,
   ) ;

   -- create table : 식별키 memeber_id 단일컬럼
   create table MEMBER (
   MEMBER_ID VARCHAR2(30) primary key,
   MEMBER_PW varchar2(20) not null,
   NAME VARCHAR2(20) not null,
   MOBILE VARCHAR2(13) not null,
   email varchar2(30) not null,
   entry_date varchar2(10) not null,
   grade varchar2(1) not null,
   mileage number(6),
   manager varchar2(20)
   ) ;

   -- create table : 식별키 memeber_id, name 다중컬럼
   -- error : 컬럼레벨로 primary key는 단일컬럼에만 적용 가능
   create table MEMBER (
   MEMBER_ID VARCHAR2(30) primary key,
   MEMBER_PW varchar2(20) not null,
   NAME VARCHAR2(20) primary key,
   MOBILE VARCHAR2(13) not null,
   email varchar2(30) not null,
   entry_date varchar2(10) not null,
   grade varchar2(1) not null,
   mileage number(6),
   manager varchar2(20)
   ) ;

2. 테이블 레벨
   create table 테이블명 (
   컬럼명 타입(길이) 제약지정,
   컬럼명 타입(길이) 제약지정,
   constraint 제약명 제약지정 expr,
   constraint 제약명 제약지정 expr,
   ) ;

   -- 테이블레벨로 다중컬럼에 대한 식별키 제약 지정
   create table MEMBER (
   MEMBER_ID VARCHAR2(30),
   MEMBER_PW varchar2(20) not null,
   NAME VARCHAR2(20),
   MOBILE VARCHAR2(13) not null,
   email varchar2(30) not null,
   entry_date varchar2(10) not null,
   grade varchar2(1) not null,
   mileage number(6),
   manager varchar2(20),
   CONSTRAINT PK_MEMBER_ID_NAME PRIMARY KEY (MEMBER_ID, NAME)
   ) ;

   -- 테이블레벨로 단일컬럼에 대한 식별키 제약 지정
   create table MEMBER (
   MEMBER_ID VARCHAR2(30),
   MEMBER_PW varchar2(20) not null,
   NAME VARCHAR2(20),
   MOBILE VARCHAR2(13) not null,
   email varchar2(30) not null,
   entry_date varchar2(10) not null,
   grade varchar2(1) not null,
   mileage number(6),
   manager varchar2(20),
   CONSTRAINT PK_MEMBER_ID PRIMARY KEY (MEMBER_ID)
   ) ;

   제약 지정 방법 :

   1) 제약명 지정하지 않고 제약 지정방법
   오라클에서는 자동으로 sys_xxx 제약명 부여

   2) 제약명을 명시적으로 지정하고 제약 지정방법
   PK_테이블명_컬럼명
FK_테이블명_컬럼명

   DATA DICTIONARY

• 데이터베이스에 있는 데이터를 관리하기 위한 부가정보를 갖는 테이블
  = 메타데이터 (데이터를 표현하는 데이타, 데이터의 데이터)
  - USER_XXX : 사용자가 사용
  - DBA_XXX : 관리자 사용
  - ALL_XX : 사용자와 관리자 모두 사용
• 제약관련 data dictionary table
  - user_constraints
  - user_cons_columns
  `

  • desc user_constraints; // 어떤 제약이 있는지 조회
    CONSTRAINT_NAME NOT NULL VARCHAR2(30)
    CONSTRAINT_TYPE VARCHAR2(1)
    TABLE_NAME NOT NULL VARCHAR2(30)

  • desc user_cons_columns; // 어떤제약이 어떤 컬럼에 부여됐는지 조회하는거
    CONSTRAINT_NAME NOT NULL VARCHAR2(30)
    TABLE_NAME NOT NULL VARCHAR2(30)
    COLUMN_NAME VARCHAR2(4000)

  -- 테이블에 대한 제약 조회
  select table_name, constraint_name, constraint_type
  from user_constraints
  where table_name in ('MEMBER');

  -- user_cons_columns 테이블 이용해서
  -- MEMBER 테이블의 PK_MEMBER_ID_NAME 제약에 대한 컬럼 조회
  select table_name, constraint_name, column_name
  from user_cons_columns
  where table_name ='MEMBER' and constraint_name = 'PK_MEMBER_ID_NAME';

게시글 테이블 : notice

• 도메인 속성, 컬럼 순서

1. 게시글 번호: PK
2. 제목 : 필수
3. 내용 : 선택
4. 작성자아이디(회원 아이디) : 필수(참조키)
5. 작성날짜 : 필수, 현재날짜
6. 조회수 : 선택

게시글 테이블 작성하기
-- 주의사항 : 테이블명, 컬럼명, 제약명 지정시에 키워드(예약어) 사용불가
create table NOTICE (
notice_no number(8),
title varchar2(30) not null,
contents varchar2(500),
MEMBER_ID varchar2(30),
write_date date not null,
hit_count number(10),
CONSTRAINT PK_NOTICE_notice_no PRIMARY KEY(notice_no),
CONSTRAINT FK_MEMBERID foreign key (MEMBER_ID) references member(member_id)
);

속성 종류(분류)

1. 기본 속성(고유 속성)
   • 주민번호(식별키가능-대체키), 이름, 휴대폰(식별키가능-대체키), 이메일(식별키가능-대체키)
2. 유추 속성
   • 주민번호 => 나이, 성별, 출생지 등
3. 설계 속성 (회원이 원래는 갖고 있지 않는건데 설계를 위해 부가적으로 만드는 속성)
   - 아이디(식별키), 비밀번호, 등급 ....
   `
   ----아이디가 식별키가 됐으니 기본속성에 있는것들이 대체키가 됨

참조키(foreign key)

1. 부모테이블(Master Table)
2. 자식테이블(Detail Table)

회원테이블(부모-PK) -> 게시글테이블(자식-FK)
부서테이블(부모-PK) -> 직원테이블(자식-FK)

• 식별관계
  - 자식테이블에서 FK(참조키)를 식별키로 사용

• 비식별관계
  - 자식테이블에서 FK(참조키)를 일반속성으로 사용

  PK = unique + not null
FK = 부모테이블에 존재함, null 허용

• 부모테이블과 자식테이블로 분리 설계한 것을
  - 조인(join)을 통해 여러개의 분리된 테이블을 합쳐서 조회 가능

• 테이블생성
  - 부모 => 자식

• 테이블삭제

  • 자식 => 부모
    drop table 자식테이블명;
    drop table 부모테이블명;
  • 부모 (자식관계를 함께 삭제, 자식 유지)
  • 회사폐업 : 판매물건에 대한 세금은 납부, 회원(삭제), 판매(유지->판매에 대한 세금납부)

• recyclebin : 완선삭제(삭제테이블 복구불가)

• drop table 자식테이블명 purge;

• recyclebin : 삭제테이블 휴지통 비우기

• purge recyclebin;

참조키 제약 지정방법

1. 컬럼레벨
   create table 테이블명 (
   컬럼명 타입(길이) 제약지정,
   fk컬럼명 타입(길이) references 부모테이블명(부모테이블식별키)
   ) ;
2. 테이블 레벨
   create table 테이블명 (
   컬럼명 타입(길이) 제약지정,
   컬럼명 타입(길이) 제약지정,
   constraint 제약명 제약지정 expr,
   constraint FK_MEMBERID foreign key(fk컬럼명) references 부모테이블명(부모테이블식별키)
   ) ;

• 게시글 컬럼레벨 참조키 지정
  create table NOTICE (
  notice_no number(8),
  title varchar2(30) not null,
  contents varchar2(500),
  MEMBER_ID varchar2(30) references MEMBER(MEMBER_ID),
  write_date date not null,
  hit_count number(10),
  CONSTRAINT PK_NOTICE_notice_no PRIMARY KEY(notice_no)
  );

• 게시글 테이블레벨 참조키 지정
  create table NOTICE (
  notice_no number(8),
  title varchar2(30) not null,
  contents varchar2(500),
  MEMBER_ID varchar2(30),
  write_date date not null,
  hit_count number(10),
  CONSTRAINT PK_NOTICE_notice_no PRIMARY KEY(notice_no),
  constraint FK_MEMBERID foreign key(MEMBER_ID) references member(member_id),
  );

테이블 변경

• 형식 : alter table 테이블명...
• 컬럼추가
• 컬럼삭제
• 컬럼변경 : 타입, 길이, 데이터 존재유무 제약
  `
• 제약추가 형식:
  alter table 테이블명
  add CONSTRAINT 제약명 제약타입 expr;
• 테이블 구조와 제약 분리 :
  - 가독성
  • modeling tools : 자동으로 script file 만들어줌

DML (데이터 조작어)

• 레코드 : C(추가) R(조회) U(변경) D(삭제)
  1. 레코드추가
  - 단일행 추가
  1. 지정한컬럼에 대해서 값을 지정추가, NOT NULL 컬럼은 모두 포함되어있어야함
  INSERT INTO 테이블명(컬럼명1, 컬럼명X) VALUES(값1, 값X) 2. 테이블구조(스키마) 순서대로 값을 지정 추가
  INSERT INTO 테이블명 VALUES(값1, 값2, 값X)
  • 다중행 추가 : 테스트 데이터, sample 데이터
    insert into 테이블명(컬럼명1, 컬럼명x) select 컬럼명1, 컬럼명x ;
• 레코드변경

1. 지정한 컬럼에 대해서 모든 레코드의 값을 변경
   UPDATE 테이블명 SET 컬럼명1=변경값 , 컬럼명N=값;

2. 지정한 조건을 만족하는 레코드의 컬럼에 대해서 값을 변경
   UPDATE 테이블명 SET 컬럼명1=변경값 , 컬럼명N=값
   WHERE 조건구문;

• 레코드 삭제

1. 지정한 컬럼에 대해서 모든 레코드를 삭제 : 복구가능
   DELETE 테이블명;
   DELETE FROM 테이블명;

2. 지정한 조건을 만족하는 레코드만 삭제 : 복구가능
   DELETE 테이블명 WHERE 조건구문;

3. 레코드 삭제 : 복구 불가능
   -- DDL : 자동 commit
   -- TRUNCATE TABLE 테이블명
   -- 복구 불가능 => 영구적 삭제처리 commit 수행
   -- WHERE 구문 사용 불가
   (IO가 발생하지 않아 삭제하는 시간이 빠름)

SEQUENCE 객체

• 일련번호를 자동 제공하는 데이터베이스 공유객체 (번호표같은거)
• 테이블에 종속적이지는 않지만, 보통은 특정 테이블의 특정 컬럼을 대상으로 설계해서 사용

1. 생성 : 기본값 생성, 시작은 1, 1씩 자동증가
   CREATE SEQUENCE 시퀀스명;
   CREATE SEQUENCE SEQ_NO;
   • 일련번호 추출: 시퀀스명.NEXTVAL;
   • 현재 일련번호 조회 : 시퀀스명.CURRVAL

1.2 시퀀스 속성 설정 생성

• 시퀀스명 : SEQ_테이블명_컬럼명

  CREATE SEQUENCE 시퀀스명
  START WITH 시작값
  INCREMENT BY 증감값
  MAXVALUE 최대값 (아니면 NOMAXVALUE)
  MINVALUE 최소값 (아니면 NOMINVALUE)
  CYCLE | NOCYCLE
  CACHE 크기 | NOCACHE
  (이거는 구분자 없음. 공백으로 구분)
  

2. 삭제
   DROP SEQUENCE 시퀀스명;
   DROP SEQUENCE SEQ_NO;

3. 변경

-- 부서테이블에 부서번호 컬럼에 사용하기 위한 시퀀스 객체 생성
-- 그럼 시퀀스의 시작번호는 몇 번 - 10
-- 시퀀스 증가값 - 10
-- 최대값 - 90
-- 최대가 됐을때 반복 여부 - nocycle

테이블 생성해서, 직원테이블 10번 부서원들의 이름, 직무 정보를 레코드 추가
1. TEST 테이블 : 신규 테이블로 만들기
- USER_NAME => ENAME VARCHAR2(14)
- USER_JOB => JOB VARCHAR2(10)

table 생성

1. 새로운 테이블 생성
   create table 테이블명(
   컬럼명 타입(길이) [제약],
   ...
   );

2. 기존 테이블 구조 참조해서 생성 :

   • SELECT 조건식이 거짓이 되도록

   • 테이블제약 NOT NULL 제약만 가져옴

   • 복사해서 생성한 테이블에 별도로 필요한 제약추가 해야 함

     create table 테이블명
     as
     select 구문
     WHERE 1 = 2
     ;

3. 기존 테이블 구조 + 데이터 참조해서 생성

   • 백업용, 보안이슈
     create table 테이블명
     as
     select 구문
     ;

10번 부서원들의 부서번호, 사번, 이름, 급여 정보 조회수
select deptno, empno, ename, sal from emp where deptno = 10;

10번 부서원들의 정보 및 구조를 갖는 테이블 생성 : EMP_10
--동일한 컬럼명 사용
CREATE TABLE EMP_10
AS
select deptno, empno, ename, sal from emp where deptno = 10;

-- 컬럼명 다르게 지정 사용
CREATE TABLE TEST_SAMPLE(NO, SID, NAME, MILEAGE)
AS
select deptno, empno, ename, sal from emp where deptno = 10;

직원테이블의 구조만을 참조해서 테이블 생성
CREATE TABLE NEW_EMP
AS
SELECT * FROM EMP
WHERE 1 = 2;