[Mysql 查询语句]——对查询结果进一步的操作
distinct 不显示重复的查询结果
(1) 对于表中一些没有唯一性约束的字段,可能存在重复的值,这时可以使用distinct来消除那些查询结果中的重复值
select cust_id from orders; +---------+ | cust_id | +---------+ | 10001 | | 10001 | | 10003 | | 10004 | | 10005 | +---------+ select distinct cust_id from orders; +---------+ | cust_id | +---------+ | 10001 | | 10003 | | 10004 | | 10005 | +---------+
order by 查询结果排序
(1) order by 属性名 [desc|asc]
这个语句的意义是:对于查询结果,指定根据这个属性来进行升序|降序的排列
(2) 排序时对于字段中的NULL值:
asc升序时,NULL在最前面
desc降序时,NULL在最后面
select distinct cust_id from orders order by cust_id desc; +---------+ | cust_id | +---------+ | 10005 | | 10004 | | 10003 | | 10001 | +---------+
limit 限制查询结果数目
(1) limit n: 表示显示前n条记录
(2) limit n,m: 表示从第n位置的记录开始,往后显示m条(注意:第一条记录的位置是0,往后依次类推)
select * from orders where order_num>2006; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ select * from orders where order_num>2006 limit 2; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | +-----------+---------------------+---------+ select * from orders where order_num>2006 limit 0,2; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | +-----------+---------------------+---------+ select * from orders where order_num>2006 limit 2,2; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | +-----------+---------------------+---------+
union合并查询结果
(1) union all: 把所有查询结果合并
(2) union : 把所有查询结果合并且去除重复行
select vend_id from vendors ; +---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1004 | | 1005 | | 1006 | +---------+ select vend_id from products; +---------+ | vend_id | +---------+ | 1001 | | 1001 | | 1001 | | 1002 | | 1002 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1005 | | 1005 | +---------+ select vend_id from vendors union all select vend_id from products; +---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1004 | | 1005 | | 1006 | | 1001 | | 1001 | | 1001 | | 1002 | | 1002 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1005 | | 1005 | +---------+ select vend_id from vendors union select vend_id from products; +---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1004 | | 1005 | | 1006 | +---------+
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版