# 0307 資料庫-1 共筆
# Online Tools
SQL: https://www.programiz.com/sql/online-compiler/
Regex: https://regex101.com/
what is my ip: https://www.whatismyip.com.tw/tw/
# SQL
## 安裝 Library
```batch
pip install pymysql
```
## 引入標頭檔
```python
import pymysql
```
## 連接資料庫
```python=
db_settings = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "",
"db": "test",
"charset": "utf8"
}
conn = pymysql.connect(**db_settings)
cursor = conn.cursor()
```
## 建立新資料庫
### 格式
```SQL
CREATE DATABASE dbName;
```
### 範例
```python=
command = "CREATE DATABASE test;"
cursor.execute(command)
```
## 刪除資料庫
### 格式
```SQL
DROP DATABASE dbName;
```
### 範例
```python=
command = "DROP DATABASE test;"
cursor.execute(command)
```
## 建立新表格
### 格式
```SQL
CREATE TABLE tableName(col1 type1, col2 type2, ...);
```
### 範例(無鍵值)
```python=
command = """CREATE TABLE Persons (
PersonID int,
Name varchar(255),
Birth date,
Sex char(1)
);"""
cursor.execute(command)
```
### 範例(有鍵值)
```python=
command = """CREATE TABLE Persons (
PersonID int PRIMARY KEY,
Name varchar(255),
Birth date,
Sex char(1)
);"""
cursor.execute(command)
```
## 刪除表格
### 格式
```SQL
DROP TABLE tableName
```
### 範例
```python=
command = "DROP TABLE Persons"
cursor.execute(command)
```
## 新增資料
### 格式
```SQL
INSERT INTO TABLE(col1, col2, ...) VALUES(val1, val2, val3);
```
### 範例(新增單筆資料)
```python=
command = """INSERT INTO
Persons(PersonID, Name, Birth, Sex)
VALUES(0, 'A', '2000-01-01', 'F');
"""
cursor.execute(command)
conn.commit()
command = """INSERT INTO
Persons(PersonID, Name, Birth, Sex)
VALUES(1, 'B', '2000-02-02', 'M');
"""
cursor.execute(command)
conn.commit()
```
### 範例(新增多筆資料)
```python=
command = """INSERT INTO
Persons(PersonID, Name, Birth, Sex)
VALUES(0, 'A', '2000-01-01', 'F'),
(1, 'B', '2000-02-02', 'M');
"""
cursor.execute(command)
conn.commit()
```
## 查詢資料
### 格式
```SQL
SELECT column FROM tableName where cond
```
### 範例(取出所有資料)
```python=
command = "SELECT * FROM Persons WHERE TRUE;"
cursor.execute(command)
for i in cursor.fetchall(): print(i)
```
### 範例(取出特定資料)
```python=
command = "SELECT * FROM Persons WHERE NAME='A';"
cursor.execute(command)
for i in cursor.fetchall(): print(i)
```
## 刪除資料
### 格式
```SQL
DELETE FROM tableName WHERE cond
```
### 範例(刪除特定資料)
```python=
command = "DELETE FROM Persons WHERE NAME='A'"
cursor.execute(command)
conn.commit()
```
### 範例(刪除所有資料)
```python=
command = "DELETE FROM Persons WHERE TRUE;"
cursor.execute(command)
conn.commit()
```
## 修改資料
### 格式
```SQL
UPDATE tableName SET col1=val1, col2=val2, ... WHERE cond
```
### 範例
```python=
command = "UPDATE Persons SET NAME='Z' WHERE SEX='F'"
cursor.execute(command)
conn.commit()
```
# Regex
## 單獨字元
==.==: 任意文字、數字及符號，但不包括換行
==\d==: 數字
==\w==: 文字、數字或底線
==\s==: 空白字元，包含空白、Tab、換行字元
==()==: 群組
==[]==: 集合
=={}==: 量詞，表示次數
### 範例
==\d==
==\w==
==\s==
## 列舉字元
在中括號 ==[]== 中放入單獨字元的比對，表列舉一定範圍中的字元
==^==: 反向列舉
==-==: 連續列舉
### 範例
==^==
==-==
## 次數符號
==*==: 重複0次以上
==+==: 重複1次以上
==?==: 重複0或1次
==.==: 除了\n之外的任意單獨字元
=={n}==: 重複n次
=={n,}==: 重複n次以上
=={n,m}==: 重複n到m次
### 範例
==*==
==+==
==?==
=={n}==
=={n,}==
## 定位符號
==^==: 字串開頭
==$==: 字串結尾
==|==: OR
### 範例
==^==
==$==
==|==
# Use Regex in Python
## 引入標頭檔
```python
import re
```
### 尋找所有匹配字串
```python
re.findall(regex語法, 目標字串)
```
```python=
str = """Those are my Email: abcd1234@gmail.com, dcba4321@gmail.com, B11915000@gapps.ntust.edu.tw"""
target = """[\w]+@[\w.]+"""
result = re.findall(target, str)
print(result)
```
### 取代所有匹配字串
```python
re.sub(regex語法, 替換目標, 目標字串)
```
```python=
str = """Those are my Email: abcd1234@gmail.com, dcba4321@gmail.com, B11915000@gapps.ntust.edu.tw"""
target = """[\w]+@[\w.]+"""
result = re.sub(target, "SomeEmail", str)
print(result)
```
Sign in with Wallet
Connect another wallet