Manngo's questions

我每天都有一个 cron 作业来转储我的数据库。crontab 的一部分包括以下内容：

01   01 *   *   *   root    /etc/cron.d/backupDaily.sh

部分备份脚本包含以下内容：

cd /data/pgsql/
sudo -u postgres /usr/pgsql-12/bin/pg_dumpall>/data/pgsql/pg.sql

我的旧笔记提到将凭据放入文件中，例如/.pgpass. 然而，从一开始我已经升级了我的服务器几次，而且我似乎不再有这个文件了。

谁能告诉我如何摆脱这个困境？这是否表明我的postgres用户没有密码？

这是我的文件中的内容pg_hba.conf：

#   TYPE    DATABASE        USER    ADDRESS         METHOD
    local   replication     all                     peer
    host    replication     all     127.0.0.1/32    ident
    host    replication     all     ::1/128         ident

    local   all             all                     trust

#   IPv4 local connections:
    host    all             all     127.0.0.1/32    trust
    host    all             all     192.168.0.0/24  trust
    host    all             all     192.168.1.0/24  trust
    host    all             all     192.168.77.0/24 trust

当将 PHP 的lastInsertId()方法与 PostgreSQL 一起使用时，有必要知道序列的名称。

如果我使用 定义列GENERATED BY DEFAULT AS IDENTITY，我知道我可以使用类似 的名称来获取序列名称pg_get_serial_sequence('whatever', 'id')。

是否可以在CREATE TABLE语句中指定序列名称？

我想使用类似的方式更改数字和货币格式：

SET lc_numeric='en_AU.UTF-8'
SET lc_money='en_AU.UTF-8'

我尝试了多种格式，比如荷兰语，但没有任何效果。看来我的操作系统上没有该语言环境。

我可以获得 PostgreSQL 中可用语言环境的列表吗？我在网上找不到任何东西。

我已经使用 PostgreSQL 很多年了，除此之外，我还添加了一些我自己的用户定义标量函数。

当然，如果我当时知道的话，我应该把自己的功能添加到一个单独的模式中，以便更好地管理。相反，我的功能都与标准功能混合在一起。

有没有办法区分标准功能，以便我更好地组织自己的功能？

出于演示目的，我有一个示例表，其中有一varchar列名为numberstring：

-4
32
0
4
16
8
-8
1024

在选择 usingORDER BY numberstring时，我发现不同的用户得到不同的结果。

一些得到：

-4, -8, 0, 1024, 16, 32, 4, 8

而有些人得到：

0, 1024, 16, 32, -4, 4, -8, 8

不知何故，第二个结果似乎忽略了减号，即使数据以其他方式排序为字符串数据。

据我所知，底层服务器是 Microsoft SQL Server express 的标准安装，没有任何特殊选项。该版本是最近几年的版本，但使用相同版本的用户仍然报告不同的结果。

设置中是否存在影响结果的内容，如何从 SSMS 中查看？

据我所知，数据库服务器和 SSMS 是在澳大利亚设置的，只有默认值。数据类型是varchar(max)。

我不得不承认我对这个可插入数据库的东西一无所知。我在 PostgresSQL、MySQL、SQL Server 方面有经验，但 Oracle 让我失败了。

我在 Docker 映像中运行 Oracle 18c。我想创建一个名为bookshop. 按照一些说明，我运行了以下命令：

CREATE PLUGGABLE DATABASE bookshop ADMIN USER pdb_adm IDENTIFIED BY Oradoc_db1
    file_name_convert=('/opt/oracle/oradata/XE/pdbseed','/opt/oracle/oradata/XE/BOOKSHOP');

接下来在说明中，我运行：

ALTER PLUGGABLE DATABASE xepdb2 OPEN READ WRITE;

除了我不能。我收到消息：

ORA-01031: 权限不足。

然后我尝试：

DROP PLUGGABLE DATABASE bookshop;

我得到了同样的信息。

当我尝试：

SELECT vp.name, vp.open_mode
FROM v$pdbs vp;

我明白了

最后，当我试图弄清楚我是谁时：

SELECT user FROM dual;

我明白了SYSTEM。

在这一点上，我不知道如何控制这个可插入的数据库。我需要切换到其他用户吗？如何？

我有理由更改我的计算机名称（它在虚拟机中运行）。我有一个在重命名之前安装的现有 SQLExpress 实例。

有一些功能会失败，因为新数据库仍然是使用旧所有者创建的。更具体地说，当我尝试创建新的数据库图表时，出现以下错误。

无法获取有关 Windows NT 组/用户 'OLDNAME\user' 的信息，错误代码 0x534。（Microsoft SQL Server，错误：15404）。

