2. SQL 기초: 데이터베이스 조작 및 기본 명령어

1. 데이터베이스 및 테이블 생성하기

데이터베이스 선택

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)
    
    1. insert into 테이블명 values ( 값1, 값2, 값3...)
    2. insert into 테이블명 (필드명1, 필드명2...)  values ( 값1, 값2, 값3...)

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', '바나나', null); 
-- insert into words values ('banana', '바나나'); # 컬럼 갯수가 일치하지 않음
-- 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, 'avocade', '아보카도');
-- 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');

 

데이터 수정

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

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

update words set lev=1; >>> 모든 lev가 1로 변경
# 모든 유저에게 50 포인트를 더해주기
update member set point=point+50; >>> 모든 point가 50씩 증가

update words set lev=2 where eng='avocado'; >>> avocado의 lev이 2로 변경
update words set kor='어륀지' where eng='orange'; >>> orange의 kor이 어륀지로 변경

# 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='avodad'; # 데이터가 없어서 삭제되지 않음
delete from member where userid='avocado'; # 데이터가 있어서 삭제됨

 

데이터 검색

select 필드명1, 필드명2, ... from 테이블명;
select 필드명1, 필드명2, ... from 테이블명 where 조건절;

select eng, kor from words; >>> eng, kor열만 표시
select eng from words; >>> eng열만 표시
select kor, eng from words; >>> kor열 eng열 순으로 표시
select 100; >>> 100 표시
select 100 + 50; >>>>150 표시
select 100 + 50 as '덧셈'; >>> 표시와 150 표시
select 100 + 50 as 덧셈; >>> 위에와 동일한 결과 표시
select 100 + 50 '덧셈';  >>> 위에 동일한 결과 표시
select 100 + 50 '덧셈 연산'; # 따음표를 사용하는 이유는 띄어쓰기가 있을 수 잇기 때문
select eng as '영단어', kor as '뜻', lev as '수준' from words; >>> eng 표시가 영단어, kor 표시가 뜻, lev 표시가 수준으로 표시

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

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

 

2. 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, gender from member where gender='남자'; >>> gender가 남자인것만 표시
# point가 150 이상인 member의 아이디와, 이름, 포인트를 출력
select userid, point from member where point >=150;

# 로그인
select userid from member where userid='apple' and userpw='1234'; # 로그인 실패
select userid from member where userid='apple' and userpw='1111'; # 로그인 성공 >>> apple이 표시됨

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

# 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, point) values
('cherry', '6666', '채리', '010-6666-6666', 'cherry@avocado.com', '여자', '401011', '1015111', '200');
select * from member; 
select * from member order by point; # point 순으로 오름차순

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

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

 

데이터 Limit (일부 갯수의 row만 출력)

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

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(): row의 갯수를 세는 함수
select * from member;
select count(*) from member;
# 전체 데이터의 갯수를 출력하려면 null이 없는 필드를 선택 - > primary key를 선택하는 것을 추천
select count(userid) from member;
select count(zipcode) from member; # null을 세지 않음
select count(zipcode) as totalcount from member;

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

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

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

# max(): row의 최대값을 구함
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; # 중복된 데이터가 없기 때문에 모든 row가 개별
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;

 

3. 데이터 정규화: 필수 개념과 종류 (첨부파일 있음)

데이터 정규화
    - 데이터 베이스를 설계할 때 중복을 최소화하는 것
    - 조직화되어 있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나누는 것
    
    데이터 정규화가 필요한 경우
    - 데이터를 변경, 삽입, 삭제할 때 원하지 않게 데이터가 삭제되거나 가공되는 일이 발생할 수 있음(이상 현상)
    - 이상 현상이 발생할 가능성이 있다면 정규화가 필요
    
    정규화의 종류
    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, ... froma 테이블1 [inner, leftm right] join 테이블2
on 테이블1.필드명 = 테이블2.필드명

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;

정규화.xlsx

0.01MB