이것이 MySQL 이다.
관계형 데이터베이스 (RDBMS)
테이블을 부르는 다른 용어로는 relation, entity 등이 있다.
테이블 단위의 효율적인 저장 구조로 여러 개의 테이블로 나누어서 저장함으로써
불필요한 공간의 낭비를 줄이고 효율성을 보장해준다.
이렇게 나뉜 테이블의 관계를 기본 키(primary key), 외래 키(foreign key)를 사용해서 맺어 줌으로써,
두 테이블을 부모와 자식 관계로 묶어줄 수 있다.
추후에 부모자식 테이블을 조합해서 결과를 얻고자 할 경우 join 기능을 이용하면 된다.
RDBMS의 가장 큰 단점은 시스템 자원을 많이 차지해서 시스템이 전반적으로 느려지는 것이다.
최근 들어서 하드웨어의 급속한 발전으로 인해 이러한 단점은 많이 보완되고 있다.
SQL은 국제 표준화 기관에서 표준화된 내용을 계속 발표해왔는데, 다음과 같은 특징을 갖는다.
SQL은 국제 표준화기관에서 표준화된 내용을 계속 발표해왔다.
DBMS 회사들은 이 표준 SQL에 맞춰 DBMS를 개발하므로 SQL은 제작회사와 독립적이다.
하지만 모든 회사가 표준 SQL을 정확히 따르진 않는다.
다양한 DBMS에서 상호 호환성이 뛰어나므로 이식성이 좋다.
SQL 표준은 계속 개선되며 컴파일 없이 결과를 바로 얻을 수 있다.
분산형 구조인 클라이언트/서버 구조를 지원한다.
기본 키는 행을 구분하는, 테이블 내 단 하나만 존재하는 개념이다.
서로 중복되지 않고 완전히 구분되는 데이터열을 보통 기본 키로 지정한다.
기본키라는 식별자로 외래키를 다른 테이블에 지정해주면
외래키의 데이터만으로 기본키가 속한 레코드의 다른 데이터와 관계를 맺을 수 있고
이러한 관계를 테이블의 1대다 관계라고 말할 수 있다.
기본키의 데이터가 아닌 경우 외래 키에서 사용할 수 없고 기본 키를 삭제하기 위해서
외래 키가 속한 레코드부터 삭제해야한다.
모델을 활용하여 다이어그램으로 표현할 수 있고
기존에 있던 스키마도 REVERSE하여 다이어그램으로 즉시 표현할 수 있다.
스키마 목록에서 원하는 테이블 우클릭 후
[Send to SQL Editor] » [Create Statement]를
선택하면 현재 쿼리 창에 선택한 테이블을 생성하는 SQL 구문이 나온다.
Duration SQL문이 실행되는데 걸린 시간,
Fetch 데이터를 테이블에서 가져오는데 걸린 시간
=ANY, IN, =SOME은 동일한 효과를 낸다.
집계 함수는 주로 GROUP BY절과 함께 쓰이며 데이터를 그룹화해준다.
DML (Data Manipulation Language) 데이터 조작 언어
DML 구문이 사용되는 대상은 테이블의 행이다. 테이블 정의가 필수.
SELECT, INSERT, UPDATE, DELETE
Transaction: 테이블의 데이터를 변경할 때
실제 테이블에 완전히 적용하지 않고 임시로 적용. DML 소속
INSERT [INTO] 테이블[(열1, 열2, . . .)] VALUES (값1, 값2 . . .)
AUTO_INCREMENT 적용된 일반 키 필드의 경우 NULL 값을 넣으면 자기 알아서 숫자 넣음.
ALTER TABLE 테이블 AUTO_INCREMENT=N; « N번째부터 숫자가 입력됨.
증가값 자체를 늘리려면 SET @@auto_increment_increment=N;
SELECT LAST_INSERT_ID(); 쿼리를 실행하면 마지막의 숫자를 출력.
대량 입력 INSERT INTO 테이블 (열1, 열2 . . .) SELECT문;
다른 테이블의 데이터를 가져와서 입력할 수 있음
CREATE TABLE 테이블 (SELECT문)으로 코드 축약 가능
DELETE는 행 삭제를 뜻하고 이 역시 WHERE문 생략시 전체 데이터가 삭제됨.
대용량 테이블을 삭제하는 경우 DELETE, DROP, TRUNCATE를 사용할 수 있는데
DELETE는 DML 언어이기에 트랜잭션이 적용되어 오래 걸림.
테이블의 구조를 남겨두고 싶다면 TRUNCATE, 아니면 DROP
WITH 절은 CTE(Common Table Expression)를 표현하기 위한 구문 MySQL(8버전부터)
CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있다.
비재귀적, 재귀적 CTE가 존재하고 교재에선 비재귀적 CTE에 대해서 학습한다.
DDL (Data Definition Language) 데이터 정의 언어
데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할을 한다.
자주 사용하는 구문은 CREATE, DROP, ALTER 등이 있다. Transaction 발생시키지 않음.
DCL (Data Control Language) 데이터 제어 언어
사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문
GRANT, REVOKE, DENY 등이 있다.
CHAR, VARCHAR 모두 UTF-8 형식을 지원하고 있지만 내부 할당 크기는 신경쓰지 않아도 된다.
MySQL 은 많은 내장 함수를 포함하고 있다.
크게 제어 흐름, 문자열, 수학, 날짜/시간, 전체 텍스트 검색, 형 변환, XML, 비트, 보안/압축,
정보, 공간 분석, 기타 함수 등으로 나눌 수 있다.
전체 함수의 개수는 수백 개가 넘는다.
SQL 고급 부턴 쓸 일이 없다.
CREATE TABLE 스키마, 테이블(필드 타입 속성 필드명, . . ., PRIMARY KEY(필드, 필드)) COMMENT = 메시지
CREATE TABLE 테이블 ( SELECT 필드 FROM 테이블 )
ALTER TABLE 테이블 (RENAME, MODIFY,CHANGE) 테이블명,타입,필드명과 속성 변경
ALTER TABLE 테이블 ADD [CONSTRAINT] FOREIGN KEY(필드) REFERENCES 테이블(필드)
ALTER TABLE 테이블 ALTER COLUMN 필드 SET DEFAULT 데이터
INSERT INTO 테이블 VALUES(데이터,데이터),( . . . ),( . . . )
INSERT INTO 테이블 SELECT 필드,필드 FROM 테이블
UPDATE 테이블 SET 필드 = 데이터, 필드 = 데이터
DELETE FROM 테이블 WHERE 필드 = 데이터
집계 함수는 GROUP과 함께 사용되고 집계 함수의 값에 대한 조건을 두고 싶다면 HAVING까지
SELECT JSON_OBJECT(필드, 데이터, 필드, 데이터) FROM 테이블 WHERE 조건문
SELECT 필드 FROM 테이블 JOIN 테이블 ON 테이블.KEY = 테이블.KEY – 꼭 KEY 여야 하나?
ON은 WHERE처럼 필터기능
OUTER JOIN을 할때 ON/WHERE 어디에 조건을 걸어야될지 헷갈릴때가 있다.
당연히 A와 B의 조인 KEY값은 ON에 들어가야된다.
그리고 ON 절에서는 우측(B)의 추가 제약 조건이 들어가면 되고
좌측(A)의 추가 제약조건은 WHERE절에 들어가면 된다.
특정 테이블 내의 정보만 필요하다면 JOIN할 필요 없다.
COMMIT과 ROLLBACK은 전체 스키마에 적용된다.
CONSTRAINT 는 키 이름 지정해줄 때 같이 적는 것 같다.
두 개의 열을 함께 기본 키로 지정할 수 있다.
기본 키가 적용되면 인덱스 지정도 같이 됨
뷰는 기존 테이블의 SELECT 문에 나온 결과에 대한 포인터를 갖는 테이블이다.
뷰의 데이터를 수정하면 참조 테이블의 데이터도 변경된다.
집계 함수, UNION ALL, JOIN, DISTINCT, GROUP BY로 나타난 데이터 수정불가
대용량 테이블은 별도의 테이블스페이스에 따로 저장하는 것이 성능에 효과적이다.
클러스터형 인덱스 = 기본 키 형 인덱스 = UNIQUE NOT NULL
unique = 보조 인덱스
인덱스는 중복된 값을 막기 때문에 현재 중복된 상태가 아니라해서 막 넣으면 안된다.
CREATE INDEX 인덱스이름 ON 테이블 (필드)
SHOW TABLE STATUS LIKE ‘테이블’
SHOW INDEX FROM 테이블
ALTER TABLE 테이블 DROP PRIMARY KEY
Data_length : 데이터 페이지 또는 클러스터형 인덱스의 크기
Index_length : 보조 인덱스의 크기
MySQL은 한 페이지(Heap)가 16KB 이다.
보조 인덱스를 사용하면 Index_length가 늘어남 = 페이지가 늘어남 = DB 용량이 늘어남
보조 인덱스 중에서 전체 데이터의 대략 15% 이상을 스캔하는 경우 인덱스를 사용하지 않고 스캔한다.
WHERE 문에 인덱스를 사용하도록 유도했지만 연산이 추가되면 사용하지 못할 수가 있다.
연산이 필요하다면 우변으로 넘겨서 해준다.
인덱스는 WHERE를 통해 활용하고, 자주 사용해야 가치있다.
특히 클러스터형 인덱스 사용 중에는 INSERT 작업 성능이 크게 하락한다.
페이지 분할과 동시에 전체 데이터의 이동이 발생할 수 있기 떄문.
데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.
오히려 인덱스 관리 비용만 늘어날 수 있음.
왜래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.
JOIN에 자주 사용되는 열에 인덱스를 생성해주는 거이 좋다.
인덱스는 단지 읽기 성능만 향상시키며 데이터 변경에서는 큰 부담을 준다.
회원 ID열을 클러스터형 인덱스로 지정한 상황이라면
아이디란 사용자에 따라 무작위로 등록되는 것이므로 페이지 분할, 데이터 분산이 동시에 일어나므로
예를 들어 할인 행사가 시작했을때 단기간 많은 회원가입이 이루어져 사이트가 마비될 수 있다.
스토어드 프로그램에는 스토어드 프로시저, 스토어드 함수, 트리거, 커서 등이 있다.
예외 처리는 프로시저에서 많이 쓴다..
테이블 이름을 파마리터로 사용할 수 없다.
동적 SQP(PREPARE)와 CONCATE을 활용하면 파라미터로 테이블 이름을 넘길 수 있다.
스토어드 프로시저의 장점
유지보수, 모듈식 프로그래밍, 보안 강화, 성능 향상
스토어드 함수 = 사용자 정의 함수
STORED PROCEDURE 와 FUNCTION의 차이점을 FUNCTION 시각에서 관찰하면,
IN, OUT의 구분된 매개변수를 사용할 수 없다. 오로지 IN 매개변수만 사용된다.
OUT이 없는 대신 RETURN이 존재하여 반환값을 지정할 수 있다.
내부에서 SELECT를 사용할 수 없고 오히려 SELECT 문장 안에서 호출된다.
커서
프로시저 내에서 루프문과 함께 데이터를 가져와 다룰 때 사용함
루프 탈출을 위한 예외 처리도 필요하며 데이터를 가져오는 행위를 FETCH라고 정의
트리거
트리거는 테이블에 부착되는 프로그램 코드라고 생각할 수 있다.
직접 실행 시킬 수 없고 이벤트 발생에 의해서만 동작된다.
DELETE 트리거는 TRUNCATE 동작시엔 적용되지 않는다.
다중 트리거: 하나의 테이블에 동일한 트리거가 여러개 부착
중첩 트리거: 하나의 트리거가 또 다른 트리거를 작동시키는 것
파티션도 나눌 수 있다 끗