[SQL] #1

도서 <혼자 공부하는 SQL> 참고

 

1장) 데이터베이스와 SQL

• 데이터베이스란 '데이터의 집합' <-- 관리하고 운영하는 소프트웨어를 DBMS
• SQL은 데이터베이스를 구축, 관리하고 활용하기 위해서 사용되는 언어
• MySQL
• 데이터베이스 모델링

 

2장) SQL 기본문법

• SELECT ~ FROM ~ WHERE

FROM 테이블_이름

WHERE 조건식

GROUP BY 열_이름

HAVING 조건식

ORDER BY 열_이름

LIMIT 숫자

• ORDER BY: 결과의 정렬을 위함
• LIMIT: 결과의 개수를 제한함
• DISTINCT: 중복된 데이터를 제거함
• GROUP BY: 지정한 열의 데이터들을 같은 데이터끼리 묶어서 결과를 추출함(합계, 평균, 개수 등)
• HAVING: GROUP BY절과 함께 사용(WHERE과 동일)

• INSERT INTO ~ VALUES ()

SET @@auto_increment_increment=3; -> 시스템 변수: MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수

• 다른 테이블의 데이터를 한번에 입력하는 INSERT INTO ~ SELECT

• UPDATE SET WHERE~
• DELETE FROM WHERE~

 

3장) SQL 고급문법

• 데이터 형식

-> CHAR는 글자의 개수가 고정된 경우, VARCHAR는 글자의 개수가 변동될 경우에 사용

• 대량의 데이터 형식: 문자형인 CHAR는 최대 255자까지, VARCHAR느느 최대 1683까지 지정이 가능함
• 실수형: 소수점이 있는 숫자 저장시 사용
• 날짜형: DATE, TIME, DATETIME

• 변수의 사용: 임시 사용

SELECT @변수이름;

• PREPARE는 실행하지 않고 SQL문만 준비해 놓고 EXECUTE에서 실행하는 방식

• 데이터 형 변환
• 두 테이블을 묶는 조인

FROM <첫 번째 테이블>

INNER JOIN <두 번째 테이블>

ON <조인될 조건>

[WHERE 검색 조건]

Ex)

SELECT *

FROM buy

INNER JOIN MEMBER

        ON buy.mem_id = member.mem_id

WHERE buy.mem_id='GRL';

 

* 코드가 너무 길어져 복잡해 보일 때는 테이블의 이름 뒤에 별칭을 줄 수 있음

• 외부 조인: 양쪽 중에 한곳이라도 내용이 있을 때 조인되는 방식

FROM <첫 번째 테이블(LEFT 테이블)>

<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>

ON <조인될 조건>

[WHERE 검색 조건];

EX)

SELECT M.MEM_ID, M.MEM_NAME, B.PROD_NAME, M.ADDR

FROM MEMBER M

LEFT OUTER join BUY B -> 왼쪽 테이블의 내용은 모두 출력되어야 함

        ON M.MEM_ID =B.MEM_ID

order by M.MEM_ID;

• 기타 조인:

EX) SELECT * FROM BUY CROSS JOIN MEMBER;

• ON 구문 불가능
• 결과의 내용은 의미가 없음 랜덤으로 조인하기 때문
• 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성시

• 자체 조인: 자신이 자신과 조인한다는 의미

FROM <테이블> 별칭A

INNER JOIN <테이블> 별칭B

ON <조인될 조건>

[WHERE 검색 조건]

 

• SQL 프로그래밍

CREATE PROCEDURE -> 스토어드_프로시저_이름()

BEGIN

-> 이 부분에 SQL 프로그래밍

END $$

DELIMITER;

CALL

• IF문

SQL문장들

END IF;

• 두 문장 이상이 처리되어야 할 때는 BEGIN~END로 묶어줘야 함
• IF ~ ELSE문

• CASE문

WHEN 조건1 THEN

SQL문장들1

WHEN 조건2 THEN

SQL문장들2

ELSE

SQL문장들3

END CASE;

• WHILE문

SQL문장들

END WHILE;

• ITERATE [레이블]: 지정한 레이블로 가서 계속 진행
• LEAVE [레이블]: 지정한 레이블을 빠져나감 즉 WHILE문 종료

• 동적 SQL

 

4장) 테이블과 뷰

• 테이블

Ex) FOREIGN KEY(mem_id) REFERENCES member(mem_id) --> 꼭 member 테이블의 mem_id에도 값이 있어야 한다는 것

• 기본 키, 외래 키의 제약조건 설정 필요

EX) ALTER TABLE member

ADD CONSTRAINT

CHECK (phone IN ('02', '031', '032'));

• DEFAULT 정의

EX) ALTER TABLE member

ALTER COLUMN phone1 SET DEFAULT '02'; <-- 열에 DEFAULT 지정

