PgSql入门
2021-08-19 本文已影响0人
codeduck1
一，数据库操作
# 创建数据库
create database mytest;
# 删除数据库
drop database if exists mytest;
# 占用问题
select pg_terminate_backend(pid) from pg_stat_activity where DATNAME = 'template1';
# 使用 \l 用于查看已经存在的数据库
\l
# 可以使用 \c + 数据库名 来进入数据库
\c mytest;
二，表操作
# 创建表
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
# 删除表
drop table if exists company;
# 列举表，相当于mysql的show tables
\d
# 查看表结构，相当于desc tblname,show columns from tbname
\d company
# 查看索引
\di
三，模式（SCHEMA）
PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
模式类似于操作系统层的目录，但是模式不能嵌套。
# 语法格式
CREATE SCHEMA myschema.mytable (
...
);
# 创建一个模式,并模式下创建一个表格
create schema myschema;
create table myschema.company(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
# 查看表格
select * from myschema.company;
# 删除一个为空的模式（其中的所有对象已经被删除）
drop schema myschema;
# 删除一个模式以及其中包含的所有对象（cascade 层叠/大量）
drop schema myschema cascade;
四，表的CRUD操作
# 插入语句 JOIN_DATE 字段使用 DEFAULT 子句来设置默认值
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
# 插入多行数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
# 查询
select * from company;
SELECT (17 + 6) AS ADDITION；
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
SELECT CURRENT_TIMESTAMP;
# 子查询
SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
# 更新
UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;
# 删除
DELETE FROM COMPANY WHERE ID = 2;
五，聚合操作
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
# like 将整型数据类型转化为字符串数据类型,再模糊搜索
SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
# limit 从第三位开始提取3个记录
SELECT * FROM COMPANY LIMIT 4;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
# GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
# HAVING 子句可以让我们筛选分组后的各组数据。
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
# DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
SELECT DISTINCT name FROM COMPANY;
六，WITH子句
在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。
WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。
WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。
WITH 子句在使用前必须先定义。
- 语法定义：
name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。
可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。
# 1导入数据
DROP TABLE COMPANY;
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
# 2使用 WITH 子句在上表中查询数据：
With CTE AS
(Select
ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;
# 3使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和
WITH RECURSIVE t(salary) AS (
VALUES (0)
UNION ALL
SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(salary) FROM t;
# 4我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
WITH moved_rows AS (
DELETE FROM COMPANY
WHERE
salary >= 30000
RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);
TIPS：如何更换DBeaver驱动下载地址
更换驱动下载中心的（maven中央仓库）即可解决驱动下载慢问题，大部分驱动都基于maven repository来下载。找到 首选项 > 连接 > 驱动 > 驱动位置 ，点击添加
阿里云中央仓库地址：
http://maven.aliyun.com/nexus/content/groups/public/
参考下载地址
https://www.jianshu.com/p/bafd56b99de1
dbeaver21企业版，需要安装jdk11，并且包含jre环境
删除dbeaver安装目录下的jre文件夹，并在dbeaver.ini文件下添加:
-javaagent:D:\DevTools\dbeaver\agent\dbeaver-agent.jar
