[성동3기 전Z전능 데이터 분석가] DAY 37 _ SQL _ DB/DBMS, SQL 설치, CRUD, SELECT, WHERE, 조건문
오전
데이터 분석가란?
데이터를 분석 & 정리하여, 비즈니스적인 결정을 할 때 도움을 주는 데이터 분석 보고서를 만드는 역할
= 프로젝트를 통해서 만든 포트폴리오에서 보여줘야 하는 역량 !
데이터베이스
조직체의 응용 시스템들이 공유해서 사용하는 운영 데이터들이 구조적으로 통합된 모임이다. 데이터 베이스의 구조는 사용되는 데이터 모델에 의해 결정된다.
- 데이터베이스 스키마
- 전체적인 데이터베이스 구조를 뜻하며, 자주 변경되지 않음
- 데이터베이스 열의 모든 가능한 속성을 미리 정의
- 내포(intension) 이라고 부름
DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)
EMPLOYEE(EMPNO, EMPNAME, TITLE, DNO, SALARY)
- 데이터베이스 상태
- 특정 시점의 데이터베이스의 내용을 의미하며, 시간이 지남에 따라 계속해서 바뀜
- 데이터베이스 행의 모든 내용을 포함
- 외연(extension) 이라고 부름
| EMPNO | EMPNAME | TITLE | DNO | SALARY |
| 2106 | 김창섭 | 대리 | 2 | 2000000 |
| 3426 | 박영권 | 과장 | 3 | 2500000 |
데이터베이스에서 사용되는 용어
- EMPLOYEE = 릴레이션 = 2차원 테이블(스프레드 시트, Dataframe 과 유사)
- EMPNO = 기본키
- 열 = column = 애트리뷰트
- 행 = row = 튜플 = 레코드
- 특정 값 = 애트리뷰트 값
DBMS (데이터베이스 관리 시스템)
데이터베이스를 정의하고, 질의어를 지원하고, 리포트를 생성하는 등의 작업을 수행하는 소프트웨어
ex. MySQL, ORACLE, MongoDB, SQLite
DBMS 언어
- DDL (Data Definition Language)
- 스키마를 정의하는 용어
- 데이터 정의어로 명시된 문장이 입력되면 DBMS 는 사용자가 정의한 스키마에 대한 명세를 시스템 카탈로그 또는 데이터 사전에 저장
- 데이터 정의어의 기본적인 기능
- 테이블 구조 생성, 구조 변경, 구조 삭제 등
- ex. CREATE TABLE, ALTER TABLE, DROP TABLE
- DML (Data Manipulation Language) ⭐️
- 사용자는 데이터 조작어를 사용해 데이터베이스 내 원하는 데이터를 검색, 수정, 삽입, 삭제(CRUD)
- 절차적 언어와 비절차적 언어로 나뉘어짐, 관계형 DBMS 에서 사용되는 SQL 은 대표적인 비절차적 언어
- ex. 데이터 검색, 수정, 삭제, 삽입 (SELECT, UPDATE, DELETE, INSERT)
DA 에게 SQL 이 중요한 이유?
데이터 분석을 위해, SQL 을 활용해 데이터베이스에서 필요한 데이터를 추출해내는 능력이 중요하기 때문이다.
SQL 이란?
- Structured Query Language : RDBMS(관계형데이터베이스 관리시스템) 를 위한 표준 언어
- SQL 문을 사용하여 데이터베이에서 정보를 저장, 업데이트, 제거, 검색 가능
- 데이터베이스 성능을 유지 관리, 최적화
SQL 을 잘 한다는 의미..?
- 효율적인(빠른) 쿼리 작성
- 데이터 모델링 이해
- 고급 SQL 기능 활용
- 성능 최적화
- 데이터 정제 및 변환
- 다양한 데이터베이스 시스템 이해
- 데이터 집계 및 요약
MySQL Community 설치
아래 링크를 통해 무료버전 설치가 가능하다. 보통 8.4.4 LTS 버전을 사용하게 된다고 한다.
https://dev.mysql.com/downloads/mysql/
MySQL :: Download MySQL Community Server
Select Version: 9.2.0 Innovation 8.4.4 LTS 8.0.41 Select Operating System: Select Operating System… Microsoft Windows Ubuntu Linux Debian Linux SUSE Linux Enterprise Server Red Hat Enterprise Linux / Oracle Linux Fedora Linux - Generic Oracle Solaris mac
dev.mysql.com
DBeaver 설치
SQL 쿼리를 날리는 도구로 사용되는 DBeaver 는 아래 링크를 통해 설치가 가능하다.
Download | DBeaver Community
Download DBeaver Community 24.3.5 Released on February 16th 2025 (Milestones). It is free and open source (license). Also you can get it from the GitHub mirror. System requirements. DBeaver PRO 24.3 Released on December 16th, 2024 PRO version website: dbea
dbeaver.io
DBeaver 과 Mysql 연결
- 좌측 콘센트 모양을 클릭하여, MySQL 클릭 후 다음
- SQL 설치 시 세팅했던 패스워드를 기입
- Driver properties 에서 allowPublicKeyRetrieval 을 True 로 바꿔준 후 완료
- localhost 에 초록색 체크 표기로 연결이 잘 되었는지 확인 !!
새로운 데이터베이스 생성
- DataBase 에서 우클릭 후 Create New Database 클릭
- 이름을 입력 후, Charset & Collation 을 Utf8mb4 & Utf8mb4_general_ci 로 선택 후 확인
데이터 가져오기
- 새롭게 생성된 DB 하위 메뉴에 Tables 을 우클릭하여 데이터 가져오기 클릭
- csv 에서 가져오기 선택 후 다음
- 가져올 데이터 셋을 선택한 후, 인코딩을 EUC-KR 로 변경, 공백을 NULL 로 치환 체크, Trim whitespaces 도 체크 후 다음
- 다음 버튼을 눌러서 데이터 가져오기(import)
- 진행을 클릭하여 데이터 가져오기 (import)
- 데이터 확인 → 테이블 클릭 후 데이터 탭을 눌러 데이터가 잘 가져와졌는 지 확인
CRUD 맛보기
CRUD 란?
- Create, Read, Update, Delete 의 약자
- 데이터나 테이블을 생성하고, 읽고, 수정하고, 삭제
- 데이터를 다루는 기본기
Create Table
Employees 라는 릴레이션(table) 을 만들고
그 안의 애트리뷰트들(column) 을 지정해주세요.
실무에서 위와 같은 요청이 들어왔을 때, CREATE 을 사용하여 릴레이션을 만들 수 있다.
이 때, DBeaver 상단의 SQL - 새 SQL 편집기를 클릭하여 빈 창을 띄운다.
쿼리를 작성 후 맥 기준, command + enter 를 이력하면 두번째 사진과 같은 결과가 나온다. tables 을 우클릭 새로고침 하여, 새로 만들어진 employees 테이블을 확인해보면 세번째 사진처럼 명령한 칼럼이 잘 입력이 된 걸 확인할 수 있다.
아래와 같이 테이블에 넣을 내용들을 추가로 코딩해주면 우측과 같이 값이 잘 들어가 있는 것을 확인할 수 있다.
Select
# select 맛보기
SELECT * FROM employees;
Update
- 데이터를 수정하는 명령어
id 가 1인 employees 테이블의 row 의 salary 를 55000.00 으로 수정해주세요.
# update 맛보기
UPDATE employees
SET salary = 55000.00
WHERE id = 1;
- Where 문을 쓰지 않으면 모든 row 가 바뀌니 주의 !!
UPDATE employees
SET salary = 800.00;
Delete
# delete 맛보기
DELETE FROM employees
WHERE id = 2;
오후
관계형 데이터베이스 (Relational Database)
관계형 데이터 모델은 지금까지 제안된 데이터 모델들 중에서 가장 개념이 단순한 데이터 모델의 하나이다. 1980년대 후반부터 여러가지 데이터 모델들이 새로 등장했지만 관계 DBMS 는 여전히 가장 널리 사용된다.
- MySQL
- MariaDB
- ORACLE Database
- DB2
- PostgresSQL
- MicrosoftSQL
- SQLite
차수(degree) & 카디날리티(cardinality)
- 차수 : 한 릴레이션에 들어 있는 애트리뷰트들의 수 (유효한 릴레이션 최소 차수 = 1)
- 카디날리티 : 릴레이션의 튜플의 수 (유효한 릴레이션은 카디날리티 = 0 가질 수 있음)
- 릴레이션의 차수는 자주 바뀌지 않음
- 릴레이션의 카디날리티는 시간이 지남에 따라 계속해서 변함
Null 값
- '알려지지 않음' 또는 '적용할 수 없음'을 나타내기 위한 값
- 널값은 숫자 도메인의 0이나 문자열 도메인의 공백 문자 또는 공백 문자열과 다름
- DBMS 들 마다 널값을 나타내기 위해 서로 다른 기호를 사용
릴레이션 스키마 (relation Schema)
- 릴레이션의 이름과 릴레이션의 애트리뷰트(열)들의 집합
- 릴레이션을 위한 틀 (framework)
- 표기법
- 릴레이션이름(애트리뷰트1, 애트리뷰트2, ... 애트리뷰트N)
- 기본 키 애트리뷰트에는 밑줄 표시
- 내포(intension)라고 함
릴레이션 인스턴스 (relation instance)
- 릴레이션에 어느 시점에 들어 있는 튜플(행)들의 집합
- 시간의 흐름에 따라 계속 변함
- 일반적으로 릴레이션에는 현재의 인스턴스만 저장됨
- 외연(extension)이라고 함
관계 데이터베이스의 스키마
하나 이상의 릴레이션 스키마들로 이루어진다.
DEPARTMENT(DEPTNO, DEPTNEMA, FLOOR)
EMPLOYEE(EMPNO, EMPNAME, TITLE, DNO, SALARY)
관계 데이터베이스의 인스턴스
릴레이션 인스턴스들의 모임으로 구성된다.
릴레이션(테이블)의 특성
- 릴레이션 : 튜플들의 집합
- 각 릴레이션은 오직 하나의 레코드 타입만 포함
- 한 애트리뷰트 내의 값들은 모두 같은 유형
- 애트리뷰트들의 순서는 중요하지 않음
- 한 튜플의 각 애트리뷰트는 원자값을 가짐 (분해할 수 없음)
- 각 애트리뷰트의 이름은 한 릴레이션 내에서만 고유
- 튜플들의 순서는 중요하지 않음
릴레이션의 KEY
- 각 튜플을 고유하게 식별할 수 있는 하나 이상의 애트리뷰트들의 모임
- 수퍼키(superkey), 후보키(candidate key), 기본키(primary key), 대채키(alternative key), 외래키(foreign key)
기본키와 외래키
- 기본키: 테이블의 각 레코드를 고유하게 식별
- 외래키: 다른 테이블의 기본키를 참조하는 칼럼이며, 테이블 간의 관계를 정의
기본키(primary key)
- 한 릴레이션에 후보키가 두 개 이상 있으면 설계자 또는 데이터베이스 관리자가 이들 중에서 하나를 기본키로 선정
- ex. 신용카드 회사의 고객 릴레이션에서 신용카드번호와 주민등록번호가 후보키가 될 수 있고, 이 중에서 신용카드번호를 기본키로 선정
- 자연스러운 기본키를 찾을 수 없는 경우에는 레코드 번호와 같이 종종 인위적인 키 애트리뷰트를 릴레이션에 추가할 수 있음
기본키 설정시 고려사항
- 애트리뷰트가 항상 고유한 값을 가질 것인가
- 애트리뷰트가 확실하게 널값을 갖지 않을 것인가
- 애트리뷰트의 값이 변경될 가능성이 높은 애트리뷰트는 기본키로 선정하지 말 것
- 가능하면 작은 정수값이나 짧은 문자열을 갖는 애트리뷰트
- 가능하면 복합 기본키를 피할 것
외래키(foreign key) ⭐️
- 어떤 릴레이션의 기본키를 참조하는 애트리뷰트
- 관계 데이터베이스에서 릴레이션들 간의 관계를 나타내기 위해서 사용됨
- 외래키 애트리뷰트는 참조되는 릴레이션의 기본키와 동일한 애트리뷰트를 가져야함
- 자신이 속한 릴레이션의 기본키의 구성요소가 되거나 되지 않을 수 있음
기본키와 외래키
아래 데이터베이스에서는 'DNO' & 'DEPTNO' 를 서로 참조할 수 있다. 데이터베이스가 굳이 두개의 표로 구분되어 있는 이유 중 하나로, 실무 상에서는 대규모의 데이터를 처리하게 되기 때문에, '처리속도' 의 측면에서의 효율성 증진을 위한 부분 때문이다.
| EMPNO → 기본키 | EMPNAME | DNO → 외래키 |
| 2106 | 김창섭 | 2 |
| 2389 | 박영권 | 3 |
| DEPTNO → 기본키 | DEPTNAME | FLOOR |
| 1 | 영업 | 8 |
| 2 | 기획 | 10 |
데이터베이스의 무결성 제약조건
- 데이터의 정확성 또는 유효성을 의미
- 일관된 데이터베이스 상태를 정의하는 규칙들을 묵시적으로 또는 명시적으로 정의함
- 무결성 제약조건은 데이터베이스 상태가 만족시켜야하는 조건
- 사용자에 의한 데이터베이스 갱신이 데이터베이스의 일관성을 깨지 않도록 보장하는 수단
- 데이터베이스가 갱신될 때 DBMS 가 자동적으로 일관성 조건을 검사하므로 응용 프로그램들은 일관성 조건을 검사할 필요는 없음
도메인 제약조건(domain constraint)
- 각 애트리뷰트값이 반드시 원자값이어야함
- 애트리뷰트 값의 디폴트 값, 가능한 값들의 범위 등을 지정할 수 있음
- 데이터 형식을 통해 값들의 유형을 제한하고, CHECK 제약 조건을 통해 값들의 범위를 제한할 수 있음
키 제약조건(key constraint)
- 키 애트리뷰트에 중복된 값이 존재해서는 안됨
기본키와 엔티티 무결성 제약조건(entity integrity constraint)
- 릴레이션의 기본키를 구성하는 어떤 애트리뷰트도 널값을 가질 수 없음
- 대체키에는 적용되지 않음
- 사용자가 릴레이션을 생성하는 데이터 정의문에서 어떤 애트리뷰트가 릴레이션의 기본키의 구성요소인가를 DBMS 에게 알려줌
외래키와 참조 무결성 제약조건(referential integrity constraint)
- 참조 무결성 제약조건은 두 릴레이션의 연관된 튜플들 사이의 일관성을 유지하는데 사용됨
- 관계 데이터베이스 포인터 없이 오직 릴레이션들로만 이루어지고, 릴레이션 사이의 관계들이 다른 릴레이션의 기본키를 참조하는 것을 기반으로 하여 묵시적으로 표현되기 때문에 외래키의 개념이 중요
- 릴레이션 R2 의 외래키가 릴레이션 R1 의 기본키를 참조할 때 참조 무결성 제약조건은 아래의 두 조건 중 하나가 성립되면 만족됨
- 외래키의 값은 R1 의 어떤 튜플의 기본키 값과 같음
- 외래키가 자신을 포함하고 있는 릴레이션의 기본키를 구성하고 있지 않으면 널값을 가짐
테이블 검색 _ SELECT * FROM table 명
- *(애스터리스크, 아스타 기호) = "모든" 이라는 메타문자
- FROM 을 통해서 어떤 테이블에서 데이터를 가져올 것인지 선택할 수 있음
Select
- SELECT... FROM... 은 필수 입력사항
- SQL 구문은 대소문자를 구분하지 않음 but 문자 상수는 대소문자를 구분
- SQL 키워드 (SELECT, FROM, WHERE) 는 주로 대문자를 사용하고, 테이블명, 칼럼 이름은 소문자로 작성하는 것을 권장
SELECT 기본 형식
SELECT * | {[ALL | DISTINCT] column | expr [alias],...}
FROM table
WHERE conditions
GROUP BY group_by_expression
HAVING group_condition
ORDER BY column;
- SELECT: 추출하고자 하는 칼럼 나열
- DISTINCT: 중복 제거, 같은 값이면 하나만 출력
- FROM: 질의에서 필요로 하는 테이블 또는 테이블 역할을 하는 것 (뷰, 가상테이블 등)
- WHERE: 데이터 필터링, 행을 제한하거나 조인조건을 기술함
- ⭐️WHERE 절을 사용하지 않으면 FROM 절에 명시된 테이블의 모든 row 를 조회
- GROUP BY: 그룹핑 칼럼을 기준으로 한 그룹으로 묶어서 합계, 평균, 최대, 최소, COUNT 등에 사용
- HAVING: 그룹핑 기준 칼럼이나 그룹함수에 조건을 줄 때 사용
- ORDER BY: SELECT 에서 추출하는 데이터의 정렬 (기본 asc, 역순 desc)
WHERE
필요한 데이터만 검색하기 위해 사용하는 절이다. 아래 순서대로 WHERE 을 배치해야 한다.
SELECT * FROM table WHERE no = 1
NULL 값 출력
= 이 아닌 is 를 사용해야, 널값이 포함되어 있는 행을 출력할 수 있다.
❗️ WHERE column is (not) NULL;
비교 연산자
- = : 좌변과 우변의 값이 같을 경우 참
- <> : 좌변과 우변의 값이 다를 경우 참
- > : 좌변이 우변보다 클 경우 참
- >= : 좌변이 우변보다 크거나 같을 경우 참
- < : 좌변이 우변보다 작을 경우 참
- <= : 좌변이 우변보다 작거나 같을 경우 참
AND & OR
- AND : 교집합
- OR : 합집합
SELECT *
FROM sample24;
SELECT *
FROM sample24
WHERE a != 0 AND b != 0;
SELECT *
FROM sample24
WHERE a != 0 OR b != 0;
조건식 주의점
❗️AND와 OR 을 조합해서 사용할 때, 괄호로 꼭 묶어주기
SELECT * FROM sample24 WHERE (a <> 0) OR (a = 2);
Reflection
고대하던 SQL 수업시간이 다가왔다. 채용공고를 보다보면, 하나같이 요구하던 'SQL' 역량 ! 사실, 교육 이전부터 유튜브를 통해 기초 쿼리 구문을 학습한 후, leet code 로 매일 한 문제씩 풀어오긴 했다. 확실히 직접 쿼리를 작성해봐야 체화가 되어가는 느낌이다. SQL 에서 특이했던 점은, 파이썬 처럼 설치 후 끝이 아니라 쿼리 편집기를 별도 설치 후 서로 연결해줘야 하는 점이었다. 즉, 설치부터 난관이었다는 의미이다. 특히, 비밀번호를 작성해야하며 꼭 기억해야하기 때문에, 노트를 필수적으로 해놔야한다는 것을 뼈저리게 알았다. 비밀번호를 잊어버려 SQL 자체를 다시 삭제후 재 설치하는 고생을 겼었기 때문이다. 하지만, 누군가 나와 같은 고난을 겪게 된다면 해결책을 이야기 해줄 수 있을 것 같다. 프로그래밍 언어는 작은 실수를 거듭하며, 조금씩 성장해가는 맛이 확실히 있다. 따라서, 이번에도 즐거운 마음가짐으로 프로젝트까지 달려보기로 하자. 🏃♂️➡️