데이터베이스 MySQL

oatraspberry·2022년 11월 1일
sql데이터베이스
post-thumbnail

데이터베이스(DB)

데이터를 저장하는 공간.
관련성을 가지며 중복이 없는 데이터들의 집합.
중복된 데이터를 없애고, 자료를 구조화하여, 효율적인 처리를 할 수 있도록 관리됨.
여러 업무에 여러 사용자가 데이터 베이스를 사용할 수 있음.

  • DBMS(DataBase Management System): 데이터베이스를 관리하는 시스템
    사용자에게 DB를 정의하고 만들고 관리하는 기능을 제공하는 소프트웨어 시스템
    데이터에 대한 많은 보안을 제공하지 않고 정규화를 수행할 수 없어 데이터는 높은 중복성을 가질 수도 있다.
  • RDBMS(Relational DMBS) - Oracle, MySQL, MSSQL, MariaDB
    DBMS의 한 유형.
    관계형 데이터베이스는 데이터가 하나 이상의 열과 행의 테이블(또는 '관계')에 저장되어 서로 다른 데이터 구조가 어떻게 관련되어 있는지 쉽게 파악하고 이해할 수 있도록 사전 정의된 관계로 데이터를 구성하는 정보 모음이다. 관계는 이러한 테이블 간의 상호작용을 기반으로 설정되는 여러 테이블 간의 논리적 연결이다.(출처: 구글클라우드)
    정규화를 통해 중복을 최소화하고 트랜잭션을 수행하는 것이 쉽다.

SQL(Structured Query Language)

Structured Query Language
데이터베이스에서 데이터를 저장하거나 얻기 위해서 사용하는 표준화된 언어.
DMBS에 데이터를 구축, 관리하고 활용하기 위해서 사용되는 언어.
사람과 DBMS가 소통하기 위한 언어.

  1. DDL(Data Definition Language)
    데이터베이스나 테이블 등을 생성, 삭제하거나 그 구조를 변경하기 위한 명령어
    CREATE, ALTER, DROP

  2. DML(Data Manipulation Language)
    데이터베이스에 저장된 데이터를 처리하거나 조회, 검색하기 위한 명령어
    INSERT, UPDATE, DELETE, SELECT 등

  3. DCL(Data Control Language)
    데이터베이스에 저장된 데이터를 관리하기 위하여 데이터의 보안성 및 무결성 등을 제어하기 위한 명령어
    GRANT, REVOKE 등

관계형 데이터베이스

현재 가장 많이 사용되고 있는 데이터베이스의 한 종류.
테이블(table)로 이루어져 있으며, 이 테이블은 키(key)와 값(value)의 관계를 나타낸다.
데이터의 종속성을 관계(relationship)로 표현하는 것이 관계형 데이터베이스의 특징.

특징

  1. 데이터의 분류, 정렬, 탐색 속도가 빠름.
  2. 신뢰성이 높고, 데이터의 무결성 보장해줌.
  3. 기존에 작성된 스키마를 수정하기가 어려움.
  4. 데이터베이스의 부하를 분석하는 것이 어려움.

용어

  • 데이터(data): 하나하나의 단편적인 정보
  • 테이블(table): 회원이나 제품의 데이터를 입력하기 위해 표 형태로 표현한 것
  • 데이터베이스(database, DB): 테이블이 저장되는 저장소
  • DBMS(Database Management System): 데이터베이스 관리 시스템 또는 소프트웨어
  • 열(column): 테이블의 세로. 유일한 이름 가지고 있으며, 자신만의 타입을 가지고 있음. 필드(field), 속성(attribute)이라고 불림.
  • 행(row): 실질적인 진짜 데이터. 한 테이블의 모든 행은 같은 수의 열을 가지고 있음. 튜플(tuple), 레코드(record)라고도 불림. 행의 개수=데이터의 개수
  • 값(value): 각각의 행과 열에 대응하는 값을 가지고 있다. 값은 열의 타입에 맞는 값이어야 함.
  • 키(key): 키(key) 또는 기본 키(primary key).
    테이블에는 열이 여러 개 있지만 기본 키는 1개만 지정해야 함.
    기본 키는 열에 지정하며, 각 행을 구분하는 유일한 값.
  • 스키마(schema): MySQL 안의 데이터베이스.
    테이블의 각 열에 대한 항목과 타입뿐만 아니라 기본 키와 외래 키도 나타내야 함.
    테이블에 적재될 데이터의 구조와 형식을 정의하는 것.
  • 문자형 / Character / CHAR: 문자를 입력할 데이터 형식
  • 정수형 / Integer / INT: 정수를 입력할 데이터 형식
  • 널 / Null: 비어 있는 값
  • 널 허용 안함 / Not Null / NN: 빈 값을 허용하지 않음

