DDL（数据定义语言）

数据库相关

-- 创建数据库
create database testdb;
-- 更改数据库
alter database testdb rename to mydb;
-- 删除数据库
drop database mydb;

表相关

-- 创建表
create table course (
    course_id char(4) primary key ,
    course_name varchar(20) not null unique ,
    course_type varchar(10) null check ( course_type in ('基础课', '专业课', '选修课') ),
    course_credit smallint null,
    course_period smallint null,
    test_method char(4) not null default '闭卷考试'
);

-- 修改表结构
ALTER TABLE student ADD selfinfo varchar(100);
ALTER TABLE student DROP selfinfo;
alter table course rename test_method to exam_method;
alter table course alter column exam_method type char(5);

-- 删除表
drop table course;

索引相关

-- 创建索引
create index course_name_idx on course(course_name);
-- 修改索引
alter index course_name_idx rename to c_n_idx;
-- 删除索引
drop index c_n_idx;

DML（数据操作语言）

-- 插入数据
insert into course(course_id, course_name, course_type, course_credit, course_period) VALUES
    ('001', '数据库', '基础课', '4', '36');
insert into course VALUES
    ('002', '操作系统', '基础课', '4', '36', '闭卷考试');
-- 批量插入数据
insert into course VALUES
    ('003', 'ARM', '基础课', '4', '36', '闭卷考试'),
    ('004', '计网', '基础课', '4', '36', '闭卷考试');

-- 更新数据
update course set course_credit=5, course_period=48 where course_name='计网';

-- 删除数据
delete from course where course_name='计网';

DQL（数据查询语言）

基础

# 去重
select DISTINCT workaddress as 工作地址 from emp;

# NULL 和 NOT NULL 查询
select * from emp where idcard is null;
select * from emp where idcard is not null;

# BETWEEN ... AND ...
select * from emp where age between 15 and 20;

# in
select * from emp where age in(18, 20, 40);

# LIKE 与 通配符
select * from emp where name like '__';
select * from emp where idcard like '%X';

聚合函数

==注意 : NULL值是不参与所有聚合函数运算的。==

# count
select count(*) from emp;
select count(idcard) from emp;

# avg max min
select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;

# sum
select sum(age) from emp where workaddress = '西安';

分组查询

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组 之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以

select gender, count(*) from emp group by gender;
select gender, avg(age) from emp group by gender;

# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count from emp where age < 45 group by workaddress having count(*) >= 3;

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB

排序查询

排序方式 ：

• ASC : 升序(默认值)

• DESC: 降序

# 升序和降序
select * from emp order by age asc;
select * from emp order by age desc;
select * from emp order by entrydate desc;

#  根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from emp order by age, entrydate desc;
select * from emp order by age asc, entrydate desc;

注意事项：

• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

分页查询

在 PostgreSQL 中，您可以使用 LIMIT 和 OFFSET 子句来实现分页查询。LIMIT 用于限制查询结果的数量，OFFSET 用于指定从哪个位置开始返回结果。这里有一个简单的示例：

假设您有一个名为 employees 的数据表，并且您想要查询第 2 页的数据，每页显示 10 条记录。您可以使用以下 SQL 语句实现分页查询：

SELECT * FROM employees
ORDER BY id  -- 按照某个字段排序，这里以 id 为例
LIMIT 10     -- 每页显示 10 条记录
OFFSET 10;   -- 跳过第一页的 10 条记录，从第 11 条记录开始

在这个示例中，我们首先使用 ORDER BY 对结果按照 id 字段进行排序。接着，我们使用 LIMIT 10 限制查询结果为 10 条记录。最后，我们使用 OFFSET 10 跳过第一页的 10 条记录，从第 11 条记录开始返回结果。

您可以根据实际需求调整 LIMIT 和 OFFSET 的值以实现不同的分页查询需求。

---

由于 PostgreSQL 的分页查询语法与 MySQL 略有不同，以上是 ChatGPT 4.0 的回答，

# 查询第1页员工数据, 每页展示10条记录
select * from emp limit 10 offset 0;
select * from emp limit 10;

# 查询第2页员工数据, 每页展示10条记录 
select * from emp limit 10 offset 10;

综合练习

-- 查询年龄为20,21,22,23岁的女员工信息
select * from emp where gender = '女' and age in (20, 21, 22, 23);

