DAY 2. MySQL 주요 문법 정리 (1)

* CRUD (create(insert포함) / read(select) / update / delete)

* 데이터 삽입하기
* 1. insert into 테이블명 values (값1, 값2, 값3...)
* 2. insert into 테이블명 (필드명1, 필드명2 ..) values (값1, 값2, ...) 넣지 않은 필드에는 null값이 들어감

 

# 데이터베이스 확인하기
use kdt;

# 테이블 확인하기
desc member;

# 테이블 삭제
drop table member;

# 필드 추가하기
alter table member add mbti varchar(10); # 새로 추가하는 컬럼은 not null을 주면 안됨(기존 회원들이 모두 null이므로)

# 필드 수정하기
alter table member modify column mbti varchar(20);

# 필드 삭제하기
alter table member drop mbti;

 

* 데이터 삽입하기
* 1. insert into 테이블명 values (값1, 값2, 값3...)
* 2. insert into 테이블명 (필드명1, 필드명2 ..) values (값1, 값2, ...) 넣지 않은 필드에는 null값이 들어감

 

위를 바탕으로 words 테이블을 만들어 본다.

create table words (
    eng varchar(50) primary key,
    kor varchar(50) not null,
    lev int default 1 );

 

이번엔 만들어진 테이블에 데이터 넣기

 

insert into words values ('apple', '사과', 1);
# insert into words values ('banana', '바나나');   # 1번문법으로는 에러남. (전달인수가 모자라기 때문) 이럴 경우 2번으로 선택
insert into words values ('banana', '바나나', null);
insert into words values ('apple', '사과', null);    #null 가능

insert into words (eng, kor, lev) values ('orange', '오렌지', 1);  #기본값을 처리할 수 있다는 게 장점
insert into words (eng, kor) values ('melon', '메론');  #이러면 lev값은 기본값으로 들어감
insert into words (lev, eng, kor) values (2, 'avocado', '아보카도');  #이런 식으로 순서를 바꾸어 등록할 수 있음
#데이터의 순서도 정해져 있지 않음(딕셔너리처럼)

 

