SQL활용 2022/03/21(기본SQL 작성하기DCL,DDL)

데이터 제어어(Data Control Language)

• 사용자에게 권한 생성 혹은 권한 삭제 명령어.
  CREATE : 계정 생성
  DROP : 계정 삭제
  GRANT : 권한 생성
  REVOKE : 권한 삭제

-- 시스템 계정 => ex) system / 1111
-- 데이터제어어(DCL) : 사용자를 추가, 권한부여, 권한삭제, 제거

-- 계정생성 (ex GUEST 219 , 암호 1234)
CREATE USER GUEST219 IDENTIFIED BY "1234";

-- 계정 확인
SELECT * FROM DBA_USERS;

-- 권한부여 (접속, 리소스)
GRANT CONNECT, RESOURCE TO GUEST219;

-- 권한회수, 제거 (접속, 리소스)
REVOKE CONNECT, RESOURCE FROM GUEST219;

-- 계정 삭제(CASCADE 권한모두 삭제)
DROP USER GUEST219 CASCADE;

---

데이터 정의어(Data Definition Language)

• 데이터의 구조를 정의하기 위한 테이블 생성, 수정, 삭제 명령어.
  CREATE : 테이블 생성
  DROP : 테이블 삭제
  ALTER : 테이블 수정
  TRUNCATE : 테이블에 있는 모든 데이터 삭제
  RENAME : 테이블명 변경

-- 제약조건
기본키(PK), 외래키(FK), 체크(CK), 고유값(UQ)

-- 타입

-- 데이터정의어(DDL) : 테이블생성 및 구조, 제약조건 등을 설정

CREATE TABLE MEMBER3 (
    ID VARCHAR2(30),
    PW VARCHAR2(200),
    AGE NUMBER(3),
    PHONE VARCHAR2(15),
    GENDER VARCHAR2(1),
    REGDATE DATE,
    REGDATE1 TIMESTAMP,
    CONSTRAINT PK_MEMBER3_ID PRIMARY KEY(ID),
    CONSTRAINT CK_MEMBER3_GENDER CHECK ( GENDER IN ('M','F') )
);

-- 주소컬럼추가
-- ALTER TABLE [테이블명] ADD [추가할컬럼명] VARCHAR2(100);
ALTER TABLE MEMBER3 ADD ADDR VARCHAR2(100);

-- 주소 타입의 길이 100 -> 120
ALTER TABLE MEMBER3 MODIFY ADDR VARCHAR2(120);

-- 주소 컬럼 삭제
ALTER TABLE MEMBER3 DROP COLUMN ADDR;

-- 기본키 제약조건 설정(동작안됨)
ALTER TABLE MEMBER3 ADD CONSTRAINT PK_MEMBER3_ID PRIMARY KEY(ID);

-- 성별에 not null 추가
ALTER TABLE MEMBER3 MODIFY GENDER NOT NULL;

-- 연락처에 고유값(UNIQUE) 설정(제약조건)
ALTER TABLE MEMBER3 ADD CONSTRAINT UQ_MEMBER3_PHONE UNIQUE(PHONE);

-- 제약조건 삭제
ALTER TABLE MEMBER3 DROP CONSTRAINT UQ_MEMBER3_PHONE; 


-- 테이블 삭제(BOARD3)
DROP TABLE BOARD3 CASCADE CONSTRAINTS;

-- 게시판(BOARD3) 테이블 생성
-- NO, 숫자
-- TITLE 제목
-- CONTENT 내용
-- WRITER 작성자
-- HIT 조회수
-- REGDATE 작성일자 DATE

CREATE TABLE BOARD3 (
    NO NUMBER,
    TITLE VARCHAR2(100),
    CONTENT CLOB,
    WRITER VARCHAR2(30),
    HIT NUMBER,
    REGDATE DATE,
    REGDATE1 TIMESTAMP,
    CONSTRAINT PK_BOARD3_NO PRIMARY KEY(NO),
    CONSTRAINT FK_BOARD3_WRITER FOREIGN KEY(WRITER) REFERENCES MEMBER3(ID)
);

-- 제약조건 추가
ALTER TABLE BOARD3 ADD CONSTRAINT PK_BOARD3_NO PRIMARY KEY(NO);
ALTER TABLE BOARD3 ADD CONSTRAINT FK_BOARD3_WRITER FOREIGN KEY(WRITER) REFERENCES MEMBER3(ID);

TEST

-- 부서테이블 (department)

1. 테이블생성(제약조건 X)
   부서번호 : no
   부서명 : name(3)

2. 제약조건
   기본키

3. 지역컬럼 추가 ( area VARCHAR2(20) )

4. 지역컬럼의 체크값을 "부산", "서울", "대전" 만 가능하게

5. 부서명의 길이를 30으로 변경

6. 부서명에 not null 추가

7. 부서명에 UNIQUE 제약조건 설정 UQ_DEPT_NAME

-- 1. 테이블생성(제약조건 X)
CREATE TABLE DEPARTMENT(
    NO NUMBER,
    NAME VARCHAR2(3)   
);