-- 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';

-- 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数
select gender, count(*) 人数 from emp where age < 60 group by gender; 

-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
select name, age, entrydate from emp where age <= 35 order by age asc, entrydate desc;

-- 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
select * from emp where gender = '男' and (age between 20 and 40) order by age asc, entrydate asc limit 5

执行顺序

-- from 先于 where 执行，所以 e.age 可以使用
select name, age from emp e where e.age > 15 order by age asc;

-- select 先于 order 执行，所以 eage 可以使用
select name, age eage from emp e where e.age > 15 order by eage asc;

DCL（数据控制语言）

create user User_Employee_2021090906016 with
    nosuperuser
    nocreatedb
    nocreaterole
    noreplication
    login
    inherit
    connection limit -1
    password '123456';

create user User_Admin_2021090906016 with
    nosuperuser
    nocreatedb
    nocreaterole
    noreplication
    login
    inherit
    connection limit -1
    password '123456';

grant select on Department_2021090906016 to user_employee_2021090906016;
grant select, update on Employee_2021090906016 to user_employee_2021090906016;
grant select on Task_2021090906016 to user_employee_2021090906016;
grant select on Assessment_2021090906016 to user_employee_2021090906016;

revoke select on Department_2021090906016 from user_employee_2021090906016;

实测 pgsql 里没有 deny 语句

约束

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致 性和完整性	FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

create table tb_user(
    id serial primary key,
    name varchar(10) not NULL unique,
    age int check(age > 0 AND age <= 120),
    status char(1) default '1',
    gender char(1)
);

COMMENT ON COLUMN tb_user.id IS 'ID唯一标识';
COMMENT ON COLUMN tb_user.name IS '姓名';
COMMENT ON COLUMN tb_user.age IS '年龄';
COMMENT ON COLUMN tb_user.status IS '状态';
COMMENT ON COLUMN tb_user.gender IS '性别';

外键

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

ALTER TABLE emp DROP CONSTRAINT fk_emp_dept_id;

在 PostgreSQL 中，您不能直接删除外键约束，您需要通过删除约束名称来完成此操作

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行 为有以下几种:

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则 也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表 中该外键值为null（这就要求该外键允许取null）。
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

-- CASCADE
alter table emp add constraint fk_emp_dept foreign key (dept_id)  references dept(id) on delete cascade on update cascade

-- SET NULL
alter table emp add constraint fk_emp_dept foreign key (dept_id)  references dept(id) on delete set null on update set null

多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结 构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

一对多

• 案例: 部门 与 员工的关系
• 关系: 一个部门对应多个员工，一个员工对应一个部门
• 实现: 在多的一方建立外键，指向一的一方的主键

多对多

• 案例: 学生 与 课程的关系
• 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

• 案例: 用户 与 用户详情的关系
• 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另 一张表中，以提升操作效率
• 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

连接查询

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接： 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名

-- 多表查询 -- 笛卡尔积
select * from emp, dept;

表的别名:

1. tablea as 别名1 , tableb as 别名2 ;
2. tablea 别名1 , tableb 别名2 ;

注意事项：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字 段。

内连接

-- 隐式内连接（where)
select * from emp, dept where emp.dept_id = dept.id;
select e.name, d.name from emp e, dept d where e.dept_id = d.id; /* 起了别名后不能再使用原来的名字 */

-- 显示内连接（inner join）
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

左外连接和右外连接

-- 查询emp表的所有数据, 和对应的部门信息（左外连接）
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

-- 查询dept表的所有数据, 和对应的员工信息(右外连接)
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 右外连接转换为左外连接
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺 序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

对于自连接查询，可以是内连接查询，也可以是外连接查询。

-- 查询员工及其所属领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id;
select a.name, b.name from emp a left join emp b on a.managerid = b.id;

注意事项: 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

-- union all, 直接连接（不去重）
select * from emp where salary < 5000
union all
select * from emp where age > 50;

-- union, 去重
select * from emp where salary < 5000
union
select * from emp where age > 50;

子查询

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。 常用的操作符：= <> > >= < <=

-- 查询 "销售部" 的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 查询在 "方东白" 入职之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白')

列子查询

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

-- 查询 "销售部" 和 "市场部" 的所有员工信息
select * from emp where emp.dept_id in (select id from dept where name in ('销售部', '市场部'));

