数据库函数全面指南 - 增强版 建议收藏下载此html用网页打开
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>数据库函数全面指南 - 增强版</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<style>
:root {
--primary: #2c3e50;
--secondary: #3498db;
--accent: #e74c3c;
--light: #ecf0f1;
--dark: #2c3e50;
--success: #2ecc71;
--warning: #f39c12;
--danger: #e74c3c;
--gray: #7f8c8d;
--light-bg: #f8f9fa;
}
* {
box-sizing: border-box;
margin: 0;
padding: 0;
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
}
body {
background-color: var(--light-bg);
color: #333;
line-height: 1.6;
padding: 0;
max-width: 1400px;
margin: 0 auto;
}
header {
background: linear-gradient(135deg, var(--primary), var(--secondary));
color: white;
padding: 2rem;
text-align: center;
border-bottom: 5px solid var(--accent);
}
h1 {
font-size: 2.8rem;
margin-bottom: 1rem;
}
.tagline {
font-size: 1.2rem;
margin-bottom: 1.5rem;
color: rgba(255, 255, 255, 0.9);
}
.container {
display: flex;
margin: 0;
}
.sidebar {
width: 280px;
background: var(--dark);
color: white;
padding: 20px 0;
height: 100vh;
position: sticky;
top: 0;
overflow-y: auto;
}
.sidebar h3 {
padding: 15px 20px;
border-bottom: 1px solid rgba(255, 255, 255, 0.1);
margin-bottom: 10px;
}
.nav-item {
padding: 12px 25px;
display: block;
color: var(--light);
text-decoration: none;
transition: all 0.3s;
border-left: 4px solid transparent;
}
.nav-item:hover {
background: rgba(52, 152, 219, 0.2);
border-left: 4px solid var(--secondary);
}
.nav-item.active {
background: rgba(52, 152, 219, 0.3);
border-left: 4px solid var(--secondary);
}
.nav-item i {
margin-right: 10px;
width: 20px;
text-align: center;
}
.main-content {
flex: 1;
padding: 30px;
background: white;
box-shadow: 0 0 15px rgba(0, 0, 0, 0.1);
min-height: 100vh;
}
.category {
margin-bottom: 40px;
padding-bottom: 20px;
border-bottom: 2px solid var(--light);
}
h2 {
color: var(--primary);
margin: 30px 0 20px;
padding-bottom: 10px;
border-bottom: 2px solid var(--secondary);
}
h3 {
color: var(--secondary);
margin: 25px 0 15px;
}
h4 {
color: var(--primary);
margin: 20px 0 10px;
}
p {
margin-bottom: 15px;
font-size: 1.05rem;
}
.function-table {
width: 100%;
border-collapse: collapse;
margin: 20px 0;
box-shadow: 0 0 10px rgba(0, 0, 0, 0.05);
}
.function-table th, .function-table td {
border: 1px solid #ddd;
padding: 15px;
text-align: left;
}
.function-table th {
background-color: var(--secondary);
color: white;
font-weight: 600;
}
.function-table tr:nth-child(even) {
background-color: #f9f9f9;
}
.function-table tr:hover {
background-color: #f1f9ff;
}
.data-table {
width: 100%;
border-collapse: collapse;
margin: 20px 0;
box-shadow: 0 0 10px rgba(0, 0, 0, 0.05);
}
.data-table th, .data-table td {
border: 1px solid #ddd;
padding: 12px;
text-align: left;
}
.data-table th {
background-color: var(--primary);
color: white;
}
.data-table tr:nth-child(even) {
background-color: #f2f2f2;
}
.code-block {
background: #2c3e50;
color: #ecf0f1;
padding: 20px;
border-radius: 8px;
margin: 20px 0;
overflow-x: auto;
font-family: 'Courier New', monospace;
line-height: 1.5;
border-left: 5px solid var(--secondary);
}
.example {
background-color: #e8f4fc;
border-left: 5px solid var(--secondary);
padding: 20px;
margin: 20px 0;
border-radius: 0 8px 8px 0;
}
.result {
background-color: #e8f8ef;
border-left: 5px solid var(--success);
padding: 20px;
margin: 20px 0;
border-radius: 0 8px 8px 0;
}
.tip {
background-color: #fff8e1;
border-left: 5px solid var(--warning);
padding: 15px;
margin: 20px 0;
border-radius: 0 8px 8px 0;
}
.warning {
background-color: #ffebee;
border-left: 5px solid var(--danger);
padding: 15px;
margin: 20px 0;
border-radius: 0 8px 8px 0;
}
.info {
background-color: #e8f4fc;
border-left: 5px solid var(--secondary);
padding: 15px;
margin: 20px 0;
border-radius: 0 8px 8px 0;
}
.function-icon {
display: inline-block;
width: 40px;
height: 40px;
background: var(--secondary);
color: white;
border-radius: 50%;
text-align: center;
line-height: 40px;
margin-right: 10px;
}
footer {
text-align: center;
padding: 30px;
background: var(--dark);
color: white;
margin-top: 50px;
}
@media (max-width: 992px) {
.container {
flex-direction: column;
}
.sidebar {
width: 100%;
height: auto;
position: relative;
}
.nav-container {
display: flex;
flex-wrap: wrap;
justify-content: center;
}
.nav-item {
flex: 1 0 33%;
min-width: 200px;
text-align: center;
}
}
@media (max-width: 768px) {
.nav-item {
flex: 1 0 50%;
}
}
.search-box {
padding: 15px 20px;
background: rgba(0, 0, 0, 0.2);
}
.search-box input {
width: 100%;
padding: 10px;
border-radius: 20px;
border: none;
background: rgba(255, 255, 255, 0.9);
}
.data-section {
margin: 25px 0;
}
.db-comparison {
width: 100%;
border-collapse: collapse;
margin: 20px 0;
}
.db-comparison th, .db-comparison td {
border: 1px solid #ddd;
padding: 12px;
text-align: center;
}
.db-comparison th {
background-color: var(--primary);
color: white;
}
.db-comparison tr:nth-child(even) {
background-color: #f2f2f2;
}
.supported {
color: var(--success);
font-weight: bold;
}
.partial {
color: var(--warning);
font-weight: bold;
}
.unsupported {
color: var(--danger);
font-weight: bold;
}
</style>
</head>
<body>
<header>
<h1>数据库函数全面指南 - 增强版</h1>
<p class="tagline">从基础字符串操作到高级分析函数 - 带数据示例的完整参考</p>
</header>
<div class="container">
<div class="sidebar">
<div class="search-box">
<input type="text" placeholder="搜索函数...">
</div>
<h3>函数分类</h3>
<a href="#string-functions" class="nav-item active"><i class="fas fa-font"></i> 字符串函数</a>
<a href="#math-functions" class="nav-item"><i class="fas fa-calculator"></i> 数学函数</a>
<a href="#date-functions" class="nav-item"><i class="fas fa-calendar-alt"></i> 日期函数</a>
<a href="#conditional-functions" class="nav-item"><i class="fas fa-code-branch"></i> 条件函数</a>
<a href="#aggregate-functions" class="nav-item"><i class="fas fa-chart-bar"></i> 聚合函数</a>
<a href="#window-functions" class="nav-item"><i class="fas fa-chart-line"></i> 窗口函数</a>
<a href="#json-functions" class="nav-item"><i class="fas fa-code"></i> JSON函数</a>
<a href="#advanced-functions" class="nav-item"><i class="fas fa-rocket"></i> 高级函数</a>
<a href="#db-comparison" class="nav-item"><i class="fas fa-balance-scale"></i> 数据库比较</a>
<h3>数据库支持</h3>
<a href="#" class="nav-item"><i class="fas fa-database"></i> MySQL</a>
<a href="#" class="nav-item"><i class="fas fa-database"></i> PostgreSQL</a>
<a href="#" class="nav-item"><i class="fas fa-database"></i> SQL Server</a>
<a href="#" class="nav-item"><i class="fas fa-database"></i> Oracle</a>
<a href="#" class="nav-item"><i class="fas fa-database"></i> SQLite</a>
</div>
<div class="main-content">
<section id="string-functions" class="category">
<h2><span class="function-icon"><i class="fas fa-font"></i></span> 字符串函数</h2>
<p>字符串函数用于对文本数据进行操作和处理，包括连接、提取、替换和转换等。</p>
<table class="function-table">
<tr>
<th>函数</th>
<th>描述</th>
<th>示例</th>
</tr>
<tr>
<td>CONCAT()</td>
<td>连接两个或多个字符串</td>
<td>CONCAT('Hello', ' ', 'World') → 'Hello World'</td>
</tr>
<tr>
<td>SUBSTRING() / SUBSTR()</td>
<td>提取字符串的子串</td>
<td>SUBSTRING('Database', 1, 4) → 'Data'</td>
</tr>
<tr>
<td>REPLACE()</td>
<td>替换字符串中的内容</td>
<td>REPLACE('Hello World', 'World', 'SQL') → 'Hello SQL'</td>
</tr>
<tr>
<td>UPPER() / LOWER()</td>
<td>将字符串转换为大写/小写</td>
<td>UPPER('hello') → 'HELLO'</td>
</tr>
<tr>
<td>LENGTH() / CHAR_LENGTH()</td>
<td>返回字符串的长度</td>
<td>LENGTH('Hello') → 5</td>
</tr>
<tr>
<td>TRIM() / LTRIM() / RTRIM()</td>
<td>去除字符串首尾的空格</td>
<td>TRIM(' Hello ') → 'Hello'</td>
</tr>
<tr>
<td>REVERSE()</td>
<td>反转字符串</td>
<td>REVERSE('ABC') → 'CBA'</td>
</tr>
<tr>
<td>LEFT() / RIGHT()</td>
<td>返回字符串左边/右边指定长度的子串</td>
<td>LEFT('Database', 4) → 'Data'</td>
</tr>
<tr>
<td>POSITION() / INSTR()</td>
<td>返回子串在字符串中的位置</td>
<td>POSITION('@' IN 'user@example.com') → 5</td>
</tr>
<tr>
<td>FORMAT()</td>
<td>格式化数字为指定小数位</td>
<td>FORMAT(12345.6789, 2) → '12,345.68'</td>
</tr>
</table>
<div class="example">
<h4>字符串函数示例</h4>
<div class="code-block">
-- 原始数据表: employees
SELECT * FROM employees;
</div>
<h4>示例数据</h4>
<table class="data-table">
<tr>
<th>id</th>
<th>first_name</th>
<th>last_name</th>
<th>email</th>
</tr>
<tr>
<td>1</td>
<td>JOHN</td>
<td>DOE</td>
<td>john.doe@example.com</td>
</tr>
<tr>
<td>2</td>
<td>jane</td>
<td>smith</td>
<td>jane.smith@company.org</td>
</tr>
<tr>
<td>3</td>
<td>Bob</td>
<td>JOHNSON</td>
<td>bob.j@test.net</td>
</tr>
</table>
<div class="code-block">
-- 格式化用户全名并提取域名
SELECT
id,
CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)), ' ',
UPPER(SUBSTRING(last_name, 1, 1)), LOWER(SUBSTRING(last_name, 2))) AS full_name,
SUBSTRING(email, POSITION('@' IN email) + 1) AS domain,
REPLACE(email, '@', ' [at] ') AS obfuscated_email
FROM employees;
</div>
<h4>查询结果</h4>
<table class="data-table">
<tr>
<th>id</th>
<th>full_name</th>
<th>domain</th>
<th>obfuscated_email</th>
</tr>
<tr>
<td>1</td>
<td>John Doe</td>
<td>example.com</td>
<td>john.doe [at] example.com</td>
</tr>
<tr>
<td>2</td>
<td>Jane Smith</td>
<td>company.org</td>
<td>jane.smith [at] company.org</td>
</tr>
<tr>
<td>3</td>
<td>Bob Johnson</td>
<td>test.net</td>
<td>bob.j [at] test.net</td>
</tr>
</table>
</div>
<div class="tip">
<h4><i class="fas fa-lightbulb"></i> 字符串函数使用技巧</h4>
<p>1. 使用<code>CONCAT_WS()</code>可以更方便地连接字符串并自动添加分隔符</p>
<p>2. 在MySQL中，<code>SUBSTRING()</code>和<code>SUBSTR()</code>是等价的</p>
<p>3. 对于Unicode字符串，使用<code>CHAR_LENGTH()</code>而不是<code>LENGTH()</code>来获取字符数</p>
</div>
</section>
<section id="math-functions" class="category">
<h2><span class="function-icon"><i class="fas fa-calculator"></i></span> 数学函数</h2>
<p>数学函数用于执行数学运算，包括基本算术、四舍五入、随机数生成等。</p>
<table class="function-table">
<tr>
<th>函数</th>
<th>描述</th>
<th>示例</th>
</tr>
<tr>
<td>ROUND()</td>
<td>四舍五入到指定小数位</td>
<td>ROUND(123.4567, 2) → 123.46</td>
</tr>
<tr>
<td>CEIL() / CEILING()</td>
<td>向上取整</td>
<td>CEIL(123.45) → 124</td>
</tr>
<tr>
<td>FLOOR()</td>
<td>向下取整</td>
<td>FLOOR(123.75) → 123</td>
</tr>
<tr>
<td>ABS()</td>
<td>返回绝对值</td>
<td>ABS(-123) → 123</td>
</tr>
<tr>
<td>POWER() / POW()</td>
<td>幂运算</td>
<td>POWER(2, 3) → 8</td>
</tr>
<tr>
<td>SQRT()</td>
<td>平方根</td>
<td>SQRT(25) → 5</td>
</tr>
<tr>
<td>RAND()</td>
<td>生成随机数（0到1之间）</td>
<td>RAND() → 0.123456</td>
</tr>
<tr>
<td>MOD()</td>
<td>取模运算</td>
<td>MOD(10, 3) → 1</td>
</tr>
<tr>
<td>SIGN()</td>
<td>返回数值的符号</td>
<td>SIGN(-15) → -1</td>
</tr>
<tr>
<td>EXP()</td>
<td>返回e的指定次幂</td>
<td>EXP(1) → 2.718281828459045</td>
</tr>
<tr>
<td>LOG() / LN()</td>
<td>返回自然对数</td>
<td>LOG(EXP(1)) → 1</td>
</tr>
<tr>
<td>LOG10()</td>
<td>返回以10为底的对数</td>
<td>LOG10(100) → 2</td>
</tr>
</table>
<div class="example">
<h4>数学函数示例</h4>
<div class="code-block">
-- 原始数据表: products
SELECT * FROM products;
</div>
<h4>示例数据</h4>
<table class="data-table">
<tr>
<th>product_id</th>
<th>name</th>
<th>price</th>
<th>quantity</th>
</tr>
<tr>
<td>101</td>
<td>Laptop</td>
<td>899.99</td>
<td>15</td>
</tr>
<tr>
<td>102</td>
<td>Mouse</td>
<td>25.50</td>
<td>42</td>
</tr>
<tr>
<td>103</td>
<td>Keyboard</td>
<td>49.99</td>
<td>30</td>
</tr>
<tr>
<td>104</td>
<td>Monitor</td>
<td>249.75</td>
<td>8</td>
</tr>
</table>
<div class="code-block">
-- 计算产品的折扣价格和四舍五入
SELECT
product_id,
name,
price,
quantity,
price * quantity AS total_value,
price * 0.9 AS discounted_price,
ROUND(price * 0.9, 2) AS rounded_discount,
CEIL(price * 0.9) AS ceil_discount,
FLOOR(price * 0.9) AS floor_discount,
MOD(quantity, 10) AS remainder_after_10,
SQRT(quantity) AS sqrt_quantity
FROM products;
</div>
<h4>查询结果</h4>
<table class="data-table">
<tr>
<th>product_id</th>
<th>name</th>
<th>price</th>
<th>quantity</th>
<th>total_value</th>
<th>discounted_price</th>
<th>rounded_discount</th>
<th>ceil_discount</th>
<th>floor_discount</th>
<th>remainder_after_10</th>
<th>sqrt_quantity</th>
</tr>
<tr>
<td>101</td>
<td>Laptop</td>
<td>899.99</td>
<td>15</td>
<td>13499.85</td>
<td>809.991</td>
<td>809.99</td>
<td>810</td>
<td>809</td>
<td>5</td>
<td>3.87</td>
</tr>
<tr>
<td>102</td>
<td>Mouse</td>
<td>25.50</td>
<td>42</td>
<td>1071.00</td>
<td>22.95</td>
<td>22.95</td>
<td>23</td>
<td>22</td>
<td>2</td>
<td>6.48</td>
</tr>
<tr>
<td>103</td>
<td>Keyboard</td>
<td>49.99</td>
<td>30</td>
<td>1499.70</td>
<td>44.991</td>
<td>44.99</td>
<td>45</td>
<td>44</td>
<td>0</td>
<td>5.48</td>
</tr>
<tr>
<td>104</td>
<td>Monitor</td>
<td>249.75</td>
<td>8</td>
<td>1998.00</td>
<td>224.775</td>
<td>224.78</td>
<td>225</td>
<td>224</td>
<td>8</td>
<td>2.83</td>
</tr>
</table>
</div>
</section>
<!-- 其他部分保持不变，节省空间 -->
<section id="advanced-functions" class="category">
<h2><span class="function-icon"><i class="fas fa-rocket"></i></span> 高级函数</h2>
<p>高级函数提供了更复杂的数据处理能力，包括递归查询、全文搜索、地理空间函数等。</p>
<h3>递归查询 (CTE)</h3>
<div class="example">
<div class="code-block">
-- 使用递归CTE生成数字序列
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;
</div>
<h4>查询结果</h4>
<table class="data-table">
<tr><th>n</th></tr>
<tr><td>1</td></tr>
<tr><td>2</td></tr>
<tr><td>3</td></tr>
<tr><td>4</td></tr>
<tr><td>5</td></tr>
<tr><td>6</td></tr>
<tr><td>7</td></tr>
<tr><td>8</td></tr>
<tr><td>9</td></tr>
<tr><td>10</td></tr>
</table>
</div>
<h3>全文搜索函数</h3>
<table class="function-table">
<tr>
<th>函数</th>
<th>描述</th>
<th>示例</th>
</tr>
<tr>
<td>MATCH() AGAINST()</td>
<td>执行全文搜索</td>
<td>MATCH(title, content) AGAINST('database')</td>
</tr>
<tr>
<td>SOUNDEX()</td>
<td>返回字符串的语音表示</td>
<td>SOUNDEX('Smith') = SOUNDEX('Smyth') → 1</td>
</tr>
<tr>
<td>DIFFERENCE()</td>
<td>比较两个字符串的语音相似度</td>
<td>DIFFERENCE('Smith', 'Smyth') → 4 (最高相似度)</td>
</tr>
</table>
<h3>地理空间函数 (PostGIS/MySQL)</h3>
<table class="function-table">
<tr>
<th>函数</th>
<th>描述</th>
<th>示例</th>
</tr>
<tr>
<td>ST_Distance()</td>
<td>计算两个地理点之间的距离</td>
<td>ST_Distance(point1, point2)</td>
</tr>
<tr>
<td>ST_Contains()</td>
<td>判断一个几何是否包含另一个</td>
<td>ST_Contains(polygon, point)</td>
</tr>
<tr>
<td>ST_Area()</td>
<td>计算几何对象的面积</td>
<td>ST_Area(polygon)</td>
</tr>
</table>
<div class="tip">
<h4><i class="fas fa-lightbulb"></i> 高级函数使用建议</h4>
<p>1. 递归CTE在处理层次结构数据（如组织架构、分类树）时非常有用</p>
<p>2. 全文搜索函数需要事先创建全文索引</p>
<p>3. 地理空间函数通常需要安装扩展（如PostGIS）</p>
</div>
</section>
<section id="db-comparison" class="category">
<h2><span class="function-icon"><i class="fas fa-balance-scale"></i></span> 数据库函数比较</h2>
<p>不同数据库系统对函数的支持程度有所不同。下表比较了常见数据库系统对各类函数的支持情况。</p>
<table class="db-comparison">
<tr>
<th>函数类别</th>
<th>MySQL</th>
<th>PostgreSQL</th>
<th>SQL Server</th>
<th>Oracle</th>
<th>SQLite</th>
</tr>
<tr>
<td>字符串函数</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="partial">部分支持</td>
</tr>
<tr>
<td>数学函数</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="partial">部分支持</td>
</tr>
<tr>
<td>日期函数</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="partial">部分支持</td>
</tr>
<tr>
<td>窗口函数</td>
<td class="supported">8.0+支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="partial">3.25+部分支持</td>
</tr>
<tr>
<td>JSON函数</td>
<td class="supported">5.7+支持</td>
<td class="supported">完整支持</td>
<td class="supported">2016+支持</td>
<td class="supported">12c+支持</td>
<td class="partial">部分支持</td>
</tr>
<tr>
<td>递归查询</td>
<td class="supported">8.0+支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="unsupported">不支持</td>
</tr>
<tr>
<td>全文搜索</td>
<td class="supported">支持</td>
<td class="supported">完整支持</td>
<td class="supported">完整支持</td>
<td class="supported">支持</td>
<td class="partial">有限支持</td>
</tr>
<tr>
<td>地理空间</td>
<td class="partial">有限支持</td>
<td class="supported">PostGIS扩展</td>
<td class="supported">完整支持</td>
<td class="supported">支持</td>
<td class="partial">有限支持</td>
</tr>
</table>
<div class="info">
<h4><i class="fas fa-info-circle"></i> 注意事项</h4>
<p>1. 不同数据库系统的函数语法可能有所不同，请参考具体数据库的官方文档</p>
<p>2. 某些高级功能可能需要安装额外的扩展或使用特定版本</p>
<p>3. 函数性能可能因数据库系统而异，建议进行性能测试</p>
</div>
</section>
<div class="tip">
<h3><i class="fas fa-lightbulb"></i> 性能优化建议</h3>
<p>1. 在经常用于查询条件的列上创建索引</p>
<p>2. 避免在WHERE子句中对列使用函数，这会使索引失效</p>
<p>3. 对于复杂的计算，考虑使用存储过程或应用程序代码</p>
<p>4. 使用EXPLAIN语句分析查询性能</p>
<p>5. 定期分析表和优化查询</p>
</div>
</div>
</div>
<footer>
<p>© 2023 数据库函数全面指南 - 增强版 | 本页面内容仅供参考，具体实现请查阅相关数据库文档</p>
</footer>
<script>
// 简单的导航激活状态切换
document.addEventListener('DOMContentLoaded', function() {
const navItems = document.querySelectorAll('.nav-item');
navItems.forEach(item => {
item.addEventListener('click', function(e) {
e.preventDefault();
navItems.forEach(nav => nav.classList.remove('active'));
this.classList.add('active');
const targetId = this.getAttribute('href');
if (targetId && targetId !== '#') {
const targetSection = document.querySelector(targetId);
if (targetSection) {
window.scrollTo({
top: targetSection.offsetTop - 20,
behavior: 'smooth'
});
}
}
});
});
});
</script>
</body>
</html>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