-- 2. 제약조건
ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPT_NO PRIMARY KEY(NO);

-- 3. 지역컬럼 추가 ( area  VARCHAR2(20) )
ALTER TABLE DEPARTMENT ADD AREA VARCHAR2(20);

-- 4. 지역컬럼의 체크값을 "부산", "서울", "대전" 만 가능하게
ALTER TABLE DEPARTMENT ADD CONSTRAINT CK_DEPT_AREA CHECK ( AREA IN ('부산', '서울', '대전') );

-- 5. 부서명의 길이를 30으로 변경
ALTER TABLE DEPARTMENT MODIFY NAME VARCHAR2(30);

-- 6. 부서명에 not null 추가
ALTER TABLE DEPARTMENT MODIFY NAME NOT NULL;

-- 7. 부서명에 UNIQUE 제약조건 설정 UQ_DEPT_NAME
ALTER TABLE DEPARTMENT ADD CONSTRAINT UQ_DEPT_NAME UNIQUE(NAME);

---

-- 회사원 테이블(employee)

1. (제약조건, 기본키, 체크만)
   사원번호 : no NUMBER (PK),
   이름 : name
   부서번호 : deptno (FK)
   상사번호 : pempno
   직급 : position VARCHAR(10), (CK - "대리", "과장", "부장", "차장") 만 가능
   급여 : pay NUMBER
   등록일 : regdate DATE

2. 부서 테이블의 부서번호를 회사원테이블의 부서 번호와 외래키 연결

-- 1. 테이블 생성(제약조건, 기본키, 체크만)

CREATE TABLE EMPLOYEE(
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20),
    DEPTNO NUMBER,
    PEMPNO NUMBER,
    POSITION VARCHAR(10),
    PAY NUMBER,
    REGDATE DATE,
    CONSTRAINT CK_EMP_POSITION CHECK ( POSITION IN ('대리', '과장', '부장', '차장') )    
);

-- 2. 부서 테이블의 부서번호를 회사원테이블의 부서 번호와 외래키 연결
ALTER TABLE EMPLOYEE ADD 
    CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(NO);

---

데이터 조작어(Data Manipulation Language) : 데이터 추가, 조회, 수정 및 삭제를 위한 명령어.

INSERT : 데이터 입력
UPDATE : 데이터 수정
DELETE : 데이터 삭제
SELECT : 데이터 조회

트랜잭션 제어어(Transaction Control Language)

COMMIT : 적용하기
ROLLBACK : 되돌리기
SAVEPOINT : 저장점을 정의

---

-- 전체 조회
SELECT * FROM MEMBER3;

-- 추가하기 
-- INSERT INTO 테이블명(컬럼명) VALUES(추가값);
INSERT INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1 )
VALUES('C', 'C', 11, '010-0000-1111', 'M', CURRENT_DATE, CURRENT_DATE);

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기

-- 일괄 추가
INSERT ALL 
    INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1 )
        VALUES('D', 'C', 11, '010-0000-1111', 'M', CURRENT_DATE, CURRENT_DATE)
    INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1 )
        VALUES('E', 'C', 11, '010-0000-1111', 'M', CURRENT_DATE, CURRENT_DATE)
    INTO MEMBER3(ID, PW, AGE, PHONE, GENDER, REGDATE, REGDATE1 )
        VALUES('F', 'C', 11, '010-0000-1111', 'M', CURRENT_DATE, CURRENT_DATE)
SELECT * FROM DUAL;

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기

-- 시퀀스 생성하기(SEQ_BOARD3_NO)
CREATE SEQUENCE SEQ_BOARD3_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE;

-- 게시글 작성
INSERT INTO BOARD3( NO, TITLE, CONTENT, WRITER, HIT, REGDATE )
VALUES(SEQ_BOARD3_NO.NEXTVAL, 'A', 'A', 'A', 1, CURRENT_DATE);

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기

SELECT * FROM BOARD3;

-- 시퀀스 조회
SELECT SEQ_BOARD3_NO.CURRVAL FROM DUAL;

-- 수정하기
-- UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값 WHERE 조건;
UPDATE MEMBER3 SET AGE=20, PHONE='010-1234-5678' WHERE ID='A';

-- 나이가 0~20까지는 나이를 200으로 
-- 나이가 21~40까지는 나이를 300으로 아니면 400으로 변경
UPDATE MEMBER3 SET AGE=
    CASE 
        WHEN (AGE >= 0 AND AGE<=20) THEN 200
        WHEN (AGE >= 21 AND AGE<=40) THEN 300
        ELSE 400 
    END;

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기

-- 게시판에서 글 번호가 1번인것의 제목을 'bbb' 조회수를 10
UPDATE BOARD3 SET TITLE= 'bbb', HIT='10' WHERE NO=1;
-- 1,3,5번 바꾸기
UPDATE BOARD3 SET TITLE= 'bbb', HIT='10' WHERE NO=1 OR NO=3 OR NO=5;
UPDATE BOARD3 SET TITLE= 'bbb', HIT='10' WHERE NO IN(1,3,5);

