[Database] SQL 활용 ②

SQL 실습

테이블 생성 - DDL

 

속성이 레벨번호, 레벨이름, 레벨등록날짜로 구성되고 기본키가 권한인 회원 권한테이블을 생성하시오.

CREATE TABLE tb_member_level  (
	level_num INT(11) NOT NULL AUTO_INCREMENT
	,level_name VARCHAR(300) NOT NULL
	,level_reg_date DATE NOT NULL
	,PRIMARY KEY(level_num)
);

 

속성이 회원 아이디, 비밀번호, 이름, 권한, 이메일, 주소, 회원등록날짜로 구성되고 기본키가 회원아이디, 회원 권한은 회원권한테이블의 아이디를 참조하는 회원테이블을 생성하시오.

CREATE TABLE tb_member (
	m_id VARCHAR(200) NOT NULL
	,m_pw VARCHAR(200) NOT NULL
	,m_name VARCHAR(200) NOT NULL
	,m_level INT(11) NULL DEFAULT NULL
	,m_email VARCHAR(200) NOT NULL
	,m_addr VARCHAR(200) NOT NULL
	,m_reg_date DATE NOT NULL
	,PRIMARY KEY (m_id)
	,CONSTRAINT FK_m_level FOREIGN KEY (m_level) REFERENCES tb_member_level(level_num)
);

 

속성이 상품코드, 상품이름, 상품가격, 판매자 아이디, 상품등록날짜 구성되고 기본키가 상품코드, 판매자 아이디는 회원테이블의 아이디를 참조하는 상품테이블을 생성하시오.

CREATE TABLE tb_goods (
	g_code VARCHAR(50) NOT NULL
	,g_name VARCHAR(50) NOT NULL
	,g_price INT(11) NOT NULL
	,g_seller_id VARCHAR(50) NOT NULL
	,g_reg_date DATE NOT NULL
	,PRIMARY KEY (g_code)
	,CONSTRAINT FK_g_seller_id FOREIGN KEY (g_seller_id) REFERENCES tb_member(m_id)
);

 

속성이 주문번호, 구매자 아이디, 주문상품코드, 주문수량, 주문등록날짜로 구성되고 기본키(자동증가)가 주문번호이고, 구매자 아이디는 회원테이블의 아이디를 참조하며 주문상품코드는 상품테이블의 상품코드를 참조하는 테이블을 생성하시오.

CREATE TABLE tb_order (
	o_num INT(11) NOT NULL AUTO_INCREMENT
	,o_id VARCHAR(200) NULL DEFAULT NULL
	,o_g_code VARCHAR(200) NULL DEFAULT NULL
	,o_amount INT(11) NULL DEFAULT NULL 
	,o_reg_date DATETIME NULL DEFAULT NULL
	,PRIMARY KEY (o_num)
	,CONSTRAINT FK_o_g_coed FOREIGN KEY (o_g_code) REFERENCES tb_goods (g_code)
	,CONSTRAINT FK_o_id FOREIGN KEY (o_id) REFERENCES tb_member (m_id)
);

 

로그인 번호, 로그인 아이디, 로그인 날짜, 로그아웃 날짜 속성으로 구성되고, 기본키(자동증가)가 로그인 번호이고, 로그인 아이디는 회원테이블의 아이디를 참조하는 로그인 테이블을 생성하시오.

CREATE TABLE tb_login (
	login_num INT(11) NOT NULL AUTO_INCREMENT
	,login_id VARCHAR(200) NOT NULL 
	,login_date DATE NOT NULL
	,logout_date DATE NOT NULL
	,PRIMARY KEY (login_num)
	,CONSTRAINT FK_login_id FOREIGN KEY (login_id) REFERENCES tb_member (m_id)
);

 

코멘트를 잘 작성하자!

---

다음과 같이 회원레벨 테이블에 삽입하는 SQL 문장을 작성하시오.

 

INSERT INTO tb_member_level
(level_num, level_name, level_reg_date)
VALUES
(1, '관리자', '2020-03-17')
,(2, '판매자', '2020-03-17')
,(3, '구매자', '2020-03-17');

 

각 데이터를 각 테이블에 삽입하는 SQL 문장 작성.
외래키가 참조된 순서에 맞추어 insert문을 작성해주어야 한다.

 

---

SQL 집계함수

SELCET 구문이나 HAVING절에서 사용한다.

집계 함수
함수명	함수표기	설명
SUM	SUM()	합계를 구한다.
AVG	AVG()	평균을 구한다.
MIN	MIN()	최소값을 구한다.
MAX	MAX()	최대값을 구한다.
COUNT	COUNT()	행의 개수를 센다. (NULL무시)
COUNT DISTINCT	COUNT(DISTINCT)	행의 개수를 센다. (중복은 1개만 인정)

SELECT
	o.o_id AS '구매자 아이디'
	,sum(o.o_amount) AS '총 주문수량'
	,AVG(o.o_amount) AS '평균 주문수량'
	,ROUND(AVG(o.o_amount),1) AS '평균 주문수량'
	,COUNT(o.o_amount) AS '총 주문횟수'
	,MAX(o.o_amount) AS '최대 주문수량'
	,MIN(o.o_amount) AS '최소 주문수량'
FROM
	tb_order AS o
GROUP BY o.o_id
;

round( n , 반올림 할 소수점 자리수 )

 