• NULL 값 허용

• 뷰

AS

SELECT 문;

• SELECT 열_이름 FROM 뷰_이름

[WHERE 조건];

• 뷰는 기본적으로 읽기 전용으로 사용되지만 뷰를 통해서 원본 테이블의 데이터 수정 가능
• 뷰를 사용하는 이유
• 뷰의 실제 작동

단, 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱(`)으로 묶어줘야 함

• 뷰의 수정은 ALTER VIEW 구문 사용하고 열 이름에 한글 사용 가능
• 뷰의 삭제는 DROP VIEW 구문 사용
• 뷰의 정보 확인은 DESCRIBE 구문 사용
• 뷰를 생성할 때 CREATE VIEW는 기존에 뷰가 있으면 오류가 발생하지만, CREATE OR REPLACE VIEW는 기존에 뷰가 있어도 덮어쓰는 효과를 내기 때문에 오류 발생X
• 뷰의 소스 코드 확인은 SHOW CREATE VIEW 구문 사용
• WITH CHECK OPTION을 통해 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 지정

EX) ALTER VIEW v_height167

       AS

SELECT * FROM member WHERE height >=167

WITH CHECK OPTION;

INSERT INTO v_hight167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');

• 뷰가 조회되지 않으면 CHECK TABLE 문으로 뷰의 상태 확인 가능

--> 뷰가 참조하는 테이블이 없어서 오류가 발생하는 것

 

5장) 인덱스

• 인덱스는 SELECT를 사용해서 테이블을 조회할 때 결과를 빠르게 추출하도록 도와주는 기능
• 하나의 열에 여러 개의 인덱스를 생성할 수도 있고, 여러 개의 열을 묶어서 하나의 인덱스 생성 가능하지만 드문 경우
• 인덱스는 균형 트리의 자료구조로 구성됨
• SHOW INDEX 문을 사용하면 인덱스 정보가 확인됨
• WHERE 절의 열에 연산을 하면 인덱스를 사용하지 않음
• CREATE INDEX 문을 사용하면 인덱스 생성 --> 보조 인덱스

ON 테이블_이름 (열_이름) [ASC | DESC]

• DROP INDEX 문을 사용하면 인덱스 제거

ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스 제거 가능

• 클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋음

• 클러스터형 인덱스
• 보조 인덱스
• 고유 인덱스
• 인덱스를 효과적으로 사용하는 방법

 

6장) 스토어드 프로시저

• 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과 가능
• DELIMITER $$

CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 매개변수 )

BEGIN

SQL 프로그래밍 코드

END $$

DEELIMITER;

• CREATE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만, DROP에서는 괄호를 붙이지 않음
• 입력 매개변수는 스토어드 프로시저에 값을 전달함
• 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려받음
• 동적 SQL은 다이나믹하게 SQL을 생성한 후 실행
• CALL 문을 통해서 스토어드 프로시저를 호출
• 스토어드 함수

CREATE FUNCTION 스토어드_함수_이름(매개변수)

RETURNS 반환형식

BEGIN

이 부분에 프로그래밍 코딩

RETURN 반환값;

END $$

DELIMITER;

SELECT 스토어드_함수_이름();

• 매개변수는 모두 입력 매개변수 IN 사용x
• 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용
• 스토어드 함수를 사용하기 위해서 먼저 권한 허용 필요

• 스토어드 커서
• 트리거

CREATE TRIGGER myTrigger

AFTER DELETE -- DELETE 문이 발생된 후에 트리거를 작동하라는 의미

ON trigger_tablee

FOR EACH ROW

BEGIN

SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 실행 시 작동되는 코드들

END $$

DELIMITER;

• 트리거는 행 데이터가 삭제 또는 수정되면 기존 데이터를 백업 테이블에 저장하도록 할 수 있음
• 트리거에서 기존 데이터는 OLD 테이블에, 새로운 데이터는 NEW 테이블에 잠깐 저장됨

 

7장) SQL과 파이썬 연결

• 파이썬과 pymysql 라이브러리를 설치한 후에는 MySQL과 연동하는 데이터베이스 연동 프로그램 작성 가능
• MySQL 연결하기

Conn = pymysql.connect(host=서버IP주소, user=사용자, password=암호, db=데이터베이스, charset=utf8) -> 연결자를 생성하고 연결 통로인 커서를 통해 파이썬에서 SQL로 전송

• 커서 생성하기
• 테이블 만들기
• 데이터 입력하기
• 입력한 데이터 저장하기
• MySQL 연결 종료하기
• 파이썬에서 SELECT 문으로 데이터를 조회한 후에는 fetchone() 함수를 통해서 데이터를 한 행씩 가져옴
• GUI 응용 프로그램