欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

Postgresql学习笔记-基础语法篇

程序员文章站 2024-03-21 10:56:46
...

Postgresql学习笔记

  • 修改密码：alter user 用户名 with password ‘新密码’;

  • 创建表：

    CREATE TABLE DEPARTMENT(
	ID INT PRIMARY KEY NOT NULL,
	DEPT CHAR(50) NOT NULL,
	EMP_ID INT NOT NULL
);

  • 往表中插入值：

    insert into department values (1,'IT Billing',1); --全插入（表中所有字段都插入值）
insert into department (ID,DEPT,...) values (1,'xxx',...); --选中表中字段插入值

Postgresql 连接（JOIN）

  • 交叉连接

    交叉连接(CROSS JOIN)把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有x行和y行，则结果表有x*y行。

    由于交叉连接(CROSS JOIN)有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

    语法

    SELECT ... FROM table1 CROSS JOIN table2 ...
举例：
select name,dept from company cross join department;
select a.name,b.dept from company as a cross join department as b;

  • 内连接

    内连接(INNER JOIN)根据连接谓词结合两个表（table1和table2）的列值来创建一个新的结果表。查询会把table1中的每一行与table2中的每一行进行比较，找到所有满足连接谓词的行的匹配对。

    当满足连接谓词时，A和B行的每个匹配对的列值会合并成一个结果行。

    内连接(INNER JOIN)是最常见的连接类型，是默认的连接类型。

    语法

    SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
举例：
select a.id,a.name,a.age,a.address,a.salary,b.dept from company 
as a inner join department as b on a.id=b.id;

  • 左外连接

    外部连接是内部连接的扩展。SQL标准定义了三种类型的外部连接：LEFT、RIGHT和FULL，Postgresql支持所有这些。

    对于左外连接，首先执行一个内连接。然后，对于表T1中不满足表T2中连接条件的每一行，其中T2的列中有null值也会添加一个连接行。因此，连接的表在T1中每一行都会保留。

    语法

    SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
举例：
select b.emp_id,a.name,b.dept from company as a left join department as b on a.id=b.emp_id;
注意：a中所有name信息都会出现在结果集中，但是b中不满足条件的全部以null代替。
left outer join 结果和 left join 结果一致。

  • 右外连接

    首先，执行内部连接。然后，对于表T2中不满足T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左连接相反；对于T2中的每一行，结果表都会做保留。

    语法

    SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
举例：
select a.id,a.name,b.dept from company as a right join department as b on a.id=b.emp_id;

  • 外连接

    首先，执行内部连接。然后，对于表T1中不满足表T2中任何行连接条件的每一行，如果T2的列中有null值也会添加到结果集中。此外，对于T2中不满足与T1中的任何行连接条件的每一行，将会添加T1列中包含null值到结果中。

    语法

    SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
举例：
select b.emp_id,a.name,b.dept from company as a full join department as b on a.id=b.emp_id;
结果与左连接类似。

Postgresql 运算符

  • 算术运算符

    运算符 描述 实例
    + 加 select 1+2 as re;
    - 减 select 2-1 as re;
    * 乘 select 1*2 as re;
    / 除 select 2/1 as re;
    % 模（取余） select 1.2%1 as re;
    ^ 指数 select 2^4 as re;
    |/ 平方根 select |/4 as re;
    ||/ 立方根 select ||/27 as re;
    ! 阶乘 select 4! as re;
    !! 阶乘（前缀操作符） select !!4 as re;

  • 比较运算符

    运算符 描述 实例
    = 等于 select 1=1 as re;
    != 不等于 select 1!=1 as re;
    <> 不等于 select 1<>2 as re;
    > 大于 select 1>2 as re;
    < 小于 select 1<2 as re;
    >= 大于等于 select 1>=2 as re;
    <= 小于等于 select 1<=2 as re;

  • 逻辑运算符

    运算符 描述
    AND 逻辑与运算符。如果两个操作数都非零，则条件为真。Postgresql中的WHERE语句可以用AND包含多个过滤条件。
    NOT 逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。Postgresql有NOT EXISTS,NOT BETWEEN,NOT IN等运算符。
    OR 逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。Postgresql中的WHERE语句可以用OR包含多个过滤条件。

    注意：SQL使用三值的逻辑系统，包括true、false和null,null表示“未知”

  • 位运算符

    p q p&q p|q p^q ~p
    0 0 0 0 0 1
    0 1 0 1 1 1
    1 0 0 1 1 0
    1 1 1 1 0 0

| 无 | 无 | 两个都为真为真 | 一个为真就为真 | 相同为0，不同为1 | 非 |

Postgresql表达式

  • 布尔表达式：

    SELECT * FROM COMPANY WHERE SALARY = 10000;

  • 数字表达式：

    SELECT (17 + 6) AS ADDITION ;

  • 内置数学函数：

    函数名 说明 用法
    avg() 返回表达式平均值 select avg(id) as re from company;
    sum() 返回指定字段的总和 select sum(id) as re from company;
    count() 返回查询的记录总数 select count(id) as re from company;

Postgresql WHERE 子句

  • 普通用法-结合各类逻辑运算符使用

    运算符 举例 说明
    AND SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; 找出 AGE(年龄) 字段大于等于 25，并且 SALARY(薪资) 字段大于等于 65000 的数据
    OR SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; 找出 AGE(年龄) 字段大于等于 25，或者 SALARY(薪资) 字段大于等于 65000 的数据
    NOT NULL SELECT * FROM COMPANY WHERE AGE IS NOT NULL; 在公司表中找出 AGE(年龄) 字段不为空的记录
    LIKE SELECT * FROM COMPANY WHERE NAME LIKE ‘Pa%’; 在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据
    IN SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); AGE(年龄) 字段为 25 或 27 的数据
    NOT IN SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); AGE(年龄) 字段不为 25 或 27 的数据
    BETWEEN SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; AGE(年龄) 字段在 25 到 27 的数据

  • 子查询

    SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

    说明：子查询语句中读取SALARY（薪资）字段大于65000的数据，然后通过EXISTS运算符判断它是否返回行，如果有返回行则读取所有的AGE(年龄)字段。

Postgresql UPDATE 语句

  • 如果我们要更新在Postgresql数据库中的数据，我们可以用UPDATE来操作。

    语法

    UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
-- 举例：（更新COMPANY表中id为3的salary字段值）
UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
-- 举例：（同时更新company表中salary和address字段值）
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

Postgresql DELETE 语句

  • 使用DELETE语句来删除Postgresql表中的数据

    语法

    DELETE FROM table_name WHERE [condition];
举例：（删除COMPANY表中id为2的数据）
DELETE FROM COMPANY WHERE ID = 2;
DELETE FROM COMPANY; --删除整张COMPANY表（慎用DELETE)

Postgresql LIKE 子句

  • 百分号 % :匹配0或多个字符

    SELECT FROM table_name WHERE column LIKE 'XXXX%';

  • 下划线 _ : 匹配一个字符

    SELECT FROM table_name WHERE column LIKE 'XXXX_';

Postgresql LIMIT 子句

  • limit子句用于限制SELECT 语句中查询的数据的数量。

    语法

    • 带有 LIMIT 子句的 SELECT 语句的基本语法如下：
    SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
举例：
SELECT * FROM COMPANY LIMIT 4;
    • LIMIT 子句与 OFFSET 子句一起使用时的语法：
    SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]
举例：从第三位开始提取 3 个记录
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Postgresql ORDER BY 语句

注意：ASC表示升序，DESC表示降序。

  • 语法

    • 对一个字段排序

      SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
举例：-- 对结果根据 AGE 字段值进行升序排列
SELECT * FROM COMPANY ORDER BY AGE ASC;

    • 对多个字段排序

      举例：-- 对结果根据 NAME 字段值和 SALARY 字段值进行升序排序
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

Postgresql GROUP BY 语句

  • 在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。

    GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

    语法

    SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
举例：--根据 NAME 字段值进行分组，找出每个人的工资总额
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Postgresql HAVING 子句

  • HAVING 子句可以让我们筛选分组后的各组数据。

    WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

    语法

    SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
举例：-- 根据 NAME 字段值进行分组，并且 name(名称) 字段的计数少于 2 数据
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

Postgresql DISTINCT 关键字

  • 在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

    我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

    语法

    SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
举例：--删除name重复的数据
select distinct name from company;
相关标签： Postgresql Postgresql学习笔记 Postgresql基础

上一篇： emacs & yasnippet 博客分类： 编辑器 emacsyasnippet

下一篇： redis cluster集群架构详解（十五）- redis cluster常用命令

推荐阅读