--study doc: client tool:dbreaver. create schema app1; drop table app1.salary; create table app1.salary ( emp n

Transcription

1 --study doc: 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, ), (90002,2018,01, ), (90003,2018,01, ); insert into salary values (90001,2018,02, ), (90002,2018,02, ), (90003,2018,02, ); insert into salary values (90001,2018,03, ), (90002,2018,03, ), (90003,2018,03, ); 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 > 加过滤条件

2 order by emp asc; select * from salary where emp in( 90001, ) 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", from emp salary."year",salary."month",salary.sal

3 full join salary on emp.emp = salary.emp; --cross join( 笛卡儿积分, 得到 X Y 行 ) select emp.emp,emp."name", from emp salary."year",salary."month",salary.sal 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 begin empsal integer; select sum(sal) into empsal from salary; end; return empsal; $empsal$ LANGUAGE plpgsql; -- 调用函数 ( 存储过程 ) select get_total(); -- 有输入参数 (argument) 的存储过程 -- 存储过程, 其实和开发语言里的函数一样, 有输入参数 也有输出参数, 还有函数体. create or replace function get_year_momth_sal(zyear numeric(4),

4 returns integer as $empsal$ declare empsal integer; begin select sum(sal) into empsal from salary where year and emp zemp numeric(5) ) -- 符号实现变量的使用 end; return empsal; $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

5 ); -- 创建一个保存日志的存储过程 : 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); END; RETURN NEW; $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, ), (90002,2018,05, ), (90003,2018,05, ); -- 查看日志, 确认触发器是否成功触发. 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

6 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; -- 什么时候应该避免使用索引? -- 应该避免在小表上使用索引 -- 不要为具有频繁, 大批量更新或插入操作的表创建索引

7 -- 索引不应用于包含大量 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 操作

8 -- 什么时候用 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);

9 BEGIN; insert into emp values (90006,' 韩梅梅 ',' X',' 广东省 '), (90007,' 李萍萍 ',' X',' 云南省 '); 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,' 韩梅梅 ',' X',' 广东省 '), (80004,' 李萍萍 ',' X',' 云南省 ');

10 -- 解锁 : rollback; -- 此时再 insert 数据, 不会报错了 insert into emp values (80001,' 韩梅梅 ',' X',' 广东省 '), (80002,' 李萍萍 ',' X',' 云南省 '); 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 语句使用从子查询返回的数据插入另一个表

11 -- 创建测试表 CREATE TABLE app1.salary_test ( ) WITH ( ) ; emp numeric(5) NULL, "year" numeric(4) NULL, "month" numeric(2) NULL, sal numeric(13,3) NULL 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 ( ,' 李飞 '), ( ,' 王海波 '),

12 ( ,' 赵子龙 '), ( ,' 李霞 '), ( ,' 陆燕 '), ( ,' 郝明 '); 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;