[SQLite] DB조작과 모델링

한결·2023년 4월 6일
db
0

WEB

목록 보기
17/63
post-thumbnail

Grouping data

Aggregate function

"집계함수"
값 집합의 최대값, 최소값, 평균, 합계 및 개수를 계산

  • 값 집합에 대한 계산을 수행하고 단일 값을 반환함
    • 여러 행으로 부터 하나의 결과 값을 반환하는 함수
  • SELECT 문의 GROUP BY 절과 함께 사용
  • 제공하는 함수 목록
    • AVG(), COUNT(), MAX(), MIN(), SUM()
  • AVG(), MAX(), MIN(), SUM()는 숫자를 기준으로 계산이 되어져야 하기 때문에 반드시 컬럼의 데이터 타입이 숫자(INTEGER)일 때만 사용 가능

예시

  • user 테이블의 전체 행 수 조회하기 
SELECT COUNT(*) FROM users;
  • COUNT()는 NULL 도 센다
    근데, COUNT("name") 처럼 column 지정해주면 NULL 안센다
  • 전체 유저의 평균 balance를 알고 싶음 
SELECT avg(balance) FROM users;

  • 지역별 평균 balance

    • 전라북도 
      SELECT DISTINCT country, avg(balance) FROM users WHERE country = "전라북도" ;

      -> 하나하나는 하겠는데 한번에 다 는 어캐하지

  • 지역별 평균 balance 필요 

SELECT DISTINCT country, avg(balance) FROM users GROUP BY country;

  • 평균 오름차순 까지 해주기 

    SELECT DISTINCT country, avg(balance) FROM users GROUP BY country ORDER BY avg(balance) DESC;

  • 나이가 30살 이상인 사람들의 평균 나이 구하기 

SELECT AVG(age) FROM users WHERE age >= 30;

  • 각 지역별로 몇명 살고 있는지 조회 
SELECT country,COUNT(country) FROM users GROUP BY country;

참고

  • COUNT 참고사항
    • 이전 쿼리에서 COUNT(), COUNT(age), COUNT(last_name) 등 어떤 컬럼을 넣어도 결과는 같음
    • 현재 쿼리에서는 그룹화된 country를 기준으로 카운트 하는 것이기 때문에 어떤 컬럼을 카운트 해도 전체 개수는 동일하기 때문

GROUP BY clause

SELECT column_1, aggregate_function(column_2) FROM table_name GROUP BY column_1, column_2;

특정 그룹으로 묶인 결과를 생성

  • 선택된 컬럼 값을 기준으로 데이터들의 공통 값을 묶어서 결과로 나타냄
  • SELECT 문에서 선택적으로 사용가능한 절
  • SELECT 문의 FROM 절 뒤에 작성
    • WHERE 절이 포함된 경우 WHERE 절 뒤에 작성 해야함
  • 각 그룹에 대해 MIN,MAX,SUM,COUNT 또는 AVG와 같은 집계 함수를 적용하여 각 그룹에 대한 추가적인 정보 제공가능

GROUP BY 실습

  • 각 성씨가 몇명 있는지 조회 
SELECT last_name, COUNT(*) FROM users GROUP BY last_name;

  • AS키워드를 사용해 컬럼명을 임시로 변경 및 조회 할 수 있음 
SELECT last_name, COUNT(*) AS the_number_of_name  FROM users GROUP BY last_name;

  • 인원이 가장 많은 성씨 순으로 조회하기 
SELECT last_name, COUNT(*) AS the_number_of_name  FROM users GROUP BY last_name ORDER BY COUNT(*) DESC;

  • 각 지역별 평균나이 조회 
SELECT country, AVG(age) FROM users GROUP BY country;

Changing data

  • 데이터 삽입, 수정, 삭제

    • INSERT
    • UPDATE
    • DELETE

  • 사전준비

    • 테이블 생성 
      CREATE TABLE classmates (
name TEXT NOT NULL,
age INTEGER NOT NULL,
address TEXT NOT NULL
);

INSERT statement

INSERT INTO table_name (column1, column2, ... ) VALUES (value1, value2, ..);

새 행을 테이블에 삽입

  • 문법 규칙

  1. 먼저 INSERT INFO 키워드 뒤에 데이터를 삽입할 테이블의 이름을 지정

  2. 테이블 이름 뒤에 쉼표로 구분된 컬럼 목록을 추가

    • 컬럼 목록은 선택 사항이지만 컬럼 목록을 포함하는 것이 권장됨

  3. VALUES 키워드 뒤에 쉼표로 구분된 값 목록을 추가

    • 만약 컬럼 목록을 생략하는 경우 값 목록의 모든 컬럼에 대한 값을 지정해야 함
    • 값 목록의 값 개수는 컬럼 목록의 컬럼 개수와 같아야 함

예시

  • 단일 행 삽입하기 
INSERT INTO classmates (name, age, address) VALUES ('엄한결', 27, '서울');
INSERT INTO classmates VALUES ('어무해', 27, '장고');

  • 여러 행 삽입 
INSERT INTO classmates 
VALUES 
('상재우', 27, '서울'),
('중재우', 27, '서울'),
('하재우', 27, '서울')
;

UPDATE statement

UPDATE table_name 
SET column_1 = new_value_1,
  column_2 = new_value_2
