SQL 常用函式、sp和各種使用方法

LEFT(對象,字數)
限制從左起取出指定數量的字串

LOCATE(搜尋字串,被搜尋字串)
搜尋字串從第幾個文字開始

LPAD(原字串  , 調整後字數 , 要加入的文字)
在左側增加文字

LIMIT X , Y
從開頭算第X列開始取Y列 2 , 4就是從第三個開始取四筆資料

LTRIM(字串)
刪除左邊的空白

POSITION(要搜尋的字串 IN 被搜尋對象)
搜尋字串的位置

REPEAT(字串 , 重複次數)
重複字串X次

SUBSTR(字串  , 開始位置  , 取出字數)
SUBSTRING(字串  , 開始位置  , 取出字數)
取出字串

ADD_MONTHS(基準月  , 加入幾個月) //可以帶-數
更改月份

CURRENT_DATE
取得當前日期

CURRENT_TIME
取得當前時間

CURRENT_TIMESTAMP
取得當前時間日期

DATE(日期)
從指定的日期中取出部分想要的資料

DATE_ADD(日期 ,INTERVAL 加上的時間 單位)
增加時間

DATE_FORMAT(日期] , 格式)
指定日期格式

DATE_PART(日期格式 , 日期)
要取出的日期格式

DATE_SUB(日期 , INTERVAL 減去值 單位)
減去日期

DATE_TRUNC(捨去的元素  , 日期)
捨去時間，從歸第一天
如:2020-02-15 04:55:56
DATE_TRUNC('month' , SomeDate)
變成 2020-02-01 00:00:00

DATEDIFF(格式 , 日期1 , 日期2)
比較日期差值，由格式決定比什麼

DATENAME(格式 , 日期)
取得格式日期字串，如設定week，則會得到week格式的資料

DATEPART(格式 , 日期)
取得格式日期數值，同上不過取得的是數值

DAY(日期)
取得該日期的日元素

DAYNAME(日期)
取得星期字串

DAYOFWEEK(日期)
取得今天星期幾

DAYOFYEAR(日期)
取得今天是今年第幾天

EXTRACT(格式 FROM 日期)
ex:SELECT 日期 , EXTRACT(格式 FROM 日期) FROM Table

GETDATE()
取得時間

GETUCTDATE()
取得標準時間

LAST_DAY(日期)
取得該月最後一天

MONTHNAME(日期)
取得該月名稱(字串)

MONTHS_BETWEEN(開始日期 , 結束日期)
得到開始日期到結束的月份差 以小數點表示

NEXT_DAY(日期 , 星期幾)
得到最近的下一個星期X

NOW()
取得現在時間

PERIOD_ADD(期間 , 月份數)
YYYYMM格式加上X月後回傳

PERIOD_DIFF(期間 , 月份數)
YYYYMM格式減掉X月後回傳

SEOND(日期)
取得秒

SYSDATE()
取得現在時間

CAST(任意形式 AS 轉換的型別)
轉換型別

COALESCE(Data , Data2 , ....)
傳回第一個不是NULL的值，Data的資料形態要一致

CONVERT(轉萬的型別 , 資料 , 若轉換成日期時的格式)
轉換型態

DEODED(原本 , 轉換值 , 原本2 , 轉換值2 , ..... , 預設值)
轉換資料

ISNULL(資料 , 轉換值)
是NULL的話轉換

NULLIF(Value1 , Value2)
相等的話回傳NULL，不相等回傳Value1

RAND()
取亂數值

ROUND(值 , 有效位數)
四捨五入後取得有效位數內的值

ROLLUP
使用於GROUP BY 之後 用於統計圖表，會在表格最後一列有一個彙總列。

SELECT (屬性)
    DISTINCT：消除重複項
    SELECT *：選取全部屬性
    AVG：平均
    MIN：最小
    MAX：最大
    SUM：總和
    COUNT：總共有幾個
FROM (資料表)
    a_data , b_data：兩資料表的笛卡爾矩陣相乘
    笛卡爾矩陣 > 兩資料表筆數相乘為新資料表總筆數
      
WHERE (條件式)
    AND OR NOT：且 或 否
    BETWEEN a AND b：在ab之間
    %：字串抓取
    like %Main% 抓取 (任意字元)Main(任意字元)
    

AS：重新命名
\：逃脫字元
ORDER BY (資料表名稱) asc,
    ORDER BY：排列 asc表示從小排到大，desc表示從大排到小，預設為asc

集合操作
SELECT x FROM da
UNION (INTERSECT) (EXCEPT) 聯集 交集 否
SELECT x FROM db
必須要有共通的值才可以互相比較，不然沒有意義且會自動消除重複的資料

左圖為拿兩個不同的屬性進行聯集，分別為時間跟ID，可以進行聯集但沒有意義