키

키(key)는 수많은 데이터를 구별할 수 있는 유일한 값. 하나의 테이블을 구성하는 여러 열 중에서 특별한 의미를 지닌 하나 또는 여러 열의 조합을 의미.

  • 기본키(primary key)
    여러 키 중에서 가장 중요한 키.
    한 테이블 내에서 중복되지 않는 값만 가질 수 있는 키.
    속성
    1. 테이블에 저장된 행을 식별할 수 있는 유일한 값이어야 한다.
    2. 값의 중복이 없어야 한다.
    3. NULL 값을 가질 수 없다.

  • 보조키
    대체키(alternate key)라고도 부르며 후보키(candidate key)에 속해 있는 키.
    후보키 중에서 기본키로 지정되지 않은 열이다.

  • 외래키(Foreign key)
    특정 테이블에 포함되어 있으면서 다른 테이블의 기본키로 지정된 키
    데이터의 중복을 피하기 위해 테이블 사이의 관계를 규명하기 위한 필수 요소.

  • 복합키(composite key)
    여러 열을 조합하여 기본키 역할을 할 수 있게 만든 키

오라클 데이터베이스

자료형

  • 숫자 데이터 NUMBER
  • 문자열 데이터 VARCHAR2
  • 날짜 데이터 DATE

객체

SQL 문법

  • 생성

CREATE DATABASE ｀데이터베이스명｀ CHARACTER SET utf8 COLLATE utf8_general_ci;

  • 삭제

DROP DATABASE ｀데이터베이스명｀;

  • 열람

SHOW DATABASE;

  • 선택

USE ｀데이터베이스명｀

use 문은 데이터를 선택하는 구문으로 한 번 지정하면 계속 유지된다.

Table

데이터가 실질적으로 저장되는 저장소

  • 테이블 리스트
SHOW tables;
  • 테이블 스키마 열람
DESC ｀테이블명｀
  • 테이블 제거
DROP TABLE ｀테이블명｀

입력 INSERT

테이블에 데이터를 입력

INSERT INTO table_name VALUES (value1, value2, value3, ...)
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)

변경 UPDATE

데이터를 수정

UPDATE 테이블명 SET 컬럼1=컬럼1의 값, 컬럼2=컬럼2의 값 WHERE 대상이 될 컬럼명=컬럼의 값

삭제 DELETE, TRUNCATE, DROP

  • DELETE

    행단위로 데이터를 삭제
DELETE FROM 테이블명 [WHERE 삭제하려는 칼럼 명=값]

  • TRUNCATE

    • 테이블의 전체 데이터를 삭제
    • 테이블에 외부키(foreign key)가 없다면 DELETE보다 훨씬 빠르게 삭제됨
TRUNCATE 테이블명;

  • DROP TABLE

    테이블을 삭제
DROP TABLE 테이블명;

조건 WHERE

SELECT 문에서 특정 조건을 조회할 때 사용하는 구문

조회 SELECT

테이블에서 데이터를 조회
가장 기본 형식은 SELECT~FROM~WHERE
SELECT 열이름 FROM 테이블이름 WHERE 조건식;

SELECT 칼럼명1, 칼럼명2
	[FROM 테이블명]
    [GROUP BY 칼럼명]
    [ORDER BY 칼럼명 [ASC | DESC]]
    [LIMIT offset, 조회할 행의 수]
SELECT mem_name, addr
	FROM member
    WHERE addr = '경기' OR addr = '전남' OR addr = '경남';

IN()을 사용하면 코드를 훨씬 간결하게 작성할 수 있다.

SELECT mem_name, addr
	FROM member
    WHERE addr IN('경기', '전남', '경남');

LIKE
문자열의 일부 글자를 검색하려면 LIKE를 사용한다.

SELECT *
	FROM member
    WHERE mem_name LIKE '우%';
    --제일 앞 글자가 '우'이고 그 뒤는 무엇이든(%) 허용한다는 의미.

  • 그룹핑 (group by)

    지정한 열의 데이터들은 같은 데이터끼리는 묶어서 결과를 추출함. 주로 그룹으로 묶는 경우는 합계, 평균, 개수 등을 처리할 때 사용하므로 집계 함수와 함께 사용함.
    HAVING절을 통해 조건식을 추가할 수 있다.
  • GROUP BY 절과 함께 사용되는 것이 차이점
