|
SQL查询语句用法举例
| 学号 |
姓名 |
班级 |
数学 |
英语 |
计算机 |
| 201001 |
张三 |
A |
78 |
86 |
66 |
| 201005 |
李四 |
A |
55 |
68 |
99 |
| 201007 |
赵六 |
C |
90 |
68 |
20 |
| 203009 |
王五 |
C |
56 |
22 |
19 |
Sub 选取部分列()
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select 英语,计算机 from
[aa$]"
Range("A2").CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
Sub 选取全部列()
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select * from
[aa$]"
Range("A2").CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
Sub 数学不及格的学生()
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select * from [aa$]
where 数学 < 60"
Range("A2").CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
Sub 数学英语均不及格的学生()
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select * from [aa$]
where 数学 < 60 and 英语 < 60"
Range("A2").CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
Sub 数学成绩及格的学生信息() '查询结果按降序
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select * from [aa$]
where 数学 >=60 order by 数学 desc"
Range("A2").CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
Sub 数学成绩不及格的人数()
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select count(*) from
[aa$] where 数学 < 60"
Range("A2") = "数学不及格的人数"
[a3].CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
Sub 数学平均分最高分()
Range("A2:F10").ClearContents
Set Conn =
CreateObject("adodb.connection")
Conn.Open
"provider=microsoft.jet.oledb.4.0;extended properties='excel
8.0';data source=" & ThisWorkbook.Path &
Application.PathSeparator & "数据库.xls"
Sql = "select avg(数学),max(数学)
from [aa$]"
Range("A2") = "数学平均分": [b2] =
"数学最高分"
[a3].CopyFromRecordset
Conn.Execute(Sql)
Conn.Close
Set Conn = Nothing
End Sub
新浪博客:http://weibo.com/u/1139851561
百度空间:http://hi.baidu.com/335081548
往期精彩在:
腾讯(QQ)微博:http://t.qq.com/huangshifang?preview
更多分享请关注微信号
微信号：Excel335081548 或:
雪山飞狐Excel
|
