Database 기초

1. Database

데이터는 정보를 구성하는 단위. > 정보는 지식을 구성하는 단위. > 지식은 지혜를 구성하는 단위

- 데이터는 관찰과 실험, 조사를 통해 얻은 정보로 추론과 추정의 근거가 되는 사실.

- 데이터는 단순 사실에 불과하지만, 특정한 의미가 부여되어 가공되는 과정을 거치면 > 정보가 된다. 

- The term data means groups of information that represent the qualitative attributes of a variable or set of variables.

 

1) 데이터베이스란?

: 여러 사람들이 공유하고 사용할 목적으로 통합 관리되는 데이터들의 모임. (데이터 창고!!)

 

2)  데이터베이스 관리 시스템 (DBMS) ?

: 다수의 사용자들이 데이터베이스 내의 데이터를 접근할 수 있도록 해주는 소프트웨어들.

 

3) 관계형 데이터베이스란? (Relational Database)

: 데이터를 관계로 표현한 데이터베이스. 

• 데이터를 테이블, 행, 열 등으로 나눠서 구조화해서 체계적으로 관리.
• 자료를 여러 테이블에 나눠서 관리하고, 테이블들 간에 관계를 설정
• 데이터 무결성 유지에 장점
• SQL을 이용해 데이터를 조회하고 조작

- 스키마  : RDB가 가진 데이터의 구조를 표현한 것. (기본적 DB 자료 구조와 표현방법 명세)

- 테이블 : 필드와 레코드를 사용해 조직된 데이터 요소들의 집합.

• 필드 : 속성 또는 칼럼. 각 필드에는 고유한 데이터 형식이 지정됨.
• 레코드 : 튜플 또는 행. 레코드 하나는 테이블 하나의 데이터를 의미.

 

- Primary Key (기본키) : 각 레코드의 고유한 값. 다른 항목과 중복 불가. DB 관리와 테이블 간 관계 설정 시 중요하게 활용.

- Foreign Key (외래키) : 한 테이블의 데이터를 표현할 때 다른 테이블의 데이터가 필요한 경우...> 해당 테이블의 PK를 테이블에 저장해서 사용한다. > 이것이 FK.

 

4) RDBMS (Relational DataBase Management System)

: 관계형 데이터베이스를 다루기 위한 프로그램의 일종 . 

- 서버의 형태로 프로그램과 소통하여 데이터베이스를 조작할 수 있게 해주는 경우가 많다. 

 

2. SQL DDL

1) SQL (Structured Query Language) 

: 관계형 데이터베이스에서 데이터를 관리하기 위해 사용하는 언어

• DDL (Data Definition Language) : 데이터의 모습을 정의하기 위한 명령 (테이블을 만들고 수정하고 삭제하고...)
• DML (Data Manipulation Language) : 데이터 조작을 위한 명령
• DCL (Data Control Language) : 데이터 접근 권한 관련 명령

 

2) CREATE TABLE

: DB에 새 테이블을 만들 때 사용.

CREATE TABLE + 테이블 이름을 명시 + 이후 각 필드의 형태를 정의

CREATE TABLE table_name (
	column_name1 data_type constraints,
    column_name2 data_type constraints
);

CREATE TABLE users (
	id INTEGER,
    username VARCHAR(64),
    first_name VARCHAR(32),
    last_name VARCHAR(32),
    email VARCHAR(100)
);

 

<SQLite 기준>

• NULL - 정보가 없는 데이터
• INTEGER - 정수형 데이터
• REAL - 실수형 데이터
• TEXT - 문자형 데이터
• BLOB - Binary Large Object

(예시를 보면 VARCHAR가 있는데.... 사실 SQLite에는 없는 형태이긴 하다...

그러나 SQLite는 호환성을 위해 Type Affinity(타입 선호도)라는 개념을 적용한다... (다른 DB에서 흔히 사용하는 타입을 SQLite의 타입으로 변환 해준다!!)

 

** 데이터 무결성과 Constaints

• NOT NULL : 컬럼에 NULL이 들어오지 못함
• UNIQUE : 해당 컬럼의 값은 고유해야 함
• PRIMARY KEY : 해당 컬럼을 PK로 지정 (암시적으로 NOT NULL)
• AUTOINCREMENT : 사용되지 않은 값이나 이전의 삭제된 행의 값을 재사용하지 않음.

 

3) ALTER TABLE 

: 이미 존재하는 테이블을 수정.

• RENAME TO : 테이블 이름을 수정
• RENAME COLUMN : 테이블에 컬럼 이름을 변경
• ADD COLUMN : 테이블에 컬럼을 추가 (**이때 제약사항 추가시 NOT NULL에 주의... 이전에 생성된 데이터를 어떻게 처리할 것인가!! 이것을 결정하여 DEFAULT로 설정해주어야 한다.)
• DROP COLUMN : 테이블의 칼럼을 제거

ALTER TABLE users RENAME TO users_backup;
ALTER TABLE users_backup RENAME TO users;

ALTER TABLE users RENAME COLUMN first_name to given_name;
ALTER TABLE users RENAME COLUMN last_name to sur_name;

ALTER TABLE uses ADD COLUMN address VARCHAR(256);

ALTER TABLE users ADD COLUMN phone VARCHAR(128) NOT NULL; --potential problem

ALTER TABLE users DROP COLUMN phone;

 

4) DROP TABLE

: 테이블을 제거 복구 불가!!!

DROP TABLE users;
DROP TABLE IF EXISTS users;

 

 

3. SQL Select

1) DML 

: 테이블 내 데이터를 조작하기 위한 언어

- INSERT, SELECT, UPDATE, DELETE

 

2) SELECT 