GROUP BY 把數值整理成不同呈現方法
    如 COUNT
    SELECT (COUNT (fcc.DateKey)) AS DATEKEY
    FROM FactCallCenter as fcc

為 總數 120個 (無刪除重複項)

而
    SELECT (COUNT (fcc.DateKey)) AS DATEKEY
    FROM FactCallCenter as fcc
    GROUP BY DateKey
則是以 各個日期為主(因GROUP BY 設定以DateKey為主)，同一日期的則數為一個數
    SELECT (COUNT (DISTINCT fcc.DateKey)) AS DATEKEY
    FROM FactCallCenter as fcc
因此若把重複項刪除，並且COUNT則只有30項(DateKey的不重複項總數)


    HAVING 類似WHERE 但是專用於GROUP的WHERE
  

    SELECT (COUNT (fcc.DateKey)) AS DATEKEY
    FROM FactCallCenter as fcc
    GROUP BY DateKey
    HAVING DateKey > 20140503
HAVING 中也可以使用SELECT中的保留字，但是使用保留字之後，型態會改變。
如COUNT (屬性) 會變成共有幾個
也就是說 20140503日期的資料，共有4筆，出來的結果會是4
因此無法寫成
HAVING DateKey > 20140503 會顯示0或不顯示

NULL：任何東西+NULL  回傳值為NULL
而NULL 會被COUNT無視

SELECT COUNT(fcc.DateKey + NULL) AS DATEKEY
FROM FactCallCenter as fcc
因此這條式子的COUNT，會顯示0，而不是120
巢狀查詢：
IN：雙方都含有
NOT IN：一方不含有
SOME：部分達成
ALL：完全達成,

SELECT (fcc.DateKey) AS DATEKEY
FROM FactCallCenter as fcc
WHERE DateKey > SOME(
SELECT fccS.DateKey AS DATEKEY
FROM FactCallCenter AS fccS
WHERE DateKey > 20140503
)

大約等於

SELECT (fcc.DateKey) AS DATEKEY
FROM FactCallCenter as fcc
GROUP BY DateKey
HAVING DateKey > 20140503

雖然20140504不知道為何也被巢狀刪除..

巢狀查詢：
EXISTS：值存在
NOT EXISTS：值不存在
UNIQUE：只有一個值
NOT UNIQUE：擁有一個以上的值

巢狀型態也可以使用在FROM。


SELECT COUNT (fcc.DateKey) AS DATEKEY
FROM (SELECT fccS.DateKey AS DATEKEY
FROM FactCallCenter AS fccS
WHERE DateKey > 20140503) AS fcc

等同

SELECT COUNT (fcc.DateKey) AS DATEKEY
FROM FactCallCenter as fcc
WHERE DateKey > 20140503

WITH AS ()

WITH DATE20140503(DATE_2014) AS(
SELECT (DateKey)
FROM FactCallCenter
WHERE Datekey = 20140503
)
SELECT fcc.DateKey
FROM DATE20140503 , FactCallCenter AS fcc
WHERE fcc.DateKey > DATE_2014

--SELECT C1 INTO TableName FROM SomeTable WHERE (條件)

SELECT INTO TableName 
SELECT * FROM SomeTable

此時可以把整個查詢到的SomeTable內容加入進TableName表中

SELECT INTO TableName(C1 , C2 , C3)
SELECT S1,S2,S3 FROM SomeTable

這邊是指定TableName 中在哪幾個欄位中插入哪些值
需要注意的就是資料型態問題，型態不對插不進去

視圖VIEW

--CREATE VIEW CView(C1 , C2 , C3)
SELECT A1,A2,A3
FROM A

建立一個視圖
可以供使用者使用，但是不會影響到原A Table的內容
使用包含SELECT INSERT等操作

觸發器(TRIGGER)

--CREATE TRIGGER TriggerName (AFTER , BEFORE) , (INSERT , UPDATE , DELETE) ON TriggerTableName
BEIGN
(要執行的SQL陳述句)
INSERT INTO SomeTable VALUES ( SOMETHING )

觸發器的頻繁使用會使的資料庫變得複雜，並且觸發器本身也是會產生負荷，因此盡可能避免使用過多的觸發器。

索引(INDEX)

--CREATE INDEX Index1
ON TableName(C1)
創造一個索引以TableName的C1欄位為主

索引如果重複會產生錯誤
--CREATE UNIQUE INDEX CIndex ON TableA (C1) 創造出不會重複的索引
以TableA 的 C1 欄位建立一個CIndex的索引進行搜尋
原本的搜尋方法為從頭找到尾，就搜尋方法來講是很浪費時間的。
而索引的方法是把資料分成兩堆，以二元樹的方式進行搜尋。


