1. 首页
  2. 标签
  3. sql
PostgreSQL 递归查询（含层级和结构）
System
2024-09-02
所有,数据库



-- 创建一个名为"recursive_query_example"的递归查询来获取组织结构中的所有员工
WITH RECURSIVE org_tree AS (
    -- 初始查询，从顶级组织开始
    SELECT org_id, parent_org_id, org_name
    FROM organizations
    WHERE parent_org_id IS NULL
 
    UNION ALL
 
    -- 递归查询，获取每个组织的子组织
    SELECT o.org_id, o.parent_org_id, o.org_name
    FROM organizations o
    INNER JOIN org_tree ot ON o.parent_org_id = ot.org_id
),
 
-- 使用cte_employees表示员工信息，并关联组织结构
employees_with_org AS (
    SELECT e.emp_id, e.emp_name, e.org_id
    FROM employees e
    JOIN org_tree ot ON e.org_id = ot.org_id
)
 
-- 最终查询，选择所有员工的信息
SELECT * FROM employees_with_org;

这个例子展示了如何使用PostgreSQL的递归CTE来获取一个组织结构中所有员工的信息。首先定义了一个递归的公用表表达式org_tree，它从顶级组织开始，并递归地获取每个组织的子组织。然后，使用这个递归查询结果，联结员工信息，最终得到一个包含所有员工和相应组织信息的结果集。

- 阅读更多 -
mysql+tomcat+jsp增删改查
System
2024-09-02
所有,java

在上一节中，我们已经创建了数据库和表，并在MySQL中插入了一些数据。接下来，我们将创建一个简单的JSP页面来显示和编辑这些数据。

  1. 首先，在Tomcat的webapps目录下创建一个新的文件夹，例如叫crudapp。
  2. 在crudapp文件夹内，创建一个WEB-INF文件夹。
  3. 在WEB-INF文件夹内，创建一个web.xml文件来配置应用程序。这是一个最简单的web.xml文件的例子：



<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
                      http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
  <display-name>CrudApp</display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>
  1. 在crudapp文件夹中创建一个index.jsp文件，这将是我们的主页面。
  2. 在index.jsp中，我们将使用JDBC来查询MySQL数据库，并显示结果。这是一个简单的index.jsp文件的例子：



<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>CRUD Example</title>
</head>
<body>
<%
    Connection conn = null;
    Statement stmt = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/cruddb", "root", "password");
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM users");
%>
        <table border="1">
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>
            <% while (rs.next()) { %>
                <tr>
                    <td><%= rs.getString("id") %></td>
                    <td><%= rs.getString("name") %></td>
                    <td><%= rs.getString("email") %></td>
                </tr>
            <% } %>
        </table>
<%
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try { if (stmt != null) stmt.close(); } catch (Exception e) { e.printStackTrace(); }
        try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); }
    }
%>
</body>
</html>

在这个例子中，请确保更改数据库连接字符串中的root和password为你自己的MySQL用户名和密码。

这个JSP页面将显示一个表格，列出所有用户的ID、姓名和电子邮件地址。这是一个简单的增删改查(CRUD)的示例，实际项目中可能会更复杂。

现在，你可以将crudapp文件夹部署到Tomcat，并通过访问http://localhost:8080/crudapp来查看结果。

- 阅读更多 -
Qt连接Sqlite3并使用Qtableview实时显示数据，重写QSqlQueryModel实现文本居中
System
2024-09-02
所有,数据库



#include <QApplication>
#include <QTableView>
#include <QSqlDatabase>
#include <QSqlQueryModel>
#include <QSqlError>
#include <QDebug>
 
class CenteredTextQueryModel : public QSqlQueryModel {
public:
    CenteredTextQueryModel(QObject *parent = nullptr) : QSqlQueryModel(parent) {}
 
protected:
    QVariant data(const QModelIndex &index, int role) const override {
        if (role == Qt::TextAlignmentRole) {
            return QVariant(Qt::AlignCenter);
        }
        return QSqlQueryModel::data(index, role);
    }
};
 
