Microsoft PowerPoint - ch07_데이터베이스 언어 SQL.pptx

Transcription

1 05-01 SQL의소개 SQL을이용한데이터정의 SQL을이용한데이터조작 뷰 삽입 SQL

2 학습목표 SQL의역할을이해하고, 이를기능별로분류해본다. SQL의데이터정의기능을예제를통해익힌다. SQL의데이터조작기능을예제를통해익힌다. 뷰의개념과장점을이해한다. 삽입 SQL의역할을이해한다. 2

3 01 SQL 의소개 SQL (Structured Query Language) 의미 관계데이터베이스를위한표준질의어 비절차적데이터언어 발전역사 SEQUEL(Structured English QUEry Language) 에서유래 SEQUEL : 연구용관계데이터베이스관리시스템인 SYSTEM R 을위한언어 미국표준연구소인 ANSI 와국제표준화기구인 ISO 에서표준화작업을진행 1999 년 SQL-99(SQL3) 까지표준화작업이완료된후계속수정및보완되고있음 사용방식 대화식 SQL : 직접데이터베이스관리시스템에접근해질의를작성하여실행 삽입 SQL : 프로그래밍언어로작성된응용프로그램에삽입 3

4 01 SQL 의소개 4

5 01 SQL 의소개 SQL 의분류 데이터정의어 (DDL) 테이블을생성하고변경 제거하는기능을제공 데이터조작어 (DML) 테이블에새데이터를삽입하거나, 테이블에저장된데이터를수정 삭제 검색하는기능을제공 데이터제어어 (DCL) 보안을위해데이터에대한접근및사용권한을사용자별로부여하거나취소하는기능을제공 5

6 01 SQL 의소개 질의에사용할판매데이터베이스 : 고객릴레이션 6

7 01 SQL 의소개 질의에사용할판매데이터베이스 : 제품릴레이션 7

8 01 SQL 의소개 질의에사용할판매데이터베이스 : 주문릴레이션 8

9 02 SQL 를이용한데이터정의 SQL 의데이터정의기능 테이블을생성, 변경, 제거 9

10 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 [ ] 의내용은생략이가능 SQL 질의문은세미콜론 (;) 으로문장의끝을표시 SQL 질의문은대소문자를구분하지않음 10

11 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 1 : 테이블을구성하는각속성의이름, 데이터타입, 기본제약사항정의 2 : 기본키정의 3 : 대체키정의 4 : 외래키정의 5 : 데이터무결성을위한제약조건정의 11

12 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 속성의정의 테이블을구성하는각속성의데이터타입을선택한다음널값허용여부와기본값필요여부를결정 NOT NULL 속성이널값을허용하지않음을의미하는키워드 예 ) 고객아이디 VARCHAR(20) NOT NULL DEFAULT 속성의기본값을지정하는키워드 예 ) 적립금 INT DEFAULT 0 문자열이나날짜데이터는작은따옴표로묶어서표현 ( 작은따옴표로묶여진문자열은대소문자를구분함 ) 예 ) 담당자 VARCHAR(10) DEFAULT 방경아 12

13 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 표 7-1 속성의데이터타입 데이터타입 INT 또는 INTEGER SAMLLINT CHAR(n) 또는 CHARACTER(n) VARCHAR(n) 또는 CHARACTER VARYING(n) NUMERIC(p, s) 또는 DECIMAL(p, s) FLOAT(n) REAL DATETIME 또는 DATE TIME 의미 정수 INT보다작은정수길이 n이고정길이의문자열최대길이가 n인가변길이의문자열고정소수점실수 p는소수점을제외한전체숫자의길이고, s는소수점이하숫자의길이길이가 n인부동소수점실수부동소수점실수년, 월, 일로표현되는날짜시, 분, 초로표현되는시간 13

14 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 키의정의 PRIMARY KEY 기본키를지정하는키워드 예 ) PRIMARY KEY( 고객아이디 ) 예 ) PRIMARY KEY( 주문고객, 주문제품 ) UNIQUE 대체키를지정하는키워드 대체키로지정되는속성의값은유일성을가지며기본키와달리널값이허용됨 예 ) UNIQUE( 고객이름 ) 14

