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;
'Programming > Database' 카테고리의 다른 글
| Transaction (0) | 2024.02.02 |
|---|---|
| JDBC (1) | 2023.12.20 |
| 정규화 Normalization와 조인 Join (1) | 2023.12.19 |
| [연습문제 모음] (0) | 2023.12.18 |
| JDBC와 DBCP(DataBase Connection Pool) (0) | 2023.09.15 |
