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

opengauss教程FG026-SQL语言与开发基础

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

目录大纲

Part01-基础概念与理论知识

1.1 SQL语言概述

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

  • 标准化：SQL是一种标准化的语言，被大多数关系型数据库系统支持
  • 简单易学：SQL语法简单明了，易于学习和使用
  • 功能强大：SQL可以完成从简单查询到复杂数据操作的各种任务
  • 通用性：SQL可以在不同的数据库系统之间移植

1.2 SQL语言分类

SQL语言主要分为以下几类：

  • DDL（Data Definition Language）：
    • 用于定义数据库对象，如创建表、修改表结构、删除表等
    • 主要命令：CREATE、ALTER、DROP、TRUNCATE等
  • DML（Data Manipulation Language）：
    • 用于操作数据库中的数据，如插入、更新、删除、查询数据等
    • 主要命令：INSERT、UPDATE、DELETE、SELECT等
  • DCL（Data Control Language）：
    • 用于控制数据库的访问权限和事务管理
    • 主要命令：GRANT、REVOKE、COMMIT、ROLLBACK等
  • TCL（Transaction Control Language）：
    • 用于管理数据库事务
    • 主要命令：COMMIT、ROLLBACK、SAVEPOINT等

1.3 SQL语言特点

SQL语言的主要特点：

  • 声明性：SQL是一种声明性语言，用户只需要告诉数据库做什么，而不需要告诉它怎么做
  • 面向集合：SQL操作的是数据集，而不是单个数据
  • 非过程性：SQL不需要指定操作的具体步骤
  • 标准化：SQL是一种标准化的语言，被大多数关系型数据库系统支持

风哥提示：SQL是数据库开发的基础，掌握SQL语言对于数据库管理和开发至关重要。

Part02-生产环境规划与建议

2.1 SQL开发规范

生产环境SQL开发规范建议

  • 命名规范：
    • 表名：使用小写字母，单词之间用下划线分隔，如fgedu_employee
    • 列名：使用小写字母，单词之间用下划线分隔，如employee_id
    • 索引名：使用idx_表名_列名的格式，如idx_fgedu_employee_name
    • 存储过程/函数名：使用proc_或func_前缀，如proc_update_employee
  • 代码风格：
    • 关键字大写，如SELECT、FROM、WHERE等
    • 缩进一致，提高代码可读性
    • 使用注释，解释复杂的SQL语句
    • 每行只写一个子句，如SELECT、FROM、WHERE等
  • 性能规范：
    • 避免使用SELECT *，只选择需要的列

      • 风哥提示：

    • 使用索引，提高查询性能
    • 避免在WHERE子句中使用函数，影响索引使用
    • 合理使用JOIN，避免笛卡尔积
  • 安全规范：
    • 使用参数化查询，防止SQL注入
    • 限制用户权限，遵循最小权限原则
    • 避免在SQL语句中硬编码敏感信息

2.2 SQL性能优化建议

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

  • 索引优化：
    • 为经常查询的列创建索引
    • 为JOIN操作的列创建索引
    • 避免创建过多索引，影响插入和更新性能
    • 定期重建索引，保持索引的有效性
  • 查询优化：
    • 使用EXPLAIN分析查询计划
    • 避免使用SELECT *，只选择需要的列

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

    • 使用LIMIT限制返回行数
    • 避免在WHERE子句中使用函数
  • 数据结构优化：
    • 合理设计表结构，避免冗余字段
    • 使用合适的数据类型，减少存储空间
    • 分区表，提高查询性能
    • 使用视图，简化复杂查询
  • 事务优化：
    • 保持事务简短，减少锁持有时间
    • 避免在事务中进行长时间操作
    • 合理使用隔离级别

2.3 SQL安全建议

生产环境的SQL安全建议：

  • 防止SQL注入：
    • 使用参数化查询
    • 使用预处理语句
    • 过滤用户输入
  • 权限管理：
    • 遵循最小权限原则
    • 定期审查用户权限
    • 使用角色管理权限
  • 数据加密：
      学习交流加群风哥QQ113257174

    • 对敏感数据进行加密存储
    • 使用SSL连接
    • 定期更换加密密钥
  • 审计日志：
    • 开启审计日志
    • 定期审查审计日志
    • 监控异常操作

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

3.1 SQL开发环境搭建