SELECT * FROM 테이블명 GROUP BY 그룹핑 할 기준 칼럼명

  • 정렬 (order)

    지정된 칼럼을 기준으로 행을 정렬
    ORDER BY - 결과의 정렬
    결과의 값이나 개수에 대해서는 영향을 미치지 않지만, 결과가 출력되는 순서를 조절함.
SELECT * FROM 테이블명 ORDER BY 정렬의 기준으로 사용할 열 [DESC | ASC]
-- DESC 내림차순, ASC 오름차순

  • index

    색인, 조회할 때 원하는 행을 빠르게 찾을 수 있게 준비해둔 데이터
    • primary: 중복되지 않는 유일한 키
    • normal: 중복을 허용하는 인덱스
    • unique: 중복을 허용하지 않는 유일한 키
    • foreign: 다른 테이블과의 관계성을 부여하는 키
    • full text: 자연어 검색, myisam에서만 지원

권한

사용자에 따라서 접근할 수 있는 데이터와 사용할 수 있는 기능을 제한

  • GRANT

    사용자를 생성하고, 권한을 부여함
GRANT 권한 ON 데이터베이스.테이블 TO '아이디'@'호스트' IDENTIFIED BY '비밀번호'

  • SHOW GRANTS

    자신의 권한이나, 특정 사용자의 권한을 열람
SHOW GRANTS [FOR 사용자]

  • REVOKE

    사용자의 권한을 제거
REVOKE 권한 ON 데이터베이스.테이블 FROM 사용자

  • DROP USER

    사용자를 삭제
DROP USER user [, user] ...

MySQL

가장 널리 사용되고 있는 관계형 데이터베이스 관리 시스템
오픈 소스이며, 다중 사용자와 다중 스레드를 지원함.
데이터베이스 엔진을 운영하는 가장 중요한 프로그램

MySQL 문법

키워드와 구문에서 대소문자를 구분하지 않는다.

주석

1. # 한 줄 주석
2. -- 한 줄 주석
3. /* 두 줄
	  이상의
      주석 */

SELECT

  1. 테이블의 모든 내용 보기
    *(asterisk)는 테이블의 모든 컬럼을 뜻한다.
SELECT * FROM Customers;
-- 이와 같이 주석을 달 수 있습니다.
  1. 원하는 column(열)만 골라서 보기
SELECT CustomerName, ContactName, Country
FROM Customers;
  1. 원하는 조건의 row(행)만 걸러서 보기
SELECT * FROM Order 
-- 나는 Order에서 모든 것들을 가져오겠다.
WHERE Quantity < 5;
-- WHERE은 조건. WHERE을 붙여 원하는 데이터만 가져올 수 있다.
  1. 원하는 순서로 데이터 가져오기
    ASC - 오름차순
    DESC - 내림차순
SELECT * FROM OrderDetails
ORDER BY ProductID ASC, Quantity DESC;
-- ORDER BY 구문을 사용해서 특정 컬럼을 기준으로 데이터를 정렬할 수 있다.
  1. 원하는 만큼만 데이터 가져오기
    LIMIT {가져올 갯수} 또는 LIMIT {건너뛸 갯수}, {가져올 갯수}를 사용하여, 원하는 위치에서 원하는 만큼만 데이터를 가져올 수 있다.
SELECT * FROM Customers
LIMIT 30, 10;
  1. 원하는 별명(alias)으로 데이터 가져오기
    AS를 사용해서 컬럼명을 변경할 수 있다.
SELECT
  CustomerId AS ID,
  CustomerName AS NAME,
  Address AS ADDR
FROM Customers;
  • 서브 쿼리
    SELECT 안에는 또 다른 SELECT가 들어갈 수 있다.
SELECT mem_name,  height FROM member WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');

각종 연산자들

  1. 사칙연산
    연산자 더하기(+), 빼기(-), 곱하기(*), 나누기(/), 나머지(%, MOD)
SELECT 'ABC' + 3;
-- 3. 숫자열이 아닌 문자열은 0으로 인식

SELECT '1' + '002' * 3;
-- 7. 숫자로 구성된 문자열은 숫자로 자동인식

