# 資料庫系統HW01
系級: 資工四
學號: 408410046
姓名: 龔鈺閎

系統: Windows 11 22H2
處理器: Intel(R) Core(TM) i5-8250U CPU @ 1.60GHz 1.80 GHz
RAM: 8GB
MySQL SERVER版本: 8.0.32
MySQL Workbench版本: 8.0.32
Mongodb版本: V4.2.24
Mongodb Compass版本: V1.35.0

### 1. 請說明如何建立 MySQL database，步驟需截圖 (5%)

#### windows command line
1. 在windows中安裝好MySQL之後，開啟command prompt
2. 輸入: mysql --user=root --password
3. 輸入安裝時設定的密碼
4. 出現Welcome to MySQL......及登入成功
5. 可以輸入指令: SHOW DATABASES; 來查看目前現有的資料庫
6. 輸入: CREATE DATABASE db_name; 來建立新的資料庫
7. 出現Query OK等字眼，表示成功建立名為hw01的database


#### MySQL Workbench
可以透過script的方式或著是GUI介面直接建立資料庫，我採用的是使用script的方式建立。

create database db_name;
use db_name;


### 2. 請說明如何建立 MySQL table，步驟需截圖 (5%)
#### windows command line
1. 建立好database之後，必須使用USE db_name的指令來告訴MySQL你要使用哪一個database
2. 先前已經先建立了名稱為test123的資料庫，這邊就輸入: USE test123;
3. 欲建立table，使用: CREATE TABLE table_name(欄位名稱 資料型態, 欄位名稱 資料型態, .....);
4. 以圖中範例，建立了三個欄位，分別為customer_id、customer_name、customer_city


#### MySQL Workbench
1. 資料庫建立好之後，先選擇你要的資料庫
2. 利用create table name(column_name data_type, ........)建立table

#	建立資料庫
create database hw01;

#	選擇資料庫
use hw01;

#	建立table
create table books(id integer, booknamechar(20), author char(20),
    price integer, category char(20), publisher char(20));

create table bookstores(no integer, name char(20), city char(20));

create table orders(no integer, id integer, quantity integer);

3. 完成之後，可以看到左側的結構圖，資料庫(hw01)底下會有三層table(分別為books、bookstores、orders)


4. 接者先使用 show varialbes like "%secure%"查看合法存放csv檔的路徑。因為不是隨意路徑下的檔案都可以進行讀取，只有MySQL認定的安全路徑下的檔案才可以讀取。然後將欲讀取的檔案放到指定安全的路徑下。


根據輸出結果 安全的路徑為: **C:\ProgramData\MySQL\MySQL Server 8.0\Uploads**

5. 讀取csv檔，存入table中。


6. 讀取成功


### 3. 顯示各table的完整內容/每筆資料(MySQL語法)，請附上搜尋的Query和結果截圖 (5%)

SELECT * FROM Books;


SELECT * FROM Bookstores;


SELECT * FROM Order;


### 4. MySQL查詢結果的完整截圖
　(1)查詢1：找出所有書籍不重覆的所有價格。列出價格 (由大至小排列)。 (10%)

select distinct books.price from books order by price;


　(2)查詢2：找出所有種類書籍的數量。列出書籍種類、該種類的數量。 (10%)

 select books.category, count(books.id) from books group by category;


　(3)查詢3：找出那些訂購所有書籍 (沒有一本書它不訂購) 的書局名稱。列出書局名稱、所在縣市。 (10%)

 select bookstores.name, bookstores.city from bookstores where not exists(
select * from books where not exists(select * from orders where 
bookstores.no = orders.no and orders.id = books.id));


### 5. 請說明如何建立 MongoDB database，步驟需截圖 (5%)
1. 開啟MongoDB Compass
2. 點選位於左側Databases欄位中右側的+號
3. 輸入Database的名稱以及collection(table)的名稱



### 6. 請說明如何建立 MongoDB table，步驟需截圖 (5%)
1. 點選位於左側database名稱右側的+號，可以增加新的table

3. 點選Add Data選單中的import file

5. 上傳csv檔

6. 完成

若是使用command line的方式的話，要使用mongoimport來讀檔，直接在command line中輸入

mongoimport --db DB_Name --collection Collection_Name --type=csv --
headerline --file=Name-of-file-to-import

DB_Name就是資料庫的名稱、Colleciton_Name是table的名稱(要新增的話 就直接打你想要取的名子)


結束之後進入mongo 查看資料是否有正確輸入到colleciton中


