MYSQL 설치 SQL언어 SQL언어의 활용 웹과 SQL언어와의 연동

개요 실험제목 목표 목차 SQL함수를 사용하여 데이터베이스 조작하기 SQL의 함수를 사용하는 연습과 JOIN, SUBQUERY, 제약 조건 이해 하기 목차 입문예제 함수의 종류 COUNT 연습 SUM 연습 AVG 연습 MAX 연습 MIN 연습 함수 사용시 주의 사항 JOIN CROSS JOIN연습 NATURAL JOIN 연습 SELF JOIN 연습 OUTER JOIN 연습 SUB-QUERY SUB-QUERY 연습 제약 조건의 종류 제약 조건 부여 연습 요약 연습문제 실습과제

입문 예제 STUDENT 테이블에서 학년(Grade)이 가장 높은 학생의 정보를 출력하시오. 실행결과 SELECT * FROM student where Grade = (SELECT MAX(Grade) FROM student); 실행결과

이론 : 함수의 종류 함 수 의 미 COUNT 조건을 만족하는 모든 행의 수를 보여준다. SUM 함   수 의 미 COUNT 조건을 만족하는 모든 행의 수를 보여준다. SUM 조건을 만족하는 모든 행의 합계를 보여준다. AVG 조건을 만족하는 모든 행의 평균을 보여준다. MAX 조건을 만족하는 모든 행의 최대값을 보여준다. MIN 조건을 만족하는 모든 행의 최소값을 보여준다. 문법 SELECT 함수 [ DISTINCT]{*, 컬럼…} FROM 테이블명 [WHERE 조건] [GROUP BY 컬럼1, 컬럼2…n] [HAVING Group-Condition] [ORDER BY 컬럼1, 컬럼2,…[ASC/DESC]]; GROUP BY : 결과값을 지정한 컬럼을 기준으로 그룹화 HAVING : GROUP BY 에 의한 결과에 대한 조건 절 ORDER BY : 결과값을 분류

연습 : COUNT, SUM함수 STUDENT 테이블에 저장되어 있는 전체 행수 출력 STUDENT 테이블에 저장되어 있는 GRADE 컬럼에 NULL을 제외한 행수 출력 SELECT COUNT(*) FROM STUDENT; *는 조건에 상관없이 모든 값을 출력 SELECT COUNT(GRADE) FROM STUDENT;

연습 : AVG, MAX, MIN함수 STUDENT 테이블에 GRADE 컬럼의 평균값 출력 SELECT AVG(GRADE) FROM STUDENT; SELECT MAX(GRADE), MIN(GRADE) FROM STUDENT;

연습 : 함수 사용시 주의 사항 GROUP BY절에 의한 조건은 WHERE절에 표현할 수 없고 HAVING절에 표현 되어야 한다. SELECT AVG(GRADE) FROM STUDENT WHERE AVG(GRADE) > 1 GROUP BY ADDRESS; 3행에 오류: 그룹함수는 허가되지 않습니다. SELECT ADDRESS, AVG(GRADE) FROM STUDENT GROUP BY ADDRESS HAVING AVG(GRADE) > 1;

이론 : JOIN JOIN JOIN 의미 CROSS 한 개 이상의 테이블로부터 데이터를 조회하는 것 문법 JOIN 의미 CROSS 하나의 테이블에 있는 어떠한 컬럼도 조인할 테이블의 어떤 컬럼에 직접적으로 일치 하지 않을 때 사용 NATURAL 2개 이상의 테이블이 공통되는 컬럼에 의해 조인하는 방법 OUTER 조인 조건을 사용할 때 조인 조건을 만족하지 않은 행들도 결과에 나타내고자 하는 경우 사용 SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2] [NATURAL JOIN table2] [JOIN table2 ON(table1.column = table2.column)] [LEFT (RIGHT) FULL OUTER JOIN table2 ON(table1.column=table2.column)]

