5장 SQL 언어 Part II

5 장 SQL 언어 Part II 박창이 서울시립대학교통계학과 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 1 / 26

데이터조작문 데이터검색 : SELECT 문데이터추가 : INSERT 문데이터수정 : UPDATE 문데이터삭제 : DELETE 문 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 2 / 26

SELECT 문 여러테이블을조합해서사용자가원하는필드와레코드를추출일반적인형식 SELECT [DISTINCT] 필드리스트 FROM 테이블이름 [WHERE 조건 ] [GROUP BY 필드리스트 ] [HAVING 조건 ] [ORDER BY 필드리스트 [ASC DESC]] 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 3 / 26

SELECT 문 : 기능 테이블의일부필드검색 ( 예제1) Customer Info 에서고객번호, 이름, 직업필드의데이터를검색 SELECT CID, Name, Job FROM Customer Info 전체필드를검색 ( 예제2) Customer OnlineInfo 에서전체필드의데이터를검색 SELECT all * FROM Customer OnlineInfo 또는 SELECT * FROM Customer OnlineInfo 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 4 / 26

SELECT 문 : DISTINCT ( 예제 ) 제품구입을한번이라도한적이있는고객들을 (Sales 테이블의고객번호들을중복없이 ) 모두검색 SELECT DISTINCT CID FROM Sales 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 5 / 26

SELECT 문 : WHERE 레코드조건검색 ( 예제 1) Customer Info에서직업이 회사원 인고객의고객번호 (CID), 이름 (Name), 직업 (job), 결혼여부 (married) 를검색 SELECT CID, Name, Job, Married FROM Customer Info WHERE Job = 회사원 ( 예제 2) Customer OnlineInfo에서전자우편주소가 null 값이아닌레코드를검색 SELECT * FROM Customer OnlineInfo WHERE Email is not NULL 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 6 / 26

비교및논리연산자 ( 예제 4) Customer Score 테이블에서고객점수가 100 이상이고 200 이하인레코드를검색 SELECT * FROM Customer Score WHERE Score >= 100 AND Score <= 200 ( 예제 5) Customer OnlineInfo테이블에서 Login ID 와 Password 를동일하게이용하는고객을검색 SELECT * FROM Customer OnlineInfo WHERE LID=LPW 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 7 / 26

LIKE 연산자 ( 예제 1) Customer Info 테이블에서 김 씨성을가진고객의고객번호, 이름, 주소를검색 SELECT CID, Name, Address FROM Customer Info WHERE Name LIKE 김 % ( 예제 2) Customer OnlineInfo 테이블에서 Email 로 hotmail 을이용하는고객검색 SELECT * FROM Customer OnlineInfo WHERE Email LIKE %hotmail% 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 8 / 26

IN 키워드 ( 예제 1) Student 테이블에서지도교수번호가 1031, 2042, 5233 인학생을검색 SELECT * FROM Student WHERE PID IN (1031, 2042, 5233) ( 예제 2) Customer Info 테이블에서직업이 회사원, 공무원 인고객검색 SELECT * FROM Customer Info WHERE Job IN ( 회사원, 공무원 ) 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 9 / 26

SELECT 문 : GROUP BY 데이터그룹화 ( 예제 1) Customer Info 테이블에서직업별고객빈도파악 SELECT Job, count(*) as freq FROM Customer Info GROUP BY Job ( 예제 2) Sales 테이블에서 2001년 4월의고객별구매회수를검색 SELECT CID, count(*) as freq FROM Sales WHERE year(sday)=2001 and month(sday) = 4 GROUP BY CID 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 10 / 26

SELECT 문 : HAVING 그룹화조건검색 ( 예제 1) Sales 테이블에서 2001년 4월의고객별구매회수 20회이상인고객검색 SELECT CID, count(*) as freq FROM Sales WHERE year(sday)=2001 and month(sday) = 4 GROUP BY CID HAVING count(*) >= 20 ( 예제 2) Score 테이블에서학생의평균점수가 90 이상인학생검색 SELECT ID, avg(score) as mean FROM Score GROUP BY ID HAVING avg(score) >= 90 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 11 / 26

SELECT 문 : ORDER BY 출력순서명시 ( 예제 1) Customer Info 테이블에서고객들을생년월일순으로검색 SELECT CID, Name, BDay FROM Customer Info ORDER BY BDay ( 예제 2) Score 테이블에서학생의평균점수를오름차순으로검색 SELECT ID, avg(score) as mean FROM Score GROUP BY ID ORDER BY avg(score) 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 12 / 26

레코드수제한 : SELECT TOP (MySQL: 끝에 LIMIT 수 ) ( 예제 1) Score 테이블에서교과목성적이상위 3명의학생을내림차순으로검색 SELECT TOP 3 * FROM Score ORDER BY Score DESC ( 예제 2) Score 테이블에서성적이상위 50% 인학생을내림차순으로검색 ( 단, 동점자포함 ) SELECT TOP 50 PERCENT WITH TIES * FROM Score ORDER BY Score DESC ( 예제 3) Customer Info 테이블에서 2000 년이후에가입한고객중가장빨리가입한고객 5명검색 SELECT TOP 5 * FROM Customer Info WHERE year(jday) >= 2000 ORDER BY JDay ASC 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 13 / 26

