0907 SQL

정혜지·2022년 9월 7일

2022.09.07

https://downloads.mysql.com/archives/installer/

// 테이블 생성 쿼리문

USE scott;

CREATE TABLE IF NOT EXISTS BONUS (
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
SAL double DEFAULT NULL,
COMM double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS DEPT (
DEPTNO int(11) NOT NULL,
DNAME varchar(14) DEFAULT NULL,
LOC varchar(13) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

CREATE TABLE IF NOT EXISTS EMP (
EMPNO int(11) NOT NULL,
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
MGR int(11) DEFAULT NULL,
HIREDATE datetime DEFAULT NULL,
SAL double DEFAULT NULL,
COMM double DEFAULT NULL,
DEPTNO int(11) DEFAULT NULL,
PRIMARY KEY (EMPNO),
KEY PK_EMP (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NULL, 10);

CREATE TABLE IF NOT EXISTS SALGRADE (
GRADE double DEFAULT NULL,
LOSAL double DEFAULT NULL,
HISAL double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);

ALTER TABLE EMP
ADD CONSTRAINT PK_EMP FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE SET NULL ON UPDATE CASCADE;

-- https://dev.mysql.com/doc/refman/8.0/en/data-types.html 참고
-- 자기만의 PEOPLE 테이블 만들어 보기!

-- 7.DDL.sql
-- DDL(Data Definition Language)
/* CRUD

- C : create, 데이터 생성 
	- 이미 존재 하는 table에 데이터를 새롭게 저장, insert
- R : read, 존재하는 데이터 검색
	- select
- U : update, 존재하는 데이터 수정
	- update
- D : delete, 존재하는 데이터 삭제
	- delete

/* 참고 :
[1] table 생성 명령어
create table table명(
컬럼명1 컬럼타입[(사이즈)][제약조건] ,
컬럼명2....
);

[2] table 삭제 명령어
drop

[3] table 구조 수정 명령어
alter
*/

-- 존재하는 table 삭제 명령어
-- 1. table삭제
DROP TABLE test;

-- 2. table 생성
-- name(varchar), age(int) 컬럼 보유한 people table 생성
CREATE TABLE people(
name varchar(20),
age int(3)
);

SHOW TABLES;
DESC PEOPLE;

-- 3. 서브 쿼리 활용해서 emp01 table 생성(이미 존재하는 table기반으로 생성)
-- emp table의 모든 데이터로 emp01 생성
SELECT *
FROM EMP;

DROP TABLE EMP01;
CREATE TABLE EMP01 AS SELECT FROM EMP;
DESC EMP01;
SELECT
FROM EMP01;
-- data 복제 없이 table구조만 복제

DROP TABLE EMP01;

CREATE TABLE EMP01 AS SELECT * FROM EMP
WHERE 1=0;

DESC EMP01;

SELECT *
FROM EMP01;

-- 4. 서브쿼리 활용해서 특정 컬럼(empno)만으로 emp02 table 생성

SELECT *
FROM EMP;

DROP TABLE EMP02;
CREATE TABLE EMP02 AS SELECT EMPNO FROM EMP;

SELECT *
FROM EMP02;

-- 5. ? deptno=10 조건문 반영해서 empno, ename, deptno로 emp03 table 생성

DROP TABLE EMP03;
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE DEPTNO = 10;

-- 6. ?데이터 insert없이 table 구조로만 새로운 emp04 table생성시
-- 사용되는 조건식 : where=거짓

CREATE TABLE EMP04 AS SELECT * FROM EMP
WHERE 1=0;

SELECT *
FROM EMP04;

-- table 수정 : alter
/*
데이터 구조 변경
1. 미존재하는 컬럼 추가
2. 존재하는 컬럼 삭제
3. 존재하는 컬럼의 타입(사이즈) 변경
경우의 수 1 : 기존 사이즈보다 작게 수정
- 이미 데이터가 존재할 경우
데이터가 변경하고자 하는 사이즈보다 작다
데이터가 변경하고자 하는 사이즈보다 크다
- 데이터가 없을 수도 있음

경우의 수 2 : 기존 사이즈보다 크게 수정
	- 이미 데이터가 존재할 경우
		데이터가 변경하고자 하는 사이즈보다 작다
		데이터가 변경하고자 하는 사이즈보다 크다
	- 데이터가 없을 수도 있음
	
경우의 수 3 : 타입 자체를 수정 ..

-- emp01 table로 실습해 보기

-- 7. emp01 table에 job이라는 특정 컬럼 추가(job varchar(10))
-- 이미 데이터를 보유한 table에 새로운 job컬럼 추가 가능
-- add() : 컬럼 추가 함수

-- 8. 이미 존재하는 컬럼 사이즈 변경 시도해 보기
-- 데이터 미 존재 컬럼의 사이즈 수정
-- modify / change column

-- 9. 이미 데이터가 존재할 경우 컬럼 사이즈가 큰 사이즈의 컬럼으로 변경 가능

-- 10. job 컬럼 삭제

-- 11. table의 순수 데이터만 완벽하게 삭제하는 명령어

-- https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_NAMING_CONVENTIONS.html
1. 공통
소문자를 사용한다.
단어를 임의로 축약하지 않는다.
ex) register_date (o) / reg_date (x)
가능한 약어의 사용을 피한다. 사용해야 하는 경우, 약어 역시 소문자로 사용한다.
동사는 능동태를 사용한다.
ex) register_date (o) / registered_date (x)

테이블
단수형을 사용한다.
이름을 구성하는 각각의 단어를 underscore로 연결되는 snake case를 사용한다.
교차 테이블(many-to-many)의 이름에 사용할 수 있는 직관적인 단어가 있다면 해당 단어를 사용한다. 적절한 단어가 없다면 관계를 맺고 있는 각 테이블의 이름을 "and" 또는 "has"로 연결한다.
ex)
articles, movies : 복수형
vip_members: 약어도 예외 없이 소문자 & 단어 연결에 underbar 사용
articles_and_movies: 교차 테이블을 "and"로 연결

컬럼
auto increment 속성의 PK를 대리키로 사용하는 경우, "테이블 이름_id"의 규칙으로 명명한다.
이름을 구성하는 각각의 단어를 snake case를 사용한다.
foreign key 컬럼은 부모 테이블의 primary key 컬럼 이름을 그대로 사용한다.
self 참조인 경우, primary key 컬럼 이름을 그대로 사용한다.
같은 primary key 컬럼을 자식 테이블에서 2번 이상 참조하는 경우, primary key 컬럼 이름 앞에 적절한 접두어를 사용한다.
boolean 유형의 컬럼이면 "_flag" 접미어를 사용한다.
date, datetime 유형의 컬럼이면 "_date" 접미어를 사용한다.
ex)
article_id, movie_id: "테이블이름" + "_id"
complet_flag: boolean 유형의 컬럼
issue_date: 날짜 유형의 컬럼

INDEX
이름을 구성하는 각각의 단어를 hyphen으로 연결하는 kebab case를 사용한다.
접두어
unique index: uix
spatial index: six
index: nix
"접두어"-"테이블 이름"-"컬럼 이름"-"컬럼 이름"
ex) uix-account-login-email
FOREIGN KEY
이름을 구성하는 각각의 단어를 hyphen으로 연결하는 kebab case를 사용한다.
"fk"-"부모테이블 이름"-"자식 테이블 이름"
ex)
fk-movie-article: article테이블이 movie테이블 참조
fk-admin-notice-1, fk-admin-notice-2: notice 테이블이 admin테이블을 2회 이상 참조하여 넘버링
VIEW
접두어 "v"를 사용한다.
기타 규칙은 테이블과 동일하다.
ex) v_privileges
Stored Procedure
Stored Procedure 명명 규칙

-접두어 usp 를 사용한다.
SP의 이름을 구성하는 각각의 단어를 underscore 로 연결하는 snake case 를 사용한다.
특정 테이블에 대한 단순 CRUD 작업인 경우, 각각 아래와 같은 이름 규칙을 사용한다.
-CREATE
usp_add{테이블 이름}
-RETRIEVE
uspget{테이블 이름} / 단일 행을 반환하는 경우
uspget_list{테이블 이름} / 여러 행을 반환하는 경우
-UPDATE
uspmod{테이블 이름}
-DELETE
uspdel{테이블 이름}
SP가 특정 비즈니스 로직을 처리하는 경우, 적절한 동사와 명사의 조합을 사용한다.
(예) uspvalidate_applicant, usp_check_brand_user, ...
local variable, input parameter, output parameter
local variable
접두어 v 를 사용한다.
input parameter
접두어 pi 를 사용한다.
output parameter
접두어 po 를 사용한다.

FUNCTION
접두어 "usf"를 사용한다.
이름을 구성하는 각각의 단어를 underscore로 연결하는 snake case를 사용한다.
ex) usf_random_key
TRIGGER
이름을 구성하는 각각의 단어를 underscore로 연결하는 snake case를 사용한다.
접두어
tra: after 트리거
trb: before 트리거
"접두어""테이블 이름""트리거 이벤트"
ex)
tga_movies_ins: after insert 트리거
tga_movies_upd: after update 트리거
tgb_movies_del: before delete 트리거

-- DML 실습시 주의사항!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- COMMIT 설정 조회 : 1은 오토커밋(자동으로 변경사항이 원본 DB에 적용)
SELECT @@AUTOCOMMIT;
SET AUTOCOMMIT = 0;
SELECT @@AUTOCOMMIT;

-- WORKBENCH 설정
EDIT - PREFERENCES - SQL EDITOR 항목 - 최하단의 SAFE UPDATES를 실습시에는 해제

INSERT INTO EMP01 (empno, ename, deptno) VALUES (0001, 'mysql1', 50);
INSERT INTO EMP01 (empno, ename, deptno) VALUES (0002, 'mysql2', 50);
INSERT INTO EMP01 (empno, ename, deptno) VALUES (0003, 'mysql3', 50);
INSERT INTO EMP01 (empno, ename, deptno) VALUES (0004, 'mysql4', 50);
INSERT INTO EMP01 (empno, ename, deptno) VALUES (0005, 'mysql5', 50);

-- BULK INSERT
INSERT INTO EMP01 (empno, ename, deptno)
VALUES (0001, 'mysql1', 50),
(0002, 'mysql2', 50),
(0003, 'mysql3', 50),
(0004, 'mysql4', 50),
(0005, 'mysql5', 50);

-- update
-- 1. 테이블의 모든 행 변경
SELECT @@AUTOCOMMIT;
SET AUTOCOMMIT = 0;
SELECT @@AUTOCOMMIT;

DROP TABLE EMP01;

CREATE TABLE EMP01 AS SELECT * FROM EMP;

SELECT DEPTNO
FROM EMP01;

UPDATE EMP01 SET DEPTNO = 60;

SELECT DEPTNO
FROM EMP01;

ROLLBACK;

SELECT DEPTNO
FROM EMP01;

-- 30이전의 데이터로 복원

-- 2. emp01 table의 모든 사원의 급여를 10%(sal1.1) 인상하기
SELECT
FROM EMP01;

UPDATE EMP01 SET SAL = SAL*1.1;
-- ? emp table로 부터 empno, sal, hiredate, ename 순으로 table 생성
DROP TABLE TEST;
DROP TABLE EMP01;
CREATE TABLE EMP01 AS SELECT EMPNO, SAL, HIREDATE, ENAME FROM EMP;

SELECT *
FROM EMP01;

-- 3. emp01의 모든 사원의 입사일을 오늘로 바꿔주세요
UPDATE EMP01 SET HIREDATE = SYSDATE();

SELECT *
FROM EMP01;

-- 4. 급여가 3000이상인 사원의 급여만 10%인상
UPDATE EMP01 SET SAL = SAL * 1.1 WHERE SAL >= 3000;

SELECT *
FROM EMP01;

-- 5.? emp01 table 사원의 급여가 1000이상인 사원들의 급여만 500원씩 삭감
UPDATE EMP01 SET SAL = SAL - 500 WHERE SAL >= 1000;

SELECT *
FROM EMP01;

-- 6. emp01 table에 DALLAS(dept의 loc)에 위치한 부서의 소속 사원들의 급여를 1000인상

-- 7. emp01 table의 SMITH 사원의 부서 번호를 30으로, 직급은 MANAGER 수정

-- 10. emp01 table에서 comm 존재 자체가 없는(null) 사원 모두 삭제
SELECT ENAME, COMM
FROM EMP01
WHERE COMM IS NULL;

DELETE FROM EMP01
WHERE COMM IS NULL;

SELECT *
FROM EMP01;

ROLLBACK;

-- 11. emp01 table에서 comm이 null이 아닌 사원 모두 삭제
DELETE FROM EMP01
WHERE COMM IS NOT NULL;

-- 12. emp01 table에서 부서명이 RESEARCH 부서에 소속된 사원 삭제
SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'RESEARCH';

DELETE FROM EMP01
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'RESEARCH');

SELECT *
FROM EMP01;

ROLLBACK;

-- 13. table내용 삭제
SELECT *
FROM EMP01;

DELETE FROM EMP01;

SELECT *
FROM EMP01;

ROLLBACK;

SELECT *
FROM EMP01;

-- TRUNCATE는 삭제된 데이터가 ROLLBACK 되지 않는다
TRUNCATE TABLE EMP01;

SELECT *
FROM EMP01;

ROLLBACK;

SELECT *
FROM EMP01;

-- 6. emp01과 dept01 table 생성
-- EMP --> EMP01 / DEPT --> DEPT01
DROP TABLE EMP01;
DROP TABLE DEPT01;

CREATE TABLE EMP01 AS SELECT FROM EMP;
CREATE TABLE DEPT01 AS SELECT FROM DEPT;

SELECT *
FROM EMP01;

SELECT *
FROM DEPT01;

SELECT * FROM information_schema.table_constraints
WHERE constraint_schema = 'scott';

-- 7. 이미 존재하는 table에 제약조건 추가하는 명령어
-- dept01 : 주(부모), emp01 : 종(자식)

-- ? emp01에 제약조건 추가해 보기
ALTER TABLE EMP01
ADD CONSTRAINT emp01_deptno_fk FOREIGN KEY (deptno) REFERENCES dept01 (deptno);

-- 에러 발생 이유 : 복사해온 emp01과 dept01 테이블co에는 PK 지정이 되어 있지 않음
-- 따라서, 그 어떤 테이블에도 FK 값을 줄 수 없음
-- 해결 방법 : emp01 - empno / dept01 - deptno 가 먼저 PK로 지정 된 이후에 FK를 지정 하면 해결 가능

ALTER TABLE emp01
ADD CONSTRAINT PRIMARY KEY (empno);

ALTER TABLE dept01
ADD CONSTRAINT PRIMARY KEY (deptno);

SELECT * FROM information_schema.table_constraints
WHERE constraint_schema = 'scott';

ALTER TABLE emp01
ADD CONSTRAINT emp01_deptno_fk FOREIGN KEY (deptno) REFERENCES dept01 (deptno);

SELECT * FROM information_schema.table_constraints
WHERE constraint_schema = 'scott';

-- 이를 해결하기 위해서는 참조하는 테이블(EMP01)에서 FOREIGN KEY 제약조건에 추가 설정을 해줘야 함!
-- 1. ON DELETE : 참조되는 테이블의 값이 삭제 될 때 동작하는 설정
-- 2. ON UPDATE : 참조되는 테이블의 값이 수정 될 때 동작하는 설정
/*

RESTRICT : 참조 테이블에 데이터가 있으면, 참조 되는 테이블의 데이터를 삭제, 수정 X
CASCADE : 참조 테이블에 데이터를 수정 혹은 삭제하면, 참조 하는 테이블의 데이터도 수정 혹은 삭제 O
SET NULL : 참조 테이블에 데이터를 수정 혹은 삭제하면, 참조 하는 테이블의 데이터는 NULL
SET DEFAULT : 참조 테이블에 데이터를 수정 혹은 삭제하면, 참조 하는 테이블의 데이터는 DEFAULT값
*/

-- 4. v_dept02에 crud : v_dep02와 dept02 table 변화 동시 검색
CREATE VIEW v_dept02 AS SELECT * FROM dept02;

DESC v_dept02;

SELECT *
FROM v_dept02;

-- 10.view.sql
/*
view 사용을 위한 필수 선행 설정
1단계 : admin 계정으로 접속
2단계 : view 생성해도 되는 사용자 계정에게 생성 권한 부여

view 에 대한 학습
- 물리적으로는 미 존재, 단 논리적으로 존재
- 물리적(create table)
- 논리적(존재하는 table들에 종속적인 가상 table)
개념
- 보안을 고려해야 하는 table의 특정 컬럼값 은닉
  또는 여러개의 table의 조인된 데이터를 다수 활용을 해야 할 경우
  특정 컬럼 은닉, 다수 table 조인된 결과의 새로운 테이블 자체를
  가상으로 db내에 생성시킬수 있는 기법
문법
- create와 drop : create view/drop view
- crud는 table과 동일
종류
5-1. 단일 view : 별도의 조인 없이 하나의 table로 부터 파생된 view
5-2. 복합 view : 다수의 table에 조인 작업의 결과값을 보유하는 view
5-3. 인라인 view : sql의 from 절에 view 문장
실습 table
-dept01 table생성 -> dept01_v view 를 생성 -> crud -> view select/dept01 select
*/

-- 1. test table생성
CREATE TABLE dept02 AS SELECT * FROM DEPT;

-- 2. dept01 table상의 view를 생성
-- SCOTT 계정으로 view 생성 권한 받은 직후에만 가능
CREATE VIEW v_dept02 AS SELECT * FROM dept02;

DESC v_dept02;

SELECT *
FROM v_dept02;

DROP VIEW v_dept02;

-- 3. ? emp table에서 comm을 제외한 v_emp01 라는 view 생성
DROP VIEW v_emp01;

CREATE VIEW v_emp01 AS SELECT EMPNO, ENAME, SAL FROM emp;

SELECT *
FROM v_emp01;

-- 4. v_dept02에 crud : v_dep02와 dept02 table 변화 동시 검색
CREATE VIEW v_dept02 AS SELECT * FROM dept02;

DESC v_dept02;

SELECT *
FROM v_dept02;

-- TEST
-- STEP01 : INSERT
SELECT *
FROM v_dept02;

INSERT INTO v_dept02 VALUES (50, 'DEV', 'MOON');

SELECT *
FROM v_dept02;

SELECT *
FROM dept02;

-- DELETE
DELETE FROM v_dept02
WHERE deptno = 50;

SELECT *
FROM v_dept02;

SELECT *
FROM dept02;

-- 5. 모든 end user가 빈번히 사용하는 sql문장으로
-- "해당 직원의 모든 정보 검색(empno, ename, deptno, loc)"하기
/* 개발 방법

두개의 join 필수
방법1 : 필요시 늘 join하는 sql문장 실행
방법2 : 이미 조인된 구조의 view를 생성 해 놓고, 필요시 view만 select */

CREATE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT *
FROM v_emp_dept;

-- 6. 논리적인 가상의 table이 어떤 구조로 되어 있는지 확인 가능한 oracle 자체 table
-- view는 text 기반으로 명령어가 저장
-- view 에 대한 정보

SELECT *
FROM information_schema.views
WHERE table_schema = 'scott';

-- 원본 테이블(dept02)을 삭제하면?? view는 어떻게 될까??
-- UPDATE
SELECT *
FROM dept02;

UPDATE dept02 SET DEPTNO = 50
WHERE LOC = 'NEW YORK';

SELECT *
FROM dept02;

SELECT *
FROM v_dept02;

-- 테이블 삭제
USE scott;
DROP TABLE dept02;

DESC dept02;

SELECT *
FROM v_dept02;

-- VIEW의 장점
-- 1. 보안
-- 2. 복잡한 쿼리 단순화 결과만을 확인

SELECT *
FROM information_schema.views
WHERE table_schema = 'scott';

-- 이전에 이야기 했지만 원래 원본과는 분리하여 사용하기 위한 용도로 VIEW 이용함
-- 그런데, 현재 실습에서는 원본과 VIEW가 연동이 너무나 잘 되어 있음
-- 그 이유는, SCOTT이 VIEW의 모든 권한을 부여 받은 계정이 이기 때문에 가능함
-- 원래 현업에서는 이러한 부분까지 업무별로 권한을 나눠 가지게 되어, 수정이 불가능 하도록 하는것이 원칙

정혜지

오히려 좋아

이전 포스트

0905 mySQL dbms

다음 포스트

0907 SQL

0905 mySQL dbms

0907 SQL

0개의 댓글