# 資料庫語言練習
## 題目來源：
> [超經典MySQL練習50題，做完這些你的SQL就過關了](https://allaboutdataanalysis.medium.com/%E8%B6%85%E7%B6%93%E5%85%B8mysql%E7%B7%B4%E7%BF%9250%E9%A1%8C-%E5%81%9A%E5%AE%8C%E9%80%99%E4%BA%9B%E4%BD%A0%E7%9A%84sql%E5%B0%B1%E9%81%8E%E9%97%9C%E4%BA%86-600fca8979a8)
>
> [SQL50題完整版](https://blog.csdn.net/GodSuzzZ/article/details/106930311)
>
> 
>
> 
# 基本語言複習->SELECT
下面
## select * ：全選的意思
以 `student` table為例
```sql!
SELECT * FROM `student
--會印出全部
```
---
只選取某幾個項目：
```sql!
SELECT `s_id`,`s_name` FROM `student`
--會印出s_id和s_name的項目
```
---
## SELECT DISTINCT ：返回唯一不同的值
以菜鳥課程的範例
要判斷`country`列中，選取唯一不同的值，也就是將重複的資料去除
```sql!
SELECT DISTINCT country FROM Websites;
-- 將印出不一樣的值
```
---
## WHERE：篩選器的概念
以資料表`score`為例
我要選擇`s_score`分數==大於==60分的所有資料
```sql!
SELECT * FROM `score` WHERE s_score > 60;
```
---
做個結合：
```sql!
SELECT `s_id`,`s_score` FROM `score` WHERE s_score > 80;
```
> 如果where要篩選的是字串的話，藥用成 \`字串\`這樣的樣子
> [其他運算符號和實例](https://www.runoob.com/sql/sql-where.html)：
| 運算符號 | 描述Description|
| -------- | -------- |
| = | 等於 |
| <> | 不等於。在某些版本中，也可寫作 **!=** |
| > | 大於 |
| < | 小於 |
| >= | 大於等於 |
| <= | 小於等於 |
| BETWEEN | 在某個範圍內 |
| LIKE | 搜尋某種模式 |
| IN | 針對某個列的多種可能值 |
---
## AND OR
懂得都懂
## ORDER BY: 排序
由大到小：DESC
由小到大：ASC
栗子：
```sql!
SELECT * FROM `score` WHERE `s_score` BETWEEN 60 and 90 ORDER BY `s_score` DESC
```
---
## LIKE 語法
用於where操作符的指定模式
```sql!
SELECT `Auto_NO`,`Date`,`Title` FROM `newsight` WHERE `Title` LIKE '%黃金%' ORDER BY Date DESC;
-- "%" 符号用于在模式的前后定义通配符（默认字母）
-- 上面'%黃金%'代表，我要在'Title'裡面，只要前後任何有'黃金'的字眼全部列出來
```
---
## IN 語法
用於where操作符的指定模式
```sql!
SELECT `Auto_NO`,`Date`,`Auth`,`Title` FROM `newsight` WHERE `Auth` IN ('吳念庭','王律文')
ORDER BY `Date` DESC;
-- IN選取的內容，主要選出('TEXT','TEXT')內所有的資料
```
---
## BETWEEN 語法
用於where操作符的指定模式
```sql!
-- 語法
SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;
-- column1, column2: 要選擇的字串名稱
-- table_name: 資料表名稱
-- column: 要查詢的字段名稱
-- value1: 範圍的起始值
-- value2: 範圍的結束值
```
**還有NOT BETWEEN 和 與IN 合作**
```sql!
-- NOT BETWEEN
SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;
-- BETWEEN and IN
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT
IN ('USA', 'IND');
```
---
## SQL 別名
可以為表名稱或者列名稱指定別名
**列的SQL別名語法**
```sql!
SELECT column_name AS alias_name
FROM table_name;
```
**表的SQL別名語法**
```sql!
SELECT column_name(s)
FROM table_name AS alias_name;
```
**列**的別名栗子
```sql!
SELECT `s_id` AS stuID, `s_name` AS stuName, `s_birth` AS stuBirth FROM `student`
```
---
## JOIN
把兩個表或多個表連接起來
:::info
:warning:常見的是INNER JOIN(也就是JOIN)
:::
```sql!
SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;
-- column1, column2...->字段名稱
-- table1 ->要連接的資料表1
-- table2 ->要連接的資料表2
-- condition ->連接條件
```
栗子
```sql!
SELECT * FROM `student` JOIN `score` ON student.s_id = score.s_id;
-- 根據student的s_is和score的s_id連結而成
```
---
```sql!
SELECT student.s_id, student.s_name, score.c_id, score.s_score FROM `student` JOIN `score` ON student.s_id = score.s_id WHERE score.c_id = 01
```
---
```sql!
SELECT student.s_id, student.s_name, course.c_name, score.s_score FROM student
JOIN score ON student.s_id=score.s_id
JOIN course ON score.c_id=course.c_id
--- 用join連接三個或數個資料表
```
---
```sql!
SELECT student.s_id as StuID, student.s_name AS StuName, course.c_name AS Course, score.s_score AS CourseScore, teacher.t_name AS '授課老師'
FROM `student`
JOIN score ON student.s_id=score.s_id
JOIN course ON score.c_id=course.c_id
JOIN teacher ON course.t_id=teacher.t_id
```
# 題目練習
## 1. 查詢”01"課程比”02"課程成績高的學生的資訊及課程分數
```sql!
-- 查詢”01"課程比”02"課程成績高的學生的資訊及課程分數
select
a.s_id as StuID
,a.s_name AS StuName
,b.s_score as Score1
,c.s_score as Score2
from Student a
join Score b on a.s_id = b.s_id and b.c_id = '01' -- 方法1兩個表透過學號連線，指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在
-- 為NULL的條件可以不存在，因為左連線中會直接排除c表中不存在的資料，包含NULL
where b.s_score > c.s_score
```
---
## 2. 查詢”01"課程比”02"課程成績低的學生的資訊及課程分數（題目1是成績高）
```sql!
-- 查詢”01"課程比”02"課程成績低的學生的資訊及課程分數
select
a.s_id as StuID
,a.s_name AS StuName
,b.s_score as Score1
,c.s_score as Score2
from Student a
join Score b on a.s_id = b.s_id and b.c_id = '01' -- 方法1兩個表透過學號連線，指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在
-- 為NULL的條件可以不存在，因為左連線中會直接排除c表中不存在的資料，包含NULL
where b.s_score < c.s_score
-- 對比第一題，就是>改成<
```
---
## 3. 查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
Sign in with Wallet
Connect another wallet