SQL활용(SQL 간단한 지식)

min seung moon·2021년 2월 8일
0

Oracle

목록 보기
9/23

1. SQL의 분류

1. DML (Data Manipulation Language)

  • DML은 데이터 조작 언어로 데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어
  • SQL문 중에 SELECT, INSERT, UPDATE, DELETE가 DML문에 해당
  • 트랜잭션이 발생하는 SQL도 DML이다

2. DDL (Data Definition Language)

  • DDL은 데이터 정의 언어로 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할
  • DDL은 CREATE, DROP, ALTER, TRUNCATE등이 해당
  • 트랜잭션이 발생하지 않은 SQL문

3. DCL (Data Control Language)

  • DCL은 데이터 제어 언어로 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 언어
  • DCL은 GRANT, REVOKE 등이 해당
  • TCL(Transaction Control Language), 트랜잭션 제어(COMMIT, ROLLBACK, CHECKPOINT)

2. DDL (Data Definition Language)

1. CREATE

  • DATABASE, USER, TABLE 등의 객체를 생성
-- DATABASE
-- 수동으로 생성 시에는 구성 정보 선택부터 옵션이 많기에 힘이 들 수 있다
-- DB를 새로 구축하거나 생성할 일이 잘 없거나 권한이 있는 사람만 가능
CREATE DATABASE databaseNAME;

-- USER
-- 수동으로 생성하는게 복잡하지는 않다
-- IDENTIFIED 뒤에 비밀번호 입력
-- TABLE 위치를 설정해주면 되며 기본적인 USER 속성은 아래처럼 하면 된다
CREATE USER userNAME IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

-- TABLE
-- 테이블을 생성할 때는 테이블에 어떠한 값을 넣을지랑 데이터 타입 등 설정이 가능하다
-- 기본 키 (PRIMARY KEY), 외래 키(FOREIGN KEY), NULL, NOT NULL ... 등의 옵션이 있다
CREATE TABLE tableName(
    ColumnName  DataType	option,
    ColumnName  DataType	option,
    ColumnName  DataType	option,
    ColumnName  DataType	option,
    ColumnName  DataType	option,
    ColumnName  DataType	option
);

2. ALTER

  • ADD, RENAME, MODIFY, DROP으로 객체 및 테이블 구조 변경
-- USER password 변경
-- ALTER USER 유저명 INDENTIFIED BY 변경할비밀번호;
ALTER USER userName INDENTIFIED BY changePW;

-- Add Column
-- ALTER TABLE 테이블명 ADD 컬럼명 데이터타입(길이) DEFAULT 기본값(생략O) NOT NULL(null 허용, 생략 O);
ALTER TABLE tableName ADD columnName dataType(length) DEFAULT ? NOT NULL;

-- Add Primary key
-- ALTER TABLE 테이블명 ADD PRIMARY KEY (기본키 추가할 컬럼);
ALTER TABLE tableName ADD PRIMARY KEY (column1,column2...);

-- Rename Column
-- ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명;
ALTER TABLE tableName RENAME COLUMN columnName(now) TO columnName(after);

-- Rename Table
-- ALTER TABLE 기존테이블명 RENAME TO 바꿀테이블명;
ALTER TABLE tableName(now) RENAME TO tableName(after);

-- Modify Column dataType 
-- ALTER TABLE 테이블명 MODIFY 컬럼명 바꿀데이터타입(길이);
ALTER TABLE tableName MODIFY columnName dataTye(length);

-- Drop Column
-- ALTER TABLE 테이블명 Drop COLUMN 컬럼명;
ALTER TABLE tableName Drop COLUMN columnName;

-- Drop Primary key
-- ALTER TABLE 테이블명 DROP PRIMARY KEY;
ALTER TABLE tableName DROP PRIMARY KEY;

3. DROP

  • 객체 삭제
-- Drop table
-- DROP TABLE 테이블명 [옵션];
DROP TABLE tableName [option];

-- Drop User
-- cascade, 사용자와 관련된 모든것을 삭제
-- root 계정으로 해야 됨
-- DROP USER 유저명 cascade;
DROP USER userName cascade;

-- Drop Database
-- DB 생성과 마찬가지로 옵션이 많고 권한이 없음
DROP DATABASE;