SELECT
  ProductName,
  Price,
  Price / 2 AS HalfPrice, -- 반값 할인
  Price * 2 AS DoublePrice, -- 두배 
  Price * 0.75 AS SalePrice -- 25% 세일
FROM Products;
  1. 참/거짓 관련 연산자
    MySQL에서는 TRUE는 1, FALSE는 0으로 저장됨.
SELECT !TRUE, NOT 1, !FALSE, NOT FALSE;
-- 0, 0, 1, 1

IS - 양쪽이 모두 TRUE/FALSE
IS NOT - 한쪽은 TRUE, 한쪽은 FALSE

SELECT (TRUE IS FALSE) IS NOT TRUE;
-- (FALSE)IS NOT TRUE = TRUE(1)

AND, && - 양쪽이 모두 TRUE일 때만 TRUE
OR, || - 한쪽은 TRUE면 TRUE

SELECT 2 + 3 = 6 OR 2 * 3 = 6;
-- 1

= - 양쪽 값이 같음
!=, <> - 양쪽 값이 다름
>, < - (왼쪽, 오른쪽) 값이 더 큼
>=, <= - (왼쪽, 오른쪽) 값이 같거나 더 큼

SELECT 1 = 1, !(1 <> 1), NOT (1 < 2), 1 > 0 IS NOT FALSE;
-- 1, 1, 0, 1

BETWEEN {MIN} AND {MAX} - 두 값 사이에 있음
NOT BETWEEN {MIN} AND {MAX} - 두 값 사이가 아닌 곳에 있음

SELECT 3 BETWEEN 1 AND 5;
-- 1
SELECT 'black' NOT BETWEEN 'April' AND 'cat';
-- 0

IN (...) - 괄호 안의 값들 가운데 있음
NOT IN (...) - 괄호 안의 값들 가운데 없음

SELECT 1 + 2 IN (2, 3, 4) 
-- 1

LIKE '... % ...' - 0~N개 문자를 가진 패턴
LIKE '... _ ...' - _ 갯수만큼의 문자를 가진 패턴

SELECT
  'HELLO' LIKE 'hel%',
  'HELLO' LIKE 'H%',
  'HELLO' LIKE 'H%O',
  'HELLO' LIKE '%O',
  'HELLO' LIKE '%HELLO%',
  'HELLO' LIKE '%H',
  'HELLO' LIKE 'L%'
  -- 1, 1, 1, 1, 1, 0, 0

숫자와 문자열을 다루는 함수들

ROUND - 반올림
CEIL - 올림
FLOOR - 내림

SELECT 
  ROUND(0.5),
  CEIL(0.4),
  FLOOR(0.6);
  -- 1, 1, 0

ABS - 절대값

SELECT ABS(2), ABS(-3), ABS(6 - 15);
-- 2, 3, 9

GREATEST - (괄호 안에서) 가장 큰 값
LEAST - (괄호 안에서) 가장 작은 값

SELECT 
  GREATEST(1, 2, 3),
  LEAST(1, 2, 3, 4, 5);
  -- 3, 1

MAX - 가장 큰 값
MIN - 가장 작은 값
COUNT - 갯수 (NULL값 제외)
📢 *를 인수로 할 수 있는 집계함수는 COUNT 뿐
SUM - 총합
AVG - 평균 값
POW(A, B), POWER(A, B) - A를 B만큼 제곱
SQRT - 제곱근

SELECT
  POW(2, 3),
  POWER(5, 2),
  SQRT(16);
  -- 8, 25, 4

TRUNCATE(N, n) - N을 소숫점 n자리까지 선택

SELECT
  TRUNCATE(1234.5678, 1),
  TRUNCATE(1234.5678, 2),
  TRUNCATE(1234.5678, 3),
  TRUNCATE(1234.5678, -1),
  TRUNCATE(1234.5678, -2),
  TRUNCATE(1234.5678, -3);
  -- 1234.5, 1234.56, 1234.567, 1230, 1200, 1000

UCASE, UPPER - 모두 대문자로
LCASE, LOWER - 모두 소문자로

SELECT
  UPPER('abcDEF'),
  LOWER('abcDEF');
  -- ABCDEF, abcdef

CONCAT(...) - 괄호 안의 내용 이어붙임
CONCAT_WS(S, ...) - 괄호 안의 내용 S로 이어붙임

