[TIL] 20.12.22 DBMS, RDB, MariaDB 설치, 쿼리 실습

[20.12.22] DBMS, RDB, MariaDB 설치, 쿼리 실습

 

너무 귀찮아서 한참 밀렸다... 거의 한 달을 쫓기고 있다... 이제까진 학생 때 배운 내용이지만 다음 주부턴 아예 모르는 내용을 배우기 때문에 포스팅이 더 힘들어질 테니 그전에 얼른 따라잡아야겠다...

 

* MariaDB 설치

- [MariaDB] MariaDB 설치 (Windows용) 참고

 

* DBMS (DataBase Management System)

- 데이터베이스를 관리/운영하는 역할

- 여러 사용자, 응용 프로그램이 DBMS에 동시에 접속하고 데이터를 공유할 수 있다.

 

1) DB/DBMS의 특징

(1) 무결성

> DB 안에 있는 데이터는 오류가 없어야 한다.

> 제약 조건이라는 특성을 가진다.

 

(2) 독립성

> DB의 크기를 변경하거나 파일의 저장소 변경 시 기존에 작성된 프로그램은 영향을 받지 않아야 한다.

 

(3) 보안

> DB 내부 데이터를 소유한 사람, 혹은 접근이 허가된 사람만 접근할 수 있다.

> 접근 시 사용자 계정에 따라서 권한이 달라진다.

 

(4) 중복 최소화

> 동일한 데이터가 중복되는 것을 방지한다.

 

(5) 안정성

> DBMS는 백업 및 복원 기능을 제공한다.

 

2) DB의 발전

> 오프라인 관리 : 종이에 직접 내용을 기록

> 파일 시스템 : 메모장, 엑셀을 활용해서 파일에 기록/저장 (중복 위험성 존재)

> DBMS : 파일 시스템 단점을 보완하여 DBMS로 대량의 데이터를 관리/운영

 

3) DB의 분류

(1) 계층형

> 특징 : 각 계층이 Tree 형태로 되어 있으며 1:N의 관계를 가진다.

> 단점 : 처음 구축한 이후로 구조 변경이 까다롭다. / 접근의 유연성이 부족(임의 검색 어려움)

 

(2) 망형

> 특징 : 1:1, 1:N, N:M 관계를 지원하여 빠른 데이터 추출이 가능해졌다.

> 단점 : 내부 포인터가 복잡해 프로그래머가 모든 구조를 이해해야만 프로그램 작성이 가능했다.

 

(3) 관계형 (RDB)

> 특징 : 테이블이라는 최소 단위로 데이터를 구성한다.

> 장점 : 업무 변화에 쉽게 적응한다. / 유지보수가 편하다. / 대용량 데이터 관리 및 무결성을 보장한다.

> 단점 : 시스템 자원을 많이 차지해 전반적으로 시스템이 느려진다. (하드웨어 발전으로 해결)

 

* SQL (Structured Query Language)

- 관계형 데이터베이스(RDB)에서 사용되는 언어다.

- DBMS 제작 회사에 독립적이다.

- 다른 시스템으로의 이식성이 좋다.

- 표준이 지속적으로 발전 중이다.

- 대화식 언어이다.

- 분산형 클라이언트/서버 구조를 갖는다.

 

1) SQL 기본 문법

(1) SELECT, FROM, WHERE문

> 데이터를 조회하는 구문이다.

> 데이터베이스의 테이블에서 원하는 정보를 추출하는 명령어다.

SELECT col1
FROM tbl
where condition;

> SELECT 뒤에 찾고자 하는 데이터의 열(column)을 입력한다.

> 여러 열을 갖고 오고 싶다면 콤마(,)로 구분한다.

> 이때 입력한 열의 순서대로 출력이 된다.

> FROM 뒤에 데이터를 찾을 테이블을 명시한다.

> WHERE 뒤에 특정 조건에 알맞은 데이터를 찾기 위해 조건을 입력한다.

 

(2) USE

> 여러 데이터베이스 중에서 사용하려는 데이터베이스를 명시한다.

USE employees;

> 위처럼 쿼리로 작성해도 되고, 하이디 SQL에서 직접 DB를 선택해도 된다.

> 왼쪽 상단 'employees' DB를 직접 선택하면 하단에 'USE employees'가 표시된다.

