MySQL이란?

가장 널리 사용되고 있는 관계형 데이터베이스 관리 시스템

오픈 소스로, 다중 사용자와 다중 스레드 지원

C, C++, Java 등의 여러 프로그래밍 언어를 위한 다양한 API제공

유닉스, 리눅스, 윈도우 등 다양한 운영체제에서 사용할 수 있으며 특히 PHP와 함께 웹개발에서 사용

오픈소스 라이센스를 따르지만 상업적으로 사용시 상업용 라이센스 구매 필요

MySQL 설치

MySQL 워크벤치를 사용해 편리하게 데이터베이스 관리 가능

MySQL 기본

• SHOW DATABASES: 현재 서버에 어떤 DB 가 있는지 관찰

• USE: 사용할 데이터 베이스 지정: USE 데이터베이스이름
  • 지정해 놓은 후 특별히 다시 USE문을 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL 문은 지정된 DB에서 수행됨
  • workbench에서 직접 선택해서도 사용 가능
  • 왼쪽 창에서 현재 선택된 DB확인 가능

• SHOW TABLE(S): 선택된 데이터베이스의 테이블 이름 보기

• SHOW TABLE STATUS: 선택된 데이터베이스의 테이블 상태 (정보) 보기

• DESCRIBE (= DESC) : DESCRIBE 데이터베이스이름

  • 해당 테이블에 어떤 열이 있는지 확인 (정보 확인)

• SELETE: 요청한 데이터를 가져오는 구문

  • 일반적으로 가장 많이 사용, 데이터베이스 내 테이블에서 원하는 정보를 추출

SELETE * FROM 데이터베이스테이블이름 : 데이터베이스 테이블 내에 있는 모든 정보를 가져옴

*: all

SELETE 필요한열1, 필요한열2... FROM 데이터베이스테이블이름 : 테이블 내에서 필요로 하는 열만 가져오는 기능, 여러 개의 열을 가져오고 싶을 때는 , 로 구분

열의 이름은 추출하고 싶은 순서대로 배열

SELETE * FROM 데이터베이스테이블이름 WHERE 조건문 : 특정 조건 추가

조건문 사용 시 다양한 관계 연산자 사용 가능 ( >, <. =, AND, OR …)

EX) 한국에 있는 도시만 추출

한국에 있는 인구 수가 백만 명 이상인 도시만 추출:

• BETWEEN: 데이터가 숫자로 구성되어있어 연속적인 값 사용 시 (where문) ex) 인구수가 8백만- 9백만 사이에 있는 경우 WHERE Population BETWEEN 8000000 AND 9000000
  

• IN: 이산적인 값의 조건에서 사용 ex) 서울, 뉴욕, 도쿄의 정보만 확인하고 싶을 때 WHERE Name IN ('Seoul', 'New York', 'Tokyo')
  

• LIKE: 문자열의 내용 검색하기 위해 사용 한 글자 당 _한 개로, % 뒤에는 무엇이든 대입해서 연산하면… ex) KOR → KO_ 로 검색 가능 Tel % → Tel 로 시작하는 항목 전부 검색 가능
  

• Sub Query: 쿼리문 안에 또 쿼리문이 들어있는 것
  • 서브쿼리의 결과가 둘 이상이 되면 에러 발생

파란줄= 서브쿼리⇒ 결과 KOR이므로 CountryCode 가 KOR인 결과 모두 검색

• ANY: 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능 == SOME =ANY == IN
  

• ALL: 서브쿼리의 여러 개의 결과를 모두 만족하는 값 검색

• ORDER BY: 결과가 출력되는 순서를 조절하는 구문

  • 기본 오름차순 (ASC) , 열이름 뒤에 DESC붙여 내림차순

  • 여러개 혼합해 사용 가능

→ CountryCode는 오름차순, Population은 내림차순으로 정렬

→ 국가 면적 크기를 내림차순 해 검색

• DISTINCT: 중복된 항목은 한 개씩만 보여줌

• LIMIT: 출력개수를 제한

  • LIMIT N: 상위의 N개만 출력 (ASC, DESC 둘 다 같이 사용 가능)

• GROUP BY: 그룹으로 묶어주는 역할, 집계함수 함께 사용

  • 집계함수:

• 읽기 좋게 하기 위해 별칭 AS 사용: AVG (Population) → Average

→ 도시의 개수

→ 도시들의 평균 면적

• HAVING : WHERE과 비슷하게 조건 제한

  • 집계 함수에 대해 조건 제한

  • HAVING 절은 반드시 GROUP BY 절 뒤에 나와야 함

Population이 8백만 초과인 항목만 GROUP BY된 형태로 출력

