---
# System prepended metadata

title: 資料庫系統HW01

---

#    資料庫系統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

![](https://i.imgur.com/jjym2vQ.png)

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

```
create database db_name;
use db_name;
```

![](https://i.imgur.com/iLgnOBO.png)




### 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

![](https://i.imgur.com/UcrfzAg.png)

####    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)

![](https://i.imgur.com/Zs8Bikq.png)

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

![](https://i.imgur.com/KPvDFMg.png)

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


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

![](https://i.imgur.com/DyfLJIi.png)

6. 讀取成功

![](https://i.imgur.com/ayBOWx0.png)


### 3. 顯示各table的完整內容/每筆資料(MySQL語法)，請附上搜尋的Query和結果截圖 (5%)
```
SELECT * FROM Books;
```
![](https://i.imgur.com/u7qPZmn.png)

```
SELECT * FROM Bookstores;
```
![](https://i.imgur.com/5Do9b64.png)

```
SELECT * FROM Order;
```
![](https://i.imgur.com/fQr6itG.png)


### 4. MySQL查詢結果的完整截圖
　(1)查詢1：找出所有書籍不重覆的所有價格。列出價格 (由大至小排列)。 (10%)
```
select distinct books.price from books order by price;
```
 
![](https://i.imgur.com/FmLJxX6.png)


　(2)查詢2：找出所有種類書籍的數量。列出書籍種類、該種類的數量。 (10%)
 ```
 select books.category, count(books.id) from books group by category;
 ```
![](https://i.imgur.com/keKxnjt.png)


　(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));
 ```
![](https://i.imgur.com/VMVt5ke.png)



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

![](https://i.imgur.com/HrdVIjk.png)

![](https://i.imgur.com/wL1Wve8.png)


### 6. 請說明如何建立 MongoDB table，步驟需截圖 (5%)
1. 點選位於左側database名稱右側的+號，可以增加新的table
![](https://i.imgur.com/VBt6Noi.png)

3. 點選Add Data選單中的import file
![](https://i.imgur.com/80VMXnz.png)

5. 上傳csv檔
![](https://i.imgur.com/658M7MT.png)

6. 完成
![](https://i.imgur.com/C12DLte.png)

若是使用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的名稱(要新增的話 就直接打你想要取的名子)

![](https://i.imgur.com/y4fhdjD.png)

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

![](https://i.imgur.com/1y7dZHf.png)





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

2. 查看完整內容，輸入: db.collection_name.find()
![](https://i.imgur.com/m49aXgK.png)

![](https://i.imgur.com/dtskPgE.png)

![](https://i.imgur.com/OHSLR7f.png)

### 8. MongoDB查詢結果的完整截圖
#### (1)查詢1：找出所有書籍不重覆的所有價格。列出價格 (由大至小排列)。 (10%)
 
 輸入db.Books.distinct("price")即可獲得價格，會自動排序(由小至大)
 ![](https://i.imgur.com/KV1I10G.png)

#### (2)查詢2：找出所有種類書籍的數量。列出書籍種類、該種類的數量。 (10%)
```
db.Books.aggregate("$group":{"_id":"$category", "total":{"$sum":1}})
```
![](https://i.imgur.com/yV5yGNX.png)


#### (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] }
    }
}])
```
![](https://i.imgur.com/xTH85ow.png)


## [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);
```

![](https://i.imgur.com/ZH8UaVQ.png)

 
### 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()
```
 ![](https://i.imgur.com/Tz4ITc3.png)
 
###    過程遇到的問題
####    中文字會是亂碼
我是使用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