4. TRUNCATE

  • 테이블 데이터 삭제
  • delete와 달리 table의 데이터 전체를 삭제하여 테이블 생성 초기 상태로 변경
-- TRUNCATE TABLE
-- TRUNCATE TABLE 테이블 명;
TRUNCATE TABLE tableName;

DCL (Data Control Language) GRANT, REVOKE, DENY

1. GRANT

  • 데이터베이스에 대한 일련권한 부여
-- 시스템 권한
-- GRANT 권한1, 권한2, 권한3 TO 사용자계정;
-- CONNECT, DB 접속 권한
-- DBA, 소유한 데이터베이스 객체를 관리하고 사용자들을 작성하고 변경하고 제거할 수 있는 모든 권한
-- RESOURCE, 객체(테이블, 뷰, 인덱스)를 생성, 수정, 삭제할 수 있는 권한
GRANT CONNECT, DBA, RESOURCE TO user;

-- 객체권한
-- GRANT 권한1, 권한2, 권한3 ON 객체명 TO 사용자계정;
-- SELECT, TABLE DATA 검색
-- INSERT, TABLE DATA 입력
-- DELETE, TABLE DATA 삭제
-- UPDATE, TABLE DATA 수정
GRANT SELECT, INSERT, DELETE, UPDATE ON dbTBL TO user;

2. REVOKE

  • 데이터베이스에 대한 일련권한 회수
-- 시스템 권한
-- REVOKE 권한1, 권한2, 권한3 TO 사용자계정;
REVOKE CONNECT, DBA, RESOURCE TO user;

-- 객체권한
-- REVOKE 권한1, 권한2, 권한3 ON 객체명 TO 사용자계정;
REVOKE SELECT, INSERT, DELETE, UPDATE ON dbTBL TO user;

3. TCL

  • COMMIT : 거래 내역을 확정, 데이터 입력, 수정, 삭제 등의 기록을 물리적으로 TABLE에 저장
  • ROLLBACK : 거래 내역을 취소함, 데이터 입력, 수정, 삭제 등의 기록을 물리적으로 TABLE에 취소
  • CHECKPOINT : 저장점 설정(ROLLBACK할 위치를 지정)
-- 입력, 삭제, 수정한 기록(거래)을 테이블에 확정하여 저장
INSERT INTO testTBL3 VALUES (cycleSEQ.nextval);
DELTE fROM testTBL2 WHERE id = 3;
UPDATE testTBL2 SET id = 12 WHERE id=13;
COMMIT;

-- 입력, 삭제, 수정한 기록(거래)을 취소
INSERT INTO testTBL3 VALUES (cycleSEQ.nextval);
DELTE fROM testTBL2 WHERE id = 3;
UPDATE testTBL2 SET id = 12 WHERE id=13;
ROLLBACK;

3. DML (Data Manipulation Language)

  • 데이터 베이스에서 가장 기본적이고 가장 필수적인 명령문

1. INSERT

  • TABLE에 DATA를 입력
-- INSERT INTO 테이블명(컬럼명1,컬럼명2,컬럼명3)VALUES(값1,값2,값3);
-- 입력시에는 컬럼의 갯수와 값의 갯수가 일치해야 된다
-- TABLE 구조에 맞게 PASSED FULL VALUE일 경우 COLUMN명은 생략 가능
INSERT INTO tableName(column1,column2,column3) VALUES (value1,value2,value3);
INSERT INTO tableName VALUES (value1,value2,value3);

2. UPDATE

  • TABLE에 DATA를 수정
-- UPDATE 테이블명 SET 컬럼명1 = 값1, 컬럼2 = 값2, ... WHERE 조건;
-- 특정 column에 값을 수정할 경우 WHERE를 입력하고 전체 컬럼에 적용시 WHERE 생략
UPDATE tableName SET column1 = value1, column2 = value2, ... WHERE condition;
UPDATE tableName SET column1 = value1, column2 = value2, ...;

3. DELETE

  • TABLE에 DATA를 삭제
-- DELETE FROM 테이블명 WHERE 조건;
-- 주의! WHERE 조건이 없으면 테이블 전체 삭제 됨
DELETE FROM tableName WHERE condition;

-- 삭제시 개수 설정 ROWNUM, 2개까지 만 삭제
DELETE FROM tableName WHERE condition AND ROWNUM <= 2;

번외1. 시퀀스

  • 자동으로 증가하는 시퀀스
