- 보안성 : 계정 권한 수준에 따라 노출되지 않아야 하는 데이터를 숨김 처리 할 수 있음
-- 기본 구문
CREATE VIEW 뷰명 AS
SELECT *
FROM 테이블명
-- 예시
create view v_member as
select
m.member_type, m.user_id,m.name,
md.mobile_no, md.marketing_yn, md.register_date
from member as m
join member_detail md
on m.member_type = md.member_type and m.user_id = md.user_id
;
| (사용자 정의)함수와 프로시져
(사용자 정의)함수
프로시져
언제쓰나?
자주쓰는 기능 저장할때
특정 비즈니스 로직을 저장해서 꺼내 쓸 때
공통점
선언부와 구현부가 있는 함수 형태
차이점
반환값 O
반환값 X
A. 함수
-- 기본 구문
CREATE FUNCTION 함수명(매개변수명 타입)
RETURNS 반환타입
BEGIN
RETURN
구현부
END;
-- 예시
-- A. 생성
create function sf_password(password varchar(255))
returns varchar(255)
begin
return
case
when length(password) > 2 then
concat(substring(password, 1, 2), '**')
else '****'
end;
end;
-- B. 호출
select
sf_password(password) as password_mask
from member;
-- C. 삭제
drop function sf_password;
B. 프로시저
-- 기본 구문
CREATE PROCEDURE 함수명()
BEGIN
구현부
END
;
-- 예시
-- A. 생성
create procedure sp_select_memeber()
begin
select *
from member;
select *
from member_detail;
end;
-- B. 호출
call sp_select_memeber();
-- C. 삭제
drop procedure sp_select_memeber;
C. delimiter를 사용해야 할까?
- 원칙적으로, 함수나 프로시저를 생성할 때, delimiter를 사용하는 것이 필요하다.
** delimiter 곧, SQL 명령어 간의 구분은 기본적으로 세미콜론(';')을 통해 이루어지기에,
SQL 명령어를 구현부에 중첩시키면 하위 명령어는 실행되지 않는 현상이 발생)
- 다만, 툴을 사용하면 delimiter를 사용하지 않아도 오류 없이 잘 실행된다.
delimiter $$ -- delimiter 변경
create procedure sp_select_memeber()
begin
select *
from member;
select *
from member_detail;
end;
delimiter ; -- delimiter default인 ;로 원상복구
| 트리거
특정 조건이 만족되면 자동으로 실행 시작
rf. 일반적으로 히스토리를 남기는 데에 사용된다.
rf. 상황에 따라 양날의 검이 될 수 있는 명령문이다. (사용에 주의가 필요)
event
old
new
insert
X
O
update
O
O
delete
O
X
-- RQ. MEMBER 테이블의 핸드폰 번호를 갱신(update)할 때마다 히스토리를 남기고 싶다.
-- A. 변경 사항 담을 히스토리 테이블
create table member_detail_history
(
id int auto_increment primary key ,
member_type varchar(10),
user_id varchar(50),
mobile_no varchar(50),
new_mobile_no varchar(50),
update_date datetime
);
-- B. 트리거 예시
delimiter &&
create trigger tg_member_mobile_no_history
before update on member_detail -- update 되기 이전에 트리거가 일어난다.
for each row
begin
insert into member_detail_history
(
member_type,
user_id,
mobile_no,
new_mobile_no,
update_date
)
values
(
old.member_type,
old.user_id,
old.mobile_no,
new.mobile_no,
now()
);
end;
delimiter ;
select
m.id as 회원아이디,
m.password as 비밀번호,
m.name as 회원명
from member as m;
| JOIN문
- JOIN만 적히는 경우 INNER JOIN이 사용된다.
INNER JOIN
키 값 기준 데이터 결합
LEFT JOIN
키 값 기준 데이터 결합 + 좌측의 나머지
RIGHT JOIN
키 값 기준 데이터 결합 + 우측의 나머지
FULL JOIN
LEFT데이터 X RIGHT데이터
- 수업에서 제공되었던 테이블은, MEMBER와 MEMBER_DETAIL 이었는데,
이와 같이 회원 정보를 간편 정보 / 상세 정보로 나누어서 필요에 따라 테이블을 조인하는 식으로 사용한다고 한다.
-- 회원정보 > 로그인 정보
create table member
(
member_type varchar(10) not null comment '회원구분',
user_id varchar(50) not null comment '회원 아이디',
password varchar(50) null comment '비밀번호',
name varchar(20) null comment '이름',
primary key (member_type, user_id)
) comment '회원정보';
-- 회원상세정보 > 로그인에 필요하지 않은 이외 정보
create table member_detail
(
member_type varchar(10) not null comment '회원구분',
user_id varchar(50) not null comment '회원 아이디',
mobile_no varchar(12) null comment '휴대폰 번호',
marketing_yn bit null comment '마케팅 수신 여부',
register_date datetime default current_timestamp() null comment '가입일',
primary key (member_type, user_id),
constraint fk_member_detail foreign key (member_type, user_id) references member (member_type, user_id)
) comment '회원상세정보';
- JOIN 코드
-- 조인 (INNER JOIN)
-- ** 단순히 select * from.. 을 쓰면 어떤 DBMS에서는 오류가 날 수 있다.
select
m.*,
md.*
from member as m
join member_detail as md
on m.member_type = md.member_type and m.user_id = md.user_id
;
-- 중복된 컬럼 제외
select
m.member_type, m.user_id, m.password, m.name,
md.mobile_no, md.marketing_yn, md.register_date
from member as m
join member_detail as md
on m.member_type = md.member_type and m.user_id = md.user_id
;
-- LEFT JOIN : member(left)를 기준으로 member_detail(right)와 키값이 동일한 데이터를 먼저 솎아내고, 그 후에 member의 나머지를 추가
select
m.member_type, m.user_id, m.password, m.name,
md.mobile_no, md.marketing_yn, md.register_date
from member as m
left join member_detail as md
on m.member_type = md.member_type and m.user_id = md.user_id
;
-- RIGHT JOIN : RIGHT를 기준으로
select
m.member_type, m.user_id, m.password, m.name,
md.mobile_no, md.marketing_yn, md.register_date
from member as m
right join member_detail as md
on m.member_type = md.member_type and m.user_id = md.user_id
;
-- FULL JOIN : LEFT x RIGHT
select
m.member_type, m.user_id, m.password, m.name,
md.mobile_no, md.marketing_yn, md.register_date
from member as m
join member_detail as md
;
| 내장함수
- 각 DBMS별로 내장되어 있는 함수들이 있다.
- 구분
입력값의 갯수에 따라
- 단일행 함수 : 함수의 입력값이 단일행 값일 때 - 다중행 함수 : 함수의 입력값이 다중행 값일 때 (ex. 집계 함수, 그룹 함수)
데이터 타입에 따라
- 문자형 함수 - 숫자형 함수 - 날짜형 함수 - 변환형 함수 : 데이터 타입 변환 - NULL 관련 함수
- SQL 조건문 : CASE ~ END
-- SQL 조건문 : 자바의 switch case와 동일
CASE
WHEN 조건 THEN 실행
ELSE 실행
END
- 수업에서 다루었던 코드
-- A. 문자형
-- (1) 비밀번호 * 처리 (substring, length, concat)
-- 오라클의 경우, 문자열 결합 시 : concat(문자열, 결합할 문자) 또는 문자열 || 결합할문자
SELECT
member_type,
user_id,
password,
name,
-- concat(substring(password, 1, 2), '**') as password_mask, -- idx가 1부터 시작
-- length(password) as password_length,
case
when length(password) > 2 then concat(substring(password, 1, 2), '**')
else ''
end as password_mask
FROM member;
-- B. 데이터 포맷 변환
SELECT register_date,
date_format(register_date, '%Y.%m.%d') as dt_format
FROM member_detail;
SELECT
'20220321',
str_to_date('20220321', '%Y%m%d') as dt_date,
date_add(str_to_date('20220321', '%Y%m%d'), interval 1 month) as dt_date2
FROM dual;
-- 현재 날짜를 통해 월초와 월말을 구하기
SELECT
date_format(now(), '%Y-%m-%01') as start_date, -- 이번달 첫일
date_add(date_add(str_to_date(date_format(now(), '%Y-%m-01'), '%Y-%m-%d'), interval 1 month), interval -1 day) as end_date
FROM dual;
--!! 주의사항 !! 특히하게 LIMIT은 idx를 0부터 시작한다.
-- 전체 data에 대해 첫번째부터 10개만 출력
SELECT *
FROM member
LIMIT 0, 10;
-- 나열된 data에 대해 10번째부터 10개만 출력
select
c.code, c.company_name, c.eng_company_name, c.category,
row_number() over (order by c.code desc) as row_index
from company c
order by c.code desc
limit 10, 10
;
-- 범위를 지정하여 출력
select *
from
(
select *
from
(
select
c.code, c.company_name, c.eng_company_name, c.category,
row_number() over (order by c.code desc) as row_index
from company c
order by c.code desc
) t1
where row_index <= 30
) t2
where row_index > 20;
📡 ORACLE
-- 기본 구문
--- '*'을 사용하면 데이터가 깨진다.
--- 테이블.* 또는 칼럼명을 하나씩 작성 필요.
SELECT
칼럼1,
칼럼2,
ROWNUM as rnum
FROM member
ORDER BY 순서기준
-- 실제 사용
SELECT *
FROM (
SELECT
a.*, ROWNUM as rnum
FROM (
SELECT
* FROM member
ORDER BY member_no
) a
)
WHERE rnum >= 1 AND rnum <= 10;
- CRUD는 MariaDB/MySQL, ORACLE 모두 동일한 문법을 사용하는 것으로 확인됐다. (아래 코드 비교)
구분
의미
SQL
REST API
WHERE 필수
CREATE
삽입
INSERT
PUT/POST
X
READ
조회
SELECT
GET
X
UPDATE
갱신
UPDATE
PUT/PATCH
O
DELETE
삭제
DELETE
DELETE
O
-- CRUD
-- INSERT
INSERT INTO bootcamp_member2
(name, email, mobile_no, password, marketing_yn, register_date)
VALUES
('테스트3', 'test3@gmail.com', '01022223212', '2222', true, now());
-- SELECT : WHERE 선택 (대다수 WHERE 사용)
SELECT *
FROM bootcamp_member2
WHERE marketing_yn = true
AND name = '테스트3';
-- UPDATE : WHERE 필수
UPDATE bootcamp_member2
SET marketing_yn = false
WHERE name = '김길동';
-- DELETE : WHERE 필수
DELETE FROM bootcamp_member2
WHERE email = 'kim123@gmail.com';
1. 데이터 삽입(INSERT)
🐱 단순 데이터 추가
- 아래와 같이 단순 데이터를 추가할 경우, 데이터가 없을 때(NULL) 문제가 발생할 수 있다. - 회원가입을 예로 들었을 때, 어떤 MEMBER의 ID는 유일해야한다. --> 데이터 입력에 대한 제약이 필요 - 여기서 제약 조건이 필요해진다. * 제약조건(Constraint) : 특정 데이터에 대해 거는 제약
(1) 선택적 조회 : WHERE절을 쓰자 - 일반적으로 회사에서 데이터를 조회할 때는, 전체 조회를 하지 않는다. 데이터 양이 방대하기 때문에 컴퓨터나 너무 느려질 수 있기 때문 - 따라서, WHERE절이 필수는 아니라도 함께 써주는 것이 좋다. (2) and 나 or를 통해 추가적인 조건사항을 걸 수 있다. - 아래의 코드처럼, where절을 통한 조건을 걸 때 and나 or를 통해 추가 조건을 걸 수 있다.
📡 MariaDB
-- 전체 조회
select *
from 테이블명
;
-- 선택 조회
select
name, email
from 테이블명
;
-- where절 조건
select *
from 테이블명
where 조건
or 조건
;
