# 2024-07-11 AIoT班上課紀錄III ## AIoT班上課紀錄 Part I網址 https://hackmd.io/@aaronlife/uch-aiot-2024 ## AIoT班上課紀錄 Part II網址 https://hackmd.io/@aaronlife/uch-aiot-2024-2 ## AIoT班上課紀錄 Part III網址 https://hackmd.io/@aaronlife/uch-aiot-2024-3 ## AIoT班上課紀錄 Part IV網址 https://hackmd.io/@aaronlife/uch-aiot-2024-4 ## 2024-07-11 #### 從Python呼叫Store Procedure ```python= import pymysql try: conn = pymysql.connect(host='localhost', port=3306, user='root', password='3939889', database='sakila') cursor = conn.cursor() cursor.execute('CALL getRand(3, 5, @result);') cursor.execute('SELECT @result;') for i in cursor: print(i[0]) cursor.execute('CALL show99();') for i in cursor: print(i) except Exception as e: print('資料庫連線失敗: ', e) ``` ## 2024-07-10 #### SP練習 ##### 1. 寫一SP, 傳入兩個數字，回傳該兩個數字之前的隨機整數。 ```sql= CREATE PROCEDURE sakila.ex1(IN `start` int, IN `end` int, OUT `result` int) BEGIN SELECT rand() * (`end` - `start`) + `start` into `result`; END ``` 結果範例: ![1](https://hackmd.io/_uploads/SyKwq3ivC.png) #### 2. 寫一SP, 產生99乘法表 結果範例: ![99](https://hackmd.io/_uploads/ryOMKniwR.png) ```sql= CREATE PROCEDURE sakila.ex2() BEGIN DECLARE i int DEFAULT 2; DECLARE j int DEFAULT 1; DECLARE v1 varchar(32); DECLARE v2 varchar(32); DECLARE v3 varchar(32); DECLARE v4 varchar(32); DECLARE v5 varchar(32); DECLARE v6 varchar(32); DECLARE v7 varchar(32); DECLARE v8 varchar(32); DROP TABLE IF EXISTS nine; CREATE TABLE nine ( `2` varchar(32), `3` varchar(32), `4` varchar(32), `5` varchar(32), `6` varchar(32), `7` varchar(32), `8` varchar(32), `9` varchar(32) ); WHILE j <= 9 DO SELECT concat(i, 'x', j, '=', (i) * j) INTO v1; SELECT concat(i + 1, 'x', j, '=', (i+1) * j) INTO v2; SELECT concat(i + 2, 'x', j, '=', (i+2) * j) INTO v3; SELECT concat(i + 3, 'x', j, '=', (i+3) * j) INTO v4; SELECT concat(i + 4, 'x', j, '=', (i+4) * j) INTO v5; SELECT concat(i + 5, 'x', j, '=', (i+5) * j) INTO v6; SELECT concat(i + 6, 'x', j, '=', (i+6) * j) INTO v7; SELECT concat(i + 7, 'x', j, '=', (i+7) * j) INTO v8; INSERT INTO nine values(v1, v2, v3, v4, v5, v6, v7, v8); SET j = j + 1; END WHILE; SELECT * FROM nine; END ``` ##### 3. 寫一SP, 計算payment table的營業總額並回傳 > 提示: 加總amount欄位 ```sql= CREATE PROCEDURE sakila.ex3(OUT total int) BEGIN SELECT sum(amount) INTO total FROM payment; END ``` 結果範例: ![2](https://hackmd.io/_uploads/Sy4j52ovC.png) ##### 4. 寫一SP, 模擬大樂透開獎 規則: 1. 1~49挑出六個數字作為中獎號碼 2. 在挑出一個數字做為特別號碼 結果範例: ![大樂透](https://hackmd.io/_uploads/HJkJ92sPA.png) ```sql= CREATE PROCEDURE sakila.ex4() BEGIN DECLARE i int DEFAULT 0; DECLARE a1 int; DECLARE a2 int; DECLARE a3 int; DECLARE a4 int; DECLARE a5 int; DECLARE a6 int; DECLARE special int; DROP TABLE IF EXISTS all_num; CREATE TABLE all_num (`num` int AUTO_INCREMENT NOT NULL, PRIMARY key(`num`)); WHILE i < 49 DO INSERT INTO all_num VALUES(); SET i = i + 1; END WHILE; DROP TABLE IF EXISTS final_num; CREATE TABLE final_num (`num` int); INSERT INTO final_num SELECT * FROM all_num ORDER BY rand(); SELECT num INTO a1 FROM final_num LIMIT 1 offset 0; SELECT num INTO a2 FROM final_num LIMIT 1 offset 1; SELECT num INTO a3 FROM final_num LIMIT 1 offset 2; SELECT num INTO a4 FROM final_num LIMIT 1 offset 3; SELECT num INTO a5 FROM final_num LIMIT 1 offset 4; SELECT num INTO a6 FROM final_num LIMIT 1 offset 5; SELECT num INTO special FROM final_num LIMIT 1 offset 6; DROP TABLE IF exists `final`; CREATE TABLE `final` (`中獎號碼` varchar(64), `特別號碼` int); INSERT INTO `final` values(concat_ws(',', a1, a2, a3, a4, a5, a6), special); SELECT * FROM `final`; END ``` #### 上面四個練習的呼叫範例 ```sql= CALL ex1(5, 7, @result); SELECT @result; CALL ex2(); CALL ex3(@total); SELECT @total; CALL ex4(); ``` #### Store Procdure預存程序 #### loop ```sql= CREATE PROCEDURE sakila.testLoop(IN t int) BEGIN DECLARE i int DEFAULT 0; aaron:LOOP IF i >= t THEN LEAVE aaron; END IF; SET i = i + 1; SELECT i; END LOOP aaron; END ``` #### while ```sql= CREATE PROCEDURE sakila.testLoop(IN t int) BEGIN DECLARE sum int DEFAULT 0; DECLARE i int DEFAULT 1; WHILE i <= t DO SET sum = sum + i; SET i = i + 1; END WHILE; SELECT sum; END ``` #### 判斷付款人次是否比免費人次多 ```sql= CREATE PROCEDURE sakila.getValue(OUT msg varchar(32)) BEGIN DECLARE payedCount int; DECLARE freeCount int; SELECT count(*) INTO freeCount FROM payment p WHERE amount = 0; SELECT count(*) INTO payedCount FROM payment p WHERE amount != 0; IF payedCount > freeCount THEN SET msg = '賺錢的'; -- ELSEIF xxx then ELSE SET msg = '不賺錢的'; END IF; END ``` 呼叫方式 ```sql= CALL getValue(@r); SELECT @r; ``` #### 取得今天星期幾 ```sql= CREATE PROCEDURE sakila.getWeekday(OUT w varchar(16)) BEGIN SELECT CASE weekday(now()) WHEN 0 THEN '星期一' WHEN 1 THEN '星期二' WHEN 2 THEN '星期三' WHEN 3 THEN '星期四' WHEN 4 THEN '星期五' WHEN 5 THEN '星期六' WHEN 6 THEN '星期日' ELSE '錯誤' END INTO w; END ``` 呼叫方式 ```sql= CALL getWeekday(@r); SELECT @r; ``` #### 幫會員tag等級(Store Procedure) ```sql= CREATE PROCEDURE sakila.tagCustomerLevel() BEGIN DROP TABLE IF EXISTS customer_level; CREATE TEMPORARY TABLE `customer_level` ( `first_name` varchar(45) NOT NULL, `level` varchar(45) NOT NULL ); INSERT INTO customer_level SELECT c.first_name, case WHEN sum(p.amount) > 200 THEN 'VIP' WHEN sum(p.amount) <= 200 AND sum(p.amount) > 100 THEN 'SOSO' ELSE '菜G' END level FROM payment p LEFT JOIN customer c ON c.customer_id = p.customer_id GROUP BY p.customer_id; END ``` #### 呼叫方式 ```sql= CALL tagCustomerLevel(); SELECT * FROM customer_level; ``` ##### 這段放在Procdure目錄下 ```sql= CREATE PROCEDURE sakila.firstSP( IN count int, OUT r int, OUT r1 varchar(64)) BEGIN DECLARE test int DEFAULT 0; -- 定義變數test並給予預設值為0 SET test = count * 2; -- 將count變數內容x2後指派給test變數 SET r = test * 2; -- -- 將test變數內容x2後指派給r變數做回傳 SELECT last_name INTO r1 FROM customer c ORDER BY customer_id LIMIT 1; END ``` ##### 這段在SQL Editor裡面執行即可 ```sql= CALL firstSP(115, @result, @result2); -- 呼叫firstSP預存程序 SELECT @result, @result2; ``` ## 2024-07-09 #### 條件邏輯 1. 幫每位顧客給予一個消費等級: - 貢獻營業額超過200 = VIP - 貢獻營業額200以下，超過100 = SOSO - 貢獻營業額100以下 = 菜G ```sql= SELECT c.first_name, sum(p.amount), case WHEN sum(p.amount) > 200 THEN 'VIP' WHEN sum(p.amount) <= 200 AND sum(p.amount) > 100 THEN 'SOSO' ELSE '菜G' END '誰是VIP' FROM payment p LEFT JOIN customer c ON c.customer_id = p.customer_id GROUP BY p.customer_id; ``` 2. 幫每部片註記熱門程度 - 如果租借次數 > 30次，標記為: 熱門 - 如果租借次數 <= 30次且 > 10次，標記為: 一般 - 如果租借次數 <= 10次，標記為: 冷門 > 語法合併在第三題 3. 幫每部影片註記分級 - PG-xxx和PG皆為保護級 - NC-xxx和G為普遍級 - R為保護級 ```sql= SELECT f.title, CASE WHEN count(*) > 30 THEN '熱門' WHEN count(*) <= 30 AND count(*) > 10 THEN '一般' ELSE '冷門' END '熱門程度', CASE WHEN f.rating LIKE 'PG%' THEN '保護級' WHEN f.rating IN ('NC-17', 'G') THEN '普遍級' WHEN f.rating = 'R' THEN '限制級' ELSE '未分級' END '分級' FROM rental r LEFT JOIN inventory i ON i.inventory_id = r.inventory_id RIGHT JOIN film f ON i.film_id = f.film_id GROUP BY f.film_id; ``` #### 子查詢 1. 查那些會員從來沒有租借過影片 ```sql= SELECT concat(c.last_name, ' ', c.first_name) FROM customer c WHERE c.customer_id NOT in (SELECT r.customer_id FROM rental r JOIN customer c ON r.customer_id = c.customer_id GROUP BY r.customer_id); ``` 2. 前三名VIP顧客名字 ```sql= SELECT c.last_name, c.first_name , sum(p.amount) '貢獻度' FROM payment p JOIN customer c ON c.customer_id = p.customer_id GROUP BY p.customer_id ORDER BY sum(p.amount) DESC LIMIT 3; ``` #### 子查詢 ```sql= SELECT concat(last_name, ' ', first_name) FROM customer c WHERE c.customer_id = (SELECT max(customer_id) FROM customer c); SELECT concat(c.last_name, ' ', c.first_name) FROM customer c WHERE c.customer_id IN (SELECT customer_id FROM payment p WHERE amount = 0); SELECT * FROM customer c WHERE customer_id IN (563, 564, 565); ``` ## 2024-07-08 1. 庫存最多的影片和最少的影片個是哪一部? ```sql SELECT title, c FROM (SELECT i.film_id AS f, count(i.film_id) c FROM inventory i GROUP BY i.film_id ORDER BY c LIMIT 1) a JOIN film ff ON ff.film_id = f union SELECT title, c1 FROM (SELECT i1.film_id AS f1, count(i1.film_id) c1 FROM inventory i1 GROUP BY i1.film_id ORDER BY c1 DESC LIMIT 1) b JOIN film ff ON ff.film_id = f1; ``` 2. 哪一個分級的影片最多?有幾部? ```sql= SELECT f.rating, count(*) c FROM film f GROUP BY f.rating ORDER BY c DESC LIMIT 1; ``` 3. 哪一個分級的影片租借次數最高? ```sql= SELECT f.rating, count(*) c FROM rental r LEFT JOIN inventory i ON r.inventory_id = i.inventory_id LEFT JOIN film f ON f.film_id = i.film_id GROUP BY f.rating ORDER BY c DESC LIMIT 1; ``` 4. 前3名受歡迎的演員為何? (演出的影片租借總和前3名) ```sql= SELECT a.actor_id, concat(a.last_name, ' ', a.first_name), count(*) c FROM rental r LEFT JOIN inventory i ON r.inventory_id = i.inventory_id LEFT JOIN film f ON f.film_id = i.film_id LEFT JOIN film_actor fa ON f.film_id = fa.film_id LEFT JOIN actor a ON a.actor_id = fa.actor_id GROUP BY a.actor_id ORDER BY c DESC LIMIT 3; ``` #### ``` SELECT customer_id, count(*) AS c FROM rental r GROUP BY customer_id HAVING count(*) >= 40; -- 實驗用having代替where SELECT * FROM rental r HAVING customer_id > 200; ``` #### ```sql= SELECT u.name FROM users u LEFT JOIN user_info ui ON u.id = ui.user_id WHERE ui.addr IS NULL; ``` #### 時間函式 ```sql= SELECT datediff('2024-07-06', '2024-07-03'); SELECT timediff('20:20:20', '12:12:12'); SELECT date_add(current_date(), INTERVAL 5 day); SELECT date_add(current_date() , INTERVAL '9-11' YEAR_MONTH); SELECT * FROM vote_history vh WHERE vote_at BETWEEN '2024-07-01' AND '2024-07-05 12:00:00'; SELECT * FROM vote_history vh WHERE vote_at = '2024-07-05 11:20:07'; ``` #### SQL函式Part II ```sql= -- 將AppleShu字串從Hello Aaron OK第7個字插入，並取代掉5個字 -- 課本: p139 SELECT INSERT('Hello Aaron OK', 7, 5, 'AppleShu'); -- 將Hello Aaron OK中的Aaron取代成Andy -- 課本: p139 SELECT REPLACE('Hello Aaron OK', 'Aaron', 'Andy'); -- 算7除以3的餘數 SELECT 7 % 3; -- 課本: p141 SELECT MOD(7, 3); -- 算2的指數 -- 課本: p142 SELECT POW(2, 4); -- 無條件進位到整數 -- 課本: p143 SELECT CEIL(33.33); -- 無條件捨去到整數 -- 課本: p143 SELECT floor(33.33); -- 四捨五入(可以指定精度) -- 課本: p143 SELECT ROUND(33.3333, 2); -- 無條件捨去(可以指定精度) -- 課本: p144 SELECT truncate(33.3333, 2); -- 取得現在時間和UTC時間 SELECT NOW(), current_timestamp(), UTC_TIMESTAMP(); -- 格式化字串 SELECT date_format(vote_at, '投票時間: %Y/%m%d %H:%i:%s') FROM vote_history; -- 將字串轉型成數字 -- 課本: p156 SELECT CAST('3' as signed integer) DATA; -- 將數字轉型成字串 SELECT cast(33 AS char(50)) data; ``` #### cast可以用的型態 - BINARY[(N)] - CHAR[(N)] - DATE - DATETIME - DECIMAL[(M[,D])] - SIGNED [INTEGER] - TIME - UNSIGNED [INTEGER] #### SQL函式 ```sql= -- 計算name欄位的字串長度 -- 課本: p132 SELECT LENGTH(name) from member; -- 組合字串 -- 課本: p131 SELECT concat('姓名:', name, ' ', age, '歲') AS '個人資訊' FROM `member` m ; -- 計算n這個字串在name欄位出現的位置 -- 課本: p133 SELECT POSITION('n' IN name AS '個人資訊' FROM `member` m ; -- 字串比較 -- 課本: p134 SELECT strcmp('dde', 'cde') -- 判斷name欄位的資料是否包含aa字串，包含=1, 不包含=0 -- 課本: p135 SELECT name LIKE '%aa%' FROM member; ``` #### user & user_info Table ```sql= -- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64) -- -- Host: localhost Database: new_db -- ------------------------------------------------------ -- Server version 8.4.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `user_info` -- DROP TABLE IF EXISTS `user_info`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user_info` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `addr` varchar(100) DEFAULT NULL, `birth` date DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `NewTable_users_FK` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user_info` -- LOCK TABLES `user_info` WRITE; /*!40000 ALTER TABLE `user_info` DISABLE KEYS */; INSERT INTO `user_info` VALUES (1,1,'xxxxx','2020-03-03'),(2,2,'yyyy','2020-03-03'),(3,5,'zzzzzz','2020-03-03'); /*!40000 ALTER TABLE `user_info` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `phone` varchar(32) DEFAULT NULL, `age` int DEFAULT NULL, `email` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` VALUES (1,'aaron','0965432187',22,NULL),(2,'andy','0954321876',33,NULL),(3,'apple','4444',3,NULL),(4,'abner','5555',4,NULL); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-07-08 10:18:52 ``` #### INNER JOIN ``` SELECT * FROM users u JOIN user_info ui ON u.id = ui.user_id ``` #### OUTER JOIN ``` SELECT * FROM users u LEFT JOIN user_info ui ON u.id = ui.user_id; ``` #### FULL JOIN ```sql= SELECT * FROM users u LEFT JOIN user_info ui ON u.id = ui.user_id UNION SELECT * FROM users u RIGHT JOIN user_info ui ON u.id = ui.user_id; ``` #### UNION/UNION ALL ```sql= SELECT name, phone, age FROM users UNION SELECT name, phone, age FROM users; SELECT name, phone, age FROM users UNION ALL SELECT name, phone, age FROM users; ``` - UNION會去除重複的資料 - UNION ALL會保留全部資料 課本用範例資料庫: https://github.com/datacharmer/test_db > **補充:** > 在sakila目錄下，先Restore `salila_mv_schema.sql`檔案後再Restore`sakila_mv_data.sql`檔案。 > ## 2024-07-04 #### 練習結果 ```sql= -- 參與投票人數 SELECT count(DISTINCT vh.user_id) AS '參與投票人數' FROM vote_history vh JOIN vote_option vo ON vh.vote_option_id = vo.id JOIN vote_question vq ON vo.vote_question_id = vq.id JOIN vote v ON vq.vote_id = v.id WHERE v.id = 1; -- 非常關注這個選項的得票率 SELECT concat(round(a1 / a2 * 100, 0), '%') AS '非常關注的投票率' FROM ( -- 非常關注的投票數 (SELECT count(*) AS a1 FROM vote_history vh JOIN vote_option vo ON vh.vote_option_id = vo.id WHERE vo.id = 1) AS a, -- 2024美國大選將在11月5日舉行，你有關注嗎的投票數 (SELECT count(*) AS a2 FROM vote_history vh JOIN vote_option vo ON vh.vote_option_id = vo.id JOIN vote_question vq ON vo.vote_question_id = vq.id WHERE vq.id = 1) AS b ); ``` #### Dump DB ```sql= -- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64) -- -- Host: localhost Database: new_db -- ------------------------------------------------------ -- Server version 8.4.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `phone` varchar(32) DEFAULT NULL, `age` int DEFAULT NULL, `email` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` VALUES (1,'aaron','2222',1,NULL),(2,'andy','3333',2,NULL),(3,'apple','4444',3,NULL); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vote_option` -- DROP TABLE IF EXISTS `vote_option`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `vote_option` ( `id` int NOT NULL, `vote_question_id` int NOT NULL, `title` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `vote_question_id` (`vote_question_id`), CONSTRAINT `vote_option_ibfk_1` FOREIGN KEY (`vote_question_id`) REFERENCES `vote_question` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vote_option` -- LOCK TABLES `vote_option` WRITE; /*!40000 ALTER TABLE `vote_option` DISABLE KEYS */; INSERT INTO `vote_option` VALUES (1,1,'非常關注'),(2,1,'有點關注'),(3,1,'不太關注'),(4,1,'完全沒關注'),(5,1,'不知道／沒意見'),(6,2,'拜登'),(7,2,'川普'),(8,2,'不知道／沒意見'),(9,3,'拜登'),(10,3,'川普'),(11,3,'不知道／沒意見'); /*!40000 ALTER TABLE `vote_option` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vote` -- DROP TABLE IF EXISTS `vote`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `vote` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '投票', `photo_url` varchar(256) DEFAULT NULL, `title` varchar(32) NOT NULL, `content` varchar(1024) DEFAULT NULL, `created_at` datetime DEFAULT (now()), `start_date` datetime DEFAULT NULL, `end_date` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vote` -- LOCK TABLES `vote` WRITE; /*!40000 ALTER TABLE `vote` DISABLE KEYS */; INSERT INTO `vote` VALUES (1,'https://s.yimg.com/lo/api/res/1.2/zy7Hu_r6bPHNuMT.G1pVPQ--~A/YXBwaWQ9dHdhYnVuZXdzO3c9MjIwO2g9MTI4O2ZpPWZpbGw7cHhvZmY9NTA7cHlvZmY9MQ--/https://s.yimg.com/os/creatr-uploaded-images/2024-06/cd6fbdb0-34fc-11ef-bfff-e6433157d1c9','你有關注美國大選嗎？','2024美國大選將由拜登與川普再次對決，台灣時間6月28日第一次電視辯論落幕。外媒認為兩人過程大致心平氣和，表現似乎都沒有太出色。','2024-07-05 11:12:32','2024-06-28 00:00:00','2024-07-02 00:00:00'); /*!40000 ALTER TABLE `vote` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vote_question` -- DROP TABLE IF EXISTS `vote_question`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `vote_question` ( `id` int NOT NULL, `vote_id` int NOT NULL, `no` varchar(16) DEFAULT NULL, `name` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `vote_id` (`vote_id`), CONSTRAINT `vote_question_ibfk_1` FOREIGN KEY (`vote_id`) REFERENCES `vote` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vote_question` -- LOCK TABLES `vote_question` WRITE; /*!40000 ALTER TABLE `vote_question` DISABLE KEYS */; INSERT INTO `vote_question` VALUES (1,1,'Q1','2024美國大選將在11月5日舉行，你有關注嗎？'),(2,1,'Q2','2024美國大選，你看好誰勝選？'),(3,1,'Q3','你認為誰當選對台灣比較有利？'); /*!40000 ALTER TABLE `vote_question` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vote_history` -- DROP TABLE IF EXISTS `vote_history`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `vote_history` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `vote_option_id` int NOT NULL, `vote_at` datetime DEFAULT (now()), PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `vote_option_id` (`vote_option_id`), CONSTRAINT `vote_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), CONSTRAINT `vote_history_ibfk_2` FOREIGN KEY (`vote_option_id`) REFERENCES `vote_option` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vote_history` -- LOCK TABLES `vote_history` WRITE; /*!40000 ALTER TABLE `vote_history` DISABLE KEYS */; INSERT INTO `vote_history` VALUES (1,1,4,'2024-07-05 11:18:05'),(2,1,7,'2024-07-05 11:18:40'),(3,1,9,'2024-07-05 11:19:02'),(4,3,1,'2024-07-05 11:20:07'),(5,3,6,'2024-07-05 11:20:07'),(6,3,9,'2024-07-05 11:20:07'); /*!40000 ALTER TABLE `vote_history` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-07-05 11:36:26 ``` #### 查詢美國總統大選問卷投票人次 ```sql= SELECT count(*) FROM vote_history vh JOIN vote_option vo ON vh.vote_option_id = vo.id JOIN vote_question vq ON vo.vote_question_id = vq.id JOIN vote v ON vq.vote_id = v.id WHERE v.id = 1; ``` #### 問卷調查Table Schema ```sql= CREATE TABLE `users` ( `id` int PRIMARY KEY AUTO_INCREMENT, `name` varchar(32) NOT NULL, `phone` varchar(32), `age` int, `email` varchar(64) ); CREATE TABLE `vote` ( `id` integer PRIMARY KEY AUTO_INCREMENT COMMENT '投票', `photo_url` varchar(256), `title` varchar(32) NOT NULL, `content` varchar(1024), `created_at` datetime DEFAULT (now()), `start_date` datetime, `end_date` datetime ); CREATE TABLE `vote_question` ( `id` int PRIMARY KEY, `vote_id` int NOT NULL, `no` varchar(16), `name` varchar(64) ); CREATE TABLE `vote_option` ( `id` int PRIMARY KEY, `vote_question_id` int NOT NULL, `title` varchar(32) ); CREATE TABLE `vote_history` ( `id` int PRIMARY KEY, `user_id` int NOT NULL, `vote_option_id` int NOT NULL, `vote_at` datetime DEFAULT (now()) ); ALTER TABLE `vote_history` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); ALTER TABLE `vote_history` ADD FOREIGN KEY (`vote_option_id`) REFERENCES `vote_option` (`id`); ALTER TABLE `vote_option` ADD FOREIGN KEY (`vote_question_id`) REFERENCES `vote_question` (`id`); ALTER TABLE `vote_question` ADD FOREIGN KEY (`vote_id`) REFERENCES `vote` (`id`); ``` > https://dbdiagram.io/d/AIoT-vote-668750d69939893dae18ad1d #### 問卷調查 參考畫面: https://tw.news.yahoo.com/poll/040d6e14-fcb3-40f5-a456-380d33aeb37b #### ER Model工具 https://dbdiagram.io/home 標記清單: https://dbml.dbdiagram.io/docs 欄位語法: https://dbdiagram.io/docs/tables 關聯語法: https://dbdiagram.io/docs/relationships ## 2024-07-03 #### Andy在7/3總共畫了多少錢 ```sql= SELECT o.date, u.name, sum(o.quantity*p.price) AS 'Andy很會花錢' FROM orders o JOIN USER u ON u.id = o.member_id JOIN products p ON p.name = o.items WHERE u.name = 'Andy' AND o.date = '2024-07-03'; ``` #### 正規化練習 ```sql= -- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64) -- -- Host: localhost Database: new_db -- ------------------------------------------------------ -- Server version 8.4.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `org_orders` -- DROP TABLE IF EXISTS `org_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `org_orders` ( `date` date DEFAULT NULL, `member_id` varchar(16) NOT NULL, `member_name` varchar(64) DEFAULT NULL, `items` varchar(128) NOT NULL, `price` varchar(128) NOT NULL, `quantity` varchar(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `org_orders` -- LOCK TABLES `org_orders` WRITE; /*!40000 ALTER TABLE `org_orders` DISABLE KEYS */; INSERT INTO `org_orders` VALUES ('2024-07-04','A001','小明','PS4,PS5,搖桿','3000,10000,3000','1,1,2'),('2024-07-03','A002','Andy','鉛筆,橡皮擦','10,15','5,2'),('2024-07-03','A003','Mary','牙線棒','500','1'),('2024-07-03','A003','Mary','牙線棒','500','1'),('2024-07-04','A002','Andy','橡皮擦','15','5'); /*!40000 ALTER TABLE `org_orders` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-07-04 15:35:04 ``` > 此Table尚未正規化 #### 資料庫正規化 1. 第一正規化 - 每一個欄位只能有一筆資料 - 每一個欄位都有一個作為識別唯一的主鍵 2. 第二正規化 - 符合第一正規化 - 消除部分相依 3. 第三正規化 - 符合第二正規化 - 消除遞移相依 #### db dump ```sql= -- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64) -- -- Host: localhost Database: my_system -- ------------------------------------------------------ -- Server version 8.4.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `member` -- DROP TABLE IF EXISTS `member`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `member` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `age` tinyint DEFAULT '0', `phone` varchar(16) DEFAULT NULL, `reg_date` datetime DEFAULT CURRENT_TIMESTAMP, `password` varchar(32) NOT NULL, `lat` decimal(10,7) DEFAULT NULL, `lng` decimal(10,7) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `member` -- LOCK TABLES `member` WRITE; /*!40000 ALTER TABLE `member` DISABLE KEYS */; INSERT INTO `member` VALUES (9,'apple',12,'0987654321',NULL,'1',NULL,NULL),(10,'aaron',22,'0965432187',NULL,'1',NULL,NULL),(11,'andy',33,'0954321876',NULL,'1',NULL,NULL),(12,'alan',21,'0932187654','2024-07-01 15:55:13','1',1.2222000,33.3300000); /*!40000 ALTER TABLE `member` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `orders` -- DROP TABLE IF EXISTS `orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `orders` ( `id` int NOT NULL AUTO_INCREMENT, `order_id` int DEFAULT NULL, `date` date DEFAULT NULL, `member_id` varchar(16) DEFAULT NULL, `items` varchar(128) DEFAULT NULL, `quantity` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `orders` -- LOCK TABLES `orders` WRITE; /*!40000 ALTER TABLE `orders` DISABLE KEYS */; INSERT INTO `orders` VALUES (1,1,'2024-07-04','A001','PS4','1'),(2,2,'2024-07-03','A003','鉛筆','5'),(3,3,'0024-07-03','A002','牙線棒','1'),(4,3,'0024-07-03','A002','牙線棒','1'),(5,5,'2024-07-04','A003','橡皮擦','5'),(6,1,'2024-07-04','A001','PS5','1'),(7,1,'2024-07-04','A001','搖桿','2'),(8,2,'2024-07-03','A003','橡皮擦','2'); /*!40000 ALTER TABLE `orders` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `products` -- DROP TABLE IF EXISTS `products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `products` ( `name` varchar(128) NOT NULL, `price` int NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `products` -- LOCK TABLES `products` WRITE; /*!40000 ALTER TABLE `products` DISABLE KEYS */; INSERT INTO `products` VALUES ('PS4',3000),('PS5',10000),('搖桿',3000),('橡皮擦',15),('牙線棒',500),('鉛筆',10); /*!40000 ALTER TABLE `products` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `rent_history` -- DROP TABLE IF EXISTS `rent_history`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `rent_history` ( `id` int NOT NULL AUTO_INCREMENT, `sno` int NOT NULL, `rent_time` datetime NOT NULL, `return_time` datetime DEFAULT NULL, `member_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `sno` (`sno`), KEY `member_id` (`member_id`), CONSTRAINT `rent_history_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `ubike` (`sno`), CONSTRAINT `rent_history_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `rent_history` -- LOCK TABLES `rent_history` WRITE; /*!40000 ALTER TABLE `rent_history` DISABLE KEYS */; INSERT INTO `rent_history` VALUES (3,2060,'2024-07-04 10:06:12','2024-07-04 11:38:31',9),(4,2060,'2024-09-09 12:30:59','2024-07-04 11:40:03',9),(6,2060,'2024-07-04 10:41:23',NULL,9),(7,2060,'2024-07-04 10:45:31',NULL,9),(8,2060,'2024-07-04 10:47:14',NULL,9),(9,2060,'2024-07-04 13:21:56',NULL,11); /*!40000 ALTER TABLE `rent_history` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ubike` -- DROP TABLE IF EXISTS `ubike`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `ubike` ( `sareaen` varchar(32) DEFAULT NULL COMMENT '行政區英文名', `sarea` varchar(16) DEFAULT NULL COMMENT '行政區中文名', `lng` decimal(10,7) DEFAULT NULL COMMENT '經度', `sna` varchar(16) DEFAULT NULL COMMENT '中文站名', `snaen` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '英文站名', `bemp` int DEFAULT NULL COMMENT '空位數量', `ar` varchar(128) DEFAULT NULL COMMENT '中文地址', `act` tinyint DEFAULT NULL COMMENT '全站禁用狀態(0:禁用、1:啟用)', `sno` int NOT NULL COMMENT '站編號', `aren` varchar(256) DEFAULT NULL COMMENT '英文地址', `tot` int DEFAULT NULL COMMENT '場站總停車格', `_id` int DEFAULT NULL COMMENT '資料編號', `sbi` int DEFAULT NULL COMMENT '場站目前車輛數量', `mday` bigint DEFAULT NULL COMMENT '微笑單車各場站來源資料更新時間', `lat` decimal(10,7) DEFAULT NULL COMMENT '緯度', PRIMARY KEY (`sno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ubike` -- LOCK TABLES `ubike` WRITE; /*!40000 ALTER TABLE `ubike` DISABLE KEYS */; INSERT INTO `ubike` VALUES ('sareaen','sarea',NULL,'sna','snaen',NULL,'ar',NULL,0,'aren',NULL,NULL,NULL,NULL,NULL),('Zhongli Dist.','中壢區',121.1946660,'中央大學圖書館','National Central University Library',2,'中大路300號(中央大學校內圖書館前)',1,2001,'No.300, Zhongda Rd.',14,1,14,20240704094033,24.9681280),('Zhongli Dist.','中壢區',121.2120380,'中壢高中','Jhungli Senior High School',7,'中央西路二段215號對面人行道',1,2002,'No.215, Sec. 2, Zhongyang W. Rd. (opposite)',12,2,12,20240704094053,24.9608150),('Zhongli Dist.','中壢區',121.2248050,'中正公園(中美路)','Zhongzheng Park (Zhongmei Rd.)',-1,'中美路101-113號對面人行道',0,2003,'No.101 to No.113, Zhongmei Rd. (opposite)',34,3,34,20230919103006,24.9591130),('Zhongli Dist.','中壢區',121.2264400,'中壢火車站(前站)中和路','TRA Zhongli Station (Front)Zhonghe Rd.',21,'中和路109號空地',1,2004,'No. 109, Zhonghe Rd.',24,4,24,20240704094041,24.9540420),('Zhongli Dist.','中壢區',121.2402010,'中原大學','Chung Yuan Christian University',24,'中北路200號(中原大學校內懷恩樓後方空地)',1,2005,'No.200, Zhongbei Rd.',28,5,28,20240704094020,24.9579430),('Zhongli Dist.','中壢區',121.2242410,'銀河廣場','Galaxy Square',4,'九和一街48號對面銀河廣場人行道',1,2006,'No.48, Jiuhe 1st St. (opposite)',12,6,12,20240704094042,24.9617160),('Zhongli Dist.','中壢區',121.2246960,'中壢區公所','Civil Affairs Office of Zhongli District',5,'環北路380號旁人行道',1,2007,'No.380, Huanbei Rd.',10,7,10,20240704094027,24.9656970),('Zhongli Dist.','中壢區',121.2173850,'光明公園','Guangming Park',14,'民權路322號對面停車場',1,2008,'No.322, Minquan Rd. (opposite)',24,8,24,20240704094032,24.9628120),('Zhongli Dist.','中壢區',121.2158780,'翠堤橋','Cuiti Bridge',11,'義民路105號旁人行道',1,2009,'No.105, Yimin Rd.',12,9,12,20240704094019,24.9532650),('Zhongli Dist.','中壢區',121.2203490,'中壢國小','Chungli Elementary School',8,'康樂路61號旁公園',1,2010,'No.61, Kangle Rd. (opposite)',12,10,12,20240704094035,24.9540970),('Zhongli Dist.','中壢區',121.2356930,'莒光公園','Juguang Park',6,'寧夏二街43號對面莒光公園入口圓環',1,2011,'No.43,Ningxia 2nd St. (opposite)',6,11,6,20240704094027,24.9514720),('Zhongli Dist.','中壢區',121.2243250,'中壢火車站(後站)','TRA Zhongli Station (Rear)',10,'新興路68-94號對面人行道',1,2012,'No.68 to No.94, Xinxing Rd. (opposite)',10,12,10,20240704094019,24.9523520),('Taoyuan Dist.','桃園區',121.3108630,'桃園市立圖書館桃園分館','Taoyuan City Library Taoyuan Branch',0,'民權路32號前方人行道',0,2013,'No.32, Minquan Rd.',66,13,66,20240412101931,24.9930400),('Zhongli Dist.','中壢區',121.2273000,'中正公園(元化路)','Zhongzheng Park(Yuanhua Rd.)',3,'元化路197巷對面中正公園旁人行道',1,2017,'Ln. 197, Yuanhua Rd. (opposite)',10,14,10,20240704094018,24.9604150),('Zhongli Dist.','中壢區',121.2300270,'中壢區聯合辦公大樓','Zhongli District Joint Office Building',5,'溪洲街298號前方人行道',1,2018,'No.298, Xizhou St.',10,15,10,20240704094024,24.9620680),('Taoyuan Dist.','桃園區',121.3066360,'民族公園','Minzu Park',11,'汕頭街15號對面民族公園旁人行道',1,2019,'No.15, Shantou St.(opposite)',12,16,12,20240704094019,24.9959460),('Taoyuan Dist.','桃園區',121.3128200,'朝陽森林公園','Chaoyang Park',26,'朝陽街104-108號對面機車停車場',1,2020,'No.104 to No.108, Chaoyang St.',28,17,28,20240704094027,24.9991070),('Taoyuan Dist.','桃園區',121.3149980,'桃園延平公園','Taoyuan Yenping Park',15,'大豐路187號對面延平公園旁人行道',1,2021,'No.187, Dafeng Rd.(opposite)',18,18,18,20240704093944,24.9804820),('Taoyuan Dist.','桃園區',121.3115130,'福豐公園','Fufeng Park',7,'建國路昆明路口(西北側)',1,2022,'Jianguo Rd./Kunming Rd.',8,19,8,20240704094018,24.9856520),('Taoyuan Dist.','桃園區',121.3142790,'桃園火車站(後站)','TRA Taoyuan Station (Rear)',14,'延平路26號對面人行道',1,2023,'No.26, Yanping Rd.(opposite)',14,20,14,20240704094025,24.9877950),('Taoyuan Dist.','桃園區',121.3177590,'桃園建國公園','Taoyuan Jianguo Park',7,'桃鶯路93號對面建國公園旁人行道',1,2024,'No.93, Taoying Rd. (opposite)',8,21,8,20240704094023,24.9874180),('Taoyuan Dist.','桃園區',121.3032960,'桃園陽明高中','Taoyuan Municipal Yang Ming High School',18,'德壽街8號前方人行道',1,2025,'No.8, Deshou St.',20,22,20,20240704094043,24.9804040),('Zhongli Dist.','中壢區',121.2615960,'夢幻公園','Dream Park',11,'榮安十三街榮民路口(西南側)',1,2026,'Rongan 13th St./Rongmin Rd.',12,23,12,20240704094031,24.9625410),('Zhongli Dist.','中壢區',121.2592790,'內壢自強公園','Neili Ziqiang Park',11,'強國路46號旁自強公園側人行道',1,2027,'No.46, Qiangguo Rd.',12,24,12,20240704094029,24.9676860),('Zhongli Dist.','中壢區',121.2481140,'內壢復興公園','Neili Fuxing Park',3,'復華街191號對面復興公園旁人行道',1,2028,'No.191, Fuhua St. (opposite)',8,25,8,20240704094024,24.9736300),('Zhongli Dist.','中壢區',121.2632490,'元智大學','Yuan Ze University',13,'遠東路135號(面元智大學左側人行道)',1,2029,'No.135, Yuandong Rd.',22,26,22,20240704094019,24.9703460),('Taoyuan Dist.','桃園區',121.2939080,'同德國中','Tongde Junior High School',8,'南平路479號對面同德國中旁人行道',1,2030,'No.479, Nanping Rd. (opposite)',10,27,10,20240704094040,25.0161300),('Taoyuan Dist.','桃園區',121.2999700,'桃園展演中心(同德六街)','Taoyuan Arts Center(Tongde 6th St.)',4,'同德六街藝文二街口東南側廣場',1,2031,'Tongde 6th St./Yiwen 2nd St.',12,28,12,20240704094034,25.0165510),('Taoyuan Dist.','桃園區',121.3135580,'東溪綠園','Dongxi Greens',7,'成功路二段1號旁小公園',1,2032,'No.1, Sec. 2, Chenggong Rd.',16,29,16,20240704094036,24.9931220),('Pingzhen Dist.','平鎮區',121.2183220,'桃園市立圖書館平鎮分館','Taoyuan Public Library Pingzhen Branch',8,'環南路三段88號右前方人行道',1,2033,'No.88, Sec. 3, Huannan Rd.',12,30,12,20240704094038,24.9400310),('Pingzhen Dist.','平鎮區',121.2166860,'新勢公園','Xinshi Park',25,'延平路一段181號對面新勢公園旁人行道',1,2034,'No.181, Sec. 1, Yanping Rd.(opposite)',28,31,28,20240704094022,24.9506920),('Taoyuan Dist.','桃園區',121.3196910,'三民運動公園','Sanmin Sports Park',31,'三民路一段200號前方人行道',1,2035,'No.200, Sec. 1, Sanmin Rd.',40,32,40,20240704094018,25.0005770),('Taoyuan Dist.','桃園區',121.2982350,'桃園展演中心(南平路)','Taoyuan Arts Center (Nanping Rd.)',7,'南平路336號對面人行道',1,2037,'No.336, Nanping Rd.(opposite)',10,33,10,20240704094015,25.0180380),('Taoyuan Dist.','桃園區',121.2893270,'國強公園','Guochiang Park',6,'國強一街益壽一街口(東北側人行道)',1,2038,'Guociang 1st St./Yishou 1st St.',14,34,14,20240704094016,24.9930770),('Taoyuan Dist.','桃園區',121.3037250,'新埔公園','Xinpu Park',3,'同安街203號對面人行道',1,2039,'No.203, Tong&rsquo;an St.(opposite)',8,35,8,20240704094043,25.0151950),('Zhongli Dist.','中壢區',121.2314540,'信義國小','Hsin Yi Elementary School',5,'成都路36號對面人行道',1,2040,'No.36, Chengdu Rd.(opposite)',6,36,6,20240704094017,24.9510170),('Taoyuan Dist.','桃園區',121.3210760,'桃園市立圖書館大林分館','Taoyuan Public Library Dalin Branch',11,'樹仁二街37號後方人行道',1,2041,'No.37, Shuren 2nd St.',12,37,12,20240704094026,24.9778120),('Taoyuan Dist.','桃園區',121.3130220,'桃園火車站(前站)','TRA Taoyuan Station (Front)',8,'中正路1號面火車站右方人行道',1,2042,'No.1, Zhongzheng Rd.',14,38,14,20240704094045,24.9896180),('Taoyuan Dist.','桃園區',121.2929540,'瑞慶公園','Ruiching Park',11,'中埔一街362號對面瑞慶公園旁人行道',1,2043,'No.362, Zhongpu 1st St.(opposite)',12,39,12,20240704094025,25.0191290),('Taoyuan Dist.','桃園區',121.2995600,'中寧公園','Zhongning Park',5,'同德二街177號對面中寧公園旁人行道',1,2044,'No.177, Tongde 2nd St.(opposite)',6,40,6,20240704094015,25.0099930),('Taoyuan Dist.','桃園區',121.3081260,'陽明運動公園','Yangming Sports Park',5,'介壽路199號前方人行道',1,2045,'No.199, Jieshou Rd.',8,41,8,20240704094029,24.9822850),('Taoyuan Dist.','桃園區',121.3147600,'大業國小','Da Yie Elementary School',5,'民光東路280號對面人行道',1,2046,'No.280, Minguang E. Rd.(opposite)',12,42,12,20240704094024,25.0068960),('Taoyuan Dist.','桃園區',121.3198690,'大有國中','Da You Junior High School',7,'大有路民光東路口西北側人行道',1,2047,'Dayou Rd./Minguang E. Rd.',12,43,12,20240704094042,25.0085850),('Zhongli Dist.','中壢區',121.2572550,'莊敬廣場','Zhuangjing Square',10,'莊敬路38-1號對面自行車停放區',1,2048,'No.38-1, Zhuangjing Rd.(opposite)',16,44,16,20240704094019,24.9718510),('Zhongli Dist.','中壢區',121.2504060,'吉林路吉利六街口','Jilin Rd. &amp; Jili 6th St. Intersection',1,'吉林路吉利六街口東南側人行道',1,2049,'Jilin Rd./Jili 6th St.',10,45,10,20240704094022,24.9829000),('Zhongli Dist.','中壢區',121.2359240,'龍岡森林公園','Longgang Park',16,'龍岡路三段289號對面龍岡森林公園旁人行道',1,2050,'No.289, Sec. 3, Longgang Rd.(opposite)',20,46,20,20240704094041,24.9339740),('Bade Dist.','八德區',121.3054690,'君臨公園','Junlin Park',13,'忠勇六街1號對面人行道',1,2051,'No.1, Zhongyong 6th St.(opposite)',22,47,22,20240704094016,24.9645240),('Luzhu Dist.','蘆竹區',121.2902290,'光明國小','Guang Ming Elementary School',35,'南昌路忠孝西路口東北側人行道',1,2053,'Nanchang Rd./Zhongxiao W. Rd.',42,48,42,20240704094036,25.0412750),('Taoyuan Dist.','桃園區',121.2810640,'桃園龍岡公園','Taoyuan Longgang Park',4,'國豐一街101號對面龍岡公園旁人行道',1,2054,'No.101, Guofeng 1st St.(opposite)',8,49,8,20240704094036,24.9903900),('Taoyuan Dist.','桃園區',121.2789430,'桃園龍鳳公園','Taoyuan Longfeng Park',19,'國豐五街51號對面龍鳳公園旁人行道',1,2055,'No.51, Guofeng 5rd St.(opposite)',20,50,20,20240704094044,24.9896880),('Taoyuan Dist.','桃園區',121.3005370,'桃園市政府文化局','Departmaent of Cultural Affairs, Taoyuan',6,'縣府路21號面文化局左側路側',1,2056,'No.21, Xianfu Rd.',12,51,12,20240704094024,24.9941840),('Taoyuan Dist.','桃園區',121.3171300,'成功春日路口','Chenggong &amp; Chunri Rd. Intersection',15,'成功路二段春日路口東南側人行道',1,2057,'Chenggong Rd./Chunri Rd.',22,52,22,20240704094044,24.9941770),('Zhongli Dist.','中壢區',121.2568530,'忠孝廣場','Zongxiao Square',8,'忠孝路8-22號對面路側',1,2058,'No.8-22, Zhongxiao Rd.(opposite)',8,53,8,20240704094042,24.9725160),('Zhongli Dist.','中壢區',121.2276970,'忠福廣場','Zhongfu Square',8,'南園二路292號對面忠福廣場旁人行道',1,2059,'No.292, Nanyuan 2nd Rd.(opposite)',10,54,10,20240704094035,24.9718050),('Zhongli Dist.','中壢區',121.2290110,'健行科技大學','Chien Hsin University of Science and Technology',11,'健行路229號(商學大樓後人行道)',1,2060,'No.229, Jianxing Rd.',16,55,16,20240704094029,24.9467780),('Zhongli Dist.','中壢區',121.2345530,'啟英高中','Chi-Ying Senior High School',3,'西園路76號對面啟英高中旁人行道',1,2061,'No.76, Xiyuan Rd.(opposite)',12,56,12,20240704094018,24.9756100),('Zhongli Dist.','中壢區',121.2501020,'內壢文化公園','Neili Wenhua Park',8,'文化路長春二路西北側公園旁人行道',1,2062,'Wenhua Rd./Changchun 2nd Rd.',10,57,10,20240704094031,24.9795670),('Zhongli Dist.','中壢區',121.2299250,'萬能科技大學','Vanung University of Technology',11,'萬能路1號(萬芳樓後方人行道)',1,2063,'No.1, Changchun 2nd Rd.',20,58,20,20240704094041,24.9927490),('Taoyuan Dist.','桃園區',121.2932090,'中德里休閒廣場','Zhongde Square',13,'國際一路國際二路口西南側停車場',1,2064,'Guoji 1st Rd./Guoji 2nd Rd.',18,59,18,20240704094028,24.9622750),('Taoyuan Dist.','桃園區',121.3239240,'桃園巨蛋','Taoyuan Arena',8,'三民路100號對面人行道',1,2065,'No.100, Sanmin Rd.(opposite)',18,60,18,20240704094017,24.9953930),('Guishan Dist.','龜山區',121.3376600,'龜山區公所','Civil Affairs Office of Guishan District',6,'中山街26號前方人行道',1,2066,'No.26, Zhongshan St.',10,61,10,20240704094044,24.9925320),('Guishan Dist.','龜山區',121.3394850,'龜山國中','Guishan Junior High School',7,'自強西路66號前方人行道',1,2067,'No.66, Ziqiang W. Rd.',12,62,12,20240704094040,24.9975360),('Taoyuan Dist.','桃園區',121.3038840,'西門綠園','Ximen Greens',6,'文康街61號對面人行道',1,2068,'No.61, Wenkang St.(opposite)',10,63,10,20240704094020,24.9939680),('Taoyuan Dist.','桃園區',121.2689810,'衛福部桃園醫院','Taoyuan General Hospital, Ministry of Health and Welfare',2,'龍壽街60號對面人行道',1,2069,'No.60, Longshou St.(opposite)',10,64,10,20240704094017,24.9793890),('Zhongli Dist.','中壢區',121.2551680,'環中東永福路口','Huanzhong E. &amp; Yongfu Rd. Intersection',0,'環中東路325號前人行道',0,2070,'No.325, Huanzhong E. Rd.',10,65,10,20240704094030,24.9620300),('Luzhu Dist.','蘆竹區',121.2978560,'南崁高中','Nankan Senior High School',26,'仁愛路二段1號(南崁高中旁人行道)',1,2071,'No.1, Sec. 2, Ren&rsquo;ai Rd.',34,66,34,20240704094032,25.0524560),('Luzhu Dist.','蘆竹區',121.2876770,'光明河濱公園','Guangming Riverside Park',5,'光明路二段100號對面路側',1,2073,'No.100, Sec. 2, Guangming Rd.(opposite)',20,67,20,20240704094020,25.0468080),('Luzhu Dist.','蘆竹區',121.2696850,'開南大學','Kainan University',37,'開南路1號(行政大樓南棟前停車場)',1,2074,'No.1, Kainan Rd.',46,68,46,20240704094035,25.0111060),('Luzhu Dist.','蘆竹區',121.2993970,'蘆竹吉林公園','Luzhu Jilin Park',24,'吉林路121-137號對面吉林公園旁人行道',1,2075,'No.121-137, Jilin Rd.(opposite)',24,69,24,20240704094026,25.0495600),('Daxi Dist.','大溪區',121.2876130,'月眉停車場','Yuemei Parking Lot',30,'月眉15鄰105號',1,2077,'No.105, Yuemei',34,70,34,20240704094040,24.8892790),('Daxi Dist.','大溪區',121.2854530,'大溪中正公園','Daxi Zhongzheng Park',26,'普濟路80號旁停車場',1,2078,'No.80, Puji Rd.',28,71,28,20240704094015,24.8831510),('Taoyuan Dist.','桃園區',121.2947390,'忠孝兒童遊樂場','Zhongxiao Children\'s Playground',7,'宏昌七街泰昌三街西南側廣場',1,2079,'Hongchang 7th St./Taichang 3rd St.',12,72,12,20240704094034,24.9868490),('Taoyuan Dist.','桃園區',121.3016800,'桃園市政府','Taoyuan City Government',4,'縣府路3號對面廣場',1,2080,'No.3, Xianfu Rd.(opposite)',18,73,18,20240704094045,24.9927990),('Zhongli Dist.','中壢區',121.2045790,'五權公園','Wuquan Park',4,'正大街五族二街西北側人行道',1,2081,'Zhengda St./Wuzu 2nd St.',8,74,8,20240704094024,24.9588670),('Zhongli Dist.','中壢區',121.2260500,'中壢新興公園','Zhongli Xinxing Park',2,'林森路90號前方人行道',1,2082,'No.90, Linsen Rd.',8,75,8,20240704094018,24.9452650),('Zhongli Dist.','中壢區',121.2579720,'桃園市立圖書館內壢分館','Taoyuan Public Library Neili Branch',8,'光華三街10號前方人行道',1,2083,'No.10, Guanghua 3rd St.',14,76,14,20240704094027,24.9779450),('Zhongli Dist.','中壢區',121.1909580,'中央大學依仁堂','Office of Physical Education, NCU',8,'中大路300號(松苑餐廳前方廣場)',1,2085,'No.300, Zhongda Rd.',14,77,14,20240704094025,24.9671190),('Bade Dist.','八德區',121.2979070,'廣豐公園','Guangfeng Park',13,'公園路廣豐二街東北側人行道',1,2088,'Gongyuan Rd./Guangfeng 2nd St.',28,78,28,20240704094036,24.9660150),('Pingzhen Dist.','平鎮區',121.2064130,'廣仁公園','Guangren Park',23,'廣南路168號對面公園旁人行道',1,2090,'No.168, Guangnan Rd.(opposite)',26,79,26,20240704094021,24.9394780),('Guishan Dist.','龜山區',121.3428030,'璟都江山社區','Jingdu-Jiangshan Community',6,'光峰路131巷6號前方人行道',1,2095,'No.6, Ln. 131, Guangfeng Rd.',8,80,8,20240704094032,25.0023000),('Luzhu Dist.','蘆竹區',121.2854890,'捷運山鼻站(A10)','MRT Shanbi Station (A10)',5,'南山路三段176號旁自行車停放區',1,2097,'No.176, Sec. 3, Nanshan Rd.',10,81,10,20240704094016,25.0809420),('Dayuan Dist.','大園區',121.2097410,'捷運大園站(A15)','MRT Dayuan Station(A15)',7,'橫湳一路1號捷運站旁人行道',1,2098,'No.1, Hengnan 1st. Rd.',12,82,12,20240704094025,25.0566270),('Dayuan Dist.','大園區',121.2221690,'捷運領航站(A17)','MRT Linghang Station (A17)',8,'領航北路四段358號對面人行道',1,2099,'No.358, Sec. 4, Linghang N. Rd.(opposite)',10,83,10,20240704094033,25.0230210),('Zhongli Dist.','中壢區',121.2139320,'捷運高鐵桃園站(A18)','MRT HSR Taoyuan Station (A18)',14,'高鐵北路一段6號(對面捷運站出口外側人行道)',1,2100,'No.6, Sec. 1, Gaotie N. Rd.(opposite)',16,84,16,20240704094029,25.0137750),('Zhongli Dist.','中壢區',121.2027820,'捷運桃園體育園區站(A19)','MRT Taoyuan Sports Park Station (A19)',4,'高鐵南路二段369號對面人行道',1,2101,'No.369, Sec. 2, Gaotie S. Rd.(opposite)',4,85,4,20240704094031,25.0015680),('Zhongli Dist.','中壢區',121.2209670,'捷運環北站(A21)','MRT Huanbei Station (A21)',3,'中豐北路26號旁人行道',1,2102,'No.26, Zhongfeng N. Rd.',8,86,8,20240704094045,24.9675630),('Zhongli Dist.','中壢區',121.2057980,'青埔國中','Qing Pu Junior High School',5,'領航北路二段281號前方人行道',1,2103,'No.281, Sec. 2, Linghang N. Rd.',8,87,8,20240704094022,25.0108320),('Zhongli Dist.','中壢區',121.2010540,'領航公園','Linghang Park',18,'領航北路一段永裕路口北側公園旁人行道',1,2104,'Sec. 1, Linghang N. Rd./Yongyu Rd.',22,88,22,20240704094021,25.0074130),('Zhongli Dist.','中壢區',121.2608420,'仁祥公園','Renxiang Park',9,'華祥一街3巷2號對面人行道',1,2105,'No.2, Ln. 3, Huaxiang 1st St.(opposite)',18,89,18,20240704094036,24.9488640),('Zhongli Dist.','中壢區',121.2632800,'篤行公園','Duxing Park',5,'永福路47號對面人行道',1,2106,'No.47, Yongfu Rd.(opposite)',8,90,8,20240704094021,24.9582880),('Taoyuan Dist.','桃園區',121.2994340,'桃園市政府警察局桃園分局','Taoyuan Police Department Taoyuan Branch',0,'永安路375號旁人行道',1,2107,'No.375, Yong&rsquo;an Rd.',10,91,10,20240704094020,25.0004180),('Taoyuan Dist.','桃園區',121.3018420,'慈文國中','Tzu Wen Junior High School',7,'中正路835號前方人行道',1,2108,'No.835, Zhongzheng Rd.',12,92,12,20240704094032,25.0089160),('Taoyuan Dist.','桃園區',121.3056330,'同新公園','Tongxin Park',9,'同安街經國路251巷口(東南側人行道)',1,2109,'Tong&rsquo;an St./Ln. 251, Jingguo Rd.',12,93,12,20240704094042,25.0123890),('Taoyuan Dist.','桃園區',121.3029250,'桃園永康公園','Taoyuan Yongkang Park',5,'力行路中正五街口東南側人行道',1,2110,'Lixing Rd./Zhongzheng 5th St.',8,94,8,20240704094033,25.0018900),('Taoyuan Dist.','桃園區',121.2920880,'桃園市立游泳池','Taoyuan City Swimming Pool',7,'吉昌街86號(對面人行道)',1,2111,'No.86, Jichang St.(opposite)',10,95,10,20240704094038,24.9894580),('Taoyuan Dist.','桃園區',121.3139030,'寶山公園','Baoshan Park',5,'民有三街517號對面公園旁人行道',1,2112,'No.517, Minyou 3rd St.(opposite)',14,96,14,20240704094033,25.0131320),('Taoyuan Dist.','桃園區',121.2821260,'國強一街上海路口','Guochiang 1st St. &amp; Shanghai Rd. Intersection',25,'國強一街上海路口西北側高架橋下廣場',1,2113,'Guoqiang 1st St./Shanghai Rd.',30,97,30,20240704094018,24.9920590),('Taoyuan Dist.','桃園區',121.3170240,'青溪公園','Qingxi Park',7,'自強路217號前方人行道',1,2114,'No.217, Ziqiang Rd.',10,98,10,20240704094020,25.0009590),('Zhongli Dist.','中壢區',121.2527510,'甲蟲公園','Jiachong Park',3,'元生二街元生三街口西北側公園旁人行道',1,2115,'Yuansheng 2nd St./Yuansheng 3rd St.',8,99,8,20240704094042,24.9775420),('Zhongli Dist.','中壢區',121.2261950,'中壢中央公園','Zhongli Central Park',7,'建國北路9號對面公園人行道',1,2116,'No.9, Jianguo N. Rd.(opposite)',12,100,12,20240704094041,24.9566630),('Pingzhen Dist.','平鎮區',121.2149810,'新榮公園','Xinrong Park',7,'振興西路新德街口西北側公園旁人行道',1,2117,'Zhenxing W. Rd./Xinde St.',8,101,8,20240704094021,24.9459910),('Bade Dist.','八德區',121.3040680,'重慶公園','Chongqing Park',13,'重慶街183號對面公園旁人行道',1,2118,'No.183, Chongqing St.(opposite)',18,102,18,20240704094033,24.9559840),('Guishan Dist.','龜山區',121.3405570,'龜山國小','Guishan Elementary School',11,'大同路23號對面人行道',1,2120,'No.23, Datong Rd.(opposite)',14,103,14,20240704094044,24.9947650),('Guishan Dist.','龜山區',121.3424810,'大同公園','Datong Park',8,'德明路77巷65號對面公園旁人行道',1,2123,'No.65, Ln. 77, Deming Rd.(opposite)',12,104,12,20240704094035,24.9893320),('Luzhu Dist.','蘆竹區',121.2943410,'蘆竹區公所','Civil Affairs District of Luzhu District',5,'南上路3號對面路側',1,2125,'No.3, Nanshang Rd.(opposite)',24,105,24,20240704094042,25.0473250),('Luzhu Dist.','蘆竹區',121.2925550,'峻德宮','Junde Temple',15,'奉化路292號旁路側',1,2126,'No.292, Fenghua Rd.',20,106,20,20240704094040,25.0369990),('Taoyuan Dist.','桃園區',121.2710350,'桃園龍山國小','Taoyuan Long Shan Elementary School',15,'龍祥街94號對面人行道',1,2127,'No.94, Longxiang St.(opposite)',26,107,26,20240704094044,24.9819880),('Dayuan Dist.','大園區',121.1753540,'桃禧航空城酒店','Hotel Orchard Park',8,'大觀路777號(桃禧航空城酒店園航路側人行道)',1,2129,'No.777, Daguan Rd.',10,108,10,20240704094036,25.0617560),('Dayuan Dist.','大園區',121.1958040,'老街溪停車場','Laojie River Parking Lot',19,'大觀路31巷1號旁老街溪上方停車場',1,2130,'No.1, Ln. 31, Daguan Rd.',26,109,26,20240704094017,25.0649740),('Dayuan Dist.','大園區',121.2018250,'大中華幼兒園','Big China Kindergarten',14,'中正三街9號對面空地',1,2131,'No.9, Zhongzheng 3rd St.(opposite)',24,110,24,20240704093938,25.0617900),('Dayuan Dist.','大園區',121.1948520,'華興池','Huaxing Pond',0,'華興路205號對面路側',0,2132,'No.205, Huaxing Rd.(opposite)',36,111,36,20230828084025,25.0591250),('Pingzhen Dist.','平鎮區',121.2497570,'東安國中','Dong An Junior High School',18,'平東路168號(面東安國中右側人行道)',1,2133,'No.168, Pingdong Rd.',20,112,20,20240704093836,24.9164970),('Luzhu Dist.','蘆竹區',121.2945750,'五福停車場','Wufu Parking Lot',22,'五福一路120號對面人行道',1,2137,'No.120, Wufu 1st Rd.(opposite)',24,113,24,20240704094023,25.0558510),('Luzhu Dist.','蘆竹區',121.2590570,'大竹國小','Dazhu Elementary School',23,'南竹路五段271號對面人行道',1,2138,'No.271, Sec. 5, Nanzhu Rd.(opposite)',28,114,28,20240704094026,25.0244810),('Luzhu Dist.','蘆竹區',121.2602680,'中興環保公園','Zhongxing Eco-Park',13,'大興四街61巷及大興四街路口公園內',1,2139,'Ln. 61, Daxing 4th St./Daxing 4th St.',20,115,20,20240704094037,25.0162210),('Taoyuan Dist.','桃園區',121.2979190,'桃園中正公園(同安街)','Taoyuan Zhongzheng Park (Tung\'an St.)',9,'同安街601號旁公園人行道',1,2140,'No.601, Tong&rsquo;an St.',14,116,14,20240704094035,25.0237010),('Taoyuan Dist.','桃園區',121.3103240,'建新公園','Jianxin Park',2,'陽明十二街25號對面公園旁人行道',1,2141,'No.25, Yangming 12th St.(opposite)',10,117,10,20240704094031,24.9792060),('Taoyuan Dist.','桃園區',121.3086330,'中正一正康一街口','Zhongzheng 1st St. &amp; Zhengkang 1st St. Intersection',0,'正康一街1號對面機車停車場',0,2142,'No.1, Zhengkang 1st St.(opposite)',34,118,34,20230621124340,24.9996370),('Pingzhen Dist.','平鎮區',121.2211360,'平鎮和平公園','Pingzhen Heping Park',20,'莒光路39號對面路側',1,2143,'No.39, Juguang Rd.(opposite)',20,119,20,20240704094044,24.9475290),('Pingzhen Dist.','平鎮區',121.2148210,'莊敬里民集會所','Zhuangjing Civil Activity Center',16,'自強街莊福路口東北側人行道',1,2144,'Ziqiang St.,/Zhuangfu Rd.',20,120,20,20240704094041,24.9040800),('Pingzhen Dist.','平鎮區',121.2118170,'中豐坤慶路口','Zhongfeng &amp; Kunqing Rd. Intersection',15,'坤慶路1號前方人行道',1,2145,'No.1, Kunqing Rd.',18,121,18,20240704094027,24.9026200),('Longtan Dist.','龍潭區',121.2100320,'龍潭大池(龍元路)','Longtan Pond (Longyuan Rd.)',20,'龍元路144號對面龍潭大池旁人行道',1,2147,'No.144, Longyuan Rd.(opposite)',26,122,26,20240704094017,24.8638510),('Longtan Dist.','龍潭區',121.2185190,'龍潭運動公園(北龍路)','Longtan Sports Park (Beilong Rd.)',0,'公園路北龍路口東南側公園旁人行道',0,2148,'Gongyuan Rd./Beilong Rd.',38,123,38,20240704094035,24.8682420),('Zhongli Dist.','中壢區',121.2561270,'黎明公園','Liming Park',2,'同慶路榮民南路口西南側公園前方路側',1,2149,'Tongqing Rd./Rongmin S. Rd.',6,124,6,20240704094025,24.9398940),('Zhongli Dist.','中壢區',121.2635480,'興仁公園','Xinren Park',5,'興仁路二段120號對面公園內人行道',1,2150,'No.120, Sec. 2, Xingren Rd.(opposite)',10,125,10,20240704094016,24.9662560),('Zhongli Dist.','中壢區',121.2533810,'自立新村(榮安一街)','Zili Community (Rong\'an 1st St.)',6,'榮安一街自立三街2巷北側人行道',1,2151,'Rong&rsquo;an 1st St./Ln. 2, Zili 3rd St.',10,126,10,20240704094024,24.9654320),('Zhongli Dist.','中壢區',121.2612930,'晉元路仁德一街口','Jinyuan Rd. &amp; Rende 1st St. Intersection',3,'仁德一街28-4號對面路側',1,2152,'No.28-4, Rende 1st St.(opposite)',6,127,6,20240704094029,24.9509260),('Zhongli Dist.','中壢區',121.2448050,'龍和公園','Longhe Park',6,'龍和三街290號(活動中心旁公園人行道)',1,2153,'No.290, Longhe 3rd St.',8,128,8,20240704094022,24.9342210),('Guishan Dist.','龜山區',121.3372640,'萬壽中興路口','Wanshou &amp; Zhongxin Rd. Interstction',3,'萬壽路二段981號對面停車場',1,2154,'No.981, Sec. 2, Wanshou Rd.(opposite)',12,129,12,20240704094044,24.9949380),('Taoyuan Dist.','桃園區',121.3040470,'大同西路簡易公園','Datong W. Rd. Simple Park',8,'大同西路7巷4號對面公園',1,2155,'No.4, Ln. 7, Datong W. Rd.(opposite)',8,130,8,20240704094028,24.9881640),('Taoyuan Dist.','桃園區',121.2878790,'霖園兒童公園','Linyuan Children\'s Park',16,'國鼎二街132號(對面公園東側人行道)',1,2156,'No.132, Guoding 2nd St.(opposite)',18,131,18,20240704094026,24.9860790),('Taoyuan Dist.','桃園區',121.3050200,'家樂福經國店','Carrefour Jingguo Branch',7,'天祥三街10號對面人行道',1,2157,'No.10, Tianxiang 3rd St.(opposite)',12,132,12,20240704094021,25.0163830),('Taoyuan Dist.','桃園區',121.3011740,'玉山公園','Yushan Park',7,'南豐二街120號斜前方公園側',1,2158,'No.120, Nanfeng 2nd St.(opposite)',14,133,14,20240704094020,24.9826210),('Taoyuan Dist.','桃園區',121.3134370,'會稽國中','Kuai Ji Junior High School',6,'大興路222號(面會稽國中校門左側人行道)',1,2159,'No.222, Daxing Rd.(opposite)',8,134,8,20240704094040,25.0161150),('Yangmei Dist.','楊梅區',121.1462470,'楊梅火車站(前站)','TRA Yangmei Station (Front)',15,'大成路256號(火車站前方人行道)',1,2162,'No.256, Dacheng Rd.',28,135,28,20240704094019,24.9138450),('Yangmei Dist.','楊梅區',121.1549950,'楊明國中','Yang-Ming Junior High School',26,'新農街337號(面楊明國中左側人行道)',1,2163,'No.337, Xinnong St.',28,136,28,20240704094040,24.9105460),('Yangmei Dist.','楊梅區',121.1471790,'楊梅國中','Yang Mei Junior High School',23,'校前路86號對面人行道',1,2164,'No.86, Xiaoqian Rd.(opposite)',26,137,26,20240704094033,24.9040930),('Taoyuan Dist.','桃園區',121.3035960,'經國國中','Jingguo Junior High School',3,'經國路276號(面經國國中校門左側人行道)',1,2165,'No.276, Jingguo Rd.(opposite)',8,138,8,20240704094023,25.0217000),('Pingzhen Dist.','平鎮區',121.2252940,'獅子林社區活動中心','Shizilin Community Activity Center',21,'大智街88號對面',1,2166,'No.88, Dazhi St.(opposite)',22,139,22,20240704094039,24.9283390),('Zhongli Dist.','中壢區',121.2228320,'紫雲宮','Ziyun Temple',0,'自忠二街126號對面停車場',0,2167,'No.126, Zizhong 2nd St.(opposite)',24,140,24,20240509100024,24.9448450),('Pingzhen Dist.','平鎮區',121.2063520,'民俗文化公園','Folk Cultural Park',27,'上海路及上海路119巷口西南側廣場',1,2168,'Shanghai Rd./Ln. 119, Shanghai Rd.',38,141,38,20240704094034,24.9119140),('Taoyuan Dist.','桃園區',121.2898930,'風禾公園','Fenghe Park',25,'文中二路慈文路口東北側人行道',1,2169,'Wenzhong 2nd Rd./Ciwen Rd.',30,142,30,20240704094035,25.0009910),('Taoyuan Dist.','桃園區',121.3207330,'財政部北區國稅局桃園分局','National Taxation Bureau of the Nothern Area, Ministry of Finance (Taoyuan Branch)',-1,'三元街150號',1,2170,'No.150, Sanyuan St.',8,143,8,20240704094019,25.0037820),('Taoyuan Dist.','桃園區',121.3085170,'桃園觀光夜市旅客服務中心','Taoyuan Tourist Night Market Service Center',23,'正康三街8號旁停車場',1,2171,'No.8, Zhengkang 3rd St.',26,144,26,20240704094040,25.0040890),('Dayuan Dist.','大園區',121.2205060,'大園國際高中','Dayuan International Senior High School',5,'大成路二段37號對面人行道',1,2172,'No.37, Sec. 2, Dacheng Rd.(opposite)',14,145,14,20240704093833,25.0208340),('Bade Dist.','八德區',121.3083820,'同福公園','Tongfu Park',17,'和義街50號對面人行道',1,2173,'No.50, Heyi St.(opposite)',22,146,22,20240704094027,24.9592570),('Zhongli Dist.','中壢區',121.2508560,'常樂公園','Changle Park',5,'文化二路237-243號旁公園人行道',1,2176,'No.237-243, Wenhua 2nd Rd.',10,147,10,20240704094021,24.9768810),('Zhongli Dist.','中壢區',121.2486250,'金鋒太子','Jinfeng Prince',6,'金鋒四街50巷2號對面',1,2177,'No.2, Ln. 50, Jinfeng 4th St.(opposite)',6,148,6,20240704094027,24.9459110),('Zhongli Dist.','中壢區',121.2597240,'華愛兒童公園','Hua-Ai Children\'s Park',5,'華愛街35號對面公園旁人行道',1,2178,'Hua-Ai Children\'s Park',8,149,8,20240704094043,24.9523030),('Luzhu Dist.','蘆竹區',121.2913980,'南榮公園','Nanrong Park',18,'南平街洛陽街口南側公園旁人行道',1,2179,'Nanping St./Luoyang St.',22,150,22,20240704094027,25.0451650),('Longtan Dist.','龍潭區',121.2217610,'龍潭行政園區','Longtan Administrative Park',16,'中興路681號對面人行道',1,2180,'No.681, Zhongxing Rd.(opposite)',26,151,26,20240704094024,24.8699620),('Zhongli Dist.','中壢區',121.1755170,'過嶺國中','Guo Ling Junior High School',5,'松智路過嶺路一段路口人行道',1,2181,'Songzhi Rd./Sec. 1, Guoling Rd.',6,152,6,20240704094022,24.9602030),('Zhongli Dist.','中壢區',121.1779060,'南方莊園','Nanfang Park',5,'樹籽路8號對面停車場旁人行道',1,2182,'No.8, Shuzi Rd.(opposite)',6,153,6,20240704094045,24.9672840),('Guishan Dist.','龜山區',121.3304720,'陸光河濱公園','Luguang Riverside Park',19,'同心二路19號對面公園內人行道',1,2184,'No.19, Tongxin 2nd Rd.(opposite)',20,154,20,20240704094029,24.9948500),('Guishan Dist.','龜山區',121.3319880,'山福里集會所','Shanfu Civil Activity Center',4,'中興路152號旁停車場',1,2185,'No.152, Zhongxing Rd.',8,155,8,20240704094029,24.9889650),('Taoyuan Dist.','桃園區',121.3168200,'大檜溪公園','Dakuaixi Park',3,'興一街65巷50號(地下停車場出入口旁公園)',1,2186,'No.50, Ln. 65, Xing 1st St.',6,156,6,20240704094028,25.0137810),('Dayuan Dist.','大園區',121.1719910,'大園國中','Dayuan Junior High School',7,'園科路400號前人行道',1,2201,'No.400, Yuanhang Rd.',26,157,26,20240704094021,25.0539760),('Xinwu Dist.','新屋區',121.1077160,'清華高中','Ching Hua High School',16,'中華路658號前方人行道',1,2202,'No.658, Zhonghua Rd.',18,158,18,20240704094031,24.9816990),('Guanyin Dist.','觀音區',121.1338810,'觀音高中','Taoyuan Municipal Guanyin High School',19,'文中路46號對面人行道',1,2203,'46F., Wenzhong Rd.(opposite)',24,159,24,20240704094029,25.0149780),('Taoyuan Dist.','桃園區',121.2982500,'溫州公園','Wenzhou Park',-2,'大興西路二段139巷173號旁公園人行道',1,2204,'No.173, Ln. 139, Sec. 2, Daxing W. Rd.',8,160,8,20240704094030,25.0080000),('Pingzhen Dist.','平鎮區',121.2249300,'忠愛公園','Zhong&rsquo;ai Park',22,'金陵路二段454巷18號旁公園',1,2205,'No.18, Ln. 454, Sec. 2, Jinling Rd.',24,161,24,20240704094035,24.9330600),('Zhongli Dist.','中壢區',121.2517100,'南亞技術學院','Nanya Institute of Technology',13,'中山東路三段414號內機車停車場',1,2206,'No.414, Sec. 3, Zhongshan E. Rd.',16,162,16,20240704094022,24.9375600),('Zhongli Dist.','中壢區',121.2617410,'華勛公園','Huaxun Park',13,'華仁街68巷34號對面公園',1,2207,'No.34, Ln. 68, Huaren St.(opposite)',22,163,22,20240704094018,24.9466220),('Yangmei Dist.','楊梅區',121.1484820,'中山親水公園','Zhongshan Waterfront Park',18,'新農街80號旁公園內',1,2208,'No.80, Xinnong St.',20,164,20,20240704094034,24.9096570),('Guishan Dist.','龜山區',121.3449600,'楓樹公園','Fengshu park',16,'楓樹一街19號對面公園',1,2209,'No.19, Fengshu 1st St.(opposite)',22,165,22,20240704094030,25.0046500); /*!40000 ALTER TABLE `ubike` ENABLE KEYS */; UNLOCK TABLES; -- -- Temporary view structure for view `ubike_lite` -- DROP TABLE IF EXISTS `ubike_lite`; /*!50001 DROP VIEW IF EXISTS `ubike_lite`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `ubike_lite` AS SELECT 1 AS `sna`, 1 AS `bemp`, 1 AS `sno`, 1 AS `tot`, 1 AS `sbi`*/; SET character_set_client = @saved_cs_client; -- -- Temporary view structure for view `ubike_lite1` -- DROP TABLE IF EXISTS `ubike_lite1`; /*!50001 DROP VIEW IF EXISTS `ubike_lite1`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `ubike_lite1` AS SELECT 1 AS `sna`, 1 AS `bemp`, 1 AS `tot`, 1 AS `sbi`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `user` -- DROP TABLE IF EXISTS `user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user` ( `id` varchar(16) NOT NULL, `name` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user` -- LOCK TABLES `user` WRITE; /*!40000 ALTER TABLE `user` DISABLE KEYS */; INSERT INTO `user` VALUES ('A001','小明'),('A002','Andy'),('A003','Mary'); /*!40000 ALTER TABLE `user` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping routines for database 'my_system' -- -- -- Final view structure for view `ubike_lite` -- /*!50001 DROP VIEW IF EXISTS `ubike_lite`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `ubike_lite` AS select `ubike`.`sna` AS `sna`,`ubike`.`bemp` AS `bemp`,`ubike`.`sno` AS `sno`,`ubike`.`tot` AS `tot`,`ubike`.`sbi` AS `sbi` from `ubike` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `ubike_lite1` -- /*!50001 DROP VIEW IF EXISTS `ubike_lite1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `ubike_lite1` AS select `ubike`.`sna` AS `sna`,`ubike`.`bemp` AS `bemp`,`ubike`.`tot` AS `tot`,`ubike`.`sbi` AS `sbi` from `ubike` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-07-04 15:21:23 ``` ### UBIKE系統 #### 租借 ``` /* 租借-以電話判斷會員是否存在並取得會員id */ SELECT id FROM `member` m WHERE phone = '0987654321'; /* 租借-以站名判斷站台是否存在並取得sno站台編號 */ SELECT sno FROM ubike u WHERE sna = '健行科技大學'; /* 租借-寫入一筆紀錄到rent_history */ INSERT INTO rent_history (sno, member_id, rent_time) VALUES (2060, 9, now()); /* 查詢租借紀錄 */ SELECT rent_time FROM rent_history rh WHERE member_id = (SELECT id FROM `member` m WHERE phone = '0987654321') AND return_time IS NULL; ``` #### 需求 1. 每分鐘自動更新桃園市Ubike OpenData: http://data.tycg.gov.tw/api/v1/rest/datastore/a1b4714b-3b75-4ff8-a8f2-cc377e4eaa0f?format=csv&limit=999 2. 可以部份站台名稱來搜尋站台的可借、可還數量 3. 可以租借、還車 4. 以使用者、時間區間查詢姓名查詢租借紀錄 > **參考資料:** > 1. https://hackmd.io/@aaronlife/python-topic-Taoyuan-ubike?utm_source=preview-mode&utm_medium=rec > 2. ```python= import requests import csv import pymysql from apscheduler.schedulers.background import BackgroundScheduler from datetime import datetime import time def save_to_mysql(data, file): try: conn = pymysql.connect(host='localhost', port=3306, user='root', password='3939889', database='my_system') cursor = conn.cursor() cursor.execute('DELETE FROM ubike;') for row in data: cursor.execute( f"""INSERT INTO ubike(sareaen,sarea,lng,sna,snaen,bemp,ar,act,sno,aren,tot,_id,sbi,mday,lat) VALUES ('{row[0]}', '{row[1]}', {row[2]}, '{row[3]}', '{row[4].replace('\'', '\\\'')}', {row[5]}, '{row[6]}', {row[7]}, {row[8]}, '{row[9].replace('\'', '\\\'')}', {row[10]}, {row[11]}, {row[10]}, {row[13]}, {row[14]}); """) conn.commit() except Exception as e: print('資料庫連線失敗: ', e, file=file) def get_ubike_data(): with open('ubike.log', 'a', encoding='utf-8') as file: print(f'{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}: 開始更新ubike資料...', file=file) response = requests.get('http://data.tycg.gov.tw/api/v1/rest/datastore/a1b4714b-3b75-4ff8-a8f2-cc377e4eaa0f?format=csv&limit=999') result = response.text.splitlines() result = list(csv.reader(result)) save_to_mysql(result, file) print(f'{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}: 更新ubike資料完成', file=file) def query(name): try: conn = pymysql.connect(host='localhost', port=3306, user='root', password='3939889', database='my_system') cursor = conn.cursor() cursor.execute(f"SELECT sna, bemp, sbi FROM ubike WHERE sna like '%{name}%';") for i in cursor: print(f'名稱: {i[0]}, 可還: {i[1]}, 可借: {i[2]}') except Exception as e: print('資料庫連線失敗: ', e) # 指定時區（一定要指定，否則會失敗） scheduler = BackgroundScheduler(timezone="Asia/Taipei") scheduler.add_job(get_ubike_data, 'interval', minutes=1) scheduler.start() # 啟動排程 while True: try: user_input except: print('===================================') print('-') print('- 桃園市Ubike租借系統') print('-') print('- 開發者: Aaron Ho') print('- 版本: 0.01') print('===================================') print('1. 查詢可借、可還') print('2. 租借') print('3. 還車') print('4. 查詢組借紀錄') print('Q. 結束系統') user_input = input('=> ') if user_input == '1': user_input = input('請輸入站台名稱(quit=離開): ') query(user_input) elif user_input == '2': try: conn = pymysql.connect(host='localhost', port=3306, user='root', password='3939889', database='my_system') cursor = conn.cursor() phone = input('請輸入電話號碼: ') count = cursor.execute(f"SELECT id FROM `member` m WHERE phone = '{phone}'") if count == 0: print('查無此會員') continue else: member_id = cursor.fetchone()[0] sna = input('請輸入要租借的站台名稱: ') count = cursor.execute(f"SELECT sno FROM ubike u WHERE sna = '{sna}';") if count == 0: print('查無此站台') continue else: sno = cursor.fetchone()[0] cursor.execute(f"INSERT INTO rent_history (sno, member_id, rent_time) VALUES ({sno}, {member_id}, '{datetime.now()}');") conn.commit() print(f'會員: {phone}({member_id}), 租借: {sna}({sno})站台成功') except Exception as e: print('資料庫連線失敗: ', e) elif user_input == '3': try: conn = pymysql.connect(host='localhost', port=3306, user='root', password='3939889', database='my_system') cursor = conn.cursor() phone = input('請輸入電話號碼: ') sql = """SELECT id FROM rent_history rh WHERE member_id = (SELECT id FROM `member` m WHERE phone = %s) AND return_time IS NULL ORDER BY rent_time DESC LIMIT 1;""" var = (phone) count = cursor.execute(sql, var) if count == 0: print('沒有租借紀錄') continue # 取得租借紀錄id rent_id = cursor.fetchone()[0] cursor.execute(f"UPDATE rent_history SET return_time = now() WHERE id={rent_id};") conn.commit() print('還車成功.') except Exception as e: print('資料庫連線失敗: ', e) elif user_input == '4': try: conn = pymysql.connect(host='localhost', port=3306, user='root', password='3939889', database='my_system') cursor = conn.cursor() phone = input('請輸入電話號碼: ') sql = f"""SELECT m.id, m.name, m.phone, u.sna, rh.rent_time, rh.return_time FROM rent_history rh JOIN `member` m ON rh.member_id = m.id JOIN ubike u ON rh.sno = u.sno WHERE m.phone = '{phone}'; """ count = cursor.execute(sql) if count == 0: print('無租借紀錄') continue for row in cursor: print(f'會員ID: {row[0]}, 姓名: {row[1]}, 電話: {row[2]}, 站台: {row[3]}, ' + f'租車: {row[4].strftime("%Y/%m/%d %H:%M") if row[4] != None else '無紀錄'}, ' + f'還車: {row[5].strftime("%Y/%m/%d %H:%M") if row[5] != None else '無紀錄'}') except Exception as e: print('資料庫連線失敗: ', e) elif user_input.lower() == 'q': print('掰掰') break ``` ### 練習 ```sql= /* rent_history table新增 member_id欄位，資料型態為int */ ALTER TABLE rent_history ADD member_id int; /* 在rent_history資料表新增外來鍵，將member_id欄位參考至 member資料表的id欄位 ALTER TABLE rent_history ADD FOREIGN KEY (member_id) REFERENCES member(id); /* 撈出租借紀錄裡面所有人的 姓名、電話、站台名稱 */ SELECT m.name, m.phone, u.sna FROM rent_history rh JOIN `member` m ON rh.member_id = m.id JOIN ubike u ON rh.sno = u.sno; ``` ### foreign key(外來鍵) ```sql= /* 建立租借紀錄資料表 */ CREATE TABLE rent_history ( id int PRIMARY KEY AUTO_INCREMENT, name varchar(64) NOT NULL, sna varchar(64) NOT NULL, sno int NOT NULL, rent_time datetime NOT NULL, return_time datetime, FOREIGN KEY (sno) REFERENCES ubike (sno) ); /* 新增租借紀錄 */ INSERT INTO rent_history (name, sna, sno, rent_time) VALUES('aaron', '中壢站', 3333, now()); /* 更新還車紀錄 */ UPDATE rent_history SET return_time = now() WHERE id=1; SELECT * FROM ubike u WHERE sna='中壢站'; /* 撈出租借紀錄的人名、站台名稱、租車時間、歸還時間 */ SELECT rh.name, u.sno, u.sna, u.ar, rh.rent_time FROM rent_history rh JOIN ubike u ON rh.sno=u.sno; ``` ### update ``` UPDATE ubike SET bemp = 99 WHERE sno=2001; ``` ### view(檢視表) ```python= /* 跳過0筆資料後撈5筆 */ SELECT * FROM ubike LIMIT 5 OFFSET 0; /* 跳過5筆資料後撈5筆 */ SELECT * FROM ubike LIMIT 5 OFFSET 5; SELECT * FROM ubike LIMIT 10 OFFSET 0; /* 算出每頁10筆的話總共需要多少頁 */ SELECT CEILING(COUNT(*)/10) FROM ubike u; CREATE VIEW ubike_lite1 AS SELECT sna, bemp, tot, sbi FROM ubike; SELECT * FROM ubike_lite ul ; INSERT INTO ubike_lite(sna, bemp, sno, tot, sbi) values('健行大學2代', 99, 3000, 99, 99); SELECT * FROM ubike_lite ul WHERE sno=3000; SELECT * FROM ubike u WHERE sno=3000; INSERT INTO ubike_lite1 (sna, bemp, tot, sbi) values('XXX', 99, 99, 99); ``` ### limit/offset ```python= /* 跳過0筆資料後撈5筆 */ SELECT * FROM ubike LIMIT 5 OFFSET 0; /* 跳過5筆資料後撈5筆 */ SELECT * FROM ubike LIMIT 5 OFFSET 5; SELECT * FROM ubike LIMIT 10 OFFSET 0; /* 算出每頁10筆的話總共需要多少頁 */ SELECT CEILING(COUNT(*)/10) FROM ubike u; ``` ### 新增資料 ### 修改資料 ### 刪除資料 ### 關聯 ### 外來鍵