原文出处:作者:张龙豪链接:http://www.cnblogs.com/knowledgesea/p/4177830.html前言数据库的查询执行,毋庸置疑是程序员必备技能之一,然而数据库查询执行的过程绚烂多彩,却是很少被人了解,今天哥哥要带你装逼带你飞,深入一下这sql查询的 ...
原文出处:
作者:张龙豪
链接:http://www.cnblogs.com/knowledgesea/p/4177830.html
前言
数据库的查询执行,毋庸置疑是程序员必备技能之一,然而数据库查询执行的过程绚烂多彩,却是很少被人了解,今天哥哥要带你装逼带你飞,深入一下这sql查询的来龙去脉,为查询的性能优化处理打个基础,或许面试你也会遇到,预防不跪还是看看吧。
这篇博客,摒弃查询优化性能,作为其基础,只针对查询流程讲解剖析。
本片博客阐述的过程为
1、上一个标识过的sql语句,展示查询执行的流程
2、上一个流程图
3、做一个例子逐步深入分析,帮助理解
4、做一个装逼的总结
sql查询语句的处理步骤,代码清单
说明:
1、顺序为有1-6,6个大步骤,然后细分,5-1,5-2,5-3,由小变大顺序,1-J,1-A,1-P,1-U,为并行次序。如果不够明白,接下来我在来个流程图看看。
2、执行过程中也会相应的产生多个虚拟表(下面会有提到),以配合最终的正确查询。
sql查询语句的处理步骤,流程图
实例准备,创建表,插入数据,写要分析的实例查询语句
1、首先创建2各表
2、创建两个表,并插入表数据,脚本如下
USE [test]GO/****** Object: Table [dbo].[Member] Script Date: 2014/12/22 14:05:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO/****** Object: Table [dbo].[Order] Script Date: 2014/12/22 14:05:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOSET IDENTITY_INSERT [dbo].[Member] ON GOINSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (1, N'张龙豪', N'18501733702')GOINSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (2, N'Jim', N'15039512688')GOINSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (3, N'Tom', N'15139512854')GOINSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (4, N'Lulu', N'15687425583')GOINSERT [dbo].[Member] ([id], [Name], [phone]) VALUES (5, N'Jick', N'13528567445')GOSET IDENTITY_INSERT [dbo].[Member] OFFGOSET IDENTITY_INSERT [dbo].[Order] ON GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (1, 1, 3, CAST(0x0000A40900B3BBFB AS DateTime))GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (2, 2, 1, CAST(0x0000A40900B3CEF2 AS DateTime))GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (3, 3, 4, CAST(0x0000A40900B3D2D0 AS DateTime))GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (4, 4, 0, CAST(0x0000A40900B3D660 AS DateTime))GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (5, 5, 1, CAST(0x0000A40900B3D9B9 AS DateTime))GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (6, 6, 2, CAST(0x0000A40900B3DFEA AS DateTime))GOINSERT [dbo].[Order] ([id], [member_id], [status], [createTime]) VALUES (7, NULL, 0, CAST(0x0000A40900E34971 AS DateTime))GOSET IDENTITY_INSERT [dbo].[Order] OFFGOALTER TABLE [dbo].[Order] ADD DEFAULT (getdate()) FOR [createTime]GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'会员表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member'GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order'GO
实例语句分步骤分析
第一步,从from开始。
1.1、加载左表
from [dbo].[Member] as m
海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理:https://www.xlkjsw.com
原标题:[转载]T
关键词:
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。