课堂笔记
--查询全体顾客的顾客编号与姓名
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!='王刚'