• SELECT * FROM table_name : 전체 컬럼 조회
• SELECT first_name, age FROM users; : 조회할 컬럼 지정

3) SELECT ORDER BY

: 순서를 지정해서 조회 (기본 오름차순, 내림차순은 DESC 추가)

- 두가지 이상의 컬럼을 지정할 시 순서대로 적용된다.

SELECT * FROM users ORDER BY age DESC, first_name; -- 나이 내림차순 이후 이름으로
SELECT * FROM users ORDER BY age DESC;

 

4) SELECT DISTINCT

: 조회할 컬럼의 중복을 제거.

- 두가지 이상의 컬럼을 지정할 시, 둘을 하나의 컬럼으로 취급한다! 

SELECT DISTINCT age FROM users;
SELECT DISTINCT first_name, last_name FROM users;
SELECT DISTINCT country FROM users ORDER BY country;

 

5) SELECT WHERE

: 조회 시 조건을 지정.

- 기본적인 등호/부등호 연산과 AND, OR를 조합할 수 있다. 

SELECT first_name FROM users WHERE age<30;
SELECT first_name, last_name FROM users WHERE age >= 30;
SELECT first_name, age, balance FROM users WHERE balance <150;
SELECT frist_name, last_name, age FROM users WHERE age<30 AND balance >180;

 

6) SELECT WHERE LIKE

: 문자열 비교 조회, 일부분이 일치하는지를 검사.

• % : 0개 이상의 문자와 동일하게 취급
• _ : 1개 이상의 문자와 동일하게 취급

SELECT first_name FROM users
WHERE email LIKE '%naver.com';

SELECT last_name, email FROM users
WHERE email LIKE '%q%';

SELECT phone FROM users 
WHERE phone LIKE '010-%';

SELECT phone FROM users
WHERE NOT phone LIKE '010-%';

 

7) SELECT LIMIT OFFSET

: 조회 결과의 일부분만 반환.

- LIMIT 뒤의 숫자만큼의 레코드가 조회됨.

- OFFSET과 함께 사용할 경우 > 해당 기록만큼 건너뛰고 조회함.

SELECT id, first_name, last_name
FROM users
WHERE age<40
LIMIT 20; -- 20만큼의 레코드 조회

SELECT id, first_name, last_name
FROM users
WHERE age<30
LIMIT 20 OFFSET 85; --86번부터 20만큼의 레코드 조회

 

 

4. SQL Insert Update Delete

1) INSERT

: 데이터를 추가할 때 사용.

- 한 번에 여러 데이터를 입력할 수도 있음. >> 튜플마다 쉼표로 레코드 구분.

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

INSERT INTO users(first_name, last_name, age, balance)
VALUES ('hehesim', 'kang', 99, 100);

INSERT INTO users(first_name, last_name, age, balance)
VALUES 
	('hehesim', 'kang', 99, 100),
    ('Chad', 'Kim', 88, 122),
    ('Brad', 'Lee', 15, 10);

 

2) UPDATE

>> UPDATE table_name SET column = value WHERE (조건)

- WHERE을 생략할 경우 > 테이블 전체가 수정된다..!

UPDATE users
SET phone = '010-1111-1111',
	age = 50
WHERE first_name = 'hehesim';

 

3) DELETE 

>> DELETE FROM table_name WHERE (조건)

- WHERE을 생략할 경우 > 테이블 전체 레코드가 사라진다...!

DELETE FROM users
WHERE first_name = 'hehesim';

 

 

5. Aggregate Functions

1) 집계하기

: 여러 열의 데이터를 모아, 특정 컬럼에 대해 집계할 수 있다.

• COUNT() : 갯수
• AVG() : 평균
• MAX() : 최대
• MIN() : 최소
• SUM() : 합계

SELECT COUNT(*) FROM user;
SELECT AVG(balance) FROM user;
SELECT MAX(age) FROM user;
SELECT MIN(age) FROM user;
SELECT SUM(balance) FROM user;

 

- WHERE절을 추가하면 특정 행들에 대한 데이터만 집계도 가능~

SELECT COUNT(*) 
FROM user
WHERE age<30; --30살 미만인 사람들의 명수

 

2) GROUP BY

: 어떤 컬럼의 데이터를 기준으로 같은 열들을 묶을 수 있다.

ex) WHERE절만 가지고 집계를 하려고 하는데 각국의 데이터를 찾아서 정리해야 한다면...? >> 이때 GROUP BY 를 사용한다.

- 집계함수 결과를 바탕으로 정렬도 가능

- 특정 컬럼을 기준으로 그룹을 만들 필요는 없다.

 

SELECT AVG(balance)
FROM user
WHERE country = 'South Korea';
-- 조회는 되지만 각국에 대하여 SQL을 반복해야 한다.

--국적과 국적별 평균 잔고를 조회한다.
SELECT country, AVG(balance)
FROM user
GROUP BY country; --국적으로 그룹을 만든다.

SELECT country, AVG(balance)
FROM user
GROUP BY AVG(balance); -- 집계함수의 결과를 바탕으로 정렬도 가능.

SELECT age/10, AVG(balance)
FROM user
GROUP BY age/10; --꼭 컬럼기준일 필요는 없다.

 

3) HAVING

: 집계 결과를 바탕으로 조회 조건을 만들고 싶다면 ? HAVING을 사용.

cf) WHERE는 집계하기 전 데이터를 걸러내는 역할!

--국적과 국적별 평균 나이를 조회
SELECT country, AVG(age)
FROM user
--국적으로 그룹을 만든다
GROUP BY country
-- 이때 나이의 평균이 40 미만이어야 함.
HAVING AVG(age) <40;