MySQL -2

use aidev;

# 데이터 수정
# update 테이블명 set 필드명1=값1, 필드명2=값2 ...
# update 테이블명 set 필드명1=값1, 필드명2=값2 ... where 조건절
select * from tb_member;
# To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.016 sec
# 메뉴 > edit > Preferences > SQL Editor > Safe Updates 체크없앰
update tb_member set mem_zipcode='12345';
update tb_member set mem_hobby='코딩' where mem_idx=2; # 반하나의 취미 업데이트

#김메론(4번)의 주소가 서울특벽시 서초구 양재동, 데이터를 변경해보자. ( '서울특별시'를 address1, '서초구'를 address2, '양재동'을 address3에 )

update tb_member set mem_address1='서울특별시', mem_address2='서초구', mem_address3='양재동' where mem_idx=4;

#mem_point 컬럼을 추가(단, 타입은 int, 기본값은 0)

alter table tb_member add mem_point int default 0;

update tb_member set mem_point= mem_point + 200

/*
SQL 연산자
1. 산술 연산자
+, -, *, /, div(나누기를 한 후 소수부분을 버림), mod(나머지 연산)
2. 대입 연산자
=
3. 비교 연산자
=, <, >, >=, <=, <>(다르다)
    is(왼쪽 피연산자와 오른쪽 피연산자가 같으면 참, 다르면 거짓) 예) mem_address1 is null
    between A and B(값이 A보다는 크거나 같고, B보다는 작거나 같으면 참) 예) mem_point between 100 and 300
in(연산자의 값이 매개변수로 전달받은 리스트에 존재하면 참을 반환) 예) mem_hobby('잠자기', '코딩', '게임')
    like(패턴으로 문자열을 검색) 예) mem_userid like 'a%'
4. 논리 연산자
and, or, xor, not
*/

# 데이터 삭제
# delete from 테이블명;
# delete from 테이블명 where 조건절;

delete from tb_member where mem_point = 300;
delete from tb_member where mem_userid = 'watermelon';

# 데이터 검색
# select 필드1, 필드2 ... from 테이블명 
select 100 + 50;
#별명
select 100 + 50 as '덧셈'; -- ''(싱글쿼트)를 사용하는 이유는 띄어쓰기 때문
select 100 + 50 as 덧셈;
select 100+50 덧셈;

select null; # 해당 셀에 insert가 되지 않은 것
select ''; #해당 셀에 '' 데이터가 삽입된 것

select 100 + null; -- null은 연산할 수 없음

select * from tb_member; -- * : 모든컬럼 ( *쓰면 속도를 저하시킴)
select mem_userid, mem_name, mem_hp, mem_email, mem_regdate, mem_point from tb_member;
select mem_userid, mem_point,mem_name, mem_hp, mem_email, mem_regdate, mem_name from tb_member;

# select 필드1, 필드2 ... from 테이블명 where 조건절
select mem_userid, mem_name, mem_hp, mem_email from tb_member where mem_userid="apple";
# 로그인 쿼리
select mem_userid, mem_name from tb_member where mem_userid='apple' and mem_userpw ='1111';

#포인트가 300,400,500인 샤용자 출력
select mem_userid, mem_name, mem_point from tb_member where mem_point=300 or mem_point= 400 or mem_point=500;
select mem_userid, mem_name, mem_point from tb_member where mem_point in (300,400,500);

# between 연산자
select mem_userid, mem_name, mem_point from tb_member where mem_point between 300 and 500;
select mem_userid, mem_name, mem_point from tb_member where mem_point >= 300 and mem_point <= 500;

#null
select mem_userid, mem_name, mem_bobby from tb_member where mem_hobby = 'null'; -- X
select mem_userid, mem_name, mem_bobby from tb_member where mem_hobby = null; -- X
select mem_userid, mem_name, mem_bobby from tb_member where mem_hobby is null; -- O
select mem_userid, mem_name, mem_bobby from tb_member where mem_hobby is not null; -- 취미가 있는사람만 추출

