MySQL

use aidev;

# 데이터 수정
# update 테이블명 set 필드명1=값1, 필드명2=값2 ... //필드 내 값들이전부 값1 or 값2로 바뀌고
# update 테이블명 set 필드명1=값1, 필드명2=값2 ... where 조건절   //조건절있으면 조건에해당하는 놈만 변경
select * from tb_member;
#잠금해제하는법
# Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
# 메뉴 -> edit -> Preference -> SQL Editor ->밑에 safe updates 체크해제하면 됨 -> 껏다키기
update tb_member set mem_zipcode='12345';
update tb_member set mem_hobby='코딩' where mem_userid='banana';
update tb_member set mem_hobby='코딩' where mem_idx=7; #조건절을 pk 사용하는 놈으로 이용하면 더빨리 찾는다
# 이메론의 주소가 서울특별시 서초구 양재동. 데이터를 변경해보자( 단 서울특별시를  address1 서초구 address2 양재동 address3에 저장)
update tb_member set mem_address1='서울특별시',mem_address2='서초구',mem_address3='양재동' where mem_idx=9;

# mem_point컬럼을 추가(단, 타입은 int, 기본값은 0)
alter table tb_member add mem_point int default 0;
alter table tb_member change mem_id mem_idx int;

#모든 회원에게 200p 선물하기
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<= x <=B 사이 있으면 참 밖이면 그짓) 예) mem_point between 100 and 300
    in(연산자의 값이 매개변수로 전달받은 리스트에 존재하면 참을 반환) 예) mem_hobby in('잠자기','코딩','게임)
    like(패턴으로 문자열을 검색) 예) mem_userid like 'a%' // a로 시작하는 사람을 찾아낼수있음
4. 논리 연산자
and, or , xor, not    
*/

# 데이터 삭제
# delete from 테이블명;
# delete from 테이블명 where 조건절;
delete from tb_member;
delete from tb_member where mem_point =300;
update tb_member set mem_point=500 where mem_userid='melon';
delete from tb_member where mem_userid='cherry';
select * from tb_member;

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

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

select 100+null;  # null 이된다 null은 연산할 수 없음

select * from tb_member; # *:모든 컬럼  / 프로그래밍할때는 * 안쓰고 일일이 필요한것만 다 써주는게 좋음
#필드의 위치순서는 신경X select 할때 순서대로 하면됨


# 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';
# or 연산자
select mem_userid, mem_name from tb_member where mem_point=300 or mem_point=200 or mem_point=500;
select mem_userid, mem_name 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_hobby from tb_member where mem_hobby is null; #널찾을땐 무조건 is null사용해야함!
select mem_userid, mem_name, mem_hobby from tb_member where mem_hobby is not null;
# like 연산자
select * from tb_member;
select mem_userid, mem_name from tb_member where mem_userid like 'a%';
select mem_userid, mem_name from tb_member where mem_userid like '%a';
select mem_userid, mem_name from tb_member where mem_userid like '%a%';
select mem_userid, mem_name from tb_member where mem_userid like 'app%';
select mem_userid, mem_name from tb_member where mem_userid like 'app%';
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는 기본형이라 생략가능)
select mem_userid, mem_name, mem_point from tb_member order by mem_point desc; -- 내림차순
# 포인트순으로 내림차순으로 데이터 조회(단, 같은 포인트인 경우 최근 가입순으로 정렬)
select * from tb_member;
select mem_userid, mem_name, mem_point, mem_regdate from tb_member order by mem_point desc, mem_regdate desc; -- 내림차순

# 성별 필드 추가
alter table tb_member add mem_gender enum('남자','여자'); #남자 아니 여자만 입력 가능
select * from tb_member;

update tb_member set mem_gender = '남' where mem_userid='orange'; #에러남
update tb_member set mem_gender = '남자' where mem_userid='orange';

# 성별이 여자인 회원을 point가 많은순으로 정렬 (단, point가 같을 경우 먼저 갑입한 순으로 정렬)
# mem_idx, mem_userid, mem_name, mem_point , mem_gender,mem_regdate
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) user from tb_member where mem_userid='apple' and mem_userpw='1112';
# 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)
values ('cherr', '1111','김사과','010-1111-1111','apple@naver.com','001011','4068518','여자');
select distinct mem_userpw, mem_gender from tb_member;  -- 2개의 필드 값이 모두 일치하므로 중복으로 판정

select distinct count(mem_gender) from tb_member; -- 6
select  count(distinct mem_gender) from tb_member; -- 2
# sum: 행의 값을 더함
select mem_suerid, sum(mem_point) total from tb_member; -- x
select sum(mem_point) total from tb_member; -- O
# 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_poiont > 0 group by mem_gender;
select mem_gender, count(mem_idx) cnt from tb_member where mem_poiont > 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_profile;
select * from tb_member;
insert into tb_profile values(7,30,180, 70, 'B', 'INTJ');  # foreign key 제약조건 위배
insert into tb_profile values(1,20,180, 60, 'A', 'INTJ');
insert into tb_profile values(2,10,150, 50, 'O', 'INTJ');
insert into tb_profile values(3,15,1520, 70, 'B', 'INTJ');
insert into tb_profile values(4,27,190, 85, 'AB', 'INTJ');

# 조인
# 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 inner 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,tb_member.mem_idx m,tb_order.or_idx o 
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,tb_member.mem_idx m,tb_order.or_idx o  
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,tb_member.mem_idx m,tb_order.or_idx o  
from tb_member 
right join tb_order 
on tb_member.mem_idx = tb_order.or_idx;

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

/*
create view 뷰이름
as
select 쿼리..
*/
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_inner_profile as
select mem_userid, mem_name, mem_hp, or_num,or_zipcode,tb_member.mem_idx m,tb_order.or_idx o 
from tb_member 
left join tb_order 
on tb_member.mem_idx = tb_order.or_idx;

select * from vw_member_inner_profile;
/*
뷰 대체
    create or replace view 뷰이름 as
    as
    select 쿼리...
    
    뷰 삭제
    drop view 뷰이름
*/
drop view vw_member_inner_profile;