DataBase - 개념 + 기본활용

DataBase란?

- 연관된 데이터의 잡합을 나타내는 것이 Database이다.

- 데이터베이스를 구성하는 요소는 개체(Entity), 속성(Attribute), 관계(Relationship)이 있다

1. 개체 : 데이터베이스에서 표현하고자 하는 유형, Ex) Schema = 학생정보

2. 속성 : 데이터베이스에서 개체의 특성이나 상태, Ex) Schema의 Row = 학생의 이름

3. 관계 : 개체와 개체 혹은 속성과 속성의 관계 Ex) INNER JOIN

- 보통 우리가 사용하는 MySql은 관계형 데이터베이스(RDBMS)이다. 

RDBMS(Relation DataBase Management System)

- RDBMS의 내부구조를 파헤치면 다음과 같은 구조로 되어 있다.

RDBMS 구조

- Table = Schema : 1개 이상의 Column과 0개 이상의 Row로 구성되어 있다

- Row : 레코드라고 불리며, 기본키(Primary Key)에 의해 구분되고 PK는 Null이 될 수 없다

- Column : 각 레코드마다 구성되어 있는 정보들

- Field : Column이 나타낼 수 있는 필드값

- Primary Key : 각각의 Row를 유일하게 식별해주는 고유값

- Foreign Key : 같은 Table의 또 다른 Table의 Primary Key를 참조하는 단일 Column, 

   * Relation DB를 설계할 수 있는 핵심 요소!

명령어 정리

1. DDL(Data Definition Language) : 테이블이나 관계의 구조를 생성하는 명령어

    Ex) CREATE, DROP, TRUNCATE, ALTER

2. DML(Data Manipulation Language) : 테이블에 데이터를 수정, 삽입, 삭제, 검색하는데 사용하는 명령어

    Ex) INSERT, SELECT, UPDATE, DELETE

3. DCL(Data Control Language) : 데이터의 사용 권한을 관리하는데 사용하는 명령어

    Ex) GRANT, REVOKE

-- SELECT 컬럼명, 컬럼명
-- [FROM 테이블명]
-- [WHERE 조건]
-- [GROUP BY 컬럼명]
-- [HAVING 조건]
-- [ORDER BY 컬럼명 표현]
-- [LIMIT 행카운트]
-- [AS 별칭]
-- [IFNULL(값, 디폴트값)]
-- [CONCAT(sal, '원')] 집어넣기
-- ;
 
show databases; -- 데이터 베이스 목록 보여달라
show tables; -- 테이블 목록 조회
create database [name]; -- 데이터 베이스 만들기
drop database [name] -- 데이터 베이스 삭제
use [name] -- 데이터 베이스을 선택하여 이용
create table [name]([colum] [type(size)), type(sie) ]) -- 테이블 생성
create table mytable(name VARCHAR(10), price INT(10), local VARCHAR(10)); -- 테이블 생성 예시
desc [name] -- 테이블 구조 보기
insert into [name] ([colum], [colum], ...) values([val], [val], ...); -- 테이블에 데이터 삽입
 
select [colum], [colum], [colum] -- 열을 선택하고
from [name] -- 테이블에 해당하는 열에 대한 데이터 조회하고
where [condition]; -- 조건에 해당하는 데이터 출력
 
select empno as 사번, ename as 이름, concat(sal, '원') from emp; -- empno을 '사번'이라 별칭, sal끝에는 '원'을 붙이자
select ENAME from emp where ENAME like '_o%'; -- 두번째 글자가 o인 사람만 출력 _: 1글자, %: 0~n글자
select distinct deptno from emp; -- 중복을 제거하여 출력
select * from emp where COMM is null; -- 커미션이 null 인 사람 조회
select * from emp where deptno = 10 or deptno = 30; -- 파이썬 과 조건연산자는 동일
select * from emp where (job, deptno) in (('manager', 10) ,('salesman', 20)); -- 직책이 매니저 부서번호 10
 

- SELECT : 검색하고자 하는 데이터 Column

- AS : 별칭을 매기는 옵션 ( alias )

- FROM : Table을 선택하는 옵션

- ORDER BY : 정렬 순서를 지정하기 위한 구문 ( ASC : 증가, DESC : 감소 )

- WHERE : 조건을 넣기 위한 구문 ( OR, AND 등의 다양한 비교 연산자 가능! )

- IN : 해당 값에 있는 column이라면 포함한다!

- LIKE : 문자열의 검색을 위한 조건! ( 'A%' = A로 시작하는 모든 문자! )

