目录
1. 多个模糊查询条件（满足）like any (array[ ])
2. 多个模糊查询条件（排除）not like all (array[ ])
1. 注意 on 和 where 的用法不能混淆（用 on 执行 where 的功能，可能会失效）
2. Error 解决：must appear in the GROUP BY clause or be used in an aggregate function
第2种方法：使用 distinct on( ) ，并且将 price 降序排列
2. interval() 函数（和关键字有区别，目前不适用于postgreSQL）
（1）展示数据时，若 booktype 为空值，则赋予'其它'
一、迁移数据库（自增数）
迁移数据库表的数据后，若产生自增数的问题：
即 将一个数据库的表数据，转移到另一个新建的数据库表中，当用户自己新增一笔数据的时候会提示ID重复，无法新增一笔数据。
1. 原因
首先，在关于表设计的时候，当有ID主键的时候，一定会设置自增数，也就是自增主键。自增数+1将会是下一笔新增数据的ID。
然而，例如从原表迁移到新表的数据有25笔，ID栏位已有1~25了，而新表的流程ID才从0开始。
所以，在新增一笔数据的ID会是1，与之前已迁移的数据ID为1的重复。
2. 解决
使用id serial，获取已有的数据个数，赋予流程ID。
即 执行SQL语句：
select nextval('"LuoboDB_book_id_seq"') FROM public."Book"使用 nextval 搭配 _id_seq
3. 注意
id serial只需执行一次即可。例如，在迁移原表的25笔数据到新表中，第一次下serial是将现有数据个数25+流程0=流程ID25，再执行一次（第二次）就是将现有的数据个数25+流程ID25=50，也就是接下来新增的数据将会是以50+1=51的ID。
二、连接数据库 & 生成表、栏位
以下操作适用于python-Django项目、postgreSQL、vscode软件。
（一）连接数据库
1. settings.py 配置
在Django项目的settings.py文件中，设置DATABASES
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'PORT': '5432',
'NAME': 'LuoboDB',
'USER': 'postgres',
'PASSWORD': 'postgres',
'HOST': 'XX.XXX.X.X',
},
}2. 安装 psycopg2 依赖
最好是建立一个虚拟环境，然后在虚拟环境中安装依赖。
pip install psycopg2（二）PgAdmin 匹配数据库
（三）vscode终端 执行迁移
三、清空数据库
以下方式针对的是清空数据表内容，不是删除表。
即清空数据表，并且让 数据表 的自增字段从0开始，实现 id 重新从1开始记录。
注意：在清空的时候，表自身的数据 不能 被其他表的数据关联到。
比如：
A表有栏位a，A表有三笔数据，该栏位的值分别1，2，3；
B表有外键栏位a_id，关联了A表的栏位a。
若B表中有记录关联了A表，a_id=1，则A表不能做清空操作。
操作命令：
truncate table "XXX" restart identity cascade
四、操作数据库（值）- 查询 select
认识 DDL （Data Define Language）
理解：用于定义数据结构，例如创建表 CREATE TABEL T (column int) 语句。 不仅是建表语句，涉及对表名的修改、列的增加删除、更改列的类型等操作都算作SQL DDL。
执行顺序
执行顺序：from、where、group by、having、select、order by、limit
书写顺序：select、from、where、group by、having、order by 、limit
where和having的区别
（一）having
1. 作用
用于过滤筛选分组后的各组数据，解决了where关键字无法与聚合函数一起使用的问题。
2. 特点
（1）可以搭配聚合函数
聚合函数有：count()求数量、sum()求和、avg()平均数、max()最大值、min()
（2）分组之后才执行的
在group by分组后的结果中查询
（二）where
1. 作用
用于过滤符合条件的初始表记录。
2. 特点
（1）不能搭配聚合函数
（2）分组之前就执行了
嵌套子查询
--- 查询出书籍出售价格的平均值，然后都再与平均值进行对比。
select * from book
where price > (select avg(price) FROM book)
limit 100
获取以前查询记录
1. 写法
select * from pg_stat_activity2. 注意
不能查询所有SQL记录。因为数据库pg只要有闲置就会定期 gc 。
garbage clean（垃圾清理）。
解决Error：查询栏位时报错 该栏位不存在
1. ERROR 详情
column "booknum" does not exist LINE 1: SELECT bookNum FROM public.book; HINT: Perhaps you meant to reference the column "book.bookNum" .
2. 原因
传统标准的SQL不区分大小写，但 PostgreSQL 对于数据库中对象名，区分大小写。
3. 解决
在SQL语句中，用双引号把支持"区分大小写"的对象名括起来。
对象名包括：表名、列名、索引名等。
举例：select "bookNum" from public."book" order by id
4. 建议
针对PostgreSQL，在建表或修改栏位名时，不推荐用双引号创建区分大小写的对象。
而是，SQL的key word用大写，其他的名称全都用小写。
模糊查询
1. 多个模糊查询条件（满足）like any (array[ ])
---（1）普通写法
select count(*) from Book -- 20
where (bookname like '数%' or bookname like '语%') -- 数%是3 -- 语%是17
and booktype = '教育'
and bookname is not null
---注意：(bookname like '数%' or bookname like '语%')得用括号括起来（因为优先级，若无括号，则查询结果有问题）
---（2）高级写法
select count(*) from Book -- 20
where bookname like any (array['数%', '语%'])
and booktype = '教育'
and bookname is not null2. 多个模糊查询条件（排除）not like all (array[ ])
select count(*) from Book
where bookname not like all (array['物%', '化%'])
and booktype = '教育'
and bookname is not null3. 左模糊匹配
select bookname from Book
where bookname like '%的事迹'
and booktype = '历史'
and bookname is not null4. 左右模糊匹配
select bookname from Book
where bookname like '%的%'
and bookname is not null5. "_" 任意单个字符模糊匹配
select btype from book where btype like '_的事迹'; --- 是单个字符，而不是多个字符同表多次查询
若针对某张表，第一次的查表结果作为条件，再在同一张表中进行第二次查询的写法如下：
1. 举例
select main.*
from (
select name,authorid,count(id) num from "BOOK" group by name,authorid
) main
where main.type='科幻' and main.num=12. 理解
第一次执行group by，查询count(id)，再第二次用main表的 num 栏位作为查询条件。
这两次的数据其实都是来源于同一张表。
两表联合查询
--- 举例：在Book表中，查询满足以下条件的书籍
--- （1）booktype值能够匹配到BookType表中typestatus是True的typename值
--- （2）bookname不是'数'、'语'开头
--- （3）bookprice升序
select bookname,booktype,bookprice,bookplace
from public."Book"
where booktype in (
select typename
from public.BookType
where typestatus = True)
and bookname not like all (array['数%', '语%'])
order by bookprice ascwith as 起附表
1. 举例
with
newnode as (
select
a.bookstorename,
b.authorname,
c.bookname
from public."STORE" a
left join public."AUTHOR" b
on a.id = b.storeid
left join public."BOOK" c
on b.id = c.authorid
where c.type ='科幻'
)2. 注意
（1）with 子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。
（2）若定义多个新表：则 with 表A as (), 表B as (), C表 as() 。with只需写在最开头的那一次。
left join 串表
针对 left_join ，on 和 where 条件的区别如下：
1. 注意 on 和 where 的用法不能混淆（用 on 执行 where 的功能，可能会失效）
（1）on 条件
on 条件是在临时表生成时，进行过滤。
无论 on 中条件是否为 true，都会返回左表中的记录，不匹配的值赋予 null 。
（2）where 条件
where 条件是在临时表生成好后，再对临时表进行过滤。
只有条件为 true ，才会返回记录，不匹配的将会被过滤掉。
2. on 条件下的右表记录要唯一
即，on 条件下，要保证右表查得的记录只有一笔，而不是多笔。
若是多笔值的话，会造成数据膨胀。
3. 查询时，最好以数量少的为主表。
比如，下方查询的两张表的内容数量：
select count(*) from public."BOOK" -- 10
select count(*) from public."AUTHOR" -- 100数据量小，所以看不出查询速率的差别。（可是数据量增大时，就有差别了）
第一种查询（效率高）：
select distinct
a.name, b.name
from public."AUTHOR" a --10000笔
left join public."BOOK" b --10笔
on a.id = b.authorid第二种查询（效率低）：
select distinct
a.name, b.name
from public."BOOK" a --10笔
left join public."AUTHOR" b --10000笔
on a.id = b.authorid栏位 in ( ，，)
1. 用法
select * from public."BOOK" where id in (1, 2) order by id desc limit 2002. 理解
即：使用栏位 in ('A' , 'B', 'C')，等价于 栏位='A' or 栏位='B' or 栏位='C'。
and 、or 的优先级
and 的优先级高于 or 。
SQL 的 format 拼接字串
1. 注意
SQL 和 原生SQL 的写法有区别。
原生SQL 是属于Django-ORM，SQL 才是真正的数据库 sql 。
2. 举例
针对字串 '苔花如米小' 的 format 拼接用法：
format('苔花%s', '如米小') # SQL
('苔花{ }').format( '如米小') # 原生SQL栏位名换行时，不要漏掉逗号隔开
1. 注意
查询多个栏位，换行时，一定不要漏掉逗号。
2. 原因
如果没有打上逗号，虽不会系统报错，但该栏位会因为没有打逗号而被省略查询。
3. 举例
select id, name, fontcolor,
covercolor
from public."BOOK"若 fontColor 后没有逗号，则该栏位不会被查询。
::text 将栏位值转换成字符串格式
1. 写法
栏位名 ::text = 值
2. 举例
select name from public."BOOK"
where prize::text = '100' 3. 延伸应用（ int 数据类型 的模糊查询）
一般情况下，字符串类型直接用 XXX like ' XX%' 来进行模糊查询。
可是，当类型为 int 的数值类型，则可以先转换成文本类型 XXX ::text，再使用 like ' XX%'
举例:
select name from public."BOOK" where id::text like '1%' limit 20::timestamp 将栏位值转换成时间格式
1. 写法
栏位名 ::timestamp
2. 举例
在更新栏位值时，遇到Error：column "栏位2" is of type timestamp with time zone but expression is of type text
update 表XXX set 表XXX.栏位2 = 表XXX.栏位1 where id = 1理解：栏位2的格式是timestamp，但是栏位1的值是text，所以需要转换栏位1值的格式。
解决：
update 表XXX set 表XXX.栏位2 = 表XXX.栏位1::timestamp where id = 1distinct 去重
1. 查询结果只展示 distinct 中的字段
（1）用法
select distinct name, authorid from BOOK （2）理解
表示去掉 书名 和 作者 这两个字段都完全重复的数据。
即：若两笔数据中，书名一样，作者不一样，则不是重复。只有当书名和作者都是一样的，才算是重复数据。
2. 自定义查询结果的展示字段
（1）用法
select
distinct on (author, btype)
author, btype, price
from "myApp_book"（2）理解
去重条件是 author, btype 字段。展示的字段是 author, btype, price 。
（3）注意
是 distinct on () ，而不是 distinct 。且 distinct on () 后面没有逗号。
group by 分组 or 去重
1. 作用：分类汇总
如：查询所有 authorid，以及它们出现的次数。
select authorid, count(id) from public."BOOK" group by authorid注意：group by 的变量需要在 select 中也写上。
2. Error 解决：must appear in the GROUP BY clause or be used in an aggregate function
举例：
（1）要求
查询 author 的最高书籍定价，并显示书籍类型
（2）错误写法
select author, btype, max(price) price from public."myApp_book" group by author
（3）报错原因
price 用了聚合函数 max 。一旦存在聚合函数，其他没有使用的字段需要group by。
但是，如果btype在group by中，得到的结果不是理想结果，如下图
（4）解决
第1种方法：使用串表
select A.author, B.btype, A.price
from (select author, max(price) price from public."myApp_book" group by author) A
left join (select author, btype, price from public."myApp_book") B
on A.author = B.author and A.price =B.price
--- 或
select *
from (select A.author, btype, B.price from public."myApp_book") as A
left join (select author, max(price) price from public."myApp_book" group by author) B
on A.author = B.author and A.price =B.price) as C
where C.price is not null
第2种方法：使用 distinct on( ) ，并且将 price 降序排列
select distinct on (author)
author, btype, price
from public."myApp_book"
order by author desc, price desc3. 作用：还可去重
和 distinct 是一样的作用。
select authorid from "BOOK" group by authoridrow_number( )
1. 格式
row_number() over([partition by column] order by column [asc | desc] )其中，row_number( ) 是给 "结果集的分区" 中的每个分区的每一行分配一个连续的整数。
partition by 是分区。order by 是排序。
比如：
select row_number() over(partition by column1, column2 order by column3 desc) as newcol from public."table" 其中，根据 column1, column2 进行分组，根据 column3 进行降序。得到的结果是每个分区各自从数字1开始累加。
2. 作用
（1）分页
（2）去重
举例：去重作者，并展示价格最高的书籍信息
方法：搭配 ROW_NUMBER() 的计算结果 = 1
with MainT as (
select author, name, btype, ROW_NUMBER() OVER( PARTITION BY author ORDER BY name desc ) rank_num
from public."myApp_book"
) select author, name, btype from MainT where rank_num=1① with XXX as () 与select 语句之间，不用加上逗号 。
② 用 with 另起一张表的原因：先得到ROW_NUMBER()结果（rank_num 栏位），才能在新表使用该栏位的值。
INTERVAL
1. interval 关键字
（1）格式
interval [+/-]<数值> <时间单位>（2）作用
加减时间。
（3）举例
第一种和第二种的结果是一样的。
--- 第一种
select (day_time - INTERVAL '3' day) as ly_time from mainT
--- 或 '3' day 改写成'3 day'
select (day_time - INTERVAL '3 day') as ly_time from mainT
--- 第二种
select (day_time + INTERVAL '-3 day') as ly_time from mainT2. interval() 函数（和关键字有区别，目前不适用于postgreSQL）
（1）格式
INTERVAL(M, N1, N2, N3, ...)（2）作用
比较函数，分区比较。对列表(M，N1，N2，N3等)中的M，与N1，N2等进行比较。
其中，M是 NULL 返回-1，M<N1返回0，M<N2返回1，M<N3返回2 等。
（3）举例
其中，若scoresT表中的有三行记录，score栏位值分别是56，78，95，则对应的rank分别是0，1，2。
select INTERVAL(score,60,80,100) as price_rank from scoresTto_char() 函数
1. 整型转成字符串类型
（1）第一种：格式以0为基础（小于）
其中，id 值的长度若小于0的数量3，则会用0补全。比如 id 值 12 会转化为ID值 '012'。
--- 第一种
select to_char(id,'000') as ID from "mainT" （2）第二种：格式以9为基础（超过）
其中，id 值的长度若超过9的长度2，则会以#显示。
比如 id 值 123 转化为ID值 ## ；id 值 12 还是为ID值 12。
--- 第二种
select to_char(id,'99') as ID from "mainT" 2. 处理时间、时间戳
（1）作用
将时间、时间戳按照指定的格式输出，结果类型是字符串。
（2）举例
其中，now() 日期格式（2023-10-15 16:44:04.271509+08） 转成 nowTime字符串（2023-10-15 16:44:04 ）
select to_char(now(),'yyyy-mm-dd hh24:mi:ss') as nowTime from "mainT" || 连接符、concat()
PostgreSQL中不能用“+”进行连接字符串，得用“||”、或concat()连接。
举例：id_name的值是id和name连接而成的字串。
1. “||” 连接
select id || name as id_name from "mainT"2. concat() 连接
select concat(id, name) as id_name from "mainT"CAST() 函数
1. 作用
转换类型，方便逻辑运算
2. 举例
（1）将字符串 '100' 转成整型 100 （前提是字串中的数值必须为整数）
SELECT CAST('100' AS INT) （2）将字符串12转换为小数且精度为2的12.00
SELECT CAST('12' AS decimal(9,2)) TO_DATE() 函数
1. 作用
定义date日期格式。
2. 举例
（1）定义为date格式
select TO_DATE('2023-05-20 15:29:27', 'YYYY-MM-DD HH24:MI:SS')
---结果是：2023-05-20（2）转换为date格式，方便时间比较
select * from A表 WHERE 栏位aa < TO_DATE('2023-05-20 15:29:27', 'YYYY-MM-DD HH24:MI:SS');COALESCE() 函数
1. 作用
返回参数列表中第一个非空表达式的值。若所有参数都是 NULL，则返回 NULL。
2. 应用
（1）替换非空值
（2）获取第一个非空值
（3）检查是否都为空
3. 举例
（1）展示数据时，若 booktype 为空值，则赋予'其它'
select bookname, coalesce(booktype, '其它') from public.book（2）排除 author、price 都是空值的记录（只要其中一个有值，就会保留记录，不会被排除）
select * from public.book
where booktype='教育' and coalesce(author, price) is not nullarray_agg() 函数
1. array_agg( )
转换成列表。
2. array_to_string( , )
合并列表值（串接），一般在使用array_agg时，搭配使用。
--- 一个元组代表一笔记录
select author from public."myApp_book" --- 字段名：author，python查得的值：[('小星星',), ('小萝',), ('萝卜干',), ('萝卜干',)]
select distinct author from public."myApp_book" --- 字段名：author，python查得的值：[('小星星',), ('小萝',), ('萝卜干',)]
select array_agg(distinct author) book_author from public."myApp_book" --- 字段名：book_author，python查得的值：[(['小星星', '小萝'],)]
select array_to_string(array_agg(distinct author),'*') book_author from public."myApp_book" ---字段名：book_author，python查得的值：[('小星星*小萝',)] 五、操作数据库（值）- 新增 insert
（一）插入一笔数据
insert into BOOK ( name, prize, onsale ) values ( '数据结构', 60, TRUE);（二）插入多笔数据
insert into BOOK ( name, prize, onsale ) values ( '数据结构', 60, TRUE), ( '操作系统', 59, TRUE)；（三）若有字段和数据库关键字冲突
解决：用双引号括起来，比如下方语句
insert into BOOK("order", name) values (1, '数据结构') 六、操作数据库（值）- 更改 update
（一）写法
update "表" set 栏位='XXX' where 条件（二）举例
update "BOOK" set name='数据结构' where id = 1（三）注意
修改之前一定要注意栏位的类型是 字符串 or 数值
update "BOOK" set prize=60,name='数据结构' where id=1
1084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