int main(int argc, char *argv[]) {
    QApplication app(argc, argv);
 
    // 连接数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("path_to_your_sqlite_db_file.sqlite3");
    if (!db.open()) {
        qDebug() << "数据库打开失败：" << db.lastError().text();
        return -1;
    }
 
    // 创建QTableView并设置模型
    QTableView tableView;
    CenteredTextQueryModel *model = new CenteredTextQueryModel(&tableView);
    model->setQuery("SELECT * FROM your_table_name", db);
    tableView.setModel(model);
    tableView.show();
 
    return app.exec();
}

这段代码演示了如何在Qt中连接SQLite数据库，并使用QSqlQueryModel和QTableView来实时显示数据。同时，我们创建了一个继承自QSqlQueryModel的CenteredTextQueryModel类，并重写了data函数，使得所有文本数据默认居中显示。这是一个实用且常见的Qt数据库和GUI编程技巧。

- 阅读更多 -
PostgreSQL查看数据库对象大小
System
2024-09-02
所有,数据库

在PostgreSQL中，您可以使用pg_total_relation_size()函数来查看数据库对象（表、索引等）的总大小，使用pg_relation_size()函数来查看特定数据库对象的大小。

以下是查看特定表的大小的SQL查询示例：




SELECT pg_size_pretty(pg_relation_size('schema_name.table_name'));

如果您想查看所有表的大小，可以使用以下查询：




SELECT
  table_schema || '.' || table_name AS table_full_name,
  pg_size_pretty(pg_relation_size(table_schema || '.' || table_name)) AS size
FROM
  information_schema.tables
WHERE
  table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY
  pg_relation_size(table_schema || '.' || table_name) DESC;

这将列出当前数据库中所有用户定义表的大小，并按大小降序排列。请注意，pg_catalog和information_schema通常包含系统表和视图，它们的大小可能不是您关心的。您可以根据需要调整查询，例如，添加更多的过滤条件或者只查看您感兴趣的表。

- 阅读更多 -
PostgreSQL两个日期的时间差比较分钟/小时
System
2024-09-02
所有,数据库

在PostgreSQL中，如果你想要计算两个日期之间的分钟或小时数，你可以使用EXTRACT函数结合EPOCH。以下是一些例子：

计算两个timestamp值之间相差的分钟数：




SELECT EXTRACT(EPOCH FROM (timestamp1 - timestamp2)) / 60 AS minutes_difference
FROM your_table;

计算两个timestamp值之间相差的小时数：




SELECT EXTRACT(EPOCH FROM (timestamp1 - timestamp2)) / 3600 AS hours_difference
FROM your_table;

这里timestamp1和timestamp2是你表格中的两个timestamp列或者是具体的日期时间值。EXTRACT(EPOCH FROM ...)将日期时间差转换为秒数，然后除以60或3600转换成分钟或小时。

确保你的列是timestamp类型，否则你可能需要先将它们转换成timestamp。

- 阅读更多 -
MySQL与Redis数据一致性的6种解决方案
System
2024-09-02
所有,redis

在处理MySQL与Redis数据一致性问题时，可以采用以下几种解决方案：

  1. 使用Redis的内存数据库特性，通过Redis的“写后 all”策略保证数据一致性。
  2. 通过MySQL的触发器（Trigger）同步数据到Redis。
  3. 使用MySQL的复制特性，配合Redis的读写分离或者使用专门的数据同步工具如:go-mysql。
  4. 使用MySQL的存储过程结合Redis进行数据同步。
  5. 使用MySQL的UDF（User-Defined Function）机制，将数据直接写入Redis。
  6. 使用第三方数据库中间件，如开源的Codis、ProxySQL等。

以下是一个简单的MySQL触发器同步数据到Redis的例子：




