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

导入数据

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

  • 搭建数据库

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

  • 导入数据表

    到这个地址https://github.com/jOOQ/sakila/tree/main/postgres-sakila-db下载两个文件
     
    1.postgres-sakila-schema.sql - 此文件包含了创建数据库结构的 sql 脚本,包含表,函数,存储过程,触发器等。
    2.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

  1. 普通查找
 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;

举个例子

  1. where 子句和LIKE 混用
SELECT first_name,
  last_name
FROM customer
WHERE first_name LIKE 'ANN%';
  1. 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;
  1. 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 例子即可。

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

  1. GROUPING SETS
  2. ROLLUP
  3. 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';

请我喝一杯瑞幸,极好

请我喝一杯瑞幸,嘻嘻

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值