15 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 키의정의 FOREIGN KEY 외래키를지정하는키워드 외래키가어떤테이블의무슨속성을참조하는지 REFERENCES 키워드다음에제시 참조무결성제약조건유지를위해참조되는테이블에서투플삭제시처리방법을지정하는옵션» ON DELETE NO ACTION: 투플을삭제하지못하게함» ON DELETE NO ACTION: 관련투플을함께삭제함» ON DELETE NO ACTION: 관련투플의외래키값을 NULL로변경함» ON DELETE NO ACTION: 관련투플의외래키값을미리지정한기본값으로변경함 15

16 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 키의정의 FOREIGN KEY 참조무결성제약조건유지를위해참조되는테이블에서투플변경시처리방법을지정하는옵션» ON UPDATE NO ACTION: 투플을변경하지못하게함» ON UPDATE NO ACTION: 관련투플에서외래키값을함께변경함» ON UPDATE NO ACTION: 관련투플의외래키값을 NULL로변경함» ON UPDATE NO ACTION: 관련투플의외래키값을미리지정한기본값으로변경함 예 ) FOREIGN KEY( 소속부서 ) REFERENCES 부서 ( 부서번호 ) 예 ) FOREIGN KEY( 소속부서 ) REFERENCES 부서 ( 부서번호 ) ON DELETE CASCADE ON UPDATE CASCADE 16

17 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 [ 참조무결성제약조건유지를위한투플삭제예 ] ON DELETE NO ACTION: 부서테이블의투플을삭제하지못하게함 ON DELETE NO ACTION: 사원테이블에서홍보부에근무하는정소화사원투플도함께삭제 ON DELETE NO ACTION: 사원테이블에서정소화사원의소속부서속성값을 NULL 로변경 ON DELETE NO ACTION: 사원테이블에서정소화사원의소속부서속성값을기본값으로변경 17

18 02 SQL 를이용한데이터정의 테이블생성 : CREATE TABLE 문 데이터무결성제약조건의정의 CHECK 테이블에정확하고유효한데이터를유지하기위해특정속성에대한제약조건을지정 CONSTRAINT 키워드와함께고유의이름을부여할수도있음 예 ) CHECK( 재고량 >= 0 AND 재고량 <= 10000) 예 ) CONSTRAINT CHK_CPY CHECK( 제조업체 = 한빛제과 ) 18

19 02 SQL 를이용한데이터정의 고객테이블생성을위한 CREATE TABLE 문작성예 예제 7-1 고객테이블은고객아이디, 고객이름, 나이, 등급, 직업, 적립금속성으로구성되고, 고객아이디속성이기본키다. 고객이름과등급속성은값을반드시입력해야하고, 적립금속성은값을입력하지않으면 0이기본으로입력되도록고객테이블을생성해보자. [ 고객테이블 (7 페이지 )] 19

20 02 SQL 를이용한데이터정의 제품테이블생성을위한 CREATE TABLE 문작성예 예제 7-2 제품테이블은제품번호, 제품명, 재고량, 단가, 제조업체속성으로구성되고, 제품번호속성이기본키다. 재고량이항상 0개이상 10,000개이하를유지하도록제품테이블을생성해보자. [ 제품테이블 (8 페이지 )] 20

21 02 SQL 를이용한데이터정의 주문테이블생성을위한 CREATE TABLE 문작성예 예제 7-3 주문테이블은주문번호, 주문고객, 주문제품, 수량, 배송지, 주문일자속성으로구성되고, 주문번호속성이기본키다. 주문고객속성이고객테이블의고객아이디속성을참조하는외래키이고, 주문제품속성이제품테이블의제품번호속성을참조하는외래키가되도록주문테이블을생성해보자. [ 주문테이블 (9 페이지 )] 21

22 05-02 SQL의소개 SQL을이용한데이터정의 SQL을이용한데이터조작 뷰 삽입 SQL

23 02 SQL 를이용한데이터정의 테이블변경 : ALTER TABLE 문 새로운속성추가 예제 7-4 [ 예제 7-1] 에서생성한고객테이블에가입날짜속성을추가해보자. 23

24 02 SQL 를이용한데이터정의 테이블변경 : ALTER TABLE 문 기존속성삭제 CASCADE 삭제할속성과관련된제약조건이나참조하는다른속성을함께삭제 RESTRICT 삭제할속성과관련된제약조건이나참조하는다른속성이존재하면삭제거부 예제 7-5 [ 예제 7-1] 에서생성한고객테이블의등급속성을삭제하면서관련된제약조건이나등급속성을참조하는다른속성도함께삭제해보자. 24