실행 순서 인지하기 (tb_order AS o 선언 시점이 사용 시점 이전에 있어야 한다!)

 

---

SQL 내장함수

데이터 형식 변환 함수

-- 형변환

SELECT
		NOW() AS 'DATETIME'
		,CAST(NOW() AS DATE) AS 'DATE'
		,CAST(NOW() AS TIME) AS 'TIME';
        
        
SELECT
    CONVERT (AVG(o.o_amount), SIGNED INTEGER) AS '평균 주문 개수'
FROM
    tb_order o;
    
 --   정수 타입: 음수까지 표현할 수 있는 SIGNED 타입
 --    		  양수만을 표현할 수 있는 UNSIGNED 타입

 

문자열 연결함수

-- 문자열 연결함수

SELECT
	CONCAT(m.m_id, ' : ',m.m_name) AS '회원 아이디 : 회원 이름'
FROM 
	tb_member m;

 

암시적인 형변환

-- 문자와 문자를 더함 (정수로 변환되서 연산됨)
SELECT '100' + '200’ ;
-- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT('100', '200’); 
-- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)
SELECT CONCAT(100, '200’);

제어 흐름 함수

SELECT IF (조건식, true시 반환하는 값, false시 반환하는 값);

-- 조건이 null이 아닐 경우 조건을 반환.
SELECT IFNULL(조건, null일경우 반환하는 값);

-- 조건1과 조건2가 같을 경우 null값을 반환, 다를경우 조건1을 반환
SELECT NULLIF(조건1,조건2);

 

 

문자열 함수

-- 문자를 ascii 코드로 변환 / ascii코드를 문자로 변환
SELECT ASCII('A'), CHAR(65);

-- 공백을 제거
SELECT TRIM('   KSMART35   ') AS result;

-- 반복
SELECT REPEAT('KSMART', 3) AS result;

-- 문자열 대체
SELECT REPLACE ('KSMART35기', 'KSMART' , '한국스마트정보교육원') AS result;

-- 문자열을 거꾸로 출력
SELECT REVERSE ('KSMART35') AS result;

-- 공백을 추가, 문자열 연결
SELECT CONCAT('KSMART', SPACE(15), '35기') AS result;

 

날짜 함수

SELECT 
   ADDDATE('2020-03-18', INTERVAL 31 DAY)
  ,ADDDATE('2020-03-18', INTERVAL 1 MONTH)

  ,SUBDATE('2020-03-18', INTERVAL 31 DAY)
  ,SUBDATE('2020-03-18', INTERVAL 1 MONTH)

  ,ADDTIME('2020-03-18 23:59:59', '1:1:1')
  ,ADDTIME('15:00:00', '2:10:10')

  ,SUBTIME('2020-03-18 23:59:59', '1:1:1')
  ,SUBTIME('15:00:00', '2:10:10');

SELECT 
   YEAR(CURDATE())
  ,MONTH(CURDATE())
  ,DAYOFMONTH(CURDATE());

SELECT 
   HOUR(CURTIME())
  ,MINUTE(CURRENT_TIME())
  ,SECOND(CURRENT_TIME())
  ,MICROSECOND(CURRENT_TIME());

SELECT DATE(NOW()), TIME(NOW());

curdate() <현재날짜

year >int data type 으로 반환

SELECT 
   DATEDIFF('2020-08-06', NOW())
  ,TIMEDIFF('17:30:01', CURRENT_TIME());

SELECT 
   DAYOFWEEK(CURDATE())
  ,MONTHNAME(CURDATE())
  ,DAYOFYEAR(CURDATE());

SELECT LAST_DAY('2020-03-18');

시스템 함수

SELECT SLEEP(3);

SELECT 
    a.*
FROM 
    tb_member a,(SELECT SLEEP(3)) b;

n초 뒤에 결과 반환

---

MySQL 변수

SET @myNumber1 = 1;
SET @myNumber2 = 2.3;
SET @myStr1 = '합계:';

SELECT
    @myNumber1 AS '변수1'
   ,@myNumber2 AS '변수2';
 
SELECT
    @myStr1
   ,@myNumber1 + @myNumber2) AS '합계';
 
SELECT
    @myStr1
   ,ROUND((@myNumber1 + @myNumber2), 3) AS '합계';

---

SELECT 문 조건 연산자

비교 연산자		논리 연산자
연산자	의미	연산자	의미
=	같다	AND	모든 조건을 만족해야 검색
<>	다르다	OR	여러 조건 중 한가지만 만족해도 검색
<	작다	NOT	조건을 만족하지 않는 것만 검색
>	크다
<=	작거나 같다
>=	크거나 같다

 

SELECT 문 조건 LIKE 키워드

사용 예	설명
LIKE '데이터%'	'데이터'로 시작하는 문자열 (길이는 상관 없다.)
LIKE '%데이터'	'데이터'로 끝나는 문자열 (길이는 상관 없다.)
LIKE '%데이터%'	'데이터'가 포함된 문자열
LIKE '데이터____'	데이터로 시작하는 7자 길이의 문자열
LIKE '__데%'	세번째 글자가 '데'인 문자열

 

---

SQL JOINS

INNER JOIN

SELECT
	m.m_id			AS '회원 아이디'
	,m.m_name		AS '회원 이름'
	,l.level_name	AS '회원 권한'
FROM
	tb_member m
	INNER join
	tb_member_level l
	on
	m.m_level = l.level_num
WHERE
	l.level_name LIKE '%판매%';