-- 기본적인 시퀀스 생성
-- CREATE SEQUENCE 시퀀스명 START WITH 초기값 INCREMENT BY 증가값;
CREATE SEQUENCE sequenceName START WITH value INCREMENT value;

-- 시퀀스 증가값 상승
-- ALTER SEQUENCE 시퀀스명 INCREMENT BY 새로운증가값;
ALTER SEQUENCE sequenceName INCREMENT BY value;

-- 시퀀스 현재값 확인
-- SELECT 시퀀스명.CURRVAL FROM DUAL;
SELECT sequenceName.CURRVAL FROM DUAL;

-- 시퀀스 특정 범위를 반복
CREATE SEQUENCE sequenceName -- 시퀀스명
START WITH value			 -- 초기값
INCREMENT BY value			 -- 증가값
MINVALUE value				 -- 최소값
MXVALUE value				 -- 최댓값
CYCLE						 -- 반복설정
NOCACHE						 -- 캐시 사용 안함
;

-- 시퀀스 삭제
-- DROP SEQUENCE 시퀀스명;
DROP SEQUENCE sequenceName;

번외2. 대량의 샘플데이터 생성

-- INSERT INTO 테이블명 (컬럼1, 컬럼2, ...) SELECT문;
INSERT INTO tableName (column1, column2, ...) SELECT FORM;

-- 데이터를 가져와서 입력
INSERT INTO testestTBL4 SELECT EMPLOYEE_ID, FIRST_NAME, 
LAST_NAME, PHONE_NUMBER FROM HR.employees;

-- 데이블의 정의까지 생략
CREATE TABLE testTBL5 AS 
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER FROM HR.employees);

1. INSERT 예제

-- testTBL2를 생성
CREATE TABLE testTBL2(
    id number(4),
    userName nchar(3),
    age number(2),
    nation nchar(4) default '대한민국'
);

-- 시퀀스 생성
CREATE SEQUENCE idSEQ
    START WITH 1
    INCREMENT BY 1;
    
-- 데이터를 입력
INSERT INTO testTBL2 VALUES(idESQ.nextval, '유나', 25, default);
INSERT INTO testTBL2 VALUES(idESQ.nextval, '혜정', 24, '영국');
SELECT * FROM testTBL2;

-- id 열의 값이 1부터 차례대로 들어가는 것을 확인
INSERT INTO testTBL2 VALUES(11, '쯔위', 18, '대만');
ALTER SEQUENCE idSEQ INCREMENT BY 10;
INSERT INTO testTBL2 VALUES(idESQ.nextval, '미나', 21, '일본');
ALTER SEQUENCE idSEQ INCREMENT BY 1;
SELECT * FROM testTBL2;

-- 시퀀스 현재 값 확인
SELECT idSEQ.CURRVAL FROM DUAL;

-- 특정 범위의 값이 계속 반복되어서 입력
CREATE SEQUENCE idSEQ
START WITH 100
INCREMENT BY 100
MINVALUE 100
MXVALUE 300
CYCLE
NOCACHE;

2. INSERT SELECT, CREATE SELECT 예제

-- 대량의 샘플데이터 생성
CREATE TABLE testTBL4(
	empID NUMBER(6),
    FirstName VARCHAR2(20),
	LastName VARCHAR(25),
    Phone VARCHAR2(20)
);

INSERT INTO testestTBL4 
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
PHONE_NUMBER
FROM HR.employees;

CREATE TABLE testTBL5 AS 
(SELECT EMPLOYEE_ID, 
FIRST_NAME, 
LAST_NAME, 
PHONE_NUMBER
FROM HR.employees);

3. UPDATE 예제

UPDATE tsetTBL4 SET phone = '없음' WHERE firstname = 'david';
UPDATE buyTBL SET price = price*1.5;

4. DELETE 예제

DELETE FROM testTBL4 WHERE firstNmae = 'peter';
-- peter DELETE 취소
Rollback;
-- 3건의 'Peter' 중 2건만 삭제할 때 ROUNUM 활용
DELETE FROM testTBL4 WHERE firstName = 'peter' and ROWNUM <= 2;
profile
min seung moon
아직까지는 코린이!
이전 포스트

SQL활용(기본SQL작성-DCL)

다음 포스트

SQL 데이터 타입

0개의 댓글