数据库查看
这是基本的数据库产看，数据库文件在附件中--[url=itkanba.com]market 数据库[/url]：
--从goods表中查询所有商品信息
select * from goods
--从customers表中查询所有客户的姓名和电话
select * from customers
select firstname+lastname,tel from customers
--查询customers表，显示客户号和客户姓名
select customerID,lastname from customers
--查询customers表，显示客户号和客户姓名，使customerid列显示的列名为“客户编号”，将lastname和firstname列组合起来成为结果集中的“姓名”列
select customerID as '客户编号',lastname+firstname as '姓名' from customers
--查看哪些货品有订单
select * from orders
select distinct goodsname from orders
--查看订货金额最大的前三笔订单
select top 3 * from orders order by ordersum desc
--查看货品“tv”的货存量情况
select * from goods
select price from goods where name='tv'
--查看库存量大于1000的货品
select name,price from goods where price>1000
--查询发生在2000/11/24日到2000/11/26日之间的订单
select * from orders
select * from orders where orderdate between '2000/11/24' and '2000/11/26'
--查询北京、上海、天津三地的客户
select * from customers
select * from customers where city in ('北京','上海','天津')
--查询电话号码第一位不是8的客户
select * from customers where tel like '[^8]%'
--查询所有customers表中所有登记了电话号码的客户
select * from customers where not tel='null'
--查询货品为"tv"且订货金额大于5000的订单
select * from orders
select * from orders where ordersum>5000 and goodsname='TV'
--查找goods表中最贵的定价
select * from goods
select max(price) as '最贵定价' from goods
--查询客户的个数
select * from customers
select count(*) from customers
--查看每一种有订单的货品的订货总数
select * from orders
select goodsname,sum(quantity) as '订货总数' from orders group by goodsname
--查询订货总数大于500的货品
select goodsname,sum(quantity) as '总数' from orders group by goodsname having sum(quantity)>500
--查询在2007/4/18日之后的订单的金额
select goodsname,ordersum,orderdate from orders where orderdate>'2007/4/18'
--查询在2007/4/18日之后的订单的金额，并统计每种货品的订单总金额
select goodsname,sum(ordersum) as '订单总金额' from orders where orderdate>'2007/4/18' group by goodsname ...................
页:
[1]
