[멋쟁이사자처럼 부트캠프 TIL 회고] BE 13기_12일차_'MySQL+쿼리'

🦁멋쟁이사자처럼 백엔드 부트캠프 13기 🦁
TIL 회고 - [12]일차

🚀12일차 데이터베이스의 설치를 모두 완료하였고, 환경까지 구축하였다.

MySQL의 Workbench 편집기를 이용하여 MySQL의 실습을 효과적으로 할 수 있었다.

MySQL에서의 문법과 쿼리문 사용, 특히 SELECT절을 이용해 데이터를 조회하는 연습을 하였다.

---

MySQL Workbench

Homepage - MySQL Workbench

• 여기서 MySQL Connections 옆 + 버튼을 눌러서 Setup New Connection을 열어줌

 

• Connection Name :
  root로 지정하여 처음의 권한을 배정하기 위해선 root로 접속
  
  
• Store in Vault.. :
  root계정으로 접속할 시 docker-compose.yml파일에서 작성했던 root 패스워드입력
  
  
• Test Connection으로 연결

• 버전 충돌 가능성에 대한 오류이므로 Continue Anyway를 클릭

접속 성공 후 화면

• root계정 접속 후 데이터베이스 접속 성공

---

쿼리 Query

• MySQL이 제공하고 있는 함수
• curdate (= current_date) : 현재 날짜 출력
• current_user(); : 현재 유저 출력 - root계정이 잘 출력됨을 확인 가능

• ▶️Ctrl + Enter
  : 커서에 해당하는 줄 실행 (= Workbench의 두번째⚡버튼 클릭)
• ▶️Ctrl + Shift + Enter
  : 쿼리 전체의 명령 실행 (= Workbench의 첫번째⚡ 버튼을 클릭)
  
  
• ▶️Ctrl + Scroll Up
  : Workbench의 폰트 크기가 커진다.

• ▶️sin함수 출력 - pi() 함수를 이용
• 이렇게 간단한 연산도 수행할 수 있다.
• 여러 문장은 수행하고 싶은 것들은 줄바꿈과 , (쉼표) 를 통해 작성 가능
•  ; (세미콜론) 까지를 하나의 문장으로 판단하므로 여러개의 수행문을 실행 가능

 

• Workbench가 아닌 프롬프트에서도 명령어가 잘 작동함을 확인할 수 있다.

• ▶️Shift + Enter
  :줄개행, 여러줄을 처리
• Workbench와 다르게 자동완성기능이 제공되지 않기에 오타 체크를 하지 못한다.

• ▶️Ctrl + / (슬래시)
  : 단축키로 마찬가지로 주석을 쓸 수 있고 방법은 [ - (하이픈) 두개 ] 로 주석을 만들 수 있다.

---

사용자 생성

CREATE USER 'id'@'%' IDENTIFIED BY 'password' -- 외부 접속용
CREATE USER 'id'@'localhost' IDENTIFIED BY 'password'; -- 로컬 접속용

CREATE USER 'like'@'%' IDENTIFIED BY 'lion'; -- 외부 접속용
CREATE USER 'like'@'localhost' IDENTIFIED BY 'lion'; -- 로컬 접속용

• CREATE USER
  : 유저를 생성하는 명령 = 정해진 문장이므로 바꿀 수 없다.
  
  
• ’id’ @ ‘%’ IDENTIFIED BY ‘password’;
  
  @ : 누가 접속하게 할 것인지 설정, 이 뒤에는 "호스트 이름”을 의미함 > %, localhost 등이 올 수 있음
  
  % : 외부 접속용 계정을 말함(=전체허용), 모든 호스트 권한
  > %대신에 아이피주소 사용가능 (= 허용된 아이피에서만 접속이 가능)
  
  localhost : 로컬 접속용 계정을 말함 을 의미함, 특정 호스트만 권한 부여 혹은 제외 가능

 

GRANT ALL PRIVILEGES ON hr.* TO 'like'@'%';
GRANT ALL PRIVILEGES ON hr.* TO 'like'@'localhost';

GRANT ALL PRIVILEGES ON exmapleDB.* TO 'like'@'%';
GRANT ALL PRIVILEGES ON exmapleDB.* TO 'like'@'localhost';

