yumyum
DB 본문
### 테이블 관리, 생성
1. 데이트베이스 생성 : 왼쪽 상자 > 새로생성 > 데이터베이스 > 이름 지정
2. 테이블 생성 : 생성한 데이터베이스 우클릭 > 새로생성 > 테이블
=> 테이블에서 데이터 유형 Null 허용 등 옵션값을 부여할 수 있다
### SQL
DML (데이터 조작)//테이블 안의 데이터
INSERT삽입
UPDATE수정
SELECT-조회
DELETE-삭제
DDL(데이터 정의)//테이블
CREATE -생성
ALTER-수정
DROP-삭제
DCL(데이터 제어)//DB
GRANT-허용
REVOKE-수정
DENY-거부
### 쿼리에서 테이블 관리, 생성
SELECT * FROM memberTBL ;
# SELECT 열이름(*은 모든걸 다 본다) FROM 테이블이름
SELECT memberID, memberName FROM memberTBL ;
SELECT memberName, memberID FROM memberTBL ;
# 열이름 적는 순서 = 출력순서
SELECT * FROM memberTBL WHERE memberName='지운이';
#WHERE 조건절 // 조건 열이름 = '조건내용'
# Quiz) 조건절이용해서 한주연| Han | 주소 나오게 출력
SELECT memberName,memberID,memberAddress FROM memberTBL WHERE memberName='한주연';
### 테이블 생성
CREATE TABLE `my testTBL` (id INT) ;
SELECT * FROM `my testTBL` ;
# 백틱 ~표 밑에 있는 문자
# 열이름 띄어씌기 할 때 사용 // 열이름 띄어쓰기 없는걸 추천
-- DB에서의 원래 주석처리
# 샾을 많이 쓰니까 지원해주기 시작
DROP TABLE `my testTBL` ;
# 생성한 테이블 삭제
# Quiz) 열이 2개 이상인 테이블 만들고, 데이터 삽입 3개 이상
CREATE TABLE `yulim testTBL` (color varchar(14), number INT) ;
SELECT * FROM `yulim testTBL` ;
INSERT INTO `yulim testtbl` (color,number)
VALUES ('yellow', 3),('blue',2);
### View(뷰) 테이블 만들기
# 원하는 열만 테이블로 만들어 보여주기
# 민감한 데이터 숨기기
CREATE VIEW v_memverTBL
AS SELECT memberName, memberAddress FROM membertbl ;
SELECT * FROM v_memverTBL ;
# 기존 테이블에서 원하는 열만 SELECT구문으로 출력
# 출력문을 AS -> 뷰테이블 생성
# 뷰테이블은 저장공간 소모 x
# 바로가기, 소프트링크와 비슷한 의미
### 환경변수
환경변수 설정 : sysdm.cpl > 고급 > 환경변수 (경로를 지정할경우 path에 들어가서 새로 추가해주면 된다)
MariaDB접속 방법 : Window powershell 관리자모드 > cd employees > mysql -u root -p 해서 MariaDB 접속 가능
** Records : 열
HeidiSQL 에 employees를 불러오기 : source employees.sql 로 C드라이브에 있는 파일 가져오기
HeidiSQL에서 가져온 모습
### 구매데이터베이스 만들기
DROP DATABASE IF EXISTS sqlDB ; # 만약에 sqlDB가 존재하면 삭제하고 작업
CREATE DATABASE sqlDB ;
USE sqlDB ;
CREATE TABLE userTBL
(userID CHAR(8) NOT NULL PRIMARY KEY, # 사용자 아이디 (pk)
name VARCHAR(10) NOT NULL, # 이름
birthYear INT NOT NULL, # 출생년도
addr CHAR(2) NOT NULL, # 지역 (서울, 경북, 경남)
mobile1 CHAR(3), # 국번 (02,070,080,010)
mobile2 CHAR(8), # 나머지 (하이픈 제외)
height SMALLINT, # 키, 데이터 저장 공간 절약
mDate DATE # 가입일 yyyy-mm-dd
);
CREATE TABLE buyTBL
(num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, # 순번 시리얼 넘버(하나씩 자동증가)(PK)
userID CHAR(8) NOT NULL, # 고객 아이디
prodName CHAR(6) NOT NULL, # 물품명
groupName CHAR(4), # 분류
price INT NOT NULL, # 단가
amount SMALLINT NOT NULL, # 재고
FOREIGN KEY(userID) REFERENCES userTBL(userID) # userID로 연결
);
=> 만들어진 모습
### INSERT INTO로 데이터값 삽입
SELECT * FROM 명령어로 데이터에 넣은 값들을 쿼리에서 바로 확인 할 수가 있다.
@ 만약 INSERT 과정에서 오류가 발생한다면, SET FOREIGN_KEY_CHECKS=0; 코드로 비활성화 한후,
테이블들의 기본조합을 utf8_general_ci로 바꿔준다
SET FOREIGN_KEY_CHECKS=1; 로 다시 활성화해주면 INSERT의 오류를 해결할 수 있다
### WHERE 조건절, 서브쿼리 , AND/OR, MAX/MIN, AVG, COUNT, SUM,LIKE, BETWEEN, IN, DISTINCT
USE employees ;
SELECT * FROM employees ;
# employees DB사용한다는 내용생략
# USE employees 사용하겠다고 미리 선언
SELECT * FROM employees.titles ;
SELECT * from titles ;
# employees.titles // DB이름.테이블 이름
SELECT first_name, last_name, gender FROM employees;
SELECT last_name, first_name, gender FROM employees;
# 열이름 순서가 출력물의 순서
# 주석
-- 주석
/*
여러줄
주석
처리
*/
SHOW TABLES ;
# 현재 DB가 갖고있는 테이블 정보 조회
SHOW TABLE STATUS ;
# 용량, 업데이트 날짜, 길이 등등
USE sqlDB ;
SELECT * FROM userTBL ;
# WHERE 조건절
# 검색할 열이름 = '조건'
# 순서주의 !!!
# 관계연산자 사용
SELECT * FROM userTBL WHERE height >= 182 ;
# 조건 1개일 때
SELECT * FROM userTBL WHERE birthYear >= 1970 AND height >= 182 ;
SELECT * FROM userTBL WHERE height >= 182 AND birthYear >= 1970 ;
# 조건 2개 이상일 때 AND, or 사용
# 조건의 순서는 안중요함
# BETWEEN
SELECT * FROM userTBL WHERE height >= 180 AND height <= 182 ;
SELECT * FROM userTBL WHERE height BETWEEN 180 AND 182 ;
# 사이값 출력, 결과는 같다
# 연속된값, 숫자에서 사용
# IN
SELECT * FROM userTBL WHERE addr='경북'OR addr='경남'OR addr='전남'
SELECT * FROM userTBL WHERE addr IN ('경북','경남','전남')
# 조건이 문자열일때 IN 사용
# LIKE
SELECT * FROM userTBL WHERE name LIKE '김%'
# %글자의 수 상관없이 전부. * 비슷한 의미
SELECT * FROM userTBL WHERE name LIKE '_종신'
# _글자 수 만큼 언더바로 표현
# Quiz ) employees DB. -> 남자, 입사일 1990 이후
성이 M으로 시작
USE employees ;
SELECT * FROM employees WHERE gender='M' AND hire_date >= '1990-01-01' AND last_name LIKE 'M%' ;
# 서브쿼리
# 조건을 쿼리문으로 작성
# 메인쿼리문 안에 서브쿼리문
USE sqlDB ;
SELECT NAME,height FROM userTBL WHERE height > 177 ;
SELECT NAME,height FROM userTBL
WHERE height > (SELECT height FROM userTBL WHERE NAME='김경호');
# 위 아래 결과가 같음
# DB => 실시간으로 값이 바뀜
# 평균값, 최대값, 최소값, 중위값, 등등 변하는 값에 대응
SELECT NAME,height FROM userTBL
WHERE height > (SELECT height FROM userTBL WHERE ADDR='경남');
# 서브(하위)쿼리에 결과가 2개 나와서 에러 발생
SELECT NAME,height FROM userTBL
WHERE height > ANY (SELECT height FROM userTBL WHERE ADDR='경남');
# 서브쿼리 앞에 ANY 옵션 설정
# 둘중에 작은 값을 결과로 인식
# height > 170 ANd height > 173
# ALL
SELECT NAME,height FROM userTBL
WHERE height > ALL (SELECT height FROM userTBL WHERE ADDR='경남');
# 결과를 모두 만족
# 큰 값을 결과로 인식
# height > 170 AND height > 173
SELECT NAME,height FROM userTBL
WHERE height = ANY (SELECT height FROM userTBL WHERE ADDR='경남');
SELECT NAME,height FROM userTBL
WHERE height IN (SELECT height FROM userTBL WHERE ADDR='경남');
# 관계연산자에 따라 결과가 달라짐
# = ANY // IN 의 결과가 같음
# ORDER BY (정렬)
SELECT NAME, mDate FROM userTBL ORDER BY mDate ;
# 날짜 기준으로 오름차순(디폴트 값)
SELECT NAME, mDate FROM userTBL ORDER BY DESC ;
# 내림차순 DESC
SELECT NAME, mDate FROM userTBL ORDER BY ASC ;
# 오름차순 ASC
# Quiz) employees => 생일순 정렬(내림) / 1989년 이후 입사일 (서브쿼리)
USE employees ;
SELECT * FROM employees WHERE hire_date >= '1989-01-01' ORDER BY birth_date DESC
SELECT * FROM employees WHERE hire_date > ALL (SELECT hire_date FROM employees WHERE hire_date = '1989-01-01')ORDER BY birth_date DESC;
SELECT * FROM employees WHERE hire_date > ALL (SELECT AVG(hire_date) FROM employees )ORDER BY birth_date DESC;
USE sqlDB ;
# Distinct (중복제거)
SELECT addr FROM userTBL ORDER BY addr ;
# 10개 밖에 안됨, 중복빼고 갯수 세는게 어렵다
SELECT DISTINCT addr FROM userTBL ORDER BY addr ;
# 열 이름 앞에 DISTINCT 위치 중요 !!!
# LIMIT (출력갯수 조절)
# 내용이 너무 많을때 테스트 용도로 쓴다
SELECT emp_no, hire_date FROM employees ORDER BY hire_date ;
SELECT emp_no, hire_date FROM employees ORDER BY hire_date LIMIT 10;
# 쿼리구문 테스트 할때 리미트로 제한하여 DB서버에 부담 줄이기
# CREATE로 테이블 복사
CREATE TABLE buytbl2 (SELECT * FROM buyTBL) ;
SELECT * FROM buytbl2 ;
# 제약조건, 키등등의 정보는 안가져옴
CREATE TABLE buytbl3 (SELECT userID, prodName, price*amount FROM buytbl) ;
SELECT * FROM buytbl3
# 원하는 열만 복붙 가능
# GROUP BY 절
SELECT userID, amount FROM buytbl ORDER BY userID ;
# 집계 함수
# AVG(평균), MIN(최소값), MAX(최대값), COUNT(갯수), SUM(합)
# COUNT(DISTINCT)(행의갯수, 중복은 하나만 인정), STDEV(표준편차), VAR_SAMP(분산)
SELECT userID FROM buytbl GROUP BY userID ;
# 여러개 중복값을 하나로 묶어주는 효과
SELECT userID, SUM(amount) FROM buytbl GROUP BY userID ;
SELECT userID, AVG(amount) FROM buytbl GROUP BY userID ;
SELECT userID, MIN(amount) FROM buytbl GROUP BY userID ;
SELECT userID, MAX(amount) FROM buytbl GROUP BY userID ;
SELECT userID, STD(amount) FROM buytbl GROUP BY userID ;
SELECT AVG(amount) AS '평균 구매 갯수' FROM buytbl ;
# AS : Alias => 별칭 // 출력물 이쁘게
# 집계함수를 쓰기위해 group by 쓰는건 x
# 집계 함수의 활용
SELECT NAME, MAX(height), MIN(height) FROM usertbl ;
# => 틀린함수, 원하는 값이 안나옴
SELECT NAME, MAX(height), MIN(height) FROM usertbl GROUP BY NAME;
# => 틀린함수
# => 서브쿼리 사용하여 해결
SELECT NAME, height FROM usertbl
WHERE height = (SELECT MAX(height) FROM userTBL)
OR height = (select MIN(height) FROM usertbl);
# 서브쿼리를 이용해 최대값, 최소값 출력
SELECT COUNT(*) FROM usertbl ;
SELECT COUNT(mobile1) FROM usertbl ;
# null 값은 카운트 안함
# Quiz) employees => 생일 순 정렬(내림차순) /사번 전체평균보다 최근 입사자만
# Quiz) salaries => 연봉 평균보다 많이 받는 사람 출력 // 사번, f이름, l이름, 입사일, 연봉
USE employees ;
CREATE TABLE employees2 (SELECT * FROM employees) ;
SELECT * FROM employees WHERE emp_no > ALL (SELECT AVG(emp_no) FROM employees )ORDER BY birth_date DESC
SELECT employees.emp_no, employees.first_name, employees.last_name, employees.hire_date, salaries.salary
FROM employees INNER JOIN salaries on employees.emp_no = salaries.emp_no
WHERE salary > ALL (SELECT AVG(salary) FROM salaries)
AND to_date >= ALL (SELECT MAX(to_date) FROM salaries) ;### HAVING
### HAVING
SELECT userID AS "사용자", SUM(price*amount) AS "총구매액"
FROM buyTBL # WHERE SUM(price*amount) > 1000
GROUP BY userID
# => 안되는 코드-> 오류 -> HAVING 절 쓰기
HAVING SUM(price*amount) > 1000 ;
# 집계함수에 조건 적용할때 사용
# GROUP BY랑 세트, 위치 조심!!!
# QUIZ) from_date => 1985-02-07 // 연봉이 평균보다 높은 사람들
SELECT emp_no, salary, from_date
FROM salaries
WHERE from_date = '1985-02-07'
GROUP BY emp_no
HAVING salary >= (SELECT AVG(salary)FROM salaries)
ORDER BY emp_no ;### ROLLUP
### ROLLUP
# 항목별 소합계
SELECT num, groupName, SUM(price*amount) AS '비용'
FROM buyTBL
GROUP BY groupName, num
WITH ROLLUP ;### INSERT
### INSERT
# INSERT INTO 테이블이름 VALUES 값1, 값2 ,,, (테이블이 갖고있는 열 갯수)
CREATE TABLE testTBL1 (id INT, userName CHAR(3), age INT) ;
# 제약조건 없는거 확인
INSERT INTO testTBL1 VALUES(1, '홍길동', 2500) ;
# 기본입력
INSERT INTO testTBL1(id,userName) VALUES (2, '설현') ;
# NULL 값이 있을 때 입력 방법 // 데이터 입력할 열 이름 적어준다
INSERT INTO testTBL1(userName,age,id) VALUES ('초아',30,3) ;
# 입력 순서 바꿀 수 있음
SELECT * FROM testTBL1### AUTO_INCREMENT
CREATE TABLE testTBL2
(id INT AUTO_INCREMENT PRIMARY KEY,
userName CHAR(10), age INT ) ;
INSERT INTO testTBL2 VALUES (NULL, 'JIMIN', 29) ;
INSERT INTO testTBL2 VALUES (NULL, 'YOONA', 28) ;
INSERT INTO testTBL2 VALUES (NULL, 'YOOKYUNG', 27) ;
SELECT * FROM testtbl2 ;### ALTER (수정)
# ALTER (수정)
ALTER TABLE testtbl2 AUTO_INCREMENT=100 ;
# 자동으로 1씩 증가, 시작값 100
INSERT INTO testtbl2 VALUES (NULL, 'CHANBI',27) ;
INSERT INTO testtbl2 VALUES (NULL, 'YULIM' ,26) ;
SELECT * FROM testtbl2### 증가값 변경
# 증가값 변경
SET @@auto_increment_increment=3 ;
# DB서버 -> DBMS -> MariaDB 변수 수정
INSERT INTO testtbl2 VALUES (NULL, 'NAYEOUN',25) ;
INSERT INTO testtbl2 VALUES (NULL, 'JUNYEON',23) ;
INSERT INTO testtbl2 VALUES (NULL, 'MOMO',24) ;
SELECT * FROM testtbl2;
# 한번에 여러개 항목값 입력
INSERT INTO testtbl2(userName,age) VALUES ('JUNSOO',28),('DONGHYUK',25),('YOUNGJAE',30) ;
SELECT * FROM testtbl2 ;
# UPDATE
# 테이블 데이터 수정
UPDATE testtbl2 SET age = 25 WHERE userName = 'YULIM'
# UPDATE 테이블 이름 SET 변경내용 WHERE 필수 !!!
# WHERE절 없으면 데이터 전체 수정 됨 ㅠㅠ
SELECT * FROM testtbl2 ;
# QUIZ) n_salary 테이블로 복붙
# n_salary => salary 사람별 높은 연봉 -> 나머지 지우기 -> 나머지 옛날 연봉 지우기
CREATE TABLE n_salary (SELECT * FROM salaries WHERE to_date = '9999-01-01' ) ;### 삭제
# 삭제할 항목 생성
CREATE TABLE bigtbl1 (SELECT emp_no, first_name, last_name FROM employees.employees) ;
CREATE TABLE bigtbl2 (SELECT emp_no, first_name, last_name FROM employees.employees) ;
CREATE TABLE bigtbl3 (SELECT emp_no, first_name, last_name FROM employees.employees) ;
SELECT * FROM bigtbl1;
SELECT * FROM bigtbl2;
SELECT * FROM bigtbl3;
DELETE FROM bigTBL1 ;
#테이블 내용삭제 (데이터 삭제)
# 트랜젝션, 로그에 기록 // 실수로 삭제해도 로그에 기록되어 복구 가능 (휴지통과 비슷) //
TRUNCATE TABLE bigtbl3 ;
# 트랜젝션 x // 복구 안됨 (바로삭제)
DROP TABLE bigTBL2 ;
# 테이블 삭제### 날짜 함수
### 날짜 함수
SELECT ADDDATE('2023-11-03', INTERVAL 31 DAY),
ADDDATE('2023-11-03', INTERVAL 1 MONTH);
SELECT CURDATE(), YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE());
SELECT HOUR(CURTIME()), MINUTE(CURTIME()), SECOND(CURTIME());
# CURDATE() : 현재 날짜 => 년-월-일
# CURTIME() : 현재 시간 => 시 : 분 : 초
SELECT DATEDIFF (NOW(),'2022-04-12') ;
# 날짜 D-DAY### 순위함수
### 순위 함수
SELECT RANK() OVER(ORDER BY height DESC) "키순위", NAME, addr, height FROM usertbl ;
# 공동 순위는 건너띄고 순위 매김### 피벗
###피벗
CREATE TABLE pivotTBL
(uname CHAR(20),
season CHAR(20),
amount INT);
INSERT INTO pivotTBL VALUES
('KIMBUMSOO','WINTER',10),('YOONJONGSHIN','SUMMER',15),('KIMBUMSOO','FALL',25),
('KIMBUMSOO','SPRING',3),('KIMBUMSOO','SPRING',37),('YOONJONGSHIN','WINTER',40),
('KIMBUMSOO','SUMMER',14),('KIMNUMSOO','WINTER',22),('YOONJONGSHIN','SUMMER',64) ;
SELECT * FROM pivottbl
# => 보기 쉽게 피벗
SELECT uname,
SUM(IF(season='SPRING', amount, 0)) AS 'SPRING',
SUM(IF(season='SUMMER', amount, 0)) AS 'SUMMER',
SUM(IF(season='FALL', amount, 0)) AS 'FALL',
SUM(IF(season='WINTER', amount, 0)) AS 'WINTER',
SUM(amount) AS 'SUM'
FROM pivottbl GROUP BY uname ;
SELECT * FROM pivottbl ;### JOIN
### JOIN
# INNER JOIN
SELECT * FROM buytbl
INNER JOIN userTBL
ON buytbl.userID = usertbl.userID # => 겹치는 항목으로 연결
WHERE buytbl.userID = 'jyp' ; # => jyp만 출력됨
# 두 테이블 모든 열 출력
SELECT buyTBL.userID, NAME, prodname, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM buytbl
INNER JOIN userTBL
ON buytbl.userID = usertbl.userID
# 겹치는 열이름은 테이블 명까지 명확하게 적어주자!!
# 요즘엔 INNER JOIN 안씀
SELECT B.userID, U.NAME, B.prodname, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buytbl B
INNER JOIN usertbl U
ON B.userID = U.userID
# 명확하게 써줄려면 TBL네임 같이 적어줘야하는데,
# 너무 많으면 ALIAS 걸어서 줄여쓰기 가능
################################################
# 3개 테이블 조인
CREATE TABLE stdTbl
(uname CHAR(20) PRIMARY KEY,
AREA CHAR(20));
INSERT INTO stdtbl VALUES
('KIMBUMSOO','KYUNGNAM'),('SEONGSIKYUNG','SEOUL'),('JOYONGPIL','KYEONGKI'),('EUNJIWON','KYUNGBOOK'),('BABIKIM','SEOUL') ;
SELECT * FROM stdtbl
CREATE TABLE stdclubTbl
(num INT PRIMARY KEY,
uname CHAR(20),
club CHAR(20),FOREIGN KEY(uname) REFERENCES stdTbl (uname),
FOREIGN KEY(club) REFERENCES clubTbl (club));
INSERT INTO stdclubTbl VALUES
(1,'KIMBUMSOO','CHESS'),(2,'KIMBUMSOO','SOCCER'),(3,'JOYONGPIL','SOCCER'),
(4,'EUNJIWON','SOCCER'),(5,'EUNJIWON','VOLUNTEER'),(6,'BABIKIM','VOLUNTEER') ;
SELECT * FROM stdclubTbl
CREATE TABLE clubTbl
(club CHAR(20) PRIMARY KEY,
room CHAR(20));
INSERT INTO clubTbl VALUES
('SWIMMING','101'),('CHESS','102'),('SOCCER','103'),('VOLUNTEER','104');
SELECT * FROM clubTbl
SELECT S.uname, S.AREA, C.club, C.room
FROM stdtbl S
INNER JOIN stdclubtbl SC
ON S.uname = SC.uname
INNER JOIN clubTbl C
ON SC.club = C.club
ORDER BY S.uname;
SELECT * FROM stdtbl ;
SELECT * FROM clubtbl ;
SELECT * FROM stdclubtbl ;
# OUTER JOIN
# 잘 안씀
# 조건에 만족되지 않는 행도 포함 함
# (LEFT, RIGHT, FULL) OUTER JOIN 두번째 테이블 명
SELECT buyTBL.userID, NAME, prodname, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM userTBL
LEFT OUTER JOIN buyTBL
ON buytbl.userID = usertbl.userID
ORDER BY userID ;
# 구매이력이 없는 데이터도 출력(NULL값까지 출력 )
SELECT buyTBL.userID, NAME, prodname, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM userTBL
LEFT JOIN buyTBL
ON buytbl.userID = usertbl.userID
ORDER BY userID ;
# => outer 없이 left 약자로 줄여서 사용할 수 있다
SELECT buyTBL.userID, NAME, prodname, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM buyTBL
RIGHT JOIN userTBL
ON buytbl.userID = usertbl.userID
ORDER BY userID ;
# => from뒤의 값과 join뒤의 값을 바꿔야 left했을때와 동일 값이 나옴
# Quiz) Development => d005
# 이름, 연봉, 입사일 출력
SELECT D.dept_no, S.salary, H.hire_date, H.emp_no
FROM dept_emp D
INNER JOIN employees DH
ON D.emp_no = DH.emp_no
INNER JOIN salaries S
ON SC.club = C.club
ORDER BY S.uname;
SELECT * FROM stdtbl ;
SELECT * FROM clubtbl ;
SELECT * FROM stdclubtbl ;
CREATE TABLE quiz1 (SELECT emp_no, first_name, hire_date FROM employees );
CREATE TABLE quiz2 (SELECT emp_no, salary FROM salaries );
CREATE TABLE quiz3 (SELECT emp_no, dept_no FROM dept_emp );
SELECT employees.emp_no, employees.first_name, salaries.salary, employees.hire_date, dept_emp.dept_no
FROM employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
WHERE dept_no = 'd005'
AND salaries.to_date = '9999-01-01';## 위 내용을 뷰테이블로 추가 => v_emp로 만들어주기
CREATE VIEW v_emp2
AS
(
SELECT E.EMP_NO, E.FIRST_NAME, E.GENDER,
S.TO_DATE, S.SALARY, DE.DEPT_NO, DP.DEPT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN SALARIES S
LEFT OUTER JOIN DEPT_EMP DE ON S.EMP_NO = DE.EMP_NO
LEFT OUTER JOIN DEPARTMENTS DP ON DE.DEPT_NO = DP.DEPT_NO
ON E.EMP_NO = S.EMP_NO
WHERE DE.dept_no = 'd005'
AND S.to_date = '9999-01-01'
);
SELECT * FROM v_emp2### 인덱스
SELECT * FROM indextbl WHERE first_name='MARY';
# 인덱스 없이 조건절로 검색(SELECT) // 0.125초
EXPLAIN SELECT * FROM indextbl WHERE first_name='MARY';
# 쿼리문 실행계획 내용 출력
# type = ALL => 인덱스 안쓴거 확인
CREATE INDEX idx_indexTBL ON indexTBL(fisrt_name);
# 인덱스 설정 => 검색많이 할 열이름으로 생성
SELECT * FROM indextbl WHERE first_name='MARY';
# 인덱스 설정한 테이블 조회 => 원래 테이블 이름으로 검색
# 0.00초 확인
EXPLAIN SELECT * FROM indextbl WHERE first_name='MARY';
# type = ref => 인덱스 참조 확인
# possible_keys => idx_indexTBL 참조확인### 트리거
### 트리거
INSERT INTO memberTBL VALUES ('피겨','김연아','경기도 군포시') ;
SELECT * FROM membertbl ;
CREATE TABLE deletedMemberTBL
(memberID CHAR(8),
memberName CHAR(5),
memberAddress CHAR(20),
deletedDate date
);
# 트리거 룰 설정 전 백업할 테이블 생성
DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL # => 임의로 트리거 이름 지정
AFTER DELETE # => 삭제후에 작업한다는 의미(동작 조건, 삭제발생 후 동작)
ON membertbl # => 트리거가 동작할 공간
FOR EACH ROW # => 모든 행마다 적용시킨다는 의미
BEGIN
INSERT INTO deletedMemberTBL # 트리거 룰 조건 맞을시 실행될 쿼리문
VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
END //
DELIMITER ;
# 트리거 룰 설정
DELETE FROM membertbl WHERE memberName = '김연아' ;
SELECT * FROM membertbl ; # => memberTBL에서 사라진것 확인
# 트리거 룰 -> DELETE 작업 실행
SELECT * FROM deletedmembertbl ; #=> 삭제된 목록에서 확인가능### 백업
'ICT기반 클라우드 네트워크 해킹 침해대응전문가 > Computer' 카테고리의 다른 글
| 용어 정리 (수정중) (0) | 2023.08.17 |
|---|