导入数据
在正式开始学习之前,我们需要有个数据库,再有一个数据表。
-
搭建数据库
关于如何搭建一个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
- 普通查找
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 三条数据,得到如下
左连接
左连接以左表的数据行为基础,根据连接条件匹配右表的每一行,如果匹配成功则将左表和右表的行组合成新的数据行返回;如果匹配不成功则将左表的行和 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';