-- 查询比 财务部 所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id in (select id from dept where name = '财务部'));

-- 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id in (select id from dept where name = '研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。 常用的操作符：= 、<> 、IN 、NOT IN

-- 查询与 "张无忌" 的薪资及直属领导相同的员工信息
select salary, managerid from emp where name = '张无忌';
select * from emp where (salary, managerid) = (12500, 1);
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

--  查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
-- a.查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name in ('鹿杖客', '宋远桥'); 
-- b.查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name in ('鹿杖客', '宋远桥'));

-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a.入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- b.查询这部分员工, 对应的部门信息
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

-- 不使用子查询也行，下面是我想到的 SQL
select * from emp e left join dept d on e.dept_id = d.id where e.entrydate > '2006-01-01';

综合练习

-- 1.查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;

-- 2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

-- 3.查询拥有员工的部门ID、部门名称
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;

-- 4.查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;

-- 5.查询所有员工的工资等级
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;

-- 6.查询 "研发部" 所有员工的信息及工资等级
select t.* from (emp e left join salgrade s on e.salary between s.losal and s.hisal) t join dept on (t.dept_id = dept.id and dept.name = '研发部');
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal ) and d.name = '研发部';

-- 7.查询 "研发部" 员工的平均工资
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

-- 8.查询工资比 "灭绝" 高的员工信息。
select * from emp where salary > (select salary from emp where name = '灭绝');
-- 自连接, my answer
select e1.* from emp e1, emp e2 where e2.name = '灭绝' and e1.salary > e2.salary;

-- 9.查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);

-- 10.查询低于本部门平均工资的员工信息
-- my answer
select dept_id, avg(salary) from emp group by dept_id;
select e.*, t.avg from emp e, (select dept_id, avg(salary) avg from emp group by dept_id) t where e.dept_id = t.dept_id and e.salary < t.avg;
-- Jury's answer
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );

-- 11.查询所有的部门信息, 并统计部门的员工人数
-- my answer,存在BUG: 员工数量为0的部门查询不到
select dept_id, count(*) from emp group by dept_id;
select d.*, t.count from dept d, (select dept_id, count(*) from emp group by dept_id) t where d.id = t.dept_id
-- Jury's answer
select d.id, d.name, ( select count(*) from emp e where e.dept_id = d.id ) 人数 from dept d;

-- 12.查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;

事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐 式的提交事务。

事务操作

在 PostgreSQL 中，您可以通过 SHOW TRANSACTION ISOLATION LEVEL 查询事务隔离级别以了解事务是否自动提交。以下是将 MySQL 的 SQL 语句转换为 PostgreSQL 支持的 SQL 语句：

SHOW TRANSACTION ISOLATION LEVEL;

如果返回的隔离级别是 "READ COMMITTED"，那么 PostgreSQL 默认使用自动提交。

begin; /* 或者 start transaction */
-- 1. 查询张三余额
select * from account where name = '张三';

-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

-- 如果执行过程中报错, 则回滚事务
-- rollback;

-- 如果正常执行完毕, 则提交事务
commit;

-- 恢复
update account set money = 2000 where name = '张三' or name = '李四';

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提 交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称ACID。

并发事务问题

• 赃读：一个事务读到另外一个事务还没有提交的数据。
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 "幻影"。

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	未解决	未解决	未解决
Read committed（PostgreSQL 默认）	解决	未解决	未解决
Repeatable Read（MySQL 默认）	解决	解决	未解决
Serializable	解决	解决	解决

注意：事务隔离级别越高，数据越安全，但是性能越低。

不可重复读 复现示例：

-- 显示当前会话的默认隔离级别
show transaction isolation level ;

要更改当前会话的默认隔离级别为 SERIALIZABLE，您可以使用 SET 语句，而不需要在事务中进行设置。以下是将当前会话的默认隔离级别更改为 SERIALIZABLE 的示例：

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

在执行此语句后，当前会话中启动的所有新事务将使用 SERIALIZABLE 隔离级别。要查看当前会话的默认隔离级别，您可以使用以下查询：

SHOW default_transaction_isolation;

这将返回当前会话的默认隔离级别，如 SERIALIZABLE。

