2. 首页 > 国产数据库教程 > openGauss教程 > 正文

opengauss教程FG015-openGauss SQL基础与高级查询

教程整理：风哥教程  |  更新时间：2026-04-10  |  教程分类：openGauss教程  |  文档学习：19

目录大纲

Part01-基础概念与理论知识

1.1 SQL概述

SQL（Structured Query Language）是一种用于管理关系型数据库的标准化语言，了解SQL的概念和使用方法对于数据库的操作和管理至关重要。风哥教程参考opengauss官方文档，SQL具有以下特点：

  • 标准化：SQL是国际标准语言，被大多数关系型数据库支持
  • 功能强大：可以用于数据查询、插入、更新、删除等操作
  • 易学易用：语法简洁明了，易于学习和使用
  • 灵活多样：支持各种复杂的查询和操作

1.2 SQL分类

SQL可以分为以下几类：

  • DML（数据操作语言）：
    • SELECT：查询数据
    • INSERT：插入数据
    • UPDATE：更新数据
    • DELETE：删除数据
  • DDL（数据定义语言）：
    • CREATE：创建对象
    • ALTER：修改对象
    • DROP：删除对象
    • TRUNCATE：截断表
  • DCL（数据控制语言）：
    • GRANT：授予权限
    • REVOKE：撤销权限
  • TCL（事务控制语言）：
    • BEGIN：开始事务
    • COMMIT：提交事务
    • ROLLBACK：回滚事务
    • SAVEPOINT：设置保存点

1.3 SQL语法基础

SQL语法的基本要素包括：

  • 关键字：如SELECT、FROM、WHERE等
  • 标识符：如表名、列名等
  • 表达式：如算术表达式、逻辑表达式等
  • 语句：完整的SQL命令
  • 注释：用于说明SQL语句的含义

风哥提示：掌握SQL基础语法是数据库操作的基础，合理使用SQL可以提高数据库操作的效率和准确性。

Part02-生产环境规划与建议

2.1 SQL编写规范

生产环境SQL编写规范

  • 命名规范：
    • 使用小写字母
    • 使用下划线分隔单词
    • 避免使用保留字
  • 格式规范：
    • 关键字大写
    • 适当缩进
    • 一行一个关键字

      • 风哥提示：

    • 使用注释说明复杂语句
  • 性能规范：
    • 避免SELECT *
    • 使用WHERE子句过滤数据
    • 合理使用索引
    • 避免在WHERE子句中使用函数

2.2 查询性能优化

生产环境的SQL查询性能优化建议：

  • 索引优化：
    • 为经常查询的列创建索引
    • 避免过度索引
    • 使用复合索引
  • 查询优化：
    • 减少查询返回的列数
    • 使用LIMIT限制返回行数
    • 避免使用子查询，使用连接代替
    • 使用EXPLAIN分析执行计划

      • 学习交流加群风哥微信: itpux-com

  • 数据操作优化：
    • 使用批量操作
    • 避免在循环中执行SQL
    • 合理使用事务

2.3 最佳实践建议

SQL使用的最佳实践建议：

  • 安全性：
    • 使用参数化查询，避免SQL注入
    • 限制用户权限
    • 使用视图控制数据访问
  • 可维护性：
    • 使用存储过程和函数
    • 编写清晰的注释
    • 使用一致的命名规范
  • 可靠性：
    • 使用事务确保数据一致性
    • 进行错误处理
    • 定期备份数据

Part03-生产环境项目实施方案

3.1 基础SQL操作

学习交流加群风哥QQ113257174

# 选择数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT id, name, age FROM fgedu_employee WHERE age > 30;
“
Password for user opengauss:
id | name | age
—-+———-+—–
3 | Wang Wu | 35
4 | Zhao Liu | 40
(2 rows)
# 插入数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_employee (name, age, dept_id, salary) VALUES (‘Sun Qi’, 28, 1, 7500.00);
“
Password for user opengauss:
INSERT 0 1
# 更新数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “UPDATE fgedu_employee SET salary = salary * 1.1 WHERE dept_id = 1;
“
Password for user opengauss:
UPDATE 3
# 删除数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “DELETE FROM fgedu_employee WHERE id = 5;
“
Password for user opengauss:
DELETE 1

3.2 高级查询技巧