- IS NULL : 해당 column의 값이 NULL을 찾는 조건!

 

함수 명령어

select upper(ename) from emp; -- 대문자로 소문자는 LOWER
select substr(ename,1,3) from emp; -- SUBSTRING 뽑아오기 Mysql은 1번지부터 시작
select concat(sal, '원') from emp; -- 맨 뒤에 문자를 붙여서 가져오기
select length() from emp; -- 문자의 길이
select round(sal) from emp; -- 반올림
select curdate(); -- 현재 날짜
select year(curdate()); -- 현재 년도
select sysdate(); -- 현재 날짜 + 시간
select to_days('2019-01-01'); -- 날짜로 바꾸기
select count(deptno) from emp where deptno = 20; -- 20번 부서의 총 인원 가져오기

- 문자형 함수 : UPPER, LOWER, INITCAP, SUBSTR, LENGTH

- 숫자형 함수 : ROUND, TRUNC, POWER

- 날짜형 함수 : SYSDATE, MONTHS_BETWEEN

- 변환형 함수 : TO_CHAR, TO_DATE, TO_NUMBER

- GROUP 함수 : COUNT, MAX, MIN, AVG, SUM

JOIN

- 하나 이상의 Table로부터 연관된 데이터를 검색해오는 방법

- 일반적으로 PK와 FK의 값을 가지고, JOIN을 하는 것이 보편적이다.

* 꼭 PK와 FK가 아니어도 JOIN은 가능하다.

- JOIN의 유형

1. Inner Join = Equi Join : 연산자를 사용하는 JOIN 방식이며, 조건에 만족하는 값만 출력한다.

select * from emp, dept where emp.DEPTNO = dept.DEPTNO;

2. Outer Join : JOIN조건을 만족하지 않아도 모든 행을 다 보는 방식

 - (1) LEFT Outer Join : 왼쪽에 있는 테이블을 오른쪽에 매칭하여 값이 있으면 join 없으면 NULL을 부여한다.

 - (2) RIGHT Outer Join : 오른쪽에 있는 테이블을 왼쪽에 매칭하여 "" .

 - (3) FULL Outer Join : 양쪽에 조건이 일치하지 않아도 Join하여 나타낸다!

3. CROSS Join : 왼쪽 테이블을 오른쪽에 교차 시켜서 Join하는 방식, 모든 경우의 수 매핑과 유사

4. SELF Join : 자기 자신의 테이블을 Join하는 방식

-- INNER JOIN
select * from emp inner join dept where emp.deptno = dept.deptno;
-- RIGHT OUTER JOIN
select * from emp right outer join dept on emp.DEPTNO = dept.DEPTNO;
-- LEFT OUTER JOIN
select * from emp left outer join dept on emp.DEPTNO = dept.DEPTNO;
-- FULL OUTER JOIN
select * from emp left outer join dept on emp.DEPTNO = dept.DEPTNO
union
select * from emp right outer join dept on emp.DEPTNO = dept.DEPTNO;
-- CROSS JOIN
select * from emp cross join dept;
-- SELF JOIN
select * from emp e1 inner join emp e2 where e1.deptno = e2.deptno;

SubQuery

- 간단히 말해서, 하나의 Query문 안에 또 다른 Query문이 들어가 있는 Query를 SubQuery라 한다!

- DataBase에 조건을 넣어서 검색을 할 때, 다양한 조건을 주기 위해서 사용한다!

1. 단일행 SubQuery : SubQuery에서 넘어가는 Row 데이터가 1개일 때!

2. 다중행 SubQuery : SubQuery에서 넘어가는 Row 데이터가 2개 이상일 때!

  * 다중행의 경우 조건을 추가해야 한다!

  - ANY : Row 데이터의 '어떤' 값을 참조한다 // 경우의 수 문제 푸는것과 유사하다

  - ALL : Row 데이터의 '모든' 값을 참조한다

  - IN : Row데이터에 '속해' 있는 값을 참조한다!

-- SubQuery
select * from emp where deptno = ( select deptno from dept where loc like 'NEW YORK');
-- ANY 는 Subquery에 있는 '어떤' 하나의 값만 참조한다
select * from emp where sal >= any ( select sal from emp where deptno = 10);
-- ALL 은 SubQuery에 있는 '모든' 값을 참조한다.
select * from emp where sal >= all ( select sal from emp where deptno = 10);
-- IN 은 SubQuery에 있는 값이면 해당 값을 참조한다.
select * from emp where deptno in ( select deptno from dept where deptno <= 20 );