CREATE INDEX Index1 ON TableName (Function (C1) )
函式索引

CREATE INDEX  Index ON TableName USING || BTREE || RTREE || BASH (C1)
預定使用哪個演算法

DROP INDEX Index
刪除索引

SQL 函式使用

--CREATE FUNCTION FunctionName (Oracle)
(C1 IN TYPE , C2 IN TYPE)
BEGIN
    RETURN C1 * C2/100
END;
功能為 回傳 C1 * C2/100
呼叫方法為
SELECT C1 ,FunctiobName(SomeNumber1 , SomeNumber2)

--CREATE FUNCTION  FunctionName(@C1 int , @C2 int) (SQLSERVER)
RETURN INT AS
BEGIN
    RETURN @C1 * @C2 /100
END

--CREATE FUNCTIOB FunctionName (C1 TYPE , C2 TYPE) (DB2)
RETURN TYPE
LANGUAGE SQL
RETURN C1 * C2 /100

--CREATE FUNCTION FunctionName (TYPE ,TYPE) (PostgreSQL)
RETURN TYPE AS ' SELECT $1 * $2 /100 
LANGUAGE sql

DROP FUCTION FctionName

SQL Package和StoredProcessdure


CREATE PACKAGE PackageName IS
FUNCTION ReturnStr RETURN VARCHAR2;
END;
宣告用來存取的Package
用來把預存程序打包

CREATE PACKAGE BODY PackageName IS
FUNCTION ReturnStr RETUNE VARCHAR2 IS 
BEGIN RETURN 'HELLO'
END ReturnStr 
    END PackageName 
定義PACKAGE BODY

DROP PACKAGE PackageName
刪除Package

CREATE PROCEEDURE ProceedureName( (Oracle)
    C1 IN TYPE 
    C2 IN TYPE
)BEGIN
    陳述式
END;
建立存取程序

CREATE PROCEDURE sp_procedure (SQL SERVER)
@C1 TYPE,
@C2 TYPE,
@C3 TYPE ...
AS
陳述式

或者使檔案加密的方法
CREATE PROCEDURE sp_procedure WITH RECOMPILE AS
陳述式

EXECUTE sp_Name = 使用該存取程序

CREATE PROCEDURE getDate()
LANGUAGE SQL
陳述式

DROP PROCEDURE sp_procedure
刪除存取程序

SELECT S.NAME '結構描述', O.NAME '資料表名稱', P.ROWS '列總數'
FROM SYS.OBJECTS O -- OBJECTS 物件數量
INNER JOIN
SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID --物件型態 INNER JOIN
SYS.PARTITIONS P ON O.OBJECT_ID = P.OBJECT_ID --物件列數判斷 WHERE (O.TYPE = 'U') AND (P.INDEX_ID IN (0,1)) ORDER BY S.NAME, O.NAME ASC;
查詢所有資料表筆數


SELECT * FROM sys.sysobjects -- object中 各種重要資訊都在此處 紀錄數量 INNER JOIN syscomments ON sys.sysobjects.id = sys.syscomments.id --對應id WHERE sys.syscomments.text LIKE '%關鍵字%' --想要查詢的關鍵字
從DB中搜索特定關鍵字是否存在

SELECT * FROM sys.sysobjects 
WHERE name Like '%關鍵字%' --用目標名稱查詢
AND type = 'P' --查詢類別

sys objects系列的type類型:
AF = 彙總函式 (CLR)
C = CHECK 條件約束
D = 預設值或 DEFAULT 條件約束
F = FOREIGN KEY 條件約束
FN = 純量函數
FS = 組件 (CLR) 純量函數
FT = 組件 (CLR) 資料表值函式 IF = 內嵌資料表函數
IT - 內部資料表
K = PRIMARY KEY 或 UNIQUE 條件約束
L = 記錄
P = 預存程序 --sp
PC = 組件 (CLR) 預存程序
R = 規則
RF = 複寫篩選預存程序
S = 系統資料表 --sys.系列
SN = 同義字
SQ = 服務佇列
TA = 組件 (CLR) DML 觸發程序
TF = 資料表函數
TR = SQL DML 觸發程序
TT = 資料表類型
U = 使用者資料表 -- Table
V = 檢視
X = 擴充預存程序

參考
https://dotblogs.com.tw/BerryNote/2016/09/30/110930

以下懶人包系列 ----------------------------------------------------------------------------------------

DECLARE @Date DATETIME = GETDATE();
 