# 聚合函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT dept_id, COUNT(*) as employee_count, AVG(salary) as avg_salary FROM fgedu_employee GROUP BY dept_id;
“
Password for user opengauss:
dept_id | employee_count | avg_salary
———+—————-+———————
1 | 2 | 8800.00000000000000
2 | 1 | 7000.00000000000000
3 | 1 | 9000.00000000000000
(3 rows)
# 连接查询
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT e.id, e.name, d.name as department, e.salary FROM fgedu_employee e JOIN fgedu_department d ON e.dept_id = d.id;
“
Password for user opengauss:
id | name | department | salary
—-+———-+————+——–
1 | Zhang San | IT | 8800.00
更多视频教程www.fgedu.net.cn
2 | Li Si | IT | 6600.00
3 | Wang Wu | HR | 7000.00
4 | Zhao Liu | Finance | 9000.00
(4 rows)
# 子查询
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT * FROM fgedu_employee WHERE salary > (SELECT AVG(salary) FROM fgedu_employee);
“
Password for user opengauss:
id | name | age | dept_id | hire_date | salary
—-+———-+—–+———+————-+——–
1 | Zhang San | 30 | 1 | 2024-01-01 | 8800.00
4 | Zhao Liu | 40 | 3 | 2024-01-01 | 9000.00
(2 rows)

3.3 事务与并发控制

# 开始事务
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss
Password for user opengauss:
gsql ((openGauss 5.0.0 build 12345) compiled at 2024-01-01 00:00:00)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.
fgedudb=# BEGIN;
BEGIN
fgedudb=# UPDATE fgedu_employee SET salary = salary + 500 WHERE id = 1;

UPDATE 1
fgedudb=# UPDATE fgedu_employee SET salary = salary + 500 WHERE id = 2;

UPDATE 1
fgedudb=# COMMIT;

COMMIT
# 事务回滚
fgedudb=# BEGIN;
BEGIN
fgedudb=# UPDATE fgedu_employee SET salary = salary – 1000 WHERE id = 1;

UPDATE 1
fgedudb=# ROLLBACK;

ROLLBACK
fgedudb=# SELECT salary FROM fgedu_employee WHERE id = 1;

salary
——–
更多学习教程公众号风哥教程itpux_com
9300.00
(1 row)

Part04-生产案例与实战讲解

4.1 基础SQL实战

# 创建测试表
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE TABLE fgedu_product (id serial PRIMARY KEY, name varchar(100) NOT NULL, price decimal(10,2) NOT NULL, stock int NOT NULL, category varchar(50));
“
Password for user opengauss:
CREATE TABLE
# 插入测试数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_product (name, price, stock, category) VALUES (‘Laptop’, 5999.99, 50, ‘Electronics’), (‘Smartphone’, 3999.99, 100, ‘Electronics’), (‘Tablet’, 2999.99, 75, ‘Electronics’), (‘Chair’, 499.99, 30, ‘Furniture’), (‘Desk’, 899.99, 20, ‘Furniture’);
“
Password for user opengauss:
INSERT 0 5
# 查询所有产品
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT * FROM fgedu_product;
“
Password for user opengauss:
id | name | price | stock | category
—-+————+———-+——-+————-
1 | Laptop | 5999.99 | 50 | Electronics
2 | Smartphone | 3999.99 | 100 | Electronics
3 | Tablet | 2999.99 | 75 | Electronics
4 | Chair | 499.99 | 30 | Furniture
5 | Desk | 899.99 | 20 | Furniture
(5 rows)
# 更新产品价格
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “UPDATE fgedu_product SET price = price * 0.9 WHERE category = ‘Electronics’;
“
Password for user opengauss:
from DB视频:www.itpux.com
UPDATE 3
# 查询更新后的产品
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT * FROM fgedu_product;
“
Password for user opengauss:
id | name | price | stock | category
—-+————+———-+——-+————-
1 | Laptop | 5399.99 | 50 | Electronics
2 | Smartphone | 3599.99 | 100 | Electronics
3 | Tablet | 2699.99 | 75 | Electronics
4 | Chair | 499.99 | 30 | Furniture
5 | Desk | 899.99 | 20 | Furniture
(5 rows)

4.2 高级查询实战