请注意，这里设置的默认隔离级别仅适用于当前会话。当会话结束时，这个设置将失效。如果您想要为整个数据库设置默认的隔离级别，需要更改 postgresql.conf 配置文件中的 default_transaction_isolation 设置，并重启数据库服务。

如果只需要在一次事务中更改隔离级别的话，可以这样写：

BEGIN; -- 开始一个新事务
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置事务隔离级别为 SERIALIZABLE
-- 在这里执行您的事务操作（如查询、插入、更新等）
COMMIT; -- 提交事务

视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视 图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作 就落在创建这条SQL查询语句上。

基本语法

-- 创建视图
create or replace view stu_view as
    select id, name from student where id <= 10;

-- 查询视图定义
select pg_get_viewdef('stu_view', true);
select viewname, definition from pg_views where viewname = 'stu_view' and schemaname = 'public';

-- 查询视图
select * from stu_view where id < 3;

-- 修改视图
create or replace view stu_view as select id,name,no from student where id <= 10;
-- 在 PostgreSQL 中，您不能直接修改现有视图的定义，而是需要使用 CREATE OR REPLACE VIEW 语句来重新创建视图
-- alter view stu_view as select id,name from student where id <= 10;

-- 删除视图
drop view if exists stu_view;

检查选项

当使用 WITH CHECK OPTION 子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如 插 入，更新，删除，以使其符合视图的定义。 MySQL 允许基于另一个视图创建视图，它还会检查依赖视 图中的规则以保持一致性。为了确定检查的范围，MySQL 提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。

create view v1 as
    select id, name from student where id <= 20;

create view v2 as
    select id, name from v1 where id >= 10 with cascaded check option ;

insert into v1 values (25, 'pgsql'); /* 成功 */
insert into v2 values (15, 'pgsql'); /* 失败 */

create view v3 as
    select id, name from v2 where id >= 5 with local check option ;

insert into v3 values (8, 'pgsql'); /* 失败 */

create or replace view v3 as
    select id, name from v1 where id >= 5 with local check option ;

insert into v3 values (8, 'pgsql'); /* 成功 */

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一 项，则该视图不可更新：

​ A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

​ B. DISTINCT

​ C. GROUP BY

​ D. HAVING

​ E. UNION 或者 UNION ALL

视图作用

1. 简单

   视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视 图，从而使得用户不必为以后的操作每次指定全部的条件

2. 安全

   数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3. 数据独立

   视图可帮助用户屏蔽真实表结构变化带来的影响

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发 人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

基本语法

在 PostgreSQL 中，函数（Function）是一种类似于存储过程的可重用对象，它们通常用于封装一组 SQL 语句以执行特定任务、实现复杂的业务逻辑或封装常用操作。与存储过程不同，函数需要返回一个值。您可以使用 PL/pgSQL（一种类似于 SQL 的过程性语言）或其他过程性语言创建函数。

创建函数的基本语法如下：

CREATE [OR REPLACE] FUNCTION function_name (parameter_name parameter_type [,...])
RETURNS return_type
LANGUAGE plpgsql
AS $$
-- 函数的 PL/pgSQL 代码
$$;

其中：

• CREATE [OR REPLACE]: 如果指定了 OR REPLACE，在创建函数时，如果同名函数已存在，则会用新的定义替换旧的定义。
• FUNCTION function_name: 函数的名称。
• parameter_name parameter_type: 函数的输入参数，可以有多个，用逗号分隔。参数有名称和类型。
• RETURNS return_type: 函数返回值的类型。
• LANGUAGE plpgsql: 指定函数使用的编程语言。在这里，我们使用 PL/pgSQL。
• AS $$: 函数的 PL/pgSQL 代码部分的开始。
• $$;: 函数的 PL/pgSQL 代码部分的结束。

要调用函数，您可以在 SQL 查询中像调用常规函数那样调用它：

SELECT function_name(parameter_values);

其中 function_name 是函数的名称，parameter_values 是传递给函数的参数值。

以下是一个简单的函数示例，该函数将两个整数相加并返回结果：

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN a + b;
END;
$$;

调用此函数：

SELECT add_numbers(3, 5);

这将返回 3 + 5 的结果，即 8。

---

-- 存储过程基本语法
-- 创建
CREATE OR REPLACE FUNCTION p1()
RETURNS bigint AS $$
BEGIN
    RETURN (SELECT count(*) FROM student);