# 连接数据库
[opengauss@fgedu.net.cn ~]$ gsql -h 192.168.1.100 -p 5432 -U opengauss -d fgedudb
Password:
gsql ((openGauss 5.0.0 build 12345) compiled at 2024-01-01 12:34:56 UTC) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
Type “help” for help.
fgedudb=#
# 查看数据库版本
fgedudb=# SELECT version();

version
———————————————————————————————
openGauss 5.0.0 build 12345 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)
# 查看数据库列表
fgedudb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
————-+———-+———-+———+———+———————–
fgedudb | opengauss | UTF8 | C | C |
postgres | opengauss | UTF8 | C | C |
template0 | opengauss | UTF8 | C | C | =c/opengauss +
| | | | | opengauss=CTc/opengauss
template1 | opengauss | UTF8 | C | C | =c/opengauss +
更多视频教程www.fgedu.net.cn
| | | | | opengauss=CTc/opengauss
(4 rows)

3.2 SQL开发流程

# 创建表
fgedudb=# CREATE TABLE fgedu_employee (
fgedudb(# id SERIAL PRIMARY KEY,
fgedudb(# name VARCHAR(100) NOT NULL,
fgedudb(# age INTEGER,
fgedudb(# dept_id INTEGER,
fgedudb(# salary DECIMAL(10, 2)
fgedudb(# );
CREATE TABLE
# 插入数据
fgedudb=# INSERT INTO fgedu_employee (name, age, dept_id, salary) VALUES
fgedudb-# (‘Zhang San’, 30, 1, 9300.00),
fgedudb-# (‘Li Si’, 25, 1, 6600.00),
fgedudb-# (‘Wang Wu’, 35, 2, 7000.00),
fgedudb-# (‘Zhao Liu’, 40, 3, 9000.00);
INSERT 0 4
# 查询数据
fgedudb=# SELECT * FROM fgedu_employee;

id | name | age | dept_id | salary
—-+———-+—–+———+——–
1 | Zhang San | 30 | 1 | 9300.00
2 | Li Si | 25 | 1 | 6600.00
3 | Wang Wu | 35 | 2 | 7000.00
4 | Zhao Liu | 40 | 3 | 9000.00
(4 rows)

3.3 SQL测试与验证

# 测试查询性能
fgedudb=# EXPLAIN ANALYZE SELECT * FROM fgedu_employee WHERE age > 30;

QUERY PLAN
—————————————————————————————————-
Seq Scan on fgedu_employee (cost=0.00..35.50 rows=10 width=44) (actual time=0.012..0.015 rows=2 loops=1)
更多学习教程公众号风哥教程itpux_com
Filter: (age > 30)
Rows Removed by Filter: 2
Planning Time: 0.023 ms
Execution Time: 0.025 ms
(5 rows)
# 测试索引性能
fgedudb=# CREATE INDEX idx_fgedu_employee_age ON fgedu_employee(age);

CREATE INDEX
fgedudb=# EXPLAIN ANALYZE SELECT * FROM fgedu_employee WHERE age > 30;

QUERY PLAN
————————————————————————————————–
Bitmap Heap Scan on fgedu_employee (cost=4.30..13.80 rows=10 width=44) (actual time=0.010..0.012 rows=2 loops=1)
Recheck Cond: (age > 30)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_fgedu_employee_age (cost=0.00..4.29 rows=10 width=0) (actual time=0.008..0.008 rows=2 loops=1)
Index Cond: (age > 30)
Planning Time: 0.035 ms
Execution Time: 0.020 ms
(7 rows)

Part04-生产案例与实战讲解

4.1 DDL语句实战

# 创建表
fgedudb=# CREATE TABLE fgedu_department (
fgedudb(# id SERIAL PRIMARY KEY,
fgedudb(# name VARCHAR(100) NOT NULL,
fgedudb(# description TEXT
from DB视频:www.itpux.com
fgedudb(# );
CREATE TABLE
# 修改表结构
fgedudb=# ALTER TABLE fgedu_employee ADD COLUMN hire_date DATE;
ALTER TABLE
# 添加外键约束
fgedudb=# ALTER TABLE fgedu_employee ADD CONSTRAINT fk_fgedu_employee_dept_id FOREIGN KEY (dept_id) REFERENCES fgedu_department(id);
ALTER TABLE
# 创建索引
fgedudb=# CREATE INDEX idx_fgedu_employee_name ON fgedu_employee(name);

CREATE INDEX
# 删除表
fgedudb=# DROP TABLE IF EXISTS fgedu_temp;

DROP TABLE

4.2 DML语句实战

# 插入数据
fgedudb=# INSERT INTO fgedu_department (name, description) VALUES
fgedudb-# (‘IT’, ‘Information Technology’),
fgedudb-# (‘HR’, ‘Human Resources’),
fgedudb-# (‘Finance’, ‘Financial Department’);
INSERT 0 3
# 更新数据
fgedudb=# UPDATE fgedu_employee SET salary = salary * 1.1 WHERE dept_id = 1;

UPDATE 2
# 删除数据
fgedudb=# DELETE FROM fgedu_employee WHERE age > 35;

DELETE 1
# 查询数据
fgedudb=# SELECT e.id, e.name, e.age, d.name AS dept_name, e.salary
fgedudb-# FROM fgedu_employee e
fgedudb-# JOIN fgedu_department d ON e.dept_id = d.id;
id | name | age | dept_name | salary
—-+———-+—–+———–+——–
1 | Zhang San | 30 | IT | 10230.00
2 | Li Si | 25 | IT | 7260.00
3 | Wang Wu | 35 | HR | 7000.00
(3 rows)

4.3 DCL语句实战

# 创建用户
fgedudb=# CREATE USER fgedu WITH PASSWORD ‘Fgedu@123’;

CREATE ROLE
# 创建角色
fgedudb=# CREATE ROLE fgedu_role_read;

CREATE ROLE
# 授予权限
fgedudb=# GRANT SELECT ON fgedu_employee TO fgedu_role_read;

GRANT
fgedudb=# GRANT fgedu_role_read TO fgedu;
GRANT
# 撤销权限
fgedudb=# REVOKE SELECT ON fgedu_employee FROM fgedu_role_read;

REVOKE
# 提交事务
fgedudb=# BEGIN;
BEGIN
fgedudb=# INSERT INTO fgedu_employee (name, age, dept_id, salary) VALUES (‘Sun Qi’, 28, 1, 8000.00);

INSERT 0 1
fgedudb=# COMMIT;

COMMIT

Part05-风哥经验总结与分享

5.1 SQL开发最佳实践

  • 代码规范：
    • 遵循命名规范
    • 保持代码风格一致
    • 使用注释
  • 性能优化：
    • 使用索引
    • 优化查询语句
    • 避免全表扫描
  • 安全措施：
    • 防止SQL注入
    • 限制用户权限
    • 加密敏感数据
  • 测试验证：
    • 测试SQL语句
    • 分析查询计划
    • 验证数据完整性

5.2 常见SQL问题与解决方案

常见SQL问题及解决方法

  • 问题1：查询性能慢
    • 症状：查询执行时间长
    • 解决方案：创建索引；优化查询语句；分析表统计信息
  • 问题2：SQL注入
    • 症状：系统被恶意攻击
    • 解决方案：使用参数化查询；过滤用户输入；使用预处理语句
  • 问题3：死锁
    • 症状：事务无法继续执行
    • 解决方案：保持事务简短；避免长时间锁；合理设计事务
  • 问题4：数据不一致
    • 症状：数据不匹配
    • 解决方案：使用事务；确保数据完整性；定期检查数据
  • 问题5：索引失效
    • 症状：查询未使用索引
    • 解决方案：检查索引是否存在；优化查询语句；重建索引

5.3 SQL性能优化技巧

  • 索引优化：
    • 为经常查询的列创建索引
    • 为JOIN操作的列创建索引
    • 避免创建过多索引
    • 定期重建索引
  • 查询优化：
    • 使用EXPLAIN分析查询计划
    • 避免使用SELECT *
    • 使用LIMIT限制返回行数
    • 避免在WHERE子句中使用函数
  • 数据结构优化：
    • 合理设计表结构
    • 使用合适的数据类型
    • 分区表
    • 使用视图
  • 事务优化：
    • 保持事务简短
    • 避免在事务中进行长时间操作
    • 合理使用隔离级别

风哥提示：SQL是数据库开发的基础，掌握SQL语言对于数据库管理和开发至关重要。在生产环境中，应该遵循SQL开发规范，优化SQL性能，确保SQL安全，这样才能保证系统的稳定性和可靠性。

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

相关推荐

联系我们

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

微信号：itpux-com

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