-- 게시판에서 조회수가 0~9 => 10
-- 조회수가 10~20 => 20
-- 조회수가 30~40=>30
-- 나머지는 40
UPDATE BOARD3 SET HIT=
    CASE 
        WHEN (HIT >= 0 AND HIT<=9) THEN 10
        WHEN (HIT >= 10 AND HIT<=20) THEN 20
        WHEN (HIT >= 30 AND HIT<=40) THEN 30
        ELSE 40
    END;

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기

-- 삭제하기
-- DELETE FROM 테이블명 WHERE 조건;
DELETE FROM BOARD3 WHERE NO=2;
-- 외래키로 다른곳에서 사용중이라서..
-- 회원삭제, 중요정보를 UPDATE를 함.
DELETE FROM MEMBER3 WHERE ID='a';

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기
-- 조건
SELECT * FROM MEMBER3 WHERE ID IN ('A','B','C');
-- 조건 + 정렬
SELECT * FROM MEMBER3 WHERE ID IN ('A','B','C') ORDER BY ID DESC;
-- 게시판 조회수가 20 이상인것을 글번호 기준 내림차순 조회
SELECT * FROM BOARD3 WHERE HIT >= 20 ORDER BY NO DESC;

-- 검색 
SELECT NO, TITLE, WRITER, HIT, REGDATE FROM BOARD3
    WHERE TITLE LIKE '%' || '가' || '%'
    ORDER BY NO DESC;
    
-- 페이지네이션
-- BOARD3 => 컬럼 ROWN 추가 => 조회
SELECT * FROM(
    SELECT 
        NO NO, TITLE TITLE, WRITER WRITER, HIT HIT, REGDATE DT, 
        ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN
    FROM 
        BOARD3
) BOARD33 WHERE ROWN BETWEEN 4 AND 6;

-- 뷰 생성하기
CREATE OR REPLACE VIEW BOARD33 AS
SELECT 
        NO NO, TITLE TITLE, WRITER WRITER, HIT HIT, REGDATE DT, 
        ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN
    FROM 
        BOARD3;

-- 조회하기
SELECT * FROM BOARD33;

---

-- 1. 시퀀스를 생성 SEQ_ITEM1_ITEMNO 시작 숫자 1001, 1, NOMAXVALUE
CREATE SEQUENCE SEQ_ITEM1_ITEMNO START WITH 1001 INCREMENT BY 1 NOMAXVALUE;

-- ITME1테이블에 시퀀스를 이용해서 5개 추가
INSERT INTO ITEM1( ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE )
    VALUES( SEQ_ITEM1_ITEMNO.NEXTVAL, '아이템5', '1000', '10', CURRENT_DATE );
    
-- ITEM1에 재고 수량이 100 미만은 100으로 변경
UPDATE ITEM1 SET ITEMQTY = 100 WHERE ITEMQTY < 100;

-- ITEM1에 재고수량*금액값을 total 컬럼으로 추가한 후 조회
SELECT ITEMNO, ITEMNAME, ITEMPRICE, ITEMQTY, ITEMDATE, ITEMPRICE*ITEMQTY TOTAL FROM ITEM1;

-- ITEM1에 "검색어" 가 포함된것 조회 ex)검색어 문자 '1'
SELECT * FROM ITEM1 WHERE ITEMNAME LIKE '%' ||'1'||'%';

-- MEMBER1에 회원 3명 추가
INSERT INTO MEMBER1( USERID, USERPW, USERNAME, USERAGE, USERDATE, USERADDR )
    VALUES( 'CCC', 'CCC', 'CCC', '30', CURRENT_DATE, 'CCC' );
    
-- 회원이름 순으로 오름차순 정렬해서 조회
SELECT * FROM MEMBER1 ORDER BY USERNAME ASC;

-- 나이가 30이상인 사람만 조회
SELECT * FROM MEMBER1 WHERE USERAGE>=30;

SELECT * FROM ITEM1;
SELECT * FROM MEMBER1;

COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기

-- 2. 시퀀스 생성 SEQ_ORDER1_ORDNO
-- 시작숫자 10001, 1, NOMAXVALUE
CREATE SEQUENCE SEQ_ORDER1_ORDNO START WITH 10001 INCREMENT BY 1 NOMAXVALUE;

-- 주문 3건 수행하기(ITEM1, MEMBER1 외래키)
INSERT INTO ORDER1( ORDNO, ORDCNT, ORDDATE, ORDITEM, ORDID )
    VALUES( SEQ_ORDER1_ORDNO.NEXTVAL, '1000', CURRENT_DATE, 1005, 'BBB' );
    
-- 주문내역을 주문일자 기준으로 내림차순 정렬 조회
SELECT * FROM ORDER1 ORDER BY ORDDATE DESC;

-- 주문수량이 1000이상인것만 조회
SELECT * FROM ORDER1 WHERE ORDCNT >= 1000;

SELECT * FROM ORDER1;
    
COMMIT; -- 적용하기 (추가, 삭제, 수정)
ROLLBACK; -- 되돌리기