SQLite查询语句

课堂笔记

--查询全体顾客的顾客编号与姓名
select Cno,cname from Customer

--查询所有顾客购买商品的年份信息，显示 Cno, Gno 和年份信息三列。
--提示：将日期处理成年份的函数为 strftime(‘%Y’,Date)
select cno,gno,strftime('%Y',data)
from Purchase

/*查询所有顾客购买商品的年份信息，并在其年份信息前加入一列，此列的每行数据均为“Year of Purchase”常量值 。*/
select Cno,Gno,'Year of Purchase' from Purchase

--查询购买表，并改变列标题名称。
select Cno as 顾客编号,
Gno 商品编号,
'Year of Purchase' AS 年份提示,
strftime('%Y',Date) 年份
from Purchase

--根据地址查询姓名
select cname from Customer
where address='北京市'

--查询所有价格在 500 元以下的商品的名称及价格
select gname,price from Goods
where price<500

--查询商品价格在 100～ 1000 之间的商品的名称、价格和供应商。
select gname,price,provider from Goods
where price>=100 and price<=1000

select gname,price,provider from Goods
where price between 200 and 1000

select gname,price,provider from Goods
where not price between 100 and 1

--查询显示所有商品类别没有重复
select distinct category from Goods

--商品按价格排序（升序）
select * from Goods
order by price 

select * from Goods
order by price asc

select * from Goods
where price > 100
order by price asc

--商品按价格排序降序
select * from Goods
order by price  desc

select cno,amount from Purchase
where gno='G002'
order by amount desc

--按类别升序排序
select * from Goods
order by category

select * from Goods
order by category

select * from Goods
order by category ,price

select * from Goods
order by category ,price desc --desc表降序

--统计顾客总人数
select count(*) as 人数 from Customer

--统计购买了商品的顾客的人数(去重，人数)
select count(distinct cno) from Purchase

--计算服装类商品的评均价格
select avg(price) from Goods  --avg()求平均
where category='服装'

--查询日常用品商品的最高价格和最低价格
select max(price),min(price) from Goods
where category like '日%'

或

select max(price),min(price) from Goods
where category='日常用品'

select max(price),min(price),avg(price) from Goods
where category='日常用品'

--对查询结果进行分组计算
GROUP BY <分组依据列>[,..n]
[HAVING<组提取条件>]

select cno,amount
from Purchase
group by gno

select *,cno,amount
from Purchase
group by gno

--统计每种商品的购买人数，列出商品编号和人数
select gno,count(distinct cno)
from Purchase
group by gno

--统计每类商品的品种数量和平均价格
select category,Cno,price
from Goods
group by category

--数量，平均
select category,count(Cno),avg(price)
from Goods
group by category

--查询至少2位顾客购买商品的编号，购买人数
select gno,count(distinct cno)
from Purchase
group by gno
	having count(distinct cno)>=2
	
--多表连接查询
select * 
from Customer join Purchase
where Customer.Cno=Purchase.Cno

--去掉重复列
select Customer.cno,cname,Date
from Customer join Purchase
where Customer.Cno=Purchase.Cno

--查询北京市顾客地址，商品编号，数量
select Customer.cno,cname,Date
from Customer join Purchase
where Customer.Cno=Purchase.Cno
	and Customer.Address='北京市'
	
--查询类别数量，平均数，最大值，最小值
select category,count(Cno),avg(price),max(price),min(price)
from Goods
group by category

select category as 类别,count(Cno),
		avg(price) 平均价格,
		max(price),min(price)
from Goods
group by category

--嵌套查询or子查询
select Cname,Address
from Customer
where address in (
	select address from Customer
	where Cname='王刚'
	)
	and Cname!='王刚'