• ROLLUP: 총합 또는 중간 합계가 필요한 경우 사용

  • GROUP BY절과 함께 WITH ROLLUP문 사용

NULL 옆 자리: 해당 그룹의 SUM 값

• JOIN: 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현 = 여러 개의 테이블을 합쳐 표현
  

• MySQL 내장 함수: 미리 정의된 함수, 문자열 함수/ 수학 함수/ 날짜, 시간 함수

• LENGTH(): 전달 받은 문자열의 길이 반환

• CONCAT(): 전달 받은 문자열을 모두 결합해 하나의 문자열로 반환 (, +기능)

  이때, 전달 받은 문자열 중 하나라도 NULL이라면 NULL을 반환
  

• LOCATE(): 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치 반환 이때 찾는 문자열이 문자열 내에 존재하지 않으면 0 반환 문자열의 시작 인덱스==1
  

• LEFT(): 문자열의 왼쪽부터 지정한 개수 만큼의 문자 반환

• RIGHT(): 문자열의 오른쪽부터 지정한 개수 만큼의 문자 반환

• LOWER(): 문자열의 문자를 모두 소문자로 변환

• UPPER(): 문자열의 문자를 모두 대문자로 변환

• REPLACE(): 문자열에서 특정 문자열을 대체 문자열로 교체

  SELECT REPLACE('MSSQL', 'MS', 'My'); >>> MySQL
  

• TRIM(): 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거
  • BOTH: 전달 받은 문자열의 양 끝에 존재하는 특정 문자를 제거 (기본 설정)
  • LEADING: 전달 받은 문자열 앞에 존재하는 특정 문자를 제거
  • TRAILING: 전달 받은 문자열 뒤에 존재하는 특정 문자를 제거
  • 제거할 문자를 명시하지 않으면 자동으로 공백 제거

• FORMAT(): 숫자 타입의 데이터를 세자리마다 , 로 구분
  • 1000000→ 1,000,000
  • 반환 되는 데이터의 형식은 문자열 타입
  • 두 번째 인수= 반올림할 소수 부분의 자릿수

• FLOOR(): 내림
• CEIL(): 올림
• ROUND(): 반올림
• SQRT(): 루트 (양의 제곱근)
• POW(): 거듭제곱 (첫번째 인수로 밑수 전달, 두번째 인수로 지수 전달)
• EXP(): e의 거듭제곱 (인수로 지수를 전달받음)
• LOG(): 자연로그
• SIN(): 사인 값 반환
• COS(): 코사인 값 반환
• TAN(): 탄젠트 값 반환
• PI(): 파이 값 반환
• ABS(): 절댓값 반환
• RAND(): 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 랜덤 생성 보통 round 함수와 함께 *100 해서 많이 사용
  

• NOW(): 현재 날짜와 시간 반환 반환되는 값: YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS
  
• CURDATE(): 현재 날짜 반환 반환되는 값: YYYY-MM-DD or YYYYMMDD
  
• CURTIME(): 현재 시각 반환 반환되는 값: HH:MM:SS or HHMMSS
  

• DATE(): 전달받은 값에 해당하는 날짜 정보 반환
• MONTH(): 월에 해당하는 값 반환
• DAY(): 일에 해당하는 값 반환 (0~31)
• HOUR(): 시간에 해당하는 값 반환 (0~23)
• MINUTE(): 분에 해당하는 값 반환 (0~59)
• SECOND(): 초에 해당하는 값 반환 (0~59)

• MONTHNAME(): 월에 해당하는 이름 반환

• DAYNAME(): 요일에 해당하는 이름 반환

• DAYOFWEEK(): 일자가 해당 주에서 몇 번째 날인지 반환

  일요일=1, 토요일=7
  

• DAYOFMONTH(): 일자가 해당 월에서 몇 번째 날인지 반환 (1~31)

• DAYOFYEAR(): 일자가 해당 년도에서 몇 번째 날인지 반환 (1~366)

• DATE_FORMAT(): 전달 받은 형식에 맞춰 날짜와 시간 정보를 문자열로 변환

MySQL 고급

• CREATE TABLE: 새로운 테이블 생성

  CREATE TABLE city2 AS SELETE *FROM city; : city 테이블의 모든 내용 가져와 city2 테이블 생성
  

• CREATE DATABASE: 새로운 데이터베이스 생성

  USE 문으로 새로 만든 데이터베이스 사용
  

• ALTER TABLE

  • ADD문과 함께 사용 시 테이블에 추가 column 생성 가능
  • MODIFY문과 함께 사용 시 테이블의 column 타입 변경 가능
  • DROP문과 함께 사용 시 테이블의 column 제거 가능

