--study doc:
--https://www.yiibai.com/postgresql/
--client tool:dbreaver.
create schema app1;

drop table app1.salary;
create table app1.salary (
	emp numeric(5),
	year numeric(4),
	month numeric(2),
	sal decimal(13,3)
);

truncate table salary;

insert into salary values
(90001,2018,01,9100.85),
(90002,2018,01,9600.23),
(90003,2018,01,8800.58);

insert into salary values
(90001,2018,02,9700.85),
(90002,2018,02,9900.23),
(90003,2018,02,8500.58);

insert into salary values
(90001,2018,03,9800.85),
(90002,2018,03,9500.23),
(90003,2018,03,8900.58);

select * from salary;

select emp,avg(sal) as Average_Salary
from salary
group by emp --合并，减少冗余
order by emp desc;

select emp,avg(sal) as Average_Salary
from salary
group by emp --合并，减少荣誉
having emp > 90001 --加过滤条件
order by emp asc;

select * from salary
where emp in(
90001,
90003
) and month = 3;

create table emp(
	emp numeric(5) not null,
	name varchar(10) not null,
	sfz  varchar(18),
	PRIMARY KEY (emp)
);

--使用dbreaver产生mock数据！
truncate table emp;
select * from emp;

select emp.emp,emp."name",
		salary."year",salary."month",salary.sal
from emp
inner join salary
on emp.emp = salary.emp;

select emp.emp,emp."name",
		salary."year",salary."month",salary.sal
from emp --from后的是左表，以左表为基准，右表没有匹配到的为空值
left join salary
on emp.emp = salary.emp;

select emp.emp,emp."name",
		salary."year",salary."month",salary.sal
from emp
right join salary  --salary为右表，以右表为基准
on emp.emp = salary.emp;

--全外连接(本质上是左连接或者右连接)
select emp.emp,emp."name",
		salary."year",salary."month",salary.sal
from emp
full join salary
on emp.emp = salary.emp;

--cross join(笛卡儿积分，得到X × Y行)
select emp.emp,emp."name",
		salary."year",salary."month",salary.sal
from emp
cross join salary

--创建视图
drop view emp_sal;

create view emp_sal as
select emp.emp,emp."name",
		salary."year",salary."month",salary.sal
from emp
right join salary
on emp.emp = salary.emp;

--查询视图
select * from emp_sal;

--PostgreSQL函数(function)也称为PostgreSQL存储过程(store procedure)。
create or replace function get_total()
returns integer as $empsal$ --注意：是returns，要加s！！
declare
	empsal integer;
begin
	select sum(sal) 
	into empsal 
	from salary;
	
	return empsal;
end;
$empsal$ LANGUAGE plpgsql;
--调用函数（存储过程)
select get_total();

--有输入参数(argument)的存储过程
--存储过程，其实和开发语言里的函数一样，有输入参数、也有输出参数，还有函数体.
create or replace function get_year_momth_sal(zyear numeric(4),
											  zemp  numeric(5)
											  )
returns integer as $empsal$
declare
	empsal integer;
begin
	select sum(sal) 
	into empsal 
	from salary
	where year = @zyear and   --输入变量前加@符号实现变量的使用
		  emp = @zemp;
	
	return empsal;
end;
$empsal$ LANGUAGE plpgsql;
--调用函数（存储过程)
select get_year_momth_sal(2018,90001);
--验证下
select sum(sal) from salary where year = 2018 and emp = 90001;

----------begin of触发器练习------------------
--触发器(trigger)
--触发器是一组动作或数据库回调函数
--用于验证输入数据，执行业务规则，保持审计跟踪等
--满足特定条件的时候会自动运行
--SAP SDI抓取HANA实时抓取HANA数据库就是靠的触发器.