### 7. 顯示各table的完整內容/每筆資料(MongoDB語法)，請附上搜尋的Query和結果截圖 (5%)
1. show collections可以查看所有的collections(tables)

2. 查看完整內容，輸入: db.collection_name.find()



### 8. MongoDB查詢結果的完整截圖
#### (1)查詢1：找出所有書籍不重覆的所有價格。列出價格 (由大至小排列)。 (10%)

輸入db.Books.distinct("price")即可獲得價格，會自動排序(由小至大)

#### (2)查詢2：找出所有種類書籍的數量。列出書籍種類、該種類的數量。 (10%)

db.Books.aggregate("$group":{"_id":"$category", "total":{"$sum":1}})


#### (3)查詢3：找出那些訂購所有書籍 (沒有一本書它不訂購) 的書局名稱。列出書局名稱、所在縣市。 (10%)

db.Orders.aggregate([
{
    $group: 
    {
        _id: "$no",
        books: { $addToSet: "$id" }
    }
},
{
    $match: 
    {
        books: { $all: db.Books.distinct("id")}
    }
},
{
    $lookup: 
    {
        from: "Bookstores",
        localField: "_id",
        foreignField: "no",
        as: "Bookstores"
    }
},
{
    $project: 
    {
        _id: 0,
        書店名稱: { $arrayElemAt: ["$Bookstores.name", 0] },
        所在縣市: { $arrayElemAt: ["$Bookstores.city", 0] }
    }
}])


## [Bonus](20%)

會依據PDF內容說明的詳細程度斟酌給分(10%)

以下為額外練習題，讓同學試著動腦想看看
### 4. MySQL查詢結果的完整截圖
　(4)查詢4：找出那些訂購特定單一書籍超過20本的書局名稱。列出該書籍名稱、書局名稱。 (5%)

select books.bookname, bookstores.name from books, bookstores where exists(
select orders.no, orders.id, sum(orders.quantity) from orders where 
books.id = orders.id and bookstores.no = orders.no 
group by orders.id, orders.no having sum(orders.quantity) > 20 
order by orders.no, orders.id);


### 8. MongoDB查詢結果的完整截圖
　(4)查詢4：找出那些訂購特定單一書籍超過20本的書局名稱。列出該書籍名稱、書局名稱。 (5%)

db.Orders.aggregate([
{
    $lookup:{
    from: "Bookstores",
    localField: "no",
    foreignField: "no",
    as: "Bookstores_name"
    }
},
{ 
    $unwind:"$Bookstores_name" 
},
{
    $lookup:
    {
    from: "Books",
    localField: "id",
    foreignField: "id",
    as: "Books_name"
    }
},
{
    $unwind:"$Books_name" 
},
{
    "$group":
    {
        "_id":
        {
            "store":"$Bookstores_name.name",
            "book":"$Books_name.bookname"
        },
        "total":
        {
            "$sum":"$quantity"
        }
    }
},
{
    "$match":
    {
        "total":
        {
            "$gt":20
        }
    }
}
]).pretty()


### 過程遇到的問題
#### 中文字會是亂碼
我是使用cmd進入mysql的服務，在select * from books的時候，輸出在terminal的中文字會是亂碼。我嘗試過使用ANSI的編碼，中文輸出會變成NULL。

後來發現是我終端機的編碼預設是Big 5，而MySQL是latin 1，必須先將終端機的encoding改成utf-8(終端機輸入chcp 65001)，然後將MySQL預設的encoding改成utf-8(set names utf8)，就可以了。

不過在cmd中輸入絕對路徑，如果路徑有包含中文字，會有問題。目前嘗試過的方法就是在預設的encoding下讀取檔案，要輸出的時候再切換成utf8的encoding。比較好的方法應該是使用workbench或者是在linux上安裝，就比較不會出現上述編碼的問題。

#### MongoDB資料型態
一開始將csv檔案匯入之後，MongoDB似乎是將string作為默認的data type，當在做sum($field)的時候，會出現總和為0的情況。

方法就是透過程式碼將string轉換成Int32

db.db-name.find({field-name: {$exists: true}}).forEach(function(obj) { 
    obj.field-name = new NumberInt(obj.field-name);
    db.db-name.save(obj);
});

完成之後，sum就可以正常加總了。

### 參考資料
https://stackoverflow.com/questions/3635166/how-do-i-import-csv-file-into-a-mysql-table
https://stackoverflow.com/questions/4973095/how-to-change-the-type-of-a-field
https://ithelp.ithome.com.tw/articles/10185952
投影片ppt