# SQL * 建立環境 * 簡介 * 基本語法 * phpmyadmin * ORM * 練習 * 參考 --- ## 建立環境 確認有安裝docker ---- 建立一個.yml檔案並輸入 ``` version: '3.1' services: db: image: mysql:5.7 restart: always environment: MYSQL_ROOT_PASSWORD: passwd ports: - "3306:3306" phpmyadmin: image: phpmyadmin/phpmyadmin restart: always ports: - "8080:80" environment: PMA_HOST: db MYSQL_ROOT_PASSWORD: passwd ``` passwd可以改成自己想要的密碼 ---- 啟動/停止 ``` // up docker-compose -f "filename" up -d //down docker-compose -f "filename" down // ``` ---- 在瀏覽器上輸入localhost:8080可以看到  帳號是root --- ## 簡介 ---- SQL Structured Query Language，結構化查詢語言 SQL是專門用來管理資料庫系統的語言 這堂課使用的資料庫是MYSQL MYSQL是一個關聯式資料庫 ---- 關聯式資料庫跟非關聯最大的區別 就是一張表可以參考另一張表  >來源 https://database.klab.tw/lesson-1/ ---- | 名稱 | 說明 | | -------------- |:--------------------------------- | | Database資料庫 | 就像excel檔 | | Table表 | 就像excel工作表 | | Row | 橫的;一筆資料 | | Column | 直的;資料類別 | | Primary Key 主鍵 | 每筆資料的唯一識別欄位 | | Foreign key 外鍵 | 跟表關聯的欄位 | --- ## 基本語法(mysql) * 資料庫與表 * 新增 * 搜尋 * 刪除/修改 --- ### 資料庫與表 建立資料庫 ``` SQL CREATE DATABASE `database name`; ``` 顯示所有資料庫 ``` SQL SHOW DATABASES; ``` 使用資料庫 ``` USE `database name`; ``` ---- 資料型態 | 型態 | 說明 | | -------- | -------- | | INT | 整數資料 | | DECIMAL(位數,小數點後位數)| 小數點資料 | | VARCHAR | 字串 | | BLOB | 檔案 | | DATE | 日期 | | TIMESTAMP | 時間 | ---- 要先決定使用哪個資料庫 創建表 ``` CREATE TABLE `table name`( 資料類別及型態分隔用, `id` INT PRIMARY KEY, //主鍵 `name` VARCHAR(10), //主鍵也可以寫成 PRIMARY KEY (`id`) ); ``` 刪除表 ``` DROP TABLE `table name`; ``` 查看表格的資料類別 ``` DESCRIBE `table name`; ``` ---- 新增/刪除資料類別 ``` ALTER TABLE `table name` ADD `value` DECIMAL(3,2); ALTER TABLE `table name` DROP COLUMN `value`; ``` --- ### 新增資料 | 符號 | 用途 | | -------- | -------- | | ``反引號 | 識別(如表、資料庫、欄位的名字 ) | |'' 單引""雙引 | 字串 | ---- ``` INERT INTO `table name` VALUES(1,'data'); //需要照著建立表格時的順序填 //不想照順序的話 INERT INTO `table name`(`name`,`id`) VALUES('data',1); ``` 查看整張表 ``` SELECT * FROM `table name`; ``` NULL 代表空值 --- ### 搜尋資料 ``` SELECT //選擇 `DATA` //資料類別 FROM `xxx` //從哪裡 WHERE condition; //查那些 ``` ---- | 特殊字符 | 功能 | 範例 | | ------ | --- | --- | | \* | 所有資料 | | % | 模糊匹配 | | _ | 一個任意字元 | ---- ### WHERE 條件 | 基本運算元 | 功能 | | -------- | -------- | | = | 等於 | | <>,!= | 不等於 | | > | 大於 | | < | 小於 | | >= | 大於等於 | | <= | 小於等於 | ---- | 運算元 | 功能 | 用法 | | --- | --- | --- | | BETWEEN | 在某個範圍內 | BETWEEN value1 AND value2 | | LIKE | 搜尋某種模式 | LIKE 'ABC' | | IN |指定針對某個列的多個可能值 | IN (value1, value2, ...) | 多條件 AND/OR --- ### 修改/刪除 ``` //修改 UPDATE `table name` SET `類別` = value, ...... WHERE condition; //刪除 DELETE FROM `table_name` WHERE condition; ``` 不加條件會報錯 修改sql_safe_updates參數可以解決(不建議) --- ## phpmyadmin --- ## ORM(物件關聯對映) 將資料庫做為一個物件 直接用程式操作資料庫 * 優點:安全、通用 * 缺點:效能、維護 ---- ### python 連結(這是**已經**建立完表格的版本) ``` from sqlalchemy import create_engine, Column, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from sqlalchemy import or_ #連結資料庫 connect = "mysql+mysqlconnector://root:Hsbiteh@localhost:3306/yunnetsumdb" # root:Hsbiteh@localhost:3306/yunnetsumdb # 帳號 密碼 資料庫位址埠號 資料庫 engine = create_engine(connect, echo=True) Base = declarative_base() Session = sessionmaker(bind=engine) dbsession = Session() # 不是很重要 # engine：真的開始建立一個「引擎」（資料庫連線）。 # echo=True：每次執行 SQL 時，印出 SQL 指令到 console，方便除錯。 # Base：定義一個基底類別，之後每張表都要繼承它。 # Session：開一個跟資料庫的溝通通道（session）。 # dbsession：打開 session，之後可以用它 add()、query() 之類的。 #規定資料庫的表格 class User(Base): __tablename__ = 'users' account = Column(String(20),primary_key=True) password = Column(String(20)) mail = Column(String(30)) phone = Column(String(14)) ``` ---- 操作 ``` # --- 增加資料（Create）--- new_user = User(account="test123", password="pass123", mail="test@mail.com", phone="0912345678") dbsession.add(new_user) dbsession.commit() # 記得 commit # --- 查詢資料（Read）--- users = dbsession.query(User).filter( User.account == "test123", # 條件 User.mail == "test@mail.com" ).all() # 或者 .firtst() (全部跟第一筆) # filter()是AND # filter(or_()) OR # --- 更新資料（Update）--- user.password = "newpassword123" dbsession.commit() # --- 刪除資料（Delete）--- dbsession.delete(user) dbsession.commit() ``` ---- csv ``` import pandas as pd df = pd.read_csv("file name.csv") # 遍歷每一筆資料 for index, row in df.iterrows(): # index 是列的編號（從 0 開始） # row 是那一整列的資料（一個字典） row['column']提取其中一個資料 # 一次把全部丟進 session 比較快 users[] users.append(user) session.add_all(users) ``` ---- ### JS 設定 ``` npm init -y npm install prisma @prisma/client npx prisma init ``` ---- 連線(prisma/schema.prisma) ``` datasource db { provider = "mysql" url = "mysql://root:你的密碼@localhost:3306/你的資料庫" } ``` 終端輸入 ``` npx prisma db pull // 抓表 ``` ---- 操作 ``` import { PrismaClient } from '@prisma/client'; const prisma = new PrismaClient(); async function main() { //增 const newUser = await prisma.user.create({ data: { name: '小明' }, }); //查(AND) const users = await prisma.user.findMany({ where: { name: '小明' } }); //查(OR) const users = await prisma.user.findMany({ where: { OR: [ { name: '小明' }, { email: 'abc@example.com' } ] } }); //改 const updatedUser = await prisma.user.update({ where: { id: newUser.id }, data: { name: '小華' }, }); //刪 const deletedUser = await prisma.user.delete({ where: { id: newUser.id }, }); } ``` ---- csv ``` npm install @prisma/client prisma csv-parse ``` ``` const records = []; fs.createReadStream('data.csv') .pipe(parse({ columns: true })) .on('data', (row) => { records.push(row); }) .on('end', async () => { // 批量插入 await prisma.user.createMany({ data: records }); ``` --- ## 練習 (一) 到https://codis.cwa.gov.tw/StationData 使用C0K400站的2024/4 月報表資料 要有觀測時間(day),氣溫(℃),最高氣溫,最低氣溫, 相對溼度(%),降水量(mm) ---- 找出 1. 當日有超過30度的日期 2. 10日~19日中最低溫度 3. 氣溫23.4,24.4,28.3的日期 4. 月平均濕度(SQL函數) 5. 計算總降雨量 ---- (二) 到https://data.gov.tw/dataset/6087 使用113年 找出 1. 所有名字有中山的 2. 私立國小的數量 3. 國中小的數量 4. 名字長度8的學校 --- ## 參考 * https://www.youtube.com/watch?v=gvRXjsrpCHw&t=1460s * https://www.runoob.com/sql/sql-join.html