【掌握SQL數據庫編程，輕鬆解決實際問題】30個實戰案例詳解

1. 數據庫基本操縱

1.1 創建數據庫跟表

CREATE DATABASE MyDB;

USE MyDB;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50)
);

1.2 拔出數據

INSERT INTO Employees (EmployeeID, EmployeeName, Department) 
VALUES (1, 'Alice', 'HR');

INSERT INTO Employees (EmployeeID, EmployeeName, Department) 
VALUES (2, 'Bob', 'IT');

1.3 查詢數據

SELECT * FROM Employees WHERE Department = 'HR';

2. SQL高等查詢

2.1 連接查詢

SELECT e.EmployeeName, d.DepartmentName 
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

2.2 子查詢

SELECT EmployeeName 
FROM Employees 
WHERE EmployeeID IN (SELECT ManagerID FROM Departments);

2.3 湊集函數

SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT MAX(Salary) AS MaxSalary FROM Employees;

3. 數據庫保護

3.1 索引

CREATE INDEX idx_employee ON Employees (EmployeeName);

CREATE INDEX idx_department ON Departments (DepartmentName);

3.2 視圖

CREATE VIEW v_employee_department AS
SELECT EmployeeName, Department
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

3.3 存儲過程

CREATE PROCEDURE GetEmployeeDetails 
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

4. 數據庫保險

4.1 角色跟權限

CREATE ROLE SalesManager;

GRANT SELECT ON Employees TO SalesManager;

GRANT INSERT, UPDATE, DELETE ON Employees TO SalesManager;

4.2 加密數據

ALTER TABLE Employees
ADD PasswordHash VARBINARY(64);

UPDATE Employees
SET PasswordHash = HASHBYTES('SHA2_256', 'mypassword');

5. 實戰案例

5.1 圖書管理體系

5.1.1 創建表

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(100),
    Genre VARCHAR(50),
    Price DECIMAL(10, 2)
);

5.1.2 拔出數據

INSERT INTO Books (BookID, Title, Author, Genre, Price) 
VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 20.00);

5.1.3 查詢數據

SELECT * FROM Books WHERE Genre = 'Classic';

5.2 電子商務平台

5.2.1 創建表

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);

5.2.2 拔出數據

INSERT INTO Orders (OrderID, CustomerName, OrderDate, TotalAmount) 
VALUES (1, 'John Doe', '2022-01-01', 150.00);

5.2.3 查詢數據

SELECT * FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND '2022-01-31';

6. 總結

經由過程以上實戰案例，你可能懂掉掉落SQL數據庫編程在現實利用中的各個方面。控制SQL數據庫編程，可能幫助你輕鬆處理現實成績。在現實利用中，根據具體須要抉擇合適的方法跟東西，壹直現實跟總結，將使你在數據庫範疇獲得更大年夜的進步。