전북대학교 수학 ∙통계정보과학부

1. 전북대학교 수학∙통계정보과학부 SQL실습 안 정 용

2. DBMS 표준 언어 • 이용자(응용 프로그램)와 DBMS 사이의 중간 다리 역할 담당 • 데이터 정의, 제어, 조작 등의 기능 포함 Database Management System • 고객정보 테이블에서 직업이 ‘학생’인 고객 검색 • Select * from Customer_Info • where job=‘학생’ SQL SQL 개요 • SQL(Structured Query Language)이란?

3. ·데이터 정의문 - CREATE - DROP - ALTER ·데이터 제어문 - GRANT - DENY - REVOKE ·데이터 조작문 - SELECT - INSERT - UPDATE - DELETE ·데이터베이스 객체 생성, 삭제, 갱신 - CREATE DATABASE ‘데이터베이스_이름’ ·데이터베이스 객체 이용권한 부여 - GRANT CREATE TABLE TO ‘사용자 계정’ ·데이터 검색, 삽입, 갱신, 삭제 - SELECT * FROM ‘테이블_이름’ • SQL 문장

4. 데이터 정의문 • 데이터베이스 생성 및 삭제 ·데이터베이스 생성 예 : CREATE DATABASE TestDB - TestDB 이름을 가진 데이터베이스 생성 - 데이터베이스의 크기, 저장될 파일 이름, 파일의 증가 단위 등을 지정할 수 있음 - 시스템에서 기본적으로 제공하는 것을 사용 ·데이터베이스 삭제 예 : DROP DATABASE TestDB - TestDB 이름을 가진 데이터베이스 삭제

5. 테이블 생성 및 삭제 ·테이블 생성 CREATE TABLE테이블_이름 ( 필드_이름 데이터_타입 [제약조건] , 필드_이름 데이터_타입 [제약조건] , … 필드_이름 데이터_타입 [제약조건] ) - 데이터_타입

6. - 제약조건 - 예 : 고객테이블

7. - 예 : 고객테이블 생성 CREATE TABLE Customer_Info ( CID char(6) PRIMARY KEY, Name char(10) not null, Address char(20) , Tel char(15) , Bday datetime not null, SID char(14) not null, Job char(10) , Sex char(1) not null, Married bit ) CREATE TABLE Customer_Info ( CID char(6) not null, Name char(10) not null, Address char(20) , Tel char(15) , Bday datetime not null, SID char(14) not null, Job char(10) , Sex char(1) not null, Married bit, PRIMARY KEY (CID) )

8. ·테이블 변경 : 필드 추가/제거, 데이터 타입 또는 크기 변경 예 : ALTER TABLE Customer_Info ALTER COLUMN NAME char(20) - Customer_Info 테이블의 Name 필드의 크기를 20으로 변경 ·테이블 제거 예 : DROP TABLE Customer_Info - Customer_Info 테이블 제거 ※ 기본 키나 외래 키가 포함되면 삭제 순서를 고려 - Customer_OnlineInfo, Customer_Info에서 삭제 순서는? DROP TABLE Customer_OnlineInfo DROP TABLE Customer_Info

9. 실습 환경 • 실습 환경 • Server Computer, DBMS : COMPSTAT 서버 이용(전북대학교) • Client 환경 : Web browser 이용 • 테이블 구성

10. 예제 데이터 - Table : Sales - Table : Customer_Info - Table : Customer_OnlineInfo • 실습 사이트 • compstat.chonbuk.ac.kr/dbbook

11. 데이터 조작문 • 데이터 검색 : SELECT 문 ·여러 테이블을 조합해서 사용자가 원하는 필드와 레코드를 추출 ·일반적인 형식 SELECT [DISTINCT] 필드_리스트 FROM테이블_이름 [WHERE 조건] [GROUP BY 필드_리스트] [HAVING 조건] [ORDER BY 필드_리스트 [ASC|DESC] ] - SELECT : 검색하고자 하는 필드 이름 명시 - FROM : 검색 테이블 이름 명시 - WHERE : 검색할 데이터의 검색 조건 지정 - GROUP BY : 그룹화(분할) 하고자 하는 필드 이름 명시 - HAVING : GROUP BY에서 명세된 그룹에 대한 검색 조건 명시 - ORDER BY : 정렬하고자 하는 기준 필드 이름 명시

12. (1) 테이블의 일부 또는 전체 필드 검색 - 예 : 테이블 Customer_Info 에서 고객번호, 이름, 직업 필드의 데이터 검색 SELECT CID, Name, Job FROM Customer_Info - 예 : 테이블 Customer_OnlineInfo 에서 전체 필드의 데이터 검색 SELECT(all ) * FROM Customer_OnlineInfo 또는 SELECT CID, LID, LPW, Email, Jday FROM Customer_OnlineInfo

