DB VIEW 활용법 - 정리해야 됌

-- create -- 
/* USE testdb;

DROP TABLE IF EXISTS usertbl;
CREATE TABLE usertbl
(
userid CHAR(8) NOT NULL,
    name varchar(8) NOT NULL,
    birthYear INT NOT NULL
);

ALTER TABLE usertbl
add CONSTRAINT PK_usertb2_userid
PRIMARY KEY(userid);
    
DESCRIBE usertbl;
*/
-- ---------------------------------------------------------------------------------
-- 제약조건(프라이머리키 설정) --
/*use testdb;
-- 기본키 삭제
-- alter table prodtbl
-- drop primary key;

DROP TABLE IF EXISTS prodtbl;
CREATE TABLE prodtbl
(
prodCode CHAR(3) NOT NULL,
    prodID CHAR(4) NOT NULL,
    prodDate DATETIME NOT NULL,
    prodCur CHAR(10) NULL
);
ALTER TABLE prodtbl
add CONSTRAINT PK_prodtbl_prodcode_prodid 
PRIMARY KEY(prodCode,prodID);
    
-- 제약조건 (foreign 설정)
DROP TABLE IF EXISTS buytbl,usertbl;
CREATE TABLE usertbl
(
userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null
);
-- create table buytbl
-- (
-- num int auto_increment not null primary key,
    -- userID char(8) not null,
    -- prodName char(6) not null,
    -- foreign key(userID) references usertbl(userID) -- FK가 userID로 나옴
-- );
create table buytbl
(
num int auto_increment not null primary key,
    userID char(8) not null,
    prodName char(6) not null
);
alter table buytbl
add constraint FK_usertbl_buytbl -- FK가 
    foreign key(userID)
    references usertbl(userID);
    
 show index from buytbl;
 */
 
 -- ----------------------------------------------------------------------------------------
 -- 제약조건(unique 설정)
 /*use testdb;
 DROP table if exists buytbl,usertbl;
 create table usertbl
 (
userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null,
    email char(30) null unique -- UNIQUE가 email 이름으로 지정됌
 );
 
 show index from usertbl;
 
DROP table if exists buytbl,usertbl;
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
email char(30) null, 
    constraint UK_email unique(email) -- UNIQUE가 UK_email로 지정됌
);

 show index from usertbl;*/
 
 -- 제약조건 (check제약 조건)
 -- 출생년도가 1900년 이후 , 2023년 이전, 이름은 반드시 넣어야 됌
/*use testdb;
DROP table if exists usertbl;
create table usertbl
(
userID char(8) not null PRIMARY key,
    name varchar(10),
    birthYear int null check (birthYear >= 1900 and birthYear <= 2023),
    mobile1 char(3) null,
    constraint CK_name check(name is not null) -- 이름 반드시 넣음
);

select * from usertbl;

alter table usertbl
add constraint CK_mobile1
    check(mobile1 in('010','011','016','017','018','019'));
 
 select * from usertbl;
 */
 
 -- 제약조건 (default 설정) -- null 값이 없을 때 기본값이 들어가게 해줌.
/*use testdb;
DROP table if exists usertbl;
create table usertbl
(
userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null default '-1',
addr char(2) not null default '서울',
    mobile1 char(3) null,
    mobile2 char(8) null,
    height int null default 170,
    mdate date null    
);

SELECT * from usertbl;
*/
-- alter table 문 사용
-- 테이블에 속성들을 추가/변경/수정/삭제 기능들을 수행함
-- use testdb;
-- 열추가
-- alter table usertbl 
-- add homepage varchar(30)
-- default 'http:// www.xxxx.co.kr' -- DEFAULT 값
        -- null; -- null 값 허용

-- 순서 바꾸기
-- alter table usertbl
-- modify name varchar(8) not null first;

-- 열 삭제
-- ALTER table usertbl
-- drop column homepage;

-- 제약 조건 삭제
-- alter table usertbl
-- drop primary key; -- 프라이머리 키 삭제
-- alter table buytbl
-- drop foreign key FK_usertbl_buytbl; -- foreign 키 삭제

-- view 사용법
-- use testdb;
-- drop view if exists v_usertbl;
-- create view v_usertbl as (select userid ,name,addr from usertbl);
        
-- select * from v_usertbl;
-- ---------------------------------------------------------------------------------------

-- 필요한 부분만 뽑아서 만드는 view
-- inner join을 활용한 필요한 내용들 뽑아내기
-- 1.
-- create view v_userbuytbl
-- as select U.userID , U.name, B.prodName, U.addr , 
-- concat(U.mobile1,U.mobile2) as '연락처' 
    -- from usertbl U -- U라는 별칭을 만듬
-- inner join buytbl B -- B라는 별칭을 만듬
-- On U.userID = B.userID;
            
-- select * from v_userbuytbl where name = '김범수';

-- 2.
-- use testdb;
-- drop view if exists v_userbuytbl;
-- create view v_userbuytbl
-- as
-- select U.userid as 'USER ID' , U.name as 'USER NAME', B.prodname,
    -- U.addr , concat(U.mobile1 , U.mobile2) as 'MOBILE PHONE'
-- from usertbl U
-- Inner join buytbl B
-- On U.userID = B.userID; 

-- select 'USER ID' , 'USER NAME' FROM v_userbuytbl;

-- ALTER 구문을 이용한 뷰의 수정
/*use testdb;
alter view v_userbuytbl
as 
select U.userid as '사용자 아이디', U.name as '이름', B.prodname as '제품 이름',
U.addr, concat()
*/

-- 뷰 정보 덮어쓰기
-- create OR replace view v_userbuytbl

-- 뷰를 이용한 데이터 수정
-- use testdb;
-- select * from v_userbuytbl where userid = 'JKW';
-- update v_userbuytbl set addr = '부산' where userid = 'JKW';

-- 뷰를 이용한 그룹함수
/* use testdb;
create view s_sum
as
select userid as 'userid' , sum(price * amount) as 'total'
from buytbl group by userid;
        
select * from v_sum;
*/


나중에 정리할 것