Stored program 2 장종원 phobos90@naver.com.

Presentation on theme: "Stored program 2 장종원 phobos90@naver.com."— Presentation transcript:

1 Stored program 2 장종원

2 성적관리 프로그램 Procedure(4장에서 했던)
SELECT ~ INSERT INTO ~ UPDATE ~ DELETE ~ Table name: SCORE Name(PK) kor eng math total Average varchar(20) int

3 SELECT Procedure화

4 Select Score Procedure
SELECT 절의 내용을 Procedure화 하여 커서를 이용해 처리

5 DatabaseManager Select부분 변경 (getScore 부분)

6 Delete Score Procedure
DELETE FROM Score WHERE name = ? 를 변수를 받아 Procedure에서 처리

7 DatabaseManager delete부분 변경 (deleteScore 부분)

8 과제 1 INSERT INTO, UPDATE SQL을 프로시저화하시오.

9 과제 2 selectScoreProcedure와 프로그램을 수정하여, 아래와 같이 나올 수 있도록 하세 요.
SELECT 절에서 CASE 문 사용 문법 SELECT (CASE WHEN 조건 THEN 값 WHEN 조건 THEN 값…. ELSE 값 END) AS grade 평균 100 ~ 90 A 평균 89 ~ 80 B 평균 79 ~ 70 C 평균 69 ~ 60 D 평균 59 ~ 0 F

10 SCORE 데이터 입력 DELETE FROM SCORE;
INSERT INTO SCORE VALUES('연아', 100, 90, 90, 280, 93); INSERT INTO SCORE VALUES('현진', 80, 80, 80, 240, 80); INSERT INTO SCORE VALUES('장훈', 100, 65, 50, 215, 71); INSERT INTO SCORE VALUES('지성', 70, 70, 70, 210, 70); INSERT INTO SCORE VALUES('연경', 50, 80, 55, 185, 61); INSERT INTO SCORE VALUES('흥민', 50, 50, 55, 155, 51);

11 Maria DB 문자 함수 CONCAT UPPER, LOWER LPAD, RPAD SUBSTR LENGTH REPLACE
INSTR TRIM, LTRIM, RTRIM IFNULL

12 CONCAT [ ] CONCAT(char1, char2) 두 문자를 결합 FROM DUAL
어떤 테이블도 참조되지 않는 상황에서는 dummy 테이블을 의미하는 DUAL을 명시할 수 있음 [ ]

13 UPPER, LOWER LOWER(char), UPPER(char) LOWER 문자열을 소문자로 변환
UNION ALL 집합 연산자

14 LPAD, RPAD LPAD(char1, n [,char2]), RPAD(char1, n [,char2])
char1 문자열 앞/뒤에 char2를 끼어 놓음(총 n개의 문자열의 길이로 맞추도록) LPAD 왼쪽에 문자열을 끼어 놓는 역할 RPAD 오른쪽에 문자열을 끼어 놓는 역할

15 SUBSTR SUBSTR(char, m ,[n]) m 번째 자리부터 길이가 n개인 문자열을 반환
m이 음수일 경우에는 뒤에서 m 번째 문자부터 n개의 문자를 반환

16 LENGTH LENGTH(char) 문자열의 길이를 반환

17 REPLACE REPLACE(char1, str1, str2) REPLACE는 문자열의 특정 문자를 다른 문자로 변환
대소문자 구분

18 INSTR INSTR (char1, str1) 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환
지정한 문자열이 발견되지 않으면 0이 반환

19 TRIM, LTRIM, RTRIM - 1 TRIM(str), LTRIM (str), RTRIM (str)

20 TRIM, LTRIM, RTRIM - 2 TRIM ( [ {BOTH | LEADING | TRAILING} [remove_str] from ] str) TRIM : 특정한 문자를 제거 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 BOTH : 양쪽 공백 혹은 문자 제거 (기본) LEADING : 좌측 공백 혹은 문자 제거 TRAILING : 우측 공백 혹은 문자 제거

21 IFNULL NULL 값을 다른 값으로 바꾸며, 모든 데이터 타입에 적용 가능 IFNULL(컬럼명, ‘대체할 값‘)
SELECT * FROM customer; SELECT custid, name, address, IFNULL(PHONE,' ’) phone FROM customer;

22 myTable 스키마 myTable.sql 참조 500개 데이터

23 과제 3 MYTABLE을 이용하여 다음 문제를 풀어주세요. (SQL 작성)
NAME의 길이보다 FAMILY의 수가 적은 사람의 수를 구하시오. NAME에 A(a)또는 B(b)가 들어가는 사람의 수를 구하시오. NAME과 CITY가 같은 글자로 시작하는 사람들의 NAME와 CITY를 출력하시오. (24명) 의 마지막 도메인을 잘라(.com, .uk, .org …) 각 항목별로 COUNT 하시오. 마지막 도메인은 2~3 글자 Group by를 이용하여 그룹핑

24 과제 3 MYTABLE을 이용하여 다음 문제를 풀어주세요. (SQL 작성)
각 연도별 인원수와 최대 SALARY를 구하고, 연도별로 정렬하시오. (2000 ~ 2018, 19개) Family 수에 따른 가장 최근 generatedate를 구하시오. Family로 정렬하시오. 힌트 : generatedate는 문자열이고, 가장 최근 날짜가 값이 제일 큰 것을 이용 문자열 숫자로 변환 : CAST(변수명 as unsigned)