postgres 数据库入门

适用场景

刚学习数据库，想要了解postgres数据库的基本语法和使用方法。

核心概念

• 数据库：用于存储和管理数据的系统。
• SQL：结构化查询语言，用于操作数据库。
• Postgres：一种流行的关系型数据库管理系统，支持丰富的数据类型和功能。

实践要点

• 学习基本的SQL语法，如SELECT、INSERT、UPDATE、DELETE等。
• 了解Postgres特有的功能，如JSONB数据类型、窗口函数等。
• 熟悉数据库设计原则，如范式、索引等。

示例

一、基础查询

-- 一、 select * 查询
-- 查询用户表
select * from users
--查询全部商品
select * from products
-- 查询全部订单
select * from orders
-- 查询全部订单明细
select * from order_items

-- 基础查询
-- where 条件过滤、order by 排序、limit 分页、聚合查询、多表链接 jion
-- 查询所有的用户姓名城市
select name,city from users
-- 查询价格大于100的商品
select * from products where price > 100
-- 查询最近创建的5条订单
select * from orders order by created_at desc limit 5

-- 练习一
-- 1、查询users表中的所有邮箱
select email from users
-- 2、查询products表中价格低于100的商品名称和价格
select name,price from products where price < 100
-- 3、查询orders表中的状态为pending的订单
select * from orders where status = 'pending'
-- 4、查询products表中最便宜的三个商品
select * from products order by price asc limit 3

二、条件查询

-- 练习二
-- 包含练习内容 >、<、=、and、or、in、between、like
-- 1、查询products表中的价格大于200的商品和名称
select name,price from products where price > 200
-- 2、查询users表中 城市名为北京且姓名为张三的用户
select * from users where city = '北京' and name = '张三'
-- 3、查询orders中状态为pending或cancelles的订单
select * from orders where status = 'pending' or status = 'cancelled'
-- 4、查询products中价格在50-300之间的商品
select * from products where price >= 50 and price <= 300
select * from products where price between 50 and 300
-- 5、查询products中名称包含“机”的商品
select * from products where name like '%机%'
-- 注意 like区分大小写 ILIKE = case-insensitive LIKE 忽略大小写

三、聚合统计

-- 二、聚合统计
-- 例如：
-- 一共有多少人-订单总数是多少
-- 总金额是多少
-- 平均价格是多少
-- 最贵的商品多少钱
-- 每个分类有多少商品这些都属于聚合统计。
-- 涉及内容 count、sum、avg、max、min、group by

-- 1、count 计数用于统计有多少条记录
-- 统计用户总数
select count(*) from users
-- 2、sum 求和用于把某一列加起来
-- 统计所有订单金额
select sum(total_amount) from orders
-- 3、avg 平均值用于求平均数
-- 统计商品平均价格
select avg(price) from products
-- 4、max 用于查找最大值
-- 查询最昂贵的商品价格
select max(price) from products
-- 5、min 用于查找最小值
-- 查询最便宜的商品价格
select min(price) from products
-- 6、group by 分组统计
-- 例如：每个城市有多少用户
-- 每种商品分类有多少个商品
-- 每种订单状态有多少订单就要用 GROUP BY
-- 统计每个城市有多少用户
select city,count(*) from users group by city

-- 练习一
-- 1、统计users表中有多少个用户
select count(*) from users
-- 2、统计orders表中的总金额
select sum(total_amount) from orders
-- 3、查询products表中的平均价格
select avg(price) from products
-- 4、查询products表中最昂贵商品的价格
select max(price) from products
-- 5、统计orders表中每种状态有多少条订单
select status,count(*) from orders group by status

四、分组筛选与排序

-- 三、group by、having、order by
-- group by 分组统计
-- 1、统计orders每种订单状态有多条订单
select status,count(*) from orders group by status
-- 2、order by 排序 如果想按数量从大到小排序就加
-- 按订单数量多到少排序
select status,count(*) from orders group by status order by count(*) desc
-- 3、having 分组后筛选
-- 查询订单数大于2的状态
select status,count(*) from orders group by status having count(*) > 2
-- 意思：先按状态分组，再保留数量大于2的组
-- 4 where 和 having可以一起使用
-- 先查已支付订单，再统计每个用户有多少条，最后只保留订单数量大于1的用户
select user_id,count(*)
from orders
where status = 'paid'
group by user_id having count(*) >= 1

-- 练习一
-- 1、统计users表中每个城市有多少用户
select city,count(*) from users group by city
-- 2、统计orders表中每种状态有多少条订单，并按数量从高到低排序
select status,count(*) from orders group by status order by count(*) desc
-- 3、统计products表中分类有多少商品，只显示商品数量大于2的分类
select category,count(*) from products group by category having count(*) >2
-- 4、统计orders表中每个用户有多少条已支付订单，只显示订单数大于等于1的用户
select user_id,count(*) from orders where status = 'paid' group by user_id having count(*) >= 1
-- 5、统计products表中分类的平均价格，并按平均价格从高到低排序
select category,avg(price) from products group by category order by avg(price) desc

五、多表查询

-- 四、多表查询 jion
-- 前言：
-- 前面查的都是一张表，比如：
--   查用户表
--   查订单表
--   查商品表但实际工作中，经常要把多张表关联起来查。
-- 比如：
--   查询订单对应的用户名
--   查询每个用户下了多少订单
--   查询哪些用户下过单
--   查询用户信息和订单金额一起显示这些都要用到 JOIN。
-- 使用关键字 jion、inner jion、left jion
-- jion + 聚合统计
-- 1、统计每个用户下了多少订单
select u.name,count(o.id)
from users u left join orders o
on u.id = o.user_id
group by u.name
-- 先把用户和订单关联
-- 再按用户名分组-统计每个用户有多少订单---
--2、统计每个用户订单总金额
select u.name,sum(o.total_amount)
from users u left join orders o
on u.id = o.user_id
group by u.name

-- 练习
-- 1、查询每条订单对应的用户姓名和订单金额
select u.name,o.id,o.total_amount
from users u inner join orders o
on u.id = o.user_id
-- 2、查询每个用户下了多少订单
select u.name,count(o.id)
from users u left join orders o
on u.id = o.user_id
group by u.name
-- 3、查询每个用户的订单金额
select u.name,COALESCE(sum(o.total_amount),0)
from users u left join orders o
on u.id = o.user_id
group by u.name
-- 4、查询哪些用户下过订单
select distinct u.name
from users u inner join orders o
on u.id = o.user_id
-- 注：可以使用distinct关键字进行去重 可以简化 group by u.name
-- 5、查询没有下过订单的用户
select u.name
from users u left join orders o
on u.id = o.user_id
where o.id is null