postgresql的安装与基础语法

postgresql不严格区分大小写

查看用户

postgres-# du

角色名称 | 属性 | 成员属于
----------+--------------------------------------------+----------
postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

查看所有数据库

postgres-# l

名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
postgres | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
template0 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)

创建数据库

postgres=# create database mydb;
CREATE DATABASE

删除数据库

if exists的使用：如果所删除的数据库不存在会跳过，而不是报错

postgres=# drop database if exists teche;
注意: 数据库 "teche" 不存在,跳过
DROP DATABASE
postgres=# drop database if exists techer;
DROP DATABASE

创建表

执行create table company();会成功，但是表中不会有任何表结构

postgres=# create table company(
postgres(# id int primary key not null,
postgres(# name text not null,
postgres(# age int not null,
postgres(# address char(50),
postgres(# salary real);
CREATE TABLE

 

删除表

postgres=# drop table if exists company;
DROP TABLE

 显示数据库下所有表

_{postgres-# dt}

关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+---------+--------+----------
public | student | 数据表 | postgres
(1 行记录)

也可以是

select n.nspname,relname from pg_class c, pg_namespace n where c.relnamespace = n.oid and nspname = 'public' and relkind = 'r' order by relname;

注意pg中单引号与双引号是有区别的

PostgreSQL 中的单引号与双引号
在pg中的sql，单引号用来标识实际的值，双引号用来标识表名（table name）或列名（column name）等数据库中存在的值

例如：

select "name" from "student" where "id"='1';

查看表结构

postgres=# d student
数据表 "public.student"
栏位 | 类型 | 修饰词
------+-------------------+--------
id | integer |
name | character varying |
sex | character varying |

也可以是

select * from information_schema.columns where table_schema='public' and table_name='student';

update

update company set name='aostin' where id='1';

delete

delete from company where id='2';

insert

insert into company(id,name,age,address,salary) values(8,'zhangsan','25','shenzhen',300),(9,'尼古拉斯.赵四','20','guangzhou',260);

like
select * from student where name like '%白'；寻找名字以白结尾的数据
select * from student where age::text like '_2'; 寻找第二个数字为2的,age::text将age转为text类型，在使用cast(age as char)转化时like '_7'查不出17的

limit
select * from company limit 4;表示取前4个数据
select * from company limit 4 offset 2; 表示去掉前两个数据去后面的4个数据

order by
select * from company order by age asc; 通过age排序升序，默认也是升序
select * from company order by age desc; 通过age排序，降序

group by
select age,count(*) from company group by age;对age进行分组并计数
age | count
-----+-------
23 | 1
25 | 1
27 | 1
17 | 2
19 | 1
18 | 1

having
HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，
select age,count(*) from company group by age having count(*)<'2';

distinct:
去除重复记录
select distinct name from company;去除name重复的记录，只能单行去重，多行会报错

约束

约束一般在创建表的时候会直接创建约束
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
PHONE TEXT NOT NULL UNIQUE,
NAME TEXT,
AGE INT ,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0),
EXCLUDE USING gist
(NAME WITH =, -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
AGE WITH <>) -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
);
not null：不能为空的约束
unique:设置列是唯一的，避免出现重复值
primary key：设置主键，这是每一条记录的唯一标识
foreign key：外键约束
如下表
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY(ID)
);
emp_id为外键，外键约束为company表中的id字段
check约束：约束列中的所有值满足某一条件
exclusion：约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null
USING gist 是用于构建和执行的索引一种类型

删除约束：
删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 d 表名 可以找到这些信息。
ALTER TABLE table_name DROP CONSTRAINT some_name;

json：
postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率，效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快
postgres=# select *,json_build_object('id',id,'name',name) as user_info from company;
id | name | age | address | salary | user_info
----+--------+-----+----------------------------------------------------+--------+-------------------------------
1 | sary | 18 | china | 100 | {"id" : 1, "name" : "sary"}
2 | pony | 17 | American | 10 | {"id" : 2, "name" : "pony"}
3 | oustin | 19 | england | 50 | {"id" : 3, "name" : "oustin"}
4 | lili | 17 | American | 60 | {"id" : 4, "name" : "lili"}
5 | teddy | 23 | norway | 200 | {"id" : 5, "name" : "teddy"}
6 | mark | 25 | rich-mond | 650 | {"id" : 6, "name" : "mark"}
7 | david | 27 | texas | 850 | {"id" : 7, "name" : "david"}
(7 行记录)
具体解释：https://www.cnblogs.com/alianbog/p/5658156.html

comment on
为表中的字段添加描述
comment on table document_directories is '文档目录';
comment on column document_directories.name is '名称';
comment on column document_directories.parent_id is '父级id';
查看描述
d+ tablename

recursive
通过使用RECURSIVE，一个WITH查询可以引用它自己的输出

with
with result as (select d.user_id from documents d group by d.user_id), info as(
select t.*,json_build_object('id',ur.id,'name',ur.name) as user_info from result t
leet join users ur on ur.id = t.user_id where ur.id is not null) select * from info;
WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，
它们可以被看成是定义只在一个查询中存在的临时表
理解：
result as (select d.user_id from documents d group by d.user_id)此时result作为一个临时表
json_build_object('id',ur.id,'name',ur.name) as user_info user_info作为info的user_info的一个json格式字段
info as(
select t.*,json_build_object('id',ur.id,'name',ur.name) as user_info from result t
leet join users ur on ur.id = t.user_id where ur.id is not null) 此为info临时表
select * from info;显示info临时表