4월 5일 (1) DB - DATE, 셀프 조인, view, MySQL 내장 함수

오류가 나는 것을 방지하는 역할

 

IF EXISTS : '만약에 존재한다면', DROP 시 사용하는 구문.

테이블 삭제할 경우 DROP TABLE IF EXISTS 테이블명;

 

IF NOT EXISTS : '만약에 존재하지 않는다면', CREATE 시 사용하는 구문

테이블 생성할 경우 CREATE TABLE IF NOT EXISTE 테이블명 (......);

 

예) 주의 표시가 뜬다.

 

날짜와 시간의 기본값 설정 방법 CREATE TABLE 

날짜와 시간 입력 형식 : 'YYYY-MM-DD hh:mm:ss'

 

데이터가 입력되는 시점의 날짜와 시간을 처리하는 함수

- now() 

INSERT문 사용, DATETIME 타입에서는 기본값으로 사용가능

 

 

1) DATE 타입

컬럼명 DATE DEFAULT(CURRENT_DATE) 또는 / 특수한 예약어

컬럼명 DATE DEFAULT(curdate())

 

2) TIME 타입

컬럼명 TIME DEFAULT (CURRENT_TIME) 또는

컬럼명 TIME DEFAULT (curtime())

 

3) DATETIME 타입

컬럼명 DATETIME DEFAULT CURRENT_TIMESTAMP 또는

컬럼명 DATETIME DEAAULT (CURRENT_TIME) 또는

컬럼명 DATETIME DEAAULT now()

 

DROP TABLE IF EXISTS date_tb1;

CREATE TABLE IF NOT EXISTS date_tb1 (
	dno INT AUTO_INCREMENT PRIMARY KEY, 
    ddate DATE DEFAULT (curdate()),
    dtime TIME DEFAULT (CURRENT_TIME),
    ddt DATETIME DEFAULT now()
);

INSERT INTO date_tb1
VALUES (NULL, '2023-04-05',  '09:01:10', '2023-04-05 09:01:10');

INSERT INTO date_tb1
VALUES (NULL, now(), now(), now());

INSERT INTO date_tb1 (dno, ddate)
VALUES (NULL, DEFAULT);

 

셀프조인(SELF JOIN)

하나의 테이블을 두 개의 테이블인 것처럼 취급하는 조인

 

예) 추천회원, (조직도 상의)부서장 정보를 조회

SELECT e.ename 부서장, a.ename 사원
FROM emp e, emp a
WHERE e.empno = a.mgr
ORDER BY e.ename;

 

 

 

FROM 절에 위치하는 서브쿼리(Inline View)

서브 쿼리의 결과를 가상의 테이블로 보고 조인

 

-- inline view, from 절 위치하는 서브쿼리
-- 10부서에서 근무하는 사원명과 부서명
SELECT e.ename, d.dname
FROM emp e JOIN (
		SELECT deptno, dname
		FROM dept
        WHERE deptno = 10) d
ON e.deptno = d.deptno
;

SELECT empno, ename, sal  FROM emp;

 

뷰 View

데이터 베이스에 실존하지 않는 가상의 테이블. 링크만 연결되어 있다고 봐야 한다. 원본의 변경 내용은 반영이 된다. 

테이블의 링크로 데이터를 보여주는 역할을 담당, 뷰는 SELECT를 목적으로 하며, 삽입, 수정, 삭제는 불가 

특정 테이블의 일부 컬럼을 모으거나, 여러 테이블의 조인 결과를 뷰로 만들면 실행 쿼리문이 간결(단순화)해 지는 효과가 있다.

 

특징 

1. 특정 사용자에게 테이블의 전체가 아닌 필요한 컬럼만 보여주도록 제한할 수 있다.

2. 복잡한 쿼리문을 단순화할 수 있다.

3. 쿼리를 제사용할 수 있다.

4. 원본 테이블의 내용이 변경되면 자동으로 뷰의 내용도 변경된다.

 

