School/PostgreSQL¶

2023年5月31日
分类于 School/PostgreSQL
需要 1 分钟阅读时间

PostgreSQL Trigger

Trigger 其实就是一个数据库在特定操作后自动执行的一种函数。

它可以被连接到表和视图。

在表上，触发器 可以被定义为在任何 INSERT，UPDATE，DELETE 操作之前或之后执行，且可以分为 对每条语句执行一次 还是 对每一个被修改的行执行一次。

对于 UPDATE 操作，还可以指定 特定的行被更新后 执行。

在视图上，触发器 可以被定义为代替任何 INSERT, UPDATE, DELETE 操作执行。

触发器可以分为 语句级 的和行级的：

语句级

在 BEFORE 触发器中无法访问语句产生的改动

在 AFTER 触发器中可以访问语句产生的所有改动

BEFORE 触发器无法访问语句所产生的改动（因为语句还未执行），而 AFTER 触发器可以访问语句所产生的所有改动。

语句

PostgreSQL SQL Dialect
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER /*name*/ { BEFORE | AFTER | INSTEAD OF } { /*event*/ [ OR ... ] }
    ON /*table_name*/
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( /*condition*/ ) ]
    EXECUTE { FUNCTION | PROCEDURE } /*function_name*/ ( /*arguments*/ )

其中 event 可以是 INSERT，UPDATE [ OF /*column_name*/ [, ...] ]，DELETE。

数据变更触发器

data change trigger 是一个满足以下条件的函数：

无参数
返回类型为 trigger

在这样的触发器中有一些特殊的变量可以访问：

NEW

类型为 RECORD，在行级触发器中。

其值为 INSERT/UPDATE 操作的新的行。

OLD

类型为 RECORD，在行级触发器中。

其值为 INSERT/UPDATE 操作的旧的行。

2023年4月14日
分类于 School/PostgreSQL
需要 2 分钟阅读时间

PostgreSQL PL_pgSQL

PL/pgSQL 是一个用于 PostgreSQL 的可加载的过程性语言

使用 PL/pgSQL 书写的函数可以接受服务器支持的任何标量或数组数据作为参数，同时它也可以返回任何这些类型。

创建 PL/SQL 函数

通过执行 CREATE FUNCTION 来在服务器创建 PL/pgSQL 函数：

PostgreSQL SQL Dialect
CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

函数体就是个简单的字符串字面值

// TODO: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

pg/pgSQL 是一个块结构的语言，一个完整的函数体必须是一个块，块可以通过如下方式定义：

PostgreSQL SQL Dialect
[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

在块中的每一个定义或语句都要以分号结尾

label 只有在你希望制指定使用某一个块用于一个 EXIT 语句的时候需要，或者标识出块中定义的变量。如果在 END 后写了 label 那么就要和开始的 label 相匹配。

下面是一个详细一些的例子：

PostgreSQL SQL Dialect
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

There is actually a hidden “outer block” surrounding the body of any PL/pgSQL function. This block provides the declarations of the function's parameters (if any), as well as some special variables such as FOUND (see Section 43.5.5). The outer block is labeled with the function's name, meaning that parameters and special variables can be qualified with the function's name.

表达式

所有在 PL/pgSQL 语句中使用的表达式都会使用服务器的主 SQL 执行器处理。比如如果你写了一个像下面这样的 PL/pgSQL 语句：

PostgreSQL SQL Dialect
IF expression THEN ...

那么 PL/pgSQL 就会像下面这样进行一次查询来对表达式求值：

PostgreSQL SQL Dialect
SELECT expression

基本语句

1. 赋值

Text Only
1	`variable { := \| = } expression;`

2. 执行 SQL 命令

一般地，任何不返回行的 SQL 命令都可以通过直接写在 PL/pgSQL 中的方式来执行：

PostgreSQL SQL Dialect
CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

而如果一条命令会返回行（比如 SELECT 或带有 RETURNING 的 INSERT/UPDATE/DELETE），有两种方式来执行： - 如果命令只返回一个行或者你只关心输出的第一行，可以通过添加一个 INTO 子句来捕获输出 - 如果要处理所有的输出行，可以将命令作为 FOR 循环的数据源

2.1 执行单行结果的命令

如下：

PostgreSQL SQL Dialect
SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

其中 target 可以是一个记录变量、行变量、逗号分割的简单变量和记录/行字段。

2023年3月29日
分类于 School/PostgreSQL
需要 4 分钟阅读时间

PostgreSQL Data Defination

参考：PostgreSQL: Documentation: 15: 5.4. Constraints

二、默认值

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

默认值也可以被设置为一个表达式，表达式会在记录被插入时求值。

一个例子就是时间戳 DEFAULT CURRENT_TIMESTAMP，还有就是自增的序列号：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

这个 nextval() 函数见 PostgreSQL: Documentation: 15: 9.17. Sequence Manipulation Functions

四、Constraints 约束

二、Not-Null 约束

要求某一列不能为空值。

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

其实等价于：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer CHECK (product_no is NOT NULL),
    name text CHECK (name is NOT NULL),
    price numeric
);

多个约束条件可以用空格隔开这么写：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

3. Unique 约束