13. (2) 레코드의 중복 제거 : DISTINCT - 예 : 제품 구입을 한 고객 모두 검색 Sales 테이블의 고객번호들을 중복 없이 검색 SELECT DISTINCT CID FROMSales (3) 레코드의 조건 검색 : WHERE - 예1 : Customer_Info 테이블에서 직업이 ‘회사원’인 고객의 고객번호(CID), 이름(Name), 직업(job), 결혼여부(married) 검색 SELECT CID, Name, Job, Married FROMCustomer_Info WHERE Job = '회사원'

14. - 예2 : Customer_OnlineInfo 테이블에서 전자우편주소(Email)가 널 값이 아닌 레코드 검색 SELECT* FROM Customer_OnlineInfo WHEREEmail is not NULL - 예3 : Customer_Score 테이블에서 Score가 100 이상이고 200 이하인 레코드 검색 SELECT* FROM Customer_Score WHEREScore>=100 and Score<=200 ※ 비교, 논리 연산자

15. - 예4 : LIKE 연산자 이용 ·특정 패턴의 문자열 검색 SELECT* SELECT * FROM Customer_Info FROM Customer_OnlineInfo WHEREName LIKE'김%'WHERE Email LIKE '%hotmail%' - 예5 : IN 연산자 이용 ·검색하고자 하는 값의 집합 이용 SELECTCID, Name, Job FROM Customer_Info WHEREJob IN ('회사원', '공무원', '학생')

16. (4) 데이터의 그룹화 : GROUP BY - 특정 필드의 값에 따라 데이터 그룹화, 일반적으로 SQL 함수와 같이 사용 - 예 : Customer_Info 테이블에서 직업별 고객 빈도수 검색 SELECT Job, count(*) as freq FROM Customer_Info GROUP BY Job (5) 데이터 그룹화의 조건검색 : HAVING - 각 그룹에 대한 조건 검색 - HAVING 절 다음에는 SELECT 다음에 명시된 필드(또는 함수)에 대해서만 조건을 줄 수 있음 - 예 : Sales 테이블에서 2001년 4월의 고객별 구매회수가 20회 이상인 고객들만 검색 SELECTCID, count(*) as freq FROM Sales WHERE year(SDay)=2001 and month(SDay)=4 GROUP BY CID HAVING count(*) >= 20

17. (6) 출력 순서 명시 : ORDER BY - 질의 결과의 레코드 정렬 - 예 : Customer_Info 테이블에서 고객들을 생년월일 순으로 검색 SELECT CID, Name, Bday FROM Customer_Info ORDER BY Bday ( ASC ) (7) 상위 레코드 선택 : SELECT TOP - 예 : Customer_OnlineInfo 테이블에서 2000년 이후 가입한 고객 중 먼저 가입한 5명 검색 SELECTTOP5 * FROM Customer_OnlineInfo WHERE year(Jday) >= 2000 ORDER BY Jday

18. (8) 여러 테이블에서 데이터 검색 : JOIN - 관계형 데이터베이스 설계 시에 데이터의 중복을 피하고 데이터의 정확성을 높이기 위해 개체와 관계를 여러 테이블로 나누어 설계 - 따라서 여러 테이블을 조합해야 원하는 데이터 검색할 수 있음 - INNER JOIN - OUTER JOIN - CROSS JOIN

19. ① INNER JOIN - 기본적인 조인형태, 조인하고자 하는 테이블을 연결하는 필드의 값이 같은 레코드 선택 - 예 : 테이블 Customer_Info, Customer_OnlineInfo 에서 고객번호, 이름, Login ID, 전자우편주소를 고객번호 순으로 검색 SELECT Customer_Info.CID, Name, LID, EMail FROM Customer_Info INNER JOIN Customer_OnlineInfo ON Customer_Info.CID= Customer_OnlineInfo.CID ORDER BY Customer_Info.CID 또는 SELECT Customer_Info.CID, Name, LID, EMail FROM Customer_Info, Customer_OnlineInfo WHERE Customer_Info.CID= Customer_OnlineInfo.CID ORDER BY Customer_Info.CID 또는 SELECT C.CID, Name, LID, EMail FROM Customer_Info AS C, Customer_OnlineInfo AS CO WHERE C.CID= CO.CID ORDER BY C.CID

20. ② OUTER JOIN - 조인할 때 기준테이블을 설정하고, 기준테이블의 모든 레코드와 다른 테이블에서 조인 조건을 만족하는 레코드 선택 - 기준테이블의 위치에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN 등이 있음 - 예 : 고객 정보에 온라인 정보를 추가해서 검색하고자 할 때 ( LEFT JOIN ) SELECTC.CID, Name, LID, EMail FROMCustomer_Info ASC LEFT JOINCustomer_OnlineInfo AS CO ONC.CID= CO.CID 또는 SELECTC.CID, Name, LID, EMail FROMCustomer_Info ASC, Customer_OnlineInfo AS CO WHEREC.CID *= CO.CID - 예 : 고객 정보에 온라인 정보를 추가해서 검색하고자 할 때 ( RIGHT JOIN ) SELECTC.CID, Name, LID, EMail FROMCustomer_OnlineInfo ASCO, Customer_Info AS C WHERECO.CID =* C.CID

