데이터 베이스 쿼리문 정리(1)

테이블 만들기

CREATE TABLE member
(
	member_type varchar(10) not null comment '회원구분',
	user_id varchar(50) not null comment '회원아이디',
	name varchar(20) null comment '비밀번호',
	password varchar(50) 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'회원상세정보';

테이블 구조 확인

Desc table

테이블 삭제

drop table MEMBER;

프라이머리 키 수정

alter table zerobase_member 
add constraint primary key pk_zerobase_member(email);

데이터 추가

insert into zerobase_member
(name, email, mobile_num,password,marketing_yn,register_date) 
values
('Park_Jisu','test3@naver.com','01029742285','1111',true,now());

데이터 읽기

Select * from table;

데이터 수정

update zerobase_member 
SET 
	marketing_yn = FALSE 
	, register_date  = now()
	, password  = '1234'
	
where email = 'test@naver.com'	
;

데이터 삭제

delete from zerobase_member where email = 'test@naver.com';

Join : 합집합

Left join: 없는 것도 다 나옴

select m.member_type, m.user_id , name, password , mobile_no, marketing_yn, 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 ;

데이터베이스 내장 함수

Case-end, length, concat, substring

select member_type, user_id, name, password , 
case 
	when length(password) >2 then concat(substring(password, 1,2),'**') 
	else ''
END as masekd_password
from member;

format함수

날짜 -> 문자열 : date_format
문자열 -> 날짜: str_to_date
날짜 바꾸기: date.add(now(),interval 1 month)

날짜 수정1

select 
register_date,
date_format(register_date, "%Y.%m.%d")as date_format 
from member_detail as md;

날짜 수정2

select '20230515' as dt_string
, str_to_date('20230515', '%Y%m%d') as dt_date
,date_add(str_to_date('20230515','%Y%m%d'), interval 1 month) as dt_date2
from dual;

현재 날짜의 월초와 월말 가져오기

select now() as now
, 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

페이징 처리

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 as c 
order by c.code desc
limit 10,10;

view 만들기, 보기, 삭제하기

create view v_member as 

select m.member_type ,m.user_id ,m.password ,m.name ,md.mobile_no , md.marketing_yn , md.register_date 
from member m
left join member_detail md on m.member_type  =  md.member_type and m.user_id  = md.user_id ;

select *
from v_member ;

drop view v_member;