이것이 MySQL이다 - part02_MySQL 기본

CHAPTER 04 데이터베이스 모델링
프로젝트란 현실세계의 업무를 컴퓨터 시스템을 옮겨놓는 일련의 과정
폭포수모델: 프로젝트 계획 - 업무분석 - 시스템설계 - 프로그램 구현 - 테스트 - 유지보수
100층짜리 건물을 지을때 벽돌을 예쁘게 쌓거나 빨리 쌓는 것이 그다지 중요한 작업이 아니다.
 
데이터베이스 모델링: 현 세계에서 사용되는 작업이나 사물들을 DBMS의 데이터베이스 개체로 옮기기 위한 과정
https://standout.tistory.com/604

데이터베이스 관리 시스템, RDBMS

https://standout.tistory.com/94

데이터베이스 서비스 구성

 
 
MySQL Workbench 는 모델링 툴을 제공해준다.
https://standout.tistory.com/1709

MySQL Workbench로 erd만들기

 
 
 
CHAPTER 05 MySQL 유틸리티 사용법
MySQL Workbench
MySQL Workbench는 2002년에 만들어진 DBDesigner4 제품, MySQL 비주얼툴로 사용되다가 2003년에 GUI Tools Bundle로 통합되었다. 2005년에 MySQL Workbench프리뷰 버전으로 변겨오디어 발표되고 2007년부터 본격적으로 개발되고 버전이 업그레이드 되었다. 
- 데이터베이스 연결기능
- 인스턴스 관리 
- 위저드를 이용한 MySQL의 동작
- 통합된 기능의 SQL 편집기
- 데이터 베이스 모델링 기능
- 포워드 리버스 엔지니어링 기능
- 데이터베이스 인스턴스 시작 종료 
- 데이터베이스 내보내기 가져오기, 계정관리 
 
connection 
접속하는 서버가 등록되어잇다. 여러개를 등록 할 수 있다. 

 
 
 
대부분 Standard (TCP/IP)를 사용한다.

 
 
 
 
로그인.
root사용자로 접속하는것은 바람직하지 않다. 모든 작업을 할 수 있기때문에 암호가 유출시 문제가 생길 수 있다.

 
 
 
 
 
ssh , advanced는 특별한경우가 아니라면 변경할필요없다.
 
ssh
보안을 위한 암호규약
서버와 클라이언트가 통신할때 암호화를 통해 비밀을 유지한다.

 
 
 
advanced
프로토콜의 압축, 인증방식을 설정할 수 있다.

 
 
 
 
 
remote management
원격관리를 위해 설정하는 부분, 이부분이 활성화되기 위해서는 hostname 부분이 127.0.0.1을 제외한 실제 ip주소로 되어있어야한다.

 
 
 
 
 
 
system profile
접속할 서버의 os종류 및 mysql 설정 파일의 경로를 설정한다.
이가 활성화되려면 remote management에 두번째 native window remote management~ 가 체크되어있어야한다.

 
 
 
navigator의 기능은 워낙많아 sql을 몰라도 사용이 가능할 정도이다.
schemas: 데이터베이스 생성 및 삭제, 데이터베이스 개체 생성 관리, 데이터베이스 속성 조회
administration:  
 - management: 서버의 가동상태 설치된 폴더 확인, 서버에 연결되어있는 클라이언트 정보 확인, 사용자 생성, 삭제 및 권한관리, 서버변숫값관리, 데이터 내보내기 가져오기 기능
 - instance: ㅡMySQL 연결정보 관리, 인스턴스 중지 시작, server에 기록된 로그 조회, MySQL 옵션 파일의 설정 정보 확인 및 변경
 - performance: 네트워크 상태 및 MySQL 성능 상태 확인, 성능 상태의 보고서 작성, 성능 구성으 ㅣ설정

 
 
 
 
 
 
내비게이터의 탭에서는 모든 개체에 대해 SQL문으로 자동 생성해주는 기능을 가지고있다. 
TABLE - 마우스 오른쪽 - SEND TO SQL EDITOR - CREATE STATEMENT

 
 
 
 
 
server status를 시각화해 볼 수 있다.

