計算機概論Lab-12

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

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.