从小白到大佬的 pgsql 学习－基础语法

我有2只猫

已于 2023-10-08 14:33:43 修改

阅读量166

点赞数

分类专栏： MYSQL 文章标签：学习 python postgresql docker

于 2023-10-07 19:11:17 首次发布

本文链接：https://blog.csdn.net/weixin_39092173/article/details/132454380

版权

MYSQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

导入数据

在正式开始学习之前，我们需要有个数据库，再有一个数据表。

搭建数据库

关于如何搭建一个pgsql数据库环境，可以参考我的上一个blogDocker 搭建Postgresql数据库
导入数据表

到这个地址https://github.com/jOOQ/sakila/tree/main/postgres-sakila-db下载两个文件
　
１．postgres-sakila-schema.sql - 此文件包含了创建数据库结构的 sql 脚本，包含表，函数，存储过程，触发器等。
２．postgres-sakila-insert-data.sql - 此文件包含了导入数据的 sql 脚本
下载后将两个文件分别　docker cp 到postgresql 容器内

sudo docker cp postgres-sakila-insert-data.sql f161f67ba0e0:/

依次运行如下指令开始导入

$ docker exec -it postgresql_pgdb_1 bash
$ psql -Uggyy -f postgres-sakila-schema.sql
$ psql -Uggyy -f postgres-sakila-insert-data.sql

遇到语法格式错误，去hub链接下，把原始内容复制下，粘贴在同文件中即可.
验证是否导入成功

$ exit # 退出容器
$ docker exec -it postgresql_pgdb_1 psql -Uggyy # 重新进入数据库
$ \dt # 列出数据表

看到这个表，恭喜你，又成功了
在这里插入图片描述

PostgreSQL　基础命令

SELECT

普通查找

 SELECT 
 	first_name as fname, 
 	last_name as lname, 
 	email 
 FROM customer;

2.如果想检索结果显示的好看些，可以使用\x , 再SELECT，退出的话再使用\x即可。
3. 使用带有表达式的

SELECT 
	first_name ||' '|| last_name,
	email
FROM customer;

WHERE

语法结构如下：

SELECT columns_list
FROM table_name
WHERE query_condition;

举个例子

where 子句和LIKE 混用

SELECT first_name,
  last_name
FROM customer
WHERE first_name LIKE 'ANN%';

where 子句和 BETWEEN 混用

SELECT first_name,
  LENGTH(first_name) name_length
FROM customer
WHERE first_name LIKE 'A%'
  AND LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY name_length;

where 子句中使用不等运算符 (<>)运算符

注意sql语句使用单引号

SELECT first_name,
  last_name
FROM customer
WHERE first_name LIKE 'BRA%'
  AND last_name <> 'MOTLEY';

ORDER BY

pgsql 中 ORDER_BY 用来对SELECT 结果进行排序

语法结构如下:

SELECT
   column1, column2, ...
FROM
   table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   [column2 [ASC|DESC],
   ...]
   [NULLS FIRST | NULLS LAST]
   ;

一个小例子

ASC表示升序， DESC表示降序
NULLS FIRST表示null值在非null值之前， NULLS LAST null值在非null值之后

按照自定义排序规则排序 CASE

SELECT 
	film_id, 
	title, 
	language_id
FROM film 
WHERE film_id <100 order by rental_rate DESC NULLS LAST;

在这里插入图片描述

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;

FETCH

语法：限制返回的行数，【可使用offset　应用于分页查询中】：

offset 0 offset first 10 rows only # 第一页
offset 10 offset first 10 rows only　# 第二页

SELECT column_list
FROM table_name
[other_clauses]
FETCH {FIRST | NEXT} [rows_count] {ROW | ROWS} ONLY;

example:

SELECT 
	film_id, title, release_year
FROM 
	film
ORDER BY
	film_id
FETCH FIRST 5 ROWS ONLY;

LIMIT

语法：LIMIT [rows count]

OFFSET

用于分页查询

DISTINCT

避免重复行

SELECT
   DISTINCT column1 [, column2, ...]
FROM
   table_name;

列别名

给列字段取一个别名　AS, 利用　|| ’ '|| 表达式，拼接字符串

SELECT
    first_name || ' ' || last_name full_name
FROM
    actor
LIMIT 10;

表别名

table_name [AS] table_alias;

IN

expr IN (value1, value2, ...)

BETWEEN

expr BETWEEN low_value AND high_value;

LIKE　模糊

单引号

select * from actor where first_name like 'P%';

IS NULL

select first_name, last_name, picture from staff where picture is NUll;

EXISTS

EXISTS 运算符用来判断一个子查询是否返回行。如果一个子查询返回了至少一个行，则 EXISTS 返回真，否则返回假。

select film_id, title, description 
from film f where exists 
(select 1 from inventory i where i.film_id = f.film_id);

ALL