END;
$$ LANGUAGE plpgsql;

-- 调用
select p1();

-- 删除
drop function if exists p1;

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前 (BEFORE) 或之后 (AFTER) ，触 发并执行触发器中定义的 SQL 语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。MySQL 不支持语句级触发，但 PostgreSQL 支持语句级触发。

-- 插入数据触发器
create or replace function tb_user_insert_trigger_function()
returns trigger as $$
begin
    insert into user_logs(operation, operate_time, operate_id, operate_params)
    values (tg_op, now(), new.id, concat('insert values: ', 'id = ', new.id, ', name = ', new.name, ', phone = ', new.phone, ', email = ', new.email));
    return null;
end;
$$ language plpgsql;

create trigger tb_insert_tri
    after insert on tb_user for each row
    execute function tb_user_insert_trigger_function();

drop trigger if exists tb_insert_tri on tb_user;

insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
values (25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

-- 更新数据触发器
create or replace function tb_user_update_trigger_function()
returns trigger language plpgsql as $$
begin
    insert into user_logs(operation, operate_time, operate_id, operate_params)
    values (tg_op, now(), new.id, concat('old values: ', 'id = ', old.id, ', name = ', old.name, ', phone = ', old.phone, ', email = ', old.email,
            ' new values: ', 'id = ', new.id, ', name = ', new.name, ', phone = ', new.phone, ', email = ', new.email));
    return new;
end;
$$;

create trigger tb_update_tri
    after update on tb_user for each row
    execute function tb_user_update_trigger_function();

update tb_user set name = 'owo' where id = 25;

-- 删除数据触发器
create or replace function tb_user_delete_trigger_function()
returns trigger language plpgsql as $$
begin
    insert into user_logs(operation, operate_time, operate_id, operate_params)
    values (tg_op, now(), old.id, concat('delete values: ', 'id = ', old.id, ', name = ', old.name, ', phone = ', old.phone, ', email = ', old.email));
    return old;
end;
$$;

create trigger tb_delete_tri
    after delete on tb_user for each row
    execute function tb_user_delete_trigger_function();

delete from tb_user where id = 25;

游标

游标在 PostgreSQL 中是一个数据库查询，它允许你从一个大的结果集中一次获取一行记录。这对于处理大量数据非常有用，因为你不需要一次性加载所有数据。

在 PostgreSQL 中，游标通常在 PL/pgSQL 代码块（如函数或存储过程）中声明和使用，或者在事务中声明使用

-- 游标案例1
create or replace function cursorDemo()
returns boolean language plpgsql as
$$
declare
    emp_cur cursor for select * from emp;
    re record;
begin
    open emp_cur;
    loop
        fetch next from emp_cur into re;
        exit when not found;
        raise notice '%, %', re.name, re.job;
    end loop;
    close emp_cur;
    return true;
end;
$$;

select cursorDemo();

-- 游标案例2
create or replace function cursorDemo2(in str varchar)
returns table(name varchar, job varchar) language plpgsql as
$$
declare
    emp_cur refcursor;
    re record;
begin
    open emp_cur for select * from emp where emp.job = str;
    fetch emp_cur into re;
    while FOUND loop
        name := re.name;
        job := re.job;
        return next;
        fetch emp_cur into re;
    end loop;
    close emp_cur;
end;
$$;

select * from cursorDemo2('职员');

杂项

select now();
SELECT current_time AT TIME ZONE 'Asia/Shanghai';
show time zone ;
SHOW config_file;
SET TIME ZONE 'Asia/Shanghai';

备份与恢复

在 PostgreSQL 中，备份通常使用 pg_dump 或 pg_dumpall 命令行工具进行，而不是通过 SQL 语句。

例如，你可以使用以下命令来备份一个名为 "sample" 的数据库：

pg_dump sample > sample.bak

这个命令将创建一个包含 "sample" 数据库所有数据和结构的 SQL 文件 "sample.bak"。

如果你想备份所有数据库，你可以使用 pg_dumpall 命令：

pg_dumpall > alldbs.bak

这个命令将创建一个包含所有数据库的 SQL 文件 "alldbs.bak"。

请注意，这些命令需要在命令行中运行，而不是在 SQL 查询中。你可能需要具有适当的权限才能运行这些命令，具体取决于你的 PostgreSQL 安装和配置。