###### tags: `My Sql`
# My Sql 安裝
window 安裝失敗就先下載 workbench
https://downloads.mysql.com/archives/workbench/

然後在全家統
https://dev.mysql.com/downloads/windows/installer/8.0.html

workbench 更新的話

是因为安全模式的保护，所以我们需要 在 workbench中设置一下：
如下：windows下是edit–>preferences–>SQL Editor
把右边的最后一行，“safe update”对用的那一行的选框按钮去掉，
然后点击ok就好了。

        query -> reconnect to server

# My Sql terminal login
on window

mysql -u root -p

看有多少databases

show databases;

# MySql 語法

MySql 基本邏輯語法

USE xxx ;
-- 像import

-- 這是comment

SELECT * 
-- 選全部

WHERE customer_id = 1 
-- Search for customer_id thatas equal to 1

ORDER BY customer_name
-- 排序! 按照 cunstomer name

**logical operator such AND , OR**

SELECT * 
FROM sql_store.customers
WHERE state ='TX' or last_name ='Boagey'

**NOT , IN for searcning**

SELECT * 
FROM sql_store.customers
WHERE state NOT IN ('VA','CA','GA')

-- WHERE state IN ('VA','CA','GA')

**More example of IN**

SELECT *
FROM sql_store.products
WHERE quantity_in_stock in (49,38,72)

**Between**

SELECT *
FROM sql_store.customers
WHERE birth_date  BETWEEN '1990-01-01'  AND '2000/01/01'

-- this is the same
-- WHERE birth_date > '1990-01-01' AND birth_date < '2000/01/01'

**LIKE + % , % % 用法**

SELECT *
FROM sql_store.Customers

-- 	We can use () to override prioraty
-- 		WHERE  birth_date > '1990-01-01' OR points >1000  AND state ='VA'

-- These two are the same
-- 		WHERE birth_date between '1990/01/01' AND '2000/01/01'
-- 		WHERE birth_date >= '1990/01/01' AND birth_date <='2000/01/01'

-- Any name start with b
-- 		WHERE last_name LIKE 'b%'

-- Any phone number end with 9
-- 		WHERE phone LIKE '%9'

-- Any name contains b
--  	WHERE last_name LIKE '%b%'

-- Search for address contain 'trail' and 'avenue' in anywhere 
-- 		WHERE (address LIKE '%trail%' or address LIKE '%avenue%') 

-- the " - " is use to specify the number , so the examle search for xxxxxy  (particular lengh which is 5 before y)
-- 		WHERE last_name LIKE '_____y'


where last_name LIKE '%field%'

IS 跟 NULL 用法

SELECT *
FROM sql_store.orders
-- WHERE phone IS NULL       找空的
-- WHERE phone IS NOT NULL   找不是空的

ORDER BY 跟 DESC 用法
1. 按照 first name descening order

SELECT *
FROM customers
ORDER BY first_name DESC

2. 按照 first name accending order

SELECT *
FROM customers
ORDER BY first_name 

**自訂 用法**
算出 quantitu * unite_price
然後 倒著排序!

SELECT *, quantity * unit_price AS total_price
FROM sql_store.order_items
WHERE order_id = 2
ORDER BY total_price DESC 

**REGEXP 基本用法**

^ 開始
& 結束

-- $ means must end with
--     WHERE last_name REgexp 'field$'  (anything end with field)

-- ^ measn must start with 
--     where last_name REGEXP '^mac' 

-- | or operator in REGEXP 
-- 		where last_name REGEXP 'field|mac' (anything contain fiekd or mac)

**REGEXP 複雜用法**

[]

1. 找 e 前面有 g 或 i 或 m 的

SELECT *
FROM sql_store.Customers
WHERE last_name REGEXP '[gim]e'

**Output:**

2. 找e前面有 a - z 的

SELECT *
FROM sql_store.Customers
-- 找 e 前面有 a-z的
WHERE last_name REGEXP '[a-z]e'

常用 REGEXP syntax


Exercis
Solution

SELECT *
FROM sql_store.Customers

-- WHERE first_name REGEXP 'ELKA|AMBUR'

-- WHERE last_name REGEXP 'EY$|ON$'

-- WHERE last_name REGEXP '^EY|SE'

-- WHERE last_name REGEXP 'B[r|u]'
-- WHERE last_name REGEXP  'br|bu' (same)

LIMIT 兩種用法
1. 只看6個

SELECT *
FROM sql_store.customers
LIMIT 6 

2. 跳過 , 然後 選多少要看的

SELECT *
FROM sql_store.customers
LIMIT 4 , 2     從第5個開始看 看2個

# pymysql 用法

pymysql.connect() 要傳入密碼
default port 在mysql workbeach 上看的到

import pymysql
db = pymysql.connect("localhost","root","2313418546Oscar" )
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print ("Database version : %s " % data)
db.close()

執行mysql 語法
Ex. "SELECT * FROM sql_store.customers"

import pymysql
db = pymysql.connect("localhost","root","2313418546Oscar" )
cur = db.cursor()
cur.execute("SELECT * FROM sql_store.customers")
print(cur.description)
print()
for row in cur:
    print(row)
cur.close()
db.close()

https://pymysql.readthedocs.io/en/latest/user/examples.html
https://github.com/PyMySQL/PyMySQL/blob/master/example.py
https://shockerli.net/post/python3-pymysql/