SELECT CONCAT('HELLO', ' ', 'THIS IS ', 2023)
-- HELLO THIS IS 2023
SELECT CONCAT_WS('-', 2023, 1, 1, 'PM')
-- 2023-1-1-PM

SUBSTR, SUBSTRING - 주어진 값에 따라 문자열 자름
LEFT - 왼쪽부터 N글자
RIGHT - 오른쪽부터 N글자

SELECT
  SUBSTR('ABCDEFG', 3),
  SUBSTR('ABCDEFG', 3, 2),
  SUBSTR('ABCDEFG', -4),
  SUBSTR('ABCDEFG', -4, 2);
  -- CDEFG, CD, DEFG, DE
 
 SELECT
  LEFT('ABCDEFG', 3),
  RIGHT('ABCDEFG', 3);
  -- ABC, EFG

LENGTH - 문자열의 바이트 길이
CHAR_LENGTH, CHARACTER_LEGNTH - 문자열의 문자 길이

SELECT
  LENGTH('ABCDE'),
  CHAR_LENGTH('ABCDE'),
  CHARACTER_LENGTH('ABCDE');
  -- 5, 5, 5

TRIM - 양쪽 공백 제거
LTRIM - 왼쪽 공백 제거
RTRIM - 오른쪽 공백 제거

SELECT
  CONCAT('|', ' HOME ', '|'),
  CONCAT('|', LTRIM(' HOME '), '|'),
  CONCAT('|', RTRIM(' HOME '), '|'),
  CONCAT('|', TRIM(' HOME '), '|');
  -- | HOME |, |HOME |, | HOME|, |HOME|

LPAD(S, N, P) - S가 N글자가 될 때까지 P를 왼쪽에 이어붙임
RPAD(S, N, P) - S가 N글자가 될 때까지 P를 오른쪽에 이어붙임

SELECT
  LPAD('ABC', 5, '-'),
  RPAD('ABC', 5, '-');
  -- --ABC, ABC--

REPLACE(S, A, B) - S중 A를 B로 변경

SELECT
  REPLACE('아이스크림 중에서는 민초가 최고다.', '민초', '레인보우샤베트');
  -- 아이스크림 중에서는 레인보우샤베트가 최고다.

INSTR(S, s) - S중 s의 첫 위치 반환, 없을 시 0

SELECT
  INSTR('ABCDE', 'ABC'),
  INSTR('ABCDE', 'BCDE'),
  INSTR('ABCDE', 'C'),
  INSTR('ABCDE', 'DE'),
  INSTR('ABCDE', 'F');
  -- 1, 2, 3, 4, 0

시간/날짜 관련 함수들

CURRENT_DATE, CURDATE - 현재 날짜 반환
CURRENT_TIME, CURTIME - 현재 시간 반환
CURRENT_TIMESTAMP, NOW - 현재 시간과 날짜 반환

DATE - 문자열에 따라 날짜 생성
TIME - 문자열에 따라 시간 생성

YEAR - 주어진 DATETIME값의 년도 반환
MONTHNAME - 주어진 DATETIME값의 월(영문) 반환
MONTH - 주어진 DATETIME값의 월 반환
WEEKDAY - 주어진 DATETIME값의 요일값 반환(월요일: 0)
DAYNAME - 주어진 DATETIME값의 요일명 반환
DAYOFMONTH, DAY - 주어진 DATETIME값의 날짜(일) 반환

HOUR - 주어진 DATETIME의 시 반환
MINUTE - 주어진 DATETIME의 분 반환
SECOND - 주어진 DATETIME의 초 반환

ADDDATE, DATE_ADD - 시간/날짜 더하기
SUBDATE, DATE_SUB - 시간/날짜 빼기

DATE_DIFF - 두 시간/날짜 간 일수차
TIME_DIFF - 두 시간/날짜 간 시간차

LAST_DAY - 해당 달의 마지막 날짜

조건에 따라 그룹으로 묶기

GROUP BY - 조건에 따라 집계된 값을 가져옴.

SELECT Country FROM Customers
GROUP BY Country;

WITH ROLLUP - 전체의 집계값
📢 ORDER BY 와는 함께 사용될 수 없다.

HAVING - 그룹화된 데이터 걸러내기
📢 WHERE은 그룹하기 전 데이터, HAVING은 그룹 후 집계에 사용

DISTINCT - 중복된 값들을 제거

profile
oatraspberry
개발자가 될테야
이전 포스트

자바스크립트

다음 포스트

Vue.js

0개의 댓글