제한사항

1. 한번 정의한(생성한) 뷰는 변경이 안 된다. - 삭제 후 다시 생성

2. 삽입, 수정, 삭제가 안 된다. - 될 수도 있으나 하지 말자!

 

뷰생성 문법 (뷰는 SELECT의 결과를 테이블화 시키는 것)

CREATE [OR REPLACE] VIEW 뷰이름 AS

SELECT 컬럼1, 컬럼2, ........

FROM 테이블명

WHERE 조건

 

CREATE OR REPLACE :  생성하거나 대체해라

같은 이름의 뷰가 없으면 뷰를 생성

같은 이름의 뷰가 있으면 뷰를 수정(삭제 후 재생성)

 

뷰삭제

DROP VIEW 뷰이름;  

SELECT empno, ename, sal  FROM emp;

CREATE OR REPLACE VIEW emp_view AS 
SELECT empno 사번, ename 이름, deptno 부서번호
FROM emp
;

SELECT * FROM  emp_view;
SELECT empno 사번, ename 이름, deptno 부서번호
FROM emp;

 

CREATE OR REPLACE VIEW emp_mgr AS 
SELECT e.emp_no, e.first_name, e.last_name, 
		d.dept_name
FROM employees e JOIN dept_manager dm
	ON e.emp_no = dm.emp_no
    JOIN departments d
    ON dm.dept_no = d.dept_no
WHERE dm.to_date LIKE '9999%';

SELECT * FROM emp_mgr;

-- 뷰 삭제
DROP VIEW IF EXISTS emp_mgr;

 

뷰 생성에서 IF NOT EXISTS 보다는 OR REPLACE 사용

뷰 삭제 시에는 IF EXISTS 사용

 

일반적으로 오류와 경고가 발생할 때의 조치, 오류는 반드시 해결해야 함, 경고는 무시

 

SQL 스크립트 파일 작성 요령

1. 위쪽에 삭제 명령문을 작성.(테이블, 뷰 등)

    삭제 순서에 주의 1) 뷰, 2) 외래키를 가진 테이블, 3) 일반테이블

 

2. 생성 명령문을 작성(테이블, 뷰 등)

    생성 순서에 주의 1) 일반테이블, 2) 외래키를 가진 테이블, 3) 뷰

 

3. 삽입 명령문을 작성(INSERT)

 

4. COMMIT 명령문 작성

 

My SQL  내장함수

DB 관리를 위해 사용하는 MySQL 전용 함수들

 

형변환 함수 : CAST(), CONVERT()

CAST(expr AS type)

expr : 변환할 값 또는 컬럼

 

 

CONVERT(expr, type)

 

-- 내장함수
USE dev_db;
SELECT avg(sal) FROM emp;
-- 평균값을 정수로 변환(실수 -> 정수)
SELECT CAST(avg(sal) AS SIGNED INTEGER) 평균급여 FROM emp;

-- 문자열 -> 날짜 형식
SELECT CONVERT('2023-04-05', DATE) 날짜;
-- 숫자 -> 날짜 형식
SELECT CONVERT(20230405, DATE) 날짜;

-- 날짜 -> 숫자
SELECT CAST(now() AS SIGNED) 날짜숫자;

-- 날짜 -> 문자열
SELECT CONVERT(now(), CHAR) 날짜문자;

-- 숫자 -> 문자열
SELECT CAST(avg(sal) AS CHAR) 평균문자 FROM emp;

 

문자열 결합 함수

CONCAT(str1, str2, ......)

SELECT first_name, last_name
FROM employees;

SELECT CONCAT(first_name, ' ',  last_name) NAME
FROM employees;

SELECT CONCAT(first_name, ' ', last_name, ' ', birth_date) info
FROM employees;

 

CONCAT_WS('구분자', str1, str2, str3, .......)

문장들 사이에 '구분자'가 들어간다.

 

SELECT CONCAT_WS(' ', first_name, last_name, birth_date) info
FROM employees;