postgresql入门

postgresql简单使用

DDL

​ postgresql的DDL语句与mysql类似，与mysql不同在postgresql处理主键自增使用的是序列（sequence）序列和数据库、数据表一样需要使用DDL语句进行创建、修改、删除。

数据库创建删除

-- 创建数据库
create database mydb; 
-- 删除数据库
drop database mydb;

schema创建删除

​ schema （模式）可以看着是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。

-- 创建一个schema
create schema testschema;
-- 删除一个schema
drop schema testschema;

表定义修改

​ 创建表 表名前为加上schema在指定的schema上创建表 未指定schema则会默认在public下建表

-- 创建表
create table testschema.TESTTABLE(
    id int primary key  not null ,
    name varchar(50) not null ,
    password varchar(50) not null
 );
 -- 修改表(增加一列)
alter table testschema.testtable add address varchar(50);
-- 修改表(删除一列）
alter table testschema.testtable drop address;
-- 删除表
drop table testschema.TESTTABLE;

序列定义修改

​ 在PgSql中使用sequence来实现自增，并且在创建序列中可以控制序列的初始值、步长、是否循环、最大值、最小值等。序列创建完毕可以将表的主键指定其要绑定的序列。

-- 创建序列（从1开始，递增幅度1，最大值无上限）
create sequence test_seq increment by 1 minvalue 1 no maxvalue start with 1;
-- CREATE SEQUENCE sequencename
--     [ INCREMENT increment ]        -- 自增数，默认是 1
--     [ MINVALUE minvalue ]      -- 最小值
--     [ MAXVALUE maxvalue ]      -- 最大值
--     [ START start ]                -- 设置起始值
--     [ CACHE cache ]                -- 是否预先缓存
--     [ CYCLE ]                   -- 是否到达最大值的时候，重新返回到最小值
create sequence if not exists id_seq
increment 1
minvalue 1
maxvalue 10000
start 1
cache 1;
-- 修改序列
alter sequence id_seq start 14;
-- 将表的主键指定创建好的序列
-- nextval('sequence_name'): 将当前值设置成递增后的值，并返回
-- currval('sequence_name'): 返回当前值
-- setval('sequence_name', n, b=true): 设置当前值；b 默认设置 true，下一次调用 nextval() 时，直接返回 n，如果设置 false，则返回 n+increment:
alter table testschema.testtable alter column id set default nextval('id_seq');

视图定义修改

​ pgsql中对视图的操作与mysql有点区别，在pgsql中对视图的修改虽然有alert view语句但是却无法对视图进行增加字段和删除字段。所以在修改视图时可以先删除视图再重新创建视图。

-- 创建视图
create view USER_V as
    select name,password from testschema.testtable;
-- 删除视图
drop view user_v;
-- 修改视图(先删除视图再创建视图)
drop view user_v;
create view user_v as
    select name from testschema.testtable;

函数简单使用

​ 在函数中returns varchar定义一个返回类型，说明返回varchar类型的数据，$$符号把存储过程包围起来，更便于存储过程的识别，declare是声明变量，而函数体都在begin和end之间，name是代表参数name，而$1也是代表name，它是指存储过程的第一个参数username name表示把name赋值给username。

-- 创建函数
create or replace function test(userId int) returns varchar as
    $$
        declare
            username varchar;
        begin
            select into username name  from testschema.testtable  where id=$1;
        return username;
        end
    $$
language plpgsql;
-- 删除函数
drop function test(userId int);
-- 调用函数
select test(2);

DML

​ pgsql的DML语句与mysql并无语法上的差异，sql语句几乎一样

添加数据

-- 添加数据
insert into testschema.testtable (name, password) values('libai','123456');
-- 添加多条
insert into testschema.testtable(id, name, password) VALUES ('2','baijuyi','548415'),('3','wangbo','4876548');

修改数据

-- 更新数据
update testschema.testtable set name = 'litaibai' where id=1;

删除数据

-- 删除数据
delete from testschema.testtable where id=3;

TCL

​ 在对事务的基本处理上主要有提交（commit）、回滚(rollback)、保留点(savepoint)。在pgsql中如果开启事务后在其中的语句有报错在不知情的情况下依旧commit，conmmit后如果pgsql的执行器觉得中间有报错则会将全体回滚。PG数据库支持保存点（savepoint）功能，在比较大的事务中，可以把执行过程分为几个步骤，每个步骤执行完成后创建一个保存点，后续步骤执行失败时，可回滚到之前的保存点，而不必回滚整个事务。

事务开启与提交

-- 事务提交
begin ; -- 也可用 begin transaction 开启事务
insert into testschema.testtable (name, password) values('libai','123456');
insert into testschema.testtable (name, password) values('dufu','546845498');
insert into testschema.testtable (name, password) values('xiean','878431');
insert into testschema.testtable (name, password) values('lishangyin','787987');
commit ; -- 也可用 end transaction 结束事务

事务报错自动回滚

-- 事务回滚
begin ;
insert into testschema.testtable (name, password) values('libai','123456');
insert into testschema.testtable (name, password) values('dufu','546845498');
insert into testschema.testtable (name, password) values('xiean','878431');
insert into testschema.testtablse (name, password) values('lishangyin','787987'); -- 有拼写错误
commit ; -- conmmit后并未提交发现报错后自动回滚

事务回滚保留点

-- 事务回滚保留点
begin ;
insert into testschema.testtable (name, password) values('libai','123456');
insert into testschema.testtable (name, password) values('dufu','546845498');
savepoint mysave1; -- 定义保留点
insert into testschema.testtable (name, password) values('xiean','878431');
insert into testschema.testtable (name, password) values('lishangyin','787987');
rollback to savepoint mysave1; -- 回滚保留点
commit ;

事务隔离级别

​ pgsql默认的事务隔离级别是read committed 而mysql默认隔离级别是repeatable read

-- 查看数据隔离级别（默认的数据隔离级别read committed）
show transaction_isolation;
-- 更改数据隔离级别
set default_transaction_isolation='read uncommitted'; -- 读未提交
set default_transaction_isolation='read committed'; -- 读已提交
set default_transaction_isolation='repeatable read'; -- 不可重复读
set default_transaction_isolation='serializable'; -- 可串行化

DQL

​ pgsql对数据的查询与mysql大部分一致，但在有些语句上还是存在差异

where子句

​ where子句中可以使用的运算符以及关键字和mysql基本都一样

-- where 子句
-- < > =
select * from testschema.testtable where age = 12;
--  and,or,in,not in
select * from testschema.testtable where age =23 and name = 'baijuyi';
select * from testschema.testtable where age =23 or age = 9;
select * from testschema.testtable where age in (23,9);
select * from testschema.testtable where age not in (23,9);
-- not null,like,between
select * from testschema.testtable where age is not null ;
select * from testschema.testtable where name like '%bai%' ;
select * from testschema.testtable where age between 25 and 65;
--  子查询 ,exists
select * from testschema.testtable where exists(select age from testschema.testtable where name like '%bai%');

limit子句

​ limit子句使用需要注意在查询时若要查询一个区间比如只需要第二条数据到第六条数据时需要使用offset关键字

-- limit 子句
-- 提取4条数据
select * from testschema.testtable  limit 4 ;
-- 提取出年龄第三大到第六大的数据
select * from testschema.testtable order by age desc limit 4 offset 2;

order by子句

​ order by 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。

-- order by子句
-- 升序
select * from testschema.testtable order by age asc ;
-- 降序
select * from testschema.testtable order by age desc ;

group by 子句

​ group by语句和 select语句一起使用，用来对相同的数据进行分组。group by 在一个 select语句中，放在 where子句的后面，order by 子句的前面

-- group by 子句
select name,count(1) from testschema.testtable group by name order by count(1) desc ;

having 子句

​ having 子句则在由 group by 子句创建的分组上设置条件

-- having 子句
select name,count(1) from testschema.testtable group by name having name = 'libai';

distinct 关键字

	distinct关键字作用是去除重复记录

-- distinct 关键字
select distinct name from testschema.testtable;

with子句

​ with语句通常被称为通用表表达式（Common Table Expressions）或者CTEs,with语句最基本的功能是把复杂查询语句拆分成多个简单的部分，也可以当做一个为了查询而存在的临时表。

-- with 子句
with name_for_user as(
    select * from testschema.user_goods where goods1 is not null
)
select * from name_for_user good left join testschema.testtable userTable on good.id=userTable.id ;

	with语句还可以通过增加`RECURSIVE`修饰符来引入它自己，从而实现递归具体可以参考`http://www.jasongj.com/sql/cte/`

表连接

​ 在pgsql中一共有五种表连接方式：

1. 内连接inner join，显示两个表匹配条件的数据，不匹配的不显示
2. 左外连接left [outer] join，左边主表的数据必定全部显示，被连接的次表如果匹配则显示，不匹配显示为null
3. 右外连接right [outer] join，右边表的数据必定全部显示，左边表如果匹配则显示，不匹配显示为null
4. 全外连接full [outer] join，两个表匹配的数据会显示，如果不匹配的则各自行的匹配字段显示为null(MySql没有全连接)
5. 交叉连接cross join，不添加匹配条件，形成笛卡尔积，即A表每条数据和B表每条数据各自匹配一次，如A5条数据B4条数据 结果为20条数据。

-- 表连接
-- 内连接(显示两表匹配的数据)
select * from testschema.testtable t1 inner join testschema.user_goods t2 on t1.id = t2.id;
-- 左外连接(左表的数据全显示，右表数据匹配则显示，不匹配则为空)
select * from testschema.testtable t1 left join testschema.user_goods t2 on t1.id = t2.id;
-- 右外连接(右表的数据全显示，左表数据匹配则显示，不匹配则为空)
select * from testschema.testtable t1 right join testschema.user_goods t2 on t1.id = t2.id;
-- 全外连接（两表所有数据都显示，不匹配的数据将会为空）
select * from testschema.testtable t1 full join  testschema.user_goods t2 on t1.id = t2.id;
-- 交叉连接（无需匹配条件，形成笛卡尔积）
select * from testschema.testtable t1 cross join  testschema.user_goods t2 ;

union操作符

	1. union用于合并两个select语句返回的结果集且会去重，两个结果集的列必须都相同并且对应列的数据类型必须一样。
	1. union all是将两个表的结果集直接合并，不会去重

-- union操作符 （会去重）
select name from testschema.testtable where id in(1,2,3,4)
union
select name from testschema.testtable where id in(6,7,8,9);
-- union all 操作符（不会去重）
select name from testschema.testtable where id in(1,2,3,4)
union all
select name from testschema.testtable where id in(6,7,8,9);

​