• GRANT : 권한을 부여할 수 있는 명령어
• ALL : 모든 권한을 의미
• PRIVILEGES : 프리빌리지스, 권한을 의미하는 예약어
  
  
• ON : 뒤에 DB이름
  ex. exampleDB.* (exampleDB의 모든 권한)
  exampleDB.employees 라고 설정하면 employees 테이블에 접근할 수 있는 권한만 부여한다
  
  
• TO : '유저'에게 권한을 준다는 의미
• 💡유저들마다 디테일하게 권한을 나눠주게되면 관리가 쉬워짐

CREATE DATABASE hr; -- 데이터베이스 새로 생성 (hr이름)

CREATE SCHEMA scott; -- 데이터베이스 새로 생성 두번째 방법 (동일한 결과를 가짐)

• 데이터베이스를 생성하는 명령어

---

▶️실습 - 사용자 생성 쿼리

• flush privileges;
  : 권한을 설정한 것들을 적용, 새롭게 적용된 권한을 메모리 캐시를 통해서 refresh
  “권한 테이블 새로고침“을 의미.
  권한을 변경한 후에는 항상 FLUSH PRIVILEGES; 실행하여 권한 변경 사항을 적용 

---

삭제 drop, revoke

scott 데이터베이스 삭제

• drop database scott; : scott 데이터베이스를 지운다.

drop user 'like'@'%';
drop user 'like'@'localhost';

• 유저또한 제거가능. 외부접속용과 로컬접속용을 모두 삭제해야한다.

REVOKE ALL PRIVILEGES ON hr.* FROM 'like'@'%';

REVOKE INSERT, UPDATE ON hr.* FROM 'like'@'%';

• REVOKE ALL : 사용자 like의 hr데이터베이스에 대한 모든 권한 제거
• REVOKE INSERT, UPDATE : INSERT와 UPDATE에 대한 특정 권한만 제거

---

테이블, 필드, 열, 행 - Table, Field, Column, Row

 

테이블 Table

• 빠른 참조를 위해 적당한 형태로 자료를 모아놓음
• 관계데이터베이스 모델에서 자료의 구조를 2차원의 표로 나타냄
  (행과 열로 관리, 키를 지정하여 원하는 자료를 빠르게 찾기가 가능)

필드 Field

• 컬럼(=열)과 동일 의미로 사용되기도하지만
  필드와 필드값은 "한 열이나 한 컬럼 사이의 교차로 존재하는 단일 항목을 특정"할때 언급

열 Column

• 자료형의 데이터값과 테이블에서의 각 열을 의미 (...세로값)
• 관계형 데이터베이스 용어에서 컬럼과 같은 의미 (=속성(attribute))

행 Row

• 테이블에서 단일 구조 데이터 “항목”을 의미 (...가로값)
• 관계형 데이터베이스에서 레코드, 튜플(tuple)이라고 불리기도함
• 테이블에서 모든 로우는 동일한 구조를 가짐

💡데이터베이스 테이블은 로우와 컬럼/필드로 구성되어있음

---

ER다이어그램 - ERDiagram 

샘플 DB hr의 ERDiagram - 출처 : http://taewan.kim/tip/oracle_sample_schema/

• ERD = ER Diagram
• Entity-Relation Diagram = 개체 관계도
• ER 모델 (개체-관계모델)
  : 현실 세계에 존재하는 데이터와 그들 간의 관계를 사람이 이해할 수 있는 형태로 표현하는 모델

❓테이블 하나에 내가 원하는 정보가 “모두 들어있을 가능성”

➡️ 그러한 가능성은 많지 않다.
따라서 ”조인”이라는 문법으로 여러개의 테이블을 연관해서 원하는 정보를 꺼내오는 작업을 수행해야한다.

• ER다이어그램을 살펴보면,
  사원 EMPLOYEES에서 department_id를 통해서 DEPARTMENTS에 접근할 수 있을 것
  DEPARTMENTS에서는 location_id를 통해서 LOCATIONS를 알아낼 수 있을 것이다.
  
  
• 이렇게 정보를 통해서 “조인”을 통해 여러 개의 정보를 얻어낼 수 있다.
  
  
• 마치 EMPLOYEES와 REGIONS는 연관된 정보가 없으므로,
  DEPARTMENTS → LOCATIONS → COUNTRIES를 통해 얻어내야한다.

