how things work : sql select statement
原网站无法访问,故保留google快照
How things work :
SQL
Select
Statement
Introduction
:
Ever asked your self how things work inside the
SQL
Select
statement? In this article we won’t be talking about how to writeSQL
Select
statement but rather we will be talking about the algorithms and the methodology behind the
Select
statement and how
SQL
decides which
algorithm
it will use to filter out the results and return our expected results.
Selecting an
Algorithm
:
In fact you can’t do so, it is up to the
SQL
Optimizer implementation to determine the selected
algorithm
that best match the query you are going to invoke in order to enhance the query performance or in other words Optimize it, so you don’t have control over selecting the
algorithm
although some
SQL
Optimizer implementations tried to enable the DB Admin to specify which selection
algorithm
is suitable based on the admin knowledge (for example the admin might know that binary search –we will mentioned that latter- might be the best choice).
The Preparation:
We need to get prepared first and get familiar with the terminologies that we will be using through the article
Before we go further we need to know the types of indexes:
• Primary index – allows records to be read in an order that corresponds to the physical order in the file.
• Secondary index – any index that is not a primary index.
When we make an index we create something like a database for Indexing except it only include the key being indexed and the location counter which holds the location on the record in the database itself (the one that contains the data).
Access path: An access path is an
algorithm
used by Database to satisfy the requirements of
SQL
statements.
相关文档:
--语 句 功 能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从数据库中删除表 ALTER TABLE --修改数据库表结构 CREATE VI ......
1.按姓氏笔画排序:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pw ......
SQL Server:
Select TOP N * from TABLE Order By NewID()
Select TOP N * from TABLE Order By NewID()
NewID()函数将创建一个 uniqueidentifier 类型的唯一值。上面的语句实现效果是从Table中随机读取N条记录。
Access:
Select TOP N *&n ......
1、 用程序中,保证在实现功能的基础上,尽量减少对数据库的访问次数;通过搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担;能够分开的操作尽量分开处理,提高每次的响应速度;在数据窗口使用SQL时,尽量把使用的索引放在选择的首列;算法的结构尽量简单;在查询时,不要过多地使用 ......
for ACCESS :
update a, b set a.name=b.name1 where a.id=b.id
for SQL Server:
"update a set a.name=b.name1 from a,b where a.id=b.id"
update a set a.status=b.status
from table1 a,table2 b
&nbs ......