本文主要介绍关于sql,sqlite,数据库的知识点,对【SQL基础编程】和【sql编程语句】有兴趣的朋友可以看下由【加菲的猫不听话】投稿的技术文章,希望该技术和经验能帮到你解决你所遇的【】相关技术问题。
安装:下载Beekeeper-studio
运行:可导入一个简单数据库进行实验
在希冀平台实验输入sql的方法:
第一步:把sql拖入%reload_ext sql
%sql sqlite:///需要你输入sql的文件名.sqlite
%sql ATTACH '需要你输入sql的文件名.sqlite' AS e_store_db;
如图所示:
第三步:对sql数据库进行操作时前提要输入%%sql,之后再写你需要编辑的代码,否则运行不了 二.SQL的单表操作 1.sql基础三步书写顺序:select…from…where…
from:定位要载入的表 → 定位hero_data_table数据表(hero_data_table为被剪辑的数据名称)
select:显示出想要出现的数据,一行一行的按条件筛选出目标行,*代表选中所有的行,运行出的结果是sql的所有数据
拓展:
1.对数据库名称简单重命名为h:from 数据库名称 as h
2.选中数据库中hero一行(可以同时选中多行中间用,
隔开):select h.hero
3.在select中将自己编辑的新的数据命名 eg:h.life + h.speed + h.attack as '
显示数字的总和'
)
如图:
where:输入筛选目标的代码,显示出来目标行中的指定列
注意事项:大前提是输入的代码应全为英文,标点符号也是,在select中不想显示所有行,可以h.想要选中某一行的名称,用英文逗号隔开,数据库命名为h之后,h后面的内容必须是数据库每一列名称的内容
sql可以单独经行运算
含义运算符加法运算+减法运算_乘法运算*除法运算/求余运算%就好像?select语句不需要from就可以独立成句显示常量一样,select语句也可以独立成句进行简单四则运算。
代码代码结果select 3+23+2=5select 3/2select 3/2=1select 3/2.0select 3/2.0=1.5select (3*(2+3))/5-6 as ‘复合运算’复合运算 = -3既然独立成句语境下支持,那么在更加复杂的使用环境下也同样支持,比如:
产品档位划分如下
使用select和where四则运算得到极简文具公司数据库处在2,3档位的文具
代码如下:
select p.产品_描述,p.产品_售价, p.产品_售价/10+1 as '价格档位'
from 产品 as p
where (p.产品_售价/10+1) in (2,3)
代码结果如下:
产品_描述产品_售价价格档位自动铅笔15216开笔记本223中性笔182马克笔102文件夹203 3.limit (限制查询结果个数) 比如对于以下代码select s.销售_订单_ID, s.销售数量
from 销售 as s
其运行的结果就有213行
如果我们指向显示,比如10行,就可以用以下的代码实现select s.销售_订单_ID, s.销售数量
from 销售 as s
**limit 10 -- 限制只显示10行结果
代码结果如下:有十行内容
销售_订单_ID销售数量8000119456800161903780037395828004015012800471962180048266378006417921800752301580086118608010616212 limit 之后一般是数字 4.order by(排序) order by XXX desc 降序 order by XXX asc 升序 应用举例:在产品表中,价格档位,售价降序排列where主要为筛选功能,对数据库进行一定规则的限制,从而达到我们需要得到的数据 (除筛选外还具有连接多表的功能,目前先不展开)
比较运算符: 条件意义表达式举例1=
判断相等
score = 80
你现在学的SQL不是编程语言→ = 在编程语言中是赋值的意思
>
判断大于
score > 80
>=
判断大于或相等
score >= 80
<
判断小于
score <80
<=
判断小于或相等
score <= 80
<>
判断不相等
score <> 80
有些数据库
!=
LIKE
判断字符串相似
name LIKE 'ab%'
%
表示
任意字符,例如’ab%‘将匹配’ab’,‘abc’,‘abcd’
is NULL
判断是否是
NULL
score is NULL
用来检测空值
LIKE
的灵活使用
以X
开头 like 'X%'
以X
结尾 like '%X'
包含X
like '%X%'
eg:使用SQL的条件表达式搜索那个叫做ca...
的游戏英雄的全部属性
select *
from hero_data_table as 'h'
where h.Hero like 'ca%'
代码结果:
<条件1> and <条件2>
where (h.attack>60) and (h.Hero like 'L%')
L
开头的游戏英雄在闭区间
[X , Y]
之内
between X and Y
where h.life between 445 and 580
<条件1> or <条件2>
where (h.life > 600) or (h.armor = 20)
IN (X, Y, Z)
where h.Hero in ('Lo', 'Zoe', 'Tariq')
Lo
或者
Zoe
或者
Tariq
where h.life in (445,580)
NOT <条件>
where not h.attack_speed = 1
where h.Hero not in ('Lo', 'Zoe', 'Tariq')
Lo
或者
Zoe
或者
Tariq
指定多复合运算的关系
( )
where (h.life > 500) and (not h.attack_speed = 1) and (not (h.Hero like 'A%'))
select 1000
代码结果
10001000select 1000 , '你好' , '2021-03-18'
代码结果
1000‘你好’‘2021-03-18’1000你好2021-03-18select
1000 as '数字',
'你好' as '字符串',
'2020-10-28' as '日期';
代码结果
数字字符串日期1000你好2020-10-28 例子:希望在极简文具数据库中找到价格<20的产品,并注明商品特征是便宜商品select
'便宜产品' as '产品特征',
p.产品_描述 as '产品名称',
p.产品_售价 as '产品价格'
from
产品 as p
where
p.产品_售价 <20
结果:
产品特征产品名称产品价格便宜产品自动铅笔15便宜产品32开笔记本8便宜产品中性笔18便宜产品橡皮5便宜产品马克笔10 7.distinct (把结果中重复的行删除)如果要得到极简文具公司数据库的所有收入超过一百万的订单的SQL代码如下:
select
s.销售订单_日期_月 as '有超过百万订单月'
from
销售 as s
where
s.销售数量 * s.产品_价格 > 1000000
order by
有超过百万订单月
代码结果:
有超过百万订单月1222345561112以上我们发现2月和5月的订单都有重复超过一万的,而我们想要的是月份,不让它有重复,我们只需要在select后加个distinct便可实现
select distinct
s.销售订单_日期_月 as '有超过百万订单月'
from
销售 as s
where
s.销售数量 * s.产品_价格 > 1000000
order by
有超过百万订单月
代码结果:
有超过百万订单月1234561112注意:如果放在多个列之前,则会只删除所有列内容都同时重复的行,比如以下代码:
select distinct
s.销售订单_日期_月 as '有百万单月',
s.销售数量 * s.产品_价格 as '销售额'
from
销售 as s
where
s.销售数量 * s.产品_价格 > 1000000
order by
s.销售订单_日期_月, 销售额
结果:
(函数大致含义:f(x)=y)
聚合函数 显示方式控制函数 (1) 聚合函数:sum()
——求和函数
很多时候我们需要计算搜索出来的数据的综合,就需要用到sum()函数,比如:
count()
——计数函数
如果我们想要知道第十一月份有多少笔订单
代码如下:
select
count(s.销售_订单_ID) as '11月订单数'
from
销售 as s
where
s.销售订单_日期_月 = 11
结果:
avg()
——平均值函数
sum()/count()这种表示运算出的结果不带小数
eg:求极简文具库11月份的产品单位订单平均销量
avg直接表示:
select
avg(s.销售数量) as '11月平均每笔订单销量'
from
销售 as s
where
s.销售订单_日期_月 = 11
结果:
sum()/count()的两种表示:
1.
select
sum(s.销售数量)/count(s.销售数量) as '11月平均每笔订单销量'
from
销售 as s
where
s.销售订单_日期_月 = 11
select sum(s.销售数量)/count(客户_ID) as '11月平均每笔订单销量'
from 销售 as s
where s.销售订单_日期_月 = 11?
结果:
min()| max()
——最小最大值函数
在select中筛选出最大最小的数,用where也可以实现,但比直接用最大最小麻烦一些
eg:使用min()和max()获得极简文具公司数据库中最贵和最便宜的产品售价
1.min()max()直接实现:
select
max(p.产品_售价) as '最高售价',
min(p.产品_售价) as '最低售价'
from
产品 as p
结果:
select distinct p.产品_价格
from 销售 as p
order by p.产品_价格 desc
limit 1
最低售价:
select distinct p.产品_价格
from 销售 as p
order by p.产品_价格 asc
limit 1
区别:where语句中主要通过distinct,order by,limit来实现的
group by
——分组函数
eg:我们想要得到每个月份的销量总和(这就需要以月份为对象进行分组,然后再求出每个月份的销售总和)
未分组前的数据:
select p.销售订单_日期_月,
sum(p.销售数量) as '每月销售总额'
from 销售 as p
group by p.销售订单_日期_月
结果如下:
round
——控制保留位小数的结果
由avg()函数中的结果得知,小数点后面有很多位,如果我们想要保留两位小数可以用round函数实现
select
round(avg(s.销售数量),2) as '11月平均每笔订单销量'
from
销售 as s
where
s.销售订单_日期_月 = 11
代码结果:
concat
——把分开的两列合成在一个结果中
理解说明:
在英雄数据库中Hero和life是独立的两个属性
select
h.Hero || ' 的生命值是 ' || h.life as '生命值>580的英雄描述'
from
hero_data_table as h
where
h.life > 580
mysql中:代码如下:
select
concat(h.Hero,' 的生命值是 ',h.life) as '生命值>580的英雄描述'
from
hero_data_table as h
where
h.life > 580
两种方式的代码结果都是一样的:
(简单来说就是在where里面嵌套一个或者加一个sql的语句)
举个栗子:在英雄数据库中找到比Nesus更快的游戏英雄
思路:首先,要找到Nesus这个英雄的速度,再以它为筛选条件输入在where中找到比它更快的英雄
select h1.speed
from hero_data_table as h1
where h1.Hero = 'Nesus'
结果:
select *
from hero_data_table as h2
where h2.speed > ( -- 以下子查询语句得到Nesus的速度
select h1.speed
from hero_data_table as h1
where h1.Hero='Nesus');
代码结果:
(与where子查询类似,这次是在from中嵌套一个sql语句)
举个栗子:在英雄数据库中找到比Nesus快的英雄中魔法值大于200的游戏英雄
大致思路:我们是要在嵌套中再加一个嵌套,我们已经有了比Nesus更快的游戏英雄的代码了(里面已经包含了一个嵌套了),只需要把它嵌套在from中
首先,已知比Nesus快的代码了,只需要写出魔法值>200的代码
代码如下:
select *
from --比Nesus快的所有的游戏英雄--
where magic>200
所以将两个代码结合:如下
select *
from ( -- 以下子查询得到比Nesus速度快的所有游戏英雄数据
select *
from hero_data_table as h2
where h2.speed > ( -- 以下子查询语句得到Nesus的速度
select h1.speed
from hero_data_table as h1
where h1.Hero='Nesus'
)
) as h3
where h3.magic > 200;
代码结果:
其实from子查询是可以直接转化为where子查询的,比如针对找到比Nesus快的所有的游戏英雄中魔法值大于200的游戏英雄的例子
我们可以进行如下转换:
select *
from hero_data_table as h3
where
h3.magic > 200
and
h3.Hero in(
select h2.Hero
from hero_data_table as h2
where h2.speed > ( -- 以下子查询语句得到Nesus的速度
select h1.speed
from hero_data_table as h1
where h1.Hero='Nesus'
)
)
不管那种方式,代码最后运行的结果都是一样的:
(与where和from子循环一样与,是在select中嵌套sql语句)
大致思路:
select (select count(h.产品_售价 is null) as '无价格产品个数'
from 产品 as h
where h.产品_售价 is null) as '无价格产品个数',(select count(h.产品_售价) as '有价格产品个数'
from 产品 as h) as '有价格产品个数'
代码结果:
2.JOIN——连接多个数据库(或多表)(简单来说就是通过表中的相同的部分把表连接起来)
客户_ID
关联起来
select
c.客户_名称,
s.销售订单_日期,
s.销售数量 * s.产品_价格 as '订单金额'
from
客户 as c JOIN 销售 as s
ON
c.客户_ID = s.客户_ID
where
c.客户_ID = 101
代码结果
客户_名称销售订单_日期订单金额图龙信息信息有限公司1/10/2020352632图龙信息信息有限公司1/26/202078112图龙信息信息有限公司1/31/2020771584图龙信息信息有限公司1/7/202050224图龙信息信息有限公司4/17/2020505960图龙信息信息有限公司6/18/2020516990图龙信息信息有限公司6/2/2020809116图龙信息信息有限公司6/29/2020840366 多表连接代码如下:select ...
from
A JOIN
B on A.id1 = B.id1
C on A.id2 = C.id2
应用:通过电子专卖店数据库,对比2018年6,7,8月份的销售额,要求保留小数后两位,总销售额以亿为单位。
实现结果:
select c.年度,c.月,round(sum(h.销售单价*s.销售数量)/100000000,2) as '销售总额_亿元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
join 日期 as c on s.订单日期=c.日期
where c.月 in (6,7,8) and c.年度=2018
group by c.月,c.年度
3.null与内连接与外连接
-null
(在有些环境下none与null表达一样)(null的意思是空值,表示未知)
应用实例:判断极简文具公司数据库_升级版中是否有产品未定产品_售价,或者缺少产品_代码
代码如下:
select *
from
产品 as p
where
p.产品_代码 is NULL or p.产品_售价 is NULL
代码结果:
inner join
内连接
交集
即:只显示A和B中
A.Key=B.Key
的那些行,如果在A中有些
A.Key
在B中没有,无法进行匹配,则不显示,例如空值,A中某一部分如果有空值与B无法匹配,则那一行数据就不会有显示。举个栗子:在极简文具库升级版中显示出,产品_代码,产品_描述,销售_订单_ID所有的信息
select
p.产品_代码,
p.产品_描述,
s.销售_订单_ID
from
产品 as p
JOIN
销售 as s
ON p.产品_代码 = s.产品_代码
order by
s.销售_订单_ID
结果:
outter join
外连接
select p.产品_代码,p.产品_描述,s.销售_订单_ID
from 产品 as p left join 销售 as s
on p.产品_代码 = s.产品_代码
order by s.销售_订单_ID asc
代码结果:
其中里面最核心的地方是 left join (左连接),它显示出了相交的部分与没有相交的部分,显示没有相交的部分是产品表中的空值部分,同理也有右连接但sqlite不支持,那想要显示出销售表中的空值只需要把from中销售表与产品表中的位置互换就可以。
本文《SQL基础编程》版权归加菲的猫不听话所有,引用SQL基础编程需遵循CC 4.0 BY-SA版权协议。