• 인덱스: 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용

  • 인덱스를 사용하지 않으면: 일반적으로 데이터를 검색할 때 순서대로 테이블 전체를 검색함 → 시간이 많이 걸림

    → 데이터 검색, 질의 시 테이블 전체를 읽지 않아도 되므로 속도가 빨라짐

  • 설정된 column 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우 인덱스도 함께 수정되어야 함 → 인덱스를 사용해도 속도가 느려질 수 있음

    ⇒ 수정보다 검색이 자주 사용되는 테이블에서 사용하면 효율적

• CREATE INDEX: 인덱스 생성

• SHOW INDEX: 인덱스 정보 보기

• CREATE UNIQUE INDEX: 중복 값을 허용하지 않는 인덱스

• FULLTEXT INDEX: 일반적인 인덱스와 달리 매우 빠르게 테이블의 모든 텍스트 column 검색

• (ALTER TABLE 이용해)인덱스 삭제: DROP문 이용

  DROP 문이 내부적으로 ALTER 문으로 자동 변환되기 때문에 ALTER TABLE 쓰지 않아도 삭제 가능

  ALTER TABLE test
  DROP INDEX Col3Idx;
  
  SHOW INDEX FROM test
  
  ==
  
  DROP INDEX Col3Idx ON test;

  → Col3Idx 삭제된 삭제로 보기
  

• 뷰: 데이터베이스에 존재하는 일종의 가상 테이블

  • 실제로 데이터를 저장하진 않지만 다른 테이블이나 뷰의 데이터를 기반으로 결과를 보여줌
  • MySQL에서 뷰는 다른 테이블이나 다른 뷰에 저장되어있는 데이터를 보여주는 역할만 수행
  • 뷰를 사용해 여러 테이블이나 여러 뷰를 하나의 테이블처럼 볼 수 있음
  • 특정 사용자에게 전체가 아닌 필요한 column만 보여줄 수 있음
  • 복잡한 쿼리 단순화해 사용 가능, 재사용 가능
  • 한번 정의된 뷰는 변경할 수 없음
  • 삽입, 삭제, 갱신과 관련한 제한사항 많음
  • 자신만의 인덱스 가질 수 없음

• CREATE VIEW: 뷰 생성

• ALTER VIEW: 뷰 수정

• DROP VIEW: 뷰 삭제

• city, country, countryLanguage 테이블을 JOIN 하고 한국에 대한 정보만 뷰 생성하기

• INSERT: 테이블 이름 다음에 나오는 열 생략 가능 (= 특정 열을 명시하지 않고도 데이터 삽입 가능) 생략할 경우,,, VALUE 다음에 나오는 값들의 순서 및 개수= 테이블이 정의된 열 순서 및 개수

  INSERT INTO test
  VALUE(1, 123, 1.1, "Test");
  
  SELECT * FROM test;
  
  >>> test 테이블에 1, 123, 1.1, "Test" 차례로 입력됨

  +) MySQL Workbench에서는 결과 그리드에서 직접 입력한 후 apply해서 사용도 가능
  
• INSERT INTO SELETE: INSERT INTO test2 SELETE * FROM test; : 복사: test의 테이블 내용을 test2에 그대로 입력
  
• UPDATE: 기존에 입력되어 있는 값 변경 WHERE문을 함께 사용하지 않으면 테이블의 전체 행의 내용이 변경됨

  UPDATE test
  SET col1=1, col2=1.0, col3='test'
  WHERE id=1; // 안 쓰면 전체 행의 내용 변경

• DELETE: 행 단위로 삭제 DELETE FROM 테이블이름 WHERE 조건 : WHERE 사용하지 않으면 전체 행 내용 삭제 데이터는 지워져도 테이블의 용량은 줄어들지 않음 원하는 데이터만 삭제, 삭제 후 삭제 실행 취소 가능
  
• TRUNCATE: 용량도 줄어들고 인덱스 등도 모두 삭제 테이블은 삭제하지 않고 데이터만 삭제 한꺼번에 전부 삭제되며, 삭제 후 절대로 되돌릴 수 없음
  
• DROP TABLE: 테이블 전체를 삭제 (공간 자체, 객체 자체를 삭제) 삭제 후 절대로 되돌릴 수 없음 → DROP TABLE한 테이블을 SELETE하면 오류 발생
  
• DROP DATABASE: 해당 데이터베이스 삭제

ERD 란?