https://standout.tistory.com/1711

MySQL Workbench Administration Server Status - RuntimeError: Unable to execute command chcp.

 
 
 
chilent connection
클라이언트가 현재 휴먼인지 아닌지 여부를 확인할 수 있다. 
마우스 오른쪽 클릭 - kill connection으로 연결을 강제로 끊을 수도 있음

 
 
 
 
이외에도 mysql 사용자 관리

 
사용자를 추가하고, 

 
최대쿼리수, 최대 업데이트수, 시간당 최대 접속수, 실시간 동시접속 최대수 등을 설정할 수 있다. 
0은 특별히 제한이 없다.

 
administrative roles에서는 권한을 부여할 수 있다.
DBA는 모든 권한 이있다.

 
 
 
schema privileges에서는 특정 테이블에 대해 권한을 부여할 수 있다.

 
 
시스템 변수 확인

 
 
 
 
 
데이터 export, import

https://standout.tistory.com/1709

MySQL Workbench로 erd만들기

 
 
 
instance
startup/shutdown
현재 mysql 서버의 작동상태 확인, mysql 서버 중지 시작

 
 
 
 
 
server logs
서버에 기록된 오류, 경고 등의 로그 확인
 
 
option file
mysql 핵심설정 파일인 my.ini 파일의 내용을 gui모드로 보여줌, 동일한 효과

 
 
 
 
 
 
dashboard
네트워크, mysql서버, innodb 상태를 그래픽으로보여준다.

 
 
 
 
 
 
performance reports
입출력이 오래걸린 파일, 비용이 많이 든 쿼리문, 데이터베이스 통게등의 항목을 조회하고 결과 내보내기

 
 
 
 
performance schema setup
성능에 대한 설정, show advance나 high advanced를 클릭하면 세부 설정을 확인 할 수 있다.

 
 
 
 
 
데이터베이스 txt를 드롭다운하면 배치된다. 오타에 활용

 
 
 
 
edit - format을 통해 예약어들을 대문자로 설정할 수 있다.

 
 
 
 
 
beauty query로 자동정렬 시킬 수 있다.

 
 
 
 
 
 
 
 
 
 
CHAPTER 06 SQL 기본
 
help 'select' 해보면 select에 다양한 옵션을 확인 할 수 있다.

help 'select';

/*
Syntax:
 SELECT
     [ALL | DISTINCT | DISTINCTROW ]
     [HIGH_PRIORITY]
     [STRAIGHT_JOIN]
     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
     [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
     select_expr [, select_expr] ...
     [into_option]
     [FROM table_references
       [PARTITION partition_list]]
     [WHERE where_condition]
     [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
     [HAVING where_condition]
     [WINDOW window_name AS (window_spec)
         [, window_name AS (window_spec)] ...]
     [ORDER BY {col_name | expr | position}
       [ASC | DESC], ... [WITH ROLLUP]]
     [LIMIT {[offset,] row_count | row_count OFFSET offset}]
     [into_option]
     [FOR {UPDATE | SHARE}
         [OF tbl_name [, tbl_name] ...]
         [NOWAIT | SKIP LOCKED]
       | LOCK IN SHARE MODE]
     [into_option]
 
 into_option: {
     INTO OUTFILE 'file_name'
         [CHARACTER SET charset_name]
         export_options
   | INTO DUMPFILE 'file_name'
   | INTO var_name [, var_name] ...
 }
*/

 
 
 
 
 
사실상 많이 쓰이는 형태는 아래와같다.

/*
Syntax:
 SELECT
     [FROM table_references]
     [WHERE where_condition]
     [GROUP BY {col_name | expr | position}]
     [HAVING where_condition]
     [ORDER BY {col_name | expr | position]
*/

 
 
 
 
 
 
주석

-- 주석
/*
여러줄 주석
*/

 
 
 
데이터베이스 조회 show databases;

 
 
 
 
수행한 결과물이 잘 뜨지않을경우 새로고침

 
 
 
 
 
select에 관계 연산자의 사용

 
 
 
where ~문도 가능하지만
or문이 굳이 많아질 필요없다. where ~ in (리스트) 형 또한 가능하다.

 
 
 
 
