萬盛學電腦網

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

SQL Server 基礎語法實戰(一):建庫、備份、建表、增刪改查、索引、視圖、系統表查詢

1. 數據庫級操作

-- 創建
CREATE DATABASE MyDB;
-- 刪除
DROP DATABASE MyDB;
-- 備份裝置與完整備份
USE master;
EXEC sp_addumpdevice 'disk', 'MyDB_BakDev', 'D:\Backup\MyDB.bak';
BACKUP DATABASE MyDB TO MyDB_BakDev;

2. 表結構管理

-- 創建表(含主鍵、非空、類型)
CREATE TABLE Products (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Qty INT DEFAULT 0,
    InDate DATETIME DEFAULT GETDATE()
);

-- 增加列
ALTER TABLE Products ADD UnitPrice DECIMAL(10,2);

-- 刪除列
ALTER TABLE Products DROP COLUMN UnitPrice;

-- 主鍵
ALTER TABLE Products ADD PRIMARY KEY (ID);
ALTER TABLE Products DROP CONSTRAINT PK_Products; -- 需知約束名

-- 索引
CREATE UNIQUE INDEX IX_Products_Name ON Products(Name);
DROP INDEX IX_Products_Name ON Products;

-- 視圖
CREATE VIEW V_ActiveProducts AS SELECT * FROM Products WHERE Qty > 0;
DROP VIEW V_ActiveProducts;

3. 基礎 DML

-- 插入
INSERT INTO Products (Name, Qty, InDate) VALUES ('礦泉水', 100, '2024-01-02');

-- 刪除
DELETE FROM Products WHERE Qty = 0;

-- 更新
UPDATE Products SET Qty = Qty + 10 WHERE Name = '礦泉水';

-- 查詢
SELECT * FROM Products;
SELECT Name, Qty FROM Products WHERE Name LIKE '%水%';
SELECT * FROM Products ORDER BY InDate DESC;
SELECT COUNT(*) AS Total FROM Products;
SELECT SUM(Qty) AS TotalQty FROM Products;

4. 系統表/信息模式查詢元數據

-- 所有用戶表
SELECT name FROM sysobjects WHERE xtype = 'U';
-- 表列
SELECT name FROM syscolumns WHERE id = OBJECT_ID('Products');
-- 信息模式(標準、跨版本穩定)
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Products';
SELECT * FROM INFORMATION_SCHEMA.VIEWS;

5. 進階查詢技巧

去重插入

INSERT INTO Target (Col1, Col2)
SELECT Col1, Col2 FROM Source s
WHERE NOT EXISTS (SELECT 1 FROM Target t WHERE t.Col1 = s.Col1);

分頁 (2012+)

SELECT * FROM Products ORDER BY ID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

按筆畫排序

SELECT * FROM Customers ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;

6. 事務模板

BEGIN TRAN
BEGIN TRY
    UPDATE Accounts SET Balance -= 100 WHERE ID = 1;
    UPDATE Accounts SET Balance += 100 WHERE ID = 2;
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    THROW; -- 重拋錯誤
END CATCH
copyright © 萬盛學電腦網 all rights reserved