> 혹은 초록색 박스에 'USE employees'를 실행해도 결과는 같다.

# (1)
SELECT a,b
FROM test_db.tbl;
# (2)
SELECT a,b
FROM tbl;

> 만약 'test_db'라는 DB를 사용 중이라면 1번, 2번 예시의 결과는 동일하다.

 

(3) 주석 작성

> 한 줄짜리 주석은 '#' 또는 '--' 으로 시작하고 여러 줄 주석은 '/* */' 사이에 입력한다.

# 한 줄 짜리 주석
-- 이거도 한 줄 짜리 주석
SELECT *;
/*
이건
여러 줄을 입력할 수 있는
주석
*/

 

2) 여러 SQL 문법

(1) 조건/관계 연산자 사용

> 특정 조건 연산자나 관계 연산자를 조합해서 데이터를 추출할 수 있다.

 

※ 관계 연산자

> A OR B : A 또는 B

> A AND B : A 그리고 B

> NOT A : A가 아님

 

※ 조건 연산자

기호	의미
A = B	A와 B가 같음
A < B	B가 A보다 큼
A > B	A가 B보다 큼
A <= B	B가 A보다 크거나 같음
A >= B	A가 B보다 크거나 같음
A <> B	A와 B가 같지 않음
A != B	A와 B가 같지 않음 (<>와 동일)

 

(2) BETWEEN A AND B

> 데이터가 숫자로 구성되어 있으며 연속적인 값일 때 사용

> A와 B 사이 숫자에 해당하는 값만 검색

SELECT name, height
FROM usertbl
WHERE height BETWEEN 180 AND 183;

 

(3) A IN (B, C, D)

> 특정 값이 범위에 포함되는 경우에 사용

> B, C, D 중 A가 해당될 경우 검색

SELECt name, addr
FROM usertbl
where addr IN ('경남', '전남', '경북');

 

(4) LIKE

> 문자열의 내용을 검색

> % : 1개 이상의 문자열 대체

> _ : 1개의 문자열 대체

# (1)
SELECT name, height
FROM usertbl
WHERE name LIKE '김%';
# (2)
SELECT name, height
FROM usertbl
WHERE name LIKE '김_호';

> 1번 예시는 김 씨 성을 가진 사람들을 추린다.

> 2번 예시는 김 씨 성을 가지고 이름이 '호'로 끝나는 3글자 이름을 가진 사람을 추린다.

 

(5) 서브 쿼리

> 쿼리문 안에 또 다른 쿼리문이 들어있는 것

# (1)
SELECT name, height
FROM usertbl
WHERE height > 177;
# (2)
SELECT name, height
FROM usertbl
WHERE height > (SELECT height FROM usertbl WHERE name = '김경호');

> DB 테이블 내에 '김경호'라는 이름을 가진 사람의 키는 177이라면 위의 두 쿼리는 동일하게 동작한다.

> 이 경우에는 서브 쿼리의 결과가 둘 이상이 되면 에러가 발생한다.

> 이를 위해서 사용하는 쿼리로 ANY, SOME, ALL이 있다.

> ANY/SOME : 서브 쿼리의 여러 결과 중 한 가지만 만족해도 된다.

> ALL : 서브쿼리의 결과 중 모든 결과에 대해 만족해야 한다.

# (1)
SELECT * FROM usertbl
WHERE height >= all(SELECT height FROM usertbl WHERE NAME LIKE '김%');
# 이름이 '김'으로 시작하는 모든 사람들의 키보다 큰 사람 찾음.
# (2)
SELECT * FROM usertbl
WHERE height >= any(SELECT height FROM usertbl WHERE NAME LIKE '김%');
# 이름이 '김'으로 시작하는 모든 사람들 중에서 한 사람보다도 키가 크면 다 찾음.
# 이 경우에는 김 씨중에 가장 키가 작은 사람보다 크기만 하면 다 출력될 것임.

 

(6) ORDER BY

> 결과물에 대한 영향은 끼치지 않고 단순 정렬을 위한 구문

> 기본적으로는 오름차순(ASCENDING)으로 정렬

> 내림차순(DSECENDING)으로 정렬하려면 열 이름 뒤에 DSEC 입력

> ORDER BY를 혼합하여 사용할 수도 있다.