要求某一列的值不重复。

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer UNIQUE,
    name text UNIQUE,
    price numeric
);

写作对表的约束可以这么写：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no, name)
);

要注意的是 NULL 被视为不相同，也就是说如果两条记录的 Unique 约束内的某一列都为 NULL，是不违反约束的。可以通过添加 NULLS NOT DISTINCT 来规定将 NULL 值视为相等：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

4. Primary Keys

一个主键唯一确定一条记录，也就是 UNIQUE 且 NOT NULL。

所以

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

其实等价于

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

可以以一组列作为主键：

PostgreSQL SQL Dialect
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

5. Foreign Keys

外键必须在其他表中存在，即参照完整性。

比如对于这样一张产品表 products：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

其中的 product_no 可能要被其他表引用，比如订单表 orders：

PostgreSQL SQL Dialect
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

这时，如果新的记录的 product_no 在 products 表中不存在则会违反约束。

上面的命令也可以简写：

PostgreSQL SQL Dialect
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

因为对于另一个表的引用其实默认就是以那个表的主键为引用列的。

也可以引用多个列：

PostgreSQL SQL Dialect
CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

当然数量和类型必须对应。

有时候外键会是自己同一张表的主键：

PostgreSQL SQL Dialect
CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

这叫做 自引用外键。

还会有一个问题，就是如果引用的外键在其他表中对应记录被删除呢？此时这个记录就不合法了。

这是有几个选择：

不允许删除作为外键被引用的记录 ON DELETE RESTRICT
将引用了外键的记录的记录也删除掉 ON DELETE CASACDE

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

如果 products 中的某条记录被引用，那么不允许删除 products 中的该条记录。

如果 orders 中的某条记录被删除，那么 order_items 引用了对应记录的键的记录就会被删除。

还有一些其他的：

如果什么都不写就是 NO ACTION，会抛出错误。

还有 SET NULL 和 SET DEFAULT xxx，顾名思义。

这块还有点复杂，先咕一下，后面用到了再详细整理。

五、修改表

1. 添加列

PostgreSQL SQL Dialect
ALTER TABLE products ADD COLUMN description text;

2. 删除列

PostgreSQL SQL Dialect
ALTER TABLE products DROP COLUMN description;

对于被引用的记录，可以使用 CASCADE 来删除依赖于此列的列：

PostgreSQL SQL Dialect
ALTER TABLE products DROP COLUMN description CASCADE;

3. 添加约束

PostgreSQL SQL Dialect
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

对于 NOT NULL 这种不能被写为表约束的约束，可以用修改列的语法来写：

PostgreSQL SQL Dialect
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

4. 删除约束

PostgreSQL SQL Dialect
ALTER TABLE products DROP CONSTRAINT some_name;

对于 NOT NULL：

PostgreSQL SQL Dialect
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

5. 改变某一列的默认值

PostgreSQL SQL Dialect
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

或者删除默认值：

PostgreSQL SQL Dialect
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

6. 改变某一列的数据类型

PostgreSQL SQL Dialect
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

7. 重命名某一列

PostgreSQL SQL Dialect
ALTER TABLE products RENAME COLUMN product_no TO product_number;

8. 重命名表

PostgreSQL SQL Dialect
ALTER TABLE products RENAME TO items;

2023年3月29日
分类于 School/PostgreSQL
需要 1 分钟阅读时间

PostgreSQL Data Manipulation

参考：PostgreSQL: Documentation: 15: Chapter 6. Data Manipulation

一、插入数据

以这张表为例：

PostgreSQL SQL Dialect
CREATE TABLE products (
    product_no    integer,
    name          text,
    price         numeric
);

可以通过下面的命令来插入一条记录：

PostgreSQL SQL Dialect
INSERT INTO products VALUES (1, 'Cheese', 9.99);

但是上面的写法要求顺序与表中列得顺序对应，也可以采取下面的写法，与表名一一对应：

PostgreSQL SQL Dialect
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

如果某一列没有值（为空）那么可以将其省略：

PostgreSQL SQL Dialect
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

上面第二行是 PostgreSQL 的扩展写法，会从左到右依次为列赋值，剩余为空。

也可以显式地指定使用某一列使用默认值或全部使用默认值：

PostgreSQL SQL Dialect
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

可以用一条命令插入多条数据：

PostgreSQL SQL Dialect
INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

还可以插入查询的结果：

PostgreSQL SQL Dialect
INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

2023年3月29日
分类于 School/PostgreSQL
需要 1 分钟阅读时间

PostgreSQL Views

参考：PostgreSQL: Documentation: 15: 41.2. Views and the Rule System

视图是从其他表中导出的表，是一个虚表。数据库只保存视图的定义，而不保存视图的数据（因为视图其实可以理解为对子查询的一个别名）。

而在 PostgreSQL 中的视图其实是使用 rule system 实现的，所以下面两个命令其实在本质上是一样的：

PostgreSQL SQL Dialect
CREATE VIEW myview AS SELECT * FROM mytab;

PostgreSQL SQL Dialect
CREATE TABLE myview (/*same column list as mytab*/);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

不过这会带来一些副作用，其中之一就是在 system catalog 中，一个视图的信息适合一个表完全一样的，所以对于解析器，它们之间没有任何区别，他们都是一个东西 —— 关系。