我使用以下说明重命名服务器：

sp_dropserver 'OLDNAME\SQLEXPRESS';
GO
sp_addserver 'NEWNAME\SQLEXPRESS','local';
GO
--  Restart Server
SELECT @@SERVERNAME;    --  NEWNAME\SQLEXPRESS

但是，这并不影响新数据库的默认所有者。它们仍在与OLDNAME\user不再存在的所有者一起创建。

我知道如何在创建后更改现有数据库的所有者。但是，我怎样才能让新数据库拥有正确的所有者呢？

我对其他 SQL（PostgreSQL、Microsoft Server、MySQL、SQLite）非常有经验，但在 Oracle 中则不然。

有什么方法可以让 Oracle 接受 ISO8601 格式的日期文字。例如：

CREATE TABLE data (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    when DATE
);
INSERT INTO data(name,when) VALUES ('Fred','2021-10-05');
--  ORA-01861: literal does not match format string

请参阅此DBFiddle。

当然必须有开关。

这同样适用于输出日期：有没有办法确保 ISO8601 格式的日期？

我一直在阅读该.pgpass文件，但我无法让它为我工作。

我的.pgpass文件看起来像这样：

127.0.0.1:5432:accounts:fred:p@55w0rd

我将权限设置为0600（这是在 CentOS Linux 上）并尝试：

psql

我在哪里得到消息：

psql：错误：致命：数据库“...”不存在

…我的用户名在哪里。

如果我使用，我可以成功连接：

psql -u … -d accounts

所以我不认为我的.pgpass文件正在做它的工作。

我怎样才能让它工作？

我对一般的关系 DBMS 非常熟悉，但对 Oracle 不熟悉。我在 Docker 中运行了 Oracle 18c XE（在 MacOS 上）。我使用第三方客户端（MacOS 上的 SQLPro Studio）连接到它。现在我想开始。

如果这是 MySQL，我可以发布CREATE DATABASE whatever; USE whatever;并继续处理这些表。与 Microsoft SQL Server 相同，与 PostgreSQL 类似。我还可以创建一个额外的模式以保持更好的组织性。

我收集 Oracle XE 只为您提供一个数据库，我想创建一个合适的模式。在这一点上，我在海上。我收集到我需要创建一个与模式关联的新用户。

我也知道不同的 RDBMS 对什么是数据库或模式有不同的想法，所以我不确定这是否是正确的定义或解决方案。

我在客户端中创建新用户或新模式都没有成功。

我努力了：

CREATE USER fred IDENTIFIED BY 'h3art0fg0ld`;

但我收到消息：

无效的普通用户或角色名称

我还读到我应该使用类似的东西c##fred，它确实有效，但是（a）我真的不知道这意味着什么，并且（b）该语句GRANT connect, resource, create sessions TO c##printsdb;给了我“缺少或无效特权”的信息，所以我没有进一步先。

在 Docker 容器中，我不知道我是否可以访问一些在线教程假定的 SQL*Plus。

如何创建和使用模式，或者包含表集合所需的任何东西？

我的 CentOS 6 服务器上正在运行 MariaDB 10.2。我可以从 LAN 上的另一台机器连接到数据库。