--PostgreSQL触发器可用于以下目的：
--验证输入数据。
--执行业务规则。
--为不同文件中新插入的行生成唯一值。
--写入其他文件以进行审计跟踪。
--从其他文件查询交叉引用目的。
--访问系统函数。
--将数据复制到不同的文件以实现数据一致性。
--数据仓库从源系统实时抓数据。

--创建一个记录日志的表：
CREATE TABLE AUDIT(  
    emp numeric(5),  
    erdate TEXT NOT NULL
);

--创建一个保存日志的存储过程:
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$  
    BEGIN  
	    --new.XXX，XXX为所监听的操作的SQL语句的字段.
        INSERT INTO AUDIT(emp, erdate) VALUES (new.emp, current_timestamp);  
        RETURN NEW;   
    END;  
$example_table$ LANGUAGE plpgsql;

--给业务表的insert操作创建触发器:
create trigger sdi_monitor after insert on salary
 FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
 
--对业务表执行insert操作，触发sdi_monitor
insert into salary values
(90001,2018,05,10000.26),
(90002,2018,05,9800.23),
(90003,2018,05,9000.58);
 
--查看日志，确认触发器是否成功触发.
select * from audit;
----------------endof触发器练习----------------


--alias别名
--通常来说，当您执行自联接时，会创建一个临时表。
--别名(Alias)用于为列或表提供临时名称.
--如果alias_name包含空格，则必须将alias_name包含在引号中。
--alias_name仅在SQL语句的范围内有效。
select emp as 工号, name as 姓名 from emp;

--表别名
--多用于多表查询的情况
select e.emp, e.name, s.year, s.month, s.sal
from emp as e, salary as s
where e.emp = s.emp and
	  s.month = 5
order by emp;

--索引
--索引是用于加速从数据库检索数据的特殊查找表。
--数据库索引类似于书的索引(目录)。 
--索引为出现在索引列中的每个值创建一个条目。
--索引有利于select，但会降低insert和update的效率.

--使用CREATE INDEX语句创建索引，指定创建索引的索引名称和表或列名称.
--可创建唯一索引，该索引防止列或列的组合上有一个索引重复的项.

--PostgreSQL中有几种索引类型，如B-tree，Hash，GiST，SP-GiST和GIN等。
--每种索引类型根据不同的查询使用不同的算法。
--默认情况下，CREATE INDEX命令使用B树索引。

--单列索引:
--如果仅在一个表列中创建索引，则将其称为单列索引。
--为emp表的emp列创建名为emp_idx的索引
create index emp_idx
on emp(emp);

--多列索引
--如果通过使用表的多个列创建索引，则称为多列索引。
--CREATE INDEX index_name  
--ON table_name (column1_name, column2_name);

--唯一索引
--创建唯一索引以获取数据的完整性并提高性能。
--它不允许向表中插入重复的值，或者在原来表中有相同记录的列上也不能创建索引
--如果要创建索引的列中有重复值，会提示类似如下错误：
--SQL 错误 [23505]: ERROR: could not create unique index "uni_idx_name"
--  详细：Key (name)=(upidatat) is duplicated.
create unique index uni_idx_name
on emp(name);

--删除
drop index emp_idx;

--什么时候应该避免使用索引？
--应该避免在小表上使用索引。
--不要为具有频繁，大批量更新或插入操作的表创建索引。
--索引不应用于包含大量NULL值的列。
--不要在经常操作(修改)的列上创建索引。


--日期和时间函数(标准函数)
select current_date, current_time,CURRENT_TIMESTAMP;
select localtime;
select localtimestamp;

--union和union all.
--UNION子句/运算符用于组合两个或多个SELECT语句的结果，而不返回任何重复的行
--UNION ALL运算符用于组合两个SELECT语句(包括重复行)的结果。 
--适用于UNION的相同规则也适用于UNION ALL运算符。

--NULL和空值、0值不是一个概念，NULL值未曾赋值的情况.
update salary set sal = null where emp  = 90001;
select * from salary where sal is null;
select * from salary where sal is not null;
select * from salary;

