<style>
.command {
display: inline-block;
font-size: 24px;
color: black;
background-color: white;
padding: 7px;
border-radius: 10px;
margin: 5px;
}
</style>
## 計算機概論Lab-12
### SQL
<p>https://hackmd.io/@IMOK/Lab12</p>
---
<img src="https://hackmd.io/_uploads/ryUSIqnJT.jpg" width=400 style="border-radius:1000px;"/>
講師: 賴昱有
---
## BEFORE CLASS
<font size = 6 >http://140.121.197.13/tutorial</font>
<font size = 6 >先下載解壓縮後執行安裝</font>
<font size = 6 ><span class="command">SQLServer2017-SSEI-Expr.exe</span></font>
---
## 什麼是資料庫
+ <font size = 7 >結構</font>
+ <font size = 6 >Database</font>
+ <font size = 6 >Table</font>
+ <font size = 6 >Field</font>
+ <font size = 6 >Record</font>
+ <font size = 7 >CRUD</font>
+ <font size = 6 >Create</font>
+ <font size = 6 >Read</font>
+ <font size = 6 >Update</font>
+ <font size = 6 >Delete</font>
----
----
## 關聯式資料庫
<font size = 5 >Relational Database Management System(RDBMS)</font>
<img src= "https://hackmd.io/_uploads/rkrV5iXE1x.png" width=650 style="margin : 0px 0px 0px 0px"/>
----
## 非關聯式資料庫
<font size = 5 >NoSQL (Not Only SQL)</font>
+ <font size = 7 >Key-Value Database</font>
+ <font size = 7 >Large-Scale Database</font>
+ <font size = 7 >Document Database</font>
+ <font size = 7 >Graph Database</font>
+ <font size = 7 >Time series database</font>
+ <font size = 7 >…</font>
---
## 匯入範例資料庫
+ <font size = 6 >打開並複製 [instnwnd.sql](https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/databases/northwind-pubs/instnwnd.sql) 中所有內容</font>
+ <font size = 6 >打開 SSMS</font>
+ <font size = 6 >連接到 MYSQL Server</font>
+ <font size = 6 >Ctrl+N 打開指令編輯器</font>
+ <font size = 6 >貼上範例資料庫內容</font>
+ <font size = 6 >F5 執行所有指令</font>
----
---
## CRUD IN MYSQL
+ <font size = 6 >Create</font>
+ <font size = 6 ><span class="command">INSERT</span></font>
+ <font size = 6 >Read</font>
+ <font size = 6 ><span class="command">SELECT</span></font>
+ <font size = 6 >Update</font>
+ <font size = 6 ><span class="command">UPDATE</span></font>
+ <font size = 6 >Delete</font>
+ <font size = 6 ><span class="command">DELETE</span></font>
----
## SELECT
<font size = 6 >基本指令形式</font>
```sql=
SELECT column1, column2
FROM table_name
[WHERE conditions]
[ORDER BY column1 order];
```
<font size = 6 >e.g.</font>
```sql=
SELECT CategoryID, CategoryName, Description, Picture
FROM Northwind.dbo.Categories
```
----
```sql=
SELECT *
FROM Northwind.dbo.Categories
WHERE CategoryID=1;
```
<img src= "https://hackmd.io/_uploads/HJB16smVkg.png" width=800 style="margin : 0px 0px 0px 0px"/>
----
<font size = 5 >查找 Description 是 S 開頭的</font>
```sql=
SELECT *
FROM Northwind.dbo.Categories
WHERE Description LIKE 'S%';
```
<font size = 5 >降序排序(DESC)、升序排列(ASC)</font>
```sql=
SELECT *
FROM Northwind.dbo.Categories
WHERE Description LIKE 'S%'
ORDER BY CategoryName DESC;
```
<font size = 5 >數資料筆數</font>
```sql=
SELECT COUNT(*)
FROM Northwind.dbo.Categories
```
---
### INNER JOIN
```sql=
SELECT *
FROM Northwind.dbo.Categories
INNER JOIN Northwind.dbo.Products
ON Northwind.dbo.Categories.CategoryID = Northwind.dbo.Products.CategoryID
```
----
<style>
table {
width: 80%;
border-collapse: collapse;
line-height: 1.5; /* 行距 */
}
th, td {
font-size: 18px; /* 字體大小 */
}
</style>
+ <font size = 6 >Categories</font>
| CategoryID | CategoryName |
| ---------- | ------------ |
| 1 | Drink |
| 2 | Food |
+ <font size = 6 >Products</font>
| CategoryID | CategoryName | CategoryID |
| ---------- | ------------ | ---------- |
| 1 | Beer | 1 |
| 2 | Cola | 1 |
| 3 | Bread | 2 |
+ <font size = 6 >Inner Join on CategoryID</font>
| CategoryID | CategoryName | ProductID | ProductName |
| ---------- | ------------ | --------- | ----------- |
| 1 | Drink | 1 | Beer |
| 1 | Drink | 2 | Cola |
| 2 | Food | 3 | Bread |
---
### INSERT
<font size = 6 >基本指令形式</font>
```sql=
INSERT INTO table_name(column1, column2, ...)
VALUES ('value1', 'value2', ...);
```
<font size = 6 >e.g.</font>
```sql=
INSERT INTO Northwind.dbo.Categories
VALUES ('test', 'test', 'test');
```
---
### UPDATE
<font size = 6 >基本指令形式</font>
```sql=
UPDATE table_name
SET column1='value1', column2='value2'
[WHERE conditions];
```
<font size = 6 >e.g.</font>
```sql=
UPDATE Northwind.dbo.Categories
SET CategoryName='test2'
WHERE CategoryName='test'
```
---
### DELETE
<font size = 6 >基本指令形式</font>
```sql=
DELETE FROM table_name
[WHERE conditions];
```
<font size = 6 >e.g.</font>
```sql=
DELETE FROM Northwind.dbo.Categories
WHERE CategoryName='test2';
```
---
## 延伸閱讀
<font size = 7 >[w3schools-sql](https://www.w3schools.com/sql/)
</font>
---
## 作業網站
作業做完請上傳到 http://140.121.197.13/tutorial
並且注意上傳時間限制、檔名、其他規範
上課時提前做完可以直接給助教 demo 登記
就不需要上傳 demo 過的部分
----
## Question 1
<font size=6>
使用 SQL 指令查詢 Northwind 資料庫內的 Order 資料表 EmployeeID=5 的資料
</font>
----
## Question 2
<font size=6>使用 SQL 指令查詢 Northwind 資料庫內的 Order 資料表
EmployeeID 介於 3 到 6 的資料
並且將查詢結果根據 EmployeeID 進行由大至小的排序
</font>
+ <font size=6 color="#ff0000">[HINT]</font> <font size=6> \>\= AND \<\= or BETWEEN…AND…</font>
----
## Question 3
<font size=6>使用 SQL 指令計算 Northwind 資料庫內的
Order 資料表共有幾筆訂單。
</font>
+ <font size=6 color="#ff0000">[HINT]</font><font size=6> SELECT COUNT</font>
----
## Question 4
<font size=6>使用 SQL 指令在 Northwind 資料庫內的 Products 資料表
新增一筆 Products 的資料，內容不限。</font>
+ <font size=6 color="#ff0000">[HINT]</font><font size=6> 需注意資料欄位內的資料型態</font>
----
## Question 5
<font size=5>使用 SQL 指令在剛剛新增的 Products 資料將
ProductName 設定為 Egg，UnitPrice設定為 12.00。
</font>
----
## Question 6
<font size=5>使用 SQL 指令刪除 Northwind 資料庫內的
Products 資料表你所新增的 Products 資料
</font>
----
## Question 7
<font size=5>使用 SQL 指令將 Northwind 資料庫內
Categories、Order Details 以及 Products 資料表進行結合
查詢出每筆訂單詳細的
產品名稱 (ProductName)、種類名稱(CategoryName) 及單位價格 (UnitPrice)
</font>
+ <font size=6 color="#ff0000">[HINT]</font> <font size=6>(nested) INNER JOIN</font>
{"title":"計算機概論Lab-12","description":"image","contributors":"[{\"id\":\"738dd674-cd6a-462c-87e2-b67e68f12ac0\",\"add\":18492,\"del\":11976}]"}