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

:::

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

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

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';




:::

---

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

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

INSERT

UPDATE

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的資料*/

:::

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

:::info
MIN、MAX

COUNT、AVG、SUM

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);





:::

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

:::success
LIKE % _

SELECT * FROM EmergencyContact WHERE Name LIKE 'f___%';
--f開頭，_有三個，長度至少要三個字元以上 故f17不會出現--


:::

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

:::info
IN、BETWEEN

ALIAS (AS)

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;




:::

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

INNER、LEFT、RIGHT、CROSS、SELF

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)把兩張表所有可能都排列組合出來。--






:::

---

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

:::info
GROUP BY、HAVING、EXISTS、ANY、ALL

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--





:::

---

:::spoiler <font color=deep yellow>8.使用期中報告資料庫設計(10%)</font>
:::success
8.使用期中報告資料庫設計(10%)


:::

---

:::spoiler <font color=red>9. Stored Procedure(5%) </font>
:::info
Stored Procedure

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

SELECT TNo FROM Teacher WHERE TNo NOT IN (SELECT InstructorID1 from RegistrationForm);
--從teacher跟 registrationForm表中，選擇沒有擔任第一指導老師的老師～


:::

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

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

# :mag:
### 連接資料庫(scienceFair)，產生"player"資料表

#輸入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()


:::