---

명령어

desc emp;

: Description - 해당 테이블의 구조를 보여줌 

desc 조회 결과

• select empno, deptno, ename from emp;
  : 해당하는 컬럼(empno, deptno, ename)만 조회할 수 있음
  
  
• 7844 | 30 | TURNER
  : “레코드, 로우”라고 함 - 행
  
  
• empno, deptno
  : “컬럼”이라고 함 - 열
  
  
• 💡데이터베이스의 테이블은 정보를 표 형태로 관리하고 있음

CREATE TABLE employees (
	employee_id INT (11) UNSIGNED NOT NULL,
	first_name VARCHAR(20),
	salary DECIMAL(8, 2) NOT NULL,
	PRIMARY KEY (employee_id)
	);

• 이처럼 컬럼, 자료형, 널값 허용/비허용, 기본키 설정 등을 설정할 수 있음

---

SELECT, FROM 절 처리방법

• 1. from절로 테이블을 읽어와서
• 2. 테이블로부터 한 줄 (한 row)씩 읽는다.
• 3. SELECT절을 이용하여 프로젝션 (Projection)

👀프로젝션 : Select 절을 이용하여 어떤 데이터를 조회할 것인가를 의미

---

SELECT 구문

 

DISTINCT

select distinct deptno from emp;

distinct 수행

• distinct를 이용해서 중복행을 제거
• 이처럼 전체결과를 봤을때 중복되는 컬럼이 있으면 제거하는 것이다.
• empno의 영향으로 deptno의 중복이 제거되진 않는다.

ALIAS

select empno 사번, sal as 급여 from emp;

• 나타날 컬럼에 대한 다른 이름(=별칭) 부여
• 이처럼 empno 뒤에 바로 “사번”을 써도 ALIAS가 자동으로 적용되고,
  as 를 사용하여 ALIAS를 명시해주어도된다.

select empno 사번, sal as "급   여" from emp;

• 급여 ← 에 공백 넣고 싶으면 "급   여" 처럼 “ “ 로 묶어서 사용할 수 있다.

select empno, sal * 18 연봉 from emp;

• 급여 데이터(sal)에 * 18을 하여 데이터를 조작한 결과를 조회할 수 있다.

급여 데이터 -> 연봉 데이터로 조작

 

Concat

• MySQL이 제공하는 함수
• String에서 사용할 수 있었던 Concat()메소드처럼 문자열을 결합하는 함수

👀Oracle에서는 || 라는 명령을 통해서 문자열을 붙일수도 있지만MySQL에서는 지원하지 않는다.

select ename || sal from emp; -- (Oracle)에서 가능

• concat에서도 ALIAS로 별칭 부여를 할 수 있다 (as 사용)

이름, 입사일을 별칭으로 지정한 예

---

ORDER BY

• 조회한 결과를 정렬

• from 절 뒤에 order by 로 정렬하면, 이름별로 오름차순 정렬 (기본값 = asc 생략) (> A부터 출력)
• 내림차순 정렬 : desc (=Descending)를 붙여준다. (W부터 출력)

 

▶️예제 - ORDER BY : 여러 정렬 옵션 부여하기

여러개의 정렬 옵션 예제

• 처음에는 급여를 오름차순 정렬하는 것
• 뒤에 ename을 포함해서 여러개의 정렬 옵션을 줄 수 있다.
  이렇게되면 같은 급여를 가지는 SCOTT, FORD가 있으면 SCOTT부터 출력 > 오름차순 정렬
  (기본값 = ASC) - Ascending

▶️예제 - ORDER BY : 여러 정렬 옵션 부여하기 2

order by 3 desc, 2;

• 여러개의 정렬옵션을 준 예로
  1. 먼저 세번째로 부여한 컬럼인 (sal)을 기준으로 내림차순 정렬하고
  2. 정렬한 후에는 다음 기준으로 (ename)을 오름차순 정렬

order by deptno desc, sal desc;

• 여러개의 정렬옵션을 준 예로
  1. 부서번호(deptno)를 내림차순(desc) 정렬하고
  2. 정렬한 후에는 다음 기준은 급여를 내림차순을 정렬
  3. 30번 부서번호를 가진 사람들의 급여가 내림차순 정렬되고,
  4. 20번 부서번호부터는 다시 높은 급여부터 내림차순 정렬되고 있음을 확인가능

---

WHERE 

• 조건을 판단할 수 있는 명령어

select * from emp where sal > 2500;

• 급여가 2500 이상인 사람들만 출력
• from절 뒤에 위치한다.

select * from emp where deptno = 20;

• 부서번호가 20번인 사람들만 출력

• 조건끼리의 연결 and 연산과 or연산을 사용한다. ( && 연산과 || 연산도 사용이 가능하다)

---

WHERE - Null 값 찾기

WHERE - LIKE (패턴 매칭)

• 이름이 4글자인 사람 조회하는 경우이다.
• 이름이 s로 시작하는 사람 조회 > like 's%';
• 이름이 s로 끝나는 사람 조회 > like '%s';
• 세번째 글자가 a인 사람 > like '__a%';

WHERE - IN (포함 검사)

•  OR연산과 결과가 같다

select * from emp where deptno = 10 or deptno = 20;

• 부서번호가 10이거나 20인 결과 조회

select * from emp where deptno in (10, 20);

• IN을 통해 접근한 결과 = OR연산과 똑같다.
• 부서번호가 10, 20에 들어있는 것들 조회 (10번과 20번을 포함하는 값 조회)

WHERE - NOT IN (미포함 검사)

• in 안의 값들을 제외하고 조회

---

좌변을 변형시키는 것 - 성능 하락의 원인

WHERE substring(hire_date, 1, 4) = ‘1989’;

where hire_date like ‘1989%’;

• 위의 방법처럼 substring으로 모든 값들을 잘라내어 찾아내는 것보다
• 밑의 방법처럼 패턴매칭을 통해 찾는것의 성능이 나을 수 있다.

---

문자형 함수

upper / lower

• UCASE, UPPER : 함수명이 여러개일뿐 똑같은 기능을 한다.

select upper('Test'); -- TEST 출력
select upper(ename); -- 컬럼명으로도 조회 가능

select ucase('Test'); -- TEST 출력

• LCASE, LOWER : 함수명이 여러개일뿐 똑같은 기능을 한다.

select lower('TEsT'); -- test 출력
select lower(ename); -- 컬럼명으로도 조회 가능

select lcase('TEsT'); -- test 출력

💡주로 lcase, ucase보다는 upper, lower를 많이 사용

substring()

select substring('Happy Day', 3, 2); -- 3번인덱스부터 2글자를 잘라달라는 명령

• 데이터베이스는 대체로 1번인덱스부터 사용하므로 pp가 출력 (3,4번인덱스)
• 공백을 포함한다. 공백도 하나의 인덱스에 해당하는 값으로 취급

---

데이터 조작 함수

date_add()

• 데이터들에 값을 추가해줄 수 있다. (날짜에서 1일 추가, 2일 추가 등)

select date_add(hiredate, INTERVAL 2 DAY) from emp;

• hiredate의 값에서 2일 추가된 결과 조회

reverse()

select reverse(ename) from emp;

---

테이블 설계

• 테이블을 설계할때는 “정규화”라는 과정을 거친다.

• ex.
  사원의 정보를 가져올 때 그 안에는 부서의 정보도 포함되어야할 것
  사원이라는 하나의 테이블에 그 부서 정보가 지속해서 담기면 중복값이 많아질것이다.
• 한 부서에 20명의 사원 → 20명의 부서 데이터는 모두 동일할 것이므로
  중복된 부서 데이터를 테이블을 쪼개서 부서에 대한 정보는 부서테이블에서 갖게한다.
• ⭐“정규화” : 데이터를 나누는게 효과적일지, 데이터를 한 곳에 모아놓는게 효과적일지를 고민하는 것

---

💡저번에 MySQL을 사용하기 위해 도커를 구축하고, 설치하는 과정에 있다가

오늘 직접적으로 MySQL과 쿼리문에 대해서 실습을 진행해보니 데이터를 조회하고, 원하는 데이터만 가져올 수 있다는 것이 새로웠다.

새롭게 배우는 내용이다보니 알아야할 것이 많았지만 모두 데이터를 다룰때 필요한 기능이어서 잘 정리해야겠다고 생각했다!🚀