|
第12章 多表连接查询 在SQL Server数据库中，各个数据表之间往往有这样或那样的联系，我们在查询数据时也往往需要使用多个数据表，这就是多表连接查询。 比如，在第7章介绍的“研究生管理”数据库的5个数据表中，“院系”表和“导师”表之间通过“院系编号”建立联系，“导师”表和“研究生”表之间通过“导师编号”建立联系，“研究生”表、“课程”表和“成绩”表之间通过“学号”和“课程代码”建立联系。这样，我们可以利用这些关系，查询需要的数据，并使数据所含的信息明确化。 12.1 SELECT多表连接查询的语法结构 在一个查询中，当需要对两个或多个表进行连接查询时，可以指定连接字段，在WHERE子句中给出连接条件，在FROM子句中指定要连接的表，其格式如下： SELECT 字段名1，字段名2，……
FROM 表1，表2，……
WHERE 连接条件 对于连接的多个表，通常存在公共字段，为了区别该字段是哪个表的字段，在连接条件中可以通过表名前缀制定连接字段，即“表名.字段名”来区分哪个表的字段，例如，“研究生.学号”表示表“研究生”的字段“学号”，而“成绩.学号”表示表“成绩”的字段“学号”。 在多表连接查询的SELECT语句中，WHERE连接条件可以是多种多样的，下面介绍几种常见的多表连接查询方法。 12.2 等值或非等值连接查询 等值连接查询，就是在WHERE子句中通过等号连接两个表，从而查询出需要的数据。 非等值连接查询，就是在WHERE子句中通过不等号（>、<、>=、<=、<>等）连接两个表，从而查询出需要的数据。 下面我们通过几个例子说明等值或非等值连接查询的基本方法和程序代码。 【例12-1】下面的例子是从数据库“研究生管理”的数据表“研究生”、“课程”和“成绩”中，查询每个学生的姓名、性别和选修的课程名称以及考试成绩。 在运行下面的程序之前，要确保已经引用了ADO对象库Microsoft
Active Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同时，还要根据电脑实际情况，将连接字符串中的SQL Server服务器名称进行变更，并输入相应的用户名和密码（如果有的话）。 Public Sub 例12—1()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL
Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select 研究生.姓名，研究生.性别，课程.课程名称，成绩.成绩 " _
& "from 研究生,课程，成绩
" _
& "where 研究生.学号=成绩.学号 and 成绩.课程代码=课程.课程代码 "
_
& "order by 研究生.姓名"
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:D1") = Array("姓名",
"性别", "课程名称", "成绩")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
MsgBox "数据追加成功！", vbInformation '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行上面的程序，就得到如图12-1所示的结果。
【例12-2】下面的例子是从数据库“研究生管理”的数据表“研究生”、“课程”和“成绩”中，查询研究生“李小菁”的性别和选修的课程名称以及考试成绩。 此时，SQL语句如下，程序的其他部分与【例12-1】完全相同。 SQL = “select 研究生.姓名，研究生.性别，课程.课程名称，成绩.成绩 ”_
& “ from 研究生，课程，成绩”
_
& “where 研究生.学号=成绩.学号 and 成绩.课程代码=课程.课程代码 and 研究生.姓名=‘李小菁’” 运行上面的程序，就得到如图12-2所示的结果。
【例12-3】下面的例子是从数据库“研究生管理”的数据表“研究生”、“课程”和“成绩”中，查询各个班级、各个课程的平均分。 Public Sub 例12—3()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select 课程.课程名称，研究生.班级，avg(成绩.成绩) as 平均成绩 " _
& "from 研究生,课程，成绩
" _
& "where 研究生.学号=成绩.学号 and 成绩.课程代码=课程.课程代码 "
_
& "group by 研究生.班级，课程.课程名称 " _
& "order by 课程.课程名称"
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:C1") = Array("课程",
"班级", "平均成绩")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行程序后的结果如图12-3所示。注意，由于在设计数据表时，对成绩分数定义为整数，因此上述程序满意计算的平均分数也为整数。
图12-3所示的表格看起来不太方便，我们可以利用条件格式将A列的课程进行格式化，格式化后的表格如图12-4所示。下面是利用条件格式将A列的课程进行格式化的程序代码，这些语句加在语句“Columns.AutoFit”后面： Range(“A2:A” & Range(“A Selection.FormatConditions.Delete Selection.FormatConditions.Add
Type:=xlExpression,Formula1:=”=SA2=SA Selection.FormatConditions(1).Font.ColorIndex=2
【例12-4】下面的例子是从数据库“研究生管理”的数据表“研究生”、“课程”和“成绩”中，查询选修“微观材料学”课程、并且考试分数高于该科平均分的学生姓名、性别、班级及考试成绩，并按成绩降序排列。 Public Sub 例12—4()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _ & "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select 课程.课程名称，研究生.姓名，研究生.性别，研究生.班级，成绩.成绩 " _
& "from 研究生,课程，成绩
" _
& "where 研究生.学号=成绩.学号 and 成绩.课程代码=课程.课程代码 "
_
& "and 课程.课程名称=‘微观材料学’" _
& "where 成绩.课程代码=课程.课程代码 and 课程.课程名称=‘微观材料学’)" _
& "order by 成绩.成绩
desc"
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:E1") = Array("课程",
"姓名", "性别", "班级",
"成绩")
Range("A1:E1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行上面的程序，就得到如图12-5所示的结果。
【例12-5】下面的例子是从数据库“研究生管理”的数据表“课程”和“成绩”中，查询所有课程的平均成绩，并按降序排列。 Public Sub 例12—5()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select 课程.课程名称，avg(成绩.成绩) as 平均成绩 from 课程，成绩 " _
& "where 成绩.课程代码=课程.课程代码 " _
& "group by 课程.课程名称"
_
& "order by 平均成绩 desc"
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:B1") = Array("课程名称",
"平均成绩")
Range("A1:E1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub. 运行上面的程序，就得到如图12-6所示的结果。
【例12-6】下面的例子是从数据库“研究生管理”的数据表“研究生”、“导师”、“课程”和“成绩”中，查询出选修某课程的学生人数多于2人的教师姓名。 Public Sub 例12—6()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select 课程.课程名称，导师.姓名，COUNT(成绩.学号) from 导师，课程，成绩 " _
& "where 成绩.课程代码=课程.课程代码 and 导师.导师编号=课程.授课教师 "
_
& "group by 课程.课程名称，导师.姓名" _
& "having COUNT(成绩.学号)>=3"
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:C1") = Array("课程名称",
"教师名称", "选修人数")
Range("A1:C1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行上面的程序，就得到如图12-7所示的结果。
12.3 自身连接查询 在数据查询中，有时候需要将同一个表进行连接，这种连接成为自身连接。进行自身连接就如同两个分开的表一样，可以把一个表的某行与同一表中的另一行连接起来。 【例12-7】下面的例子是从数据库“研究生管理”的数据表“成绩”中，查询出选修课程代码为“200601003”、并且成绩高于学号为“A03200602”学生成绩的所有学生考试成绩记录，并按成绩从高到低进行排列。 Public Sub 例12—7()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select x.学号，x.课程代码， x.成绩" _
& "from 成绩 as x,成绩 as
y" _
& "where x.课程代码='200601003' and y.学号='A03200602' and x.成绩>y.成绩 " _
& "order by x.成绩 desc"
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:C1") = Array("学号",
"课程代码", "成绩")
Range("A1:C1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行上面的程序，就得到如图12-8所示的结果。
12.4 内连接查询 内连接是常用的连接操作，利用INNER JION……ON连接几个表。此时的SELECT语句结构如下： SELECT 属性或字段列表
FROM 表1名 [INNER]
JION 表2名
ON 连接条件
[WHERE 限定条件] 这里，INNER可以省略，用ON 指定连接条件，用WHERE指定其他限定条件。 【例12-8】下面的例子是从数据库“研究生管理”的数据表“成绩”和“研究生”中，查询出班级编号为“A03200601”的全部学生的信息记录，包括基本信息和考试信息。 Public Sub 例12—8()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select x.班级，x.学号， x.姓名，x.性别，y.课程代码，y.成绩" _
& "from 研究生 as x inner join 成绩 as y " _
& " on x.学号 = y.学号 " _
& "where x.班级='A03200601' "
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:F1") = Array("班级",
"学号", "姓名", "性别",
"课程代码", "成绩")
Range("A1:F1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行上面的程序，就得到如图12-9所示的结果。
12.5 左外连接查询 左外连接查询，是指在查询结果中包含第一个表中满足条件的所有记录。如果是在连接条件上满足匹配条件，那么就返回第2个表相应的值，否则就返回空值。此时的SELECT语句结构如下： SELECT 属性或字段列表
FROM 表1名 LEFT
OUTER JION 表2名
ON 连接条件
[WHERE 限定条件] 【例12-9】下面的例子是从数据库“研究生管理”的数据表“导师”和“院系”中，查询满足匹配条件的各个院系具体名称等信息。 Public Sub 例12—9()
Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理" cnn.Open '查询数据
SQL = " select x.姓名，x.性别， x.职称，x.院系名" _
& "from 导师 as x left outer join 院系 as y " _
& " on x.院系编号 = y.院系编号 "
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:D1") = Array("姓名",
"性别", "职称", "院系")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行程序后的结果如图12-10所示。
12.6 右外连接查询 右外连接查询，是指在查询结果中包含第二个表中满足条件的所有记录。如果是在连接条件上满足匹配条件，那么就返回第一个表相应的值，否则就返回空值。此时的SELECT语句结构如下： SELECT 属性或字段列表
FROM 表1名 RIGHT
OUTER JION 表2名
ON 连接条件
[WHERE 限定条件] 【例12-10】下面的例子是从数据库“研究生管理”的数据表“导师”和“院系”中，查询满足匹配条件的各个导师的院系具体名称等信息。 Public Sub 例12—10()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select x.姓名，x.性别， x.职称，x.院系名" _
& "from 导师 as x right outer join 院系 as y " _
& " on x.院系编号 = y.院系编号 "
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:D1") = Array("姓名",
"性别", "职称", "院系")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行程序后的结果如图12-11所示。
12.7 全外连接查询 全外连接查询，是指在查询结果中包含两个表中满足条件的所有记录。如果是在连接条件上满足匹配的元组，那么就返回另一个表相应的值，否则另一个表就返回空值。此时的SELECT语句结构如下： SELECT 属性或字段列表
FROM 表1名 FULL
OUTER JION 表2名
ON 连接条件
[WHERE 限定条件] 【例12-11】下面的例子是从数据库“研究生管理”的数据表“导师”和“院系”中，查询满足匹配条件的各个导师的院系具体名称等信息。 Public Sub 例12—11()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String '建立与SQL Server服务器的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open '查询数据
SQL = " select x.姓名，x.性别， x.职称，x.院系名" _
& "from 导师 as x full outer join 院系 as y " _
& " on x.院系编号 = y.院系编号 "
Set rs = cnn.Execute(SQL) '复制查询出的数据
Cells.Clear
Range("A1:D1") = Array("姓名",
"性别", "职称", "院系")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit '关机记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing End Sub 运行程序后的结果如图12-11所示。
|
|
|
来自： 昵称380475 > 《数据库管理技术详解》