# 按类别分组统计
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT category, COUNT(*) as product_count, AVG(price) as avg_price, SUM(stock) as total_stock FROM fgedu_product GROUP BY category;
“
Password for user opengauss:
category | product_count | avg_price | total_stock
————-+—————+———————+————-
Electronics | 3 | 3899.99000000000000 | 225
Furniture | 2 | 699.99000000000000 | 50
(2 rows)
# 窗口函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT id, name, price, category, RANK() OVER (PARTITION BY category ORDER BY price DESC) as rank FROM fgedu_product;
“
Password for user opengauss:
id | name | price | category | rank
—-+————+———-+————-+——
1 | Laptop | 5399.99 | Electronics | 1
2 | Smartphone | 3599.99 | Electronics | 2
3 | Tablet | 2699.99 | Electronics | 3
5 | Desk | 899.99 | Furniture | 1
4 | Chair | 499.99 | Furniture | 2
(5 rows)
# 复杂连接查询
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE TABLE fgedu_order (id serial PRIMARY KEY, customer_name varchar(100), product_id int REFERENCES fgedu_product(id), quantity int, order_date date DEFAULT CURRENT_DATE);
“
Password for user opengauss:
CREATE TABLE
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_order (customer_name, product_id, quantity) VALUES (‘Alice’, 1, 2), (‘Bob’, 2, 1), (‘Charlie’, 3, 3), (‘David’, 4, 2), (‘Eve’, 5, 1);
“
Password for user opengauss:
INSERT 0 5
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT o.id, o.customer_name, p.name as product_name, p.price, o.quantity, (p.price * o.quantity) as total_amount FROM fgedu_order o JOIN fgedu_product p ON o.product_id = p.id;
“
Password for user opengauss:
id | customer_name | product_name | price | quantity | total_amount
—-+—————+————–+———-+———-+————–
1 | Alice | Laptop | 5399.99 | 2 | 10799.98
2 | Bob | Smartphone | 3599.99 | 1 | 3599.99
3 | Charlie | Tablet | 2699.99 | 3 | 8099.97
4 | David | Chair | 499.99 | 2 | 999.98
5 | Eve | Desk | 899.99 | 1 | 899.99
(5 rows)

4.3 性能优化实战

# 分析执行计划
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employee WHERE salary > 8000;
“
Password for user opengauss:
QUERY PLAN
———————————————————————————————-
Index Scan using idx_fgedu_employee_salary on fgedu_employee (cost=0.29..26.34 rows=2500 width=37)
Index Cond: (salary > 8000::numeric)
Execution Time: 0.123 ms
(3 rows)
# 优化查询
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE SELECT id, name, salary FROM fgedu_employee WHERE salary > 8000;
“
Password for user opengauss:
QUERY PLAN
———————————————————————————————-
Index Scan using idx_fgedu_employee_salary on fgedu_employee (cost=0.29..26.34 rows=2500 width=21)
Index Cond: (salary > 8000::numeric)
Execution Time: 0.098 ms
(3 rows)
# 创建复合索引
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE INDEX idx_fgedu_employee_dept_salary ON fgedu_employee(dept_id, salary);
“
Password for user opengauss:
CREATE INDEX
# 测试复合索引
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employee WHERE dept_id = 1 AND salary > 8000;
“
Password for user opengauss:
QUERY PLAN
———————————————————————————————-
Index Scan using idx_fgedu_employee_dept_salary on fgedu_employee (cost=0.29..8.30 rows=1 width=37)
Index Cond: ((dept_id = 1) AND (salary > 8000::numeric))
Execution Time: 0.075 ms
(3 rows)

Part05-风哥经验总结与分享

5.1 SQL编写技巧

  • SQL编写规范：
    • 使用一致的命名规范
    • 适当缩进和换行，提高可读性
    • 使用注释说明复杂语句
    • 避免使用SELECT *，只选择需要的列
  • 查询优化技巧：
    • 使用WHERE子句过滤数据
    • 合理使用索引
    • 避免在WHERE子句中使用函数
    • 使用连接代替子查询
  • 数据操作技巧：
    • 使用批量操作提高性能
    • 合理使用事务确保数据一致性
    • 使用参数化查询避免SQL注入
    • 定期清理无用数据

5.2 查询性能优化要点

  • 索引优化：
    • 为经常查询的列创建索引
    • 使用复合索引提高多列查询性能
    • 定期重建索引保持索引效率
    • 避免过度索引影响写操作性能
  • 查询计划优化：
    • 使用EXPLAIN分析执行计划
    • 优化查询语句结构
    • 避免全表扫描
    • 使用覆盖索引减少回表操作
  • 数据库参数优化：
    • 调整shared_buffers参数
    • 优化work_mem参数
    • 调整maintenance_work_mem参数
    • 合理设置random_page_cost参数

5.3 常见问题与解决方案

常见SQL问题及解决方法

  • 问题1：查询性能慢
    • 症状：查询执行时间长
    • 解决方案：分析执行计划，创建适当的索引，优化查询语句
  • 问题2：SQL注入
    • 症状：恶意用户通过输入恶意SQL代码
    • 解决方案：使用参数化查询，限制用户输入
  • 问题3：死锁
    • 症状：事务执行时出现死锁
    • 解决方案：优化事务设计，减少事务持有锁的时间，使用合理的锁定顺序
  • 问题4：数据一致性问题
    • 症状：数据不一致
    • 解决方案：使用事务，确保数据操作的原子性、一致性、隔离性和持久性

风哥提示：SQL是数据库操作的核心，掌握SQL的使用技巧和优化方法对于数据库性能和数据管理至关重要。在生产环境中，应该编写高效、安全的SQL语句，定期监控和优化查询性能，确保数据库的稳定运行和高性能。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息