数据库相关-Top应用SQL执行顺序

写在前面的话 我们常常使用top来查询前几条语句,或使用嵌套的top的方式获取分页的数据,那么我们对top及SQL执行的顺序真正了解吗. 具体如下例. 实例 建表toptest,表结构如下: 列 sid sname int nvarchar(50) 类型 建表语句如下: create table toptest ( sid int, sname nvarchar(50) ) 表内数据如下: sid 1 2 3 4 5 6 张三 李四 王五 赵六 刘七 宋八 sname 插入语句如下:

insert into toptest values (1,'张三')

insert into toptest values (2,'李四')

insert into toptest values (3,'王五')

insert into toptest values (4,'赵六')

insert into toptest values (5,'刘七')

insert into toptest values (6,'宋八')

现在我们想要获取第三条、第四条数据,执行下面的语句:

select top 2 * from (select top 4 * from toptest) t order by sid desc

能够得到我们需要的结果呢? 答案是:不能. 返回的是: 6 宋八 5 刘七

为什么会出现这样的结果呢?

那么我们来看看执行(select top 4 * from toptest) 返回的是什么结果呢? 结果是: 1 张三 2 李四 3 王五 4 赵六

那么为什么从表t中返回的就不是 3 王五 4 赵六 了呢?

这里我们可以在SQLServer里查看执行计划,执行SQL语句,在执行计划标签中得到下图:

我们再来执行下列语句:

set showplan_all on

然后在执行SQL语句,在结果标签中,得到如下结果:

|--select top 2 sid,sname from (select top 4 sid,sname from toptest ) t order by sid desc;

|--Top(TOP EXPRESSION:((2)))

|--Sort(TOP 4, ORDER BY:([SQLTest].[dbo].[toptest].[sid] DESC))

|--Table Scan(OBJECT:([SQLTest].[dbo].[toptest]))

此处,我们看到执行步骤如下: 1.表扫描

2.先按照sid进行降序排序,然后再选取top 4,

此处为何先执行排序再进行top操作请看下面的SQL逻辑处理步骤 SQL逻辑处理步骤:

(8) SELECT (9) DISTINCT (11) (1) FROM