#like 연산자
select mem_userid, mem_name from tb_member where mem_userid like 'a%'; -- a로 시작하는 userid 찾기
select mem_userid, mem_name from tb_member where mem_userid like '%a'; -- a로 끝나는 userid 찾기
select mem_userid, mem_name from tb_member where mem_userid like '%a%'; -- a가 들어가는 userid 찾기
select mem_userid, mem_name from tb_member where mem_userid like 'app%'; -- app로 시작하는 userid 찾기
select mem_userid, mem_name from tb_member where mem_userid like 'app__'; -- app로 시작하고 5글자인 데이터 검색

# select 필드1, 필드2 ... from 테이블명 [ where 조건절] order by 필드 [asc, desc]
select mem_userid, mem_name, mem_point from tb_member order by mem_point asc; -- 오름차순 // 오름차순일 때 asc는 생략가능
select mem_userid, mem_name, mem_point from tb_member order by mem_point desc; -- 내림차순

# 포인트 순으로 내림차순으로 데이터 조회(단, 같은 포인트인 경우 최근 가입순으로 정렬)
select mem_userid, mem_name, mem_point, mem_regdate from tb_member order by mem_point desc, mem_regdate;

#성별 필드 추가
alter table tb_member add mem_gender enum('남자','여자');

update tb_member set mem_gender = '남자' where mem_userid = 'orange'; #Error Code: 1265. Data truncated for column 'mem_gender' at row 1 0.000 sec
update tb_member set mem_gender = '여자' where mem_userid = 'banana';

select * from tb_member;

# 성별이 여자인 회원을 point가 많은 순으로 정렬(단, point가 같을 경우 먼저 가입한 순으로 정렬)
select mem_idx, mem_userid, mem_name, mem_point, mem_gender, mem_regdate from tb_member 
where mem_gender = '여자' order by mem_point desc, mem_regdate;

# select 필드1, 필드2 ... from 테이블명 limit 가져올 행의 갯수
# select 필드1, 필드2 ... from 테이블명 limit 시작행, 가져올 행의 갯수
select mem_userid, mem_name, mem_hp, mem_email from tb_member limit 3;
select mem_userid, mem_name, mem_hp, mem_email from tb_member limit 3,2; # 4번째 행부터 2개를 가져옴

# 집계함수
# count : 행의 갯수를 세는 함수
select count(mem_idx) from tb_member;
select count(mem_hobby) from tb_member; -- null을 제외하고 갯수를 셈
select count(mem_idx) from tb_member where mem_userid = 'apple' and mem_userpw='1111';

# distinct : 뒤에 나오는 필드에 대하여 같은 값을 가진 중복된 행을 제외
select distinct mem_gender from tb_member;
select distinct mem_userid, mem_gender from tb_member;

insert into tb_member ( mem_userid, mem_userpw, mem_name, mem_hp, mem_email, mem_ssn1, mem_ssn2 )
value('cherry', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '001011', '4068518', '여자');

select * from tb_member;

select distinct mem_userpw, mem_gender from tb_member; -- 2개의 필드 값이 모두 일치하므로 중복으로 판정

delete from tb_member where mem_userid= 'cherry';


select distinct count(mem_gender) from tb_member; -- 6
select count(distinct mem_gender) from tb_member; -- 2

#sum : 행의 값을 더함
select mem_userid, sum(mem_point) total from tb_member; -- X
select sum(mem_point) total from tb_member;

#avg : 행의 값의 평균을 구함
select avg(mem_point) avg from tb_member;

# min, max : 행의 최대값 또는 최소값을 구함
select min(mem_point) min from tb_member;
select max(mem_point) max from tb_member;

# select 그룹을 맺은 컬럼 또는 집계함수 from 테이블명 [where 조건절] group by 필드 having 조건절 order by 필드 [asc,desc]
select mem_gender from tb_member group by mem_gender;
select mem_gender, count(mem_idx) cnt from tb_member group by mem_gender;
select mem_gender, sum(mem_point) cnt from tb_member group by mem_gender;

select mem_gender, count(mem_idx) cnt from tb_member where mem_point > 0 group by mem_gender;
select mem_gender, count(mem_idx) cnt from tb_member where mem_point > 0 group by mem_gender having mem_gender='남자';
select * from tb_member;