出于好奇，我想重新审视这是如何实现的。我读到的所有内容都提到bind-address了my.cnf文件中的使用，但是当我检查这个文件时，没有这样的指令。文件中也没有任何内容/etc/my.cnf.d/*.cnf。

在这种情况下，网络访问是默认的，还是有其他一些神秘的指令？

大多数其他数据库允许您使用VALUES. 据我所知，SQL Server 允许您使用子查询执行此操作：

SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', '[email protected]'),
        ('Ebenezer','Splodge', '[email protected]'),
        ('Sylvester','Underbar', '[email protected]'),
        ('Cynthia','Hyphen-Smythe', '[email protected]')
) as sq(givenname,familyname,email);

但我似乎无法让它与 CTE 一起使用。我能做的最好的就是在 CTE 中嵌入一个子查询：

WITH data AS (
    SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', '[email protected]'),
        ('Ebenezer','Splodge', '[email protected]'),
        ('Sylvester','Underbar', '[email protected]'),
        ('Cynthia','Hyphen-Smythe', '[email protected]')
    ) as sq(givenname,familyname,email)
)
SELECT * FROM data;

我更喜欢 CTE 因为它们的可读性，但是有没有更直接的方法呢？例如 PostgreSQL、MariaDB（支持版本）和 SQLite 都接受这个：

WITH data(givenname,familyname,email) AS (
    VALUES
        ('Cornelius','Eversoe', '[email protected]'),
        ('Ebenezer','Splodge', '[email protected]'),
        ('Sylvester','Underbar', '[email protected]'),
        ('Cynthia','Hyphen-Smythe', '[email protected]')
)
SELECT * FROM data;

使用ntile()窗口函数时，主要问题是它任意分组为大致相等的部分，而不管实际值如何。

例如，使用以下查询：

select
    id,title,price,
    row_number() over(order by price) as row_number,
    rank() over(order by price) as rank,
    count(*) over(order by price) as count,
    dense_rank() over(order by price) as dense_rank,
    ntile(10) over(order by price) as decile
from paintings
order by price;

我会得到 10 组大小大致相同的画作，价格相同的画很有可能最终会被放入不同的垃圾箱。

例如：

┌────┬────────────────────────────────────────────┬───────┬────────────┬──────┬───────┬────────────┬────────┐
│ id │ title                                      │ price │ row_number │ rank │ count │ dense_rank │ decile │
╞════╪════════════════════════════════════════════╪═══════╪════════════╪══════╪═══════╪════════════╪════════╡
│ 11 │ Eyes in the Heat                           │ 10    │ 1          │ 1    │ 1     │ 1          │ 1      │
│ 19 │ Deux fillettes, fond jaune et rouge        │ 11    │ 2          │ 2    │ 2     │ 2          │ 1      │
│ 17 │ Flowers in a Pitcher                       │ 12    │ 3          │ 3    │ 6     │ 3          │ 1      │
│ 5  │ Composition with Red, Yellow and Blue      │ 12    │ 4          │ 3    │ 6     │ 3          │ 2      │
│ 18 │ La lecon de musique (The Music Lesson)     │ 12    │ 5          │ 3    │ 6     │ 3          │ 2      │
│ 9  │ The Adoration of the Magi                  │ 12    │ 6          │ 3    │ 6     │ 3          │ 2      │
│ 29 │ Self-Portrait                              │ 14    │ 7          │ 7    │ 10    │ 4          │ 3      │
│ 25 │ Symphony in White, No. 1: The White Girl   │ 14    │ 8          │ 7    │ 10    │ 4          │ 3      │
│ 30 │ The Anatomy Lecture of Dr. Nicolaes Tulp   │ 14    │ 9          │ 7    │ 10    │ 4          │ 3      │
│ 20 │ Les repasseuses (Women Ironing)            │ 14    │ 10         │ 7    │ 10    │ 4          │ 4      │
│ 1  │ The Birth of Venus                         │ 15    │ 11         │ 11   │ 14    │ 5          │ 4      │
│ 12 │ Femme se promenant dans une foret exotique │ 15    │ 12         │ 11   │ 14    │ 5          │ 4      │
│ 24 │ Portrait of the Painter’s Mother           │ 15    │ 13         │ 11   │ 14    │ 5          │ 5      │
│ 28 │ Jeunes filles au piano                     │ 15    │ 14         │ 11   │ 14    │ 5          │ 5      │
│ 7  │ Portrait de l artiste (Self-portrait)      │ 16    │ 15         │ 15   │ 17    │ 6          │ 5      │
│ 3  │ The Last Supper                            │ 16    │ 16         │ 15   │ 17    │ 6          │ 6      │
│ 13 │ Combat of a Tiger and a Buffalo            │ 16    │ 17         │ 15   │ 17    │ 6          │ 6      │
│ 4  │ The Creation of Man                        │ 17    │ 18         │ 18   │ 19    │ 7          │ 6      │
│ 22 │ Le Chemin de Fer                           │ 17    │ 19         │ 18   │ 19    │ 7          │ 7      │
│ 6  │ Femmes de Tahiti [Sur la plage]            │ 18    │ 20         │ 20   │ 24    │ 8          │ 7      │
│ 21 │ Le Bar aux Folies-Berg                     │ 18    │ 21         │ 20   │ 24    │ 8          │ 7      │
│ 26 │ Lady at the Piano                          │ 18    │ 22         │ 20   │ 24    │ 8          │ 8      │
│ 15 │ Remembrance of a Garden                    │ 18    │ 23         │ 20   │ 24    │ 8          │ 8      │
│ 16 │ 1914                                       │ 18    │ 24         │ 20   │ 24    │ 8          │ 8      │
│ 14 │ Ancient Sound, Abstract on Black           │ 19    │ 25         │ 25   │ 28    │ 9          │ 9      │
│ 8  │ The Large Turf                             │ 19    │ 26         │ 25   │ 28    │ 9          │ 9      │
│ 23 │ On the Beach                               │ 19    │ 27         │ 25   │ 28    │ 9          │ 9      │
│ 2  │ Portrait of Mona Lisa                      │ 19    │ 28         │ 25   │ 28    │ 9          │ 10     │
│ 27 │ On the Terrace                             │ 20    │ 29         │ 29   │ 30    │ 10         │ 10     │
│ 10 │ The She-Wolf                               │ 20    │ 30         │ 29   │ 30    │ 10         │ 10     │
└────┴────────────────────────────────────────────┴───────┴────────────┴──────┴───────┴────────────┴────────┘

请注意，有四个项目 price 12，但其中两个在有十分位数 1 中，其中两个在十分位数 2 中。我想把这些项目放在一起，我不关心哪个十分位数。

我已经包含了其他窗口函数来进行比较。

似乎ntile()使用row_number()唯一的并以此为基础。如果它使用rank()orcount(*)函数会更公平，因为价格相同的物品最终会放在同一个 bin 中。

这是 PostgreSQL 和 SQL Server 的行为，并且可能是其余的行为。

问题是，有没有办法做到这一点？

我知道NULLIF比较两个值并NULL在它们匹配时返回的函数。

是否有一个函数可以比较两个值并NULL在它们不匹配时返回？那就是过滤掉其他值。

我知道我可以使用CASE：

CASE column WHEN value THEN 1 END

我也知道我可以写一个函数。

也许有一个NULLIF我不知道的微妙技巧。

我认为NULLIF是通用的。我正在寻找一种通用的解决方案，以便它可以应用于任何标准数据库。

我知道 Oracle Express 版本最高可达版本 11g，它已经过时了。然而，我读到 Oracle 正在改变/已经改变了他们对新版本的许可，这样我们就不必再等待数年才能获得新的 Express 版本。

我向来自不同背景的用户教授 SQL，其中一些人在工作中使用 Oracle。将他们指向他们可以练习的软件包会很有用。但是，我发现 Oracle 的网站有些令人费解。

• 目前哪些版本可供个人免费使用，例如培训？
• 是否还有适用于小型企业（例如拥有 4 台计算机）的免费版本？

我有两个版本的多重连接，它们产生相同的结果。五张表分别是：

customers ← sales ← saleitems → paintings → artists

箭头（希望）显示了表之间的关系。

每个表都有一个名为 的主键id，而内部表有一个指向另一个名为 的表的外键[othertable]id。

第一个版本是按上述顺序列出的表的经典连接

SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s ON c.id=s.customerid
            JOIN saleitems si ON s.id=si.saleid
                JOIN paintings p ON si.paintingid=p.id
                    JOIN artists a ON p.artistid=a.id

;

第二个版本JOIN首先是所有子句，然后是ON子句，顺序相反。

SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s 
            JOIN saleitems si
                JOIN paintings p
                    JOIN artists a
                    ON p.artistid=a.id
                ON si.paintingid=p.id
            ON s.id=si.saleid
        ON c.id=s.customerid

;

好的，所以它可以工作，但是任何人都可以解释第二个版本是如何工作的吗？为什么从句的顺序必须与ON从句相反JOIN？是否可以随机排序JOINorON子句？

我已经在 Microsoft SQL 以及 PostgreSQL 中对此进行了测试。

Microsoft SQL Server 有一个我认为非常明智的函数，如果强制转换不成功，try_cast()它会返回 a null，而不是引发错误。

这使得可以使用CASE表达式或 acoalesce来回退。例如：

SELECT coalesce(try_cast(data as int),0);

问题是，PostgreSQL 有类似的东西吗？

提出这个问题是为了填补我的知识空白，但也有一般原则，即有些人更喜欢对某些用户错误做出不那么戏剧性的反应。null在 SQL 中返回 a比返回错误更容易。例如SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. 根据我的经验，如果有 B 计划，有时会更好地处理用户错误。

据我所知，SQLite 不强制定义表数据类型——它具有动态类型。例如，它允许您将字符串插入数字字段，或超过字符串的长度。

如果是这样，那么在CREATE TABLE语句中定义数据类型的意义何在？

我找不到任何关于什么构成 PostgreSQL 函数中有效变量名的确切信息。

我假设该函数是使用plpgsql. 我知道我可以使用字母、数字（除了第一个字符）和下划线。还有别的事吗？

这是在哪里记录的？

SQL Server 有一个带有可选唯一列的怪癖：一个表中不能有多个null列。

例如：

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) unique  --  optional, but unique
);

（在澳大利亚，我们有纳税人独有的税号）。

以下是一种解决方法：

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9)
);
CREATE UNIQUE NONCLUSTERED INDEX uq_employees_tfn ON employees(tfn)
WHERE tfn IS NOT NULL;

是否可以命名一个唯一约束并给它上面的条件是类似于第一个例子的方式？也就是说，是否可以在列定义中包含更复杂的约束？