21. (9) 중첩 질의 - WHERE 절에 완전한 하나의 SELECT 질의문이 포함되어 있는 형태 - 어떤 테이블내에 존재하는 레코드를 검색한 다음 이를 비교 조건에서 사용 - 예 : Customer_Info 테이블에도 존재하고, Customer_Score 테이블에도 존재하는 고객(온라인 고객이면서 구매 실적이 있는 고객)의 명단 검색 SELECT CID, Name FROM Customer_Info as C WHERE EXISTS ( SELECT * FROM Customer_OnlineInfo as CO WHERE C.CID = CO.CID ) AND EXISTS ( SELECT * FROM Customer_Score as SC WHERE C.CID = SC.CID )

22. 데이터 삽입 : INSERT 문 ·테이블에 새로운 레코드 삽입 ·일반적인 형식 INSERT (INTO) [ ( 필드_이름 [ , … n ] ) ] VALUES(데이터 [ , … n ] ) - 예1 : Customer_Info 테이블에 고객 번호, 고객 이름, 생년월일, 주민등록번호, 성별이 (100005, '김철수', '82-6-1', '820601-1450512', 1) 인 레코드 삽입 INSERT INTO Customer_Info (CID, Name, Bday, SID, Sex) VALUES('100005', '김철수', 82-6-1, '820601-1450512', 1) - 예2 : Customer_Info 테이블에서 남자 고객만 Customer_Male 테이블로 복사 INSERT INTO Customer_Male ( SELECT CID, Name, Address, Tel, Sex FROMCustomer_Info WHERE Sex=0 )

23. 데이터 갱신 및 삭제 : UPDATE 문, DELETE 문 - 예1 : Customer_Info 테이블에서 고객번호가 100005인 고객의 전화번호를 '254-4567', 직업을 '회사원'으로 수정 UPDATE Customer_Info SET Tel='254-4567', Job='회사원' WHERE CID='100005' - 예2 : Customer_Info 테이블에서 고객번호가 100005인 레코드를 삭제 DELETE Customer_Info WHERE CID='100005'

24. SQL 함수 • 스칼라(Scalar) 함수 (1) 수학 함수 - 예 : 0과 1사이의 난수 생성 SELECT RAND() as RN

25. (2) 문자열 함수 - 예 : Customer_OnlineInfo 테이블에서 LPW 필드의 길이 검색 SELECT CID, LEN(LPW) as LengthofPassword FROM Customer_OnlineInfo

26. (3) 날짜 함수 - 예 : Customer_Info 테이블에서 고객들의 나이를 내림차순으로 검색 SELECTCID, Name, year(GETDATE())-year(Bday) as Age FROMCustomer_Info ORDER BY Age DESC

27. 통계 관련 함수 (1) 집계 함수 - 예 : Customer_Info 테이블의 전체 레코드 수, 고객 나이의 표준편차를 계산 SELECT COUNT(*) as countAll, STDEV( year(GETDATE())-year(Bday)) as sampleSTDEV STDEVP( year(GETDATE())-year(Bday)) as popSTDEV FROM Customer_Info

28. (2) 다차원 분석 연산자 : ROLLUP, CUBE, COMPUTE - GROUP BY 와 함께 데이터에 대한 2차적 요약 - 예 : Customer_Info 테이블과 Sales 테이블을 JOIN 하여 고객별, 제품별 평균 구매액 산출 SELECT Name, PID, AVG(Amount) as averAmount FROM Customer_Info as C INNER JOIN Sales as S ON C.CID=S.CID GROUP BY Name, PID WITH CUBE ORDER BY Name

29. SQL 활용 예 • ASP : 고객정보 입력 <html> <% userId = Trim(Request("userid")) Password = Trim(Request("password")) userName = Trim(Request("username")) : : email = Trim(Request("email")) sql= " Insert into db_Users Values ( '"&userId&"' ,'"&Password&"', '"&userName&"', … , '"&email&"' )" Set DBConn = Server.CreateObject("ADODB.CONNECTION") DBConn.Open Session("Dsn") DBConn.Execute sql Dbconn.Close Set Dbconn=Nothing %> </html> 사용자 입력 데이터 SQL 문장 DB 연결 SQL 문 실행

30. ASP : 로그인 <html> <% userID = Trim(Request.Form("UserID")) userPW = Trim(Request.Form("Password")) sql = "Select * FROM db_Users " sql = sql & " WHERE LID = '"&userID&"' and LPW='"&userPW&"' " set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, Session("Dsn") if not rs.EOF then Response.Redirect "../start.asp" ‘사용자가 존재하면 else Response.redirect "error.asp" ‘사용자가 존재하지 않으면 end if ' 사용자가 존재하지 않으면 %> </html> 사용자 입력 데이터 SQL 문장 DB 연결 및 SQL 문 실행