高级 SQL

|
// 连接到数据库的第一步：建立连接
public void connectToStudentDatabase() {
    try {
        // 加载数据库驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        
        // 建立与数据库的连接
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/university", 
            "admin", "password123");
        
        // 创建语句执行器
        Statement stmt = conn.createStatement();
        
        // 插入新学生信息
        stmt.executeUpdate(
            "INSERT INTO student VALUES('S2023001', '张小明', '计算机科学', 3.8)");
        
        // 查询计算机科学专业的学生平均成绩
        ResultSet result = stmt.executeQuery(
            "SELECT major, AVG(gpa) " +
            "FROM student " +
            "WHERE major = '计算机科学' " +
            "GROUP BY major");
        
        // 处理查询结果
        while (result.next()) {
            String major = result.getString("major");
            double avgGpa = result.getDouble(2); // 也可以用列号
            System.out.println(major + "专业平均GPA: " + avgGpa);
        }
        
        // 关闭连接，释放资源
        stmt.close();
        conn.close();
    } catch (Exception e) {
        System.out.println("数据库操作失败: " + e.getMessage());
    }
}

|
public void findStudentsByMajor(String major) {
    try {
        // 使用预处理语句，用?作为参数占位符
        PreparedStatement pstmt = conn.prepareStatement(
            "SELECT student_id, name, gpa " +
            "FROM student " +
            "WHERE major = ? AND gpa > ?");
        
        // 设置参数值（第一个参数是位置，从1开始）
        pstmt.setString(1, major);      // 设置专业名称
        pstmt.setDouble(2, 3.0);        // 设置GPA阈值
        

|
// C语言中使用ODBC连接数据库的示例
void queryStudentData() {
    SQLHENV env;    // 环境句柄
    SQLHDBC conn;   // 连接句柄
    SQLHSTMT stmt;  // 语句句柄
    
    // 分配环境句柄
    SQLAllocEnv(&env);
    
    // 分配连接句柄
    SQLAllocConnect(env, &conn);
    
    // 连接到数据库
    SQLConnect(conn, "university_db", SQL_NTS, 
               "admin", SQL_NTS, "password", SQL_NTS);
    
    // 分配语句句柄

|
// 嵌入式SQL的示例（使用EXEC SQL标记）
void updateStudentGrades() {
    // 声明程序变量
    EXEC SQL BEGIN DECLARE SECTION;
    char student_id[10];
    char course_id[8];
    float new_grade;
    EXEC SQL END DECLARE SECTION;
    
    // 连接到数据库
    EXEC SQL CONNECT TO university_db 
        USER admin USING 'password123';
    
    // 声明游标来遍历需要更新的记录
    EXEC SQL DECLARE grade_cursor CURSOR FOR
        SELECT student_id, course_id
        FROM enrollment
        WHERE grade IS NULL

|
-- 创建一个计算图书分类总数的函数
CREATE FUNCTION count_books_by_category(category_name VARCHAR(50))
RETURNS INTEGER
BEGIN
    DECLARE book_count INTEGER;
    
    -- 查询指定分类下的图书数量
    SELECT COUNT(*) INTO book_count
    FROM books
    WHERE category = category_name;
    
    RETURN book_count;
END

|
-- 查询各个分类的图书数量
SELECT category, count_books_by_category(category) AS total_books
FROM (SELECT DISTINCT category FROM books) AS categories
WHERE count_books_by_category(category) > 10;

|
-- 创建返回表的函数
CREATE FUNCTION get_books_by_author(author_name VARCHAR(100))
RETURNS TABLE (
    book_id INT,
    title VARCHAR(200),
    publication_year INT,
    price DECIMAL(8,2)
)
AS
RETURN (
    SELECT book_id, title, publication_year, price
    FROM books

|
-- 获取"金庸"的所有作品
SELECT * 
FROM get_books_by_author('金庸')
WHERE price > 30.00;

|
-- 创建图书借阅的存储过程
CREATE PROCEDURE borrow_book(
    IN student_id VARCHAR(20),
    IN book_id INT,
    OUT result_message VARCHAR(200)
)
BEGIN
    DECLARE student_status VARCHAR(20);
    DECLARE book_available INT;
    DECLARE overdue_count INT;
    
    -- 检查学生状态

|
-- 声明变量接收返回消息
DECLARE borrow_result VARCHAR(200);
 
-- 调用借书存储过程
CALL borrow_book('S2023001', 1001, borrow_result);
 
-- 输出结果
SELECT borrow_result AS 'borrowing_result';

|
-- 创建一个自动生成学生成绩报告的存储过程
CREATE PROCEDURE generate_grade_report(IN student_id VARCHAR(20))
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE subject_name VARCHAR(50);
    DECLARE score DECIMAL(5,2);
    DECLARE total_score DECIMAL(5,2) DEFAULT 0;

|
-- 创建带异常处理的转账存储过程
CREATE PROCEDURE transfer_money(
    IN from_account VARCHAR(20),
    IN to_account VARCHAR(20),
    IN amount DECIMAL(10,2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE insufficient_balance CONDITION FOR SQLSTATE '45000';
    DECLARE

|
-- 创建订单后自动更新库存的触发器
CREATE TRIGGER update_inventory_after_order
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    DECLARE product_name VARCHAR(100);
    
    -- 获取当前库存和商品名称
    SELECT stock_quantity, name INTO current_stock, product_name
    FROM products
    WHERE product_id = NEW.product_id

|
-- 学生选课时检查班级容量的触发器
CREATE TRIGGER check_class_capacity_before_enrollment
BEFORE INSERT ON course_enrollments
FOR EACH ROW
BEGIN
    DECLARE current_enrollment INT;
    DECLARE max_capacity INT;
    DECLARE course_name VARCHAR(100);
    
    -- 获取当前选课人数和最大容量
    SELECT 
        COUNT(*), c.max_capacity, 

|
-- 员工信息变更的审计触发器
CREATE TRIGGER audit_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 记录薪资变更
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_audit_log (
            employee_id, old_salary, new_salary, 
            change_date, changed_by
        ) VALUES (
            NEW.employee_id, OLD.salary, 

|
-- 删除部门时自动处理员工转移的触发器
CREATE TRIGGER handle_department_deletion
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
    DECLARE employee_count INT;
    
    -- 检查该部门是否有员工
    SELECT COUNT(*) INTO employee_count
    FROM employees
    WHERE department_id = OLD.department_id;
    
    IF employee_count 

|
-- 安全的库存同步触发器（避免循环）
CREATE TRIGGER sync_inventory_safely
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- 只有当库存量真正改变时才执行同步
    IF OLD.stock_quantity != NEW.stock_quantity THEN
        -- 使用标志位避免循环触发
        IF @sync_in_progress IS NULL THEN
            SET @sync_in_progress = 1;
            
            -- 同步到备份表
            UPDATE product_backup

|
-- 简洁高效的日志记录触发器
CREATE TRIGGER log_important_changes
AFTER UPDATE ON customer_accounts
FOR EACH ROW
BEGIN
    -- 只记录重要字段的变更
    IF OLD.account_status != NEW.account_status 
       OR OLD.credit_limit != NEW.credit_limit THEN
        INSERT INTO account_change_log (
            account_id, change_type, old_value, new_value, change_time
        ) VALUES (

|
-- 查找CS401的所有先修课程
WITH RECURSIVE course_prerequisites(course_code, prerequisite_code, level) AS (
    -- 基础查询：找到直接先修课程
    SELECT 
        course_code, 
        prerequisite_code,
        1 as level
    FROM prerequisites
    WHERE course_code = 'CS401'
    
    UNION ALL
    
    -- 递归查询：找到先修课程的先修课程
    SELECT 
        p.course_code,
        p.prerequisite_code

|
-- 查找某个管理者下面的所有下属
WITH RECURSIVE employee_hierarchy(employee_id, name, manager_id, level) AS (
    -- 基础查询：找到指定的管理者
    SELECT 
        employee_id, 
        name, 
        manager_id,
        0 as level
    FROM employees
    WHERE employee_id = 'MGR001'  -- 查找总经理下的所有员工
    
    UNION ALL
    
    -- 递归查询：找到下属的下属
    SELECT 
        e.

|
-- 查找从北京到上海的所有可能路线
WITH RECURSIVE route_finder(
    start_city, 
    end_city, 
    current_city, 
    path, 
    total_distance, 
    hop_count
) AS (
    -- 基础查询：从北京出发的直达路线
    SELECT 
        '北京' as start_city,
        end_city,
        end_city as current_city,
        '北京 → ' || end_city as path,
        distance as total_distance,
        1 as

|
-- 找到用户可能认识的人（朋友的朋友）
WITH RECURSIVE friend_network(user_id, friend_id, connection_level, path) AS (
    -- 基础查询：直接好友
    SELECT 
        user_id,
        friend_id,
        1 as connection_level,
        CAST(user_id || ' → ' || friend_id AS VARCHAR(1000)) as path
    FROM friendships
    WHERE user_id = 'USER123'
    
    UNION ALL

|
-- 递归查询文件夹下的所有文件
WITH RECURSIVE file_tree(file_id, file_name, parent_id, full_path, level) AS (
    -- 基础查询：根目录
    SELECT 
        file_id,
        file_name,
        parent_id,
        file_name as full_path,
        0 as level
    FROM files
    WHERE parent_id IS NULL  -- 根目录
    
    UNION ALL
    
    -- 递归查询：子目录和文件
    SELECT 
        f.

|
-- 计算学生GPA排名
SELECT 
    student_id,
    student_name,
    gpa,
    RANK() OVER (ORDER BY gpa DESC) as class_rank,
    DENSE_RANK() OVER (ORDER BY gpa DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY gpa DESC) as row_num
FROM students
ORDER BY gpa 

|
-- 按专业分组进行排名
SELECT 
    student_name,
    major,
    gpa,
    RANK() OVER (PARTITION BY major ORDER BY gpa DESC) as major_rank,
    RANK() OVER (ORDER BY gpa DESC) as overall_rank
FROM students
ORDER BY major, major_rank;

|
-- 计算学生成绩的百分位数和分桶
SELECT 
    student_name,
    gpa,
    PERCENT_RANK() OVER (ORDER BY gpa) as percentile,
    CUME_DIST() OVER (ORDER BY gpa) as cumulative_distribution,
    NTILE(4) OVER (ORDER BY gpa DESC) as quartile,
    NTILE(10) OVER (ORDER BY

|
-- 计算股票价格的移动平均
SELECT 
    trading_date,
    stock_price,
    AVG(stock_price) OVER (
        ORDER BY trading_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7_days,
    AVG(stock_price) OVER (
        ORDER BY trading_date 
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as moving_avg_30_days

|
-- 销售额的同期对比分析
SELECT 
    year_month,
    sales_amount,
    LAG(sales_amount, 1) OVER (ORDER BY year_month) as prev_month,
    LAG(sales_amount, 12) OVER (ORDER BY year_month) as same_month_last_year,
    sales_amount - LAG(sales_amount, 1) OVER (ORDER BY year_month) as month_growth,
    sales_amount - LAG

|
-- 计算累积销售额和运行总计
SELECT 
    sale_date,
    daily_sales,
    SUM(daily_sales) OVER (
        ORDER BY sale_date 
        ROWS UNBOUNDED PRECEDING
    ) as cumulative_sales,
    SUM(daily_sales) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as rolling_7_day_total,
    AVG(daily_sales) OVER (

|
-- 创建一个多维度的销售分析视图
CREATE VIEW sales_analysis AS
SELECT 
    YEAR(sale_date) as year,
    QUARTER(sale_date) as quarter,
    MONTH(sale_date) as month,
    region,
    category,
    brand,
    SUM(quantity) as total_quantity,
    SUM(revenue) as total_revenue,
    AVG(unit_price) as avg_price,

|
-- 切片操作：只分析2023年的销售数据
SELECT 
    region as '地区',
    category as '商品类别',
    SUM(total_revenue) as '销售额',
    SUM(total_quantity) as '销售数量'
FROM sales_analysis
WHERE year = 2023
GROUP BY region, category
ORDER BY SUM(total_revenue) DESC;

|
-- 切块操作：分析2023年第三、四季度华北和华东地区的服装销售
SELECT 
    quarter as '季度',
    region as '地区',
    brand as '品牌',
    SUM(total_revenue) as '销售额'
FROM sales_analysis
WHERE year = 2023 
    AND quarter IN (3, 4)
    AND region IN ('华北', 

|
-- 上钻操作：从月度数据聚合到季度数据
SELECT 
    year as '年份',
    quarter as '季度',
    SUM(total_revenue) as '季度销售额',
    AVG(total_revenue) as '月均销售额',
    COUNT(*) as '月份数'
FROM sales_analysis
GROUP BY year, quarter
ORDER BY year, quarter;

|
-- 下钻操作：从年度数据细化到月度数据
SELECT 
    year as '年份',
    month as '月份',
    region as '地区',
    SUM(total_revenue) as '月销售额',
    SUM(total_quantity) as '月销售量',
    COUNT(DISTINCT brand) as '品牌数量'
FROM sales_analysis
WHERE year = 2023
GROUP BY year

|
-- 旋转操作：将地区作为列，类别作为行
SELECT 
    category as '商品类别',
    SUM(CASE WHEN region = '华北' THEN total_revenue ELSE 0 END) as '华北',
    SUM(CASE WHEN region = '华东' THEN total_revenue ELSE 0 END) as '华东',
    SUM(CASE

|
-- CUBE操作：生成所有维度组合的销售数据
SELECT 
    COALESCE(region, '全部地区') as region,
    COALESCE(category, '全部类别') as category,
    COALESCE(brand, '全部品牌') as brand,
    SUM(total_revenue) as revenue,
    COUNT(*) as record_count
FROM sales_analysis
WHERE year = 2023
GROUP BY CUBE

|
-- ROLLUP操作：按时间层次进行聚合
SELECT 
    COALESCE(CAST(year AS CHAR), '总计') as year,
    COALESCE(CAST(quarter AS CHAR), '全年') as quarter,
    COALESCE(CAST(month AS CHAR), '全季') as month,

|
DECLARE gpa_result DECIMAL(4,2);
CALL calculate_gpa('S2023001', gpa_result);
SELECT gpa_result AS 'GPA';

|
-- 查看某图书当前状态
SELECT title, available_copies FROM books WHERE book_id = 1001;
 
-- 插入借阅记录
INSERT INTO borrowings (student_id, book_id, borrow_date, due_date)
VALUES ('S2023001', 1001, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY));
 
-- 再次查看图书状态，应该减少了1本
SELECT title, available_copies FROM books WHERE book_id = 1001;