CREATE TRIGGER sync_to_redis AFTER INSERT OR UPDATE OR DELETE
ON your_table FOR EACH ROW
BEGIN
  IF NEW.id IS NOT NULL THEN
    CASE
      WHEN OLD.id IS NOT NULL THEN
        -- UPDATE操作
        CALL redis_call('HMSET', 'your_table', OLD.id, CONCAT('{', NEW.data, '}'));
      WHEN OLD.id IS NULL THEN
        -- INSERT操作
        CALL redis_call('HSET', 'your_table', NEW.id, CONCAT('{', NEW.data, '}'));
    END CASE;
  END IF;
  IF OLD.id IS NOT NULL THEN
    -- DELETE操作
    CALL redis_call('HDEL', 'your_table', OLD.id);
  END IF;
END;

其中redis_call是一个假设的函数，实际中需要自行实现与Redis的交互。

请注意，这些解决方案都需要根据具体的业务场景和架构选择合适的方法。在实施时，还需考虑性能、一致性、可靠性和复杂性等因素。

- 阅读更多 -
SQLite——世界上部署最广泛的免费开源数据库（简介）
System
2024-09-02
所有,数据库

SQLite是一个开源的嵌入式数据库引擎，实现了多数的SQL标准和事务完整性特性。它在许多设备上被广泛部署，包括手机、平板电脑和auto-navigation systems等。

SQLite的主要特点包括：

  1. 嵌入式：SQLite数据库是嵌入到宿主程序中的。
  2. 独立性：不需要独立的服务进程。
  3. 高可靠性：数据存储在单一的磁盘文件中。
  4. 易于使用：简单的SQL语言，不需要复杂的配置。
  5. 稳定性：广泛的应用场景，已经被实践证明是稳定的。
  6. 兼容性：可以和多种编程语言无缝集成，如C, C++, PHP, Python, Java, C#, Perl, Ruby等。

以下是一个使用Python连接和操作SQLite数据库的简单示例：




import sqlite3
 
# 连接到SQLite数据库
# 数据库文件是test.db，如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('test.db')
 
# 创建一个Cursor:
cursor = conn.cursor()
 
# 执行一条SQL语句，创建user表:
cursor.execute('CREATE TABLE IF NOT EXISTS user (id VARCHAR(20) PRIMARY KEY, name VARCHAR(20))')
 
# 关闭Cursor:
cursor.close()
 
# 提交事务：
conn.commit()
 
# 关闭Connection:
conn.close()

这段代码演示了如何在Python中使用SQLite3库连接到一个SQLite数据库，创建一个名为user的表，并包含id和name两个字段。在创建表之前，它会检查表是否已经存在以避免重复创建。最后，它关闭了Cursor和Connection对象，并提交了事务。

- 阅读更多 -
uniapp安卓端使用SQLite保存网络文件至本地
System
2024-09-02
所有,数据库

在uniapp中，你可以使用plus.io API来操作SQLite数据库，以及使用uni.downloadFile方法下载网络文件到Android设备本地。以下是一个简单的示例代码：




// 引入数据库操作库，如果使用的是uView UI，则可以直接使用uView提供的库
import db from '@/common/db.js';
 
export default {
  methods: {
    async downloadFileFromNetwork(fileUrl, fileName) {
      // 下载文件
      const downloadTask = uni.downloadFile({
        url: fileUrl, // 网络文件地址
        success: (downloadResult) => {
          if (downloadResult.statusCode === 200) {
            // 下载成功，保存文件路径到数据库
            const localPath = downloadResult.tempFilePath;
            db.executeSql(
              `INSERT INTO file_table (file_name, file_path) VALUES (?, ?)`,
              [fileName, localPath],
              () => {
                console.log('文件保存成功');
              },
              (error) => {
                console.error('保存文件信息到数据库失败:', error);
              }
            );
          } else {
            console.error('文件下载失败:', downloadResult.errMsg);
          }
        },
        fail: (error) => {
          console.error('下载文件失败:', error);
        }
      });
      
      downloadTask.onProgressUpdate((res) => {
        console.log('下载进度' + res.progress);
      });
    }
  }
}

