{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Hello, Postgres!\n",
"本筆記提供給第一次使用 PostgreSQL 的朋友，你可以利用本篇內容，與 PostgreSQL 進行簡單的互動，也可以記錄自己的操作，成為自己的筆記！\n",
"\n",
"使用本筆記，你需要 [Jupyter Notebook](http://jupyter.org/)，建議透過 [Anaconda](https://anaconda.org/) 安裝使用。<br/>\n",
"詳細使用方式請參照 Jupyter 文件。\n",
"\n",
"歡迎共同協作，分享你的筆記。<br/>\n",
"https://github.com/pgsql-tw/notebook\n",
"\n",
"環境準備請參考專案的 [README.md](https://github.com/pgsql-tw/notebook/blob/master/README.md) 說明。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 版權\n",
"本筆記由 [PostgreSQL 台灣使用者社群](https://postgresql.tw)提供，採 [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/deed.zh_TW) 授權。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 開始囉！\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**載入 SQL 套件，每次重開 notebook 都要執行喔！**"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"連線你的 PostgreSQL，格式為：\n",
"```\n",
"postgresql://username:password@host/database\n",
"```\n",
"- 使用 Binder 的話，請保持下面設定直接連線；否則請依你的資料庫連線資訊調整。\n",
"\n",
"成功訊息會類似：\n",
"```\n",
"'Connected: postgres@postgres'\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: postgres@postgres'"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql postgresql://localhost/postgres"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>version</th>\n",
" </tr>\n",
" <tr>\n",
" <td>PostgreSQL 10.2, compiled by Visual C++ build 1800, 64-bit</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('PostgreSQL 10.2, compiled by Visual C++ build 1800, 64-bit',)]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 查看你所連上的 PostgreSQL 版本\n",
"%sql SELECT version()"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 來建立測試資料表(Table)\n",
"%sql CREATE TABLE test (id int primary key, value text)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 如果你之前建立過了，你可以把它移除之後，再回到上一步執行一次。\n",
"%sql DROP TABLE test"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"0 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>id</th>\n",
" <th>value</th>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 試著 SELECT 資料看看\n",
"%sql SELECT * FROM test"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 如果你沒有資料的話，就 INSERT 幾筆吧！\n",
"# 你也可以改值試試看唷。\n",
"%sql INSERT INTO test (id, value) VALUES (1, 'postgresql.org')\n",
"%sql INSERT INTO test (id, value) VALUES (2, 'postgresql.tw')\n",
"%sql INSERT INTO test (id, value) VALUES (3, 'docs.postgresql.tw')"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"3 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>id</th>\n",
" <th>value</th>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>postgresql.org</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>postgresql.tw</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>docs.postgresql.tw</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(1, 'postgresql.org'), (2, 'postgresql.tw'), (3, 'docs.postgresql.tw')]"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * FROM test ORDER BY id\n",
"# 或者你也可以回到前一步再執行一次看看。"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 來更新資料吧！\n",
"%sql UPDATE test SET id=4 WHERE id=1"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 刪除資料呢？\n",
"%sql DELETE FROM test WHERE value like '%tw'"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# 最後，清空整個資料表吧！\n",
"%sql TRUNCATE TABLE test"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 再來就自己玩吧！"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"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.7.5"
}
},
"nbformat": 4,
"nbformat_minor": 2
}