MySQL - SQL语句执行顺序

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/J080624/article/details/80703903

【1】SQL执行语法顺序

--查询组合字段
(5)select (5-2) distinct(5-3) top(<top_specification>)(5-1)<select_list>
--连表
(1)from (1-J)<left_table><join_type> join <right_table> on <on_predicate>
        (1-A)<left_table><apply_type> apply <right_table_expression> as <alias>
        (1-P)<left_table> pivot (<pivot_specification>) as <alias>
        (1-U)<left_table> unpivot (<unpivot_specification>) as <alias>
--查询条件
(2)where <where_pridicate>
--分组
(3)group by <group_by_specification>
--分组条件
(4)having<having_predicate>
--排序
(6)order by<order_by_list>
(7)limit <n,m>//MySQL支持limit Oracle sqlserver支持top

说明:
1、顺序为有1-7,7个大步骤,然后细分,5-1,5-2,5-3,由小变大顺序,1-J,1-A,1-P,1-U,为并行次序。

2、执行过程中也会相应的产生多个虚拟表,以配合最终的正确查询。


流程图如下:

这里写图片描述


【2】实例分析

① 创建两个表如下:

这里写图片描述


② 创建两个表并插入数据

USE [test]
GO
/****** Object:  Table [dbo].[Member]    Script Date: 2014/12/22 14:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Member](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](30) NULL,
    [phone] [varchar](15) NULL,
 CONSTRAINT [PK_MEMBER] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Order]    Script Date: 2014/12/22 14:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [member_id] [int] NULL,
    [status] [int] NULL,
    [createTime] [datetime] NULL,
 CONSTRAINT [PK_ORDER] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Member] ON 

GO
INSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (1, N'张龙豪', N'18501733702')
GO
INSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (2, N'Jim', N'15039512688')
GO
INSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (3, N'Tom', N'15139512854')
GO
INSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (4, N'Lulu', N'15687425583')
GO
INSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (5, N'Jick', N'13528567445')
GO
SET IDENTITY_INSERT [dbo].[Member] OFF
GO
SET IDENTITY_INSERT [dbo].[Order] ON 

GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (1, 1, 3, CAST(0x0000A40900B3BBFB AS DateTime))
GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (2, 2, 1, CAST(0x0000A40900B3CEF2 AS DateTime))
GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (3, 3, 4, CAST(0x0000A40900B3D2D0 AS DateTime))
GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (4, 4, 0, CAST(0x0000A40900B3D660 AS DateTime))
GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (5, 5, 1, CAST(0x0000A40900B3D9B9 AS DateTime))
GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (6, 6, 2, CAST(0x0000A40900B3DFEA AS DateTime))
GO
INSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (7, NULL, 0, CAST(0x0000A40900E34971 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Order] OFF
GO
ALTER TABLE [dbo].[Order] ADD  DEFAULT (getdate()) FOR [createTime]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member', @level2type=N'COLUMN',@level2name=N'phone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'会员表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'会员编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'member_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'下单日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'createTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order'
GO

③ 编写查询语句

select top(4)  status , max(m.id) as maxMemberID
from [dbo].[Member] as m right outer join [dbo].[Order] as o 
on m.id=o.member_id 
where m.id>0
group by status 
having status>=0
order by maxMemberID asc

④ 实例语句分步骤分析

第一阶段:from开始

1.1 加载左表

from [dbo].[Member] as m 

查询结果:member表中的所有数据。


1.2 这里应该是 right outer join ,但是这里在sql中被定义分解为2个步骤,即join ,right outer join 。表达式关键字从左到右,依次执行。

join [dbo].[Order] as o 

查询结果:存入虚拟表vt1,为两个表的笛卡尔集合。

1.3、on 筛选器

on m.id=o.member_id 

查询结果如下:

这里写图片描述

从上一步的笛卡尔集中的35条数据中删除掉不匹配的行,得到5条数据,存入虚拟表Vt2。


1.4 、添加外部行(outer row)

right outer join [dbo].[Order] as o 

查询结果如下:

这里写图片描述

右表(order)作为保留表,把剩余的数据重新添加到上一步的虚拟表vt2中,生成虚拟表vt3。


第二阶段:where 阶段

where m.id>0

查询结果:存入虚拟表vt4,为筛选的条件为true的结果集,这里加入一个记忆点,就是,where的筛选删除为永久的,而on的筛选删除为暂时的,因为on筛选过后,有可能会经过outer添加外部行,重新把数据加载回来,而where则不能。


第三阶段:group by分组

group by status

查询结果:存入vt5,以status列的数值开始分组,即status列,值一样的分为一组,这里的两个null在三值逻辑中被视为true。三值逻辑:true,false,null。此三值,null为未知,是数据的逻辑特色,有的地方两个null相等为ture,在有些地方则为false。


第四阶段:having 筛选

having status>=0

查询结果:筛选分好组的组数据,把不满足条件的删除掉。


第五阶段:select 查询挑拣计算列

5.1、计算表达式

select status , max(m.id)

查询结果:从分过组的数据中计算各个组中的最大m.id,列出要筛选显示的列。

5.2、distinct过滤重复

5.3、top 结合order by 筛选 多少行,但这里的数据没有排序只是把多少行数据列出来而已。


第六阶段:order by

排序显示


【3】MySQL语法实例

MySQL完整语法与执行顺序如下:

(7)     SELECT   
(8)     DISTINCT <select_list>  
(1)     FROM <left_table>  
(3)     <join_type> JOIN <right_table>  
(2)     ON <join_condition>  
(4)     WHERE <where_condition>  
(5)     GROUP BY <group_by_list>  
(6)     HAVING <having_condition>  
(9)     ORDER BY <order_by_condition>  
(10)    LIMIT <limit_number>  
展开阅读全文

没有更多推荐了,返回首页