PostgreSQL学习总结

发表于 更新于 分类于 本文字数： 2.2k 阅读时长 ≈ 2 分钟

PG数据库：

理解：对于PostgreSQL数据库，我个人理解为和MySQL数据库很相识，比MySQL功能多，在pg数据库中，用户可以指定表集合，这点和mongodb很像。

模式(架构)：是指定的表集合，它还可以包含视图、索引、序列、数据类型、运算符和函数.

1
创建模式:create schema <schemaname>

架构作用:

1、模式有助于多用户使用一个数据库,而不会互相干扰

2、将数据库对象组织成逻辑组,使其更易于管理

3、可以将第三方模式放入单独的模式中,以避免与其他对象的名称相冲突

PostgreSQL中的连接类型：

  • 内连接（inner join）显示两个集合共有部分（交集）

    select table1.columns,table2.columns from table1 inner join table2 on table1.common_filed = table2.common_filed;

  • 左外连接（left outer join）显示左集合的全部，右集合不满足的用空补全

    select table1.columns,table2.columns from table1 left outer join table2 on table1.common_filed = table2.common_filed;

  • 右外连接（right outer join）显示右集合的全部，左集合不满足的用空补全

    select table1.columns,table2.columns from table1 right outer join table2 on table1.common_filed = table2.common_filed;

  • 全连接（full outer join）显示两个集合的全部，不满足条件的用空补全

    select table1.columns,table2.columns from table1 full outer join table2 on table1.common_filed = table2.common_filed;

  • 跨连接（cross join）将第一个表的每一行与第二个表的每一行相匹配。

    select coloums from table1 cross join table2;

  • having子句和group by子句组合使用，用于选择函数结果满足某些条件的特定行。

普通用户创建与登陆：

1、登陆超级用户：sudo -u postgres psql

2、创建用户：create user dbuser encrypted password ‘test’;

3、授权：alter user dbuser with createdb; alter user dbuser with login;

4、创建dbuser用户的数据集：create database dbusers with owner=’dbuser’;

5、退出\q

6、普通用户登陆：psql -U dbuser -d dbusers -h localhost

触发器

1
2
3
4
5
6
create or replace function auditlogfunc() returns trigger as $example_table$
begin
	insert into audit(emp_id,entry_date) values (new.ID, current_timestamp);
	return new
end;
$example_table$ language plpgsql;
1
create trigger example_trigger after insert on company for each row execute procedure auditlogfunc();

PostgreSQL触发器目的：

1、验证输入数据

2、执行业务规则

3、为不同文件中新插入的行生成唯一值

4、写入其他文件以急性审计跟踪

5、从其他文件查询交叉引用目的

6、访问系统函数

7、将数据复制到不同的文件以实现数据一致性

触发器的查询、删除：

  • 列出当前数据库的所有触发器：select * from pg_trigger;
  • 列出某张表上的触发器：SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname=’<tablename>’;
  • 删除触发器：drop trigger ;

查询创建的函数：

1
2
3
4
5
6
7
8
9
10
11
SELECT 
	pg_proc.proname AS "函数名称",
	pg_type.typname AS "返回值数据类型",
	pg_proc.pronargs AS "参数个数" 
FROM 
	pg_proc 
	JOIN pg_type ON (pg_proc.prorettype = pg_type.oid) 
WHERE 
	pg_type.typname != 'void' 
	AND 
	pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');

索引

用于加速从数据库中检索数据

创建索引:create index index_name on table_name;

响应时间的计算模型：
响应时间=网络传输时间（请求）+服务器处理时间（一层或是多层）+网络传输时间（响应）+页面前段解析时间