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数据库编程，能够帮助你轻松解决实际问题。在实际应用中，根据具体需求选择合适的方法和工具，不断实践和总结，将使你在数据库领域取得更大的进步。