目录
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_activity
2. 注意
不能查询所有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 null
2. 多个模糊查询条件(排除)not like all (array[ ])
select count(*) from Book
where bookname not like all (array['物%', '化%'])
and booktype = '教育'
and bookname is not null
3. 左模糊匹配
select bookname from Book
where bookname like '%的事迹'
and booktype = '历史'
and bookname is not null
4. 左右模糊匹配
select bookname from Book
where bookname like '%的%'
and bookname is not null
5. "_" 任意单个字符模糊匹配
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=1
2. 理解
第一次执行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 asc
with 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 200
2. 理解
即:使用栏位 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 = 1
distinct 去重
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 desc
3. 作用:还可去重
和 distinct 是一样的作用。
select authorid from "BOOK" group by authorid
row_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 mainT
2. 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 scoresT
to_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 null
array_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