MySQL, 2일차

데이터 베이스 선택

use kdt; 

 

테이블 확인하기

desc member;

 

테이블 삭제하기

drop table member;

 

필드 추가하기

alter table member add mbti varchar(10);

 

필드 수정하기

 alter table member modify column mbti varchar(20);

 

필드 삭제하기

alter table member drop mbti;

 

crud(Create Read Update Delete)

데이터 삽입하기

insert into 테이블명 values (값1, 값2, 값3...)

insert into 테이블명 (필드명1, 필드명2 ...) values (값1, 값2, ...)

create table words(

     eng varchar(50) primary key,
     kor varchar(50) not null,

     lev int default 1

);

 

desc words;

 

insert into words values ('apple', '사과', 1);

-- insert into words values ('apple', '사과', 1); # 중복 데이터 삽입 에러
-- insert into words values ('banana', '바나나'); # 컬럼 갯수가 일치하지 않음

insert into words values ('banana', '바나나', null); # null이 들어감
insert into words values ('orange', null, null); # 뜻에 null을 넣을 수 없음

 

insert into words (eng, kor, lev) values ('orange', '오렌지', 1);
insert into words (eng, kor) values ('melon', '메론');
insert into words (lev, eng, kor) values (2, 'avocado', '아보카도');
-- insert into words (eng) values ('cherry'); # 뜻에 null을 넣을 수 없음

 

데이터 삽입 실습
member 테이블에 5명의 데이터를 삽입

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');

 

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

 

update words set eng = 'Rucy';

#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.

 1. 일시적인 safe모드를 해제 : set sql_safe_updates=0;
 2. 영구적인 safe모드를 해제 : Edit -> Preferences -> SQL Editor -> Safe Updates 체크 해제 -> workbench 재시작

 

update words set lev=1;

update member set point=point+50;

update words set lev=2 where eng='avocado';

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

 

member 테이블의 아이디가 'apple'인 회원에 대해 우편번호는 '12345', 주소1은 '서울시 서초구',
주소2는 '양재동', 주소3은 'XX아파트 101동'으로 수정하기

 

update member set zipcode='12345', address1='서울시 서초구', address2='양재동', address3='XX아파트 101동' where userid = 'apple';

 

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

delete from words;
-- delete from member where userid='avocad'; # 데이터가 없어서 삭제되지 않음
delete from member where userid='avocado';

 

데이터 검색하기
select 필드명1, 필드명2, ... from 테이블명
select 필드명1, 필드명2, ... from 테이블명 where 조건절;

insert into words values ('apple', '사과', 1);
insert into words values ('banana', '바나나', null); # null이 들어감
insert into words (eng, kor, lev) values ('orange', '오렌지', 1);
insert into words (eng, kor) values ('melon', '메론');
insert into words (lev, eng, kor) values (2, 'avocado', '아보카도');

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 as 덧셈;
select 100 + 50 덧셈;
select 100 + 50 '덧셈 연산'; # 따옴표를 사용하는 이유는 띄어쓰기가 있을 수 있기 때문
select eng as '영단어', kor as '뜻', lev as '수준' from words;

 

select * from words; # 모든 컬럼을 가져오기
select eng, kor, lev from words;

select null; # 데이터가 없음, insert가 되지 않은 것
select ''; # 해당 셀에 ''데이터가 삽입된 것
select 100 + null; # 결과: null, 연산할 수 없음
select 100 + ''; # 결과: 100, 연산할 수 있음

 

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

 

select userid, name from member where userid='apple';
select userid, name from member where gender='남자';
select userid, name, gender from member where gender='남자';

 

#point가 150 이상인 member의 아이디, 이름, 포인트를 출력 
select userid, name, point from member where point >= 150;

# 로그인 
select userid from member where userid='apple' and userpw='1111'; #로그인
select userid from member where userid='apple' and userpw='1234'; #로그인실패

# words 테이블에서 lev이 null인 데이터를 출력
select * from words where lev = 'null'; # X
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; # 아이디로 오름차순
select * from member order by userid desc; # 아이디로 내림차순

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('avocado', '5555', '안가도', '010-5555-5555', 'avocado@avocado.com', '남자', '001011', '1015111');

select * from member;
select * from member order by point;

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('cherry', '6666', '채리', '010-6666-6666', 'cherry@cherry.com', '여자', '001011', '1015111');

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

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

 

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

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

# 집계(그룹) 함수
# count(): 로우의 갯수를 세는 함수 
select * from member;
select count(*) from member;
# 전체 데이터의 갯수를 출력하려면 null이 없는 필드를 선택 -> primary key를 선택하는 것을 추천
select count(userid) from member;
select count(zipcode) from member; # null을 세지 않음
select count(userid) as totalcount from member;

# sum(): 로우의 값을 더함
select sum(point) as 'totalpoint' from member;
-- select userid, sum(point) as 'totalpoint' from member; 집계함수는 그룹과 사용해야 함

# avg(): 로우의 평균을 구함
select avg(point) as 'avg' from member;

# min(): 로우의 최소값을 구함
select min(point) as 'min' from member;

# max(): 로우의 최대값을 구함
select max(point) as 'max' from member;

 

그룹
    select 그룹을 맺을 컬럼 또는 집계함수 from 테이블명 group by 필드명;
    select 그룹을 맺을 컬럼 또는 집계함수 from 테이블명 group by 필드명 having 조건절;

 

select gender from member group by gender;
select gender 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='여자';

select * from member;

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

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

 

데이터 정규화
 - 데이터 베이스를 설계할 때 중복을 최소화하는 것 
 - 조직화되어 있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나누는 것

데이터 정규화가 필요한 경우
- 데이터를 변경, 삽입, 삭제할 때 원하지 않게 데이터가 삭제되거나 가공되는 일이 발생할 수 있음(이상 현상)
 - 이상 현상이 발생할 가능성이 있다면 정규화가 필요

 정규화의 종류
 1. 1NF(제 1정규화)
 - 테이블 안의 모든 값들은 단일 값이어야 함
 - 더 이상 쪼개질 수 없는 단위로 저장
    
   2. 2NF(제 2정규화)
 - 1NF를 만족하면서 완전 함수 종속성을 가진 관계들로만 테이블을 생성
  - 종속성들 중 종속 관계에 있는 열들끼리 테이블을 구분해 주는 것 
  - 기본키에 속하지 않은 속성 모두가 기본키에 완전 함수 종속인 정규형
 - 함수 종속성: x값에 따라 y값이 결정되는 경우

   3. 3NF(제 3정규화)
  - 2NF를 만족하면서, 기본키에 대해 이행적 함수 종속이 되지 않는 것을 의미
    
   4. 비정규화
    - 정규형에 일치하게 되어 있는 테이블을 정규형을 지키지 않는 테이블로 변경
    - 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많아 나뉘어 성능이 저하된다면
    비정규화를 하여 테이블을 다루는 것이 더 효율적일 수 있음
    - 어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 하는 것이 좋음 
    

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

desc profile;

insert into profile values ('apple', 160, 50, 'ISTP');
select * from profile;
-- insert into profile values ('grapes', 170, 70, 'ESTP'); # member에 없는 데이터 
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.필드명 

 

select * from member;
select * from profile;

 

inner 조인

: 조인하는 테이블의 on 절의 조건이 일치하는 결과만 출력
두 테이블의 교집합
- join, 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;