SQL - 혼자 공부하는 SQL

📒 SQL 문법

📕 1. SELECT ~ FROM ~ WHERE

• 1) Use문

  • use 데이터베이스 이름;
  • 데이터 베이스 지정
  • 한 번 지정하면 계속 유지 (MySQL 워크벤치 재시작하거나, 새 쿼리 창을 열면 USE 재지정 해야함)
  • 여러 번 수행해도 오류 발생X

• 2) SELECT

  • select 컬럼명
    from 테이블명
    where 조건식
    group by 컬럼명
    having 조건식
    order by 컬럼명
    limit 시작, 개수;

• 3) WHERE 절

  • 조회되는 데이터의 조건 지정
  • where 없이 조회 (테이블의 모든 행 출력)

  select *
  from 테이블명 

  • 관계 연산자, 논리 연산자 사용
  • BETWEEN ~ AND
  • IN()
  • LIKE (일부 글자 검색)
  • _ (언더바, 한 글자 검색)

• 4) ORDER BY절

  • 결과가 출력되는 순서를 조절
  • asc(오름차순), desc(내림차순)

• 5) LIMIT문

  • 출력의 개수 제한
  • select 컬럼명
    from 테이블명
    limit(시작, 개수);

• 6) DISTINCT문

  • 중복된 결과를 제거
  • select distinct 컬럼명
    from 테이블명;

• 7) GROUP BY절

  • 데이터를 그룹으로 묶어주는 역할
  • select 컬럼명, 집계 함수(컬럼)
    from 테이블명
    group by 컬럼명
  • 집계 함수
    • sum() : 합게
    • avg() : 평균
    • min() : 최소값
    • max() : 최대값
    • count() : 행의 개수
    • count(distinct) : 중복 없는 행의 개수

• 8) Having절

  • 집계 함수에 대해서 조건 제한하는 것 (group by절과 함께 사용)
  • select 컬럼명, 집계 함수(컬럼)
    from 테이블명
    group by 컬럼명
    having 집계함수 조건;

📕 2. 데이터 변경을 위한 SQL문

1) INSERT문

• insert into 테이블 (컬럼명)
  values ('값');
  • 테이블에 데이터 삽입
  • 테이블의 열 순서 및 개수와 동일해야 함 (컬럼명 생략 가능)
• AUTO_INCREMENT문
  • 1부터 증가하는 값을 입력
  • alter table 테이블명 auto_increment= 시작값;
    set @@auto_increment_increment = 증가값;
  • 해당 컬럼은 반드시 기본키로 지정

2) UPDATE문

• update 테이블
  set 열 = 값
  where 조건
  • 테이블의 데이터 수정

3) DELETE문

• 테이블의 데이터 삭제
  • delete from 테이블
    where 조건
  • drop table 테이블명 (테이블 자체 삭제)
  • truncate table 테이블명 (테이블 데이터 전체 삭제)

4) 데이터 형식

• 정수형

  • TINYINT (1 Byte)
  • SMALLINT (2 Byte)
  • INT (4 Byte)
  • BIGINT (8 Byte)
  • Out of range : 입력값의 범위를 벗어났음
  • UNSIGNED : 값의 범위가 0부터 시작

• 문자형

  • CHAR (1 ~ 255 Byte) : 고정 길이 문자형
  • VARCHAR (1 ~ 16383 Byte) : 가변 길이 문자형

• 대량의 데이터 형식

  • TEXT (1 ~ 65535 Byte)
  • LONGTEXT (1 ~ 42944967295 Byte)
  • BLOB (1 ~ 65535 Byte)
  • LONGBLOB (1 ~ 42944967295 Byte)

• 실수형

  • FLOAT (4 Byte)
  • DOUBLE (8 Byte)

• 날짜형

  • DATE (3 Byte) : 날짜 YYYY-MM-DD
  • TIME (3 Byte) : 시간 HH:MM:SS
  • DATETIME (8 Byte) : 날짜 및 시간 YYYY-MM-DD HH:MM:SS

5) 변수의 사용

• 변수 선언 및 대입

  • SET @변수 이름 = 변수의 값;

• 변수 출력

  • SELECT @변수 이름;

USE market_db;

SET @txt = '가수 이름 => ';
SET @height = 166;

SELECT @txt, mem_name FROM member WHERE height > @height;

• LIMIT에 변수 사용
  • LIMIT에는 변수를 사용할 수 없기 때문에 문법상 오류
  • PREPARE(실행X, SQL만 준비)와 EXECUTE(실행) 사용

SET @count = 3;

PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count; // USING으로 물음표(?)에 변수의 값 대입 

6) 데이터 형 변환

• 명시적인 변환
  • 직접 함수를 사용해서 변환
  • CAST (값 AS 데이터 형식 [(길이)])
  • CONVERT (값, 데이터 형식 [(길이)])

SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy; // SIGNED : 부호가 있는 정수
// OR
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy; 

SELECT CAST('2022$12$12' AS DATE); // 결과 : 2022-12-12