연습 : CROSS JOIN, NATURAL JOIN CROSS JOIN 이용한 STUDENT와 SCHOOL 정보 출력 NATURAL JOIN 이용한 STUDENT 정보 출력 [우측란에는 실행결과를 보여줌] SELECT * FROM student CROSS JOIN school; SELECT id, name, department FROM student NATURAL JOIN school; SELECTs.id, s.name, s.department FROM student s school p Where s.department = p.deprarment;; 형태로 표현 가능

연습: OUTER JOIN school 테이블 Student 테이블 SELECT s.id, s.name, s.department, p.department FROM student s LEFT(RIGHT) OUTER JOIN school p ON s.department = p.department; RIGHT OUTER JOIN 실행 OUTER JOIN을 기준으로 오른쪽에 배치된 테이블에 데이터가 존재 하지 않는경우 RIGHT JOIN, 왼쪽의 테이블에 데이터가 존재 하지 않을 때 LEFT JOIN사용 오른쪽에 배치된 테이블인 SCHOOL의 department의 데이터인 5가 student에 존재하지 않기 때문에 NULL값이 출력됨 school 테이블 LEFH OUTER JOIN 실행 Student 테이블

이론 : SUB-QUERY SUB-QUERY QUERY문장에 사용 된 또 다른 QUERY문장을 의미 문법 SUB-QUERY의 MAIN-QUERY내의 위치 SELECT 절, FROM 절, WHERE 절, HAVING 절, INSERT 문의 INTO 절, UPDATE문의 SET절 ORDER BY절에는 사용 할 수 없다. SELECT ……. FROM …… WHERE (……. (SELECT ….. FROM ……. WHERE ….); ); SUB-QUERY MAIN-QUERY

연습: SUB-QUERY SELECT department FROM student WHERE id = 11; SELECT * Tedy와 같은 학과에 있는 학생 정보 출력 먼저 실행한 SQL문의 결과로 다시 SELECT문 실행 SUB-QUERY사용한 SELECT문 실행 [우측란에는 실행결과를 보여줌] SELECT department FROM student WHERE id = 11; SELECT * FROM student WHERE department = 2; SELECT * FROM student WHERE department = (SELECT department WHERE id = 11);

이론 : 제약 조건의 종류 제약 조건이란 테이블의 해당 컬럼에 사용자가 원치 않은 데이터가 입력, 변 경, 삭제되는 것을 방지하기 위하여 테이블을 생성할 때 어떤 조건을 설정할 수 있다. 6가지의 제약 조건이 있다. 제 약 조 건 의미 PRIMARY - KEY 하나의 행에서 그 행을 대표하는 컬럼이며 FOREIGN-KEY 컬럼이 참조하는 컬럼은 반드시 PRIMARY – KEY 컬럼이어야 한다. FOREIGN - KEY 입력되어야 할 값이 다른 테이블의 Primary-key컬럼인 컬럼 CHECK 입력되어야 할 값이 정해져 있는 경우 직접 정의할 수 잇음 UNIQUE 컬럼의 값이 테이블 전체에서 유일한 값이어야 하는 경우 NOT NULL 컬럼에 NULL값이 입력되어서는 안 되는 경우

