INSERT
① 정식 형식 : 컬럼 리스트와 값 리스트는 1:1 대응 ( 데이터형, 관련순서 )
INSERT INTO 테이블명 ( 컬럼 리스트, … )
VALUES ( 값 리스트, … ) ;
② 약식 형식
INSERT INTO 테이블명
VALUES ( 값 리스트, … ) ;
→ 이 경우에는 컬럼을 알 수 없으므로, 테이블 구조(컬럼 구조) 형식으로 값을 입력함
• 대용량 데이터를 삽입할 경우, 트랜잭션 이용 권장
③ 주로 백업용으로 필요한 데이터만 모으는 결과보고 데이터용으로 사용
INSERT INTO 테이블명 ( 컬럼 리스트, … )
SELECT 문장 ( 컬럼 리스트와 일치하는 검색 컬럼 리스트 )
( 참고 사항 )
• 실제 데이터 백업용으로 사용할 때 많이 쓰는 방법은 테이블을 만들 때 SELECT문 사용 가능
⇒ 이 부분은 추후에 배울 예정
현재는 DDL인 CREATE문을 이용해 테이블을 생성
CREATE TABLE 테이블명
SELECT 문장 ;
→ 이 구문을 이용하면, 테이블 구조를 지정해서 만들 필요 없이 SELECT문으로 검색된 데이터들이 자동으로 테이블 구조를 만들고 데이터 추가 가능
1) SELECT문으로 검색 시, 검색결과가 없도록 하면 테이블 구조만 복사하여 생성됨
CREATE TABLE member_backup AS
SELECT * FROM member WHERE mem_id <= -1 ;
위의 경우는 mem_id가 -10부터 시작한다고 가정 시, 데이터 검색 가능성 존재
→ 이럴 때, ROWNUM 필드 이용하자
: SELECT문을 이용해 조회한 데이터에 일련번호를 붙이는 역할
( 테이블이나 특정 집합에서 원하는 만큼의 행만 가져오고 싶을 때, 행의 개수를 제한하는 용도로 사용함 )
❶ 반환되는 쿼리 결과의 임시 행번호이므로, 반드시 1부터 나오게 됨
❷ 중복 허용 불가
CREATE TABLE member_save AS
SELECT * FROM member WHERE ROWNUM < 1 ;
2) SELECT문으로 검색한 결과, 테이블 구조와 함께 같이 복사하게 하는 경우
CREATE TABLE member_copy AS
SELECT * FROM member WHERE mem_name LIKE ‘이%’;
이 구문 실행 시, ‘member_copy’ 테이블의 경우, ‘member’ 테이블과 구조가 동일하며, ‘이’로 시작하는 mem_name의 데이터만 존재함
/*
③ 주로 백업용으로 필요한 데이터만 모으는 결과보고 데이터용으로 사용
INSERT INTO 테이블명 ( 컬럼 리스트, … )
SELECT 문장 ( 컬럼 리스트와 일치하는 검색 컬럼 리스트 )
*/
-- ③ 방식으로 실제 데이터 입력해보기
-- : subway_statistics 테이블의 구조를 복사해서 테이블 생성해보기
-- ( ROWNUM <1의 조건 결과, 데이터가 없는 테이블의 구조만 생성됨 )
CREATE TABLE SUBWAY_STATISTICS_backup AS
SELECT * FROM SUBWAY_STATISTICS ss WHERE ROWNUM < 1 ;
⇒ 원본 테이블과 복사한 테이블 비교
: 복사한 테이블에는 NOT NULL만 복사되고, 기본키 설정은 되어 있지 않음
즉, NULL 값만 아닌 경우, 중복된 데이터가 추가될 수 있으므로, 추가적으로 기본키를 설정해주어야 함
※ 이렇게 생성된 테이블에서는 기본키 제약이 없으므로, 기본키 제약을 추가해주어야 함
- 테이블 구조에 기본키 제약 조건이 추가되는 것이므로 ALTER문 이용
• 형식
ALTER TABLE 테이블명 ADD 제약조건 ;
제약조건 → 속성명 데이터타입 또는 PRIMARY KEY( 컬럼명 )
ALTER TABLE 테이블명 ADD PRIMARY KEY( seq_id ) ;
- 기본키 제약조건을 추가한 후, 복사된 테이블에 데이터 추가
-- 위의 테이블에 검색 조건을 적용한 데이터를 추가해보자
INSERT INTO SUBWAY_STATISTICS_BACKUP ssb
SELECT * FROM SUBWAY_STATISTICS ss WHERE STATION_NAME LIKE '잠실%' ;
-- 데이터 추가 후, 제대로 추가되었는지 확인
SELECT * FROM SUBWAY_STATISTICS_BACKUP ssb ;→ 총 54개의 데이터가 추가됨
- 원본 데이터를 전부 복사해보면, 이미 '잠실'로 시작하는 데이터는 추가되었으므로 무결성 제약조건에 위배됨
→ NOT LIKE 연산자를 이용해 '잠실'를 포함하지 않는 데이터만 다시 추가
-- 원본 테이블의 데이터를 전부 복사해보자 ⇒ 무결성 제약조건 위배
INSERT INTO SUBWAY_STATISTICS_BACKUP ssb
SELECT * FROM SUBWAY_STATISTICS ss;
-- STATION_NAME이 '잠실'을 포함하지 않은 데이터만 추가
INSERT INTO SUBWAY_STATISTICS_BACKUP ssb
SELECT *
FROM SUBWAY_STATISTICS ss
WHERE STATION_NAME NOT LIKE '잠실%' ;
SELECT COUNT(*) FROM SUBWAY_STATISTICS_BACKUP ssb ; -- count(*) : 2142→ 총 2142개의 데이터가 추가됨
- 기본키와 충돌하지 않도록 데이터를 입력하는 방법
→ 기존에 추가된 데이터 패턴을 모두 기억하지 못하는 경우에도 사용 가능
INSERT INTO SUBWAY_STATISTICS_BACKUP ssb
SELECT
ss.*
FROM SUBWAY_STATISTICS ss
WHERE NOT EXISTS (
SELECT 1 -- 컬럼 불필요하므로, 의미없는 1 기입
FROM SUBWAY_STATISTICS_BACKUP ssb
WHERE
ss.SEQ_ID = ssb.SEQ_ID ) ;- EXISTS(서브 쿼리) : 서브 쿼리의 결과가 한 건이라도 존재하면 TRUE, 없으면 FALSE를 리턴
•EXISTS 구문에서는 IN 구문과 달리 메인쿼리에 먼저 접근하여 row 를 하나 가져오고 EXISTS 의 서브쿼리를 실행시켜
결과가 존재하는지를 판단
→ 서브쿼리의 결과가 TRUE 인지 FALSE 인지 체크하기 떄문에 EXISTS 에서는 TRUE일 때, 메인쿼리의 결과를 출력함
•NOT EXISTS : 서브쿼리 내 결과가 FALSE 면 메인쿼리의 결과를 출력함
※ ( 주의 ) 서브쿼리에서 TRUE만 되면, 레코드가 출력되기 때문에, 두 테이블에서 같은 값을 가져오려면 WHERE 절로 조건을 주어야함
DELETE 확장
• 구문
DELETE FROM 테이블명 WHERE 검색조건식
→ WHERE 검색조건식을 SELECT의 검색부분과 같으므로 일종의 확장으로 봄
-- DELETE 확장 사용 예시
DELETE
FROM SUBWAY_STATISTICS_BACKUP ssb
WHERE STATION_NAME LIKE '잠실%';
SELECT count(*) FROM SUBWAY_STATISTICS_BACKUP ssb ; -- 결과 : 2088
UPDATE 확장
• 구문
UPDATE 테이블명
SET 컬럼명1 = 값1,
컬럼명2 = 값2,
…
WHERE 조건 ;
→ 컬럼 = 값 뒤에 이어지는 ‘,’는 나열 연산자이므로, 마지막 컬럼 = 값 뒤에는 붙이지 않음
-- UPDATE 확장 사용 예시
-- 먼저 업데이트를 적용할 내용을 검색하기
-- ( 업데이트할 대상인지 확인할 필요 있음 )
SELECT *
FROM SUBWAY_STATISTICS_BACKUP ssb
WHERE STATION_NAME LIKE '삼성%'
ORDER BY SEQ_ID ; -- 검색 결과 18개의 데이터 출력됨
-- '삼성역'을 검색한 후, 승객 수를 기존값 + 100 더하고,
-- 탑승일(BOARDING_DATE)는 기존일자를 기준으로 한달 뒤로 변경
UPDATE SUBWAY_STATISTICS_BACKUP
SET
PASSENGER_NUMBER = PASSENGER_NUMBER + 100 ,
BOARDING_DATE = BOARDING_DATE + 30
WHERE STATION_NAME LIKE '삼성%';
-- 업데이트된 데이터 조회
SELECT * FROM SUBWAY_STATISTICS_BACKUP ssb
WHERE STATION_NAME LIKE '삼성%';
-- '한 달'이라는 데이터의 경우, 일 수가 정확하지 않음
-- 따라서 날짜 함수 중 ADD_MONTHS(date, n)를 이용해보자
UPDATE SUBWAY_STATISTICS_BACKUP
SET
PASSENGER_NUMBER = PASSENGER_NUMBER + 100,
BOARDING_DATE = ADD_MONTHS(BOARDING_DATE, 1)
WHERE STATION_NAME LIKE '삼성%';
트랜잭션
• 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로, 작업의 단위가 됨
• 하나의 트랜잭션은 COMMIT되거나 ROLLBACK되어야 종료됨
- 트랜잭션의 4가지 특징 ( ACID )
❶ Atomicity ( 원자성 ) : 트랜색션의 수행 결과, 데이터베이스에 모두 반영이 되든지(commit) 또는 전혀 반영되지 않도록(rollback) 되어야 함
❷ Consistency ( 일관성 ) : 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 반환함
❸ Isolation ( 독립성 ) : 둘 이상의 트랜잭션이 동시에 병행 실행되는 경우, 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없음
❹ Durability ( 지속성 ) : 성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 함
JOIN에서 사용할 테이블 생성
- 사원 테이블(tbl_employee), 부서 테이블(tbl_dept), 주소 테이블(tbl_address) 생성
-- 1. 사원 테이블
CREATE TABLE tbl_employee (
emp_id NUMBER NOT NULL, -- 사원번호
emp_name VARCHAR2(100), -- 사원이름
gender VARCHAR2(10), -- 성별
age NUMBER, -- 나이
hire_date DATE, -- 입사일
dept_id NUMBER, -- 외래키 : 외부 테이블인 부서테이블의 기본키 참조
address_id NUMBER, -- 외래키 : 외부 테이블인 주소테이블의 기본키 참조
PRIMARY KEY ( emp_id ) -- emp_id를 기본키로 설정
-- 기본키 설정 시, 기본키의 인덱스 이름을 직접 설정하고 싶다면,
-- CONSTRAINT 기본키파일이름 PRIMARY KEY ( 기본키 컬럼명 ) 사용
);
-- 2. 부서 테이블
CREATE TABLE tbl_dept (
dept_id NUMBER NOT NULL, -- 부서번호
dept_name VARCHAR2(50), -- 부서명
user_yn VARCHAR2(2) DEFAULT 'Y', -- 사용여부
dept_desc VARCHAR2(200), -- 부서설명
CONSTRAINT tbl_dept_pk PRIMARY KEY ( dept_id ) -- dept_id를 기본키로 설정
);
-- 3. 주소 테이블
-- ※ Oracle에서는 테이블과 COMMENT를 함께 생성 불가
-- → 테이블 생성 후, 'COMMENT ON'을 사용해 일일히 코멘트 부여해야 함
CREATE TABLE tbl_address (
address_id NUMBER NOT NULL, -- 주소 일련번호
city VARCHAR2(100), -- 도시명
gu VARCHAR2(50), -- 구이름
address_name VARCHAR2(100), -- 나머지 주소
CONSTRAINT tbl_address_pk PRIMARY KEY( address_id )
);
Oracle에서 COMMENT 사용
1) Oracle에서 객체 주석 사용
• 객체 주석을 사용하면, 설명기능이 추가되어 나중에 컬럼의 용도나 중요한 정보를 추가하여 개발 시 참조 가능
예 ) COMMENT ON COLUMN tbl_dept.use_yn IS ‘부서가 현재 사용중이면 Y, 아니면 N’ ;
2) 구문
※ Oracle에서는 테이블과 COMMENT를 함께 생성 불가
→ 테이블 생성 후, 'COMMENT ON'을 사용해 일일히 코멘트 부여해야 함
• COMMENT ON 객체타입[ TABLE | COLUMN ] 테이블명.컬럼객체명 IS '주석' ;
COMMENT ON TABLE tbl_address IS '주소테이블' ;
COMMENT ON COLUMN tbl_address.address_id IS '주소 일련번호';
3) 추가한 코멘트 확인
① 테이블의 주석 확인
SELECT *
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = ‘테이블명’
② 테이블의 컬럼 주석 확인
SELECT *
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = ‘테이블명’
※ ( 주의 ) Oracle에서 테이블명과 컬럼명은 상수 취급!
→ 대문자로 관리되므로 대문자로 작성하거나 UPPER 이용
'백엔드 > Oracle' 카테고리의 다른 글
| Oracle : SQL ( OUTER JOIN / SUBQUERY ) (0) | 2023.07.17 |
|---|---|
| Oracle : SQL ( INNER JOIN ) (0) | 2023.07.14 |
| SQL ( GROUP BY ~ HAVING / 집합연산자 ) (0) | 2023.07.12 |
| Oracle : SQL ( CASE WHEN THEN / GROUP BY ) (0) | 2023.07.11 |
| Oracle : SQL ( 날짜형 함수 / 형변환 함수 / NULL 처리 함수 / GREATEST, LEAST, DECODE / WITH절 ) (0) | 2023.07.10 |