PostgreSQL数据库操作指令
-
SELECT语句
SELECT column1, column2,...column FROM table_name; 【例1】读取表中的所有数据 SELECT * FROM table_name; student=# select * student-# from company; id | name | age | address | salary ----+-------+-----+----------------------------------------------------+-------- 1 | PAUL | 32 | California | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 【例2】读取指定字段ID和NAME student=# select ID, NAME FROM company; id | name ----+------- 1 | PAUL 4 | Mark 5 | David (3 行记录)
-
PostgreSQL运算符
常见的运算符有:
-
算数运算符:+、-、*、/、%、^、|/(平方根)、||/(立方根)、!(阶乘)、 !!(阶乘前缀操作符)
-
比较运算符:=、!=、<>、>、<、>=、<=
-
逻辑运算符:AND、NOT、OR
-
位运算符:&、|、^
真值表:
p q p & q p | q 0 0 0 0 0 1 0 1 1 1 1 1 1 0 0 1
student=# select 2+3; ?column? ---------- 5 (1 行记录) student=# select 5 !; ?column? ---------- 120 (1 row) student=# select !!5; ?column? ---------- 120 (1 row) #读取 SALARY 字段大于 50000 的数据 student=# select * student-# from company student-# where salary > 50000; id | name | age | address | salary ----+-------+-----+----------------------------------------------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 行记录)
-
-
PostgreSQL表达式
布尔表达式是根据一个指定条件来读取数据
SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION; 【例】查看company表中salary=10000的数据 student=# SELECT * FROM COMPANY WHERE SALARY = 10000; id | name | age | address | salary ----+-------+-----+----------+-------- 7 | James | 24 | Houston | 10000 (1 row)
数字表达式常用于查询语句中的数学运算
SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ; student=# SELECT (17 + 6) AS ADDITION ; addition ---------- 23 (1 row) --avg():返回一个表达式的平均值 --sum():返回指定字段的总和 --count():返回查询的记录总数 【例】查询company表的记录总数 student=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY; RECORDS --------- 7 (1 row)
日期表达式返回当前系统的日期和时间
student=# select current_timestamp; current_timestamp ------------------------------ 2021-03-20 06:10:44.80687+08 (1 行记录)
-
WHERE 语句
当我们需要根据指定条件从单张表或者多张表中查询数据时,就可以在 SELECT 语句中添加 WHERE 子句,从而过滤掉我们不需要数据
--用法: SELECT column1, column2, columnN FROM table_name WHERE [condition1] 【例1】找出 AGE(年龄) 字段大于等于 25,并且 SALARY(薪资) 字段大于等于 65000 的数据: student=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; 【例2】找出 AGE(年龄) 字段大于等于 25,或者 SALARY(薪资) 字段大于等于 65000 的数据: student=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; 【例3】在company表中找出 AGE(年龄) 字段不为空的记录: student=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL 【例4】在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据: student=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%'; 【例5】以下 SELECT 语句列出了 AGE(年龄) 字段为 25 或 27 的数据: student=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); 【例6】以下 SELECT 语句列出了 AGE(年龄) 字段不为 25 或 27 的数据: student=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
子查询:例:读取 SALARY(薪资) 字段大于 65000 的数据,然后通过 EXISTS 运算符判断它是否返回行,如果有返回行则读取所有的 AGE(年龄) 字段。
student=# SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); id | name | age | address | salary ----+------+-----+------------+-------- 1 | Paul | 32 | California | 20000
-
AND & OR 运算符
AND 和 OR 也叫连接运算符,在查询数据时用于缩小查询范围,可以用 AND 或者 OR 指定一个或多个查询条件
SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]
-
UPDATE语句
更新PostgreSQL数据库中 的数据
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition]; 【例】 UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
-
DELETE语句
DELETE FROM table_name WHERE [condition]; 【例】UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
-
LIKE子句
通配符:类似于正则表达式的匹配方式,主要有百分号和下划线两种
-
百分号:
案例 含义 WHERE SALARY::text LIKE ‘200%’ 找出 SALARY 字段中以 200 开头的数据。 WHERE SALARY::text LIKE ‘%200%’ 找出 SALARY 字段中含有 200 字符的数据。 WHERE SALARY::text LIKE ‘_00%’ 找出 SALARY 字段中在第二和第三个位置上有 00 的数据。 WHERE SALARY::text LIKE ‘2 % %’ 找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据。 WHERE SALARY::text LIKE ‘%2’ 找出 SALARY 字段中以 2 结尾的数据 WHERE SALARY::text LIKE ‘_2%3’ 找出 SALARY 字段中 2 在第二个位置上并且以 3 结尾的数据 -
下划线:
案例 含义 WHERE SALARY::text LIKE ‘2___3’ 找出 SALARY 字段中以 2 开头,3 结尾并且是 5 位数的数据 -
提示:LIKE 子句是只能用于对字符进行比较,因此在上面列子中,我们要将整型数据类型转化为字符串数据类型
-
-
LIMIT子句
SELECT column1, column2, column3 FROM tabl_name LIMIT [no of rows] SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num] 【例】从上往下依次读取4条数据 student=# SELECT * FROM COMPANY LIMIT 4; id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 (4 rows) 【例2】读取3、4、5条数据 student=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2; id | name | age | address | salary ----+-------+-----+-----------+-------- 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows)
-
ORDERED BY语句
用于对一列或者多列数据进行升序(ASC)或者降序(DESC)排列
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; --ASC、DESC分别代表升序和降序
-
GROUP BY
分组查询,按select列表的列进行分组,每一组产生一个综合查询结果。
GROUP BY子句的列名又称为组列名。
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN 【例】若有如下表 id | name | age | address | salary ----+-------+-----+----------------------------------------------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | JoJo | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 行记录) --根据 NAME 字段值进行分组,找出每个客户的工资总额: student=# select name ,sum(salary) from company group by name order by name; name | sum -------+------- Allen | 15000 David | 85000 James | 10000 JoJo | 10000 Kim | 45000 Mark | 65000 Paul | 40000 Teddy | 20000 (8 行记录)
-
WITH AS语句
WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些
WITH name_for_summary_data AS( SELECT Statement) SELECT columns FROM name_for_summary_data WHERE conditions <=> ( SELECT column FROM name_for_summary_data) [ORDER BY columns] 【例】查询数据 With CTE AS (Select ID , NAME , AGE , ADDRESS , SALARY FROM COMPANY ) Select * From CTE;
**公用表表达式(CTE)**使用CTE,可以使SQL语句的可维护性,同时,CTE要比表变量的效率高得多
--递归调用WITH语句 WITH RECURSIVE t(n) AS( VALUES(0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY < 20000 ) SELECT sum(n) FROM t; sum ------- 25000 (1 row)
-
HAVING 语句
设计意图与GROUP BY一起使用,来限定那些分组将出现在最终的查询结果中。虽然他与WHERE子句语法类似,但用途不同。
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2 【例】对于员工人数多于一人的分公司,计算出每一个分公司的员工人数和他们的工资总和 SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum FROM Staff GROUP BY branchNo HAVING COUNT(staffNo) > 1 ORDER BY branchNo;
-
DISTINCT关键字
DISTINCT 关键字与 SELECT 语句一起使用,用于去除重复记录,只获取唯一的记录
SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition] 【例】 INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (8, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (9, 'Allen', 25, 'Texas', 15000.00 ); --在已有重复内容上进行合并查找 SELECT DISTINCT name FROM COMPANY;