在这个示例中，db.executeSql是假设你已经封装好的数据库操作方法。你需要根据自己的数据库操作库进行相应的调整。

请注意，在Android平台上，文件的存储位置可能受到不同版本的权限和空间限制的影响。确保你的应用有正确的存储权限，并且考虑到Android 6.0及以上版本的运行时权限请求。

- 阅读更多 -
PostgreSQL查询引擎——SELECT STATEMENTS SelectStmt
System
2024-09-02
所有,数据库

在PostgreSQL中，SELECT语句是用于从数据库表中检索数据的基本SQL命令。以下是一些使用SELECT语句的方法：

  1. 基本的SELECT语句：



SELECT * FROM table_name;
  1. 使用WHERE子句的SELECT语句：



SELECT * FROM table_name WHERE condition;
  1. 使用LIMIT子句的SELECT语句：



SELECT * FROM table_name LIMIT number;
  1. 使用GROUP BY子句的SELECT语句：



SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;
  1. 使用ORDER BY子句的SELECT语句：



SELECT * FROM table_name ORDER BY column_name;
  1. 使用JOIN子句的SELECT语句：



SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;
  1. 使用DISTINCT关键字的SELECT语句：



SELECT DISTINCT column_name FROM table_name;
  1. 使用UNION操作符的SELECT语句：



SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
  1. 使用子查询的SELECT语句：



SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);
  1. 使用INSERT INTO ... SELECT语句：



INSERT INTO table1 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table2;

以上就是一些基本的SELECT语句的使用方法，具体使用哪种方法，取决于你的具体需求。

- 阅读更多 -
centos7搭建postgresql主从（主备）架构
System
2024-09-02
所有,数据库

在CentOS 7上搭建PostgreSQL的主从（主备）架构，你需要做以下几个步骤：

  1. 安装PostgreSQL
  2. 配置主服务器（Master）
  3. 配置从服务器（Slave）

以下是具体的操作步骤和示例配置：

1. 安装PostgreSQL




sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql12-server
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

2. 配置主服务器（Master）

编辑PostgreSQL配置文件postgresql.conf，通常位于/var/lib/pgsql/12/data/目录下：




sudo nano /var/lib/pgsql/12/data/postgresql.conf

确保以下设置正确：




listen_addresses = '*'          # what IP address(es) to listen on;
wal_level = replica             # minimal level of WAL logging
max_wal_senders = 3             # max number of walsender processes

编辑pg_hba.conf文件，允许从服务器连接：




sudo nano /var/lib/pgsql/12/data/pg_hba.conf

添加从服务器的IP和认证方式：




host    replication     slave_user     slave_ip/32        md5

重启PostgreSQL服务：




sudo systemctl restart postgresql-12

3. 配置从服务器（Slave）

确保PostgreSQL已安装，并且数据库用户（如replication用户）已创建。

编辑recovery.conf文件，创建并配置此文件：




sudo nano /var/lib/pgsql/12/data/recovery.conf

添加以下内容：




standby_mode = 'on'
primary_conninfo = 'host=master_ip user=replication_user password=replication_password port=5432 sslmode=prefer'

重启PostgreSQL服务：




sudo systemctl restart postgresql-12

注意事项

  • 替换master_ip、slave_ip、slave_user和replication_password为你的实际IP和认证信息。
  • 确保防火墙设置允许从服务器连接到主服务器的5432端口。
  • 根据你的实际情况调整配置文件的路径和设置。

以上步骤可能需要根据你的实际网络环境和安全要求进行适当的调整。

- 阅读更多 -
Copyright © 2025 Tech Blog
All Right Reserved | 京ICP备19053165号