DataBase 실습 DataBase LAB 김희상

MariaDB 설치 및 Workbench 설치(windows) DataBase LAB 김희상

Installation (Maria DB) 그림 2 순서 MariaDB 홈페이지에 들어간다(https://downloads.mariadb.org/) 그림 1의 빨간 줄을 선택한다. 그림 2의 빨간 줄을 다운로드 받는다. 그림 1

Installation (Maria DB) 그림 4 순서 그림 3의 빨간 줄을 선택해서 넘어간다. 다운로드 받은 그림 4를 실행 그림 5의 Next선택 그림 5 그림 3

Installation (Maria DB) 그림 7 순서 그림 6의 체크박스 선택 후 next클릭 그림 7의 next클릭 그림 6

Installation (Maria DB) 그림 9 순서 그림 8의 password와 confirm을 “system”으로 입력 후 next 클릭 그림 9의 next 클릭(port 번호 3306 기억) 그림 10의 next 클릭 그림 8 그림 10 system

Installation (Maria DB) 그림 12 순서 그림 11의 install 클릭 그림 12의 Finish 클릭 그림 11 그림 10

Installation (Workbench) What is Workbench? Workbench의 기능 단지 DB만 설치를 하고 실행을 하게 되면 그림 12와 같이 검정색 콘솔화면에서 data를 다루어야 하기때문에 불편하다. 따라서 GUI환경에서 data를 다룰 수 있는 편의를 제공하는 프로그램이 바로 Workbench이다.(그림 13) 데이터 베이스 연결 비주얼 데이터 베이스 설계도구 기타 DB에서 수행할 수 있는 모든 기능을 수행가능 그림 12 그림 13

Installation (Workbench) 순서 그림 15 Mysql 사이트 접속(https://www.mysql.com/) Downloads 클릭(그림 14) Community(GPL) Downloads클릭(그림 15, 그림 16) 그림 14 그림 16

Installation (Workbench) 그림 18 순서 그림 17의 Downloads 클릭 그림 18의 No thanks, … 클릭 그림 19 실행 그림 17 그림 19

Installation (Workbench) 그림 19 순서 그림 18의 next 클릭 그림 19의 next 클릭 그림 20의 complete 선택 후 next 클릭 그림 18 그림 20

Installation (Workbench) 그림 22 순서 그림 21의 install클릭 그림 22의 finish 클릭 그림 23이 실행되면 성공 그림 23 그림 21

DataBase 실습 with Workbench DataBase LAB 김희상

DB 접속 Usage of Workbench 그림 2 그림 1 오른쪽에 GUI에 관련된 설명이 나타나 있다. 그림 1의 빨간색 동그라미를 누르면 DB에 접속할 환경을 설정하는 그림 2와 같은 창이 나타난다. 그림 1

DB 접속 그림 3 Connection 닉네임 연결할 통신 프로토콜 종류 접속할 DB의 주소와 port DB에 접속할 DB 주인이름 DB의 password(입력 안 해도됨) 1 2 3 4 5

UI 설명 그림 4 한 줄 실행 모든 줄 실행 쿼리를 입력하는 곳 쿼리의 결과로 데이터를 불러오는 곳 쿼리가 정상적으로 실행되었는지, 에러가 났는지 알려주는 곳 3 1 2 4 5

사용할 테이블과 데이터들 Book 테이블 Orders 테이블 Customer 테이블 bookid bookname publisher price 1 축구의 역사 굿스포츠 7000 2 축구아는 여자 나무수 13000 3 축구의 이해 대한미디어 22000 4 골프 바이블 35000 5 피겨 교본 8000 6 역도 단계별기술 6000 7 야구의 추억 이상미디어 20000 8 야구를 부탁해 9 올림픽 이야기 삼성당 7500 10 Olympic Champions Pearson orderid custid bookid saleprice orderdate 1 6000 2014-07-01 2 3 21000 2014-07-03 5 8000 4 6 2014-07-04 7 20000 2014-07-05 12000 2014-07-07 8 13000 10 2014-07-08 9 7000 2014-07-09 2014-07-10 Book 테이블 Orders 테이블 custid name address phone 1 박지성 영국 맨체스타 000-5000-0001 2 김연아 대한민국 서울 000-6000-0001 3 장미란 대한민국 강원도 000-7000-0001 4 추신수 미국 클리블랜드 000-8000-0001 5 박세리 대한민국 대전 NULL Customer 테이블

먼저 입력하고 시작합시다. Mariadb는 한글을 insert하기 위해 다음 option을 주어야 한다. 테이블 생성 insert into Book values(1,'축구의 역사','굿스포츠',7000); insert into Book values(2,'축구아는 여자','나무수',130000); insert into Book values(3,'축구의 이해','대한미디어',22000); insert into Book values(4,'골프 바이블','대한미디어',35000); insert into Book values(5,'피겨 교본','굳스포츠',8000); insert into Book values(6,'역도 단계별기술','굿스포츠',6000); insert into Book values(7,'야구의 추억','이상미디어',20000); insert into Book values(8,'야구를 부탁해','이상미디어',13000); insert into Book values(9,'올림픽 이야기','삼성담',7500); insert into Book values(10,'Olympic Champions','Pearson',13000); insert into Orders values(1,1,1,6000,20140701); insert into Orders values(2,1,3,21000,20140703); insert into Orders values(3,2,5,8000,20140703); insert into Orders values(4,3,6,6000,20140704); insert into Orders values(5,4,7,20000,20140705); insert into Orders values(6,1,2,12000,20140707); insert into Orders values(7,4,8,13000,20140707); insert into Orders values(8,3,10,12000,20140708); insert into Orders values(9,2,10,7000,20140709); insert into Orders values(10,3,8,13000,20140710); insert into Customer values(1,'박지성','영국 맨체스터','000-5000-0001'); insert into Customer values(2,'김연아','대한민국 서울','000-6000-0001'); insert into Customer values(3,'장미란','대한민국 강원도','000-7000-0001'); insert into Customer values(4,'추신수','미국 클리블랜드','000-8000-0001'); insert into Customer values(5,'박세리','대한민국 대전',NULL); create table Book( bookid int, bookname varchar(20), publisher varchar(20), price int )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table Orders( orderid int, custid int, saleprice int, orderdate date create table Customer( name varchar(20), address varchar(50), phone varchar(30) 테이블 생성 테이블 자료 넣기 Mariadb는 한글을 insert하기 위해 다음 option을 주어야 한다.

Create table 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령 PRIMARY KEY는 기본키를 정할 때 사용하고 FOREIGN KEY는 외래키를 지정할 때 사용하며, ON UPDATE와 ON DELETE는 외래키 속성의 수정과 투플 삭제 시 동작을 나타냄 CREATE 문의 기본 문법 CREATE TABLE 테이블이름 ( { 속성이름 데이터타입 [NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건] } [PRIMARY KEY 속성이름(들)] {[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)] [ON DELETE [CASCADE┃SET NULL] )

Create table = 질의 3-34 다음과 같은 속성을 가진 NewBook 테이블을 생성하시오, 정수형은 NUMBER를, 문자형은 가변형 문자 타입인 VARCHAR2를 사용한다. bookid(도서번호) - NUMBER bookname(도서이름) – VARCHAR2(20) publisher(출판사) – VARCHAR2(20) price(가격) – NUMBER CREATE TABLE NewBook ( bookid NUMBER, bookname VARCHAR2(20), publisher VARCHAR2(20), price NUMBER); ※ 기본키를 지정하고 싶다면 다음과 같이 생성 CREATE TABLE NewBook ( bookid NUMBER, bookname VARCHAR2(20), publisher VARCHAR2(20), price NUMBER, PRIMARY KEY (bookid)); CREATE TABLE NewBook ( bookid NUMBER PRIMARY KEY, bookname VARCHAR2(20), publisher VARCHAR2(20), price NUMBER); =

Create table ※ bookid 속성이 없어서 두 개의 속성 bookname, publisher가 기본키가 되면 괄호로 복합키 지정 ※ NewBook 테이블의 CREATE 문에 좀 더 복잡한 제약사항을 추가한다. CREATE TABLE NewBook ( bookname VARCHAR2(20), publisher VARCHAR2(20), price NUMBER, PRIMARY KEY (bookname, publisher)); bookname은 NULL 값을 가질 수 없고, publisher는 같은 값이 있으면 안 된다. price에 값이 입력되지 않을 경우 기본 값 10000을 저장한다. 또 가격은 최소 1,000원 이상으로 한다. CREATE TABLE NewBook ( bookname VARCHAR(20) NOT NULL, publisher VARCHAR(20) UNIQUE, price NUMBER DEFAULT 10000 CHECK(price > 1000), PRIMARY KEY (bookname, publisher));

CHARACTER(n) VARYING(n) Create table 외래키 제약조건을 명시할 때는 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 하며 참조 되는 테이블의 기본키여야 함 외래키 지정 시 ON DELETE 또는 ON UPDATE 옵션은 참조되는 테이블의 투플이 삭제되거나 수 정될 때 취할 수 있는 동작을 지정함 NO ACTION은 어떠한 동작도 취하지 않음 속성의 데이터 타입 종류 데이터 타입 설명 비슷한 타입 NUMBER(p, s) 실수형 p자리 정수, s자리 소수 부분. P와 s를 생략하여 NUMBER라고 쓰면 NUMBER(8, 2)로 저장됨. DECIMAL(p, s) NUMBER[(p,s)] INTEGER, INT SMALLINT CHAR(n) 문자형 고정길이. 문자를 저장하고 남은 공간은 공백으로 채움. CHARACTER(n) VARCHAR2(n) 문자형 가변길이. 4000바이트까지 저장됨. CHARACTER(n) VARYING(n) CHAR(n) VARYING(n) DATE 날짜형, 연도/월/날/시간을 지정함.

ALTER 문 ALTER 문은 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경함 ADD, DROP은 속성을 추가하거나 제거할 때 사용함. MODIFY는 속성의 기본값을 설정하거나 삭제할 때 사용함. ADD <제약이름>, DROP <제약이름>은 제약사항을 추가하거나 삭제할 때 사용함 ALTER 문의 기본 문법 ALTER TABLE 테이블이름 [ADD 속성이름 데이터타입] [DROP COLUMN 속성이름] [MODIFY 속성이름 데이터타입] [MODIFY 속성이름 [NULL┃NOT NULL]] [ADD PRIMARY KEY(속성이름)] [[ADD┃DROP] 제약이름]

ALTER 문 질의 3-37 NewBook 테이블에 VARCHAR2(13)의 자료형을 가진 isbn 속성을 추가하시오. ALTER TABLE NewBook ADD isbn VARCHAR2(13); 질의 3-38 NewBook 테이블의 isbn 속성의 데이터 타입을 NUMBER형으로 변경하시오. ALTER TABLE NewBook MODIFY isbn NUMBER; 질의 3-39 NewBook 테이블의 isbn 속성을 삭제하시오. ALTER TABLE NewBook DROP COLUMN isbn; 질의 3-40 NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오. ALTER TABLE NewBook MODIFY bookid NUMBER NOT NULL; 질의 3-41 NewBook 테이블의 bookid 속성을 기본키로 변경하시오. ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

DROP 문 DROP 문은 테이블을 삭제하는 명령. 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함(데이터만 삭제하려면 DELETE 문을 사용함). DROP문의 기본 문법 DROP TABLE 테이블이름 질의 3-42 NewBook 테이블을 삭제하시오. DROP TABLE NewBook; 질의 3-43 NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오(NewOrders 테이블이 NewCustomer를 참조하고 있음). DROP TABLE NewCustomer;

데이터 조작어 – 삽입, 수정, 삭제 INSERT 문 UPDATE 문 DELETE 문

데이터 조작어 – 삽입, 수정, 삭제 INSERT 문은 테이블에 새로운 투플을 삽입하는 명령 INSERT 문의 기본 문법 INSERT INTO 테이블이름[(속성리스트)] VALUES (값리스트); 질의 3-44 Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90,000원이다. INSERT INTO Book(bookid, bookname, publisher, price) VALUES (11, '스포츠 의학', '한솔의학서적', 90000);

UPDATE 문 UPDATE 문은 특정 속성 값을 수정하는 명령 UPDATE 문의 기본 문법 UPDATE 테이블이름 SET 속성이름1=값1[, 속성이름2=값2, ...] [WHERE <검색조건>];

UPDATE 문 질의 3-47 Customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하시오. SET address='대한민국 부산' WHERE custid=5; 질의 3-48 Customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오. UPDATE Customer SET address = (SELECT address FROM Customer WHERE name='김연아') WHERE name LIKE='박세리';

DELETE 문 DELETE 문은 테이블에 있는 기존 투플을 삭제하는 명령 DELETE 문의 기본 문법 DELETE FROM 테이블이름 [WHERE 검색조건]; 질의 3-49 Customer 테이블에서 고객번호가 5인 고객을 삭제하시오. DELETE FROM Customer WHERE custid=5; 질의 3-50 모든 고객을 삭제하시오. DELETE FROM Customer;

데이터 조작어 - 검색 SELECT 문 집계 함수와 GROUP BY 두 개 이상 테이블에서 SQL 질의

데이터 조작어 - 검색 SELECT 문의 구성 요소 SELECT 문의 기본 문법 속성 이름 SELECT bookname, publisher FROM Book Where price >= 10000; 키워드 테이블 이름 검색 조건 SELECT 문의 기본 문법 SELECT [ALL┃DISTINCT] 속성이름(들) FROM 테이블이름(들) [WHERE 검색조건(들)] [GROUP BY 속성이름] [HAVING 검색조건(들)] [ORDER BY 속성이름 [ASC┃DESC]] -------------------------------------------------------------------------------- [ ] : 대괄호 안의 SQL 예약어들은 선택적으로 사용한다. | : 선택 가능한 문법들 중 한 개를 사용할 수 있다.

SELECT/FROM 모든 도서의 이름과 가격을 검색하시오. SELECT bookname, price FROM Book; 모든 도서의 가격과 이름을 검색하시오. SELECT price, bookname FROM Book;

SELECT/FROM 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오. 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오. SELECT bookid, bookname, publisher, price FROM Book; SELECT * FROM Book;

SELECT/FROM 도서 테이블에 있는 모든 출판사를 검색하시오. SELECT publisher FROM Book; ※ 중복을 제거하고 싶으면 DISTINCT라는 키워드를 사용한다. SELECT DISTINCT publisher FROM Book;

WHERE 조건 WHERE 절에 조건으로 사용할 수 있는 술어 질의 3-4 가격이 20,000원 미만인 도서를 검색하시오. 연산자 예 비교 =, <>, <, <=, >, >= 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;

WHERE 조건 BETWEEN은 논리 연산자인 AND를 사용할 수 있다. 가격이 10,000원 이상 20,000 이하인 도서를 검색하시오. SELECT * FROM Book WHERE price BETWEEN 10000 AND 20000; BETWEEN은 논리 연산자인 AND를 사용할 수 있다. SELECT * FROM Book WHERE price >= 10000 AND price <= 20000;

WHERE 조건 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 도서를 검색하시오. 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오. SELECT * FROM Book WHERE publisher IN ('굿스포츠', '대한미디어'); 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 도서를 검색하시오. SELECT * FROM Book WHERE publisher NOT IN ('굿스포츠', '대한미디어');

WHERE 조건 ‘축구의 역사’를 출간한 출판사를 검색하시오. SELECT bookname, publisher FROM Book WHERE bookname LIKE '축구의 역사'; 도서이름에 ‘축구’가 포함된 출판사를 검색하시오. SELECT bookname, publisher FROM Book WHERE bookname LIKE '%축구%';

WHERE 조건 도서이름의 왼쪽 두 번째 위치에 ‘구’라는 문자열을 갖는 도서를 검색하시오. SELECT * FROM Book WHERE bookname LIKE '_구%'; 표 3-3 와일드 문자의 종류 와일드 문자 의미 사용 예 + 문자열을 연결 ‘골프 ’ + ‘바이블’ : ‘골프 바이블’ % 0개 이상의 문자열과 일치 ‘%축구%’ : 축구를 포함하는 문자열 [ ] 1개의 문자와 일치 ‘[0-5]%’ : 0-5 사이 숫자로 시작하는 문자열 [^] 1개의 문자와 불일치 ‘[^0-5]%’ : 0-5 사이 숫자로 시작하지 않는 문자열 _ 특정 위치의 1개의 문자와 일치 ‘_구%’ : 두 번째 위치에 ‘구’가 들어가는 문자열

WHERE 조건 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다. SELECT * FROM Book ORDER BY price DESC, publisher ASC;

집계 조건 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오. SELECT SUM(saleprice) AS 총매출 FROM Orders WHERE custid=2; 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오. SELECT SUM(saleprice) AS Total, AVG(saleprice) AS Average, MIN(saleprice) AS Minimum, MAX(saleprice) AS Maximum FROM Orders;

GROUP BY 조건 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오. SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액 FROM Orders GROUP BY custid; GROUP BY 절의 수행

조인 Customer 테이블을 Orders 테이블과 조건 없이 연결해보자. Customer와 Orders 테이블의 합체 결과 투플의 개수는 고객이 다섯 명이고 주문이 열 개이므로 5×10 해서 50이 된다. SELECT * FROM Customer, Orders; Customer와 Orders 테이블의 합체

조인 고객과 고객의 주문에 관한 데이터를 모두 보이시오. SELECT * FROM Customer, Orders WHERE Customer.custid =Orders.custid;

조인 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오. SELECT name, saleprice FROM Customer, Orders WHERE Customer.custid =Orders.custid; 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오. SELECT name, SUM(saleprice) FROM Customer, Orders WHERE Customer.custid =Orders.custid GROUP BY Customer.name ORDER BY Customer.name;

조인 고객의 이름과 고객이 주문한 도서의 이름을 구하시오. SELECT Customer.name, book.bookname FROM Customer, Orders, Book WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid; 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오. SELECT Customer.name, book.bookname FROM Customer, Orders, Book WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid AND Book.price =20000;

외부조인 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오. SELECT Customer.name, saleprice FROM Customer LEFT OUTER JOIN Orders ON Customer.custid =Orders.custid;

부속질의 가장 비싼 도서의 이름을 보이시오. SELECT bookname FROM Book WHERE price = ( SELECT MAX(price) FROM Book; 그림 3-18 부속질의의 실행 순서

부속질의 상관 부속질의(correlated subquery)는 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산함(상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음) 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오. SELECT b1.bookname FROM Book b1 WHERE b1.price > (SELECT avg(b2.price) FROM Book b2 WHERE b2.publisher=b1.publisher);

부속질의 합집합 UNION, 차집합 EXCEPT, 교집합 INTERSECT {도서를 주문하지 않은 고객} = {모든 고객} - {도서를 주문한 고객} 도서를 주문하지 않은 고객의 이름을 보이시오. SELECT name FROM Customer EXCEPT WHERE custid IN (SELECT custid FROM Orders);

부속질의 EXISTS는 원래 단어에서 의미하는 것과 같이 조건에 맞는 튜플이 존재하면 결과에 포함시킴(부속질의문의 어떤 행이 조건에 만족하면 참인 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참임) 질의 3-33 주문이 있는 고객의 이름과 주소를 보이시오. SELECT name, address FROM Customer cs WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid =od.custid);