SELECT name, height
FROM usertbl
ORDER BY height DESC, name ASC;

> 키 순서대로 정렬하되, 만약 키가 동일하다면 이름순으로 정렬한다.

 

(7) DISTINCT

> 중복된 것은 하나만 남기는 구문

SELECT DISTINCT height FROM usertbl;

 

(8) LIMIT

> 출력 개수를 제한하는 구문

> 'LIMIT N' 처럼 N개까지 제한한다.

SELECT * FROM usertbl LIMIT 5;

 

(9) GROUP BY, HAVING, ROLLUP

> 결과를 그룹으로 묶어준다.

> 집계 함수와 함께 사용한다.

> 열 이름을 읽기 좋게 별칭 구문(AS)을 사용한다.

-- 집계 함수
SELECT amount FROM buytbl;
SELECT SUM(amount) FROM buytbl;
SELECT AVG(amount) FROM buytbl;
SELECT MAX(amount) FROM buytbl;
SELECT MIN(amount) FROM buytbl;
SELECT STD(amount) FROM buytbl;
SELECT COUNT(amount) FROM buytbl; -- (null이 아닌 값의 개수)

-- group by
SELECT userID, SUM(price) FROM buytbl
GROUP BY userID;

-- alias(AS) 별칭추가, as는 생략 가능.
SELECT userID, SUM(price) AS 합, AVG(price) AS 평균
FROM buytbl
GROUP BY userID

> 집계 함수는 대충 이름 보면 알 텐데, STD는 표준편차를 구하는 것이다.

> HAVING은 WHERE과 비슷하지만 집계 함수에 대한 조건을 제한한다.

> 반드시 HAVING 절은 GROUP BY 절 다음에 나와야 한다.

> 총합 또는 중간 합계가 필요할 경우 WITH ROLLUP을 사용한다.

 

* 실습문제

> 문제를 일일이 요약해서 쓰기엔 귀찮은 수준에 이르렀기 때문에 복붙 한다.

> 어차피 나밖에 안 볼 건데 상관없겠다.

1) basic_select_1

# 풀이
USE employees;
-- 1)
SELECT last_name, gender, hire_date
FROM employees
WHERE emp_no = 10002;

-- 2)
SELECT emp_no
FROM salaries
WHERE salary > 60000;

-- 3)
SELECT emp_no
FROM dept_emp
WHERE dept_no != 'd009';

-- 4)
SELECT dept_name
FROM departments
WHERE dept_no IN ('d009', 'd008');

-- 5)
SELECT emp_no, birth_Date, first_name, last_name, gender
FROM employees
WHERE emp_no not BETWEEN 10000 AND 10800;

-- 6)
SELECT *
FROM salaries
WHERE salary BETWEEN 70000 AND 80000
AND( (from_date<='2000-01-01' AND to_date>'2000-01-01')
	OR (from_date BETWEEN '2000-01-01' AND '2000-12-31' AND to_date <= '2000-12-31')
	OR (from_date >= '2000-01-01' AND to_date <= '2000-12-31'))
ORDER BY from_date ASC;
# 솔직히 첨 배웠는데 이거 문제 내신건 좀 에바였음 ㅋㅋ;

-- 7)
SELECT *
FROM salaries
WHERE salary NOT BETWEEN 70000 AND 80000;

 

2) basic_select_2

# 풀이
USE sqldb;

-- 1)
SELECT userID, NAME, ADDr, birthYear
FROM usertbl
WHERE NAME LIKE '조%'
ORDER BY birthYear ASC;

-- 2)
SELECT DISTINCT prodName, price
FROM buytbl
ORDER BY price DESC;

-- 3)
SELECT prodName
FROM buytbl
WHERE price BETWEEN 50 AND 200;

-- 4)
SELECT prodName, price
FROM buytbl
WHERE userID IN ('BBK', 'JYP', 'KBS')
ORDER BY price DESC LIMIT 1;

-- 5)
SELECT prodName, SUM(amount)
FROM buytbl
GROUP BY prodname;

-- 6)
SELECT groupname, AVG(amount)
FROM buytbl
GROUP BY groupname;

 

※ 이 게시물은 '이것이 MySQL이다' 교재와 엔코아 플레이데이터에서 배운 내용을 토대로 작성되었습니다.