ALL 运算符用于将一个值与一个子查询返回的所有的值进行比较，并返回布尔值。 ALL 运算符可==用于 WHERE ==子句中过滤指定条件行。

# 查找大于等于所有的租金的影片的数量
select count(*) from film 
where 
rental_rate >= ALL(select rental_rate from film);

ANY

# 检查是否存在租金高于 5 美元的影片
select 5 < any(select rental_rate from film);

GROUP BY　将结果集按照某个维度进行汇总

使用 GROUP BY 子句，HAVING 子句和聚合函数 sum() 从 payment 表中查找总消费金额在 180 美元以上的客户

 select customer_id, sum(amount) as total 
 from payment 
 group by customer_id 
 having sum(amount) > 180 
 order by total desc;

HAVING

==HAVING 子句必须与 GROUP BY 子句一起使用，　不能在 HAVING 子句中使用列别名, ==
参考上面group by 例子即可。

一次查询中生成多个维度的报表，三种实现

GROUPING SETS
ROLLUP
CUBE

JOIN

表关联查询，有六类：

内连接 (inner join)　符合ON 连接的　交集
交叉连接（cross join）　# 笛卡尔积
自然连接 (natural join)　# 隐式的内连接
左连接 (left join)　 #
右连接 (right join) #
全连接 (full join) # 左连接和右连接的并集

创建　student, student_score 两个表，并insert 三条数据，得到如下
student 表数据
student_score 表数据

左连接

在这里插入图片描述
左连接以左表的数据行为基础，根据连接条件匹配右表的每一行，如果匹配成功则将左表和右表的行组合成新的数据行返回；如果匹配不成功则将左表的行和 NULL 值组合成新的数据行返回。

右连接和左连接相反, 不赘述~~~~~~~~~~~~

全连接

使用 USING 代替连接字段

SELECT
  student.*,
  student_score.*
FROM
  student
  FULL JOIN student_score USING(student_id);

子查询

将子查询使用在 EXISTS, IN, ANY, ALL 等表达式中，也可以将子查询的结果直接和值比较。

SELECT count(*)
FROM film
WHERE rental_rate > ( # 子查询的结果直接和值比较
    SELECT avg(rental_rate)
    FROM film
  );

CTE (后面再补，太陌生啦，哎)

UNION

求集合间的并集，　用法同下

INTERSECT

求两个查询的交集

SELECT_statement_1
INTERSECT
SELECT_statement_2
INTERSECT
SELECT_statement_3
INTERSECT
...
[ORDER BY ...];

EXCEPT

获取影片表 film 中获取评级为 G 的影片，但是不包括那些影片长度大于 55 分钟的影片，请使用如下带有 EXCEPT 运算符的语句：

select film_id, title, rating, length, rental_rate 
from film 
where rating='G' 
except select film_id, title, rating, length, rental_rate 
from film 
where length > 55;

INSERT

drop table if exits student;
create table student 
	(id serial primary key, 
	name varchar(50) not null, 
	gender char(1) not null, 
	birthday date, 
	notes varchar(255));

insert into student (name, gender) values ('Tom', 'M');

INSERT ON CONFLICT

当插入冲突时（已有字段或已有列）。冲突对象中，使用字段名称，或者使用约束名称。

insert into users (nickname, login_name, notes) 
values ('Tim3', 'tim', 'This is Tim3') 
on conflict on constraint users_login_name_key # 等价于on conflict (login_name)
do update set 
	nickname = excluded.nickname, 
	notes = excluded.notes 
returning *;

UPDATE

update customer 
set first_name = 'Tim', 
	last_name = 'Duncan', 
	email= 'Tim.Duncan@sakilacustomer.org' 
where customer_id = 1 
returning first_name, last_name, email;

UPDATE FROM

# 更新 city 表中的城市名称，在其后添加 @ 和国家名
update city_copy a 
set city = city || '@' || b.country 
from country b 
where b.country_id =1 or b.country_id = 2 returning city_id, city;

DELETE

# copy 一个表
CREATE TABLE film_copy AS SELECT * FROM film;
# 删除　表中三行
 delete from film_copy  where film_id in (1,2,3);
# 删除并返回删除的信息
delete from film_copy  where film_id in (4) returning film_id, title;

清空表

当清空一个表中的所有行时
DELETE * FROM table
或者使用 TRUNCATE TABLE 语句。

TRUNCATE 语句和不带任何条件的 DELETE 语句的效果相同。但是 TRUNCATE 速度更快，因为它不扫描表。并且，TRUNCATE 会立即回收磁盘空间。 TRUNCATE 在清空大表时很有用。

简单学习一下json

查找时， -> 返回的值是 JSON 类型， ->> 返回的值是文本类型。
在这里插入图片描述

select 
	detail ->> 'name' as NAME, 
	detail -> 'address' ->> 'country' as country
	from login_logs 
	where detail -> 'address' ->> 'country' = 'US';

请我喝一杯瑞幸，极好

请我喝一杯瑞幸，嘻嘻