---
# System prepended metadata

title: My Sql 安裝
tags: [My Sql]

---

###### 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:**
![](https://i.imgur.com/D7CHaaO.png)

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

常用 REGEXP  syntax

![](https://i.imgur.com/L8brA64.png)


Exercis 
![](https://i.imgur.com/Qxkl1c3.png)
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/