萬盛學電腦網

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

SQL 基礎語法大全:增刪改查、去重插入、條件/排序/分頁/聚合/模糊/范圍/集合運算

1. 插入數據 (INSERT)

-- 單行
INSERT INTO StuInfo (StuName, StuNo, StuAge) VALUES ('張三', 'S2T14', 22);

-- 多行 (VALUES 列表)
INSERT INTO StuInfo (StuName, StuNo, StuAge) VALUES 
('李四','S2T15',21), ('王五','S2T16',22);

-- 從查詢結果插入 (INSERT SELECT)
INSERT INTO TongXunLu (Name, Address, Email)
SELECT SName, SAddress, SEmail FROM Students;

-- 創建新表並插入 (SELECT INTO)
SELECT SName, SAddress, SEmail INTO TongXunLu FROM Students;

-- 帶標識列的 SELECT INTO
SELECT IDENTITY(INT,1,1) AS StuID, SName, SAddress INTO NewTable FROM Students;

-- UNION 合並多源插入
INSERT INTO Students (SName, SGrade, SSex)
SELECT '女生1',7,0 UNION ALL SELECT '女生2',7,0 UNION ALL SELECT '男生1',7,1;

2. 刪除數據 (DELETE / TRUNCATE)

DELETE FROM StuInfo WHERE StuAge < 20;

TRUNCATE TABLE Students;  -- 清空表,DDL,不可回滾(隱式提交)、重置標識、不觸發觸發器、不記錄行日志

3. 更新數據 (UPDATE)

UPDATE StuInfo SET StuAge = 25 WHERE StuName = '張三';

-- 表達式更新
UPDATE Scores SET Score = Score + 5 WHERE Score <= 85;

4. 查詢數據 (SELECT)

全列/指定列

SELECT * FROM Students;
SELECT SName, SAge FROM Students;

列別名

SELECT SCode AS 學員編號, SName AS 學員姓名 FROM Students;
SELECT '姓名' = SName + '.' + LName FROM Employees;

空值判斷

SELECT SName FROM Students WHERE SEmail IS NULL;
SELECT SName FROM Students WHERE SEmail IS NOT NULL;

常量列

SELECT SName, '河北新龍' AS SchoolName FROM Students;

限制行數

SELECT TOP 10 SName FROM Students WHERE SSex = 0;

排序

SELECT * FROM Scores WHERE Score > 60 ORDER BY Score DESC, CourseID;

特殊排序:字符串數字混合 (13-1, 13-10, 13-100...)

SELECT ListNumber FROM SellRecord
ORDER BY 
    CONVERT(INT, LEFT(ListNumber, CHARINDEX('-', ListNumber)-1)),
    CONVERT(INT, STUFF(ListNumber, 1, CHARINDEX('-', ListNumber), ''));

5. 模糊查詢 (LIKE)

SELECT * FROM Students WHERE SName LIKE '張%';
SELECT * FROM Students WHERE SName LIKE '_三';  -- _ 單字符

6. 范圍查詢 (BETWEEN)

SELECT * FROM Scores WHERE Score BETWEEN 60 AND 80;
SELECT * FROM Sales WHERE OrdDate NOT BETWEEN '1992-08-01' AND '1993-08-01';

7. 列舉查詢 (IN)

SELECT SName FROM Students WHERE SAddress IN ('北京','廣州','上海') ORDER BY SAddress;

8. 聚合函數

SELECT Type, SUM(YtdSales) FROM Titles WHERE Type='business' GROUP BY Type;

9. 分組與篩選 (GROUP BY / HAVING)

SELECT Dept, AVG(Salary) FROM Emp GROUP BY Dept HAVING AVG(Salary) > 5000;

10. 集合運算

  • UNION:合併去重
  • UNION ALL:合併不去重(性能更好)
  • EXCEPT:差集 (左-右) 去重
  • INTERSECT:交集 去重
  • 均要求列數、順序、類型兼容。
copyright © 萬盛學電腦網 all rights reserved
函數說明示例
SUM求和SUM(YtdSales)
AVG平均AVG(Price)
COUNT行數/非空列數COUNT(*) / COUNT(Email)
MAX/MIN極值MAX(Score)