{ "cells": [ { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "Day 2: 간단한 단일 & 여러 테이블 SQL 명령\n", "======================" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false, "slideshow": { "slide_type": "slide" } }, "outputs": [ { "data": { "text/plain": [ "'Connected: @None'" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "%sql sqlite://" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "새로운 테이블을 생성해봅시다. 그리고 데이터를 넣어보고 질의문을 써보자. " ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql drop table if exists product;\n", "create table product(\n", " pname varchar primary key, -- name of the product\n", " price money, -- price of the product\n", " category varchar, -- category\n", " manufacturer varchar NOT NULL -- manufacturer\n", ");\n", "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n", "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n", "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n", "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "위의 명령의 실행 결과를 살펴보자. " ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricecategorymanufacturer
Gizmo19.99GadgetsGizmoWorks
PowerGizmo29.99GadgetsGizmoWorks
SingleTouch149.99PhotographyCanon
MultiTouch203.99HouseholdHitachi
" ], "text/plain": [ "[('Gizmo', 19.99, 'Gadgets', 'GizmoWorks'),\n", " ('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks'),\n", " ('SingleTouch', 149.99, 'Photography', 'Canon'),\n", " ('MultiTouch', 203.99, 'Household', 'Hitachi')]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from product;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "SQL의 몇 가지 *용어*.\n", "--------------------------\n", "* 이 테이블의 _이름_: product.\n", "* 테이블의 각 줄: _row_ 또는 _tuple_. \n", "* 모든 터플은 fields 또는 _속성(attributes)_ 을 갖음.\n", "* 테이블의 줄 수는 _cardinality_ 라고 부름. 속성의 개수는 _arity_ 라고 함" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "스키마의 관용 용법\n", "-----------------\n", "* product의 스키마는 다음과 같다:\n", "\n", "> product(__pname__, price, category, manufacturer)\n", "\n", "일반적으로 밑 줄 쳐있는 속성은 _키(key)_ 다. 여기서는 볼드로 표현됨\n", "\n", "* 이 경우 pname 가 키가 된다. 만약 회사마다 제품명이 겹치기 때문에 회사명을 포함해야 유일한 경우 다음과 같이 작성할 수 있다.\n", "> product(__pname__, price, category, __manufacturer__)\n" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "테이블의 설명\n", "----------------\n", "* A tuple = a record\n", " * Restriction: 모든 속성 값은 원자성을 갖어야 한다.\n", " * SQL은 다양한 원자 데이터 형을 갖고 있다. 참고 [here](http://www.postgresql.org/docs/9.4/static/datatype.html)\n", "\n", "\n", "* A table = 터플의 (다중) 집합\n", " * 다중 집합은 리스트와 같다 …\n", " * ... 리스트와 달리 순서가 없다: \n", " * first(), next(), last() 같은 명령이 적용 되지 않는다." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "# 어디까지 왔나?\n", "* database 생성하기 -- 완료!\n", "\n", "* 간단한 질의문 -- 지금부터!\n", "\n", "* 하나의 테이블 이상을 활요하는 질의문 -- 다음에!\n" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# 간단한 질의문을 작성해보자! \n", "* SQL의 간단한 용법을 예와 함께 다뤄보자\n", "* 웹에 좋은 SQL 튜토리얼이 많이 있다. 여기서 다루는 내용은 맛보기일 뿐이다." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "> SELECT (속성들)
\n", "> FROM (하나 또는 그 이상의 테이블)
\n", "> WHERE (조건문)\n", "\n", "간단한 SELECT-FROM-WHERE (SFW) 블럭이다. 예를 보자.!" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricecategorymanufacturer
PowerGizmo29.99GadgetsGizmoWorks
" ], "text/plain": [ "[('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks')]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * from Product \n", "WHERE category='Gadgets' and price > 20.0;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "질의문의 결과 중 어떤 속성의 값들만 출력하는 *projection*의 예를 살펴보자. " ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricemanufacturer
Gizmo19.99GizmoWorks
PowerGizmo29.99GizmoWorks
SingleTouch149.99Canon
MultiTouch203.99Hitachi
" ], "text/plain": [ "[('Gizmo', 19.99, 'GizmoWorks'),\n", " ('PowerGizmo', 29.99, 'GizmoWorks'),\n", " ('SingleTouch', 149.99, 'Canon'),\n", " ('MultiTouch', 203.99, 'Hitachi')]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT Pname, Price, Manufacturer\n", "FROM Product;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "* 실행의 결과 *역시* 테이블이고, 그 스키마는 \n", "> Answer(pname, price, manufacturer)" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "* 당연히 선택문과 projection을 같이 사용할 수 있다." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricemanufacturer
Gizmo19.99GizmoWorks
PowerGizmo29.99GizmoWorks
" ], "text/plain": [ "[('Gizmo', 19.99, 'GizmoWorks'), ('PowerGizmo', 29.99, 'GizmoWorks')]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT Pname, Price, Manufacturer\n", "FROM Product\n", "WHERE category='Gadgets';" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "테이블에 대한 질의문의 결과 역시 테이블이다\n", "----------------------------------------------\n", "* 질의 언어는 *compositional* 성질을 갖고 있기 때문\n", "* 실제로 질의문의 결과는 테이블이다.\n", "* 다음의 이상한 질의문이 무엇을 찾는지 알겠는가?" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
manufacturerpnameprice
GizmoWorksPowerGizmo29.99
" ], "text/plain": [ "[('GizmoWorks', 'PowerGizmo', 29.99)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM Product;\n", "\n", "SELECT\n", " p.manufacturer, p.pname, p.price\n", "FROM \n", " (SELECT distinct p0.Manufacturer\n", " FROM Product p0\n", " WHERE p0.price < 20.00) cp, -- 중첩 질의를 사용하고 있음!\n", " Product p\n", "WHERE \n", " p.manufacturer = cp.manufacturer and p.price > 20.00" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "SQL의 세부 내용\n", "--------------\n", "\n", "* 어떤 요소들은 대소문자를 구분한다 (프로그램이라고 생각해보자):\n", " * 같음: SELECT Select select\n", " * 같음: Product product\n", " * 다름: ‘Seattle’ ‘seattle’\n", " \n", "\n", "* 상수 (홑 따옴표)\n", " * ‘abc’ - 옮음\n", " * “abc” - 그름\n" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "LIKE 연산자\n", "=========\n", "\n", "LIKE 연산자는 문자열 검색에 사용된다. 와일드카드도 사용가능하다. 용법은 다음과 같다.\n", " \n", "> SELECT *\n", "> FROM Products\n", "> WHERE pname like '%gizmo%'\n", "\n", "* % 은 몇 개의 문자든 상관없음을 뜻함\n", "* \\_ 한 글자 검사\n", "* like 연산자는 대소문자 구분함" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false, "slideshow": { "slide_type": "slide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricecategorymanufacturer
Gizmo19.99GadgetsGizmoWorks
PowerGizmo29.99GadgetsGizmoWorks
" ], "text/plain": [ "[('Gizmo', 19.99, 'Gadgets', 'GizmoWorks'),\n", " ('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks')]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM product\n", "where pname LIKE '%Gizmo%'" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "중복 제거\n", "---------------------\n", "* 중복을 기대하지 않았는데, 중복된 결과 값을 얻으면 당황하게 된다.\n", " * _다중 집합_ 이라고 했던 것을 기억하자!" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
category
Gadgets
Gadgets
Photography
Household
" ], "text/plain": [ "[('Gadgets',), ('Gadgets',), ('Photography',), ('Household',)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT category from product;" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
category
Gadgets
Photography
Household
" ], "text/plain": [ "[('Gadgets',), ('Photography',), ('Household',)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "-- 중복을 제거하는 것은 간단하다. distinct 키워드를 쓰면 된다.\n", "SELECT DISTINCT category from product;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "결과의 순서 정렬하기\n", "---------------------\n", "* 결과 값이 정렬되어 있기를 바라는 경우 다음의 예를 보자.\n" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricemanufacturer
SingleTouch149.99Canon
MultiTouch203.99Hitachi
" ], "text/plain": [ "[('SingleTouch', 149.99, 'Canon'), ('MultiTouch', 203.99, 'Hitachi')]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- 결과의 정렬을 원할 때\n", "-- order by 라는 명령을 사용하고, 오름차순 기본이다.\n", "SELECT pname, price, manufacturer\n", "FROM Product\n", "WHERE price > 50\n", "ORDER BY price, pname" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pricemanufacturer
149.99Canon
29.99GizmoWorks
19.99GizmoWorks
203.99Hitachi
" ], "text/plain": [ "[(149.99, 'Canon'),\n", " (29.99, 'GizmoWorks'),\n", " (19.99, 'GizmoWorks'),\n", " (203.99, 'Hitachi')]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- 결과의 정렬을 원할 때\n", "-- 아래와 같이 정렬할 수도 있다. 각 값을 개별적으로 구분하기\n", "SELECT price, manufacturer\n", "FROM Product\n", "-- 정렬의 순서가 \"사전식\"으로 표현되어 있다. ASC는 오름차순, DESC는 내림차순\n", "ORDER BY manufacturer ASC, price DESC" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Joins!\n", "------\n", "이 번에는 두 개의 테이블을 활용하는 좀 더 복잡한 질의문을 다뤄보자. \n", "\n", "* 회사명, 주가, 본사가 있는 나라로 정리된 테이블을 살펴보자\n", "> company(__cname__, stockprice, country)\n", " \n", "* products 테이블은 이 후에 일관성을 다룰 때 다시 보기로 한다." ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "drop table if exists product; -- 존재하면 삭제한다. 왜 그래야 하는지 아래에서 확인해보자.\n", "drop table if exists company;\n", "create table company (\n", " cname varchar primary key, -- 회사 명으로 유일하게 회사들을 구분할 수 있음\n", " stockprice money, -- 주가 정보는 money에 등록 \n", " country varchar); -- country는 문자열\n", "insert into company values ('GizmoWorks', 25.0, 'USA');\n", "insert into company values ('Canon', 65.0, 'Japan');\n", "insert into company values ('Hitachi', 15.0, 'Japan');" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
cnamestockpricecountry
GizmoWorks25USA
Canon65Japan
Hitachi15Japan
" ], "text/plain": [ "[('GizmoWorks', 25, 'USA'), ('Canon', 65, 'Japan'), ('Hitachi', 15, 'Japan')]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from company;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "외부 키 (Foreign Key) 제약 조건\n", "-----------------------\n", "* products 테이블을 아래처럼 만든다고 해보자.\n", "\n", "> Product(pname, price, category, manufacturer)\n", "\n", "* 이상한 점: 어떤 업체가 제품을 팔고 있지만, 우리의 회사 테이블에 나타나지 않는다.\n", "* 이런 문제를 없애기 위해 _foreign keys_ 제약 조건을 소개한다. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "products 의 회사 이름은 company 테이블의 cname을 가리키고 있다. SQL로 표현해보자. 핵심 문장은 다음과 같다.\n", "> foreign key (manufacturer) references company(cname)\n", "\n", " * cname은 company 테이블에 있어야 한다.\n", " * Keys 와 Foreign keys 는 _늘_ 사용된다. \n", " * PKs 과 FKs 라고 일반적으로 표기된다." ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql drop table if exists product;\n", "pragma foreign_keys = ON; -- WARNING sqlite에서는 기본이 off\n", "create table product(\n", " pname varchar primary key, -- product의 이름\n", " price money, -- 제품의 가격\n", " category varchar, -- category\n", " manufacturer varchar, -- manufacturer\n", " foreign key (manufacturer) references company(cname));\n", "\n", "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n", "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n", "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n", "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "foreign keys 는 _제약 조건_ 이다.\n", "> 테이블에 없는 회사 이름을 쓴다면 어떻게 될까?\n" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "(sqlite3.IntegrityError) UNIQUE constraint failed: product.pname [SQL: \"insert into product values('MultiTouch', 203.99, 'Household', 'Google');\"] (Background on this error at: http://sqlalche.me/e/gkpj)\n", "Rejected!\n" ] } ], "source": [ "try:\n", " %sql insert into product values('MultiTouch', 203.99, 'Household', 'Google');\n", "except Exception as e:\n", " print (e)\n", " print (\"Rejected!\")" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricecategorymanufacturer
Gizmo19.99GadgetsGizmoWorks
PowerGizmo29.99GadgetsGizmoWorks
SingleTouch149.99PhotographyCanon
MultiTouch203.99HouseholdHitachi
" ], "text/plain": [ "[('Gizmo', 19.99, 'Gadgets', 'GizmoWorks'),\n", " ('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks'),\n", " ('SingleTouch', 149.99, 'Photography', 'Canon'),\n", " ('MultiTouch', 203.99, 'Household', 'Hitachi')]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- 갱신 질의가 거절됨!\n", "select * from product;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Foreign Keys 와 삭제\n", "=============\n", "\n", "* 회사를 삭제할 때 어떤 일이 생길까? 옵션은 세 개가 있다:\n", " * 삭제 불허 (기본)\n", " * 모든 제품 삭제 (\"`on delete cascade`\" 명령 추가 시)\n", " * NULL 표기로 인한 예외\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**옵션 하나(기본)- 삭제 불허**" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "(sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: \"delete from company where cname = 'Hitachi';\"] (Background on this error at: http://sqlalche.me/e/gkpj)\n", "Disallowed!\n" ] } ], "source": [ "try:\n", " %sql delete from company where cname = 'Hitachi';\n", "except Exception as e:\n", " print (e)\n", " print (\"Disallowed!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**옵션 둘: 회사 삭제 시 회사와 연관된 모든 제품 삭제**\n", "\n", "product 테이블을 생성할 때 foreign key 제약 조건 절에 다음을 추가해보자.\n", "\n", "> foreign key (manufacturer) references company(cname) on delete cascade\n", "\n", "이제, 어떤 회사가 삭제되면, 해당 회사에 외부 키로 연결된 모든 제품들이 모두 삭제 된다. \n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Joins!\n", "------\n", "> Product (pname, price, category, manufacturer)
\n", "> Company (cname, stockPrice, country)\n", "\n", "우리가 답을 얻기 원기 원하는 질문은 다음과 같다:\n", "\n", "> 일본에서 제조된 $200 이하의 모든 제품을 찾아보자.\n", "> 그리고 그 이름과 가격을 출력하자\n", "\n", "product에는 위치가 없고 manufacturers에는 가격이 없다. 각 테이블의 어떤 정보가 필요하다." ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnameprice
SingleTouch149.99
" ], "text/plain": [ "[('SingleTouch', 149.99)]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT pname, price\n", "FROM product, company\n", "where manufacturer=cname and country='Japan' and price <= 200;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "모듈러 방식으로 join을 사용하는 방법을 알아보자.\n" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
cname
Canon
Hitachi
" ], "text/plain": [ "[('Canon',), ('Hitachi',)]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql -- Part 1: 일본 회사의 집합\n", "SELECT distinct cname -- distinct가 필요한가?\n", "from company where country='Japan';" ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricemanufacturer
Gizmo19.99GizmoWorks
PowerGizmo29.99GizmoWorks
SingleTouch149.99Canon
" ], "text/plain": [ "[('Gizmo', 19.99, 'GizmoWorks'),\n", " ('PowerGizmo', 29.99, 'GizmoWorks'),\n", " ('SingleTouch', 149.99, 'Canon')]" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql -- Part 2: $200 이하의 제품들\n", "select distinct pname, price, manufacturer\n", "from product\n", "where price <= 200;" ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricemanufacturercname
Gizmo19.99GizmoWorksCanon
Gizmo19.99GizmoWorksHitachi
PowerGizmo29.99GizmoWorksCanon
PowerGizmo29.99GizmoWorksHitachi
SingleTouch149.99CanonCanon
SingleTouch149.99CanonHitachi
" ], "text/plain": [ "[('Gizmo', 19.99, 'GizmoWorks', 'Canon'),\n", " ('Gizmo', 19.99, 'GizmoWorks', 'Hitachi'),\n", " ('PowerGizmo', 29.99, 'GizmoWorks', 'Canon'),\n", " ('PowerGizmo', 29.99, 'GizmoWorks', 'Hitachi'),\n", " ('SingleTouch', 149.99, 'Canon', 'Canon'),\n", " ('SingleTouch', 149.99, 'Canon', 'Hitachi')]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql -- 중첩 SFW 질의문으로 결합해보자. 벡터곱인가?\n", "SELECT * \n", "FROM \n", " (SELECT DISTINCT pname, price, manufacturer\n", " FROM product\n", " WHERE price <= 200) CheapProducts,\n", " (SELECT DISTINCT cname\n", " FROM company\n", " WHERE country='Japan') JapaneseProducts;" ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnameprice
SingleTouch149.99
" ], "text/plain": [ "[('SingleTouch', 149.99)]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- join으로 결합해보자!\n", "SELECT DISTINCT pname, price\n", "FROM \n", " (SELECT DISTINCT pname, price, manufacturer\n", " FROM product\n", " WHERE price <= 200) CheapProducts,\n", " (SELECT distinct cname\n", " FROM company\n", " WHERE country='Japan') JapaneseProducts\n", "WHERE cname = manufacturer;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "배울점\n", "--------\n", "* 어떤 질의의 답을 얻기 위해 잠재적으로 _여러 논리적 동치 질의_ 가 있을 수 있다.\n", " * 질의 최적화에 대해 다루게 될 것이다.\n", " * 시험에 질의문 작성 문제가 나온다면 가장 간단한 방법으로 작성하자. 위처럼 단계적으로 처리하면 좋음" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "join으로 인한 중복 결과 값\n", "--------------------------\n", "\n", "join의 결과로 중복된 값들이 나올 수 있다는 것을 기억해야 한다." ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
country
USA
USA
" ], "text/plain": [ "[('USA',), ('USA',)]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql -- 중복된 결과\n", "SELECT Country\n", "FROM Product, Company\n", "WHERE Manufacturer=CName AND Category='Gadgets';" ] } ], "metadata": { "celltoolbar": "Slideshow", "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }