备案控制台
开发者社区 数据库 文章 正文

PostgreSQL语句大全

2023-11-04 80
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
简介： PostgreSQL是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。PostgreSQL开发者把它念作post-gress-Q-L。PostgreSQL的Slogan是"世界上最先进的开源关系型数据库"。基本语法CREATEFUNCTION//声明创建函数ADD(INTEGER,INTEGER)//定义函数名称RETURNSINTEGER//定义函数返回值'//定义函数体'--使用函数。......

 

目录

前言

基本操作

数据库操作

创建表

表操作

往表中插入数据

修改表数据

删除表数据

表查询

单表指定条件查询

单表指定条件复杂查询

多表查询

子查询

数据类型

数值类型

日期与时间类型

编辑

字符串类型

运算符

算术运算符

编辑

比较运算符

逻辑运算符

函数

数值函数

字符串函数

编辑

日期时间函数

自定义函数

数据库索引

数据库视图

主键、外键

主键

外键

约束

前言

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。PostgreSQL 开发者把它念作 post-gress-Q-L。PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。

一、基本操作

数据库操作

-- 创建数据库
CREATE DATABASE db_lbh;
-- 在创建数据库时指定参数信息  WITH OWNER:所有者  ENCODING:编码格式
CREATE DATABASE db_lbh2 WITH OWNER = postgres ENCODING = 'utf-8';
-- 修改数据库名称
ALTER DATABASE db_lbh2 RENAME TO db_lbh3;
-- 修改连接限制数
ALTER DATABASE db_lbh CONNECTION LIMIT 20;
-- 删除数据库
DROP DATABASE db_lbh3;

image.gif

创建表

-- 创建表
CREATE TABLE student(
id INT,
name VARCHAR(30),
birthday DATE,
score NUMERIC(5,2)
);

image.gif

表操作

-- 修改表名
ALTER TABLE student RENAME TO student1;
-- 修改字段名
ALTER TABLE student1 RENAME id TO myid;
-- 修改字段类型
ALTER TABLE student1 ALTER COLUMN name TYPE VARCHAR(40);
-- 删除字段
ALTER TABLE student1 DROP COLUMN birthday;
-- 添加字段
ALTER TABLE student1 ADD COLUMN birthday varchar(20);
-- 删除表
DROP TABLE student1;
-- 删除前进行判断
DROP TABLE IF EXISTS student1;

image.gif

往表中插入数据

-- 插入数据
INSERT INTO student VALUES (1,'阿道夫','2022-10-10',NULL);
-- 指定字段插入数据
INSERT INTO student (id,name) VALUES('2','王老五');
--SELECT批量插入
-- 将表数据插入到新表中
INSERT INTO student1 SELECT * FROM student;
-- 指定字段批量插入
INSERT INTO student1 (id,name) SELECT id, name FROM student;

image.gif

修改表数据

-- 更新数据，将id等于2的用户名改成'张三' ,如果不指定更新条件，会全局更新
UPDATE student SET name = '张三' WHERE id = 2;

image.gif

删除表数据

指定条件删除

--删除id等于2的表数据
DELETE FROM student WHERE id = 2;
--删除id在1到3的表数据
DELETE FROM student WHERE id BETWEEN 1 and 3;

image.gif

清空表数据

--清空数据表
DELETE from student;
TRUNCATE TABLE student;

image.gif

表查询 

--  查询所有字段内容
SELECT * FROM student;
--  批量字段查询
SELECT id, name FROM student;
--  多表查询
SELECT student.name, student1.name  FROM student;
--  给表取别名后查询，简化代码
SELECT s.id, s.name  FROM student s;

image.gif

单表指定条件查询

-- in关键字查询 查询id为1，3，5的成员
SELECT id, name FROM student WHERE id IN (1,3,5);
-- between and 关键字查询，在什么之间
SELECT id, name, birthday  FROM student 
WHERE birthday BETWEEN '2020-10-10' AND '2024-10-10';
--模糊查询,查询所有姓张的用户
SELECT id, name FROM student WHERE name LIKE '张%';

image.gif

单表指定条件复杂查询 

--  查询所有字段内容
SELECT * FROM student;
-- 只显示前3条数据
SELECT * FROM student LIMIT 3;
-- 查询三条数据，从第二条开始
SELECT * FROM student LIMIT 3 OFFSET 2;
-- 查询姓名非空内容
SELECT id,name FROM student where name is NOT null;
-- 1 or 3 ：1与3 ;ORDER BY id ASC 按id升序 ORDER BY id DESC 按id降序
SELECT id,name FROM student where id=1 OR id=3 ORDER BY id ;

image.gif

多表查询 

--创建一个班级表，id为主键
CREATE TABLE class(
id int PRIMARY KEY,
name VARCHAR(10)
);
--插入班级与对应id
INSERT INTO class VALUES(1,'一班'),(2,'二班');
--创建学生表，id为主键，classid为外键，关联表class的id
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(30),
birthday DATE,
score NUMERIC(5,2),
classid int,
CONSTRAINT pk_student_id FOREIGN KEY(classid) REFERENCES class(id)
);
INSERT INTO student VALUES
(1,'阿道夫','2022-10-10',NULL,1),
(2,'沙和尚','2022-10-10',NULL,2),
(3,'孙悟空','2022-10-10',NULL,2);
-- 隐式内连接，关联classid显示student成员所在班级
SELECT student.id, class.id,student.name, class.name FROM student, class WHERE  student.classid = class.id;
-- 显示内连接,INNER JOIN不返回空值；LEFT JOIN 左连接，RIGHT JOIN 右连接，不能匹配的数据也返回
SELECT student.id, class.id,student.name, class.name 
FROM student INNER JOIN class ON student.classid = class.id ;

image.gif

image.gif

子查询

子查询或称为内部查询、嵌套查询，指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。

一个 SELECT 语句的查询结果能够作为另一个语句的输入值

-- EXISTS关键字子查询
SELECT * FROM student WHERE EXISTS
(SELECT class.id FROM class WHERE class.name = '二班' AND student.classid = class.id);
-- IN 关键字子查询
SELECT * FROM student WHERE student.classid IN
(SELECT class.id FROM class WHERE class.name = '二班' );

image.gif

查询结果合并操作

UNION                 可去重复         执行速度慢

UNION ALL         不可去重复         执行速度快

二、数据类型

数值类型

--整数类型
SMALLINT          //小整数：-32768 ~ 32767
INT(INTEGER)        //普通整数：-2147483648 ~ 2147483647
--任意精度浮点数类型
REAL            //6位十进制数字精度
NUMERIC(m,n)        //任意精度（m总位数，n小数位）

image.gif

举例

--创建学生表，设置id数值类型
CREATE TABLE student(
id1 SMALLINT,
id2 INT,
id3 REAL,
id4 numeric(5,2)
);

image.gif

日期与时间类型

image.gif

举例：

--创建表
CREATE TABLE student(
t TIME,
d DATE,
tm TIMESTAMP
);
--插入数据
INSERT INTO student VALUES('10:10:10','2020-10-10','2020-10-10 10:10:10');

image.gif

image.gif

字符串类型

image.gif举例：

-- 创建表
CREATE TABLE student(
ch CHAR(10),
vch VARCHAR(30),
t TEXT
);
-- 插入数据
INSERT INTO student VALUES ('哈哈哈','哈哈哈','哈哈哈')
SELECT concat('(',ch,')'),
       concat('(',vch,')'),
       concat('(',t,')')
FROM student;

image.gif

可以看见CHAR类型字符长度不够会空白补充

image.gif

运算符

算术运算符

image.gif

比较运算符

image.gif

-- 判断2是否在1~3之间  T 
SELECT 2 BETWEEN 1 AND 3;
-- 判断2是否在234集合中 T
SELECT 2 in (2,3,4);
--  like运算符模糊匹配 %代表多个字符  _代表一个字符
SELECT 'abc' LIKE 'a%',       --T
       'abc' LIKE '_a_',    --F
       'abc' LIKE '_b_',    --T
       'abc' NOT LIKE '_b_';  --F
SELECT LEAST(1,2,3),      --返回最小值：1
       GREATEST(1,2,3); --返回最大值：3

image.gif

逻辑运算符

    • NOT （逻辑非）
    • AND （逻辑与）
    • OR （逻辑或）

    image.gif

    函数

    数值函数

    image.gif

    SELECT 函数（字段名） from 表名；  

    字符串函数

    image.gif

    SELECT 函数（字段名） from 表名；

    日期时间函数

    image.gif

    image.gif

    EXTRACT使用

    image.gif

    自定义函数

    基本语法

    CREATE FUNCTION               //声明创建函数
  ADD(INTEGER,INTEGER)          //定义函数名称
RETURNS INTEGER               //定义函数返回值
  AS'SELECT $1 + $2;'           //定义函数体
LANGUAGE SQL                //用以实现函数的语言名字
RETURNS NULL ON NULL INPUT;           //定义参数为NULL时处理情况

    image.gif

    举例：

    CREATE FUNCTION                   
  add2(INTEGER,INTEGER)           
RETURNS INTEGER                   
  AS'SELECT $1 + $2 + $1;'              
LANGUAGE SQL                      
RETURNS NULL ON NULL INPUT;  
--使用函数
SELECT add2(1,2);
-- 结果： 4

    image.gif

    删除函数

    DROP FUNCTION concat_student(INT,VARCHAR,DATE);

    数据库索引

    使用索引的优缺点

    优点：

      1. 通过创建唯一的索引，可以保证数据库表中每一行数据的唯一性。
      2. 可以大大提高查询速度
      3. 加速表与表之间的连接
      4. 减少查询中分组和排序的时间

      缺点：

        1. 创建和维护索引耗费时间，数据量越多耗费的时间越多
        2. 索引需要占用物理空间
        3. 对表的数据进行增加、删除、修改的时候，索引也需要动态维护，降低了数据的维护速度。

        image.gif

        -- 创建索引 此处默认使用B-tree
CREATE INDEX student_name_index ON student(name);
-- 删除索引
DROP INDEX student_name_index;

        image.gif

        数据库视图

        使用视图具有 简单化、安全性、逻辑数据独立性

        --创建视图top3，展示id,name,birthday 条件是id<=3  并且按id降序
CREATE VIEW top3 AS SELECT id,name,birthday FROM student where id <= 3 ORDER BY id desc;
--查看视图
SELECT * FROM top3;
--删除视图
DROP VIEW top3;

        image.gif

        image.gif

        主键、外键

        主键

        比如我们前面创建的学生表student里，id是唯一的，就可以使用PRIMARY KEY语句设置为主键

        --方式1，列级约束，使用PRIMARY KEY语句，不能指定主键名称
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(30),
birthday DATE,
score NUMERIC(5,2)
);
--方式2，表级约束，可以指定主键名称
CREATE TABLE student(
id INT,
name VARCHAR(30),
birthday DATE,
score NUMERIC(5,2),
CONSTRAINT pk_student_id PRIMARY KEY(id)
);

        image.gif

        外键 

        --创建一个班级表，id为主键
CREATE TABLE class(
id int PRIMARY KEY,
name VARCHAR(10)
);
--插入班级与对应id
INSERT INTO class VALUES(1,'一班'),(2,'二班');
--创建学生表，id为主键，classid为外键，关联表class的id
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(30),
birthday DATE,
score NUMERIC(5,2),
classid int,
CONSTRAINT pk_student_id FOREIGN KEY(classid) REFERENCES class(id)
);

        image.gif

        约束

        主键与约束的区别，主键的值是非空加唯一，且一个表只能有一个主键，可以有多个约束

        CREATE TABLE student(
id INT UNIQUE,               // UNIQUE    唯一约束
name VARCHAR(30) NOT NULL,       // NOT NULL  非空约束
birthday DATE,
score NUMERIC(5,2) DEFAULT 0.0     // DEFAULT   默认值约束
);

        image.gif

        文章标签：
        云原生数据库 PolarDB
        关系型数据库
        PostgreSQL
        数据库
        索引
        Unix
        相关实践学习
        使用PolarDB和ECS搭建门户网站
        本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
        阿里云数据库产品家族及特性
        阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
        LiBihao
        目录
        相关文章
        猫头虎
        |
        7月前
        |
        SQL 关系型数据库 Go
        PostgreSQL 查询语句大全
        PostgreSQL 查询语句大全
        猫头虎
        69 0
        晚风_END
        |
        5月前
        |
        SQL NoSQL 关系型数据库
        postgresql|数据库|SQL语句冲突的解决
        postgresql|数据库|SQL语句冲突的解决
        晚风_END
        78 0
        vohelon
        |
        关系型数据库 PostgreSQL
        PostgreSQL INSERT INTO 语句
        PostgreSQL INSERT INTO 语句
        vohelon
        287 0
        SKRP
        |
        SQL 关系型数据库 数据库
        postgresql SQL
        SQL语法
        SKRP
        106 0
        慕枫技术笔记
        |
        SQL 关系型数据库 MySQL
        30个实用SQL语句，玩转PostgreSQL
        PostgreSQL是一款功能非常强大的开源关系型数据库,本文主要整理总结了30个实用SQL，方便大家可以高效利用PostgreSQL。
        慕枫技术笔记
        227 0
        30个实用SQL语句，玩转PostgreSQL
        chrisx
        |
        关系型数据库 PostgreSQL
        postgresql行列转换函数
        postgresql行列转换函数 Pg提供相关行列转换函数string_agg和regexp_split_to_table。行转列：string_agg 测试表 postgres=# select * from test.
        chrisx
        5997 0
        流浪的猫666
        |
        SQL 关系型数据库 索引
        PostgreSQL实用查询SQL
        流浪的猫666
        3435 0
        嗯哼9925
        |
        SQL 关系型数据库 PostgreSQL
        PostgreSQL在何处处理 sql查询之九
        嗯哼9925
        880 0
        嗯哼9925
        |
        关系型数据库 PostgreSQL SQL
        我对PostgreSQL 中 执行计划的处理范围的理解
        嗯哼9925
        1224 0
        嗯哼9925
        |
        SQL 关系型数据库 数据库
        PostgreSQL在何处处理 sql查询
        嗯哼9925
        1242 0