--alter
--用于修改表结构
--还可以使用ALTER TABLE命令在现有表上添加和删除各种约束。

--添加列
alter table emp add city varchar;
alter table emp add provience varchar;
select * from emp;

--删除列
alter table emp drop city;

--修改列的数据类型
alter table emp alter column provience type text;
select * from emp;

------截断表（TRUNCATE TABLE语句）
--TRUNCATE TABLE命令用于从现有表中删除完整的数据。
--TRUNCATE和DROP的区别在于TRUNCATE还保留表结构；
--TRUNCATE和DELETE的区别在于TRUNCATE不扫描表，所以它的速度更快。
--此外，TRUNCATE会立即回收磁盘空间，而不需要后续的VACUUM操作。

--什么时候用drop、truncate、delete，看情况。
truncate table audit;
select * from audit;

-------事务
--事务是对数据库执行的工作单元。事务是以逻辑顺序完成的工作的单位或顺序
--事物的四个标准属性：ACID
--原子性(Atomicity)：确保工作单位内的所有操作成功完成; 否则事务将在故障点中止，以前的操作回滚到其以前的状态。
--一致性(Consistency)：确保数据库在成功提交的事务时正确更改状态。
--隔离性(Isolation)：使事务能够独立运作并相互透明。
--持久性(Durability)：确保在系统发生故障的情况下，提交的事务的结果或效果仍然存在。

--以下命令用于控制事务：
--BEGIN TRANSACTION：开始事务。
--COMMIT：保存更改，或者您可以使用END TRANSACTION命令。
--ROLLBACK：回滚更改。

--事务控制命令仅用于DML命令INSERT，UPDATE和DELETE。 
--创建表或删除它们时不能使用它们，因为这些操作会在数据库中自动提交。

--可以使用BEGIN TRANSACTION或简单的BEGIN命令来开始事务。 
--这样的事务通常会持续下去，直到遇到下一个COMMIT或ROLLBACK命令。 
--但如果数据库关闭或发生错误，则事务也将ROLLBACK。

--COMMIT命令是用于将事务调用的更改保存到数据库的事务命令。
--COMMIT命令自上次的COMMIT或ROLLBACK命令后将所有事务保存到数据库。
--COMMIT命令的语法如下：
--COMMIT;
--or
--END TRANSACTION;

--ROLLBACK命令是用于还原尚未保存到数据库的事务的事务命令。
--自上次发出COMMIT或ROLLBACK命令以来，ROLLBACK命令只能用于撤销事务。
--ROLLBACK命令的语法如下：
--ROLLBACK;

--例子：COMMIT
--先把数据删除
delete from emp where emp in(90006,90007);

BEGIN;
	insert into emp values
	(90006,'韩梅梅','23011000X','广东省'),
	(90007,'李萍萍','532011000X','云南省');
COMMIT;

--如果执行事物的过程中报错，运行一次rollback即可！
rollback;

select * from emp where emp in(90006,90007);

--------锁(lock)
--锁或独占锁或写锁阻止用户修改行或整个表。
--在UPDATE和DELETE修改的行在事务的持续时间内被自动独占锁定。 
--这将阻止其他用户更改行，直到事务被提交或回退。
--用户必须等待其他用户当他们都尝试修改同一行时。 如果他们修改不同的行，不需要等待。 SELECT查询不必等待。
--数据库自动执行锁定。 然而，在某些情况下，必须手动控制锁定。 
--手动锁定可以通过使用LOCK命令完成。 它允许指定事务的锁类型和范围。

--加锁的主要目的是为了保持数据的一致性和程序的稳定性。

--死锁
--当两个事务正在等待彼此完成操作时，可能会发生死锁。

--咨询锁
--PostgreSQL提供了创建具有应用程序定义含义的锁的方法。
--这些称为咨询锁(劝告锁,英文为：advisory locks)。 
--由于系统不强制使用它，因此应用程序正确使用它们。 
--咨询锁可用于锁定针对MVCC模型策略。