같은 맥락으로 어제 만들어 둔 member 테이블에도 데이터를 넣었다.

 

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('apple', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '여자', '001011', '4015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('banana', '2222', '반하나', '010-2222-2222', 'banana@banana.com', '여자', '001011', '2015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('orange', '3333', '오렌지', '010-3333-3333', 'orange@orange.com', '남자', '001011', '3015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('melon', '4444', '이메론', '010-4444-4444', 'melon@melon.com', '남자', '001011', '3015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('avocado', '5555', '안가도', '010-5555-5555', 'avocado@avocado.com', '남자', '001011', '1015111');

 

* 데이터 수정하기
* 1. update 테이블명 set 필드명1=값1, 필드명2=값2 ..;
* 2. update 테이블명 set 필드명1=값1, 필드명2=값2 .. where 조건절;

 

★UPDATE문 작성시 유의점:

 

update words set eng='Rucy';

 

# 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. 0.000 sec
# PK이기도 하지만 전체테이블을 모두 변경하는 것은 프로그램 자체에서 막는다. 해제하는 방법有
# 1. 일시적인 safe모드를 해제: set sql_safe_updates=0;
# 2. 영구적인 safe모드를 해제: Edit -> preference -> SQL Editor -> safe Updates 체크해제 -> workbench재시작

---

# 모든 유저에게 50포인트를 더해주기
update member set point = point + 50;

update words set lev=2 where eng='avocado';
update words set kor='어륀지', lev=2 where eng='orange';

# member 테이블의 userid가 'apple'인 회원에 대해 우편번호는 '12345', 주소1 '서울시 서초구',
# 주소2는 '양재동', 주소3 'xx아파트 101동'으로 수정하기
update member set zipcode='12345', address1='서울시 서초구', address2='양재동', address3='xx아파트 101동' where userid='apple';

 

* 데이터 삭제하기
* 1. delete from 테이블명; # 데이터만 날림
* 2. delete from 테이블명 where 조건절;

 

drop은 아예 테이블을 삭제하는 거고 delete는 데이터만 삭제하는 것

어쨌든 drop이든 delete든 삭제는 신중히.

 

이제 제일 많이 쓰는 SELECT문-

 

select eng, kor from words;
select eng from words;
select kor, eng from words;
select 100;
select 100 + 50;
select 100 + 50 as '덧셈';
select 100 + 50 덧셈;
select 100 + 50 '덧셈 연산'; # 따옴표를 사용하는 이유는 띄어쓰기가 있을 수 있기 때문
select eng as '영단어', kor as '뜻', lev as '수준' from words;
 
select * from words; #모든 컬럼을 가져오기
select null; #데이터가 없음, insert가 되지 않은 것
select ''; #해당 셀에 ''데이터가 삽입된 것
select 100 + null; #결과: null, 연산할 수 없음
select 100 + ''; #결과: 100, 연산할 수 있음 

 

SELECT는 연산도 해준다! 

이쯤에서 연산자 알아보기

 

* SQL 연산자
* 1. 산술 연산자: +, -, *, /, mod(나머지), div(몫)
* 2. 비교 연산자: =, 컬럼명 쓰는 곳에 있는 =는 대입이고 where절에 있는 =는 비교임., <, >, >=, <=, <>
* 3. 대입 연산자: =
* 4. 논리 연산자: and, or, not, xor
* 5. 기타 연산자:
* is: 양쪽의 피 연산자가 모두 같으면 true, 아니면 false
* between A and B: 값이 A보다 크거나 같고 B보다 작거나 같으면 true, 아니면 false
* in: 매개변수로 전달된 리스트에 값이 존재하면 true 아니면 false
* like: 패턴으로 문자열을 검색하여 값이 존재하면 true 아니면 false

 

#포인트가 150점 이상인 회원 조회하기
select userid, name, point from member where point >= 150;

# 로그인
select userid from member where userid='apple' and userpw='1234'; #결과값 null. 로그인실패
select userid from member where userid='apple' and userpw='1111'; #결과값 도출. 로그인성공

#words 테이블에서 lev이 null인 데이터를 출력
select * from words where lev=null; #X
select * from words where lev is null;
select * from words where lev is not null;

#member 테이블에서 point가 0이상 150이하인 데이터를 출력
select * from member where point >= 0 and point <= 150;
select * from member where point between 0 and 150;

select * from member where name in ('김사과', '반하나', '오렌지');

select * from member where userid like 'a%'; #a로 시작하는 사람~
select * from member where userid like '%a'; #a로 끝나는 사람~
select * from member where userid like '%a%'; #a를 포함하는 사람~

 

* 정렬하기
* select 필드명1, 필드명2, ... from 테이블명 [where 조건절] order by 필드명 [asc, desc];

 

select * from member order by userid asc;
select * from member order by userid; #똑같음. 기본이 asc정렬
select * from member order by userid desc;

 

update member set point = 200 where userid='avocado';
select * from member;
select * from member order by point; #조건 
# member테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 오름차순
select * from member order by point desc, userid asc;

 

#member 테이블의 여성회원을 포인트 순으로 오름차순하고, 포인트가 같다면 userid로 오름차순
select * from member where gender='여자' order by point, userid;

 

* limit
* select 필드명1, 필드명2 .. from 테이블명 limit 가져올 로우의 갯수
* select 필드명1, 필드명2 .. from 테이블명 limit 시작로우(인덱스), 가져올 로우의 갯수

 

select * from member;
select * from member limit 3; #처음부터 3개 짤라서
select * from member limit 2, 2; #시작인덱스 부터 2개까지

#member테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 오름차순한 뒤 top3를 출력
select * from member order by point desc, userid limit 3;

 

집계(그룹)함수
* count(): 로우의 갯수를 세는 함수
* sum(): 로우의 값을 더함
* avg(): 로우의 평균을 구함
* min(): 로우의 최소값을 구함
* max(): 로우의 최대값을 구함

 

select count(userid) from member; # *는 자제하도록. 전체를 다 불러오기 때문에 속도가 느려짐
select count(zipcode) from member; # null값 제외하고 반환함
# 전체 데이터의 갯수를 출력하려면 null이 없는 필드를 선택 -> PK를 선택하는 것을 추천
select count(userid) as totalcount from member;

select sum(point) as 'totalpoint' from member; #집계함수는 일반컬럼과 같이 조회불가(group by를 쓰게되면 가능)

select avg(point) as '평균' from member;

select min(point) as 'min' from member;

select max(point) as 'max' from member;

 

그룹(GROUP BY)
* select 그룹을 맺은 컬럼, 또는 집계함수 from 테이블명 group by 필드명;
* select 그룹을 맺은 컬럼, 또는 집계함수 from 테이블명 group by 필드명 having 조건절; #having: group에 한한 조건
* where은 전체에서 조건을 거는거고 having은 그룹내에서 조건을 건다.

 

select gender from member group by gender;
select userid from member group by userid; #중복된 데이터가 없기 떄문에 모든 로우가 개별로 조회됨
select gender, count(userid) as '인원' from member group by gender; 
select gender, count(userid) as '인원' from member group by gender having gender='여자';

#포인트가 100을 초과하는 member 중에서 남자, 여자 그룹으로 나눠 포인트의 평균을 구하고 
#평균 포인트가 150이상인 성별에 대해 출력, 단 포인트가 많은 성별을 우선으로 출력

select gender, avg(point) from member where point > 100 
group by gender having avg(point) >= 150 order by avg(point) desc;

 

* 데이터 정규화
* - 데이터 베이스를 설계할 때 중복을 최소화 하는 것
* - 조직화되어 있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나누는 것
*
* 데이터 정규화가 필요한 경우
* - 데이터를 변경, 삽입, 삭제할 때 원하지 않게 데이터가 삭제되거나 가공되는 일이 발생할 수 있음(이상 현상)
*   같이 딸린 컬럼인 경우. 하나만 삭제해도 다 삭제되는 이상 현상이 발생할 가능성이 있다면 정규화가 필요
*
* 정규화의 종류
* 1. 1NF(제1정규화)
* - 테이블 안의 모든 값들은 단일 값이어야 함
* - 더 이상 쪼개질 수 없는 단위로 저장
*
* 2. 2NF(제2정규화)
* - 1NF를 만족하면서 완전 함수 종속성을 가진 관계들로만 테이블을 생성
* - 종속성들 중 종속 관계에 있는 열들끼리 테이블을 구분해 주는 것
* - 기본키에 속하지 않은 속성 모두가 기본키에 완전 함수 종속인 정규형
*   함수 종속성: x값에 따라 y값이 결정되는 경우
*
* 3. 3NF(제3정규화)
* - 2NF를 만족하면서, 기본키에 대해 이행적 함수 종속이 되지 않는 것을 의미.
*   중복된건 쪼갤 수 있을 만큼 다 쪼개고 네이밍해라.
*
* 4. 비정규화
* - 정규형에 일치하게 되어있는 테이블을 정규형을 지키지 않는 테이블로 변경
* - 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많이 나뉘어 성능이 저하된다면
*   비정규화를 하여 테이블을 다루는 것이 더 효율적일 수 있음
* - 어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 하는 것이 좋음

 

이제 JOIN을 위한 테이블을 하나 또 만들어 본다.

 

create table profile (
    userid varchar(20) not null,
    height double,
    weight double,
    mbti varchar(10),
    foreign key(userid) references member(userid)
);

 

member테이블과 연동을 시킬거라 userid를 FK로 해서 넣어줬다.

 

insert into profile values ('apple', 160, 50, 'ISTP');
# insert into profile values ('grapes', 170, 70, 'ESTP'); #에러. foreign key 제약조건에 걸림
insert into profile values ('avocado', 180, 80, 'INFP');
insert into profile values ('orange', 170, 70, 'ENFP');

 

데이터를 넣고-

 

* 조인(Join)
* select 필드명1, 필드명2, ... from 테이블1 [inner, left, right] join 테이블2
* on 테이블1.필드명 = 테이블2.필드명

 

# inner 조인
# 조인하는 테이블의 on 절의 조건이 일치하는 결과만 출력(교집합)
# DBMS에 따라 inner join, cross join 등으로 쓰임 
 
select member.userid, name, gender, mbti from member inner join profile on member.userid = profile.userid;
select m.userid, name, gender, mbti from member as m inner join profile as p on m.userid = p.userid;

 

# left / right 조인
# 두 테이블이 조인될 때 왼쪽 또는 오른쪽을 기준으로 기준 테이블의 데이터를 모두 출력
select m.userid, name, gender, mbti from member as m left join profile as p on m.userid = p.userid;

select m.userid, name, gender, mbti from member as m right join profile as p on m.userid = p.userid;