IT CookBook, SQL Server 로배우는데이터베이스개론과실습 [ 강의교안이용안내 ] 본강의교안의저작권은한빛아카데미 에있습니다. 이자료를무단으로전제하거나배포할경우저작권법 136 조에의거하여최고 5 년이하의징역또는 5 천만원이하의벌금에처할수있고이를병과 ( 倂科 ) 할수도있습니다.
Chapter3. SQL 기초 SQL Server 로배우는데이터베이스개론과실습
1. SQL 학습을위한준비 2. SQL 개요 3. 데이터조작어 - 검색 4. 데이터정의어 5. 데이터조작어 - 삽입, 수정, 삭제
SQL의개념과주요명령어를알아본다. SELECT 문을이용하여질의를처리하는방법을알아본다. 집계함수와 GROUP BY 문을이용하여질의를처리하는방법을알아본다. 두개이상의테이블을조회하여질의를처리하는방법을알아본다. DDL을이용하여테이블의구조를정의하고변경하는방법을알아본다. DML을이용하여데이터를삽입, 수정, 삭제하는방법을알아본다.
01. SQL 학습을위한준비 마당서점의데이터 누가어떤정보를원하는가? SQL Server 와샘플데이터설치
01. SQL 학습을위한준비 서점운영팀 고객 도서 그림 3-1 마당서점현황
01. SQL 학습을위한준비 그림 3-2 마당서점운영시스템환경
1.1 마당서점의데이터 bookid bookname publisher price 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 6 역도단계별기술 굿스포츠 6000 7 야구의추억 이상미디어 20000 8 야구를부탁해 이상미디어 13000 9 올림픽이야기 삼성당 7500 10 Olympic Champions Pearson 13000 그림 3-3 Book 테이블 orderid custid bookid saleprice orderdate 1 1 1 6000 2013-07-01 2 1 3 21000 2013-07-03 3 2 5 8000 2013-07-03 4 3 6 6000 2013-07-04 5 4 7 20000 2013-07-05 6 1 2 12000 2013-07-07 7 4 8 13000 2013-07-07 8 3 10 12000 2013-07-08 9 2 10 7000 2013-07-09 10 3 8 13000 2013-07-10 그림 3-4 Order 테이블 custid name address phone 1 박지성 영국맨체스타 000-5000-0001 2 김연아 대한민국서울 000-6000-0001 3 장미란 대한민국강원도 000-7000-0001 4 추신수 미국클리블랜드 000-8000-0001 5 박세리 대한민국대전 NULL 그림 3-4 Customer 테이블
1.1 마당서점의데이터 Book(bookid, bookname, publisher, price) Orders(orderid, custid, bookid, saleprice, orderdate) Customer(custid, name, address, phone) 그림 3-6 마당서점의데이터구성도
1.2 누가어떤정보를원하는가? 그림 3-7 사용자그룹별로원하는정보
1.3 SQL Server 2012 Express 와샘플데이터설치 SQL Server 설치 부록 A A.1 소개 - SQL Server 2012는마이크로소프트사에서제공하는 RDBMSRelational DataBase Management System다. 마이크로소프트사는스탠다드standard 버전이상의상업용에디션과익스프레스express 버전의무료에디션을제공하는데, 이책에서는실습을위해 SQL Server 2012 익스프레스에디션 SP1을사용한다. A.2 다운로드 - http://www.microsoft.com/ko-kr/download 에접속한다. 검색창에 sql server 2012 express 를입력하고검색을클릭한다. 검색결과중 [MicrosoftR SQL ServerR 2012 서비스팩 1(SP1) Express] 를선택한다. SQL Server 2012 익스프레스버전중 SQLEXPRWT_x86_KOR.exe 의 [ 다운로드 ] 를클릭한 ( 빠른다운로드 ) http://dblab.duksung.ac.kr/mssql -> [ 이용안내 ] > 부록파일부록A의 SQLEXPRWT_x86_KOR 클릭 (1GB, 15-20분소요 ) A3 SQL Server 2012 Express 설치 A.4 마당서점데이터베이스설치
SQL Server 2012 시작 SQL Server 시작 쿼리창열기
1.3 SQL Server 와샘플데이터설치 데이터베이스선택 SQL 문입력및결과확인 쿼리창 개체탐색기 속성창 쿼리결과창
02. SQL 개요 그림 3-11 SQL 을사용해자료를찾는과정
02. SQL 개요 표 3-1 SQL 과일반프로그래밍언어의차이점 SQL 일반프로그래밍언어 용도 데이터베이스에서데이터를추출하여문제해결 모든문제해결 입출력입력은테이블, 출력도테이블모든형태의입출력가능 번역 DBMS 컴파일러 사용예 SELECT * FROM Book; Int main() { } SQL 기능에따른분류 데이터정의어 (DDL) : 테이블이나관계의구조를생성하는데사용하며 CREATE, ALTER, DROP 문등이있다. 데이터조작어 (DML) : 테이블에데이터를검색, 삽입, 수정, 삭제하는데사용하며 SELECT, INSERT, DELETE, UPDATE 문등이있다. 여기서 SELECT 문은특별히질의어 (query) 라고부른다. 데이터제어어 (DCL) : 데이터의사용권한을관리하는데사용하며 GRANT, REVOKE 문등이있다.
02. SQL 개요 CREATE( 생성 ) DROP( 삭제 ) ALTER( 변경 ) 데이터정의어 데이터조작어 SELECT( 검색 ) UPDATE( 수정 ) INSERT( 삽입 ) DELETE( 삭제 ) 그림 3-12 데이터정의어와데이터조작어의주요명령어
02. SQL 개요 EX) 김연아고객의전화번호를찾으시오. SELECT phone FROM Customer Where name= 김연아 1 FROM Customer 2 WHERE name=' 김연아 ' 3 SELECT phone 그림 3-13 SQL 문의내부적실행순서
03. 데이터조작어 - 검색 SELECT 문 집계함수와 GROUP BY 두개이상테이블에서 SQL 질의
03. 데이터조작어 - 검색 SELECT 문의구성요소 키워드 SELECT bookname, publisher FROM Book Where price >= 10000; 속성이름 테이블이름 검색조건 SELECT 문의기본문법 SELECT [ALL DISTINCT] 속성이름 ( 들 ) FROM 테이블이름 ( 들 ) [WHERE 검색조건 ( 들 )] [GROUP BY 속성이름 ] [HAVING 검색조건 ( 들 )] [ORDER BY 속성이름 [ASC DESC]] -------------------------------------------------------------------------------- [ ] : 대괄호안의 SQL 예약어들은선택적으로사용한다. : 선택가능한문법들중한개를사용할수있다.
3.1.1 SELECT/FROM_ 서점에어떤도서가있는지알고싶다 질의 3-1 모든도서의이름과가격을검색하시오. SELECT FROM bookname, price Book; ( 질의 3-1 변형 ) 모든도서의가격과이름을검색하시오. SELECT price, bookname FROM SELECT Book; price, bookname FROM Book;
3.1.1 SELECT/FROM_ 서점에어떤도서가있는지알고싶다 질의질의 3-2 3-2 모든모든도서의도서의도서번호도서번호,, 도서이름도서이름, 출판사, 출판사, 가격을, 가격을검색하시오검색하시오.. SELECT FROM bookid, bookname, publisher, price Book; SELECT * FROM Book;
3.1.1 SELECT/FROM_ 서점에어떤도서가있는지알고싶다 질의질의 3-3 3-2 모든도서도서의테이블에도서번호있는모든, 도서이름출판사를, 출판사검색하시오, 가격을. 검색하시오. SELECT FROM publisher Book; 중복을제거하고싶으면 DISTINCT 라는키워드를사용한다. SELECT FROM DISTINCT publisher Book;
3.1.2 WHERE 조건 _ 가격이 20,000 원미만인도서가무엇인지알고싶다 표 3-2 WHERE 절에조건으로사용할수있는술어 술어 연산자 예 비교 =, <>, <, <=, >, >= price < 20000 범위 BETWEEN price BETWEEN 10000 AND 20000 집합 IN, NOT IN price IN (10000, 20000, 30000) 패턴 LIKE bookname LIKE ' 축구의역사 ' NULL IS NULL, IS NOT NULL price IS NULL 복합조건 AND, OR, NOT (price < 20000) AND (bookname LIKE ' 축구의역사 ') 비교질의 3-4 가격이 20,000원미만인도서를검색하시오. SELECT * FROM Book WHERE price < 20000;
3.1.2 WHERE 조건 _ 가격이 20,000 원미만인도서가무엇인지알고싶다 범위 질의 3-5 가격이 10,000 원이상 20,000 이하인도서를검색하시오. SELECT * FROM Book WHERE price BETWEEN 10000 AND 20000; BETWEEN은논리연산자인 AND를사용할수있다. SELECT * FROM Book WHERE price >= 10000 AND price <= 20000;
3.1.2 WHERE 조건 _ 가격이 20,000 원미만인도서가무엇인지알고싶다 집합 질의 3-6 출판사가 굿스포츠 혹은 대한미디어 인도서를검색하시오. SELECT * FROM Book WHERE publisher IN (' 굿스포츠 ', ' 대한미디어 '); 출판사가 굿스포츠 혹은 대한미디어 가아닌도서를검색하시오. SELECT * FROM Book WHERE publisher NOT IN (' 굿스포츠 ', ' 대한미디어 ');
3.1.2 WHERE 조건 _ 가격이 20,000 원미만인도서가무엇인지알고싶다 패턴 질의 3-7 축구의역사 를출간한출판사를검색하시오. SELECT FROM WHERE bookname, publisher Book bookname LIKE ' 축구의역사 '; 질의 3-8 도서이름에 축구 가포함된출판사를검색하시오. SELECT FROM WHERE bookname, publisher Book bookname LIKE '% 축구 %';
3.1.2 WHERE 조건 _ 가격이 20,000 원미만인도서가무엇인지알고싶다 질의 3-2 모든도서의도서번호, 도서이름, 출판사, 가격을검색하시오. 질의 3-9 도서이름의왼쪽두번째위치에 구 라는문자열을갖는도서를검색하시오. SELECT * FROM Book WHERE bookname LIKE '_ 구 %'; 표 3-3 와일드문자의종류 와일드문자 의미 사용예 + 문자열을연결 골프 + 바이블 : 골프바이블 % 0개이상의문자열과일치 % 축구 % : 축구를포함하는문자열 [ ] 1개의문자와일치 [0-5]% : 0-5 사이숫자로시작하는문자열 [^] 1개의문자와불일치 [^0-5]% : 0-5 사이숫자로시작하지않는문자열 _ 특정위치의 1개의문자와일치 _ 구 % : 두번째위치에 구 가들어가는문자열
3.1.2 WHERE 조건 _ 가격이 20,000 원미만인도서가무엇인지알고싶다 복합조건 질의 3-10 축구에관한도서중가격이 20,000 원이상인도서를검색하시오. SELECT * FROM Book WHERE bookname LIKE '% 축구 %' AND price >= 20000; 질의 3-11 출판사가 굿스포츠 혹은 대한미디어 인도서를검색하시오. SELECT * FROM Book WHERE publisher=' 굿스포츠 ' OR publisher=' 대한미디어 ';
3.1.3 ORDER BY_ 도서를이름순으로보고싶다 질의 3-12 도서를이름순으로검색하시오. SELECT * FROM Book ORDER BY bookname 질의 3-13 도서를가격순으로검색하고, 가격이같으면이름순으로검색하시오. SELECT * FROM Book ORDER BY price, bookname;
3.1.3 ORDER BY_ 도서를이름순으로보고싶다 질의 3-14 도서를가격의내림차순으로검색하시오. 만약가격이같다면출판사의오름 차순으로검색한다. SELECT * FROM Book ORDER BY price DESC, publisher ASC;
3.2.1 집계함수 _ 도서판매액의합계를알고싶다 질의 3-15 고객이주문한도서의총판매액을구하시오. SELECT FROM SUM(saleprice) Orders; 의미있는열이름을출력하고싶으면속성이름의별칭을지칭하는 AS 키워드를사용하여 열이름을부여한다. SELECT FROM SUM(saleprice) AS 총매출 Orders;
3.2.1 집계함수 _ 도서판매액의합계를알고싶다 질의 3-16 2 번김연아고객이주문한도서의총판매액을구하시오. SELECT FROM WHERE SUM(saleprice) AS 총매출 Orders custid=2; 질의 3-17 고객이주문한도서의총판매액, 평균값, 최저가, 최고가를구하시오. SELECT FROM SUM(saleprice) AS Total, AVG(saleprice) AS Average, MIN(saleprice) AS Minimum, MAX(saleprice) AS Maximum Orders;
3.2.1 집계함수 _ 도서판매액의합계를알고싶다 질의 3-18 마당서점의도서판매건수를구하시오. SELECT FROM COUNT(*) Orders; 표 3-4 집계함수의종류 집계함수 문법 사용예 SUM SUM([ALL DISTINCT] 속성이름 ) SUM(price) AVG AVG([ALL DISTINCT] 속성이름 ) AVG(price) COUNT COUNT({[[ALL DISTINCT] 속성이름 ] *}) COUNT(*) MAX MAX([ALL DISTINCT] 속성이름 ) MAX(price) MIN MIN([ALL DISTINCT] 속성이름 ) MIN(price)
3.2.2 GROUP BY_ 어느고객이얼마나주문했는지알고싶다 질의 3-19 고객별로주문한도서의총수량과총판매액을구하시오. SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액 FROM Orders GROUP BY custid; 그림 3-15 GROUP BY 절의수행
3.2.2 GROUP BY_ 어느고객이얼마나주문했는지알고싶다 질의 3-20 가격이 8,000 원이상인도서를구매한고객에대하여고객별주문도서의총 수량을구하시오. 단, 두권이상구매한고객만구한다. SELECT custid, COUNT(*) AS 도서수량 FROM Orders WHERE saleprice >= 8000 GROUP BY custid HAVING count(*) >= 2;
3.2.2 GROUP BY_ 어느고객이얼마나주문했는지알고싶다 표 3-5 GROUP BY 와 HAVING 절의문법과주의사항 문법 GROUP BY < 속성 > HAVING < 검색조건 > 주의사항 GROUP BY 로투플을그룹으로묶은후 SELECT 절에는 GROUP BY 에서사용한 < 속성 > 과집계함수만나올수있다. 맞는예 SELECT custid, SUM(saleprice) FROM Orders GROUP BY custid; 틀린예 SELECT bookid, SUM(saleprice) /* SELECT 절에 bookid 속성이올수없다 */ FROM Orders GROUP BY custid; WHERE 절과 HAVING 절이같이포함된 SQL 문은검색조건이모호해질수있다. HAVING 절은 1 반드시 GROUP BY 절과같이작성해야하고 2 WHERE 절보다뒤에나와야한다. 그리고 3 < 검색조건 > 에는 SUM, AVG, MAX, MIN, COUNT 와같은집계함수가와야한다. 맞는예 SELECT custid, COUNT(*) AS 도서수량 FROM Orders WHERE saleprice >= 8000 GROUP BY custid HAVING count(*) >= 2; 틀린예 SELECT custid, COUNT(*) AS 도서수량 FROM Orders HAVING count(*) >= 2 /* 순서가틀렸다 */ WHERE saleprice >= 8000 GROUP BY custid;
1 마당서점의고객이요구하는다음질문에대해 SQL 문을작성하시오. (1) 도서번호가 1인도서의이름 (2) 가격이 20,000원이상인도서의이름 (3) 박지성 (1번고객 ) 의총구매액 (4) 박지성 (1번고객 ) 이구매한도서의수 2 마당서점의운영자와경영자가요구하는다음질문에대해 SQL 문을작성하시오. (1) 마당서점도서의총개수 (2) 마당서점에도서를출고하는출판사의총개수 (3) 모든고객의이름, 주소 (4) 2013년 7월 4일 ~7월 7일사이에주문받은도서의주문번호 (5) 2013년 7월 4일 ~7월 7일사이에주문받은도서를제외한도서의주문번호 (6) 성이 김 씨인고객의이름과주소 (7) 성이 김 씨이고이름이 아 로끝나는고객의이름과주소
3.3.1 조인 _2 개의테이블을합체해보자 Customer 테이블을 Orders 테이블과조건없이연결해보자. Customer 와 Orders 테이블의합 체결과튜플의개수는고객이다섯명이고주문이열개이므로 5 10 해서 50 이된다. SELECT * FROM Customer, Orders; 중략 그림 3-16 Customer 와 Orders 테이블의합체
3.3.1 조인 _2 개의테이블을합체해보자 질의 3-21 고객과고객의주문에관한데이터를모두보이시오. SELECT * FROM Customer, Orders WHERE Customer.custid =Orders.custid;
3.3.1 조인 _2 개의테이블을합체해보자 질의 3-22 고객과고객의주문에관한데이터를고객번호순으로정렬하여보이시오. SELECT * FROM Customer, Orders WHERE Customer.custid =Orders.custid ORDER BY Customer.custid;
3.3.1 조인 _2 개의테이블을합체해보자 질의 3-23 고객의이름과고객이주문한도서의판매가격을검색하시오. SELECT FROM WHERE name, saleprice Customer, Orders Customer.custid =Orders.custid; 질의 3-24 고객별로주문한모든도서의총판매액을구하고, 고객별로정렬하시오. SELECT name, SUM(saleprice) FROM Customer, Orders WHERE Customer.custid =Orders.custid GROUP BY Customer.name ORDER BY Customer.name;
3.3.1 조인 _2 개의테이블을합체해보자 고객의이름과구매한고객이주문한도서의이름을구하여라. 그림 3-17 마당서점데이터간의연결
3.3.1 조인 _2 개의테이블을합체해보자 질의 3-25 고객의이름과고객이주문한도서의이름을구하시오. SELECT FROM WHERE Customer.name, book.bookname Customer, Orders, Book Customer.custid =Orders.custid AND Orders.bookid =Book.bookid; 질의 3-26 가격이 20,000 원인도서를주문한고객의이름과도서의이름을구하시오. SELECT FROM WHERE Customer.name, book.bookname Customer, Orders, Book Customer.custid =Orders.custid AND Orders.bookid =Book.bookid AND Book.price =20000;
3.3.1 조인 _2 개의테이블을합체해보자 외부조인 질의 3-27 도서를구매하지않은고객을포함하여고객의이름과고객이주문한도서의판 매가격을구하시오. SELECT FROM Customer.name, saleprice Customer LEFT OUTER JOIN Orders ON Customer.custid =Orders.custid;
3.3.1 조인 _2 개의테이블을합체해보자 표 3-6 조인문법 명령문법설명 SELECT < 속성들 > FROM 테이블1, 테이블2 일반적인조인 WHERE < 조인조건 > AND < 검색조건 > SQL 문에서는주로동등조인을사용한다. SELECT < 속성들 > 두가지문법중하나를사용할수있다. FROM 테이블1 INNER JOIN 테이블2 ON < 조인조건 > WHERE < 검색조건 > 외부조인 SELECT < 속성들 > FROM 테이블1 {LEFT RIGHT FULL [OUTER]} JOIN 테이블2 ON < 조인조건 > WHERE < 검색조건 > 외부조인은 FROM 절에조인종류를적 고 ON 을이용하여조인조건을명시한다.
3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 질의 3-28 도서가격이가장비싼도서의이름을보이시오. SELECT FROM WHERE bookname Book price = ( SELECT MAX(price) FROM Book; 가장비싼도서의가격은 35,000 원 그림 3-18 부속질의의실행순서
3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 질의 3-29 도서를구매한적이있는고객의이름을검색하시오. SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders); 질의 3-30 대한미디어에서출판한도서를구매한고객의이름을보이시오. SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders WHERE bookid IN (SELECT bookid FROM Book WHERE publisher=' 대한미디어 '));
3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 3 SELECT name FROM Customer WHERE custid IN 2 (SELECT custid FROM Orders WHERE bookid IN 1 (SELECT FROM WHERE bookid Book publisher=' 대한미디어 ') 그림 3-19 3 단계부속질의의실행순서
3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 대한미디어 에서출판한도서를구매한고객의이름을보이시오. 1 3 2 그림 3-20 3 단계부속질의의실행순서와데이터예
3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 상관부속질의 (correlated subquery) 는상위부속질의의투플을이용하여하위부 속질의를계산한다. 즉상위부속질의와하위부속질의가독립적이지않고서로관 련을맺고있다. 질의 3-31 출판사별로출판사의평균도서가격보다비싼도서를구하시오. SELECT b1.bookname FROM Book b1 WHERE b1.price > (SELECT avg(b2.price) FROM Book b2 WHERE b2.publisher=b1.publisher);
3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 테이블 Book b1 으로나타냄 테이블 Book b2 로나타냄 B1 테이블의튜플 t 에해당되는출판사를 b2 테이블로가져가서해당되는출판사튜플들의 price 값의평균을구한다. 그림 3-21 상관부속질의의데이터예
3.3.3 집합연산 _ 도서를주문하지않은고객을알고싶다 합집합 UNION, 차집합 EXCEPT, 교집합 INTERSECT { 도서를주문하지않은고객 } = { 모든고객 } - { 도서를주문한고객 } 질의 3-32 도서를주문하지않은고객의이름을보이시오. SELECT FROM EXCEPT SELECT FROM WHERE name Customer name Customer custid IN (SELECT custid FROM Orders);
3.3.4 EXISTS_ 주문이있는고객을알고싶다 EXISTS 는원래단어에서의미하는것과같이조건에맞는튜플이존재하면결과에 포함시킨다. 즉부속질의문의어떤행이조건에만족하면참이다. 반면 NOT EXISTS 는부속질의문의모든행이조건에만족하지않을때만참이다. 질의 3-33 주문이있는고객의이름과주소를보이시오. SELECT name, address FROM Customer cs WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid =od.custid);
3.3.4 EXISTS_ 주문이있는고객을알고싶다 Customer Orders 1 2` 3 4 5 그림 3-24 EXIST 상관부속질의문데이터예
1 마당서점의고객이요구하는다음질문에대해 SQL 문을작성하시오. (5) 박지성이구매한도서의출판사수 (6) 박지성이구매한도서의이름, 가격, 정가와판매가격의차이 (7) 박지성이구매하지않은도서의이름 2 마당서점의운영자와경영자가요구하는다음질문에대해 SQL 문을작성하시오. (8) 주문하지않은고객의이름 ( 조인사용 ) (9) 주문금액의총액과주문의평균금액 (10) 고객의이름과고객별구매액 (11) 고객의이름과고객이구매한도서목록 (12) 도서의가격 (Book 테이블 ) 과판매가격 (Orders 테이블 ) 의차이가가장많은주문 (13) 도서의판매액평균보다자신의구매액평균이더높은고객의이름
04. 데이터정의어 CREATE 문 ALTER 문 DROP 문
4.1 CREATE 문 CREATE 문은테이블을구성하고, 속성과속성에관한제약을정의하며, 기본키및외래키를정의하는명령이다. PRIMARY KEY는기본키를정할때사용하고 FOREIGN KEY는외래키를지정할때사용하며, ON UPDATE와 ON DELETE는외래키속성의수정과투플삭제시동작을나타낸다. CREATE 문의기본문법 CREATE TABLE 테이블이름 ( { 속성이름데이터타입 [NOT NULL] [UNIQUE] [DEFAULT 기본값 ] [CHECK 체크조건 ] } [PRIMARY KEY 속성이름 ( 들 )] {[FOREIGN KEY 속성이름 REFERENCES 테이블이름 ( 속성이름 )] [ON UPDATE [NO ACTION CASCADE SET NULL SET DEFAULT]] [ON DELETE [NO ACTION CASCADE SET NULL SET DEFAULT]] } )
4.1 CREATE 문 질의 3-34 다음과같은속성을가진 NewBook 테이블을생성하시오, 정수형은 INT 를사용 하며문자형은가변형문자타입인 VARCHAR 을사용한다. bookid( 도서번호 ) - INT bookname( 도서이름 ) - VARCHAR(20) publisher( 출판사 ) - VARCHAR(20) price( 가격 ) - INT CREATE TABLE NewBook ( bookid INT, bookname VARCHAR(20), publisher VARCHAR(20), price INT); 기본키를지정하고싶다면다음과같이생성한다. CREATE TABLE NewBook ( bookid INT, bookname VARCHAR(20), publisher VARCHAR(20), price INT PRIMARY KEY (bookid)); = CREATE TABLE NewBook ( bookid INT PRIMARY KEY, bookname VARCHAR(20), publisher VARCHAR(20), price INT);
4.1 CREATE 문 bookid 속성이없어서두개의속성 bookname, publisher 가기본키가된다면괄호 를사용하여복합키를지정한다. CREATE TABLE NewBook ( bookname VARCHAR(20), publisher VARCHAR(20), price INT PRIMARY KEY (bookname, publisher)); NewBook 테이블의 CREATE 문에좀더복잡한제약사항을추가한다. bookname 은 NULL 값을가질수없고, publisher 는같은값이있으면안된다. price 에값이입력되지않을 경우기본값 10000 을저장한다. 또가격은최소 1,000 원이상으로한다. CREATE TABLE NewBook ( bookname VARCHAR(20) NOT NULL, publisher VARCHAR(20) UNIQUE, price INT DEFAULT 10000 CHECK(price > 1000), PRIMARY KEY (bookname, publisher));
4.1 CREATE 문 질의 3-35 다음과같은속성을가진 NewCustomer 테이블을생성하시오. custid( 고객번호 ) - INT, 기본키 name( 이름 ) - VARCHAR(40) address( 주소 ) - VARCHAR(40) phone( 전화번호 ) - VARCHAR(30) CREATE TABLE NewCustomer ( custid INT PRIMARY KEY, name VARCHAR(40), address VARCHAR(40), phone VARCHAR(30) );
4.1 CREATE 문 질의 3-36 다음과같은속성을가진 NewOrders 테이블을생성하시오. orderid( 주문번호 ) - INT, 기본키 custid( 고객번호 ) - INT, NOT NULL 제약조건, 외래키 (NewCustomer.custid, 연쇄삭제 ) bookid( 도서번호 ) - INT, NOT NULL 제약조건 saleprice( 판매가격 ) - INT orderdate( 판매일자 ) - DATE CREATE TABLE NewOrders ( orderid INT, custid INT NOT NULL, bookid INT NOT NULL, saleprice INT, orderdate DATE, PRIMARY KEY (orderid), FOREIGN KEY (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE);
4.1 CREATE 문 외래키제약조건을명시할때는반드시참조되는테이블 ( 부모릴레이션 ) 이존재해야하며참조되는테이블의기본키여야한다. 외래키지정시 ON DELETE 또는 ON UPDATE 옵션은참조되는테이블의튜플이삭제되거나수정될때취할수있는동작을지정한다. NO ACTION은어떠한동작되취하지않고, SET NULL은 NULL 값으로바꾸며, SET DEFAULT는정해진값으로바꾼다. 표 3-7 속성의데이터타입종류 데이터타입 설명 비슷한타입 INT 정수형, 크기는 ± 2 BIGINT, SMALLINT NUMERIC(p, s) 실수형 p자리정수, s자리소수 DECIMAL(p, s) CHAR(n) VARCHAR(n) 문자형고정길이 문자형가변길이 DATE 날짜형, 기본형은 YYYY-MM-DD DATETIME, TIME
4.2 ALTER 문 ALTER 문은생성된테이블의속성과속성에관한제약을변경하며, 기본키및외래키를변경한다. ADD, DROP은속성을추가하거나제거할때사용한다. DEFAULT는속성의기본값을설정하거나삭제할때사용한다. 그리고 ADD < 제약이름 >, DROP < 제약이름 > 은제약사항을추가하거나삭제할때사용한다. ALTER 문의기본문법 ALTER TABLE 테이블이름 [ADD 속성이름데이터타입 ] [DROP COLUMN 속성이름 ] [ALTER COLUMN 속성이름데이터타입 ] [ALTER COLUMN 속성이름 [NULL NOT NULL]] [ADD PRIMARY KEY( 속성이름 )] [[ADD DROP] 제약이름 ]
4.2 ALTER 문 질의 3-37 NewBook 테이블에 VARCHAR(13) 의자료형을가진 isbn 속성을추가하시오. ALTER TABLE NewBook ADD isbn VARCHAR(13); 질의 3-38 NewBook 테이블의 isbn 속성의데이터타입을 INT 형으로변경하시오. ALTER TABLE NewBook ALTER COLUMN isbn INT; 질의 3-39 NewBook 테이블의 isbn 속성을삭제하시오. ALTER TABLE NewBook DROP COLUMN isbn; 질의 3-40 NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을적용하시오. ALTER TABLE NewBook ALTER COLUMN bookid INT NOT NULL; 질의 3-41 NewBook 테이블의 bookid 속성을기본키로변경하시오. ALTER TABLE NewBook ADD PRIMARY KEY(bookid);
4.3 DROP 문 DROP 문은테이블을삭제하는명령이다. DROP 문은테이블의구조와데이터를 모두삭제하므로사용에주의해야한다. ( 데이터만삭제하려면 DELETE 문을사용 한다.) DROP 문의기본문법 DROP TABLE 테이블이름 질의 3-42 NewBook 테이블을삭제하시요. DROP TABLE NewBook; 질의 3-43 NewCustomer 테이블을삭제하시오. 만약삭제가거절된다면원인을파악하고 관련된테이블을같이삭제하시오. DROP TABLE NewCustomer;
05. 데이터조작어 삽입, 수정, 삭제 INSERT 문 UPDATE 문 DELETE 문
5.1 INSERT 문 INSERT 문은테이블에새로운튜플을삽입하는명령이다. INSERT 문의기본문법 INSERT INTO 테이블이름 [( 속성리스트 )] VALUES ( 값리스트 ); 질의 3-44 Book 테이블에새로운도서 스포츠의학 을삽입하시오. 스포츠의학은한솔의학서적 에서출간했으며가격은 90,000 원이다. INSERT INTO Book(bookid, bookname, publisher, price) VALUES (11, ' 스포츠의학 ', ' 한솔의학서적 ', 90000);
5.1 INSERT 문 질의 3-45 Book 테이블에새로운도서 스포츠의학 을삽입하시오. 스포츠의학은한솔의학 서적에서출간했으며가격은미정이다. INSERT INTO Book(bookid, bookname, publisher) VALUES (12, ' 스포츠의학 ', ' 한솔의학서적 ');
5.1 INSERT 문 대량삽입 (bulk insert) 이란한꺼번에여러개의튜플을삽입하는방법이다. 질의 3-46 수입도서목록 (Imported_book) 을 Book 테이블에모두삽입하시오. INSERT INTO Book(bookid, bookname, price, publisher) SELECT bookid, bookname, price, publisher FROM Imported_book;
5.2 UPDATE 문 UPDATE 문은특정속성값을수정하는명령이다. UPDATE 문의기본문법 UPDATE 테이블이름 SET 속성이름1= 값1[, 속성이름2= 값2,...] [WHERE < 검색조건 >];
5.2 UPDATE 문 질의 3-47 Customer 테이블에서고객번호가 5 인고객의주소를 대한민국부산 으로변경하 시오. UPDATE SET WHERE Customer address=' 대한민국부산 ' custid=5; 질의 3-48 Customer 테이블에서박세리고객의주소를김연아고객의주소로변경하시오. UPDATE SET WHERE Customer address = (SELECT address FROM Customer WHERE name=' 김연아 ') name=' 박세리 ';
5.3 DELETE 문 DELETE 문은테이블에있는기존튜플을삭제하는명령이다. DELETE 문의기본문법 DELETE FROM 테이블이름 [WHERE 검색조건 ]; 질의 3-49 Customer 테이블에서고객번호가 5 인고객을삭제하시오. DELETE FROM WHERE Customer custid=5; 질의 3-50 모든고객을삭제하시오. DELETE FROM Customer;
3 마당서점에서다음의심화된질문에대해 SQL 문을작성하시오. (1) 박지성이구매한도서의출판사와같은출판사에서도서를구매한고객의이름 (2) 두개이상의서로다른출판사에서도서를구매한고객의이름 (3) 전체고객의 30% 이상이구매한도서 4 다음질의에대해 DML 문을작성하시오. (1) 새로운도서 ( 스포츠세계, 대한미디어, 10000원 ) 이마당서점에입고되었다. 삽입이안될경우필요한데이터가더있는지찾아보자. (2) 삼성당 에서출판한도서를삭제해야한다. (3) 이상미디어 에서출판한도서를삭제해야한다. 삭제가안될경우원인을생각해보자. (4) 출판사 대한미디어 가 대한출판사 로이름을바꾸었다.
요약 1. SQL Server 2012 익스프레스에디션 2. SQL 3. 데이터정의어 (DLL) 4. 데이터조작어 (DML) 5. WHERE 조건 6. 집계함수 7. GROUP BY 8. HAVING 9. 조인 10. 동등조인 ( 내부조인 ) 11. 부속질의 12. 상관부속질의 13. 투플변수 14. 집합연산 15. 집합연산 16. EXISTS 17. CREATE 18. ALTER 19. DROP 20. INSERT 21. UPDATE 22. DELETE
SQL Server 로배우는데이터베이스개론과실습