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. 聚合函數
| 函數 | 說明 | 示例 |
| SUM | 求和 | SUM(YtdSales) |
| AVG | 平均 | AVG(Price) |
| COUNT | 行數/非空列數 | COUNT(*) / COUNT(Email) |
| MAX/MIN | 極值 | MAX(Score) |
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