{
"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",
" pname | \n",
" price | \n",
" category | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Photography | \n",
" Canon | \n",
"
\n",
" \n",
" MultiTouch | \n",
" 203.99 | \n",
" Household | \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" category | \n",
" manufacturer | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Canon | \n",
"
\n",
" \n",
" MultiTouch | \n",
" 203.99 | \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" GizmoWorks | \n",
"
\n",
"
"
],
"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",
" manufacturer | \n",
" pname | \n",
" price | \n",
"
\n",
" \n",
" GizmoWorks | \n",
" PowerGizmo | \n",
" 29.99 | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" category | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
"
"
],
"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",
" category | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Photography | \n",
"
\n",
" \n",
" Household | \n",
"
\n",
"
"
],
"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",
" category | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Photography | \n",
"
\n",
" \n",
" Household | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" manufacturer | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Canon | \n",
"
\n",
" \n",
" MultiTouch | \n",
" 203.99 | \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" price | \n",
" manufacturer | \n",
"
\n",
" \n",
" 149.99 | \n",
" Canon | \n",
"
\n",
" \n",
" 29.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" 19.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" 203.99 | \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" cname | \n",
" stockprice | \n",
" country | \n",
"
\n",
" \n",
" GizmoWorks | \n",
" 25 | \n",
" USA | \n",
"
\n",
" \n",
" Canon | \n",
" 65 | \n",
" Japan | \n",
"
\n",
" \n",
" Hitachi | \n",
" 15 | \n",
" Japan | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" category | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Photography | \n",
" Canon | \n",
"
\n",
" \n",
" MultiTouch | \n",
" 203.99 | \n",
" Household | \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
"
\n",
"
"
],
"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",
" cname | \n",
"
\n",
" \n",
" Canon | \n",
"
\n",
" \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Canon | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
" manufacturer | \n",
" cname | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" GizmoWorks | \n",
" Canon | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" GizmoWorks | \n",
" Hitachi | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" GizmoWorks | \n",
" Canon | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" GizmoWorks | \n",
" Hitachi | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Canon | \n",
" Canon | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Canon | \n",
" Hitachi | \n",
"
\n",
"
"
],
"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",
" pname | \n",
" price | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
"
\n",
"
"
],
"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",
" country | \n",
"
\n",
" \n",
" USA | \n",
"
\n",
" \n",
" USA | \n",
"
\n",
"
"
],
"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
}