like %, _ 검색. _는 한글자 %는 무엇이든 허용

 
 
 
 
 
서브쿼리 + any all in
서브쿼리는 문법상 문제가 없어보이지만 둘 이상의 값을 반환할때 오류가난다
이때 사용하는것이 any () 서브쿼리 앞에 붙여준다.
이 any()는 앞서 살펴봤던 in()과 동일하다.
 

또 all을 붙여줄 수도 있는데
any는 '170~173보다 크거나 같은' 등의 조건이 적용되었다면
all은 교집합 '173보다 크거나 같은' 조건이 적용된디

 
 
 
order by 순서대로 출력 desc asc 내림차순 오름차순 

 
 
 
 
중복제거
select distinct 테이블 from 컬럼

 
 
 
 
 
 
갯수제한 limit

 
 
 
 
table 복사하기
create table 새로운테이블 ( select 컬럼들, 컬럼들 from 복사하고싶은 table)
값도 통째로 복사된다.

 
 
 
 
group by 그룹으로 묶어주는 역할

 
 
 
 
 
groupby와 집계함수는 짝꿍이다.
sum()말고도 여러 집계함수가 있다. 
 
 
 
avg() 평균

 
 
 
 
min max

 
 
 
coujnt(), 행 전체는 count(*)

 
 
 
 
 
 
문법상으론 맞지만
where 다음에는 집계함수가 나타날 수 없다. 
 
where 뒤에 집계함수를 못쓰는 이유는 '실행순서'때문인데 sql은 from - where - group by - 집계함수 - select - order by 순으로 실행된다.
where일때는 그룹 만들기 전 단계로 각 행을 하나씩 필터링 하고있게되는데 이때 avg 같은 계산단계는 존재하지않는다 그러기에 대신 having문법을 추가해 사용하는것.
단순 타이밍 문제.
 
 
 
having.
having 절은 꼭 group by 다음에 나와야한다.
group by는 그룹핑, having은 where의 조건

 
 
 
 
 
rollup 중간 합계 + 총합계를 구해줌

 
 
 
sql은 dml ddl dcl로 분류한다.
DML Data Manipulation language - select insert update delete
DDLData Definition language - create drop alter
DCL Data Control Language  - grant revoke deny
 
 
 
 
auto_increment 자동증가

 
 
 
 
 
 
select last_insert_id는 마지막으로 insert됫던 auto_increment 값을 보여주지만, 
직접 지정하여 insert했다면 값이 0 일 수 있다.

 
 
 
 
특정 원하는 시작숫자가 있다면 auto_increment = 1000를 지정할 수 있다.

 
 
 
 
set @@auto_increment_increment = 10
set global auto_increment_increment = 10
증가값을 설정할 수 있다.
 
@@은 “변수 값을 읽거나/설정할 때 쓰는 표현”
GLOBAL / SESSION은 “어디에 적용할지 지정하는 키워드"
실제로 예전에는 @@variable로,  SET @@global.auto_increment_increment = 10;라 했으나
GLOBAL 변수는 “서버 전체 설정 변경”의 구조적인 특성을 지녀 요즘은 분리한다.
 
 
내 Workbench 창 1개 SESSION으로 연결 종료하면 사라지지만
GLOBAL시 MySQL 서버 전체로 다른 사람 영향을 주며 서버 재시작 전까지 유지된다

 
 
 
insert into 해당테이블 select 컬럼, 컬럼, 컬럼... from 예시로쓸 테이블
샘플데이터 입력하기
원하는 테이블을 생성하고
사용할 테이블을 선택해서 사용하자. null이있다면 그대로 가져온다.

 
 
 
 
 
delete 데이터 삭제, where과 limit등을 활용할 수 있다. 

 
 
 
 
조건이 안맞을 경우에 insert문이 중단될 수 있다.
한 행 때문에 나머지 데이터가 입력이 안된다는 상황은 너무나 답답하다. 이때 사용하는 것이 ignore
에러난 값은 무시하고 진행한다.

 
 
 
 
 
 