(3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH {CUBE | ROLLUP}

(7) HAVING (10) ORDER BY

3.选取top 2 sid,sname 4.获取整个结果集

由此,我们可以看到 order by sid desc 虽然是写在外部的,但是却在子查询时已经被执行了.所以,在第二步时,实际上获取了: 6 宋八 5 刘七 4 赵六 3 王五

然后,再执行第四步的top 2,即获取了: 6 宋八 5 刘七

现在,我们来执行下列语句,看执行计划和执行结果是什么?

select top 2 sid,sname from (select top 4 sid,sname from toptest order by sid asc) t order by sid desc;

注意,此处的子查询中加入了order by sid asc

|--select top 2 sid,sname from (select top 4 sid,sname from toptest order by sid asc) t order by sid desc;

|--Sort(TOP 2, ORDER BY:([SQLTest].[dbo].[toptest].[sid] DESC)) |--Sort(TOP 4, ORDER BY:([SQLTest].[dbo].[toptest].[sid] ASC)) |--Table Scan(OBJECT:([SQLTest].[dbo].[toptest]))

至此,我们得到了需要的数据.

由此,我们也想到了公用表表达式(CTE),CTE中如何实现该功能呢?

with t as (

select top 4 sid,sname from toptest order by sid asc )

select top 2 sid,sname from t

order by sid desc

执行计划图:

执行计划:

|--with t as (select top 4 sid,sname from toptest order by sid asc) select top 2 sid,sname from t order by sid desc;

|--Sort(TOP 2, ORDER BY:([SQLTest].[dbo].[toptest].[sid] DESC)) |--Sort(TOP 4, ORDER BY:([SQLTest].[dbo].[toptest].[sid] ASC)) |--Table Scan(OBJECT:([SQLTest].[dbo].[toptest]))

结果: 4 赵六

3 王五

其实,上述错误的查询中之所以会被认为是正确的,那是因为我们在默认的情况下已经给表中的数据按照sid进行排序了,可实际上:

select top 4 sid,sname from toptest

默认是按照sid来进行排序的吗? 现在我们来执行下列语句:

insert into toptest values (0,'玲玲')

update toptest set sid=1000 where sid=2 此时,数据库中的数据发生了变化,具体如下: sid 1 1000 3 4 5 6 0 张三 李四 王五 赵六 刘七 宋八 玲玲 sname 此时,我们来执行

select top 4 sid,sname from toptest

会是什么结果呢?返回的会是: 0 玲玲 1 张三 3 王五 4 赵六 吗?

各位同学,如果你看到此处,亲手执行一下,看是何结果呢? 此时,如果再执行

select top 2 * from (select top 4 * from toptest) t order by sid desc

得到的又是什么结果呢?

Android SQLite查询

1. 1.package com.eoeAndroid.SQLite; 2.

3. import android.app.Activity;

4. import android.content.Context; 5. import android.database.Cursor;

6. import android.database.SQLException;

7. import android.database.sqlite.SQLiteDatabase; 8. import android.database.sqlite.SQLiteOpenHelper; 9. import android.os.Bundle; 10.import android.util.Log; 11.import android.view.View;

12.import android.view.View.OnClickListener; 13.import android.widget.Button; 14.

15.public class ActivityMain extends Activity { 16. OnClickListener listener1 = null; 17. OnClickListener listener2 = null; 18. OnClickListener listener3 = null; 19. OnClickListener listener4 = null; 20. OnClickListener listener5 = null; 21.

22. Button button1; 23. Button button2; 24. Button button3; 25. Button button4; 26. Button button5; 27.

28. DatabaseHelper mOpenHelper; 29.

30. private static final String DATABASE_NAME = \"dbForTest.db\";

31. private static final int DATABASE_VERSION = 1; 32. private static final String TABLE_NAME = \"diary\"; 33. private static final String TITLE = \"title\"; 34. private static final String BODY = \"body\"; 35.

36. private static class DatabaseHelper extends SQLiteOpenHelper

{

37. DatabaseHelper(Context context) {

38. super(context, DATABASE_NAME, null, DATABASE_VERSION

);

39. } 40.

41. @Override

42. public void onCreate(SQLiteDatabase db) { 43.

44. String sql = \"CREATE TABLE \" + TABLE_NAME + \" (\" + T

ITLE

45. + \" text not null, \" + BODY + \" text not nul

l \" + \");\";

46. Log.i(\"haiyang:createDB=\47. Log.v(\"lunzi\48. db.execSQL(sql); 49.

50. } 51.

52. @Override

53. public void onUpgrade(SQLiteDatabase db, int oldVersion,

int newVersion) { 54. } 55. } 56.

57. @Override

58. public void onCreate(Bundle savedInstanceState) { 59. super.onCreate(savedInstanceState); 60. setContentView(R.layout.main); 61. prepareListener(); 62. initLayout();

63. mOpenHelper = new DatabaseHelper(this); .

65. } 66.

67. private void initLayout() {

68. button1 = (Button) findViewById(R.id.button1); 69. button1.setOnClickListener(listener1); 70.

71. button2 = (Button) findViewById(R.id.button2); 72. button2.setOnClickListener(listener2); 73.

74. button3 = (Button) findViewById(R.id.button3); 75. button3.setOnClickListener(listener3);

76. button4 = (Button) findViewById(R.id.button4); 77. button4.setOnClickListener(listener4); 78.

79. button5 = (Button) findViewById(R.id.button5); 80. button5.setOnClickListener(listener5); 81.

82. } 83.

84. private void prepareListener() {

85. listener1 = new OnClickListener() { 86. public void onClick(View v) { 87. CreateTable(); 88. } . };

90. listener2 = new OnClickListener() { 91. public void onClick(View v) { 92. dropTable(); 93. } 94. };

95. listener3 = new OnClickListener() { 96. public void onClick(View v) { 97. insertItem(); 98. } 99. };

100. listener4 = new OnClickListener() { 101. public void onClick(View v) { 102. deleteItem(); 103. } 104. };

105. listener5 = new OnClickListener() { 106. public void onClick(View v) { 107. showItems(); 108. } 109. }; 110. } 111.

112. /*

113. * 重新建立数据表 114. */

115. private void CreateTable() {

116. SQLiteDatabase db = mOpenHelper.getWritableDatabas

e();

117. String sql = \"CREATE TABLE \" + TABLE_NAME + \" (\" +

TITLE

118. + \" text not null, \" + BODY + \" text not n

ull \" + \");\";

119. Log.i(\"haiyang:createDB=\120. Log.v(\"lunzi\121. try {

122. db.execSQL(\"DROP TABLE IF EXISTS diary\");

123. db.execSQL(sql);

124. setTitle(\"数据表成功重建\");

125. } catch (SQLException e) {

126. setTitle(\"数据表重建错误\"); 127. } 128. } 129.

130. /*

131. * 删除数据表 132. */

133. private void dropTable() {

134. SQLiteDatabase db = mOpenHelper.getWritableDatabas

e();

135. String sql = \"drop table \" + TABLE_NAME; 136. Log.v(\"lunzi\137. try {

138. db.execSQL(sql);

139. setTitle(\"数据表成功删除：\" + sql); 140. } catch (SQLException e) {

141. setTitle(\"数据表删除错误\"); 142. } 143. } 144.

145. /*

146. * 插入两条数据 147. */

148. private void insertItem() {

149. SQLiteDatabase db = mOpenHelper.getWritableDatabas

e();

150. String sql1 = \"insert into \" + TABLE_NAME + \" (\" +

TITLE + \

151. + \") values('haiyang', 'android的发展真是

迅速啊');\";

152. String sql2 = \"insert into \" + TABLE_NAME + \" (\" +

TITLE + \

153. + \") values('icesky', 'android的发展真是迅

速啊');\";

154. try {

155. Log.i(\"haiyang:sql1=\156. Log.v(\"lunzi\

157. Log.i(\"haiyang:sql2=\158. Log.v(\"lunzi\159.

160. db.execSQL(sql1); 161. db.execSQL(sql2);

162. setTitle(\"插入两条数据成功\");

163. } catch (SQLException e) {

1. setTitle(\"插入两条数据失败\"); 165. } 166. } 167.

168. /*

169. * 删除其中的一条数据 170. */

171. private void deleteItem() { 172. try {

173. SQLiteDatabase db = mOpenHelper.getWritableDat

abase();

174. db.delete(TABLE_NAME, \" title = 'haiyang'\

ll);

175. setTitle(\"删除title为haiyang的一条记录

\");

176. } catch (SQLException e) { 177.

178. } 179.

180. } 181.

182. /*

183. * 在屏幕的title区域显示当前数据表当中的数据的条

数。

184. */

185. private void showItems() { 186.

187. SQLiteDatabase db = mOpenHelper.getReadableDatabas

e();

188. String col[] = { TITLE, BODY };

1. Cursor cur = db.query(TABLE_NAME, col, null, null,

null, null, null);

190. Integer num = cur.getCount(); 191. cur.moveToFirst();

192. while (!cur.isAfterLast()) {

193. Log.v(\"lunzi\0)+\"-\"+cu

r.getString(1));

194. System.out.println(\"cols:\"+cur.getString(0)+\"-\"+cur.getString(1));

195. cur.moveToNext(); 196. }

197. db.close();

198. /*for (int i=0;i199. 200. 201. 202. 203. 204.

Log.v(\"lunzi\ }*/

setTitle(Integer.toString(num) + \" 条记录\"); } }