SELECT @Date AS '日前時間'
,DATEADD(DD,-1,@Date) AS '昨天'
,DATEADD(DD,1,@Date) AS '明天'
/*月相關*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到天)'
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到datetime的小數位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最後一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))  AS '下月最后一天'
/*周相關*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(星期日)'
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上個星期第一天(星期日)'
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下個星期第一天(星期日)'
,DATENAME(WEEKDAY,@Date) AS '本日是星期幾'
,DATEPART(WEEKDAY,@Date) AS '本日是星期幾'
/*年相關*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季相關*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
 
參考出處：http://bbs.csdn.net/topics/390304212?page=1
參考
https://louis176127.pixnet.net/blog/post/138139574

留言

張貼留言

這個網誌中的熱門文章

無法載入檔案或組件 'System.IO.Compression' 或其相依性的其中之一。

圖片
曾經在拿取檔案的時候發生過這個問題 我不清楚究竟是版本的問題還是(應該不是版本的問題，因為我從我的夥伴手中拿它可以跑的程式，修改Config後仍然有問題) 主要的問題可能是因為package的問題，時常因為package跟參考以及nuget和WebConfig有不同而出現版本問題 遇到上述問題的話可能都要檢查以上三個檔案 建議是直接複製查詢 然後把「你認為不該存在的」參考給移除。 我自己本人是只留下參考中的檔案，是會把package和WebConfig的依賴給註解後再使用nuget重新安裝。 題外話抱怨 package.Config貌似就是參考的引入，但是不知道為啥連那邊都會跟參考互相衝突，微軟真的不打算修一下逆? 補充說明: 比如說遇到這個錯誤 上面有寫明說 Newtonsoft.Json culture = neutral publicKeyToken = 30ad4fe6b2a6aeed version = 6.0.0.0 就可以在web.config中寫這段加入  <dependentAssembly>    <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed"/>    <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0"/>  </dependentAssembly> oldVersion - 那些版號要對應 - 可以用 - 來包含版號區間。 注意，newVersion不代表一定比oldVersion高。換句話說也有可能高版本全部要用低版本的dll（不過這種情景比較少 ，因為一般都是高版本會向下兼容） 不過這種方法不太建議，一律建議使用最上面的方法 參考 http://shaurong.blogspot.com/2020/02/systemiocompression.html
閱讀完整內容

MongoDB 入門

圖片
 MongoDB是一個NoSQL(非關聯性資料庫) 主要的儲存方式是 一個DB => 底下為一個Collection (類似Table) => 接著是文檔(docume) 存的資料型態為 BSON 是一種JSON的變體 如這樣 ID 自動生成 如SN 為唯一值 name sort之類的數值都是自己產生的 常用語法 通常都是以 db.CollectionName 開頭 find() 類似where 不輸入為全搜尋 insertOne({colname : data , colname2 :"data2"}) insert 用逗號分隔 insertMany([{colname : data} , {colname2 :"data2"}]) 如上 只是有中括號用來代表陣列 find().limit(N) 只回傳N筆 類似select() find().skip(N) 略過N筆後 類似skip() find().sort({colname : 1 , colname2 : -1}) 1是升冪 -1降冪 find()進階 => find({colname : 3} , {colname : 1 , colname2 : 0}) 這段的意思是 依照前面大括內的where 搜尋後 依照後面的select 條件選出 1是要輸出 0是不要輸出 假如只有0 則是 除了這些以外都要輸出  通常 1 跟 0 同時出現的時候是 _id這個字樣 因為這個是會自動返回的字樣 find(colname : {$gt : 3})  gt 大於 lt 小於 gte 大於等於 lte 小於等於 eq 等於 in => {$in :[ 1 , 3]} 一或三 nin => {$nin :[ 1 , 3]} 不等於1 或 3 都出 colname : { $exists : 1 } 代表這個colname存在的話則顯示 (nosql 因此型態可能不同) 0則是沒有的顯示 colname : {$regex :{/data/}} 正則表達式 找所有data  countDocuments() 總數 ex 找大於等於3小於等於5的 db.CollName.find(colname : {$gte : 3 , $let : 5}) 參考 : h...
閱讀完整內容

javascript 更改屬性及創建標籤

雖然應該是一些很基本的東西 但是我的前端其實蠻破的，所以筆記一下關於這個東西 //-----------------------------------1 如果要呈現某種屬性的話 就用 document.querySelector('id').setAttribute('readonly', true); 如果要刪除(隱藏)某種屬性的話就用 document.querySelector('id').removeAttribute('readonly'); //-----------------------------------2 $(document).ready(function () {         //取目標Tag為img者          //getElementsByTagName 不能使用forEach走訪 只好使用for走訪         var varTaglist = document.getElementsByTagName('img');         for (let i = 0; i < varTaglist.length; i++) {             //走訪建立id用於區分點選的目標             varTaglist[i].setAttribute('id', 'imgID' + i)             //走訪建立onclick屬性             varTaglist[i].setAttribute('onclick', 'imgclick(id)');         }     });     //設定屬性值 part2     var imgclick = function ...
閱讀完整內容