width절은 CTE Common Table Expression을 표현하기 위한 구문, MySQL 8.0이후부터 사용할 수 있다.
기존의 view, 파생테이블, 임시테이블로 사용되던것들을 대신 할 수 있다. 
비재귀적 CTE 복잡한 쿼리문장을 단순화 시킨다.
예) with 새로운이름(컬럼이름, 컬럼이름) as (쓰던 쿼리문) select * from 새로운이름 order by 컬럼 desc~

 
 
width절로 얼마나 가시성이 확보되고 코드가 단순해진다는걸까?
 
width절은 중복 cte가 허용된다.
주의할점은 ccc에서 aaa, bbb를 참조할 수 있지만 aaa이나 bbb에서 ccc를 참조할 순 없다.
정의하지 않은 cte를 미리 참조할 수 없는것.
cte common table expression

 
 
 
 
CHAPTER 07 SQL 고급
 
숫자형 데이터형식은 정수, 실수 등의 숫자를 표현하고 BIT, TINYINT, SMALLINT, MEDIUMINT, INT INTEGER, BIGINT, FLOAT, DOUBLE REAL, DECIMAL(m, [d]), NUMBER(m.[d])이 있다. 
 
DECIMAL 데이터형식은 정확한 수치를 저장한다. 소수점이 들어간 실수를 저장하려면 되도록 DECIMAL을 사용하는 것이 바람직하다.
FLOAT DOUBLE은 근사치의 숫자를 저장한다. 상당히 큰 숫자를 저장할 수 있다. 
TINYINT, SMALLINT, MIDIUMINT부호없는 정수를 지정할때는 UNSIGNED 예약어를 뒤에 붙여준다. 나머지 데이터 형식에도 붙일 수 있으나 자주 사용되지않는다.
 