• 암시적인 변환
  • 별도의 지시 없이 자연스럽게 변환
  • 문자 ↔️ 숫자 변한 후에 연산

SELECT '100' + '200'; // 결과 : 300
SELECT CONCAT(100, '200'); // 결과 : 100200

• 문자열 연결
  • CONCAT(숫자->문자, 문자)

📕 3. 두 테이블을 묶는 조인

1) 내부 조인

• 두 테이블에 모두 데이터가 있어야먄 결과 출력
• 두 테이블의 조인을 위해서는 테이블이 일대다(ont to many) 관계로 연결되어야 함
  • 일대다 관걔는 한쪽 테이블에는 하나의 값만 존재하지만, 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계
  • 기본 키와 외래 키 설정 (PK-FK 관계)
• 형식
  • SELECT <열 목록>
    FROM <첫 번째 테이블>
        INNER JOIN <두 번째 테이블>
        ON <조인될 조건>
    [WHERE 검색 조건]

SELECT B.mem_id, CONCAT(M.phone1, M.phone2) '연락처'
FROM buy B
	INNER JOIN memeber M
    ON B.mem_id = M.mem_id
WHERE B.mem_id = 'GRL';

2) 외부 조인

• 한쪽 테이블에만 데이터가 있어도 결과 출력
• 종류
  • LEFT OUTER JOIN : 왼쪽 테이블 내용은 모두 출력
  • RIGHT OUTER JOIN : 오른쪽 테이블 기준으로 외부 조인
  • FULL OUTER JOIN : 한 쪽에 들어있는 내용이면 모두 출력
• 형식
  • SELECT <열 목록>
    FROM <첫 번째 테이블(LEFT 테이블)>
        <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
        ON <조인될 조건>
    [WHERE 검색 조건];

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;

3) 상호 조인

• 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능
  • 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수
• 특징
  • ON 구문을 사용X
  • 랜덤 조인하기 때문에 결과 내용은 의미 없음
  • 테스트 하기 위한 대용량 데이터 생성 용도
• 형식
  • SELECT <열 목록>
    FROM <테이블>
        CROSS JOIN <테이블>

CREATE TABLE cross_table
  SELECT *
  FROM buy
      CROSS JOIN memeber;

4) 자체 조인

• 1개의 테이블로 조인
• 형식
  • SELECT <열 목록>
    FROM <테이블> 별칭A
        INNER JOIN <테이블> 별칭B
        ON <조인될 조건>
    [WHERE 검색 조건]

// 직원 중 경리 부장의 직속상관인 관리이사의 사내 연락처를 알고 싶을 경우 EMP 열과 MANAGER 열을 조인 
SELECT A.emp "직원", B.emp "직속상관", B.phone "직속상관연락처"
FROM emp_table A
	INNER JOIN emp_table B
    ON A.manager = B.emp
WHERE A.emp = "경리부장";

📕 4. SQL 프로그래밍

1) 스토어드 프로시저

• 개념
  • MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체
  • SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만듦
• 형식
  • DELIMITER $$
    CREATE PROCEDURE 스토어드 프로시저 이름
        BEGIN
         // SQL 프로그래밍 코딩
        END $$
    DELIMITER ; // 종료 문자 변경
    CALL 스토어드 프로시저 이름; // 스토어드 프로시저 호출

2) IF 문

• 조건문
  • 조건식이 참이면 'SQL 문장' 실행하고, 그렇지 않으면 넘어감
• 형식
  • IF <조건식> THEN
         // SQL 문장들 (2문장 이상일 경우 BEGIN~END)
    END IF;

DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	DECLARE debutDate DATE; -- 데뷔 일자 
    DECLARE curDate DATE; -- 오늘 
    DECLARE days INT; -- 활동한 일수
    SELECT debut_date INTO debutDate // 결과를 변수에 저장
    	FROM market_db.memeber
        WHERE mem_id = 'APN';
        
    SET curDATE = CURRENT_DATE(); -- 현재 날짜
    SET days = DATEDIFF(curDate, debutDate); -- 날짜의 차이, 일 단위
    
    IF (days/365) >= 5 THEN
    	SELECT CONCAT('데뷔 ', days, '일');
    ELSE
    	SELECT '데뷔' + days + '일';
    END IF;
    
	IF 100 = 100 THEN -- 항상 실행
    	SELECT '100은 100과 같습니다.';
    END IF;
END $$
DELIMITER ;

CALL ifProc1();

3) CASE 문

• 조건문
  • 여러 가지 조건 중에서 선택해야 하는 경우
  • 2가지 이상의 여러 가지 경우일 때 처리가 가능한 다중 분기
• 형식
  • CASE
        WHEN 조건1 THEN
             SQL 문장
        WHEN 조건2 THEN
             SQL 문장
        ELSE
             SQL 문장
    END CASE;

