# SQL
先條件後排序

用結果去排序

## sql index差異
https://oldmo860617.medium.com/%E4%BB%A5-postgressql-%E7%82%BA%E4%BE%8B%E4%BA%86%E8%A7%A3%E8%B3%87%E6%96%99%E5%BA%AB%E7%9A%84-query-plans-abd8b5f54c66




## postgresql分析工具 要錢
pganalyze

## store routines

## query plain
https://oldmo860617.medium.com/%E4%BB%A5-postgressql-%E7%82%BA%E4%BE%8B%E4%BA%86%E8%A7%A3%E8%B3%87%E6%96%99%E5%BA%AB%E7%9A%84-query-plans-abd8b5f54c66

## explain
https://pevenc12.github.io/posts/postgresql-explain-explained-scan/

https://pevenc12.github.io/posts/postgresql-explain-explained-join/


## 排序
https://airbyte.com/blog/postgresql-query-plans-for-sorting-data


## where結果很大 且要排序
當 WHERE 條件的結果集很大，而 ORDER BY 的列無法直接用索引排序時，PostgreSQL 可能執行全表掃描或排序，導致性能問題，尤其是「Rows Removed by Filter」很高。以下是解決方法


## order by + limit
小心選錯index

https://pganalyze.com/blog/5mins-postgres-planner-order-by-limit

## 縮小排除法原則

## join index

## 排序

## like

**postgresql**

前綴

CREATE INDEX idx_name_booking_like
ON postgres_air.booking (email varchar_pattern_ops);

varchar_pattern_ops

## 排序
https://www.cnblogs.com/thomasbc/p/15572368.html

## 刪除大量會有問題

https://www.cnblogs.com/wjq310/p/14216684.html

## 事務id 大寫寫入
https://blog.sentry.io/transaction-id-wraparound-in-postgres/

## 注意

## 索引整理


## @>
https://stackoverflow.com/questions/36985926/what-does-the-operator-in-postgres-do

## in 排序

in, where加上order by field

## 只查詢月份
EXTRACT(MONTH FROM birthday)

## lock查詢

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM 
    pg_catalog.pg_locks blocked_locks
JOIN 
    pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN 
    pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN 
    pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE 
    blocked_locks.granted = false;

## 正規
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-TABLE

## view
除了一班的
可以選擇建立條件


## window

WINDOW function 也可以拿來做移動平均值或累計值的計算

## 中位數

## 游標

## 行鎖

## union
去重跟沒去重複

## sql 阿里雲
https://www.alibabacloud.com/help/zh/log-service/latest/unnest-clause

## index(重點)
https://ithelp.ithome.com.tw/articles/10230211

覆蓋索引
https://ithelp.ithome.com.tw/articles/10230245
複合索引
https://ithelp.ithome.com.tw/articles/10230278

ICP
https://ithelp.ithome.com.tw/articles/10230362
## coalesce
類似is_null的

## Explain
https://mp.weixin.qq.com/s?__biz=MzU3NDc5NzMzNw==&mid=2247484530&idx=1&sn=4048e4b29633b1793fbde12db84f840c&chksm=fd2da704ca5a2e129844d035f14ddb615f0861a26346eb501afc1d9799042afbdc2298b41810&scene=21#wechat_redirect

## 排序要小心
orderBy 如果同一區間會隨機
https://www.jianshu.com/p/9d5becb03577

## 排序優化
https://cloud.tencent.com/developer/article/1502417?from=article.detail.1505335
## 優化
https://cloud.tencent.com/developer/article/1505335?from=article.detail.1868851

## exists vs in
外大內小 用in
外小內大 用 exists
一樣用 join
https://cloud.tencent.com/developer/article/1868851


## 臨時表
https://segmentfault.com/q/1010000013095609

## 子查詢優化
https://blog.csdn.net/dxyzhbb/article/details/106803069

## sql count
https://bbs.huaweicloud.com/blogs/323789

## GROUP_CONCAT
https://ithelp.ithome.com.tw/articles/10132553

## function
https://zhuanlan.zhihu.com/p/101891364

## data function
https://zhuanlan.zhihu.com/p/105375801

## where 1=1
https://zhuanlan.zhihu.com/p/282817046

## where or on

## 防 sql 注入

1. 过滤特殊字符
2. 过滤数据库关键字
3. 验证数据类型及格式
4. 使用预编译模式，绑定变量

## 約束

### check
https://stackoverflow.com/questions/38223158/laravel-migration-adding-check-constraints-in-table
如果現有資料違反會有error


## 運算子

## day name
DAYNAME 可以顯示星期幾之類的很多可以用
要轉中文要 set @@ 改變語言那樣
## 涵式

### FIND_IN_SET 找array自動對應key
https://blog.johnsonlu.org/mysql-find-in-set/

## mysql如何查询每个用户消费金额最大的记录
https://learnku.com/mysql/t/63401

https://learnku.com/laravel/t/59320

## 最新的唯一数据
https://learnku.com/mysql/t/68543t

### mid
可以用month或字串去擷取

### 按照姓氏 排序 left + field

左邊第一個字然後按照filed排
按照順序由1開始
如果不再後面的就是0

排序按照大到小
## 自連結
https://www.bilibili.com/video/BV1ht411J7ik?p=7&spm_id_from=pageDriver
表
sql

找某某同一個甚麼
一開始有先限定了
記得要排除原本的

例子二
找後盾人 同一年出生的
一樣一開始有先限定誰

## min max

查詢最好用子查詢
如果直接select min(a) 會只有一筆

## FIND_IN_SET
https://blog.johnsonlu.org/mysql-find-in-set/

## if
if 可以讓else不更新
等於欄位名稱就好

## Distinct
https://ithelp.ithome.com.tw/articles/10259378

即使加了括號，還是會作用到兩個欄位

SELECT (DISTINCT supplier_name), `order_status` FROM orders;
那有沒有其他方式可以針對一個欄位去重，但同時還是可以單純顯示其他欄位？這時候可以用 GROUP BY，或說一開始的情境用GROUP BY也可以！

SELECT `supplier_name`, `order_status` FROM orders
WHERE `order_status` = 2
GROUP BY `supplier_name`;

## 如何讓 query 區分大小寫

解法：Binary
在要區分大小寫的條件欄位前加上 BINARY 就可以解決

`SELECT * FROM records WHERE BINARY code = 'F23B';`
執行結果如下，因為沒有完全匹配的紀錄，所以撈出 0 筆資料

mysql> select * from users where binary code = 'F23B';
Empty set (0.00 sec)

在 Laravel 中就會是這樣下：

$code = "F23B";
$records = Record::whereRaw('BINARY `code` = ?', $code)->get();

## 子查詢

每一個子查詢都是一個Select指令，必須用小括弧包起來，能夠針對不同資料表進行查詢。
如果SQL查詢指令內有子查詢，首先處理的是子查詢條件，然後再依子查詢取得的條件值來處理主查詢，然後就去得最後的查詢結果。

這邊注意 是先跑主表 select .... 然後where的時候才先執行括號內

1. 子查詢要在括號()中。
1. 通常子查詢SELECT只會取得單一欄位的值，以便主查詢的欄位進行比較運算。
1. 如需排序，子查詢不能使用ORDER BY，只能使用GROUP BY 子句。
1. 如果子查詢可以取得多筆資料，在主查詢需使用IN邏輯運算子。

### 多列多行

多行
### 表子查詢

## function
https://learnku.com/articles/68574

###### tags: `SQL`