데이터 베이스와 SQL

데이터 베이스

여러 사람들이 공유할 목적으로 통합하여 관리하는 데이터의 집합

즉 데이터를 쌓는 창고 역할

DBMS

DataBase Management System의 약자로 데이터 베이스를 관리하는 시스템

SQL

데이터 베이스에서 내가 원하는 데이터만 골라서 볼때 사용하는 구문

Structured Query Language의 약자로 언어의 한 종류

데이터 베이스에서 데이터를 추가 조회 수정 삭제하는데 특화된 언어

CRUD 라고도 부름 (추가-Create , 조회-Read, 수정-Update, 삭제 Delete)

데이터를 정의, 조작 제어하는데 각 목적에 맞게 크게 세가지로 구분 가능

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Languege)

속성	설명	주요 명령어
DDL	데이터베이스나 테이블 등을 생성, 삭제하거나 그 구조를 변경하기 위한 명령어	CREATE, ALTER, DROP
DML	데이터베이스에 저장된 데이터를 처리하거나 조회, 검색하기 위한 명령어	INSERT, UPDATE, DELETE, SELECT 등
DCL	데이터베이스에 저장된 데이터를 관리하기 위하여 데이터의 보안성 및 무결성 등을 제어하기 위한 명령어	GRANT, REVOKE 등

관계형 DBMS 사용 할 예정

AWS - RDS (MySQL(MariaDB), PostgreSQL , …)

DBMS Ranking 참고

https://db-engines.com/en/ranking

RDBMS (Relational Database Management System)

관계형 데이터 베이스

데이터를 2차원의 테이블 구조로 저장

테이블 형태이다보니 마치 엑셀을 다루는 듯한 느낌도 든다.

관계형 데이터베이스는 사람이 쉽게 읽을 수 있는 형태로 데이터를 다루니 관리가 쉽다는 장점이 있다.

관계형 데이터 베이스의 특징

테이블간의 관계를 정의하여 데이터 사이의 연관성을 표현한다.

• 1:1 관계: 한 개의 행이 다른 한 개의 행에 대응
• 1:N 관계: 한 개의 행이 다른 여러 개의 행에 대응
• N:M 관계: 여러 개의 행이 다른 여러 개의 행에 대응

데이터 베이스 용어

테이블

데이터를 구성하기 위한 가장 기본적인 단위

테이블은 행과 열로 구성되며 행은 여러 속성으로 구성된다.

행 (row)

테이블의 구성 요소중 하나이며 테이블의 가로로 배열된 데이터의 집합.

행은 반드시 고유한 식별자인 기본키를 가지고 행은 레코드(record)라고 부름.

열 (column)

테이블의 구성 요소중 하나이며 행에 저장되는 유형의 데이터

열은 각 요소에 대한 속성을 나타내며 무결성을 보장한다.

기본키 (primary key)

행을 구분할 수 있는 식별자

테이블에서 유일해야하며 중복 값을 가질 수 없다.

기본키의 값은 수정되어서는 안되며 유효한 값이어야 한다. 즉 null값이 될 수 없다.

쿼리(query)

데이터베이스에서 데이터를 조회하거나 삭제, 생성, 수정 같은 처리를 하기 위해 사용하는 명령문.

SQL이라는 데이터 베이스 전용 언어를 사용하여 작성함.

SQL이라는 단어와 혼용되기도 함.

DML

Data Manipulation Language의 약자로 데이터베이스에서 데이터를 조작하는데 사용한다. SQL 문법이라고 함.

즉 SQL을 이용하여 데이터를 조회, 삽입, 수정, 삭제 하기 위한 문법

SELECT	데이터 조회에 사용
INSERT	데이터 삽입에 사용
UPDATE	데이터 수정에 사용
DELETE	데이터 삭제에 사용

조회

SELECT, FROM, WHERE

SELECT	조회할 열을 지정
FROM	조회할 테이블을 지정
WHERE	조회할 데이터를 필터링

블럭을 지정하지 않으면 처음부터 실행이 되어서 오류가 발생할 수 있다.

아니면 ctrl + enter 로 실행하면 커서가 있는 쿼리문만 실행 가능

삽입

INSERT INTO

테이블에 새로운 행(row)을 삽입할 때 INSERT INTO 문을 사용합니다.

수정

UPDATE	수정할 테이블을 지정
SET	데이터 수정
WHERE	수정할 데이터를 필터링

수정할때는 where 조건절을 무조건 적어준다.

UPDATE students
SET   name = '이름'
// 이렇게 실행시키면 이름이 모~두 바뀐다. 사고다 사고.

삭제

DELETE FROM	삭제할 테이블을 지정
WHERE	삭제할 데이터를 필터링