연습 : 제약 조건 부여 table 생성시 부여 table 생성 후 부여 Create table student( Id int PRIMARY KEY, Name varchar(30) NOT NULL, Address varchar(100) CHECK (Address in(‘seoul’,’daegu’,’LA’) mobilnumber varchar(50) ); Id는 PRIMARY-KEY로 선언. Name는 NOT NULL조건 사용하여 NULL값 입력 불가능. Address는 CHECK조건 사용하여 seoul, daegu, LA만 입력 가능. Alter table student ADD( Constraint PRIMARY KEY(id)); ALTER절 사용하여 student의 id속성에 primary key설정

요약 [실험수행을 효과적으로 수행하기 위한 tip] SQL문의 작성 SQL문 작성시 주의사항 COUNT 함수 SUM 함수 조건을 만족하는 모든 행의 수를 보여줌 SUM 함수 조건을 만족하는 모든 행의 합계를 보여줌 AVG 함수 조건을 만족하는 모든 행의 평균을 보여줌 MAX 함수 조건을 만족하는 모든 행의 최대값을 보여줌 MIN 함수 조건을 만족하는 모든 행의 최소값을 보여준다 CORSS JOIN 두 테이블의 어떤 컬럼에 직접적으로 일치 하지 않을 때 사용 NATURAL JOIN 2개 이상의 테이블이 공통되는 컬럼에 의해 조인 SELF JOIN 한 개의 테이블의 행들을 같은 테이블의 행들과 조인 OUTER JOIN 조인 조건을 사용할 때 조인 조건을 만족하지 않은 행들도 결과 에 나타내고자 하는 경우 사용 PRIMARY - KEY 하나의 행에서 그 행을 대표하는 컬럼 FOREIGN - KEY 입력되어야 할 값이 다른 테이블의 Primary-key컬럼인 컬럼 CHECK 입력되어야 할 값이 정해져 있는 경우 사용 UNIQUE 컬럼의 값이 테이블 전체에서 유일한 값이어야 하는 경우 NOT NULL 컬럼에 NULL값이 입력되어서는 안 되는 경우 [실험수행을 효과적으로 수행하기 위한 tip] SQL문의 작성 키워드는 대문자로 작성하는 것이 바람직함 각 절을 구분하여 작성 의미있는 단어를 사용하여 작성 GROUP BY절에 의한 조건은 WHERE절에 표현 할 수 없고 HAVING절에 표현 SQL문 작성시 주의사항 문장의 끝에는 항상 ; 사용. 문자 입력시 “ “ 사용. 윈도우에서는 대소문자 구분하지 않는다.

연습문제(구현) 아래 제시한 관계형 테이블을 생성(테이블명 Professor)하고 Professor의 테 이블에서 소속이 서울시립대인 교수의 수를 출력하는 SQL문을 작성하시오. 아래 제시한 관계형 테이블을 생성(테이블명 SchoolPhoneBook)하고 교수들의 교수번호, 이름, 전공, 소속학교 전화번호를 출력하는 SQL문을 작성 하시오.(JOIN사용) 교수번호 이름 전공 소속 급여 144001 조승우 생물학 서울시립대 250 144002 박영구 물리학 300 144003 강수정 290 144004 조승현 미술학 서울대 350 소속 전화번호 서울시립대 02-3459-0987 서울대 02-245-1372 연세대 01-573-8702

연습문제(구현) 이름이 조승우인 교수의 소속 대학교 전화번호를 출력하는 SQL문을 작성하 시오. Professor 테이블에 저장되어있는 전체 행 수를 출력하는 SQL문을 작성하 시오. 소속별 평균 급여액을 출력하는 SQL문을 작성하시오. 급여액의 합을 출력하는 SQL문을 작성하시오. 급여의 최대값과 최소값을 출력하는 SQL문을 작성하시오. 급여액의 평균값을 출력하는 SQL문을 작성하시오.

실습과제 1. Emp와 Dept 테이블을 JOIN하여 부서번호, 부서명, 이름,급여를 출력하시오. 사원번호 사원명 직급 급여 부서번호 5 유관순 이사 550 40 2 주영현 과장 250 10 주일환 차장 300 4 홍길동 부장 350 30 3 홍경욱 200 20 1. Emp와 Dept 테이블을 JOIN하여 부서번호, 부서명, 이름,급여를 출력하시오. 2. Emp 테이블의 주영현 사원이 속해있는 부서의 모든 사람의 사원번호, 이름, 급여를 출력하시 오. 3. 주일환 사원이 속해있는 부서의 모든 사람의 사원번호, 이름, 급여, 부서명을 출력하시오. 4. 급여가 10번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하시오. 5. 급여가 10번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하시오. 6. 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 지역, 급여를 출력하시오. 7. 10번 부서의 연봉을 계산하여 이름, 부서번호, 급여, 연봉을 출력하는 SQL문을 작성하시오. 단 연말에 급여의 150%를 보너스로 지급한다. 8. 급여가 250 부터 350사이의 사람은 급여의 15%를 회비로 지불하기로 했다. 이름 급여, 회비 를 출력하는 SQL문을 작성하시오. 부서번호 부서명 지역코드 10 전산과 1 20 경영지원과 30 총무과 40 기술지원과 Dept테이블 Emp 테이블