SELECT M.mem_id, M.mem_name, SUM(price * amount) "총구매액"
	CASE
    	WHEN (SUM(price * amount) >= 1500) THEN "최우수고객"
        WHEN (SUM(price * amount) >= 1000) THEN "우수고객"
        WHEN (SUM(price * amount) >= 1) THEN "일반고객"
        ELSE "비회원고객"
    END "회원등급"
FROM buy B
	RIGHT OUTER JOIN member M
    ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price * amount) DESC;

4) WHILE 문

• 반복문
  • 조건식이 참인 동안에 계속 같은 내용을 반복
  • ITERATE [레이블] : 지정한 레이블로 가서 계속 진행
  • LEAVE [레이블] : 지정한 레이블을 빠져 나감 (WHILE문 종료)
• 형식
  • WHILE <조건식> DO
        SQL 문장
    END WHILE;

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;
    
    myWhile: -- WHILE문을 myWhile 이라는 레이블로 지정함
    WHILE (i <= 100) DO
    	IF (i%4 == 0) THEN
        	SET i = i + 1; -- i의 원래 값에 1를 더해서 다시 i에 넣으라는 의미
            ITERATE myWhile; -- 지정한 label 문으로 가서 계속 진행
        END IF;
        
    	SET hap = hap + i;
    
    	IF (hap > 1000) THEN
    		LEAVE myWhile; -- 지정한 label 문을 떠남 (즉, while문 종료)
   	 	END IF;
    END WHILE;
    
    SELECT '1부터 100까지의 합 ==> ', hap;
 END $$
 DELIMITER;
 CALL whileProc();

5) 동적 SQL

• 개념
  • 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용
  • 미리 SQL을 준비한 후에 실시간으로 필요한 값을 전달해서 나중에 동적으로 SQL 실행
  • PREPARE 문에서 ?로 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING으로 ?에 값을 전달
• PREPARE
  • SQL 문을 실행하지는 않고 미리 준비
  • 실행 후에 DEALLOCATE PREPARE로 문장 해제 필요
• EXECUTE
  • 준비한 SQL문을 실행

CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

📒 제약조건과 뷰

📕 1. 제약조건

1) 제약조건

• 개념
  • 테이블을 만들 때 테이블의 구조에 필요한 제약조건 설정
  • 데이터의 무결성을 지키기 위해 제한하는 조건
  • 🗒️ 예시 : 기본 키, 외래 키
• 종류
  • PRIMARY KEY 제약조건
  • FOREIGN KEY 제약조건
  • UNIQUE 제약조건
  • CHECK 제약조건
  • DEFAULT 정의
  • NULL 값 허용

2) PRIMARY KEY 제약조건

• 기본 키에 입력되는 값은 중복X + NULL값 입력X

• 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성됨

• 테이블은 기본 키를 1개만 가질 수 있음

• CREATE TABLE에서 설정하는 기본 키 제약조건

CREATE TABLE member
( mem_id	CHAR(8) NOT NULL PRIMARY KEY, -- 기본 키 설정
  mem_name	VARCHAR(10) NOT NULL,
  height	TINYINT UNSIGNED NULL
);

// 또는

CREATE TABLE member
( mem_id	CHAR(8) NOT NULL,
  mem_name	VARCHAR(10) NOT NULL,
  height	TINYINT UNSIGNED NULL,
  PRIMARY KEY (mem_id) -- 기본 키 설정
  
  // 기본 키 이름 설정
  CONSTRAINT PRIMARY KEY pk_mem_id (mem_id)
);

• ALTER TABLE에서 설정하는 기본 키 제약조건

ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY (mem_id);

3) FOREIGN KEY 제약조건

• 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할
  • 외래 키가 설정된 열은 다른 테이블의 기본 키와 연결
  • 기준 테이블(기본 키 테이블)과 참조 테이블(외래 키 테이블)로 구성
    ㅛ

4) UNIQUE 제약조건

5) CHECK 제약조건

)

)

📕 2. 뷰 (가상의 테이블)

)

)

)

📖 참고 📖

• ✏️

• 🗒️ 예시

1️⃣2️⃣3️⃣4️⃣5️⃣ ➡️⬇️⬆️

📒 인덱스

📕 1. 인덱스

📕 2. 인덱스 내부 작동

📕 3. 인덱스 실제 사용

📖 참고 📖

• ✏️

• 🗒️ 예시

1️⃣2️⃣3️⃣4️⃣5️⃣ ➡️⬇️⬆️

📒 스토어드 프로시저

📕 1. 스토어드 프로시저 사용 방법

📕 2. 스토어드 함수와 커서

📕 3. 자동 실행되는 트리거

📖 참고 📖

• ✏️

• 🗒️ 예시

1️⃣2️⃣3️⃣4️⃣5️⃣ ➡️⬇️⬆️

📒 SQL과 파이썬 연결

📕 1. 파이썬 개발 환경 준비

📕 2. 파이썬과 MySQL 연동

📕 3. GUI 응용 프로그램

📖 참고 📖

• ✏️

• 🗒️ 예시

1️⃣2️⃣3️⃣4️⃣5️⃣ ➡️⬇️⬆️