萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

15 個 SQL 初學者必會的基礎查詢語句完整教學

15 個 SQL 初學者必會的基礎查詢語句完整教學

本文以餐廳訂單系統(ItemMaster、OrderMaster、OrderDetail 三表)為例,從建表、插資料開始,循序漸進講解 15 類核心查詢技巧,包含 SELECT 基礎、彙總/標量/日期函數、TOP/DISTINCT、WHERE 進階、GROUP BY/HAVING、子查詢、多表 JOIN、UNION、CTE、VIEW、PIVOT、預存程序、自訂函數。

1. 建立範例資料表與測試資料

ItemMaster(產品主檔)

CREATE TABLE ItemMasters (
  Item_Code VARCHAR(20) PRIMARY KEY,
  Item_Name VARCHAR(100), Price INT, TAX1 INT, Discount INT,
  Description VARCHAR(200), IN_DATE DATETIME, IN_USR_ID VARCHAR(20),
  UP_DATE DATETIME, UP_USR_ID VARCHAR(20)
);
INSERT INTO ItemMasters VALUES
('Item001','Coke',55,1,0,'Coke which need to be cold',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Item002','Coffee',40,0,2,'Coffe Might be Hot or Cold',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Item003','Chiken Burger',125,2,5,'Spicy',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Item004','Potato Fry',15,0,0,'No Comments',GETDATE(),'SHANU',GETDATE(),'SHANU');

OrderMaster(訂單主檔)

CREATE TABLE OrderMasters (
  Order_No VARCHAR(20) PRIMARY KEY,
  Table_ID VARCHAR(20), Description VARCHAR(200),
  IN_DATE DATETIME, IN_USR_ID VARCHAR(20), UP_DATE DATETIME, UP_USR_ID VARCHAR(20)
);
INSERT INTO OrderMasters VALUES
('Ord_001','T1','',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Ord_002','T2','',GETDATE(),'MAK',GETDATE(),'MAK'),
('Ord_003','T3','',GETDATE(),'RAJ',GETDATE(),'RAJ');

OrderDetail(訂單明細)

CREATE TABLE OrderDetails (
  Order_Detail_No VARCHAR(20) PRIMARY KEY,
  Order_No VARCHAR(20) REFERENCES OrderMasters(Order_No),
  Item_Code VARCHAR(20) REFERENCES ItemMasters(Item_Code),
  Notes VARCHAR(200), QTY INT,
  IN_DATE DATETIME, IN_USR_ID VARCHAR(20), UP_DATE DATETIME, UP_USR_ID VARCHAR(20)
);
INSERT INTO OrderDetails VALUES
('OR_Dt_001','Ord_001','Item001','Need very Cold',3,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_002','Ord_001','Item004','very Hot',2,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_003','Ord_001','Item003','Very Spicy',4,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_004','Ord_002','Item002','Need very Hot',2,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_005','Ord_002','Item003','very Hot',2,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_006','Ord_003','Item003','Very Spicy',4,GETDATE(),'SHANU',GETDATE(),'SHANU');

2. 基礎 SELECT 查詢

-- 全欄位
SELECT * FROM ItemMasters;
-- 指定欄位與別名
SELECT Item_Code, Item_Name AS Item, Price, Description, IN_DATE FROM ItemMasters;
-- 字串串接
SELECT 'My Name' AS Col1, 'Is' AS Col2, 'SYED SHANU' AS Col3;

3. 彙總與標量函數

-- 彙總
SELECT COUNT(*) TotalRows, AVG(Price) AvgPrice, MAX(Price) MaxPrice, MIN(Price) MinPrice, SUM(Price) TotalPrice FROM ItemMasters;
-- 標量
SELECT UPPER(Item_Name) UpperName, LOWER(Item_Name) LowerName,
       SUBSTRING(Item_Name,2,3) SubStr, LEN(Item_Name) LenName,
       ROUND(Price,0) RoundPrice FROM ItemMasters;

4. 日期函數

SELECT GETDATE() Now,
       FORMAT(GETDATE(),'yyyy-MM-dd') DateOnly,
       FORMAT(GETDATE(),'HH:mm:ss') TimeOnly,
       CONVERT(VARCHAR(10),GETDATE(),110) Conv110,
       DATEPART(yyyy,GETDATE()) Yr, DATEPART(mm,GETDATE()) Mo, DATEPART(dd,GETDATE()) Dy,
       DATEADD(day,10,GETDATE()) Add10Day,
       DATEDIFF(day,'2020-01-01',GETDATE()) DiffDay;

5. TOP / ORDER BY / DISTINCT

SELECT TOP 2 * FROM ItemMasters ORDER BY Item_Code DESC;
SELECT DISTINCT Item_Name, Price FROM ItemMasters;

6. WHERE 進階篩選

-- 精確比對
SELECT * FROM ItemMasters WHERE Item_Name = 'Coke';
-- 模糊比對
SELECT * FROM ItemMasters WHERE Item_Name LIKE 'C%';
-- 多條件 AND/OR
SELECT * FROM ItemMasters WHERE Item_Name LIKE 'C%' AND Price >= 40;
-- IN 列表
SELECT * FROM ItemMasters WHERE Item_Name IN ('Coffee','Chiken Burger') ORDER BY Item_Code DESC;
-- BETWEEN 日期區間
SELECT * FROM ItemMasters WHERE IN_DATE BETWEEN '2024-01-01' AND '2024-12-31';

7. GROUP BY / HAVING

-- 基本分組彙總
SELECT Item_Name, COUNT(*) Cnt, AVG(Price) AvgP FROM ItemMasters GROUP BY Item_Name;
-- 多欄位分組
SELECT Order_No, Item_Code, SUM(QTY) TotalQty FROM OrderDetails WHERE QTY >= 2 GROUP BY Order_No, Item_Code ORDER BY Order_No DESC, Item_Code;
-- HAVING 過濾分組結果
SELECT Order_No, SUM(QTY) TotalQty FROM OrderDetails GROUP BY Order_No HAVING SUM(QTY) > 4;

8. 子查詢

-- WHERE 子查詢
SELECT * FROM ItemMasters WHERE Item_Code IN (SELECT Item_Code FROM ItemMasters WHERE Price > 40);
-- INSERT 子查詢
INSERT INTO ItemMasters (Item_Code,Item_Name,Price,TAX1,Discount,Description,IN_DATE,IN_USR_ID,UP_DATE,UP_USR_ID)
SELECT 'Item006', Item_Name, Price+4, TAX1, Discount, Description, GETDATE(),'SHANU',GETDATE(),'SHANU'
FROM ItemMasters WHERE Item_Code='Item002';

9. 多表 JOIN

-- 內連接三表
SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.QTY, I.Price, I.Price*D.QTY AS TotalPrice
FROM OrderMasters M
INNER JOIN OrderDetails D ON M.Order_No = D.Order_No
INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code;

-- LEFT/RIGHT/FULL OUTER JOIN 範例同上,僅替換關鍵字

10. UNION / UNION ALL

SELECT Item_Code,Item_Name,Price FROM ItemMasters WHERE Price <= 44
UNION ALL
SELECT Item_Code,Item_Name,Price FROM ItemMasters WHERE Price > 44;

11. CTE (Common Table Expression)

DECLARE @sDate DATETIME = DATEADD(day,-5,GETDATE()), @eDate DATETIME = DATEADD(day,16,GETDATE());
WITH cte AS (
  SELECT @sDate AS StartDate, 'W'+CAST(DATEPART(wk,@sDate) AS VARCHAR)+'('+CONVERT(VARCHAR(2),@sDate,106)+')' AS SDT
  UNION ALL
  SELECT DATEADD(day,1,StartDate), 'W'+CAST(DATEPART(wk,DATEADD(day,1,StartDate)) AS VARCHAR)+'('+CONVERT(VARCHAR(2),DATEADD(day,1,StartDate),106)+')'
  FROM cte WHERE DATEADD(day,1,StartDate) <= @eDate
)
SELECT * FROM cte OPTION (MAXRECURSION 0);

12. VIEW(檢視表)

CREATE VIEW myUnionView AS
SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.QTY, I.Price, I.Price*D.QTY AS TotalPrice
FROM OrderMasters M
INNER JOIN OrderDetails D ON M.Order_No = D.Order_No
INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code
WHERE I.Price <= 44
UNION ALL
SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.QTY, I.Price, I.Price*D.QTY AS TotalPrice
FROM OrderMasters M
INNER JOIN OrderDetails D ON M.Order_No = D.Order_No
INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code
WHERE I.Price > 44;

-- 使用 View
SELECT * FROM myUnionView WHERE Price > 40;

13. PIVOT 行轉欄

SELECT * FROM (
  SELECT Item_Name, Price AS TotAmount FROM ItemMasters
) src
PIVOT (SUM(TotAmount) FOR Item_Name IN ([Chiken Burger],[Coffee],[Coke])) pvt;

14. 預存程序

CREATE PROC USP_SelectPivot AS
BEGIN
  DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);
  SELECT @Cols = STUFF((SELECT ','+QUOTENAME(Item_Name) FROM ItemMasters GROUP BY Item_Name ORDER BY Item_Name FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'');
  SET @SQL = N'SELECT '+@Cols+N' FROM (SELECT Item_Name,Price AS TotAmount FROM ItemMasters) x PIVOT (SUM(TotAmount) FOR Item_Name IN ('+@Cols+N')) p';
  EXEC sp_executesql @SQL;
END;
EXEC USP_SelectPivot;

15. 自訂函數

-- 取得 ItemMasters 筆數+1
CREATE FUNCTION ufnSelectItemMaster() RETURNS INT AS BEGIN
  DECLARE @c INT; SELECT @c = COUNT(*)+1 FROM ItemMasters; RETURN @c; END;
SELECT dbo.ufnSelectItemMaster();

-- 取得指定年月的最後一天
CREATE FUNCTION ufn_LastDayOfMonth(@DATE NVARCHAR(10)) RETURNS NVARCHAR(10) AS BEGIN
  RETURN CONVERT(NVARCHAR(10), DATEADD(day,-1, DATEADD(month,1, CAST(SUBSTRING(@DATE,1,7)+'-01' AS DATETIME))), 120);
END;
SELECT dbo.ufn_LastDayOfMonth('2024-09-01'); -- 2024-09-30

小結

掌握這 15 類查詢模式,即可應付絕大多數日常開發與報表需求。建議實際在 SSMS 中跑一次範例,體會各子句的執行順序與結果差異。

copyright © 萬盛學電腦網 all rights reserved