DELETE FROM : where절을 작성하지 않으면 모든 데이터가 지워지지만 테이블 구조는 살아있어 데이터를 기존에 설정해둔 설정에 맞춰 넣을 수 있다. 로그도 살아 있음.

TRUNCATE : 데이터만 삭제, 로그도 삭제.. 초기화..!

DROP TABLE : drop은 데이터 및 테이블 구조까지 모든 정보가 사라진다.

delete

• soft delete - 바로 삭제하지 않고 겉보기로 일단 삭제
  • UPDATE customer SET active=N WHERE id = 탈퇴한 회원 ID
• hard delete - 바로 삭제
  • DELETE FROM customer WHERE id = 탈퇴한 회원

내장함수

함수란

데이터베이스에서 함수는 프로그래밍에서의 함수와 역할이 동일하다

입력 값을 받아 계산(작업)을 수행하고 결과를 반환하는 구조로 되어있다.

이때 내장함수란 DBMS에서 기본적으로 제공하는 함수를 말한다.

자주 사용하는 내장 함수

SUM

숫자의 합을 반환한다

// 모든 학생의 나이의 합
SELECT SUM age FROM students;

AVG

숫자의 평균을 반환한다.

// 모든 학생의 나이의 평균값
SELECT AVG(age) FROM students;

MAX

숫자의 최대값을 반환한다

MIN

숫자의 최소값을 반환한다

COUNT

행의 개수를 반환한다.

//학생들의 주소에서 중복을 제거한 값이 몇 개인지 구한다.
SELECT COUNT(DISTINCT address) FROM students;

CONCAT

두 문자열을 연결한다.

CHAR_LENGTH

문자열의 길이를 반환한다.

REPLACE

특정 문자열을 다른 문자열로 치환한다.

NOW

현재의 날짜와 시간을 반환한다.

