数据库语句之单表查询语句

• 单表查询

查询语句基本格式

SELECT <查询列>

[FROM <数据源>]

[WHERE <行条件表达式>]

[GROUP BY <分组依据>]

[HAVING <组选择条件>]

[ORDER BY <排序依据>]

实例：

1.查询全部

select * from buydetil

2.投影运算即选列查询

select buyno.goodsno,unit from buydetail

select distinct buyno from buydetail

3.包含计算列

select * ,price*quantity from buydetail

取别名

select * ,price*quantity amount from buydetail

或

select * ,price*quantity as amount from buydetail

price*quantity为计算列

4.选择运算查询即选列查询

select goodsNo,price,quantity from buydetail where buyno='B09002' and goodno like '03%'  and price>200

 5.分组统计

根据某些列的列值进行分组，列值相同的为一组，然后对每一组用聚合函数统计

常用的聚合函数：

SUM(),COUNT(),MAX(),MIN(),AVG()

(1)查询所有进货单总金额

select sum(price*quantity) from buydetail

(2)查询进货单总行数

select count(*) from buydetail

(3)查询进货单总张数

select count(distinct buyno) from buydetail     //dinstinct去除重复行

(4)查询每张订货单的总金额，查询结果包括进货单号，合计金额

select buyno sum(price*quantity) from buydetail group by buyno

(5)查询合计金额大于4000的进货单

select buyno sum(price*quantity) from buydetail group by buyno having sum(price*quantity)>4000

6.排序

(1)查询进货单明细表，进货单号按升序，同一进货单的行按商品降序

select * from buydetail order by buyno asc,price desc