首页 / MYSQL / mysql查询表格方法汇总3
mysql查询表格方法汇总3
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql查询表格方法汇总3，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4541字，纯文字阅读大概需要7分钟。
内容图文
gdcode,gdname,gdprice
-> from goods
-> where tid=1
-> order by gdprice;
结果
+--------+-----------+---------+
| gdcode | gdname | gdprice |
+--------+-----------+---------+
| 001 | 迷彩帽 | 63 |
| 008 | A字裙 | 128 |
| 005 | 运动鞋 | 400 |
+--------+-----------+---------+
3 rows in set (0.02 sec)
mysql> select gdcode,gdname,gdsaleqty,gdprice -> from goods -> where tid=1 -> order by gdsaleqty desc,gdprice;
结果
+--------+-----------+-----------+---------+ | gdcode | gdname | gdsaleqty | gdprice | +--------+-----------+-----------+---------+ | 008 | A字裙 | 200 | 128 | | 005 | 运动鞋 | 200 | 400 | | 001 | 迷彩帽 | 29 | 63 | +--------+-----------+-----------+---------+ 3 rows in set (0.00 sec)
mysql> select gdcode,gdname,gdprice -> from goods -> limit 3; 结果 +--------+--------------+---------+ | gdcode | gdname | gdprice | +--------+--------------+---------+ | 001 | 迷彩帽 | 63 | | 003 | 牛肉干 | 94 | | 004 | 零食礼包 | 145 | +--------+--------------+---------+ 3 rows in set (0.00 sec)
拓展：查询表格从第四行开始，三行数据；
首先查询表格数据：
mysql> select gdcode,gdname,gdprice from goods; +--------+---------------+---------+ | gdcode | gdname | gdprice | +--------+---------------+---------+ | 001 | 迷彩帽 | 63 | | 003 | 牛肉干 | 94 | | 004 | 零食礼包 | 145 | | 005 | 运动鞋 | 400 | | 006 | 咖啡壶 | 50 | | 008 | A字裙 | 128 | | 009 | LED小台灯 | 29 | | 010 | 华为P9_PLUS | 3980 | +--------+---------------+---------+ 8 rows in set (0.00 sec)
0
1
2
3
...
mysql> select gdcode,gdname,gdprice -> from goods -> limit 4,3; +--------+--------------+---------+ | gdcode | gdname | gdprice | +--------+--------------+---------+ | 006 | 咖啡壶 | 50 | | 008 | A字裙 | 128 | | 009 | LED小台灯 | 29 | +--------+--------------+---------+ 3 rows in set (0.00 sec)
mysql> select sum(gdsaleqty) from goods; +----------------+ | sum(gdsaleqty) | +----------------+ | 807 | +----------------+ 1 row in set (0.00 sec) mysql> select max(gdsaleqty) from goods; +----------------+ | max(gdsaleqty) | +----------------+ | 234 | +----------------+ 1 row in set (0.00 sec)
首先创建orders表
use onlinedb; SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for orders -- ---------------------------- DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `oID` int(11) NOT NULL AUTO_INCREMENT, `uID` int(11) DEFAULT NULL, `oTime` datetime NOT NULL, `oTotal` float DEFAULT NULL, PRIMARY KEY (`oID`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of orders -- ---------------------------- INSERT INTO `orders` VALUES (‘1‘, ‘1‘, ‘2017-12-04 08:45:07‘, ‘83‘); INSERT INTO `orders` VALUES (‘2‘, ‘3‘, ‘2017-12-04 08:45:07‘, ‘144‘); INSERT INTO `orders` VALUES (‘3‘, ‘9‘, ‘2017-12-04 08:45:07‘, ‘29‘); INSERT INTO `orders` VALUES (‘4‘, ‘8‘, ‘2017-12-04 08:45:07‘, ‘1049‘); INSERT INTO `orders` VALUES (‘5‘, ‘4‘, ‘2017-12-04 08:45:07‘, ‘557‘); INSERT INTO `orders` VALUES (‘6‘, ‘3‘, ‘2017-12-04 08:45:07‘, ‘1049‘);
直接查询uid是6个人
mysql> select count(uID) from orders; +------------+ | count(uID) | +------------+ | 6 | +------------+ 1 row in set (0.00 sec)
mysql> select count(distinct uID) from orders; +---------------------+ | count(distinct uID) | +---------------------+ | 5 | +---------------------+ 1 row in set (0.00 sec)
加上distinct是五个人；
mysql>select uID,uName,uSex,uCity from users group by uCity;
mysql> select uCity,count(*) from users -> group by uCity;
mysql> select uCity,GROUP_CONCAT(uID)as uIDs -> from users -> GROUP BY uCity;
mysql> select uCity,GROUP_CONCAT(uID ORDER BY uID SEPARATOR‘_‘)as ‘编号‘ -> from users -> GROUP BY uCity;
mysql> select uCity,count(*) from users -> where uCity in(‘长沙‘,‘上海‘) -> GROUP BY uCity -> WITH ROLLUP;
mysql> select uCity,count(*) from users -> GROUP BY uCity -> HAVING COUNT(*)>=3;
语句中有聚合函数必须用having
mysql查询表格方法汇总3
标签：sts span sep 技术 一个 长沙 运动 rom ice
本文系统来源：https://www.cnblogs.com/yssgxxy/p/12751052.html
内容总结
以上是互联网集市为您收集整理的mysql查询表格方法汇总3全部内容，希望文章能够帮你解决mysql查询表格方法汇总3所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】