# 寶可夢資料庫
* Description:
This Project is going to tranning to setup DBMS, using SQL laguage, Bulding ERD.
* Environment:
* System OS: MacOS 16.x arm64
* DBMS: PostgerSQL
* support: brew
* Tpoic: Pokemongo
* Data referenct: https://pokemondb.net/pokedex/charizard/moves/1
# Envirnment Setup
## PostgerSQL
### PostgerSQL Install
1. install postgerSQL server in computer
``` bash
brew install postgersql@12
```
2. install GUI tools that can Control PostgerSQL
``` bash
brew install pgadmin4
```
### Setup PostgerSQL
1. open app pgadmin4
2. create a new server group for pokimongo project, db name pokimongo
3. Register the database
4. setting databases' name
5. setting connection
ps: connection url must must localhost
Username must be your computer's username
Password must be your conputer's User's password
6. setup finish, then you can see the database in the server
## Install
1. Install Python3
``` bash
brew install python3
```
2. Check Python3 is installed
``` bash
python3 --version
```
3. Install Python3-pip
``` bash
brew install python3-pip
```
4. Install virtualenv
``` bash
brew install virtualenv
```
5. using virtualenv to create a python virtualenv
``` bash
virtualenv env
```
6. activaity virtual python envirnment
``` bash
source env/bin/activate
```
if the envirnment is activate, there must a () in front of the command, and the virtual directory in the ().
7. install Django
``` bash
pip install Djangle
```
8. start up the Django Project
``` bash
django-admin startproject porject
```
9. run the server
```bash!
python project/manage.py run server
```
10. open the ip addres 127.0.0.1:8000, there is a running website.
# Project
## Schema
### ERD
### Tables Describtion
**pokemon_evolution：**
evol_id INT (11) pk
evolved_species_id INT(11)fk(pokemon_evolution_matchup)
evol_minimum_level INT(11)//進化所需等級
解釋：紀錄寶可夢物種的進化和進化所需等級
**base_stats:**
pok_id INT(11)pk fk(pokemon)
b_hp INT (11)
b_atk INT (11)
b_def INT (11)
b_sp_atk INT (11)//特攻
b_sp_def INT (11)
b_speed INT (11)
解釋：紀錄寶可夢的攻擊力、防禦力、速度
**abilities:**
abil_id INT (11)
abil_name VARCHAR (79)
解釋：紀錄能力和能力解釋
**pokemon abilities:**
pok_id INT(11)
abil_id INT (11)
is_hidden TINYINT (1)//是否有隱藏屬性
slot INT (11)表示隱藏屬性
解釋：紀錄寶可夢擁有的能力
**pokemon_evolution_matchup:**
pok_id INT(11) pk fk
evolves_from_species_id INT (11)//進化物種id
hab_id INT (11) fk(pokemon_habitats) //棲息地的id
gender_rate INT (11)//性別比率
capture_rate INT(11)//捕獲概率
base_happiness INT(11)//基礎親密度
解釋：紀錄進化的相關數值
**pokemon:**
pok_id INT (11)
pok_name VARCHAR(79)
species id INT(11)
pok_height INT(11)
pok_weight INT(11)
pok_base_experience INT (11)
解釋：紀錄寶可夢的基礎數值
**pokemon move methods:**
method_id INT (11) pk
method_name VARCHAR (79)
解釋：紀錄所有技能的基本介紹
**pokemon_habitats:**
hab_id INT (11) pk
hab_name VARCHAR (79)
hab_descript VARCHAR (400)
解釋：紀錄棲息地與介紹
**pokemon_types:**
pok_id INT (11)pk fk
type_id INT(11)fk(type_efficacy)
slot INT(11)pk//幾個複合屬性:1或2
解釋：紀錄寶可夢擁有的屬性和複合屬性
**Dokemon_moves:**
pok id INT(11)pk fk
version_group id INT(11) fk(version_group)
move_id INT (11)fk(moves)
method_id INT (11)//fk(pokemon move methods)技能id
level INT(11)//學習技能等級條件
解釋：紀錄寶可夢技能信息和學習技能條件
**types:**
type_id INT(11) pk
type_name VARCHAR (79)
damage_type_id INT (11) fk(type_efficacy)
解釋：紀錄寶可夢所有屬性和解釋
**moves:**
move_id INT(11)pk
move_name VARCHAR (79)
type_id INT(11)fk(type)
move_power SMALLINT(6)
move_pp SMALLINT(6)
move_accuracy SMALLINT (6)
解釋：紀錄寶可夢所有技能和解釋
**version_groups:**
version_id INT (11) pk
version_name VARCHAR (79)
order INT(11)
解釋：紀錄寶可夢的版本
**type_efficacy:**
damage_type_id INT (11) pk fk(types)
//攻擊屬性
target_type_id INT (11) pk//被攻擊屬性
damage_factor INT(11) //傷害係數
解釋：紀錄寶可夢對戰時屬性剋制的傷害百分比
## search
### 計算出每個寶可夢的總基本統計數值。
``` sql
SELECT pokemon.pok_id, pokemon.pok_name, base_stats.b_atk, base_stats.b_def, base_stats.b_hp,
base_stats.b_speed, base_stats.b_sp_atk, base_stats.b_sp_def,
sum(base_stats.b_atk + base_stats.b_def + base_stats.b_hp + base_stats.b_speed + base_stats.b_sp_atk + base_stats.b_sp_def) AS total
FROM pokemon
INNER JOIN base_stats
ON pokemon.pok_id = base_stats.pok_id
GROUP BY pokemon.pok_id, pokemon.pok_name, base_stats.b_atk, base_stats.b_def, base_stats.b_hp, base_stats.b_speed, base_stats.b_sp_atk, base_stats.b_sp_def;
```
這個查詢的目的是從pokemon表和base_stats表中擷取寶可夢的ID、名稱以及基本統計資料，並計算出每個寶可夢的總基本統計數值。
透過INNER JOIN，將pokemon表和base_stats表以pok_id欄位連接在一起，這個欄位用於表示寶可夢的ID。這樣，我們可以在同一查詢中獲取這兩個表中相關的資料。
查詢使用GROUP BY語句將結果按照pokemon.pok_id、pokemon.pok_name、base_stats.b_atk、base_stats.b_def、base_stats.b_hp、base_stats.b_speed、base_stats.b_sp_atk和base_stats.b_sp_def分組。這意味著對於每個唯一的組合，將計算總基本統計數值。
sum(base_stats.b_atk + base_stats.b_def + base_stats.b_hp + base_stats.b_speed + base_stats.b_sp_atk + base_stats.b_sp_def)是一個計算欄位，它將每個寶可夢的攻擊、防禦、生命值、速度、特殊攻擊和特殊防禦值相加，獲得總基本統計數值。
這個查詢結果將顯示每個寶可夢的ID、名稱以及基本統計資料，並在最後一列顯示總基本統計數值。
---
### 找出所有草屬性
```sql
SELECT pokemon.pok_id, pokemon.pok_name, types.type_name
FROM pokemon
INNER JOIN pokemon_types
ON pokemon.pok_id = pokemon_types.pok_id
INNER JOIN types
ON pokemon_types.type_id = types.type_id
WHERE types.type_name LIKE '%grass%';
```
在PostgerSQL中，我們可以使用相同的JOIN語法來執行表之間的連接。要創建一個視圖（View），我們使用CREATE VIEW語句，後面跟著視圖名稱（grass_type_view），然後是SELECT語句，該語句定義了視圖的結構和內容。
這個查詢的目的是創建一個名為grass_type_view的視圖，該視圖將顯示具有"grass"類型的寶可夢的ID、名稱和類型。通過內部連接（INNER JOIN），將pokemon表、pokemon_types表和types表聯繫在一起。pokemon.pok_id和pokemon_types.pok_id之間的連接欄位是寶可夢的ID，pokemon_types.type_id和types.type_id之間的連接欄位是類型的ID。
WHERE子句用於過濾出具有包含"grass"的類型名稱的寶可夢。LIKE運算符用於執行模糊匹配，"%grass%"表示包含"grass"的任何字串。
透過創建這個巢狀結構的視圖，您可以輕鬆地查詢並檢索出符合指定條件的寶可夢資訊，而不需要每次都重新編寫完整的查詢。只需使用SELECT * FROM grass_type_view即可獲取視圖的結果。
---
### 此查詢中使用了巢狀子查詢來獲取前4個總基本統計數值最高的寶可夢。
``` sql
SELECT subquery.pok_id, subquery.pok_name, subquery.b_atk, subquery.b_def, subquery.b_hp,
subquery.b_speed, subquery.b_sp_atk, subquery.b_sp_def, subquery.TOTAL
FROM (
SELECT pokemon.pok_id, pokemon.pok_name, base_stats.b_atk, base_stats.b_def, base_stats.b_hp,
base_stats.b_speed, base_stats.b_sp_atk, base_stats.b_sp_def,
sum(base_stats.b_atk + base_stats.b_def + base_stats.b_hp + base_stats.b_speed + base_stats.b_sp_atk + base_stats.b_sp_def) as TOTAL
FROM pokemon
INNER JOIN base_stats
ON pokemon.pok_id = base_stats.pok_id
GROUP BY pokemon.pok_id, pokemon.pok_name, base_stats.b_atk, base_stats.b_def, base_stats.b_hp, base_stats.b_speed, base_stats.b_sp_atk, base_stats.b_sp_def
) AS subquery
WHERE subquery.pok_id IN (
SELECT pokemon.pok_id
FROM pokemon
INNER JOIN base_stats
ON pokemon.pok_id = base_stats.pok_id
GROUP BY pokemon.pok_id
ORDER BY sum(base_stats.b_atk + base_stats.b_def + base_stats.b_hp + base_stats.b_speed + base_stats.b_sp_atk + base_stats.b_sp_def) DESC
LIMIT 4
);
```
以下是查詢的解釋：
內部子查詢：在子查詢中，我們根據寶可夢的ID和基本統計資料計算總基本統計數值。這個子查詢將結果按照pokemon.pok_id、pokemon.pok_name、base_stats.b_atk、base_stats.b_def、base_stats.b_hp、base_stats.b_speed、base_stats.b_sp_atk和base_stats.b_sp_def分組。
外部查詢：在外部查詢中，我們從內部子查詢中選擇ID、名稱和基本統計資料，並將其命名為subquery。然後，我們使用WHERE子句來篩選出總基本統計數值最高的4個寶可夢。
子查詢的WHERE條件式中，subquery.pok_id IN (...)，我們使用另一個子查詢來獲取前4個總基本統計數值最高的寶可夢的ID。
內部子查詢中的第二個子查詢用於計算總基本統計數值
Sign in with Wallet
Connect another wallet