SQL语句--postgres（DDL、DCL、DML）

2. 支持DDL语句的审计，数据库表创建表、删除表、修改表结构等（DDL）

1）新建数据库：

CREATE DATABASE runoobdb;

1）创建表：

create table table1 (id int primary key, a varchar(255));

2）增加表字段：

alter table table1 add column a2 varchar(255);

3）字段改名：

ALTER TABLE table1 rename column A TO A1;

4）删除字段：

ALTER TABLE table1 DROP a2 CASCADE;

5）删除表：

DROP TABLE IF EXISTS table1 CASCADE

6）字段注释：

COMMENT ON COLUMN table1."a1" IS '0全部发送（默认）1区别发送';

7）清空表：

create TABLE tb_AW(

name varchar(10),

sex varchar(30)

);

insert into tb_AW values('Joe','男');

insert into tb_AW values('周欣红','女');

select * from tb_AW;

truncate table tb_AW;

12）删除模式

DROP DATABASE runoobdb;

3. 支持DCL语句的审计，授予、解除用户访问权限、拒绝用户访问等（DCL）

1）添加用户并设置密码：

create user user4 with PASSWORD '123456';

2）查询当前用户：

select user

3）授予权限：

grant INSERT on tb_AW to user4;

4）修改用户密码:

alter USER user4 with password 'postgres123456'

5）收回权限:

REVOKE INSERT ON tb_AW FROM user4

6）删除用户：

DROP USER user4;

4. 支持DML语句的审计，检索、更新数据、添加行、删除行（DML）

CREATE TABLE students(

"s_id" INT NOT NULL ,

"s_name" VARCHAR (100) NOT NULL

)

1）添加：

INSERT INTO students VALUES ('1', '张三'),('2', '李四'),('3', '王五')

2）修改表数据：

UPDATE STUDENTS SET "s_name" = '周生' WHERE "s_id"  = '01'

3）查找表数据：

SELECT * FROM students

4）条件查询

select "s_name" as "姓名" from Students;

select * from Students where "s_name" = '周生';

select * from Students where "s_name" like '%生%';

select * from Students where "s_id" between 01 and 02

select * from Students where "s_name" in('周生','孙风');

select u."s_id",u."s_name" from Students u order by u."s_id" desc;

5）删除表数据：

delete from Students where "s_name" = '周生'

5. 支持数据库存储过程审计

1）新建存储过程：

create procedure transfer(id1 int)

language plpgsql

as $$

    begin

        update Students set s_name='杨过' where s_id = id1;

        commit;  --提交事务

    end;

$$

2）调用：

call transfer(1);

3）删除：

drop procedure transfer

6. 函数：

1）创建函数：

CREATE OR REPLACE FUNCTION totalRecords ()  

RETURNS integer AS $total$  

declare  

    total integer;  

BEGIN  

   SELECT count(*) into total FROM Students;  

   RETURN total;  

END;  

$total$ LANGUAGE plpgsql;

2）调用：

select totalRecords();

3）删除：

DROP FUNCTION totalRecords;

7. 视图：

1）创建视图：

CREATE VIEW view1

AS

SELECT * FROM Students

2）调用视图：

SELECT * FROM view1;

3）删除视图：

DROP VIEW view1

8. 索引：

create TABLE tb_AW(

name varchar(10),

sex varchar(30)

);

1）创建索引：

CREATE INDEX UQ_ENAME_IDX ON tb_AW(sex);

2）查看索引：

select * from pg_statio_all_indexes where relname='tb_aw';     

3）删除索引：

DROP INDEX UQ_ENAME_IDX CASCADE;

9. 事务：

create table table1 (id int primary key, a varchar(255));

1）提交事务：

BEGIN

insert into table1 values('1','杨过');

commit;

2）回滚事务：

BEGIN;

insert into table1 values('2','小龙女');

ROLLBACK;

3）使用savepoint部分回滚

savepoint-postgres.txt   === 等待更新

10. 超长SQL语句审计：

超长sql语句-postgres.txt  === 等待更新

11. 中文乱码审计：

create table student4

(

  stuid      varchar(11) not null,--学号：'S'+班号（7位数）+学生序号（3位数）(1)

  stuname    varchar(50) not null,--学生姓名

  sex        char(20) not null,--性别

  age        int not null,--年龄

  classno    varchar(20) not null,--班号：'C'+年级（4位数）+班级序号（2位数）

  idnumber   varchar(20) default '身份证未采集' not null--身份证

)

insert into student4 values('01' , '赵雷' , '男' , '22' , '11' , '41130319970513680X');

insert into student4 values('02' , '钱电' , '男' , '23' , '21' , '41130319980513680X');

12. 报表特权操作补充：

1）创建角色：

create role testRole;

2）查看角色：

SELECT * FROM pg_roles

3）删除角色：

drop role testRole;

13. 超长执行时长语句的审计

SELECT pg_sleep(300);