SQL學習筆記(1)-資料查詢、獨顯一值、運算符、函數

前言

本系列大多部分來自Hahow的課程－SQL的50道練習，學習過程中得到的筆記，文章內容也有部份都是從教材擷取出來，如果有興趣可以去上課看看！

那這張的基本語法其實網路上的網站fooish都講得蠻完善的了，部分文章內容也會引用網站文章內容。

開發環境

電腦系統：macOS（Big Sur）

資料庫管理工具：DBeaver

使用語言：SQLite

MySQL & SQLite差異

在語法中大多相同，所以SQLite對我的學習上是有幫助的，這兩者最大的差異主要是SQLite不支持網路訪問， 沒有伺服器的特性，但他也跟他的名字一樣，在設定、管理上都更加的輕盈、快速，有興趣可以到這個網路文章參考看看。

SELECT、FROM

SELECT 敘述句主要由兩部分構成，第一部分是要 “拿什麼” 資料 (若有多項用逗號隔開)；第二部分則為 “從哪拿”。

SELECT table_column1, table_column2, table_column3...
FROM table_name;

這個應該不會有什麼問題！

這裡table_column1, table_column2指的是要從資料表拿取col的名稱，table_name則是從哪一個資料表拿取。

LIMIT

主要用來限制資料查詢的數量（number）

SELECT table_column1, table_column2... 

FROM table_name LIMIT number;

範例

在課程中，imdb.db的有一個名字叫movies的資料表（table）；

如果想觀看資料表的全部可以搭配「＊」，「＊」意思是全選

SELECT ＊

 FROM movies 

結果；

.

.

.

如果只想顯示前三筆資料的話：

SELECT * --SELECT是選擇欄位

  FROM movies --FROM代表指定從哪一個資料表查詢

LIMIT 3 ;--LIMIT查詢結果顯示前幾列

結果：

AS語法

在 SQL 中我們可以替資料表或欄位名稱取一個別名 (Alias)，這可以使名稱複雜的 SQL 查詢語句更易讀且可以有更直觀的查詢結果。

​ 在下面，alias_name表示的就是要取的別名，

SELECT table_column1, table_column2, table_column3... 

FROM table_name AS alias_name;

範例

還沒使用AS：

SELECT firstName

			,lastName

 FROM players 

LIMIT 5;

結果：

使用AS

SELECT firstName AS first_name,

			,lastName AS last_name 

 FROM players 

LIMIT 5;

可以看到從查詢結果中col的名稱從firstName 改成了 first_name

DISTINCT

一個資料表的某欄位中可能會有多個紀錄都是相同值的情況，在 SELECT 查詢語句中我們可使用 DISTINCT 關鍵字過濾重複出現的紀錄值。

SELECT DISTINCT table_column1, table_column2

FROM table_name;

綜合SELECT、 FROM、AS、DISTINCT範例

在練習題08中，答案程式碼：

SELECT DISTINCT

	confName || ', ' || divName AS conf_div 

FROM teams

這邊的解釋就是，從資料表teams中，查詢出一筆confName再加上用「, 」跟divName合併的資料，這筆資料的名稱叫做conf_div，並且過濾掉重複的內容。

查詢結果：

ORDER BY

ORDER BY的能依照欄位來做排序，由ASC（遞增）或DESC（遞減）來決定遞增或遞減，預設是ASC，當ORDER BY有多欄位的時候，表示依照這些欄位在程式中的順序來排序

SELECT table_column1, table_column2...
FROM table_name
ORDER BY column_name1 ASC|DESC, column_name2 ASC|DESC...

範例

SELECT Date,

 			Country_Region ,

 			aily_Cases 

 	 FROM time_series --從time_series取出三筆資料Date,Country_Region ,aily_Cases 

  ORDER BY Daily_Cases DESC --依照Daily_Cases按照遞減做排序

LIMIT 10; --顯示前１０列

函數

函數可依照功能分為兩大類：

• 通用函數（Universal functions）
  • 轉換資料類型。
  • 計算數值。
  • 操作文字。
  • 操作日期時間。
• 聚合函數（Aggregate functions）

那其實如果要判斷是哪一種類型的函數的話，通用函數作用在「水平」方向的結果，聚合函數作用在「垂直」方向的結果。

通用函數

CAST

使用 CAST 函數可以將查詢結果的資料類型轉換為指定資料類型

data是要轉換的資料，data_type則是轉換的資料類型

CAST(data AS data_type)

CAST常用知識：

在sql中，兩過整數相除的時後結果只會是整數，如果想要顯示小數點的話，在做除法的時候，可以將「分子或分母其中之一」轉成REAL就可以顯示小數點

COALESCE

使用 COALESCE 函數可以將空值（或稱遺漏值）轉換為指定常數

這邊，field_name可以是欄位名稱，也可以是函數計算的結果，如果為null的話將會取代成replacement

COALESCE(field_name, replacement)

ROUND

使用 ROUND 函數可以調整查詢結果的小數點位數

n_digits＝小數點第幾為

ROUND(REAL, n_digits)

LENGTH

使用 LENGTH 函數可以計算文字中有幾個字元，包含空格、標點符號

TEXT裡面放TEXT的參數：

LENGTH(TEXT)

LOWER ＆ UPPER

使用 LOWER 與 UPPER 函數可以調整英文的大小寫

LOWER(TEXT)
UPPER(TEXT)

STRFTIME

在這部分，sqLite跟mySql的差別較大，sql可以參考官方文件的格式參數來調整顯示的格式，但mysql則是靠函數來操作，

SQLite

使用 STRFTIME 函數調整日期、時間或日期時間的顯示格式，

format是要顯示的格式，DATE/TIME/DATETIME則是要顯示的來源，

STRFTIME(format, DATE/TIME/DATETIME)

詳細用法參考網站

常見的日期與日期時間格式參數

• %d：二位數的日（01-31）
• %j：一年中的第幾天（001-366）
• %m：二位數的月（01-12）
• %w：一星期中的第幾天（0-6）
• %W：一年中的第幾週（00-53）
• %Y：四位數的年（0000-9999）

MYSQL

日期操作相關的函數 (date functions)

• NOW() 取得現在的日期時間
• CURDATE() 取得現在的日期
• CURTIME() 取得現在的時間
• DATE() 取出日期時間中日期的部分
• EXTRACT() 取出日期時間中特定的部分
• DATE_ADD() 給日期時間增加指定的間隔
• DATE_SUB() 給日期時間減去指定的間隔
• DATEDIFF() 日期相減
• TIMESTAMPDIFF() 日期時間相減
• DATE_FORMAT() 格式化日期時間顯示

來源

範例

SELECT DISTINCT STRFTIME('%Y-%m',Date) AS distinct_year_month
	FROM time_series;

從time_series資料表裡面選取FROM time_series

DATE欄位，並且只顯示年份還有月份STRFTIME('%Y-%m',Date)

並且不顯示重複的值 DISTINCT

別名為distinct_year_month AS distinct_year_month

功能：得知在time_series資料表裡面年份還有月份

結果：

．

．

．

聚合函數

常見的聚合函數

• AVG(column_name)：計算變數的平均數
• COUNT(column_name)：計算變數的「非」遺漏值數
• COUNT(*)：計算資料表的觀測值數
• MAX(column_name)：計算變數的最大值
• MIN(column_name)：計算變數的最小值
• SUM(column_name)：計算變數的加總

GITHUB

詳細範例放在github，（之後補）