SELECT 문 : JOIN INNER JOIN ( 예제 1) 테이블 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 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 14 / 26

테이블이름의별명 : AS SELECT C.CID, Name, LID, EMail FROM Customer Info AS C INNER JOIN Customer OnlineInfo AS CO ON C.CID=CO.CID ORDER BY C.CID 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 15 / 26

INNER JOIN과 WHERE ( 예제 2) Customer Info, Customer OnlineInfo에서 1980년이후출생자의고객번호, 이름, Login ID, 전자우편주소를고객 ID 순으로검색 ( 전자우편주소가 null값이면제외 ) SELECT C.CID, Name, LID, EMail FROM Customer Info AS C INNER JOIN Customer OnlineInfo AS CO ON C.CID=CO.CID WHERE year(bday) >= 1980 and Email is not NULL ORDER BY C.CID ( 예제 3) Student, Score, Curriculum 에서학생들의과목별성적을검색 SELECT S.ID, Name, SubjectName, Score FROM Student as S INNER JOIN Score as SC ON S.ID= SC.ID INNER JOIN Curriculum as C ON SC.SubjectID=C.SubjectID ORDER BY S.ID 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 16 / 26

OUTER JOIN LEFT OUTER JOIN ( 예제 1) 고객정보에온라인정보를합해서볼때 SELECT C.CID, Name, LID, EMail FROM Customer Info AS C LEFT JOIN Customer OnlineInfo AS CO ON C.CID=CO.CID 또는 SELECT C.CID, Name, LID, EMail FROM Customer Info AS C, Customer OnlineInfo AS CO WHERE C.CID *= CO.CID 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 17 / 26

RIGHT OUTER JOIN ( 예제 2) 고객정보에온라인정보를합해서볼때 SELECT C.CID, Name, LID, EMail FROM Customer OnlineInfo AS CO RIGHT JOIN Customer Info AS C ON CO.CID=C.CID 또는 SELECT C.CID, Name, LID, EMail FROM Customer OnlineInfo AS CO, Customer Info AS C WHERE CO.CID =* C.CID 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 18 / 26

CROSS JOIN 조인가능한모든조합 ( 예제 ) SELECT C.CID, Name, LID, EMail FROM Customer Info AS C CROSS JOIN Customer OnlineInfo AS CO WHERE C.CID= 100001 or C.CID= 100002 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 19 / 26

SELECT 문 : UNION 테이블연결검색 ( 예제 ) 테이블 Score 와 Grad Score 에서학번, 교과목번호, 성적을검색 SELECT ID, SubjectID, Score FROM Score UNION SELECT ID, SubjectID, Score FROM Grad Score 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 20 / 26

SELECT 문 : 중첩질의 어떤질의의 WHERE 문안에완전한 SELECT 질의가포함되어있는형태 ( 예제 1) Customer Info 테이블에서 Email 주소를가지고있는고객명단검색 SELECT CID, Name FROM Customer Info WHERE CID IN ( SELECT CID FROM Customer onlineinfo WHERE Email is not null ) 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 21 / 26

( 예제 2) Student 테이블엔있지만 Score 테이블에존재하지않는학생명단 SELECT ID, Name FROM Student as S WHERE NOT EXISTS ( SELECT * FROM Score as Sc WHERE S.ID = Sc.ID ) ( 예제 3) 온라인고객이면서구매실적이있는고객명단검색 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 ) 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 22 / 26

INSERT 문 Create Table 명령문에의하여생성된테이블에새로운레코드를삽입 ( 예제 1) INSERT INTO Customer Info (CID, Name, Bday, SID, Sex) VALUES ( 999999, 송연지, 82-10-18, 821018-2853419, 1) ( 예제 2) 모든필드의데이터가준비되어있을때필드명은생략가능 INSERT INTO Customer Info VALUES ( 999998, 김가영, 충북청원군, 84-2-18, 840218-2498625, 학생, 1, 0) 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 23 / 26

( 예제 3) Customer Info 테이블에서남자고객만 Customer Male 테이블로복사 INSERT INTO Customer Male ( SELECT CID, Name, Address, Tel, Sex FROM Customer Info WHERE Sex=0 ) 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 24 / 26

UPDATE 문 테이블에저장되어있는레코드값을수정 ( 예제 1) Customer Info 테이블에서고객번호가 999999인고객의주소를 서울시노원구 으로수정 UPDATE Customer Info SET Address= 서울시노원구 WHERE CID= 999999 ( 예제 2) Customer Info 테이블에서고객번호가 999998인고객의전화번호를 254-4167, 직업을 회사원, 결혼여부를 1로수정 UPDATE Customer Info SET Tel= 254-4167, Job= 회사원, Married=1 WHERE CID= 999998 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 25 / 26

DELETE 문 테이블에저장되어있는레코드를삭제 ( 예제 ) Customer Info 테이블에서고객번호가 999998인레코드를삭제 DELETE Customer Info WHERE CID= 999998 박창이 ( 서울시립대학교통계학과 ) 5 장 SQL 언어 Part II 26 / 26