---
# System prepended metadata

title: database(9)1130504
tags: [database]

---

# database(9)1130504 期末報告
:::info
# 指導老師：林哲正 教授
# 組 員：陳昆顯、陳英木、楊珠慧

:::

|:::spoiler <font color="#AC19C9">1.select/where/orderby(20%)</font>

:::info
SELECT …. WHERE 
含 AND、OR、NOT 
ORDER BY ASC/DESC    

```sql=
SELECT * FROM Student WHERE Address='Tainan' or Address='Address5' order by SNo DESC;
--從student表中選擇address是tainan或address5以及名字是stu3的資料 並依SNo降冪排列--
SELECT * FROM Student WHERE Address='Tainan' and Name='stu3' order by SNo DESC;
--從student表中選擇address是tainan以及名字是stu3的資料 並依SNo降冪排列--
SELECT * FROM Student WHERE NOT Address = 'Tainan';


```
![截圖 2024-05-04 上午10.13.30](https://hackmd.io/_uploads/Hkq59MmfA.png)

![截圖 2024-05-04 上午10.16.29](https://hackmd.io/_uploads/HJYj9f7fA.png)

![截圖 2024-05-04 下午1.45.05](https://hackmd.io/_uploads/HkvOjrXz0.png)


:::

---

<style>
.blue {color: blue;}
</style>

:::spoiler <span class="blue">2.delete/insert/update(10%)</span>
:::success
DELETE 

INSERT 

UPDATE


```sql=
INSERT INTO `EmergencyContact`(`StuNO`, `Name`, `Relation`, `phone`) VALUES ('s101', 'father101', '父子', '0918000101');
    /* 在EmergencyContact資料表中插入新資料*/
UPDATE `EmergencyContact` SET `Name` = 'update_father101' WHERE `EmergencyContact`.`StuNO` = 's101';
    /* 在EmergencyContact表中 修改名字*/
DELETE FROM `EmergencyContact` WHERE `EmergencyContact`.`StuNO` = 's101';
/*在EmergencyContact表中，刪除StuNO=s101的資料*/
```
![截圖 2024-04-26 上午10.50.08](https://hackmd.io/_uploads/S1CtP5d-0.png)
![截圖 2024-04-26 上午10.50.30](https://hackmd.io/_uploads/Byssw5_ZR.png)
![截圖 2024-04-26 上午10.50.52](https://hackmd.io/_uploads/SkK3DqdZA.png)
![截圖 2024-04-26 上午10.51.26](https://hackmd.io/_uploads/ryr6Pq_ZC.png)
:::

---
:::spoiler <font color=green>3.min/max/count/avg/sum(5%)</font>

:::info
MIN、MAX 

COUNT、AVG、SUM

```sql=
SELECT Max(EntryNO) AS maxEntryNO FROM Student;

SELECT Min(EntryNO) AS minEntryNO FROM Student;

SELECT COUNT(*) AS EntryNOsum, Sex FROM Student GROUP BY Sex;

SELECT Sex, SUM(EntryNO) AS EntryNOsum FROM Student GROUP BY Sex;

SELECT * FROM Student WHERE EntryNO > (SELECT AVG(EntryNO) FROM Student);

```
![截圖 2024-04-27 上午9.13.36](https://hackmd.io/_uploads/ry8IZCKWA.png)


![截圖 2024-04-27 上午9.12.58](https://hackmd.io/_uploads/r1GN-CtbC.png)


![截圖 2024-04-27 上午9.12.23](https://hackmd.io/_uploads/SkTbbRFb0.png)


![截圖 2024-04-27 上午9.10.17](https://hackmd.io/_uploads/r1y9xAYZR.png)

![截圖 2024-04-27 上午9.04.26](https://hackmd.io/_uploads/HyiGgCFZA.jpg)
:::

---
:::spoiler <font color="blue">4. Like % _(10%)
</font>

:::success
LIKE % _

```sql=
SELECT * FROM EmergencyContact WHERE Name LIKE 'f___%';
--f開頭，_有三個，長度至少要三個字元以上 故f17不會出現--
```
![截圖 2024-04-27 上午11.20.39](https://hackmd.io/_uploads/Sk54JecbA.jpg)


:::


---
:::spoiler <font color=gree>5. in/between/alias(as)(5%)
</font>

:::info
IN、BETWEEN 

ALIAS (AS)
```sql=
SELECT * FROM Student WHERE SNo IN (SELECT stuNO FROM EmergencyContact);
--從student表中找出
SELECT * FROM Teacher WHERE GuideNO BETWEEN 5 AND 10 AND Object IN ("物理","化學");

SELECT Name AS "My student Name" FROM Student;

```
![截圖 2024-04-27 下午2.53.01](https://hackmd.io/_uploads/S1tU-7qWC.jpg)

![截圖 2024-04-27 下午3.11.47](https://hackmd.io/_uploads/SypSBQqW0.png)

![截圖 2024-04-27 下午3.20.34](https://hackmd.io/_uploads/HkDLPm5b0.png)


:::

---
:::spoiler <font color=orange>6. join/inner/left/right/cross/self(10%)</font>
:::success
JOIN 

INNER、LEFT、RIGHT、CROSS、SELF
```sql=
SELECT RegistrationForm.SerialNO,Student.Name, Student.Address, Student.Email,Student.ID FROM Student INNER JOIN RegistrationForm ON RegistrationForm.ContestantID1=Student.SNo;
--inner join--
SELECT RegistrationForm.SerialNO,Student.SNo,Student.Name FROM Student LEFT JOIN RegistrationForm ON RegistrationForm.ContestantID2 = Student.SNo ORDER BY RegistrationForm.SerialNO;
--left join--
SELECT RegistrationForm.SerialNO,Student.SNo,Student.Name FROM Student RIGHT JOIN RegistrationForm ON RegistrationForm.ContestantID2 = Student.SNo ORDER BY RegistrationForm.SerialNO;
--right join--
SELECT EmergencyContact.Name AS EName1, EmergencyContact.Name AS EName2, EmergencyContact.stuNO FROM EmergencyContact WHERE EmergencyContact.Name =EmergencyContact.Name AND EmergencyContact.sex = EmergencyContact.sex ORDER BY EmergencyContact.StuNO;
--self join--
SELECT Teacher.Name,RegistrationForm.Object FROM Teacher CROSS JOIN RegistrationForm;
--cross join 笛卡兒乘積 (Cartesian product)把兩張表所有可能都排列組合出來。--
````
![截圖 2024-04-27 下午4.07.28](https://hackmd.io/_uploads/BJvUMNqWR.png)

![截圖 2024-04-27 下午4.32.53](https://hackmd.io/_uploads/rJqBONcZ0.png)

![截圖 2024-04-27 下午4.34.16](https://hackmd.io/_uploads/HJ6o_EcZA.png)

![截圖 2024-04-27 下午5.28.28](https://hackmd.io/_uploads/Hk2DrBqZ0.jpg)

![截圖 2024-05-03 凌晨12.29.12](https://hackmd.io/_uploads/HkMhJrZzR.jpg)



:::

---

:::spoiler <font color=bluelight>7. group by/having/exists/any/all(10%) </font>

:::info
GROUP BY、HAVING、EXISTS、ANY、ALL 
```sql=
SELECT Object, SUM(GuideNO) FROM Teacher GROUP BY Object HAVING SUM(GuideNO)<10;
--在teacher表中，找出指導次數<10的科目--
SELECT TNo FROM Teacher WHERE not EXISTS (SELECT InstructorID1 from RegistrationForm where RegistrationForm.InstructorID1=Teacher.TNo);
--從teacher跟 registrationForm表中，選擇沒有擔任第一指導老師的老師 用in的方法在11.subquery～
SELECT Name FROM Teacher WHERE TNo = all (SELECT InstructorID1 FROM RegistrationForm WHERE Object="物理");
--如果registratorForm中的科目都是物理，列出teacher表中的name--
SELECT Name FROM Teacher WHERE TNo = any (SELECT InstructorID1 FROM RegistrationForm WHERE Object="物理");
--如果registratorForm中的科目有任一值是物理，列出teacher表中相對應的name--

```
![截圖 2024-05-03 上午10.15.08](https://hackmd.io/_uploads/r1OC_aZMR.png)

![截圖 2024-05-03 上午11.27.37](https://hackmd.io/_uploads/HJEl5RZzR.png)

![截圖 2024-05-03 中午12.04.19](https://hackmd.io/_uploads/By4DzkGzC.png)

![截圖 2024-05-03 中午12.03.48](https://hackmd.io/_uploads/S1kcMkfzR.png)


:::

---

:::spoiler <font color=deep yellow>8.使用期中報告資料庫設計(10%)</font>
:::success
8.使用期中報告資料庫設計(10%)
![截圖 2024-04-27 下午2.46.44](https://hackmd.io/_uploads/S1591Q9Z0.png)

![截圖 2024-04-27 下午2.49.04](https://hackmd.io/_uploads/Hk8geQ5bR.png)


:::

---

:::spoiler <font color=red>9. Stored Procedure(5%) </font>
:::info
Stored Procedure
```sql=
DROP PROCEDURE IF EXISTS get_city_country;
DELIMITER //
CREATE PROCEDURE get_city_country(
    IN city_filter varchar(50),
   IN country_filter varchar(50)
)
BEGIN
    SELECT * FROM customers WHERE city = city_filter AND country = country_filter ;
END //
DELIMITER ;

call get_city_country('Nantes', 'France');
```

:::

---
:::spoiler <font color=pink>10. Referential Integrity(5%)</font>
:::success
Referential Integrity
:::

---
:::spoiler <font color=greenligt>11. Subquery(5%)</font>
:::info
Subquery
```sql=
SELECT TNo FROM Teacher WHERE TNo NOT IN (SELECT InstructorID1 from RegistrationForm);
--從teacher跟 registrationForm表中，選擇沒有擔任第一指導老師的老師～

```
![截圖 2024-05-03 上午10.57.17](https://hackmd.io/_uploads/BkFsz0-fR.png)



:::

---
:::spoiler <font color=purple>12. python connect sql(ues pymysql to create table)(5%)
</font>

:::success
利用Python 執行 SQL 指令

# :mag: 
### 連接資料庫(scienceFair)，產生"player"資料表
```python=
#輸入PyMySQL
import pymysql

#連接資料庫，並簡化執行命令
db = pymysql.connect(host="localhost",user="root",passwd="",database="scienceFair")

#使用cursor()
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS player")

#建立table
sql = """CREATE TABLE `player` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Age` int(11) DEFAULT NULL,
  `Sex` varchar(1) DEFAULT NULL,
  `Salary` float DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""

cursor.execute(sql)

# disconnect from server
db.close()

```
![截圖 2024-04-21 上午10.29.20](https://hackmd.io/_uploads/B1pwilzZA.png)

![截圖 2024-04-21 上午10.34.49](https://hackmd.io/_uploads/Hy4KslGbA.png)
:::