# 포인트가 0이 아닌 회원의 집합에서 남자, 여자로 그룹을 나눠 포인트의 평균을 구하고
# 포인트가 300이상인 성별을 검색하여 포인트로 내림차순 정렬

select mem_gender, avg(mem_point) as avg from tb_member where mem_point > 0 group by mem_gender 
having avg>= 300 order by avg desc;

# 프로필 테이블 생성
create table tb_profile(
pro_idx int not null,
    pro_age int,
    pro_height double,
    pro_weight double,
    pro_blood varchar(5),
    pro_mbti varchar(10),
    foreign key(pro_idx) references tb_member(mem_idx)
);

select * from tb_member;
select * from tb_profile;

insert into tb_profile values (6, 30, 170, 70, 'B', 'INTP'); -- foreign key 제약조건 위배
insert into tb_profile values (1, 20, 160, 50, 'A', 'INTP');
insert into tb_profile values (2, 25, 163, 60, 'B', 'INTP');
insert into tb_profile values (3, 30, 180, 80, 'O', 'INTP');
insert into tb_profile values (4, 28, 155, 50, 'B', 'INTP');

# 조인
# select 필드1, 필드2 .. from 테이블1 [inner, left, right ] join 테이블2 on 테이블1.필드 = 테이블2.필드;
select mem_userid, mem_name, mem_hp, pro_age, pro_blood, pro_mbti from tb_member left join tb_profile
on tb_member.mem_idx = tb_profile.pro_idx;
    
select mem_userid, mem_name, mem_hp, pro_age, pro_blood, pro_mbti from tb_member inner join tb_profile
on tb_member.mem_idx = tb_profile.pro_idx;
    
select A.mem_userid, A.mem_name, A.mem_hp, B.pro_age, B.pro_blood, B.pro_mbti from tb_member A left join tb_profile B
on A.mem_idx = B.pro_idx;
    
create table tb_order(
or_idx int,
    or_num varchar(8) not null,
    or_zipcode varchar(5),
or_address1 varchar(100),
    or_address2 varchar(100),
    or_address3 varchar(100)
);



insert into tb_order values (null, '00000001', '12345', '서울', '서초구', '양재동');
insert into tb_order values (1, '00000002', '12345', '서울', '서초구', '양재동');
insert into tb_order values (4, '00000003', '12345', '서울', '서초구', '양재동');
insert into tb_order values (1, '00000004', '12345', '서울', '서초구', '양재동');
insert into tb_order values (null, '00000005', '12345', '서울', '서초구', '양재동');
insert into tb_order values (7, '00000006', '12345', '서울', '서초구', '양재동');

select * from tb_order;

select mem_userid, mem_name, mem_hp, or_num, or_zipcode from tb_member left join tb_order
on tb_member.mem_idx = tb_order.or_idx;

select mem_userid, mem_name, mem_hp, or_num, or_zipcode from tb_member inner join tb_order
on tb_member.mem_idx = tb_order.or_idx;

select mem_userid, mem_name, mem_hp, or_num, or_zipcode from tb_member right join tb_order
on tb_member.mem_idx = tb_order.or_idx;

# 뷰(view)
# 가상의 테이블을 생성
# 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 저장하고 있지는 않음
# SQL코드를 간결하게 만들기 위함, 보안상 내부 데이터를 전체 공개하고 싶지 않을 때
# 삽입, 삭제, 수정 작업에 제한 사항을 가짐
# 자신만의 인덱스를 가질 수 없음
# 한번 정의된 뷰는 변경할 수 없음

/*
뷰 대체
create view 뷰이름
    as
    select 쿼리..
    뷰 삭제
    drop view 뷰이름
*/
drop view vw_member_left_profile;

select * from tb_member;
create view vw_member_simple as select mem_idx, mem_userid, mem_userpw, mem_name, mem_hp from tb_member;
select * from vw_member_simple;

create view vw_member_left_profile as
select mem_userid, mem_name, mem_hp, pro_age, pro_blood, pro_mbti from tb_member left join tb_profile
on tb_member.mem_idx = tb_profile.pro_idx;
select * from vw_member_inner_profile;

select * from vw_member_left_profile;