어제 이어 sql에 대해 더 알아보자.
먼저 어제 만들어 놓은 데이터 베이스를 다시 지정해준다.
# 데이터베이스 선택
use kdt;
# 테이블 확인하기
desc member;
어제 만들어 놓은 member테이블이 잘 있는지 확인해본다. desc 이용
# 테이블 삭제하기
drop table member;
# 필드 추가하기
alter table member add mbti varchar(10);
member 테이블에 mbti 변수를 추가한다 문자열을10까지 받을수 있게.
# 필드 수정하기
alter table member modify column mbti varchar(20);
문자열을 20자 까지 받을 수 있게 수정해준다.
# 필드 삭제하기
alter table member drop mbti;
mbti 필드를 삭제한다.
crud(Create Read Update Delate)
데이터 삽입하기
1. insert into 테이블명 values (값1,값2,값3,...) #전체필드에 값을 넣을때, 넣고 싶은값과 필드의 갯수가 똑같이 맞아야함
2. 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;
- 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을 넣어서 기본값 1이 안들어감
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을 넣을 수 없음
- words 테이블안에 데이터를 추가해주는 작업을 해준다.
desc member ;
insert into member(userid,userpw,name,hp,email,gender,ssn1,ssn2) values ('lkdf','ewfd','dfdfd','010-4349-8658','sdfdedg','hkgj','sdfewg','wegdgge');
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');
- 어제 만들어 놓았던 member테이블에 오늘 배웠던 데이터 추가하는 방법을 이용해 데이터를 추가해보자.
데이터 수정하기
1. update 테이블명 set 필드명1=값1, 필드명2=값2 .. ;
2. update 테이블명 set 필드명1=값1, 필드명2=값2 .. where 조건절;
-- update words set eng='Rucy'; #0 64 10:23:52 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.
#1. 일시적인 safe모드를 해제 : set sql_safe_updates=0;
#2. 영구적인 safe 모드를 해제 : Edit ->Preferentces-> SQL Editor->Safe Updates 체크 해제->workbench를 재시작
update member set point=point +50;
update words set lev=2 where eng='avocado';
update words set kor='어뤤지',lev=2 where eng='orange';
- 첫번째 코드 모든 유저에게 포인트가 50씩 늘어난다.
- 두번째는 각각의 내가 원하는 데이터를 넣는 방식이다.
예제
# member 테이블의 아이디가 '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 조건절;
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을 넣어서 기본값 1이 안들어감
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;
- *from 테이블명은 모든 컬럼을 다 가져온다는 뜻이다.
- 그걸 원치 않으면 가져오고 싶은 데이터를 적어주면 된다.
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='남자';
- userid , name 을 멤버 테이블에서 고르는데 userid 가 apple인 것만 찾아줘라는 뜻
예제 # point가 150이상인 member의 아이디,이름,포인트를 출력
답 : select userid,name,point from member where point>=150;
# words 테이블에서 lev이 null인 데이터를 출력
select *from words where lev is null; #o
select *from words where lev is not null # null값이 아닌값을 찾아줘
- is 구문을 써서 null인 것을 찾아달라고 하면 된다.
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]; # [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,point) values
('avocado', '5555', '안가도', '010-5555-5555', 'avocado@avocado.com', '남자', '001011', '1015111','200');
- 포인트값을 추가해준다
select * from member;
select * from member order by point;
- order by 뒤에 생략되었으니 오름차순으로 정리되어 나타난다.
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2,point) values
('cherry', '6666', '체리', '010-6666-6666', 'cherry@cherry.com', '여자', '001011', '4015111','200');
- 임의로 체리 데이터를 넣어준다
---------------------------------------------------------------예제-----------------------------------------------------------
# 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로 오름차순한 뒤 top3을 출력
답 : 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;
- 조건에 맞게 집게된 함수의 갯수가 나온다.
- as ' ' 로 별명을 붙여 쓸수 있다.
#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 userid from member group by userid; # 중복된 데이터가 없기때문에 모든 로우가 개별그룹임
- gender 끼리 그룹을 짠다.
- userid 끼리 그룹을 짠다.
select gender,count(userid) as '인원' from member group by gender;
select gender,count(userid) as '인원' from member group by gender having gender='여자';
- gender 끼리 그룹을 나누고 거기서 userid의 갯수를 세주고 인원이라고 별명을 준다.
- 위에 코드에서 gender가 여자인 값만 보고 싶을때 쓰는 코드이다.
---------------------------------------------------------------------예제-------------------------------------------------------------------------
# 포인트가 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. 비정규화
- 정규형에 일치하게 되어 있는 테이블을 정규형을 지키지 않는 테이블로 변경
- 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많이 나뉘어 성능이 저하된다면 비정규화를 하여 테이블을 다루는 것이 더 효율적일 수 있음
- 어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 하는 것이 좋음
Slack
w1625119637-4pe994825.slack.com
정규화와 비정규화에 관한 엑셀파일이다.
create table profile(
userid varchar(20) not null,
height double,
weight double,
mbti varchar(10),
foreign key(userid) references member (userid)
);
- profile이라는 새로운 테이블 만들되, userid를 member과 일치하게 가져간다.
insert into profile values ('apple',150,50,'istp');
select * from profile;
-- insert into profile values ('grapes',170,70,'infp'); # 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;
member테이블에 있는 userid,name,gender,mbti를 profile 테이블에 결합한다.
둘중어느 userid를 쓸것이냐고 물어보면 on member.userid라고 했으므로 member아이디를 쓴다.
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;