문자 데이터 형식은 CHAR(n), VARCHAR(n), BIANRY(n), VARBINARY(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, ENUM, SET이 있다.
 
CHAR(100)에 3글자만 저장해도 97자리를 낭비하게 되지만 VARCHAR(100)에는 3자리만 사용해 공간을 효율적으로 운영할 수 있다. 하지만 CHAR형식을 설정하는것이 INSERT, UPDATE시 더 좋은 성능을 발휘한다.
BINARY와 VARBINARY는 바이트 단위의 이진데이터 값을 저장하는데 사용된다.
TEXT는 필요한 크기에 따라 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT를 사용한다.
BLOB는 사진파일, 동영상파일, 문서파일등의 대용량 이진데이터를 저장하는데 사용될 수 있다.
 
날짜 시간 데이터 형식에는 DATE, TIME, DATETIME, TIMESTAMP, YEAR가 있다. 
 
기타 데이터 형식에는 GEOMETRY, JSON 이 있다.
 
 
LOB Large Object, 약 4gb파일을 하나의 데이터로 저장할 수 있어 영화대본열에는 영화대본 전체가 들어가고, 영화동영상 열에는 실제 영화파일 전체가 들어갈 수 있다. (!!!)
 
 
SQL도 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. 
SET @ 변수이름 = 값;
SELECT @변수이름;

https://cafe.naver.com/THISISMYSQL

이것이MySQL이다 : 네이버 카페

 
 
 
 
 
 
limit에는 숫자가 와야하기때문에 원칙적으로 변수를 사용할 수 없다.
하지만 prepare와 execute문을 활용해 변수의 활용이 가능하다. 
prepare 쿼리이름 from '쿼리문'은 쿼리문만 준비해놓고 실행하지않는다. 긜고 execute쿼리이름을 만나는 순간에 ?가 대입되어 실행된다.
결론적으론 limit 뒤에 숫자가 온다는것.

 
 
 
 
 
 
 
 
데이터 형식 변환 함수
CAST(), CONVERT() 이 둘은 형식만 다를뿐 거의 비슷한 기능을 한다.
반올림한 정수의 결과를 반환한다.
+ AS SIGNED는 부호있는 정수로 강제로 변환하는 것이다. 
정수로 바꿔 해석하되 음수도 허용하는것으로 AS SIGNED가 기본이고 UNSIGNED는 양수만가능하고 음수는 불가능하다
문자열에서 숫자로 변환할때 명시적으로 사용한다.

 
 
 
 
 
 
 
 
 
 
 
명시적 형변환
CAST, CONVERT없이도 데이터 형식을 변환하는 것을 말한다.
다만 다른 DBMS에서 겨로가가 다를 수 있다. CONCAT처럼 문자열 처리가 되기도한다.

 
 
 
 
 
 
 
MYSQL 내장함수
MYSQL은 많은 내장 함수를 포함하고 있다. 크게 제어흐름, 문자열, 수학, 날짜/시간, 전체텍스트, 형변환, XML,비트, 보안/압축, 정보, 공간분석, 기타 함수등으로 나뉜다. 
전체 함수의 갯수는 수백개가 넘으며 지금까지 다루지 않았던 내장함수 중 자주 사용되는 것을 다루자.
 
 
 
 
 
 
 
제어흐름 함수
IF()

 
 
 
ifnull()

 
 
 
nullif(a, b) a와 b가 다를경우 a를 반환한다.

 
 
 
 
 
 
 
case when else end
내장함수라기보다 연산자로 분류되는데 다중분기에 사용된다. if elif과 같음 try catch finally와도 비슷함.

https://standout.tistory.com/1172

예외처리: try-catch문, try catch finally

 
 
 
 
 
 
ascii(), char()  아스키코드, 숫자로 돌려준다.

 
 
 
BIT_LENGTH 할당된 bit크기, length 할당된 byte수, CHAR_LENGTH 문자의 갯수 반환

 
 
 
 
concat(a, b..), concat(구분자, a, b..)문자열 이어주기, 구분자를 포함해 문자열 이어주기

 
 
 
 
 
ELT(위치, a, b..)  위치번째에 해당하는 문자열 반환

 
 
 
 
 
 
 
field(문자열, a, b..) 
해당 문자를 찾아 위치반환, 없을경우 0 null 반환

 
 
 
 
find_in_set(찾을 문자열, 문자열 리스트)
뭉떵이 문자열에서 찾을 수 있음

 
 
 
instr(기준문자열, 부분문자열)
문자열에 해당 문자가 몇번쨰  index에서 시작되는지

 
 
locate(부분문자열, 기준문자열)
instr과 파라미터가 반대, 해당문자가 문자열에서 몇번쨰 index에서 시작되는지

 
 
 
 
 
 
format()
원하는 소수점 자릿수까지표시, 000마다 쉼표도 표시해줌

 
 
 
 
 
 
bin hex oct 2진수, 16진수, 8진수 값 반환

 
 
 
 
 
 
insert()
문자열에서 특정 위치에서 n개문자열을 지우고 삽입한다.

 
 
 
 
 
 
left() right()
왼쪽, 오른쪽에서 문자열 길이만큼 반환한다.

 
 
 
 
 
 
대소문자변환

 
 
 
 
 
 
lpad() rpad()
문자열을 n길이만큼 늘린 뒤에 왼쪽 혹은 오른쪽에 빈곳을 문자열로 채운다.

 
 
 
 
 
 
ltrim() rtrim() 
문자열의 왼쪽 또는 오른쪽 공백을 제거한다. 중간은 제거되지않는다.

 
 
 
 
trim(leading/both/trailing  a from 문자열)
문자열에서 왼쪽 양쪽 오른쪽으로 해당 문자열을 제거할 수 있다 .
다만 공백을 만나는순간 멈춰버리니 공백이 되도록 없는 문자열을 사용할것.

 
 
 
 
 
 
 
repeat() 
문자열을 n번 반복

 
 
 
 
 
 
 
 
 
replace()
문자열에서 a를 찾아 b로 덮어쓰기해줌

 
 
 
 
 
 
reverse() 거꾸로 만들기

 
 
 
 
 
space() 공백추가하기

 
 
 
 
 
 
 
 
substring()
문자열에서 a번째로부터 b개잘라 출력한다.
,가 아닌 from for 로도 표현가능하다

 
 
 
 
 
 
SUBSTRING_INDEX()
문자열에서 a가 b번째 나왔을때부터 왼쪽 혹은 오른쪽을 버린다.

 
 
 
 
 
 
 
 
 
 
abs() 절대값 반환

 
 
 
 
삼각함수 - SIN() 원하는 각도로 위로이동

 
 
삼각함수 - COS() 원하는 각도로 오른쪽으로 간다

 
 
 
삼각함수 - TAN() 원하는 각도로 이동후 기울기를 계산한다.

 
 
 
 
삼각함수 - degrees()
sin값에서 몇도인지 헷갈릴떄

 
 
 
삼각함수 - degrees()
acos()가 몇도인지 헷갈릴떄

 
 
삼각함수 - degrees()
반대로 기울기로 각도찾기

 
 
 
 
삼각함수 - degrees()
좌표로 각도 찾기
삼각함수 - degrees()

 
 
 
 
 
 
 
celing, floor, round
반올림, 반내림, 버림

 
 
 
 
 
conv(바꿀것, 원래진수, 바꿀지수)
원래 진수에서 변환할 진수로 변환

 
 
 
degree 각도 반환

 
 
 
 
 
 
 
지수, 로그 관련 함수를 살펴보기전에 e에 대해 잠시 알아보자.
 
e = 2.718 = 연속적인 변화(성장/감소)를 수학적 기준 상수 복리, 연속 성장, 미분/적분 (변화량 계산), 확산/성장 모델 등 항상 등장하는 상수인데 일부자연현상과 증가복리에서 대부분 이런 패턴을 확인 할 수 있다 .계속 쪼개 등가할때 자동으로 나오는 기본 성장 숫자. 복리 계산에서 등장 (Jacob Bernoulli)하여 자연로그 연구 (Euler가 정리)로 Euler가 기호 e 사용했다.
 
exp() e를 n번 곱한값

 
 
 
ln() 반대로 e를 몇번을 곱해야 n값이 나오는가?

 
 
 
 
 
 
log()
이 언어의 기본값을 몇번 곱해야 이값이 나오는가? mysql에서는 e가 기본값으로 현재 ln과 같이 나옴
다른 언어/수학에서는 다를 수 있다. 
기준값이 있을경우: log(a, b) 기준값에 몇번 곱해야 b가 나오는가?

 
 
 
 
 
 
log2() 2를 몇번 곱해야 이값이 나오는가?
log10() 10를 몇번 곱해야 이값이 나오는가?

 
 
 
 
 
 
 
 
mod() 나머지

 
 
 
 
 
 
pow() 거듭제곱구하기
sqrt 루트값 구하기

 
 
 
 
 
rand() 랜덤 실수값 구하기
특정 구간의 정수값을 구하고 싶다면  floor(m + (rand() * (n-m)))

 
 
 
 
 
 
sign() 음수인지 양수인지 확인

 
 
 
 
 
 
truncate()
소숫점n번째까지만 표시하고 버린다.

 
 
 
기준날짜에서 특정기간을 더하거나 빼기
adddate( 기준날짜, interval n day month..)
subdate( 기준날짜, interval n day month..)

 
 
curdate() curtime() 현재날짜 현재시간
now() 현재 날짜 +시간
sysdate()

now()와 sysdate() 차이
모두 현


 
 
 year() month() day() hour() minute() second() microsecond()
지정된 날짜, 시간에서 연 월 일 시 분 초 밀리초를 구한다.
 date() time() datetime 형식에서 날짜, 시간만 반환함

 날짜와 os 관려 

DATEDIFF(날짜, 날짜)
TIMEDIFF(날짜/시간, 날짜/시간)

SELECT DATEDIFF('2025-01-01', NOW()),
       TIMEDIFF('23:23:59', '12:11:10');

SELECT DAYOFWEEK(CURDATE()),
       MONTHNAME(CURDATE()),
       DAYOFYEAR(CURDATE());

SELECT LAST_DAY('2026-05-01');

SELECT MAKEDATE(2026, 30);

SELECT MAKETIME(12, 11, 10);

SELECT PERIOD_ADD(202601, 11),
       PERIOD_DIFF(202601, 202612);

SELECT QUARTER('2026-05-23');

SELECT TIME_TO_SEC('12:12:10');

SELECT USER(), DATABASE();