35
0
1
常见开发语言
主流开发语言核心语法示例与代码高亮测试指南
对于开发者而言，快速识别和理解不同编程语言的语法结构是一项基本技能。本文旨在提供一个集中的参考，展示包括 Python、JavaScript、Java、C#、Go、Rust 等在内的 13 种常见开发语言的基础语法示例。每个示例至少包含 5 行代码，涵盖了变量定义、控制结构、函数 / 方法声明等核心概念，非常适合用于测试代码编辑器的语法高亮功能，同时也为开发者提供了一份简洁的跨语言语法速查手册。
1. Python - 简洁高效的脚本语言
Python 示例：
# Python 示例：函数、列表推导式和条件判断
def calculate_fibonacci(n: int) -> list:
"""生成斐波那契数列"""
if n <= 0:
return []
sequence = [0, 1]
while len(sequence) < n:
sequence.append(sequence[-1] + sequence[-2])
return sequence[:n]
# 使用列表推导式生成平方数
squares = [x**2 for x in range(10) if x % 2 == 0]
print(f"斐波那契数列：{calculate_fibonacci(8)}")
print(f"偶数平方列表：{squares}")
2. JavaScript - 动态的Web脚本语言
JavaScript 示例：
// JavaScript 示例：箭头函数、异步操作和ES6模块
const fetchUserData = async (userId) => {
try {
const response = await fetch(`/api/users/${userId}`);
if (!response.ok) throw new Error('Network response was not ok');
const data = await response.json();
return data;
} catch (error) {
console.error('Fetch error:', error);
return null;
}
};
// 数组的高阶函数使用
const numbers = [1, 2, 3, 4, 5];
const doubled = numbers.map(num => num * 2);
console.log(`原始数组：${numbers}，加倍后：${doubled}`);
3. TypeScript - 类型安全的JavaScript超集
TypeScript 示例：
// TypeScript 示例：接口、泛型和类型注解
interface ApiResponse {
status: number;
data: T;
message: string;
}
const processResponse = (response: ApiResponse): T | null => {
if (response.status >= 200 && response.status < 300) {
console.log(`Success: ${response.message}`);
return response.data;
}
console.error(`Error ${response.status}: ${response.message}`);
return null;
};
// 使用定义的接口
const userResponse: ApiResponse<{ id: number; name: string }> = {
status: 200,
data: { id: 1, name: 'Alice' },
message: 'User found'
};
const userData = processResponse(userResponse);
4. Java - 面向对象的跨平台语言
Java 示例：
// Java 示例：类定义、泛型集合和流式API
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Main {
public static void main(String[] args) {
// 使用泛型集合
List names = new ArrayList<>();
names.add("John");
names.add("Jane");
names.add("Doe");
// 使用Stream API进行过滤和转换
List filteredNames = names.stream()
.filter(name -> name.startsWith("J"))
.map(String::toUpperCase)
.collect(Collectors.toList());
System.out.println("Filtered names: " + filteredNames);
System.out.println("Total count: " + names.size());
}
}
5. C# - 微软的现代多范式语言
C# 示例：
// C# 示例：属性、LINQ查询和异步方法
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
public string Name { get; set; } // 自动属性
public static async Task Main(string[] args)
{
// 集合初始化器
var numbers = new List { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
// LINQ查询表达式
var evenSquares = from num in numbers
where num % 2 == 0
select num * num;
Console.WriteLine($"Even squares: {string.Join(", ", evenSquares)}");
// 异步任务示例
await ProcessDataAsync();
}
private static async Task ProcessDataAsync()
{
await Task.Delay(1000);
Console.WriteLine("Async processing completed.");
}
}
6. Go - 简洁的并发编程语言
Go 示例：
// Go 示例：协程、通道和结构体方法
package main
import (
"fmt"
"time"
)
// 定义结构体
type Worker struct {
ID int
Name string
}
// 结构体方法
func (w Worker) PerformTask(task string) {
fmt.Printf("Worker %d (%s) is processing: %s\n", w.ID, w.Name, task)
}
func main() {
// 使用通道进行协程间通信
taskChan := make(chan string, 3)
doneChan := make(chan bool)
// 启动工作协程
go func() {
for task := range taskChan {
worker := Worker{ID: 1, Name: "GoRoutine"}
worker.PerformTask(task)
time.Sleep(500 * time.Millisecond)
}
doneChan <- true
}()
// 发送任务
tasks := []string{"Task A", "Task B", "Task C"}
for _, task := range tasks {
taskChan <- task
}
close(taskChan)
<-doneChan
fmt.Println("All tasks completed.")
}
7. Rust - 内存安全的系统编程语言
Rust 示例：
// Rust 示例：所有权、模式匹配和Result处理
use std::fs::File;
use std::io::{self, Read};
fn read_file_contents(path: &str) -> Result {
let mut file = File::open(path)?;
let mut contents = String::new();
file.read_to_string(&mut contents)?;
Ok(contents)
}
fn process_data(data: &str) -> Option {
match data.len() {
0 => None,
len if len > 100 => Some(100), // 使用匹配守卫
len => Some(len),
}
}
fn main() {
let filename = "data.txt";
let result = read_file_contents(filename);
match result {
Ok(content) => {
println!("File read successfully.");
if let Some(processed_len) = process_data(&content) {
println!("Processed length: {}", processed_len);
}
}
Err(e) => println!("Error reading file: {}", e),
}
// 向量和迭代器
let numbers = vec![10, 20, 30, 40, 50];
let sum: i32 = numbers.iter().sum();
println!("Sum of numbers: {}", sum);
}
8. C++ - 高性能的系统级语言
C++ 示例：
// C++ 示例：智能指针、模板和范围for循环
#include
#include
#include
#include
template
void print_vector(const std::vector& vec) {
std::cout << "Vector elements: ";
for (const auto& element : vec) { // 范围for循环
std::cout << element << " ";
}
std::cout << std::endl;
}
int main() {
// 使用智能指针管理动态内存
auto numbers = std::make_unique>();
numbers->push_back(5);
numbers->push_back(3);
numbers->push_back(8);
numbers->push_back(1);
// 使用算法库排序
std::sort(numbers->begin(), numbers->end());
print_vector(*numbers);
// Lambda表达式
auto is_even = [](int n) { return n % 2 == 0; };
int count = std::count_if(numbers->begin(), numbers->end(), is_even);
std::cout << "Count of even numbers: " << count << std::endl;
return 0;
}
9. PHP - 广泛使用的服务器端脚本语言
PHP 示例：
// PHP 示例：类、数组操作和命名空间
namespace App\Services;
class UserService {
private $users = [];
public function __construct(array $initialUsers = []) {
$this->users = $initialUsers;
}
public function addUser(string $name, int $age): void {
$this->users[] = ['name' => $name, 'age' => $age];
}
public function getAdultUsers(): array {
return array_filter($this->users, function($user) {
return $user['age'] >= 18;
});
}
public function getUserNames(): array {
return array_column($this->users, 'name');
}
}
// 使用示例
$service = new UserService([['name' => 'John', 'age' => 25]]);
$service->addUser('Jane', 17);
$adults = $service->getAdultUsers();
echo "Adult users: " . count($adults) . "\n";
print_r($service->getUserNames());
10. Ruby - 优雅灵活的面向对象语言
Ruby 示例：
# Ruby 示例：类、块和符号
class Calculator
attr_accessor :result
def initialize
@result = 0
end
def add(value)
@result += value
self # 允许链式调用
end
def multiply(value)
@result *= value
self
end
def calculate(&block)
instance_eval(&block) if block_given?
@result
end
end
# 使用块进行计算
calc = Calculator.new
total = calc.calculate do
add 10
multiply 3
add 5
end
puts "Calculation result: #{total}"
# 数组的map和select方法
numbers = [1, 2, 3, 4, 5]
squared_evens = numbers.select { |n| n.even? }.map { |n| n**2 }
puts "Squared even numbers: #{squared_evens}"
11. Swift - Apple的现代编程语言
Swift 示例：
// Swift 示例：可选类型、协议和闭包
import Foundation
protocol Displayable {
var description: String { get }
}
struct Product: Displayable {
let id: UUID
var name: String
var price: Double
// 计算属性
var description: String {
return "\(name) - $\(price)"
}
// 方法
mutating func applyDiscount(percentage: Double) {
guard percentage > 0 && percentage <= 50 else {
print("Invalid discount percentage.")
return
}
price *= (1 - percentage / 100)
}
}
// 使用高阶函数处理集合
let products = [
Product(id: UUID(), name: "Laptop", price: 999.99),
Product(id: UUID(), name: "Mouse", price: 25.50),
Product(id: UUID(), name: "Keyboard", price: 75.00)
]
let expensiveProducts = products.filter { $0.price > 50 }
.sorted { $0.price > $1.price }
print("Expensive products:")
for product in expensiveProducts {
print(product.description)
}
// 使用可选绑定
if let firstProduct = products.first {
print("First product: \(firstProduct.name)")
}
12. Shell (Bash) - 强大的命令行脚本语言
Shell (Bash) 示例：
#!/bin/bash
# Shell脚本示例：函数、条件判断和循环
# 定义函数
backup_files() {
local source_dir=$1
local backup_dir=$2
local timestamp=$(date +"%Y%m%d_%H%M%S")
if [[ ! -d "$source_dir" ]]; then
echo "错误：源目录 '$source_dir' 不存在."
return 1
fi
mkdir -p "$backup_dir"
echo "开始备份 $source_dir 到 $backup_dir/backup_$timestamp.tar.gz"
# 创建压缩备份
tar -czf "$backup_dir/backup_$timestamp.tar.gz" -C "$(dirname "$source_dir")" "$(basename "$source_dir")"
if [[ $? -eq 0 ]]; then
echo "备份成功完成."
return 0
else
echo "备份过程中出现错误."
return 1
fi
}
# 主脚本逻辑
readonly LOG_FILE="/var/log/myapp.log"
# 检查并创建日志文件
if [[ ! -f "$LOG_FILE" ]]; then
touch "$LOG_FILE"
echo "$(date): 创建新的日志文件。" >> "$LOG_FILE"
fi
# 循环处理文件
for file in /tmp/*.tmp; do
if [[ -f "$file" ]]; then
echo "处理文件: $file" >> "$LOG_FILE"
# 实际处理逻辑...
fi
done
# 调用函数
backup_files "/home/user/documents" "/backup"
# 退出状态检查
if [[ $? -eq 0 ]]; then
echo "脚本执行成功。"
exit 0
else
echo "脚本执行失败。"
exit 1
fi
13. SQL - 结构化查询语言
SQL 示例：
-- =====================================================
-- SQL 示例：复杂查询、事务、窗口函数、存储过程、触发器
-- 演示特性：CTE、聚合、分区、索引、外键约束
-- =====================================================
-- -------------------- 表结构定义 --------------------
-- 用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
avatar_url TEXT,
role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('admin', 'moderator', 'user', 'guest')),
is_active BOOLEAN DEFAULT TRUE,
last_login TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role_active ON users(role, is_active);
CREATE INDEX idx_users_created_at ON users(created_at DESC);
-- 产品分类表
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(100) NOT NULL UNIQUE,
description TEXT,
parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_slug ON categories(slug);
-- 产品表
CREATE TABLE products (
id SERIAL PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
description TEXT,
long_description TEXT,
category_id INTEGER REFERENCES categories(id) ON DELETE RESTRICT,
price DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
compare_at_price DECIMAL(12, 2) CHECK (compare_at_price >= 0),
cost DECIMAL(12, 2) DEFAULT 0,
quantity INTEGER NOT NULL DEFAULT 0,
min_quantity INTEGER DEFAULT 1,
max_quantity INTEGER DEFAULT 999,
weight_kg DECIMAL(8, 3),
is_active BOOLEAN DEFAULT TRUE,
is_featured BOOLEAN DEFAULT FALSE,
tags TEXT[], -- PostgreSQL 数组类型
metadata JSONB, -- PostgreSQL JSONB 类型
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_active_featured ON products(is_active, is_featured);
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);
-- 订单表
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_number VARCHAR(20) NOT NULL UNIQUE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN (
'pending', 'confirmed', 'processing', 'shipped',
'delivered', 'cancelled', 'refunded'
)),
total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
discount_amount DECIMAL(12, 2) DEFAULT 0,
shipping_amount DECIMAL(12, 2) DEFAULT 0,
tax_amount DECIMAL(12, 2) DEFAULT 0,
shipping_address JSONB NOT NULL,
billing_address JSONB,
payment_method VARCHAR(50),
payment_status VARCHAR(20) DEFAULT 'pending',
notes TEXT,
placed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_placed_at ON orders(placed_at DESC);
CREATE INDEX idx_orders_shipping_address ON orders USING GIN(shipping_address);
-- 订单明细表
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
sku VARCHAR(50) NOT NULL,
product_name VARCHAR(255) NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(12, 2) NOT NULL CHECK (unit_price >= 0),
discount_amount DECIMAL(12, 2) DEFAULT 0,
total_price DECIMAL(12, 2) GENERATED ALWAYS AS
(quantity * unit_price - discount_amount) STORED,
metadata JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
-- 产品评论表
CREATE TABLE product_reviews (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
title VARCHAR(255),
content TEXT,
is_verified_purchase BOOLEAN DEFAULT FALSE,
helpful_count INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(product_id, user_id)
);
CREATE INDEX idx_product_reviews_product_id ON product_reviews(product_id);
CREATE INDEX idx_product_reviews_user_id ON product_reviews(user_id);
CREATE INDEX idx_product_reviews_rating ON product_reviews(rating);
-- -------------------- 触发器函数 --------------------
-- 自动更新 updated_at 字段
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_product_reviews_updated_at
BEFORE UPDATE ON product_reviews
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- 生成订单号触发器
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TRIGGER AS $$
BEGIN
NEW.order_number := 'ORD-' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || '-' ||
LPAD(CAST(nextval('order_number_seq') AS TEXT), 6, '0');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE SEQUENCE IF NOT EXISTS order_number_seq START 1;
CREATE TRIGGER trg_generate_order_number
BEFORE INSERT ON orders
FOR EACH ROW
WHEN (NEW.order_number IS NULL)
EXECUTE FUNCTION generate_order_number();
-- 库存更新触发器
CREATE OR REPLACE FUNCTION update_product_quantity()
RETURNS TRIGGER AS $$
BEGIN
UPDATE products
SET quantity = quantity - NEW.quantity
WHERE id = NEW.product_id;
-- 检查库存是否为负
IF (SELECT quantity FROM products WHERE id = NEW.product_id) < 0 THEN
RAISE EXCEPTION 'Insufficient inventory for product_id: %', NEW.product_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_inventory
AFTER INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION update_product_quantity();
-- -------------------- 视图 --------------------
-- 产品销售统计视图
CREATE OR REPLACE VIEW product_sales_summary AS
WITH sales_data AS (
SELECT
p.id,
p.sku,
p.name,
p.category_id,
c.name AS category_name,
COUNT(DISTINCT o.id) AS order_count,
COALESCE(SUM(oi.quantity), 0) AS total_quantity_sold,
COALESCE(SUM(oi.total_price), 0) AS total_revenue,
AVG(oi.unit_price) AS avg_selling_price,
COALESCE(AVG(pr.rating), 0) AS avg_rating,
COUNT(DISTINCT pr.id) AS review_count
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
AND o.status IN ('delivered', 'shipped')
LEFT JOIN product_reviews pr ON p.id = pr.product_id
GROUP BY p.id, p.sku, p.name, p.category_id, c.name
)
SELECT
*,
CASE
WHEN total_revenue > 10000 THEN 'High Performer'
WHEN total_revenue > 5000 THEN 'Steady Seller'
WHEN total_revenue > 1000 THEN 'Moderate'
WHEN total_revenue > 0 THEN 'Low Volume'
ELSE 'No Sales'
END AS performance_tier
FROM sales_data;
-- 用户订单概览视图
CREATE OR REPLACE VIEW user_order_summary AS
SELECT
u.id AS user_id,
u.username,
u.email,
u.full_name,
COUNT(o.id) AS total_orders,
COUNT(CASE WHEN o.status = 'delivered' THEN 1 END) AS completed_orders,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
COALESCE(AVG(o.total_amount), 0) AS avg_order_value,
MAX(o.placed_at) AS last_order_date,
EXTRACT(DAY FROM CURRENT_TIMESTAMP - u.created_at) AS days_since_registration
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email, u.full_name, u.created_at;
-- -------------------- 存储过程 / 函数 --------------------
-- 获取产品推荐（基于购买历史）
CREATE OR REPLACE FUNCTION get_product_recommendations(
p_user_id INTEGER,
p_limit INTEGER DEFAULT 10
)
RETURNS TABLE (
product_id INTEGER,
sku VARCHAR,
product_name VARCHAR,
price DECIMAL,
category_name VARCHAR,
avg_rating NUMERIC,
recommendation_score NUMERIC
) AS $$
BEGIN
RETURN QUERY
WITH user_purchased_categories AS (
SELECT DISTINCT p.category_id
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = p_user_id
AND o.status IN ('delivered', 'shipped')
),
user_purchased_products AS (
SELECT DISTINCT oi.product_id
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = p_user_id
),
category_ranked_products AS (
SELECT
p.id,
p.sku,
p.name,
p.price,
c.name AS category_name,
COALESCE(ps.avg_rating, 0) AS avg_rating,
ps.total_quantity_sold,
ROW_NUMBER() OVER (
PARTITION BY p.category_id
ORDER BY ps.total_quantity_sold DESC, p.price ASC
) AS category_rank
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN product_sales_summary ps ON p.id = ps.id
WHERE p.is_active = TRUE
AND p.id NOT IN (SELECT product_id FROM user_purchased_products)
)
SELECT
crp.id,
crp.sku,
crp.name,
crp.price,
crp.category_name,
ROUND(crp.avg_rating::NUMERIC, 2),
ROUND(
(CASE WHEN crp.category_id IN (SELECT category_id FROM user_purchased_categories)
THEN 10 ELSE 0 END) +
(crp.total_quantity_sold * 0.01) +
(crp.avg_rating * 2) -
(crp.category_rank * 0.5),
2
) AS score
FROM category_ranked_products crp
WHERE crp.category_rank <= 5
ORDER BY score DESC
LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;
-- 计算订单总额的存储过程
CREATE OR REPLACE PROCEDURE calculate_order_totals(
IN p_order_id UUID,
OUT p_subtotal DECIMAL,
OUT p_tax DECIMAL,
OUT p_total DECIMAL
) AS $$
DECLARE
v_tax_rate DECIMAL := 0.08; -- 8% 税率
BEGIN
SELECT COALESCE(SUM(oi.total_price), 0)
INTO p_subtotal
FROM order_items oi
WHERE oi.order_id = p_order_id;
p_tax := ROUND(p_subtotal * v_tax_rate, 2);
p_total := p_subtotal + p_tax;
UPDATE orders
SET total_amount = p_total,
tax_amount = p_tax,
updated_at = CURRENT_TIMESTAMP
WHERE id = p_order_id;
RAISE NOTICE 'Order % totals: Subtotal=%, Tax=%, Total=%',
p_order_id, p_subtotal, p_tax, p_total;
END;
$$ LANGUAGE plpgsql;
-- -------------------- 复杂查询示例 --------------------
-- 1. 使用 CTE 和窗口函数进行销售分析
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', o.placed_at) AS month,
p.category_id,
c.name AS category_name,
COUNT(DISTINCT o.id) AS order_count,
SUM(oi.quantity) AS units_sold,
SUM(oi.total_price) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY DATE_TRUNC('month', o.placed_at), p.category_id, c.name
),
category_rankings AS (
SELECT
month,
category_name,
revenue,
units_sold,
RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS revenue_rank,
LAG(revenue) OVER (PARTITION BY category_name ORDER BY month) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue) OVER (PARTITION BY category_name ORDER BY month))
/ NULLIF(LAG(revenue) OVER (PARTITION BY category_name ORDER BY month), 0) * 100,
2
) AS growth_percentage
FROM monthly_sales
)
SELECT
TO_CHAR(month, 'YYYY-MM') AS month_display,
category_name,
TO_CHAR(revenue, 'FM$999,999,990.00') AS formatted_revenue,
units_sold,
revenue_rank,
TO_CHAR(prev_month_revenue, 'FM$999,999,990.00') AS prev_month_revenue,
growth_percentage || '%' AS growth
FROM category_rankings
WHERE month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 months')
ORDER BY month DESC, revenue_rank;
-- 2. 递归 CTE 查询分类树
WITH RECURSIVE category_tree AS (
-- 锚点：根分类
SELECT
id,
name,
slug,
parent_id,
0 AS level,
name AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- 递归：子分类
SELECT
c.id,
c.name,
c.slug,
c.parent_id,
ct.level + 1,
ct.path || ' > ' || c.name
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
id,
REPEAT(' ', level) || name AS indented_name,
level,
path,
(SELECT COUNT(*) FROM products WHERE category_id = ct.id) AS product_count
FROM category_tree ct
ORDER BY path;
-- 3. 用户购买行为分析（RFM）
WITH user_rfm AS (
SELECT
u.id AS user_id,
u.username,
u.email,
MAX(o.placed_at) AS last_purchase_date,
EXTRACT(DAY FROM CURRENT_TIMESTAMP - MAX(o.placed_at)) AS recency_days,
COUNT(DISTINCT o.id) AS frequency,
COALESCE(SUM(o.total_amount), 0) AS monetary
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.status IN ('delivered', 'shipped')
GROUP BY u.id, u.username, u.email
),
rfm_scores AS (
SELECT
*,
NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
FROM user_rfm
WHERE last_purchase_date IS NOT NULL
)
SELECT
username,
email,
TO_CHAR(last_purchase_date, 'YYYY-MM-DD') AS last_purchase,
recency_days::INT AS days_since_purchase,
frequency AS order_count,
TO_CHAR(monetary, 'FM$999,990.00') AS total_spent,
r_score,
f_score,
m_score,
CONCAT(r_score, f_score, m_score) AS rfm_cell,
CASE
WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Loyal'
WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Lost'
ELSE 'Others'
END AS segment
FROM rfm_scores
ORDER BY monetary DESC
LIMIT 20;
-- 4. 产品库存预警查询
SELECT
p.id,
p.sku,
p.name,
p.quantity AS current_stock,
p.min_quantity,
COALESCE(ps.total_quantity_sold, 0) AS sold_last_30_days,
ROUND(COALESCE(ps.total_quantity_sold, 0) / 30.0, 2) AS daily_sales_rate,
CASE
WHEN p.quantity <= 0 THEN 'Out of Stock'
WHEN p.quantity <= p.min_quantity THEN 'Low Stock - Reorder Now'
WHEN p.quantity <= p.min_quantity * 2 THEN 'Stock Running Low'
WHEN p.quantity > p.min_quantity * 5 THEN 'Overstocked'
ELSE 'Healthy'
END AS stock_status,
CASE
WHEN COALESCE(ps.total_quantity_sold, 0) > 0
THEN ROUND(p.quantity / (COALESCE(ps.total_quantity_sold, 1) / 30.0), 0)
ELSE 999
END AS days_of_inventory_left
FROM products p
LEFT JOIN product_sales_summary ps ON p.id = ps.id
WHERE p.is_active = TRUE
ORDER BY
CASE
WHEN p.quantity <= p.min_quantity THEN 0
WHEN p.quantity <= p.min_quantity * 2 THEN 1
ELSE 2
END,
days_of_inventory_left ASC;
-- 5. 使用 JSONB 函数查询
SELECT
p.id,
p.name,
p.metadata->>'brand' AS brand,
p.metadata->>'color' AS color,
p.metadata->'dimensions'->>'width' AS width,
p.metadata->'dimensions'->>'height' AS height,
p.metadata->'specifications' AS specifications,
jsonb_array_length(p.metadata->'images') AS image_count
FROM products p
WHERE p.metadata IS NOT NULL
AND p.metadata @> '{"brand": "TechCorp"}'
AND p.metadata->'dimensions'->>'width' IS NOT NULL
ORDER BY (p.metadata->>'release_date')::DATE DESC NULLS LAST;
-- -------------------- 事务示例 --------------------
BEGIN;
-- 创建新订单
INSERT INTO orders (user_id, status, shipping_address, payment_method)
VALUES (
1,
'pending',
'{"street": "123 Main St", "city": "Tech City", "zip": "94105"}'::JSONB,
'credit_card'
)
RETURNING id INTO v_order_id;
-- 添加订单项（触发器会自动扣减库存）
INSERT INTO order_items (order_id, product_id, sku, product_name, quantity, unit_price)
VALUES
(v_order_id, 101, 'SKU-101', 'Wireless Mouse', 2, 29.99),
(v_order_id, 205, 'SKU-205', 'Mechanical Keyboard', 1, 89.99);
-- 计算订单总额
CALL calculate_order_totals(v_order_id, v_subtotal, v_tax, v_total);
-- 更新订单状态
UPDATE orders SET status = 'confirmed' WHERE id = v_order_id;
COMMIT;
-- -------------------- 聚合与分组查询 --------------------
-- 按小时统计订单量
SELECT
EXTRACT(HOUR FROM placed_at) AS hour_of_day,
COUNT(*) AS order_count,
ROUND(AVG(total_amount), 2) AS avg_order_value,
SUM(total_amount) AS total_revenue
FROM orders
WHERE placed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY EXTRACT(HOUR FROM placed_at)
ORDER BY hour_of_day;
-- 产品标签分析
SELECT
UNNEST(tags) AS tag,
COUNT(*) AS product_count,
ROUND(AVG(price), 2) AS avg_price,
SUM(quantity) AS total_inventory
FROM products
WHERE is_active = TRUE
AND tags IS NOT NULL
GROUP BY tag
HAVING COUNT(*) > 1
ORDER BY product_count DESC;
-- 评论情感分析模拟
SELECT
p.id AS product_id,
p.name AS product_name,
COUNT(pr.id) AS total_reviews,
ROUND(AVG(pr.rating), 2) AS avg_rating,
COUNT(CASE WHEN pr.rating >= 4 THEN 1 END) AS positive_reviews,
COUNT(CASE WHEN pr.rating <= 2 THEN 1 END) AS negative_reviews,
ROUND(
100.0 * COUNT(CASE WHEN pr.rating >= 4 THEN 1 END) / NULLIF(COUNT(pr.id), 0),
1
) AS positive_percentage
FROM products p
LEFT JOIN product_reviews pr ON p.id = pr.product_id
GROUP BY p.id, p.name
HAVING COUNT(pr.id) >= 5
ORDER BY avg_rating DESC, total_reviews DESC;