WHERE
  search_condition;
  1. UPDATE 절 이후에 업데이트할 테이블 지정
  2. SET 절에서 테이블의 각 컬럼에 대해 새 값을 설정
  3. WHERE 절의 조건을 사용하여 업데이트할 행을 지정
  • WHERE 절은 선택사항
    생략하면 UPDATE 문은 테이블의 모든 행에 있는 데이터를 업데이트 함
  1. 선택적으로 ORDER BY 및 LIMIT 절을 사용하여 업데이트 할 행 수를 지정 할 수도 있음

예시

  • 2번 데이터의 이름을 '김철수', 주소를 '제주'로 수정하기 
UPDATE classmates
SET 
name = '김철수',
address = '제주'
WHERE 
name = '어무해';

DELETE statement

DELETE FROM table_name
WHERE search_condition;

테이블에서 행을 제거

  • 테이블의 한 행, 여러 행 및 모든 행을 삭제할 수 있음
  • 문법 규칙
    1. DELETE FROM 키워드 뒤에 행을 제거하려는 테이블의 이름을 지정
    2. WHERE 절에 검색 조건을 추가하여 제거할 행을 식별
      • WHERE 절은 선택사항
        생략하면 DELETE 문은 테이블의 모든 행을 삭제
    3. 선택적으로 ORDER BY 및 LIMIT 절을 사용하여 삭제할 행 수를 지정할 수도 있음

예제

  • 5번 데이터 삭제 
DELETE FROM classmates
WHERE rowid = 5;

  • 이름에 '우'가 포함되는 데이터 삭제 
DELETE FROM classmates
WHERE name LIKE '%우%';

  • 테이블 모든 데이터 삭제 
DELETE FROM classmates;

정규형

  • 테이블 나누기 -> 유지보수에 좋음

  • 데이터 베이스 정규형

    • 데이터베이스를 구조화 하는 방법론
    • 데이터의 중복을 최소화하고 일관성과 무결성을 보장하기 위함
    • 데이터의 구조를 더 좋은 구조로 바꾸는 것을 정규화하고 함
    • 관계형 데이터베이스의 경우 6개의 정규형이 있음

제 1 정규형

  • 하나의 속성 값이 복수형을 가지면 안됨 (하나의 속성엔 하나의 값만)
이름나이취미
엄결27헬스
엄한결27노래, 축구
  • 2번 행의 취미 column처럼 하지말라는 거

제 2 정규형

  • 테이블의 테마와 관련 없는 컬럼은 다른 테이블로 분리하는 것
회원 id강사명운동가격
1000엄헬스30000
1001엄한수영1000
1002엄한결필라테스20000
1003엄결요가40000

-> 이런 형태의 DB에서는 수영의 금액을 변경해야하고 회원이 몇 천명이면 DB 전부 뒤져서 하나하나 다 수정해야함

-> 수영하는 회원이 추가되면 쓸데없는 데이터 (금액같은)도 하나한 다 추가해야함

따라서, table을 나눠야함

  • 등록 table
회원 ID강사명
1000엄
1001한
1001결
1001한결
  • 운동 table
강사명운동금액
엄결헬스30000
엄한결수영10000
  • 이런식으로 테마 별로 table을 분리하는 것이 제 2 정규형

제 2 정규형 추가 설명

  • 테이블에서 부분 함수적 종속성을 제거한 것

  • 복합 키, composite key

    • 테이블에서 행을 유일하게 구분하기 위해 두 개 이상의 속성을 조합하여 사용하는 기본키

  • composite PK, Partial Dependency를 키워드로 추가 공부 필요

제 3 정규형

강사명운동금액
엄결헬스30000
엄한결수영10000
결헬스30000

  • 금액은 pk인 강사명과 관련 없음, 금액은 운동column에 의존
    -> 분리

    • 강사 테이블
      강사명운동
      엄결헬스
      엄한결수영
      결헬스
    • 가격 테이블
      운동금액
      헬스30000
      수영10000

  • Transitive Dependency 키워드로 추가 공부 필요

JOIN

  • table 나눠서 관리 편해짐
    -> 2번글을 작성한 유저의 권한을 알고 싶으면 다시 모아서 table을 1개로 만들어야 함
    == JOIN

CROSS JOIN

SELECT * FROM articles,users;

  • CROSS JOIN은 데이터가 너무 많으므로 거의 안씀
    -> articles의 userid와 users의 id가 같은 것만 join하고 싶을 때
    -> 방법 2가지
    1. WHERE 사용
      SELECT * FROM articles,users WHERE articles.userid = users.rowid;
    2. INNER JOIN

INNER JOIN

{테이블 1} INNER JOIN {테이블 2} ON {조건식}

SELECT * FROM articles INNER JOIN users ON userid = users.rowid

LEFT (OUTER) JOIN

-> INNER JOIN

-> Ariticles(왼쪽꺼) 누락없이 users 데이터가 있으면 가져오기 

SELECT * FROM articles LEFT JOIN users
ON userid = users.rowid;

RIGHT (OUTER) JOIN

users(오른쪽 꺼) 누락없이 articles 있으면 가져오기 

SELECT * FROM articles RIGHT JOIN users ON userid=users.rowid;

정리

JOIN

두 개 이상의 테이블에서 데이터를 가져와 결합하는 것

  • CROSS JOIN : 모든 조합 출력
  • INNER JOIN : 두 테이블에서 일치하는 데이터만 출력
  • LEFT JOIN : 왼쪽 테이블의 데이터를 기준으로 오른쪽 데이터와 결합, 없으면 NULL
  • RIGHT JOIN : LEFT 반대
profile
한결
이전 포스트

[SQLite] Database

다음 포스트

[SQLite] Transaction

0개의 댓글