2022_11_16 SQL 정리
--제약조건
--테이블의 컬럼에 대한 조건 ( 원치않는 데이터가 입력, 수정, 삭제가 되는것을 방지)
제약조건 유형 :
NOT NULL
UNIQUE KEY -- (유일한 값) NULL 가능 / 중복 X
PRIMARY KEY --NOT NULL / 중복X
FOREIGN KEY --NULL 가능 / 중복 가능 (PK에 지정된값만 가능)
CHECK -- 지정된 값일때만 데이터를 입력하게 해줌
일반적으로 테이블 생성할때 제약조건을 열레벨에서 설정하는걸 자주씀
--------------------------------------------------------
------------ 테이블 생성과 제약조건
--primary key(테이블 고유키, 중복x, null허용 x)
--unique (중복x)
--not null(null을 허용하지 않음)
--foreign key(참조하는 테이블의 pk를 저장하는 컬럼 , 참조테이블의 pk에 없다면 등록x, null허용)
--check (정의된 형식만 저장하도록 허용)
--------------------------------------------------------
SELECT * FROM user_constraints;
CREATE TABLE dept2 (
dept_no NUMBER(2) CONSTRAINT dept2_no_pk PRIMARY KEY,
dept_name VARCHAR2(15) CONSTRAINT dept2_name_nn NOT NULL,
loca NUMBER(4) CONSTRAINT dept2_loca_locaid_fk REFERENCES locations(location_id),
dept_date DATE DEFAULT sysdate,
dept_bonus NUMBER(10) DEFAULT 0,
dept_phone VARCHAR2(20) CONSTRAINT dept2_phone_uk UNIQUE,
dept_gender CHAR(1) CONSTRAINT dept2_gender_ck CHECK (dept_gender IN ('M', 'F'))
);
--열레벨 제약(CONSTRAINTS 생략가능)
DROP TABLE DEPT2;
CREATE TABLE dept2 (
dept_no NUMBER(2) PRIMARY KEY,
dept_name VARCHAR2(15) NOT NULL,
loca NUMBER(4) REFERENCES locations(location_id),
dept_date DATE DEFAULT sysdate,
dept_bonus NUMBER(10) DEFAULT 0,
dept_phone VARCHAR2(20) UNIQUE,
dept_gender CHAR(1) CHECK (dept_gender IN ('M', 'F'))
);
DROP TABLE DEPT2;
--테이블레벨(NOTNULL만 열레벨로사용)
CREATE TABLE dept2 (
dept_no NUMBER(2) ,
dept_name VARCHAR2(15) NOT NULL,
loca NUMBER(4) ,
dept_date DATE DEFAULT sysdate,
dept_bonus NUMBER(10) DEFAULT 0,
dept_phone VARCHAR2(20) ,
dept_gender CHAR(1) ,
CONSTRAINT dept2_no_pk PRIMARY KEY (dept_no /*, dept_name7*/), --슈퍼키
CONSTRAINT dept2_loca_locaid_fk FOREIGN KEY (loca) REFERENCES locations(location_id),
CONSTRAINT dept2_phone_uk UNIQUE (dept_phone),
CONSTRAINT dept2_gender_ck CHECK (dept_gender IN ('M', 'F'))
);
-------------------------------제약조건의 위배
DESC EMPLOYEES;
--개체 무결성 위배 (NULL, 중복이 PK에 들어가지 못함)
INSERT INTO EMPLOYEES (employee_id, last_name, email, hire_date, job_id)
VALUES (100,'test','test',sysdate,'test');
--참조 무결성 위배 (참조하는 테이블의 PK로 존재해야 FK에 들어갈수 있음)
INSERT INTO EMPLOYEES (employee_id, last_name, email, hire_date, job_id, department_id)
VALUES (501, 'test','test', sysdate, 'test', 5);
--도메인 무결성 위배 ( 컬럼에 정의된 값만 들어갈수 있음)
INSERT INTO EMPLOYEES (employee_id, last_name, email, hire_date, job_id, salary)
VALUES (501, 'test','test', sysdate, 'test', -10);
---------------------------------------------------------
--제약조건 추가, 삭제 (변경불가)
DROP TABLE dept2;
--제약조건이 없는 테이블 생성
CREATE TABLE dept2 (
dept_no NUMBER(2) ,
dept_name VARCHAR2(15) ,
loca NUMBER(4) ,
dept_date DATE DEFAULT sysdate,
dept_bonus NUMBER(10) DEFAULT 0,
dept_phone VARCHAR2(20) ,
dept_gender CHAR(1)
);
--PK추가
ALTER TABLE dept2 ADD CONSTRAINTS dept_no_pk PRIMARY KEY (dept_no);
--FK추가
ALTER TABLE dept2 ADD CONSTRAINTS dept_loca_fk FOREIGN KEY (loca) REFERENCES locations (location_id);
--UNIQUE 추가
ALTER TABLE dept2 ADD CONSTRAINTS dept_phone_uk UNIQUE (dept_phone);
--CHECK 추가
ALTER TABLE dept2 ADD CONSTRAINTS dept_gender_ck CHECK ( dept_gender IN ( 'M' , 'F' ) );
--NOT NULL
ALTER TABLE dept2 MODIFY dept_name VARCHAR2(15) NOT NULL;
--제약조건 삭제
ALTER TABLE dept2 DROP CONSTRAINTS dept_loca_fk;
DROP TABLE MEM;
--다음과 같은 mem테이블을 생성하고 데이터를 insert하세요 (커밋)
--조건) M_NAME 는 가변문자형, 널값을 허용하지 않음
--조건) M_NUM 은 숫자형, 이름(mem_memnum_pk) primary key
--조건) REG_DATE 는 날짜형, 널값을 허용하지 않음, 이름:(mem_regdate_uk) UNIQUE키
--조건) GENDER 가변문자형
--조건) LOCA 숫자형, 이름:(mem_loca_loc_locid_fk) foreign key ? 참조 locations테이블(location_id)
---테이블 생성
CREATE TABLE mem(
m_name VARCHAR2(5) NOT NULL,
m_num NUMBER(2) CONSTRAINT mem_mumnum_pk PRIMARY KEY,
reg_date DATE CONSTRAINT mem_regdate_uk UNIQUE,
gender CHAR(1),
loca NUMBER(5) CONSTRAINT mem_loca_loc_locid_fk REFERENCES locations(location_id)
);
ALTER TABLE mem MODIFY m_name VARCHAR(20);
INSERT INTO MEM (m_name, m_num, reg_date, gender, loca)
VALUES('AAA',1, '2018-07-01','M',1800);
INSERT INTO MEM (m_name, m_num, reg_date, gender, loca)
VALUES('BBB',2, '2018-07-02','F',1900);
INSERT INTO MEM (m_name, m_num, reg_date, gender, loca)
VALUES('CCC',3, '2018-07-03','M',2000);
INSERT INTO MEM (m_name, m_num, reg_date, gender, loca)
VALUES('DDD',4, SYSDATE,'M',2000);
SELECT * FROM MEM;
COMMIT;
--문제 2.
--MEMBERS테이블과 LOCATIONS테이블을 INNER JOIN 하고 m_name, m_mum, street_address, location_id
--컬럼만 조회
--m_num기준으로 오름차순 조회
SELECT m.m_name, m_num, l.street_address, l.location_id
FROM mem m
JOIN locations l ON m.loca = l.location_id
ORDER BY m_num ASC;
---------------------VIEW(뷰)
뷰 = 다른 테이블을 기초로 하는 가상테이블
데이터 관리를 간단하게 해줌(필요한 데이터만 뽑아서 뷰로 확인함)
단순뷰 - 한개의 원본테이블에서 몇몇 컬럼만 빼옴
복합뷰 - 두개이상의 테이블에 의해 생성(조인을 통해)
뷰는 편하게 데이터를 확인하기 위해서 생성함
CREATE or REPLACE VIEW 뷰이름 AS 서브쿼리로 필요한 내용;
--VIEW는 제한적인 자료만 보기위해 사용할 수 있는 가상테이블의 개념이다
--뷰는 기본테이블로 유도된 가상테이블 이기 때문에 필요한 컬럼만 저장해두면 관리가 편해진다
--뷰는 가상테이블로 실제 데이터가 물리적으로 저장된 형태는 아니다
--뷰를 통해서 데이터에 접근하면 원본데이터는 안전하게 보호할 수 있다.
select* from emp_details_view;
--단순뷰
--뷰의 컬럼 이름은 함수같은 가상표현식이면 안된다
CREATE OR REPLACE VIEW view_emp
AS (SELECT employee_id,
first_name || ' ' || last_name AS NAME,
job_id,
salary
FROM employees
WHERE department_id = 60);
SELECT * FROM VIEW_EMP;
--복합뷰
--여러테이블을 조인하여 필요한 데이터만 저장하고 빠른 확인을 위해서 사용
CREATE OR REPLACE VIEW view_emp_dept_job
AS (SELECT
e.employee_id,
first_name || ' ' ||last_name as name,
d.department_name,
j.job_title
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
LEFT OUTER JOIN jobs j
ON e.job_id = j.job_id)
ORDER BY employee_id;
SELECT * FROM view_emp_dept_job;
--뷰의 수정 (동일이름로 만들면 수정된다)
CREATE OR REPLACE VIEW view_emp_dept_job
AS (SELECT
e.employee_id,
first_name || ' ' ||last_name as name,
e.salary, --추가
d.department_name,
j.job_title
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
LEFT OUTER JOIN jobs j
ON e.job_id = j.job_id)
ORDER BY employee_id;
--뷰를 이용 하면 데이터를 손쉽게 조회 가능
select j.job_title,
(SELECT AVG(salary) FROM employees e where j.job_id = e.job_id) as sal
from jobs j
order by sal;
---------------------------------------똑같음
select j.job_title,
AVG(salary) as sal
from jobs j
left join employees e
on j.job_id = e.job_id
group by job_title
order by sal;
---------------------------------------똑같음
select job_title, avg(salary) as 평균
from (select *
from employees e
left join jobs j on e.job_id = j.job_id)
group by job_title
order by 평균;
--------------------------뷰를 쓰면 더 편해짐
select job_title, avg(salary) as 평균
from view_emp_dept_job
group by job_title
order by 평균;
--뷰의 삭제
DROP VIEW 뷰이름;
--------------------------------------------------------------------------------
DESC view_emp_dept_job ;
---뷰를 통한 DML은 제한이 많다.
--가상열 컬럼이 있다면 허용되지 않음
INSERT INTO view_emp_dept_job ( name, employee_id ) VALUES ('XXX', 300);
--원본테이블의 데이터가 null을 허용하지 않는 경우도 안된다
INSERT INTO view_emp_dept_job ( employee_id, salary) VALUES (300,10000);
--join된 뷰의 경우도 허용되지 않는다.
INSERT INTO view_emp_dept_job (employee_id, job_title) VALUES (300, 'xxx');
--------------------------------------------------------------------------------
--뷰의 옵션
--with check option -조건컬럼 제약
create or replace view view_emp_test
as(select employee_id, first_name, department_id
from employees
where department_id IN(60,70,80)
)
with check option;
select * from view_emp_test;
--with read only -읽기 전용 뷰(조회만 가능ㅇ)
create or replace view view_emp_test
as(select employee_id, first_name, department_id
from employees
where department_id IN(60,70,80)
)
with read only;
------------------------------------------------------------
SEQUENCE (순차적으로 증가하는 값)
----
CREATE SEQUENCE 시쿼스 명
INCREENT BY (증가값)
START WITH (시작값)
MAXVALUE (최대값) / NOMAXVALUE
MINVALUE (최소값) / NOMIVALUE (최소값)
CYCLE/NOCYCLE (MAX값에 도착하면 처음으로 가냐의 여부) NO가 보편적
CACHE/NOCACHE (값을 가까운곳에 저장할것인가 아닌가의 여부) NO가 보편적
----
NEXTVAL - 다음 사용 가능한 시퀀스값을 리턴
CURRVAL - 현재 시퀀스 값을 리턴함
새로운 데이터를 삽입할때 시퀀스명.NEXTVAL로 중복없이 PK를 증가시킬수있음
--시퀀스 ( 순차적으로 증가하는 값_ PK에 많이 사용됨 )
SELECT * FROM USER_SEQUENCES;
--테이블 생성
CREATE TABLE DEPT3(
DEPT_NO NUMBER(2) PRIMARY KEY,
DEPT_NAME VARCHAR2(20),
LOCA VARCHAR2(20),
DEPT_DATE DATE
);
--시퀀스 삭제
DROP SEQUENCE DEPT3_SEQ;
--시퀀스 생성
CREATE SEQUENCE DEPT3_SEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 10
MINVALUE 1
NOCYCLE
NOCACHE
;
--시퀀스 사용 CURRVAL, NEXVAL
SELECT DEPT3_SEQ.CURRVAL FROM DUAL; --CURRVAL는 NEXTVAL한번 이후부터 사용가능
SELECT DEPT3_SEQ.NEXTVAL FROM DUAL;
INSERT INTO DEPT3(DEPT_NO, DEPT_NAME, LOCA, DEPT_DATE)
VALUES (DEPT3_SEQ.CURRVAL, 'TEST','TEST',SYSDATE);
SELECT * FROM DEPT3;
--옵션이 없는 시퀀스 생성(기본시퀀스) (기본으로하면 CACHE를 할당하기때문에 NOCACHE로 설정하는게 좋음)
CREATE SEQUENCE DEPT3_SEQ NOCACHE;
-- 시퀀스 수정
ALTER SEQUENCE DEPT3_SEQ NOCACHE;
ALTER SEQUENCE DEPT3_SEQ MAXVALUE 1000;
ALTER SEQUENCE DEPT3_SEQ INCREMENT BY 10;
--시퀀스가 테이블에서 사용되고 있다면 DROP하면 안됨
--시퀀스 값을 내리고싶을때
-- 시퀀스 값을 초기화 하려면
--1 현재 시퀀스 확인
SELECT DEPT4_SEQ.CURRVAL FROM DUAL;
--2 증가값을 -현재시퀀스
ALTER SEQUENCE DEPT4_SEQ MINVALUE 0;
ALTER SEQUENCE DEPT4_SEQ INCREMENT BY -3;
--3 NEXTVAL로 실행
SELECT DEPT4_SEQ.NEXTVAL FROM DUAL;
--4 증가값을 1로 변경
ALTER SEQUENCE DEPT4_SEQ INCREMENT BY 1;
--5 실행
SELECT DEPT4_SEQ.NEXTVAL FROM DUAL;
--------------------------------------------------------------------------------
--시퀀스 사용 응용
CREATE TABLE DEPT4(
DEPT_NO VARCHAR2(30) PRIMARY KEY,
DEPT_NAME VARCHAR2(20)
);
DELETE FROM DEPT4 WHERE DEPT_NAME = 'TEST';
CREATE SEQUENCE DEPT4_SEQ NOCACHE;
--LPAD('값','MAX길이','채울값')를 이용해서 PK에 적용하는 값을 (년월-0000시퀀스) 형태로 INSERT
SELECT * FROM DEPT4;
INSERT INTO DEPT4 VALUES (
CONCAT( 2022 || '년-' , LPAD(DEPT4_SEQ.NEXTVAL, 5, 0 ) ), 'TEST');