• ERD: 데이터 모델링. 데이터베이스 구조를 한눈에 알아보기 위해서 사용

  • 개체: entity: 관리하고자 하는 정보의 실체, 사람, 객체, 개념

    데이터베이스 설계 시 entity= 테이블

    모든 entity는 하나 이상의 식별자 (UID)를 가져야 함
    

  • 속성: attribute: entity를 구성하는 요소

    데이터 타입을 반드시 명시해주어야 함
    
    - Key Attribute: 객체 식별 시 사용
    - Composite Attribute: 독립적인 attribute들이 모여서 생성된 attribute
    - Multi-Valued Attribute: 하나의 attribute가 여러개의 값을 가지는 attribute
    - Derived Attribute: 다른 attribute가 갖고 있는 값으로부터 유도된 attribute

    학생: entity
    학번, 이름, 주소, 전공… : attribute

  • 관계: relationship: 두 entity간 선을 긋고 relationship명칭 기록

    • 1:1 관계: 양쪽 모두 하나씩 존재

    • 1:N 관계: 일대다/ 다대일 관계, 하나의 원소가 두개 이상의 원소와 관계를 맺는 것

    • N:M 관계: 양쪽 모두 하나 이상과 연관 가능

      

    1. 없어도 ,하나만, 다중 가능
    2. 없거나 다중만 가능
    3. 없거나 하나만 있어도 가능
    4. 하나 이거나 다중
    5. 오직 한 개
    6. 다중
    7. 한 개

• 데이터 모델링 방법:

1. 업무 파악 (요구 사항 수집 및 분석)
2. 개념적 데이터 모델링
3. 논리적 데이터 모델링
4. 물리적 데이터 모델링

ex) 네이버 게시판의 화면에 어떤 것들이 필요한지에 대해 정리 (요구사항 수집 및 분석)

→ 네이버 게시판의 화면에 표현되는 데이터들을 파악해 관계 설정 (개념적 데이터 모델링)

→ 개념적 데이터 모델링한 것을 표로 제작 (논리적 데이터 모델링)

→ 실제 데이터베이스 테이블로 제작 (물리적 데이터 모델링)

https://velog.io/@cil05265/E-R-다이어그램ERD-작성-표기법-및-예시

https://dbdiagram.io/d

1차 ERD

• 제약 조건: 모든 관계의 인스턴스가 만족해야하는 조건

• domain constraints- 도메인 제약 조건: 속성의 값= 특정 범위, 집합 내의 값

  도메인(Domain)
  도메인은 특정 애트리뷰트가 가질 수 있는 값의 집합을 정의합니다. 예를 들어, "학번" 애트리뷰트의 도메인은 특정 형식의 문자열(예: 8자리 숫자)일 수 있고, "전공" 애트리뷰트의 도메인은 "컴퓨터공학", "전자공학", "경영학" 등과 같은 특정 전공의 목록일 수 있습니다.

  원자값(Atomic Value)
  원자값은 더 이상 나눌 수 없는 값으로, 데이터베이스에서 각 애트리뷰트는 원자값을 가져야 합니다. 예를 들어, "이름" 애트리뷰트는 "홍길동"과 같은 단일 이름을 가져야 하며, "이름, 나이"와 같은 복합적인 값은 허용되지 않습니다.

  따라서 "각 애트리뷰트의 값은 반드시 도메인 dom(A)에 속하는 원자값이어야 한다"는 것은:

  각 애트리뷰트는 정의된 도메인 내의 값만 가질 수 있다.
  애트리뷰트의 값은 원자값이어야 하며, 복합적인 값은 허용되지 않는다.
  

• key constraints - 키 제약조건
  기본 키는 중복 값을 가질 수 없으며, NULL값은 허용되지 않음

  외래 키는 참조하는 테이블의 기본 키와 동일한 도메인 값을 가져야 하며 NULL값을 허용할 수 있음
  
  외래 키를 통해 두 테이블간의 관계를 정의하고 데이터의 무결성 유지 가능

• entity integrity constraints - 엔티티 무결성 제약조건
  각 테이블은 반드시 하나의 기본 키를 가져야 하며 이 기본 키는 각 레코드를 고유하게 식별함

  기본 키를 구성하는 attribute는 NULL값을 가질 수 없음
  
  기본 키의 값음 테이블 내에서 유일해야하며 중복된 값을 가질 수 없음

• referential integrity constraints - 참조 무결성 제약조건

  외래 키가 참조하는 기본 키의 값이 변경되거나 삭제될 경우, 참조 무결성 제약조건은 이러한 변경이 다른 테이블에 미치는 영향을 관리합니다. 예를 들어, 외래 키가 참조하는 기본 키가 삭제되면, 해당 외래 키를 가진 레코드도 삭제되거나 NULL로 설정될 수 있습니다.