• 실습 코드

  CREATE DATABASE test_db;
  
  -- SQL 주석처리
  
  -- 해당 데이터 베이스를 사용하겠다 선언 
  use test_db;
  
  -- 가독성을 위해서 대소문자 구분해서 사용하기 
  -- DDL문 
  CREATE TABLE students (
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255) NOT NULL
  );
  
  INSERT INTO students (name, age, address) VALUES
  ('이황', 28, '경상북도'),
  ('정약용', 29, '경기도'),
  ('김정호', 30, '전라북도'),
  ('박지원', 31, '전라북도'),
  ('김홍도', 32, '경기도'),
  ('신윤복', 33, '서울특별시'),
  ('김광균', 34, '서울특별시'),
  ('한용운', 35, '경상남도'),
  ('박두진', 36, '경기도');
  
  CREATE TABLE classes (
    name VARCHAR(255) NOT NULL,
    class_name VARCHAR(255) NOT NULL
  );
  
  INSERT INTO classes (name, class_name) VALUES
  ('이황', '데이터베이스'),
  ('이황', '알고리즘'),
  ('정약용', '데이터베이스'),
  ('김정호', '자료구조'),
  ('박지원', '데이터베이스'),
  ('김홍도', '알고리즘'),
  ('신윤복', '자료구조'),
  ('신윤복', '알고리즘'),
  ('김광균', '데이터베이스'),
  ('김광균', '자료구조'),
  ('김광균', '알고리즘');
  
  -- SELECT
  SELECT *
  FROM 	students;
  
  -- Q1 20대만 출력 && || 도 사용 가능
  SELECT *
  FROM students
  WHERE age >= 20 AND age < 30;
  
  -- Q2 중복 행 제거
  SELECT DISTINCT class_name
  FROM classes;
  
  -- Q3 데이터 베이스 강의 듣는 학생 출력
  SELECT name
  FROM classes
  WHERE class_name = '데이터베이스';
  
  -- Q4 김광균 학생이 듣는 강의명 출력
  SELECT class_name
  FROM classes
  WHERE name = '김광균';
  
  -- Q5 제주도에 사는 학생 조회
  SELECT *
  FROM students
  WHERE address = '제주도';
  
  -- 한 행 삽입
  -- 이때 값을 적지 않고 추가하면 null값이 부여된다. 만약 Not Null이라면 Null값이 부여되지 않고 오류가 난다.
  INSERT INTO students(name, age, address)
  -- VALUES ('김민지', 20, '서울'),
  VALUES ('김민지', 20, '강원도 춘천');
  SELECT *
  FROM students;
  
  -- line 10번에 주소값을 not null 설정값을 주었는데 값을 설정하지 않아서 오류 발생.
  INSERT INTO students (name, age)
  VALUES ('신기루', 20);
  
  -- '경상남도'에 사는 학생 정보를 조회해서 새로운 row로 저장하기
  INSERT INTO students (name, age, address)
  SELECT name, age, address
  FROM students
  WHERE address = '경상남도';
  SELECT *
  FROM students;
  
  -- UPDATE
  SET SQL_SAFE_UPDATES = 0;		-- 1175에러 안전모드 해제 아래줄은 설정 코드
  SET SQL_SAFE_UPDATES = 1;
  
  -- 필드 하나만 수정
  UPDATE students
  SET age = 99
  WHERE name = '이황';
  SELECT *
  FROM students;
  
  -- 필드 여러개 수정
  UPDATE students
  SET age = 10, address = '서울특별시'
  WHERE name = '정약용';
  SELECT *
  FROM students;
  
  -- 조건부 수정
  -- 33세 미만 학생들 주소지 변경 '부산광역시'
  UPDATE students
  SET address = '부산광역시'
  WHERE age < 30;
  SELECT *
  FROM students;
  
  -- DDL 테이블 삭제 쿼리
  DROP TABLE students;
  -- 테이블 생성
  CREATE TABLE students (
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255) NOT NULL
  );
  -- 데이터 삽입
  INSERT INTO students (name, age, address) VALUES
  ('이황', 28, '경상북도'),
  ('정약용', 29, '경기도'),
  ('김정호', 30, '전라북도'),
  ('박지원', 31, '전라북도'),
  ('김홍도', 32, '경기도'),
  ('신윤복', 33, '서울특별시'),
  ('김광균', 34, '서울특별시'),
  ('한용운', 35, '경상남도'),
  ('박두진', 36, '경기도');
  
  SELECT *
  FROM students;
  
  -- DELETE FROM
  DELETE 
  FROM students
  WHERE name = '이황';
  SELECT *
  FROM students;
  -- 조건절로 삭제하기
  DELETE 
  FROM students
  WHERE age BETWEEN 30 AND 33;
  SELECT *
  FROM students;
  
  -- '자료구조' 강의가 취소되었습니다. 강의 정보를 삭제해주세요.
  DELETE
  FROM classes
  WHERE class_name = '자료구조';
  SELECT *
  FROM classes;
  
  -- 나이가 40세 이상인 학생 정보를 삭제해주세요
  DELETE
  FROM students
  WHERE age > 40;
  SELECT *
  FROM students;
  
  -- 나이가 35 이상, 37이하인 학생 정보를 삭제 해주세요 (BETWEEN AND 사용)
  DELETE
  FROM students
  WHERE age BETWEEN 35 AND 37;
  SELECT *
  FROM students;
  
  -- TRUNCATE TABLE
  TRUNCATE TABLE students;
  SELECT * FROM students;	-- 테이블은 남아있음.
  TRUNCATE TABLE classes;
  SELECT * FROM classes;
  
  -- DROP TABLE
  DROP TABLE classes;
  SELECT * FROM classes;		-- 에러, 없는 테이블 찾아서
  DROP TABLE students;
  
  -- 숫자 관련 내장함수 sum(), avg(), max(), min(), count()
  SELECT sum(age) FROM	students;
  SELECT avg(age) FROM	students;
  SELECT MAX(age) FROM	students;
  SELECT MIN(age) FROM	students;
  SELECT count(distinct address) FROM		students;
  
  -- 문자열 관련 내장 함수 char_length(), concat(), replace()
  SELECT address, char_length(address) FROM	students;	-- 문자 길이를 반환
  SELECT address, length(address) FROM	students;	-- 문자열의 byte 길이 출력해준다. (UTF-8 기준 한글 한글자당 3byte)
  SELECT name, address, concat(name, address) FROM	students;
  SELECT address, replace(address, '도', '레') FROM		students;
  
  SELECT * FROM students;
  -- Q1 30대 학생들 중 가장 어린 학생의 나이를  출력
  SELECT MIN(age)
  FROM students
  WHERE age >= 30;
  -- = WHERE age BETWEEN 30 AND 39
  
  -- Q2 20대 학생 나이의 평균을 구하세요
  SELECT AVG(age)
  FROM students
  WHERE age BETWEEN 20 and 29;
  
  -- Q3 35세 이상의 학생은 총 몇명인지 출력
  SELECT count(*) -- unique값을 넣어줌. ex) id, name
  -- SELECT count(age)
  FROM students
  WHERE age >= 35;
  
  -- Q4 가장 가격이 낮은 제품을 출력 smallPrice
  SELECT MIN(Price) AS smallPrice 
  FROM Products;
  
  -- 시간 함수 DBMS NOW()
  SELECT NOW();