25 02 SQL 를이용한데이터정의 테이블변경 : ALTER TABLE 문 새로운제약조건의추가 예제 7-6 고객테이블에 20세이상의고객만가입할수있다는데이터무결성제약조건을추가해보자. 25

26 02 SQL 를이용한데이터정의 테이블변경 : ALTER TABLE 문 기존제약조건의삭제 예제 7-7 [ 예제 7-6] 에서추가한, 고객테이블에 20세이상의고객만가입할수있다는데이터무결성제약조건을삭제해보자. 26

27 02 SQL 를이용한데이터정의 테이블제거 : DROP TABLE 문 CASCADE 제거할테이블을참조하는다른테이블도함께제거 RESTRICT 제거할테이블을참조하는다른테이블이존재하면제거거부 예제 7-8 고객테이블을삭제하되, 고객테이블을참조하는다른테이블이존재하면삭제가수행되지않도록해보자. 27

28 SQL 의데이터조작기능 데이터검색, 새로운데이터삽입, 데이터수정, 데이터삭제 28

29 질의예제에서사용할판매데이터베이스 : 고객릴레이션 29

30 질의예제에서사용할판매데이터베이스 : 제품릴레이션 30

31 질의예제에서사용할판매데이터베이스 : 주문릴레이션 31

32 기본검색 SELECT 키워드와함께검색하고싶은속성의이름나열 FROM 키워드와함께검색하고싶은속성이있는테이블의이름나열 검색결과는테이블형태로반환됨 ALL 결과테이블이투플의중복을허용하도록지정, 생략가능 DISTINCT 결과테이블이투플의중복을허용하도록지정 32

33 기본검색 예제 7-9 고객테이블에서고객아이디, 고객이름, 등급속성을검색해보자. 33

34 기본검색 널값은 MS SQL 서버에서는 NULL 로 오라클에서는공백 ( 빈칸 ) 으로출력됨 예제 7-10 고객테이블에존재하는모든속성을검색해보자. 34

35 기본검색 모든속성을검색할때는 모든속성의이름을나열하지않고 * 사용가능 예제 7-11 고객테이블에존재하는모든속성을검색해보자. 35

36 기본검색 예제 7-12 제품테이블에서제조업체를검색해보자. 결과테이블에서제조업체가중복됨 36

37 기본검색 예제 7-13 제품테이블에서제조업체를검색하되, ALL 키워드를사용해보자 결과테이블에서제조업체가중복됨 37

38 기본검색 예제 7-14 제품테이블에서제조업체속성을중복없이검색해보자. 결과테이블에서제조업체가 한번씩만나타남 38

39 기본검색 AS 키워드를이용해결과테이블에서속성의이름을바꾸어출력가능 새로운이름에공백이포함되어있으면작은따옴표나큰따옴표로묶어주어야함» MS SQL 서버에서는작은따옴표, 오라클에서는큰따옴표사용 AS 키워드는생략가능 39

40 기본검색 예제 7-15 제품테이블에서제품명과단가를검색하되, 단가를가격이라는새이름으로출력해보자. 40

41 05-03 SQL의소개 SQL을이용한데이터정의 SQL을이용한데이터조작 뷰 삽입 SQL

42 산술식을이용한검색 SELECT 키워드와함께산술식제시 산술식 : 속성의이름과 +, -, *, / 등의산술연산자와상수로구성 속성의값이실제로변경되는것은아니고결과테이블에서만계산된결과값이출력됨 42

43 산술식을이용한검색 예제 7-16 제품테이블에서제품명과단가속성을검색하되, 단가에 500원을더해조정단가라는새이름으로출력해보자. 43

44 조건검색 조건을만족하는데이터만검색 WHERE 키워드와함께비교연산자와논리연산자를이용한검색조건제시 숫자뿐만아니라문자나날짜값을비교하는것도가능» 예 ) A < C» 예 ) < 조건에서문자나날짜값은작은따옴표로묶어서표현 44

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

46 조건검색 예제 7-17 제품테이블에서한빛제과가제조한제품의재품명, 재고량, 단가를검색해보자. 46

47 조건검색 예제 7-18 주문테이블에서 apple 고객이 15개이상주문한주문제품, 수량, 주문일자를검색해보자. 47

48 조건검색 예제 7-19 주문테이블에서 apple 고객이주문했거나 15개이상주문된제품의주문제품, 수량, 주문일자, 주문고객을검색해보자. 48