--以下实例将表emp锁定，直到事物结束结束、后者回滚或者提交事物
begin;
LOCK table emp in access EXCLUSIVE MODE;

--此时尝试往表中insert数据，将不会成功!!
insert into emp values
(80003,'韩梅梅','23011000X','广东省'),
(80004,'李萍萍','532011000X','云南省');


--解锁:
rollback;

--此时再insert数据，不会报错了。
insert into emp values
(80001,'韩梅梅','23011000X','广东省'),
(80002,'李萍萍','532011000X','云南省');

select * from emp where emp < 90000;

-------子查询(subquery)
--子查询用于返回将在主查询中使用的数据作为进一步限制要检索的数据的条件。
--简单的说，子查询就是将一个查询的结果作为另一个查询的过滤条件值范围的查询。

--子查询必须遵循以下规则：
--子查询必须括在括号中。
--子查询在SELECT子句中只能有一列，除非主查询中有多个列用于比较其所选列的子查询。
--ORDER BY不能用于子查询，尽管主查询可以使用ORDER BY。 GROUP BY可用于执行与子查询中的ORDER BY相同的功能。
--返回多行的子查询只能与多个值运算符一起使用，例如：IN，EXISTS，NOT IN，ANY / SOME，ALL运算符。
--BETWEEN运算符不能与子查询一起使用; 但是，BETWEEN可以在子查询中使用。

--子查询最常用于SELECT语句
--制造一些垃圾数据
insert into salary values
(30001,2018,01,5000),
(30001,2018,02,5000),
(30001,2018,03,5000);

--验证
select * from salary;

--子查询.
select emp, year, month, sal
from salary
where emp in(select emp from emp);

--带INSERT语句的子查询
--INSERT语句使用从子查询返回的数据插入另一个表
--创建测试表
CREATE TABLE app1.salary_test (
	emp numeric(5) NULL,
	"year" numeric(4) NULL,
	"month" numeric(2) NULL,
	sal numeric(13,3) NULL
)
WITH (
	OIDS=FALSE
) ;

--insert子查询
insert into salary_test
 select * from salary where sal > 9800;
--验证一下
select * from salary_test order by sal asc;

--create table子查询
create table salary_test1 as 
 select * from salary where sal > 9800;

--验证一下
 select * from salary_test1;

--自动递增(AUTO_INCREMENT)
--如果您希望某列具有唯一的约束或是主键，则必须使用其他数据类型进行指定。
--类型名称serial用于创建整数列。 
--类型名称bigserial创建一个bigint类型的列。 
--如果您期望在表的使用期限内使用超过2^31个标识符，则应使用bigserial。 
--类型名称smallserial创建一个smallint列。

--创建一个具有自动递增列的表
create table student(
	id bigserial primary key, --自动递增列，类型为bigserial
	sid numeric(8),
	name varchar
);

insert into student (sid,name) values
(20180401,'李飞'),
(20180402,'王海波'),
(20180403,'赵子龙'),
(20180404,'李霞'),
(20180405,'陆燕'),
(20180406,'郝明');

select * from student;
 
--PostgreSQL权限
--对于大多数类型的对象，初始状态是只有所有者(或超级用户)可以修改或删除对象。 
--要允许其他角色或用户使用它，必须授予权限或权限。

--PostgreSQL中的不同类型的权限是(一共12种)：
--select
--insert
--update
--delete
--TRUNCATE
--references
--trigger
--create
--connect
--temporary
--execute
--USAGE

--要为用户分配权限，使用GRANT命令。
--创建用户user1
CREATE USER user1 WITH PASSWORD 'password';

--给postgres授予表salary的所有权限.
GRANT ALL ON salary TO postgres;

--取消用户postgres对表salary的insert和update权限.
REVOKE insert,update ON salary FROM postgres;