###### tags: `資料庫管理系統`
# Week 8 10/31
[TOC]
:::info
在這邊說明一下~
即便是線上點名，我們助教也會依據是否有與助教申請線上課程來登記簽到喔!
>[name=] 助教
:::
## 10/24第一次小考檢討
1. 請用 SQL 使用資料庫的資訊，計算出你自己的 BMI，請問你的 BMI 是否合乎健康標準?
```sql=
SELECT Weight/(Height*Height)*10000 as BMI FROM `quiz1` WHERE StuID = '學號';
```
:::info
n次方函數
```sql=
POWER(數字, n次方)
```
```sql=
SELECT POWER(2, 2); -- 4
SELECT POWER(2, 3); -- 8
SELECT POWER(3, 3); -- 27
SELECT POWER(4, 4); -- 256
SELECT
Weight / POWER(Height / 100, 2) as bmi
FROM
quiz1
WHERE
StuID = `學號`; -- 你的BMI
```
:::
2. 學生帳號的密碼是身分是學號後三碼，接上身分證號的第 2 到第 5 位字元，以及個人姓名的第二個字。請幫忙列出所有人的學號、身分證號、姓名跟密碼
```sql=
SELECT StuID,ID,Name,concat(right(`stuID`,3),substr(`ID`,2,4),substr(`Name`,2,1)) as pwd FROM `quiz1`;
```
3. 請找出班上英文成績是偶數的同學，依照年紀由少至老排序
(小提示:年紀越小，生日的數字越大)
```sql=
SELECT * FROM `quiz1` WHERE `English` % 2 =0 ORDER BY `Birthday` DESC;
```
4. 操行成績滿分 100,每缺席(absent)一次扣 5 分，請使用 SQL 新增一個欄位，並填入每個人的操行成績
```sql=
ALTER TABLE `quiz1` ADD `participate` INT NOT NULL AFTER `absent`;
UPDATE `quiz1` set participate = 100;
UPDATE `quiz1` set participate = participate - absent*5;
```
```sql=
ALTER TABLE `quiz1` ADD `participate` INT NOT NULL AFTER `absent`;
UPDATE `quiz1` set participate = 100 - absent*5;
```
5. 學生的平均成績由國英數三科，依照 1:2:3 的權重加權計算，請找出班上第 5 到 8 名同學的姓名與平均成績，依照**成績由高至低**排序
```sql=
SELECT `Name`,(`Chinese`*1+`English`*2+`Math`*3)/(1+2+3) as wAvg/*加權成績*/
FROM `quiz1`
ORDER BY 2 desc /*SELECT關鍵字的第二個欄位由高至低排序*/
limit 4,4/*跳過4筆後列出4筆*/
```
6. 請幫非資管系且數學不及格(低於 60)的同學，數學加三分，但加分後的總分最高為60分
```sql=
UPDATE `quiz1`
set `Math` = IF(`Math`<57,`Math` + 3,60)
WHERE `Dept` <> '資管系' AND `Math` < 60;
```
```sql=
UPDATE `quiz1` set `Math` = 60 WHERE `Dept` <> '資管系' AND `Math` < 60 AND `Math` >= 57;
UPDATE `quiz1` set `Math` = `Math` + 3 WHERE `Dept` <> '資管系' AND `Math` < 57;
```
```sql=
UPDATE
quiz1
SET
Math = CASE
WHEN Math < 58 THEN Math + 3
WHEN 58 <= Math
AND Math < 60 THEN Math + (60 - Math)
END
WHERE
Dept != "資管系";
```
7. 學餐要請身分證號數字部分，同時包含3跟9的人喝飲料，請幫忙找出這些同學的系所、學號、身分證號跟姓名，依照系所、學號(大至小)排序(5%, hint: use string function)
```sql=
SELECT `Dept`,`StuID`,`ID`,`Name`
FROM quiz1
WHERE `ID` LIKE '%3%9%'
OR`ID` LIKE '%9%3%'
ORDER BY `Dept`,`StuID` DESC;
```
```sql=
SELECT `Dept`, `StuID`, `ID`,`Name`
FROM `quiz1`
where`ID`like '%3%' and `ID` like'%9%'
order by `Dept`,`StuID` desc;
```
```sql=
SELECT `Dept`,`stuID`,`Name`
FROM quiz1
WHERE instr(`ID`,'9')>0 and instr(`ID`,'3')>0
ORDER BY `Dept`,`stuID` desc
```
```sql=
SELECT
Dept,
StuID,
ID,
Name
FROM
test.quiz1
WHERE
LOCATE("3", ID) != 0
AND LOCATE("9", ID) != 0
ORDER BY
Dept,
StuID DESC;
```
## IF 函數
IF(expr1,expr2,expr3)
- expr1邏輯成立會回傳expr2(當expr1 = true,則output expr2)
- expr1邏輯不成立會回傳expr3(若expr1 = false,則outpur expr3)
```sql=
SELECT IF(1>2,2,3);
+-------------+
| IF(1>2,2,3) |
+-------------+
| 3 |
+-------------+
```
```sql=
SELECT IF(1<2,'yes','no');
+--------------------+
| IF(1<2,'yes','no') |
+--------------------+
| yes |
+--------------------+
```
:::info
### MIN() 函數
- 會取小的那個值(ex: `min(Math +3,60)` -> 取Math+3和60中最小的值)
- 簡單來說，可以用來定上限，而定下限的話則用MAX()
```sql=
SELECT MIN(column_name) FROM table_name
```
:::
## INSTR(欄位,是否包含的值) 函數
```sql=
instr(`欄位`,'是否包含的值')
```
- 回傳substr在str的位置
```sql=
SELECT INSTR('foobarbar', 'bar');
+---------------------------+
| INSTR('foobarbar', 'bar') |
/*尋找bar是否有在第一個欄位裏頭*/
+---------------------------+
| 4 |
/*說明bar這三個字現在原字串的第4個字母*/
+---------------------------+
```
```sql=
SELECT INSTR('My', 'Maria');
+----------------------+
| INSTR('My', 'Maria') |
/*尋找Maria是否有在第一個欄位裏頭*/
+----------------------+
| 0 |
/*否，不在任何一個字母*/
+----------------------+
```
:::info
找東西在東西裏位置
```sql=
LOCATE(想找什麽, 在哪裏找, 從第幾個位開始找(不填預設從頭開始))
```
**結果從 1 開始數起**
東西不在東西裏面回傳至為 0
- 大小寫視同一個字
- 找一堆東西回傳那東西開始位置
- 從第幾個位開始找包含參數填的位置
```sql=
LOCATE("e", "Lorem"); -- 4
LOCATE("E", "Lorem"); -- 4
LOCATE("4", "987654321"); -- 6
LOCATE("早", "早上好"); -- 1
LOCATE("A", "早上好"); -- 0
LOCATE("呱", "Lorem"); -- 0
LOCATE("re", "Lorem"); -- 3
LOCATE(47,253476); -- 4
LOCATE(5,253); -- 2
LOCATE(5, 253253, 4); -- 5
LOCATE(5, 253253, 2); -- 2
```
跟 ``INSTR()`` 差別
- 參數位置相反
```sql=
LOCATE(想找什麽, 在哪裏找);
INSTR(在哪裏找, 想找什麽);
```
- 不能從中間開始找
```sql=
LOCATE("bar", "foobarbar"); -- 4
LOCATE("bar", "foobarbar", 6); -- 7
INSTR("foobarbar", "bar"); -- 4
INSTR("foobarbar", "bar", 6); -- 沒這東西, SQL報錯 ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
```
:::
## Date & Time Function
### YEAR
- YEAR(date)
```sql=
SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
SELECT * FROM t1 WHERE YEAR(d) = 2011;
+---------------------+
| d |
+---------------------+
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+
```
### MONTH
- MONTH(date)
- 找出十月壽星
```sql=
SELECT * FROM `quiz1` WHERE MONTH(Birthday) = 10;
```
```sql=
SELECT MONTH('2019-01-03');
+---------------------+
| MONTH('2019-01-03') |
/*假設資料庫中有此日期*/
+---------------------+
| 1 |
/*傳回月份1*/
+---------------------+
SELECT MONTH('2019-00-03');
+---------------------+
| MONTH('2019-00-03') |
/*假設資料庫中有此日期*/
+---------------------+
| 0 |
/*傳回月份0*/
+---------------------+;
```
### DAY
- DAY(date)
### NOW
- 可傳回現在時間
```sql=
SELECT * FROM `quiz1` WHERE MONTH(Birthday) =month(now());
```
```sql=
SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2010-03-27 13:13:25 |
+---------------------+
SELECT NOW() + 0;
+-----------------------+
| NOW() + 0 |
+-----------------------+
| 20100327131329.000000 |
+-----------------------+
```
- 找出下個月的壽星 **如果是12月就跳到1月，否則當月+1**
```sql=
SELECT * FROM `quiz1` WHERE MONTH(Birthday) =IF(MONTH(NOW()) = 12,1,MONTH(NOW())+1);
```
```sql=
SELECT * FROM `quiz1` WHERE MONTH(Birthday) % 12 = MONTH(NOW() + 1) % 12;
```
- 跟現在時間日期同步(例如:本月壽星)
```sql=
SELECT * FROM `quiz1` WHERE month(Birthday) = month(now());
```
- 找出下個月的壽星(如果是12月，利用IF變成1月)
```sql=
SELECT * FROM `quiz1` WHERE month(Birthday) =IF( month(now()) =12,1,month(now())+1);
```
- 判斷是否為12月
```sql=
SELECT *FROM `quiz1` WHERE (month(Birthday))%12 = (month(now()) +1) %12;
```
- 找下個月的壽星(12月為特殊情形，直接+1會變13月)
```sql=
SELECT * FROM `quiz1`
WHERE MONTH(Birthday) = IF (
MONTH(NOW()) = 12,
1,
MONTH(NOW()) + 1);
```
- 找下個月壽星(1.用%判斷是否為12月，2.用IF判斷)
方法一
```sql=
SELECT * FROM `quiz1`
WHERE MONTH(Birthday) % 12
= (MONTH(now()) + 1) % 12;
```
方法二
```sql=
SELECT *FROM `quiz1`
WHERE MONTH(Birthday)=IF(month(now())=12,1,MONTH(now())+1);
```
### SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
- 回傳扣掉expr unit的那天
```sql=
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| SUBDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2007-12-02 |
+----------------------------------------+
```
- 輸出生日後的一個月
```sql=
SELECT Birthday,Birthday + INTERVAL 1 month FROM `quiz1`;
```
- 輸出生日後的30天(會有月份大/小的差別)
```sql=
SELECT Birthday,Birthday + INTERVAL 30 day FROM `quiz1`;
```
### DATEDIFF(expr1,expr2)
- expr1-expr2差幾天
```sql=
SELECT DATEDIFF(Birthday,Birthday + INTERVAL 30 day) FROM `quiz1`;
/*輸出為-30*/
```
## Aggregate Function(縱向函數)
- AVG:平均
- COUNT:計算符合查詢條件的有幾筆
- MAX:最大值是多少
- MIN:最小值是多少
- SUM:總和
```sql=
SELECT AVG(`Height`),MAX(`Weight`),MIN(`IQ`),count(*)
FROM `quiz1` WHERE 1;
/* count(*)=資料表中共幾筆資料 ->不會算NULL */
```
## SQL格式
- select 輸出資訊
- from 資料來源
- where 過濾條件(分組統計前)
- <font color="#f00">group by</font> 分組依據
- <font color="#f00">having</font> 分組統計後的過濾條件
- order by 排序依據
- limit 傳回筆數
## 縱向函數
- 根據統計後結果 ，系所少於5人的不列入範圍
```sql=
SELECT `Dept`,AVG(`Height`),MAX(`Weight`),MIN(`IQ`),count(*) AS C
FROM `quiz1`
GROUP BY `Dept`
HAVING C >=5
ORDER BY 2 DESC;
```
- 依據導師分組，數學分數低於60的人數統計，統計人數低於5人的不列入範圍
```sql=
SELECT `Mentor`,count(*) AS C
FROM `quiz1`
where math < 60
GROUP BY `Mentor`
HAVING C > 4
ORDER BY 2 DESC;
```
- 請依照系所，計算英文及格的人裡面，數學平均是多少
```sql=
SELECT Dept, AVG(Math) as C
FROM `quiz1`
WHERE English >= 60
GROUP BY Dept
ORDER BY 2 DESC;
```
:::info
FLOOR( )：無條件捨去
```sql=
SELECT FLOOR(1.23);
+-------------+
| FLOOR(1.23) |
+-------------+
| 1 |
+-------------+
SELECT FLOOR(-1.23);
+--------------+
| FLOOR(-1.23) |
+--------------+
| -2 |
+--------------+
```
:::
- 每三個智障學生(IQ<60)可以分配到一位慧心助教，請幫各導師計算他可以獲得幾位助教
```sql=
SELECT Mentor,floor(count(*)/3) as C
FROM `quiz1`
WHERE IQ<60
GROUP BY `Mentor`
order by 2 desc;
```
- 請問有哪些導師，導生住台中的人數達到2位
```sql=
SELECT `Mentor`,`City`,count(*) as C
FROM `quiz1`
WHERE `City`='台中市'
GROUP BY `Mentor`
HAVING C >1;
```
- 請找出人數超過3位的姓氏
```sql=
SELECT Left(Name,1) as Last_Name,COUNT(*) as C
FROM `quiz1`
/*不需要WHERE，因為沒有過濾條件*/
GROUP BY Last_Name
HAVING C>3
ORDER BY 2 DESC;
```
:::info
[已經有開 11/07的共筆囉!](https://hackmd.io/@Shaila-Hsiao/SJUzykIBs)
> [name= 助教]
:::
Google Drive
Gist
Clipboard
Sign in with Wallet