PostgreSQL SQL Dialect
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW /*name*/ [ ( /*column_name*/ [, ...] ) ]
    [ WITH ( /*view_option_name*/ [= /*view_option_value*/] [, ... ] ) ]
    AS /*query*/
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

最基本的创建视图的命令如下：

PostgreSQL SQL Dialect
CREATE VIEW /*name*/ AS /*query*/

如果天加上 OR REPLACE 则会在视图存在的时候将其替换。

其他的一些参数：

TEMP：临时的视图会在当前 session 结束时自动 drop 掉
RECURSIVE：创建一个递归的视图

PostgreSQL SQL Dialect
CREATE RECURSIVE VIEW [ schema . ] /*view_name*/ (/*column_names*/) AS SELECT ...;

其实等价于

PostgreSQL SQL Dialect
CREATE VIEW [ schema . ] /*view_name*/ AS WITH RECURSIVE /*view_name*/ (/*column_names*/) AS (SELECT ...) SELECT /*column_names*/ FROM /*view_name*/;

CHEK OPTION：控制自动更新的视图的行为

开启后，INSERT 和 UPDATE 命令会被检查确保新的行满足视图定义的条件，能够在视图中显示。

如果有视图依赖于视图的情况：

LOCAL：会仅检查当前视图
CASCADE（默认）：会递归地检查每个视图

2023年3月29日
分类于 School/PostgreSQL
需要 1 分钟阅读时间

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.

一、引入

比如对于这样一个表和查询：

PostgreSQL SQL Dialect
CREATE TABLE test1 (
    id integer,
    content varchar
);

PostgreSQL SQL Dialect
SELECT content FROM test1 WHERE id = xxx;

正常情况下数据库会一行一行地扫描整张表才能得到结果，显然这是个很低效的方法。

于是我们可以通过创建索引，来借助数据结构优化搜索的效率。

PostgreSQL SQL Dialect
CREATE INDEX test1_id_index ON test1 (id);

在表 test1 中基于 id 列建立索引，名为 test1_id_index。

在索引被创建后，系统会随着数据的插入、更新、删除更新索引。

对一张很大的表创建索引可能会花费很长时间，默认情况下 PostgreSQL 允许在创建索引的同时支持读操作，但所有写操作都会阻塞直至索引创建完成，不过这一点可以修改，见 Building Indexes Concurrently。

二、索引的类型

咕

三、多列索引

索引也可以基于多列来创建，比如对于下面的表和查询：

PostgreSQL SQL Dialect
CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

PostgreSQL SQL Dialect
SELECT name FROM test2 WHERE major = constant AND minor = constant;

或许创建一个这样的索引是个不错的选择：

PostgreSQL SQL Dialect
CREATE INDEX test2_mm_idx ON test2 (major, minor);

四、索引与 ORDER BY

索引也可以调整排序顺序，默认为升序。

使用 DESC 标明降序，使用 NULLS FIRST 和 NULLS LAST 来更改空值的位置。

PostgreSQL SQL Dialect
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

2023年3月29日
分类于 School/PostgreSQL
需要 10 分钟阅读时间

专业课程综合实训II 记录-专题一、二

一、创建数据库和数据表

md这呆B文档，字段类型不用标准类型，命名规范不统一，同样是 xno，有的用字符串有的用数字我服了。

有空我改一改改得统一一些。

利用 CREATE 命令创建6张表（表结构如下）：

学生基本信息表 student

字段名	字段类型	约束条件	字段含义说明
sno	varchar(8)	Primary key	学号
sname	varchar(8)	Not null	姓名
ssex	varchar(2)	'男' 或 '女'	性别
sbirthday	date		出生日期
classno	varchar(6)	Foreign key	班级号
totalcredit	smallint	初值为 0	已选修的总学分

班级信息表 class

字段名	字段类型	约束条件	字段含义说明
classno	varchar(6)	Primary key	班级号
classname	varchar(20)	Not null	班级名称
classmajor	varchar(20)		所属专业
classdept	varchar(20)		所属系别
studentnumber	smallint	[20..40]	学生数

课程信息表 course

字段名	字段类型	约束条件	字段含义说明
cno	varchar(6)	Primary key	课程号
cname	varchar(30)	Not null	课程名称
ccredit	smallint	[1..4]	学分

选修课程信息表 sc

字段名	字段类型	约束条件	字段含义说明
sno	varchar(8)	Foreign key	学号
cno	varchar(6)	Foreign key	课程号
grade	integer	[0..100]	成绩

教师信息表 teacher

字段名	字段类型	约束条件	字段含义说明
tno	integer	Primary key	教师号
tname	varchar(8)	Not null	教师姓名
tsex	varchar(2)	'男' 或 '女'	性别
tbirthday	date		出生日期
ttitle	varchar(20)		职称

教师任课情况表 teaching

