혼자 공부하는 SQL-4장-SQL고급 문법

데이터 형식

정수형 / 문자형 으로 있으며

 

정수형 : 소수점이 없는 숫자이며 INT, TINYINT, TINYINT UNSIGNED (UNSIGNED를 붙이면 범위가 0부터 지정된다)

문자형 : 고정형 문자형 CHAR, 가변형 문자형 VARCHAR

 

대량의 데이터 형식 : TEXT형식(TEXT, LONGTEXT)

 - LONGTEXT(대량의 TEXT), LONGBLOB(대량의 데이터)로 설정하면 최대4GB까지 입력할 수 있다.

실수형 : 소수점이 있는 숫자 FLOAT(소수점 아래 7자리까지 표현), DOUBLE(소수점 아래 15자리까지 표현)

날짜형 : 날짜 및 시간을 저장할때 DATE(날짜만 저장), TIME(시간만 저장), DATETIME(날짜 및 시간을 저장)

 

변수의 사용 : MySQL 에서 제공되는 변수 앞에는 @를 붙인다.

변수적용 예

 

 

변수끼리 연산한 후에 출력

 

prepare는 'select ~~limit ?'문을 실행하지 않고 mySQL이라는 이름으로 준비만 해놓는다.

 

데이터 형 변환   :  형 변환 함수에는 CAST(), CONVERT()

문자형을 정수형으로 바꾸거나, 정수형을 문자형으로 바꾸는것을 데이터의 형 변환이라 한다.

직접 함수를 사용하여 변환하는 명시적인 변환과 별도 지시없이 자연스럽게 변환되는 암시적인 변환이 있다.

SELECT SAFE_CAST(rating AS FLOAT64) AS number_rating

-- rating컬럼내용중 소수점포함 숫자로 변환 SAFE_CAST는 변환이 실패할 경우, 오류를 발생시키지 않고 NULL을 반환 

평균가격을 정수로 표현하기 위한 데이터 형 변환

정수를 문자로 표현

암시적인 변환

문자와 문자

숫자와 문자인경우 문자가 숫자200으로 자동 변환되어 300 출력

정수와 문자

concat()함수는 문자로 변환하여 출력한다.

 

두 테이블을 묶는 조인(join)

조인(join)은 두 테이블을 엮에서 정보를 추출하는 것으로

내부 조인(inner join), 외부 조인(outer join), 기타 조인{상호조인(cross join), 자체 조인(self join)} 이 있습니다.

 

내부 조인(inner join)

기본 키(Primary Key) -(연결) - 외래 키(Foreign Key) 

 

예를 들면 아래 처럼 기본 키 주민등록번호 일에 대해 외래 키 도서 대출 기록에 주민등록번호는 여러개가 될 수 있습니다.

 

• 기본 키는 주민등록번호
  • 각 사람(행)을 고유하게 식별합니다.
  • 주민등록번호는 중복될 수 없고, 항상 고유합니다.
• 외래 키는 도서 대출 기록에 적힌 주민등록번호와 같습니다.
  • 대출 기록은 누구의 기록인지 알기 위해 주민등록번호를 참조합니다.
  • 이 번호는 주민등록 데이터베이스에 반드시 존재해야 합니다.

buy테이블과 member테이블을 조인한 결과

 

 

select 문에서 mem_id가 어느 테이블인지 명확하게 하기위해 buy테이블이라고 명확하게 해줘야된다.

별칭(alias)을 사용하여 buy 는 B, member은 M으로 간결하게 표현할 수 있다.

 

내부조인은 두 테이블에 모두 있는 내용만 출력된다.

구매한 기록이 있는 회원만 출력할때는 DISTINCT문을 사용.

 

외부 조인(outer join)  한쪽에만 데이터가 있어도 결과가 나옴.

LEFT 는 왼쪽 회원테이블 member M을 모두 출력해줌.

 

B.prod_name is null 구매이력이 없는 회원

 

 

기타 조인

상호 조인 : 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때 사용됨. 

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능(전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.)

 

자체 조인 : 자신이 자신과 조인한다. 따라서 1개의 테이블을 사용한다.

 

스토어드 프로시저 : MySQL에서 프로그래밍 기능이 필요할 때 사용되는 데이터베이스 개체

DELIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출한다.

 

 

, 로 구분하여 case ~ end문을 추가 하여 회원등급 열 추가

 

DECLARE : 는 SQL에서 변수, 커서, 조건 처리 핸들러 등 선언에 사용되는 명령어. 주로 프로시저, 함수, 트리거, 블록 단위의 스크립트에서 사용되며, SQL의 일반적인 쿼리문에서는 사용할 수 없다.

 

• 변수 선언:
  • 변수를 선언하고, 데이터를 저장하거나 조작할 수 있다.
  • 주로 저장 프로시저, 함수 내에서 사용된다.
  • 예) DECLARE i INT; -- 1에서 100까지 증가할 변수
• 커서 선언:
  • 커서를 선언하여 여러 행을 반복적으로 처리할 수 있다.
• 핸들러 선언:
  • 예외 처리를 위해 조건 핸들러를 선언한다.

while 문 조건식이 참인 동안에 반복한다. iterate는 지정한 레이블로 가서 계속 진행, leave는 지정한 레이블을 빠져나간다.

 

동적 SQL

prepare 와 execute로 동적 SQL문 만들기