# SQL學習筆記
* [Greeksforgeeks教程](https://www.geeksforgeeks.org/30-days-of-sql-from-basic-to-advanced-level/)
* 環境:mySQL 8.0,windows
---
[Toc]
---
基礎語法就略過
## debug
### Could not acquire management access for administration
#### 假若mySQL連登入密碼都無法輸入，代表server的服務沒有啟動，需要手動執行
#### 我先前架server在MAMP PRO上，他預設的伺服器架構是apache+mySQL+php，似乎是版本上沒有完全對應到，所以跳出錯誤，通常是程式重開可以解決衝突的問題，否則網路上有提到，需要進BIOS去開啟一個服務
#### 這裡也可以看到兩個服務是各自獨立的，到時如果發布在MAMP上，便不會有這個問題
## UPDATE
#### 建database
#### 這邊要做更新data
```
update s_list
set n = "JENNA" , age = 23
where id = 55;
```
## FOREIGN KEY
### REFERENCES
#### 外來鍵限制表格內的某一個值指到(references)其他table的*primary key，注意一下外來鍵一定只能對應到主鍵
```
create table p_list(
p_id int(3) primary key,
n varchar(20),
s_id int(3),
price int(5),
foreign key (s_id) references s_list(id)
);
```
## 運算子
### Arithmetic Operators
#### 做簡單的運算處理
```
select id,n,age+10 as "age+10" from s_list;
```
#### 新增一項salary，計算薪資除以年紀
```
update s_list
set salary = age*1000;
```
#### 這裡嘗試以運算子update資料的數值，報錯顯示我在安全模式下無法以其他data(KEY column)更新資料，如果進去preferences更動設定可以解決這個問題，但就先不做更動，手動更新資料
```
select id,n,age,salary,salary/age as "salary/age" from s_list;
```
### order
#### 讓他排序以薪資比來做順序排列
```
select id,n,age,salary,salary/age as "salary/age" from s_list
order by salary/age desc;
```
### Logical Operators
#### 邏輯運算，最基本的就是AND,OR,NOT
```
select * from s_list
where id>10 and age<50 or salary> 50000;
```
#### 比較特別的用法還有BETWEEN
```
select * from s_list
where salary between 10000 and 50000;
```
#### 可以注意到他的範圍是包括極值
## 萬用字元(Wildcard operators)
#### 這是一個很酷的概念，囊括你查詢的字串可以用以代替字元的用法
```
select * from s_list
where n like "%A%";
```
#### 笑死我這才發現，我取的每個名字都有A在裡頭
```
select * from s_list
where n like "%AN%";
```
## 函數
### count計算資料數總和，total總共五筆資料
```
select count(*) from s_list;
```
### avg
```
select avg(salary) from s_list;
```
### sum
```
select sum(salary) from s_list;
```
### max
```
select max(salary),id,n,age from s_list;
```
### TOP
#### 在mySQL當中的語法以limit來作替代，本身select top是無法使用的，但得到的結果相同，取得資料庫最上方的幾筆資料
```
select * from s_list limit 3;
```
## MINUS
#### 會用到兩個select，用途就是將第一個查詢結果減去第二個
#### mySQL不能用，以left join來模擬
```
create table x_list(
id int(3) primary key,
temp int(3),
stock_id char(10)
);
insert into x_list values (1,2,"AAPL"),(2,3,"NVDIA");
```
```
select id from s_list
left join x_list using (id)
where x_list.id is null;
```
## UNION
#### 用法就像OR(聯集)，將兩個以上的select串聯在一起查詢
```
select n from s_list where id<20
union
select id from s_list;
```
## JOIN
#### 建立另一個表格參照第一個s_list，設定foreign key連接到id，可以當作是交易明細或是任何客戶及商品的對應database
### LEFT JOIN
#### JOIN也是相當於OR的用法，與UNION的差異是，UNION是以垂直整合，JOIN則將欄位整合成一個table
```
select p_list.p_id,p_list.n as "stock",s_list.id,s_list.n,p_list.trading*p_list.price as "i_total"
from p_list
left join s_list
on p_list.s_id = s_list.id;
```
#### 將兩個table整理在一起
```
select p_list.p_id,s_list.n,s_list.age,s_list.salary,p_list.n as "stock",p_list.price,p_list.trading,p_list.trading*p_list.price as "i_total"
from p_list
left join s_list
on p_list.s_id = s_list.id;
```
### RIGHT JOIN
### INNER JOIN
#### 查詢的資料都要符合條件才會顯示，倘若有NULL或是其他空集合的資料，就不會顯示出來
```
select p_list.p_id,s_list.n,p_list.price
from p_list
inner join s_list
on p_list.s_id = s_list.id
order by p_list.p_id;
```
#### 這裡可以看到**第八筆**因為s_id為null，在inner join的參照又是對到s_id，他就被剃除掉，但第九筆的s_id因為還留著，即使其他資料不完整但也被顯示出來
## HAVING
#### 在函數的條件設定下，不能用WHERE來做限制，此時使用HAVING代替WHERE的查詢功能
用前兩個表格參照查詢總投資的金額
```
select s_list.n,sum(p_list.trading*p_list.price) as "stoock"
from p_list
left join s_list
on p_list.s_id = s_list.id
group by s_list.n
having sum(p_list.trading*p_list.price)>70000;
```
顯示投資金額大於70000的客戶
## VIEW
#### VIEW的概念就是虛擬的database，他也是由row,column組成，但是select參照其他資料得到的
### VIEW的特性
#### 適用於安全性的考量，可以把資料的原始屬性隱藏起來，甚至操控不同權限的使用者，可以看到特定的內容，也可以直接簡化資料，只顯示有需要使用的資料
#### 由於view的資料是唯讀的，也就代表外部使用者是無法對view做更動
#### 當資料需要作暫時的更動，僅需操作view，而不需要直接動用程式去更新database
### CREATE VIEW
```
create view view_1(n,stock,trading) as
select s_list.n,p_list.trading*p_list.price,p_list.trading
from p_list
join s_list
on p_list.s_id = s_list.id;
```
#### 針對這個交易報表再做分析，找到交易金額特別高的幾個客戶
```
select n,sum(stock),sum(trading)
from view_1
group by n
having sum(stock)>30000;
```
### DROP VIEW
#### 用法都跟一般的table相同
```
drop view view_1;
```
### CREATE OR REPLACE VIEW
#### 用以更新view，假如view已存在，則可以當作alter使用
```
create or replace view view_1(n,stock,trading,age,salary) as
select s_list.n,p_list.trading*p_list.price,p_list.trading,s_list.age,s_list.salary
from p_list
join s_list
on p_list.s_id = s_list.id;
```
## *除法(DIVISION)
## GRANT
### 授予db使用權限
#### 建立一個新帳號後，你要授與資料庫使用權限給這位使用者，這帳號才能開始連線進去資料庫操作
```
GRANT type_of_permission ON database_name.table_name TO 'username'@'hostname';
```
#### hostname 表示允許這帳號能從什麼地方連線到資料庫。localhost 表示只允許從本地端登入
```
GRANT ALL PRIVILEGES ON *.* TO 'andy'@'%';
```
#### % 是萬用字元，表示允許andy從任何address登入
#### 權限型態
* ALL PRIVILEGES
* CRETAE
* DROP
* INSERT
* DELETE
* SELECT(*)
* UPDATE(可以更動資料)
* GRANT OPTION(給予他人權限)
### FLUSH PRIVILEGES
#### 讓權限生效
```
FLUSH PRIVILEGES;
```
## CASE
#### SQL允許直接使用直覺的if,then,else，但前面要寫case做抬頭
```
select *,
case when trading > 100 then price*10
when trading > 1000 then price
else price*20
end "saved weight"
from p_list;
```
#### 這裡要注意select跟case之間一定要有逗號做分隔
# mysqli database connect
## phpmyadmin create db
[link text][reference]pw:5-0*j1}[([!PI%MB
## create table
## php連接資料庫
```
<?php
// 建立MySQL的資料庫連接
$link = @mysqli_connect(
'localhost', // MySQL主機名稱
'root', // 使用者名稱
'1234', // 密碼
'test'); // 預設使用的資料庫名稱
if ( !$link ) {
echo "MySQL資料庫連接錯誤!<br/>";
exit();
}
else {
echo "MySQL資料庫test連接成功!<br/>";
}
mysqli_close($link); // 關閉資料庫連接
?
```
#### 基本上基本的語法就是這樣，設php變數post get進db，步驟很簡單，但第一次寫還有很多bug要找，網路上對javascript submit表單的資料不多，可能現在的資料庫都是綁定網頁架構平台，包括phpmyadmin，事實上在架設的過程都非常簡單人性化，甚至sql injection的問題也會直接做防範，以往需要對輸入的資料做一系列的篩檢，或是限制，如今一個指令就可以幫你過濾可能會有問題的輸入格式
## Google search console
Sign in with Wallet
Connect another wallet