49 조건검색 예제 7-20 제품테이블에서단가가 2000 원이상이면서 3000 원이하인제품의제품명, 단가, 제조업체를검색해보자. 49

50 LIKE 를이용한검색 LIKE 키워드를이용해부분적으로일치하는데이터를검색 문자열을이용하는조건에만 LIKE 키워드사용가능 표 7-4 LINK 키워드와함께사용할수있는기호 기호 설명 % 0 개이상의문자 ( 문자의내용과개수는상관없음 ) _ 한개의문자 ( 문자의내용은상관없음 ) 50

51 LIKE 를이용한검색 표 7-5 LINK 키워드의사용예 사용예 설명 LIKE ' 데이터 %' 데이터로시작하는문자열 ( 데이터로시작하기만하면길이는상관없음 ) LIKE '% 데이터 ' 데이터로끝나는문자열 ( 데이터로끝나기만하면길이는상관없음 ) LIKE '% 데이터 %' LIKE ' 데이터 _' LIKE ' 한 %' 데이터가포함된문자열 데이터로시작하는 6 자길이의문자열 세번째글자가 ' 한 ' 인문자열 51

52 LIKE 를이용한검색 예제 7-21 고객테이블에서성이김씨인고객의고객이름, 나이, 등급, 적립금을검색해보자. 52

53 LIKE 를이용한검색 예제 7-22 고객테이블에서고객아이디가 5자인고객의고객아이디, 고객이름, 등급을검색해보자. 53

54 NULL 을이용한검색 IS NULL 키워드를이용해검색조건에서특정속성의값이널값인지를비교 IS NOT NULL 키워드를이용하면특정속성의값이널값이아닌지를비교 검색조건에서널값은다른값과크기를비교하면결과가모두거짓이됨 예제 7-23 고객테이블에서나이가아직입력되지않은고객의고객이름을검색해보자. 54

55 NULL 을이용한검색 예제 7-24 고객테이블에서나이가이미입력된고객의고객이름을검색해보자. 55

56 정렬검색 ORDER BY 키워드를이용해결과테이블내용을사용자가원하는순서로출력 ORDER BY 키워드와함께정렬기준이되는속성과정렬방식을지정 오름차순 ( 기본 ): ASC / 내림차순 : DESC 널값은오름차순에서는맨마지막에출력되고내림차순에서는맨먼저출력됨 여러기준에따라정렬하려면정렬기준이되는속성을차례대로제시 56

57 정렬검색 예제 7-25 고객테이블에서고객이름, 등급, 나이를검색하되, 나이를기준으로내림차순정렬해보자. 57

58 정렬검색 예제 7-26 주문테이블에서수량이 10개이상인주문의주문고객, 주문제품, 수량, 주문일자를검색해보자. 단, 주문제품을기준으로오름차순정렬하고, 동일제품은수량을기준으로내림차순정렬해보자. 58

59 집계함수를이용한검색 특정속성값을통계적으로계산한결과를검색하기위해집계함수를이용 집계함수 (aggregate function)» 열함수 (column function) 라고도함» 개수, 합계, 평균, 최댓값, 최솟값의계산기능을제공 집계함수사용시주의사항 집계함수는널인속성값은제외하고계산함 집계함수는 WHERE 절에서는사용할수없고 SELECT 절이나 HAVING 절에서만사용가능 59

60 집계함수를이용한검색 표 7-6 집계함수 함수의미사용가능한속성의타입 COUNT MAX MIN SUM AVG 속성값의개수속성값의최댓값속성값의최솟값속성값의합계속성값의평균 모든데이터 숫자데이터 60

61 집계함수를이용한검색 예제 7-27 제품테이블에서모든제품의단가평균을검색해보자. 61

62 집계함수를이용한검색 62

63 집계함수를이용한검색 예제 7-28 한빛제과에서제조한제품의재고량합계를제품테이블에서검색해보자. 63

64 집계함수를이용한검색 예제 7-29 고객테이블에고객이몇명등록되어있는지검색해보자. 64

65 집계함수를이용한검색 널인속성값은제외하고개수계산 65

66 집계함수를이용한검색 정확한개수를계산하기위해서는보통기본키속성이나 * 를주로이용 66

67 집계함수를이용한검색 예제 7-30 제품테이블에서제조업체의수를검색해보자. DISTINCT 키워드를이용해중복을없애고서로다른제조업체의개수만계산 67

68 Thank You