2- SQL语句的强化
2024-08-25 22:25:41
查询类型cate_name为 '超极本' 的商品名称、价格 select name,price from goods where cate_name = '超级本'; 显示商品的种类 select cate_name from goods group by cate_name; 求所有电脑产品的平均价格,并且保留两位小数 select round(avg(price),2) as avg_price from goods; 显示每种商品的平均价格 select cate_name,avg(price) from goods group by cate_name; 查询每种类型的商品中 最贵、最便宜、平均价、数量 select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name; 查询所有价格大于平均价格的商品,并且按价格降序排序 select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc; 查询每种类型中最贵的电脑信息
select g.name,g.cate_name,g.price from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new left join goods as g on g_new.cate_name=g.cate_name and g_new.max_price=g.price; insert into goods values(0,'xiatian','笔记本','夏天','',default,default); select g.name,g.cate_name,g.price from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new left join goods as g on g_new.cate_name=g.cate_name and g_new.max_price=g.price order by g.cate_name;
查询所有价格大于平均价格的商品,并且按价格降序排序 select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods) #括号内的sql语句结尾没有分号;
order by price desc;
最新文章
- WCF学习系列一【WCF Interview Questions-Part 1 翻译系列】
- IOS各种集合遍历效率对比
- ZeroMQ接口函数之 :zmq_ctx_shutdown - 停止一个ZMQ context
- 【转】lower_case_table_names=1 让MySQL不区分大小写
- linux传输大文件
- php-数据库访问--增、删、改
- Fatal error: Call to undefined function imagettftext()解决办法
- SVM3 Soft Margin SVM
- 【iOS开发】UIWebView与JavaScript(JS) 回调交互
- css+div如何解决文字溢出
- Windows服务编程Demo
- C++中不可重载的5个运算符
- mysql博客
- Eclipse插件安装总结
- Extended ComboBox添加图标
- SignalR的另类实现技巧
- ABP文档笔记 - 数据过滤
- [NOIP2018]旅行(数据加强版)(图论+基环树)
- Java中用字符串常量赋值和使用new构造String对象的区别
- Java中创建对象的五种方式