字段名	字段类型	约束条件	字段含义说明
tno	integer	Foreign key	教师号
cno	varchar(6)	Foreign key	课程号
language	varchar(10）	'Chinese' 或 'Bilingual' 或 English	授课语言

可以得到表的引用关系，如下图：

---
title: 表的引用关系
---
classDiagram
    class Class {
        classno
        classname
        classmajor
        classdept
        studentnumber
    }
    class Student {
        sno
        sname
        ssex
        sbirthday
        classno
        Totalcredit
    }
    Class <-- Student

    class Course {
        cno
        cname
        ccredit
    }
    Student <-- Sc
    Course <-- Sc
    class Sc {
        sno
        cno
        grade
    }

    class Teacher {
        tno
        tname
        tsex
        tbirthday
        ttitle
    }
    Teacher <-- Teaching
    Course <-- Teaching
    class Teaching {
        tno
        cno
        language
    }

按照顺序依次创建六张表：

PostgreSQL SQL Dialect
CREATE TABLE class (
    classno          varchar(6)   PRIMARY KEY,
    classname        varchar(20)  NOT NULL,
    classmajor       varchar(20),
    classdept        varchar(20),
    studentnumber    smallint     CHECK (studentnumber BETWEEN 20 AND 40)
);

PostgreSQL SQL Dialect
CREATE TABLE student (
    sno            varchar(8)  PRIMARY KEY,
    sname          varchar(8)  NOT NULL,
    ssex           varchar(2)  CHECK (ssex in ('男', '女')),
    sbirthday      date,
    classno        varchar(6)  REFERENCES class,
    totalcredit    smallint    DEFAULT 0
);

PostgreSQL SQL Dialect
CREATE TABLE teacher (
    tno          varchar(6)    PRIMARY KEY,
    tname        varchar(8)    NOT NULL,
    tsex         varchar(2)    CHECK (tsex in ('男', '女')),
    tbirthday    date,
    ttitle       varchar(20)
);

PostgreSQL SQL Dialect
CREATE TABLE course (
    cno        varchar(6)     PRIMARY KEY,
    cname      varchar(30)    NOT NULL,
    ccredit    smallint       CHECK (ccredit BETWEEN 1 AND 4)
);

PostgreSQL SQL Dialect
CREATE TABLE sc (
    sno      varchar(8)    REFERENCES student,
    cno      varchar(6)    REFERENCES course,
    grade    integer       CHECK (grade BETWEEN 0 AND 100)
);

PostgreSQL SQL Dialect
CREATE TABLE teaching (
    tno         varchar(6)     REFERENCES teacher,
    cno         varchar(6)     REFERENCES course,
    language    varchar(10)    CHECK (language in ('Chinese', 'Bilingual', 'English'))
);

二、利用 INSERT 命令向六张表中插入数据

class 表内容

classno	classname	classmajor	classdept	studentnumber
'Rj0801'	'软件 0801'	'软件工程'	'软件开发'	24
'Rj0802'	'软件 0802'	'软件工程'	'软件开发'	26
'Rj0803'	'软件 0803'	'软件工程'	'数字媒体'	25
'Rj0804'	'软件 0804'	'软件工程'	'软件开发'	25
'Rj0805'	'软件 0805'	'软件工程'	'数字媒体'	24
'Rj0806'	'软件 0806'	'软件工程'	'软件开发'	24

PostgreSQL SQL Dialect
INSERT INTO class (classno, classname, classmajor, classdept, studentnumber) VALUES
 ('Rj0801', '软件 0801', '软件工程', '软件开发', 24),
 ('Rj0802', '软件 0802', '软件工程', '软件开发', 26),
 ('Rj0803', '软件 0803', '软件工程', '数字媒体', 25),
 ('Rj0804', '软件 0804', '软件工程', '软件开发', 25),
 ('Rj0805', '软件 0805', '软件工程', '数字媒体', 24),
 ('Rj0806', '软件 0806', '软件工程', '软件开发', 24);

student 表内容

sno	sname	ssex	sbirthday	classno
'08300010'	'李在'	'男'	'1991-10-1'	'Rj0801'
'08300012'	'葛畅'	'男'	'1990-8-8'	'Rj0801'
'08300015'	'刘晶'	'女'	'1990-5-22'	'Rj0801'
'08300020'	'杨敏'	'女'	'1989-1-8'	'Rj0801'
'08300030'	'胡贤斌'	'男'	'1990-10-8'	'Rj0801'
'08300048'	'赵鸿泽'	'男'	'1989-6-6'	'Rj0802'
'08300050'	'王威'	'男'	'1990-6-10'	'Rj0802'
'08300067'	'赵玮'	'女'	'1990-8-21'	'Rj0803'
'08300075'	'王娜娜'	'女'	'1991-9-23'	'Rj0803'
'08300088'	'秦键'	'男'	'1989-3-1'	'Rj0803'
'08300100'	'田邦仪'	'女'	'1990-2-26'	'Rj0804'
'08300148'	'赵心砚'	'男'	'1991-4-25'	'Rj0805'
'08300150'	'杨青'	'女'	'1989-11-15'	'Rj0805'
'08300160'	'杨玲玲'	'女'	'1990-12-12'	'Rj0806'

PostgreSQL SQL Dialect
INSERT INTO student (sno, sname, ssex, sbirthday, classno) VALUES
 ('08300010', '李在',   '男', '1991-10-1',  'Rj0801'),
 ('08300012', '葛畅',   '男', '1990-8-8',   'Rj0801'),
 ('08300015', '刘晶',   '女', '1990-5-22',  'Rj0801'),
 ('08300020', '杨敏',   '女', '1989-1-8',   'Rj0801'),
 ('08300030', '胡贤斌', '男', '1990-10-8',  'Rj0801'),
 ('08300048', '赵鸿泽', '男', '1989-6-6',   'Rj0802'),
 ('08300050', '王威',   '男', '1990-6-10',  'Rj0802'),
 ('08300067', '赵玮',   '女', '1990-8-21',  'Rj0803'),
 ('08300075', '王娜娜', '女', '1991-9-23',  'Rj0803'),
 ('08300088', '秦键',   '男', '1989-3-1',   'Rj0803'),
 ('08300100', '田邦仪', '女', '1990-2-26',  'Rj0804'),
 ('08300148', '赵心砚', '男', '1991-4-25',  'Rj0805'),
 ('08300150', '杨青',   '女', '1989-11-15', 'Rj0805'),
 ('08300160', '杨玲玲', '女', '1990-12-12', 'Rj0806');

course 表内容

cno	cname	ccredit
'800001'	'计算机基础'	4
'800002'	'程序设计语言'	4
'800003'	'数据结构'	4
'810011'	'数据库系统'	4
'810013'	'计算机网络'	3
'810015'	'微机原理与应用'	4

PostgreSQL SQL Dialect
INSERT INTO course (cno, cname, ccredit) VALUES
    ('800001', '计算机基础',     4),
    ('800002', '程序设计语言',   4),
    ('800003', '数据结构',       4),
    ('810011', '数据库系统',     4),
    ('810013', '计算机网络',     3),
    ('810015', '微机原理与应用', 4);

sc 表内容

sno	cno	grade
'08300012'	'800003'	88
'08300015'	'800003'
'08300020'	'800003'	81
'08300030'	'800003'	78
'08300048'	'800003'	95
'08300100'	'810011'	67
'08300148'	'810011'	58
'08300150'	'810011'	89
'08300160'	'810011'	71

PostgreSQL SQL Dialect
INSERT INTO sc (sno, cno, grade) VALUES
   ('08300012', '800003', 88),
   ('08300015', '800003', NULL),
   ('08300020', '800003', 81),
   ('08300030', '800003', 78),
   ('08300048', '800003', 95),
   ('08300100', '810011', 67),
   ('08300148', '810011', 58),
   ('08300150', '810011', 89),
   ('08300160', '810011', 71);

teacher 表内容

tno	tname	tsex	tbirthday	ttitle
'000001'	'李英'	'女'	'1975-11-3'	'讲师'
'000002'	'王大山'	'男'	'1969-3-2'	'副教授'
'000003'	'张朋'	'男'	'1970-2-13'	'讲师'
'000004'	'陈为军'	'男'	'1985-8-14'	'助教'
'000005'	'宋浩然'	'男'	'1976-4-23'	'讲师'
'000006'	'许红霞'	'女'	'1966-2-12'	'副教授'
'000007'	'徐永军'	'男'	'1962-1-24'	'教授'
'000008'	'李桂菁'	'女'	'1960-12-15	'教授'
'000009'	'王一凡'	'女'	'1974-12-8'	'讲师'
'000010'	'田峰'	'男'	'1988-1-18'	'助教'

PostgreSQL SQL Dialect
INSERT INTO teacher(tno, tname, tsex, tbirthday, ttitle) VALUES
 ('000001', '李英', '女', '1975-11-3', '讲师'),
 ('000002', '王大山', '男', '1969-3-2', '副教授'),
 ('000003', '张朋', '男', '1970-2-13', '讲师'),
 ('000004', '陈为军', '男', '1985-8-14', '助教'),
 ('000005', '宋浩然', '男', '1976-4-23', '讲师'),
 ('000006', '许红霞', '女', '1966-2-12', '副教授'),
 ('000007', '徐永军', '男', '1962-1-24', '教授'),
 ('000008', '李桂菁', '女', '1960-12-15', '教授'),
 ('000009', '王一凡', '女', '1974-12-8', '讲师'),
 ('000010', '田峰', '男', '1988-1-18', '助教');

teaching 表内容

cno	tno	language
'800001'	'000001'	'English'
'800002'	'000002'	'Chinese'
'800003'	'000002'	'Bilingual'
'810011'	'000003'	'Chinese'
'810013'	'000004'	'English'
'800001'	'000005'	'Chinese'
'800002'	'000006'	'Chinese'
'800003'	'000007'	'English'
'810011'	'000007'	'English'
'810013'	'000008'	'Bilingual'

PostgreSQL SQL Dialect
INSERT INTO teaching (cno, tno, language) VALUES
 ('800001', '000001', 'English'),
 ('800002', '000002', 'Chinese'),
 ('800003', '000002', 'Bilingual'),
 ('810011', '000003', 'Chinese'),
 ('810013', '000004', 'English'),
 ('800001', '000005', 'Chinese'),
 ('800002', '000006', 'Chinese'),
 ('800003', '000007', 'English'),
 ('810011', '000007', 'English'),
 ('810013', '000008', 'Bilingual');

三、简单的数据操作 24 条

查询所有同学的所有基本信息。

PostgreSQL SQL Dialect
SELECT * FROM student;

查询结果：

Text Only

   sno    | sname  | ssex | sbirthday  | classno | totalcredit
----------+--------+------+------------+---------+-------------
 08300010 | 李在   | 男   | 1991-10-01 | Rj0801  |           0
 08300012 | 葛畅   | 男   | 1990-08-08 | Rj0801  |           0
 08300015 | 刘晶   | 女   | 1990-05-22 | Rj0801  |           0
 08300020 | 杨敏   | 女   | 1989-01-08 | Rj0801  |           0
 08300030 | 胡贤斌 | 男   | 1990-10-08 | Rj0801  |           0
 08300048 | 赵鸿泽 | 男   | 1989-06-06 | Rj0802  |           0
 08300050 | 王威   | 男   | 1990-06-10 | Rj0802  |           0
 08300067 | 赵玮   | 女   | 1990-08-21 | Rj0803  |           0
 08300075 | 王娜娜 | 女   | 1991-09-23 | Rj0803  |           0
 08300088 | 秦键   | 男   | 1989-03-01 | Rj0803  |           0
 08300100 | 田邦仪 | 女   | 1990-02-26 | Rj0804  |           0
 08300148 | 赵心砚 | 男   | 1991-04-25 | Rj0805  |           0
 08300150 | 杨青   | 女   | 1989-11-15 | Rj0805  |           0
 08300160 | 杨玲玲 | 女   | 1990-12-12 | Rj0806  |           0
(14 行记录)

查询所有男同学的学号、姓名、出生日期

PostgreSQL SQL Dialect
SELECT (sno, sname, sbirthday) FROM student WHERE ssex LIKE '男';

查询结果：

Text Only

             row
------------------------------
 (08300010,李在,1991-10-01)
 (08300012,葛畅,1990-08-08)
 (08300030,胡贤斌,1990-10-08)
 (08300048,赵鸿泽,1989-06-06)
 (08300050,王威,1990-06-10)
 (08300088,秦键,1989-03-01)
 (08300148,赵心砚,1991-04-25)
(7 行记录)

在基本表 student 中增加 addr：varchar(20)列，然后将其长度由 20 改为 25

PostgreSQL SQL Dialect
ALTER TABLE student ADD COLUMN addr varchar(20);

PostgreSQL SQL Dialect
ALTER TABLE student ALTER COLUMN addr type varchar(25);

在基本表 student 中增加 register_date：date 列，并为其设置默认值为当前系统时间，再删除该列

PostgreSQL SQL Dialect
ALTER TABLE student ADD COLUMN register_date date DEFAULT CURRENT_TIMESTAMP;

PostgreSQL SQL Dialect
ALTER TABLE student DROP COLUMN register_date;

在基本表 student 中为 age: integer 列，增加默认值为 18

PostgreSQL SQL Dialect
ALTER TABLE student ADD COLUMN age integer DEFAULT 18;

在基本表 sc student 中将 sname 设置为唯一值（unique）

~~你妈的 sc 表哪来的 sname~~

PostgreSQL SQL Dialect
ALTER TABLE student ADD CONSTRAINT unique_sname UNIQUE (sname);

基本表 ~~course~~ sc 中创建索引：（sno，cno DESC）

~~你妈的 course 表哪来的 sno~~

PostgreSQL SQL Dialect
CREATE INDEX course_index ON sc (sno, cno DESC);

在基本表 student 中增加约束条件：男生年龄小于 23 岁，女生年龄小于 21 岁。

PostgreSQL SQL Dialect
ALTER TABLE student ADD CONSTRAINT age_constraint CHECK(ssex LIKE '男' AND age < 23 OR ssex LIKE '女' AND age < 21 );

创建视图 View_80，存放成绩高于 80 分的选课信息, 显示学号、课程号和成绩，使用 with check option 选项。

PostgreSQL SQL Dialect
CREATE VIEW View_80 AS SELECT sno, cno, grade FROM sc WHERE grade > 80 WITH CHECK OPTION;

在视图 View_80 中查询成绩高于 90 的选课信息。
PostgreSQL SQL Dialect
1
SELECT * FROM View_80 WHERE grade > 90;
在视图 View_80 中依次插入如下元组：

08301168，810011，87（可插入成功）会违反外键约束，hai shi

08301167，810011，78（插入不成功）
PostgreSQL SQL Dialect
1 2
INSERT INTO View_80 VALUES ('08301168', '810011', 87); INSERT INTO View_80 VALUES ('08301167', '810011', 78);
在视图 View_80 中依次修改如下元组：

将（08301168，810011）所对应的成绩改为 90；（可修改成功）

将（08301168，810011）所对应的成绩改为 70；（修改不成功）
PostgreSQL SQL Dialect
1 2
UPDATE View_80 SET grade=90 WHERE sno='08301168' AND cno='810011'; UPDATE View_80 SET grade=70 WHERE sno='08301168' AND cno='810011';
在视图 View_80 中删除如下元组：sno=08301168，cno=810011
PostgreSQL SQL Dialect
1
DELETE FROM View_80 WHERE sno='08301168' AND cno='810011';
查询所有在“1980-01-01”之前出生的女同学的学号、姓名、性别、出生日期
查询所有姓“李”的男同学的学号、姓名、性别、出生日期
查询所有用英文授课的教师号、姓名及英语授课的门数
查询所有职称不是“讲师”的教师号、姓名、职称
查询虽然选修了课程，但未参加考试的所有同学的学号
查询所有考试不及格的同学的学号、成绩，并按成绩降序排列
查询在 1970 年出生的教师号、姓名、出生日期
查询各个课程号的选课人数
查询讲授 2 门课以上的教师号
查询选修了 800001 课程的学生平均分数、最低分数和最高分数
查询 1960 年以后出生的，职称为讲师的教师的姓名、出生日期，并按出生日期升序排列。

四、复杂数据查询 30条

五、用数据操作语言 DML 完成对表的更新操作 7条

六、存储过程 6条

创建一个能向学生表 student 中插入一条记录的存储过程 insert_student，该过程需要 5 个参数，分别用来传递学号、姓名、性别、出生日期、班级号。写出调用存储过程 insert_student 的 SQL 语句，向数据表 student 中插入一个新同学，并提供相应的实参值。

PostgreSQL SQL Dialect
CREATE OR REPLACE PROCEDURE insert_student(sno text, sname text, ssex text, sbirthday date, classno text) AS
$$
BEGIN
 INSERT INTO student(sno, sname, ssex, sbirthday, classno) VALUES (sno, sname, ssex, sbirthday, classno);
END;
$$
LANGUAGE plpgsql;

PostgreSQL SQL Dialect
CALL insert_student('123123', 'sb', '男', '2023-05-30', 'Rj0801');

创建一个向课程表 course 中插入一门新课程的存储过程 insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为 4，即当调用存储过程 insert_course 时，未给第三个参数“学分”提供实参值时，存储过程将按默认值 4 进行运算。调用存储过程 insert_course，向课程表 course 中插入一门新课程。分两种情况（给出第三个参数和未给出第三个参数）写出相应的 SQL 命令，并比较结果。

PostgreSQL SQL Dialect
CREATE OR REPLACE PROCEDURE insert_course(cno text, cname text, ccredit smallint DEFAULT 4) AS
$$
BEGIN
 INSERT INTO course(cno, cname, ccredit) VALUES (cno, cname, ccredit);
END;
$$
LANGUAGE plpgsql;

PostgreSQL SQL Dialect
CALL insert_course('321', '123', cast(3 as smallint));

创建一个名称为 query_student 的存储过程，该存储过程的功能是从数据表 student 中根据学号查询某一同学的姓名、性别、出生日期、班级号。调用存储过程 query_student，查询学号为“08301050”的姓名、性别、出生日期、班级号，并写出完成此功能的 SQL 命令。

PostgreSQL SQL Dialect
CREATE OR REPLACE PROCEDURE query_student(qsno text) AS
$$
DECLARE
 _sname text;
 _ssex text;
 _sbirthday date;
 _classno text;
BEGIN
 SELECT sname, ssex, sbirthday, classno INTO _sname, _ssex, _sbirthday, _classno FROM student WHERE sno=qsno;
 RAISE NOTICE 'sname: %, ssex: %, sbirthday: %, classno: %', _sname, _ssex, _sbirthday, _classno;
END;
$$
LANGUAGE plpgsql;

PostgreSQL SQL Dialect
CALL query_student('08300010');

建立存储过程，输出平均成绩大于 80 分的学生的姓名、性别、年龄和平均成绩。调用该存储过程，并输出相应的结果。

PostgreSQL SQL Dialect
CREATE OR REPLACE PROCEDURE proc4() AS
$$
DECLARE
 curs CURSOR FOR SELECT sname, ssex, age, avg(grade) AS avg_grade FROM (student NATURAL JOIN sc) GROUP BY sname, ssex, age HAVING avg(grade) > 80;
BEGIN
 FOR record IN curs LOOP
     RAISE NOTICE 'sname: %, ssex: %, age: %, avg_grade: %', record.sname, record.ssex, record.age, record.avg_grade;
 END LOOP;
END;
$$
LANGUAGE plpgsql;

写存储过程显示所有选择了给定学生姓名选择的全部课程的学生的学号、姓名、选课数、平均成绩、总学分

PostgreSQL SQL Dialect
CREATE OR REPLACE PROCEDURE proc5(qsname text) AS
$$
BEGIN
 SELECT sno, sname, count(cno), avg(grade), totalcredit
 FROM student NATURAL JOIN sc GROUP BY sno, sname
 WHERE exists SELECT * FROM;
END;
$$
LANGUAGE plpgssql;

七、触发器 8条

创建一个当向学生表 student 中插入一新同学时能自动列出全部同学信息的触发器 display_trigger。执行存储过程 insert_student，向学生表中插入一个新同学，看触发器 display_trigger 是否被触发。

PostgreSQL SQL Dialect
CREATE OR REPLACE FUNCTION display_student() RETURNS trigger AS
$$
DECLARE
 curs CURSOR FOR SELECT * FROM student;
BEGIN
 FOR record IN curs LOOP
     RAISE NOTICE '%', record;
 END LOOP;
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

PostgreSQL SQL Dialect
CREATE TRIGGER display_trigger AFTER INSERT
 ON student
 EXECUTE FUNCTION display_student();

PostgreSQL SQL Dialect
CALL insert_student('123', '23232', '女', '1000-01-02', 'Rj0801');

创建一个触发器，当向学生表 student 中插入一新同学时能自动更新（增加 1）class 班级表中该生所在班级的总人数。

PostgreSQL SQL Dialect
CREATE OR REPLACE FUNCTION update_class_student_cnt() RETURNS trigger AS
$$
DECLARE
 cnt integer;
BEGIN
 -- RAISE NOTICE '%', NEW;
 SELECT count(*) FROM student INTO cnt WHERE classno = NEW.classno;
 RAISE NOTICE '%', cnt;
 UPDATE class SET studentnumber = cnt WHERE classno = NEW.classno;
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

不对，nmd 是增加一，不是根据 student 表更新

PostgreSQL SQL Dialect
CREATE OR REPLACE FUNCTION update_class_student_cnt() RETURNS trigger AS
$$
DECLARE
 cnt integer;
BEGIN
 -- RAISE NOTICE '%', NEW;
 -- SELECT count(*) FROM student INTO cnt WHERE classno = NEW.classno;
 -- RAISE NOTICE '%', cnt;
 UPDATE class SET studentnumber = studentnumber + 1 WHERE classno = NEW.classno;
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

PostgreSQL SQL Dialect
CREATE TRIGGER update_class_student_cnt_trigger AFTER INSERT
 ON student FOR EACH ROW
 EXECUTE FUNCTION update_class_student_cnt();

略
略
略
写一个触发器阻止将学生成绩降低

RAISE EXCEPTION 'xxx' 即可

来自 New Bing：

PostgreSQL SQL Dialect
-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_update_trigger_func() RETURNS TRIGGER AS $$
BEGIN
  -- 假设 score 表有 id 和 grade 两个字段
  IF NEW.grade < OLD.grade THEN
    RAISE EXCEPTION 'Cannot decrease the grade';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER check_update_trigger BEFORE UPDATE ON score FOR EACH ROW EXECUTE PROCEDURE check_update_trigger_func();

2023年3月28日
分类于 School/PostgreSQL
需要 1 分钟阅读时间

Debian服务器配置Postgresql远程访问

以华为云 HECS 云耀云服务器 Debian 11.1.0 64-bit 为例。

首先进行安装：

Bash
1	`apt-get install postgresql`

在安装完成后默认用户名为 postgres，密码为空。

但是目前并不能直接通过 psql -U postgres 来访问数据库，因为 PostgreSQL 在本地连接时的默认认证方式为 peer，会从操作系统内核中获取当前用户名作为允许连接的用户名进行认证。

这可以通过修改 /etc/postgresql/13/main/pg_hba.conf 来改变：

Diff
#
# Database administrative login by Unix domain socket
-local   all             postgres                                peer
+local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

+host    all             all             0.0.0.0/0               trust

顺便在最后添加了一个允许所有IP访问。

然后修改 /etc/postgresql/13/main/postgresql.conf

Diff
1 2	`-#listen_addresses = 'localhost' # what IP address(es) to listen on; +listen_addresses = '*' # what IP address(es) to listen on;`

之后可以使用 systemctl restart postgresql 来重启服务。

可以使用 systemctl enable postgresql 启用开机启动服务。

现在就可以在其他的机器上使用 psql -h xxx.xxx.xxx.xxx -U postgres 来访问。

注：这里的配置只用于学习用途，不保证生产环境下的安全问题。

2023年3月21日
分类于 School/PostgreSQL
需要 1 分钟阅读时间

PostgreSQL 一、创建、删除表

你可以通过指定表名和所有列名及其类型来创建一个新表，下面创建一个 weather 表：

PostgreSQL SQL Dialect
CREATE TABLE weather (
    city       varchar(80),
    temp_lo    int,         -- low temperature
    temp_hi    int,         -- high temperature
    prcp       real,        -- precipitation
    date       date
);

psql 会将 ; 视为一个完整语句的结束。

在 SQL 命令中可以自由的使用空白（空格、制表符、换行），-- 开头的为注释。

PostgreSQL 支持标准 SQL 类型 int、smallint、real、double precision、char(N)、varchar(N)、date、time、timestamp、interval，以及一些方便的工具类型和几何类型，同时也支持用户添加类型，详细的见数据类型一篇。

如果要删除 weather表：

PostgreSQL SQL Dialect
DROP TABLE weather

参考

PostgreSQL: Documentation: 15: 2.3. Creating a New Table

2023年3月4日
分类于 School/PostgreSQL
需要 2 分钟阅读时间

PostgreSQL安装

T13:53:00+08:00

官网：PostgreSQL: The world's most advanced open source database

相关信息：

PostgreSQL 与 MySQL 相比，优势何在？ - 知乎 (zhihu.com)

一、下载

进入 Download 页面，选择你的系统：

二、安装

一路下一步。

配置访问数据库需要的密码：

配置访问数据库使用的端口（一般不用改）：

然后继续一路下一步，最后不用选择在退出时打开 Stack Builder。