栋栋的个人博客

excel-vba 数据库操作方式

  • ~5.94K 字

一、卡点：

image-20230728134957123

这个问题是用wps连接的OLEDB（access数据库）, 用excel打开就没有问题，如果用wps就需要用其他方式

excel：

Dim con As ADODB.Connection
Set con = New ADODB.Connection '对象变量赋值必须用set
` 建立数据连接    
con.Open "provider=Microsoft.ACE.OLEDB.16.0;data source=" & _
    ThisWorkbook.Path & "\基础数据.accdb"

wps：

Dim con As New ADODB.Connection
con.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.Path & "/test2.xls"

SQLserver：

  • 数据库本身（非服务器本身的密码）没有账号密码
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

Dim serverName As String
Dim dbName As String
Dim connStr As String

serverName = "服务器名称"  ' 替换为实际的服务器名称
dbName = "数据库名称"  ' 替换为实际的数据库名称

Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & dbName & ";Trusted_Connection=Yes;"
  • 数据库本身有密码
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

Dim serverName As String
Dim dbName As String
Dim username As String
Dim password As String

serverName = "服务器名称"  ' 替换为实际的服务器名称
dbName = "数据库名称"  ' 替换为实际的数据库名称
username = "用户名"  ' 留空，因为使用服务器身份验证
password = ""  ' 留空，因为使用服务器身份验证

conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & dbName & ";User ID=" & username & ";Password=" & password & ";"
conn.Open
  • 示例：
Sub 连接数据库()
    Dim serverName As String
    Dim dbName As String
    Dim userName As String
    Dim password As String
    serverName = "DONGDONGA"
    dbName = "中台数据库"
    userName = ""
    password = ""
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & dbName & ";Trusted_Connection=Yes;"
    
End Sub

二、access 数据库操作指南

  • 第一步：告诉电脑我要用ADO了，

      ___这个操作在【工具】--> 【引用】--> 【Microsoft ActiveX Data Objects 6.1 	Libray】___

image-20230728143220859

  • 第二步：创建连接对对象
 ' 2-1 声明对象变量名
Dim con As ADODB.Connection
 ' 2-2 创建对象变量并赋值
Set con = New ADODB.Connection '对象变量赋值必须用set
  • 第三步：连接数据库
' 建立数据库连接
con.Open "provider=Microsoft.ACE.OLEDB.16.0;data source=" & _
ThisWorkbook.Path & "\基础数据.accdb"

(注意这个部分，OLEDB, 2007以上是12.0，一下是8.0或4.0)

  • 第四步：执行sql语句
'插入记录：insert into 表名 （字段1，字段2，...） values(值1，值2，...)
Dim sql As String
sql = "insert into TEST(测试1,测试2,测试3) values('a09','D文学员','6999')"
' 执行sql
con.Execute (sql)
  • 第五步： 关闭数据库连接，并释放变量（一定要）
con.Close
' 释放变量
Set con = Nothing

三、数据库语法

 1. **数据常见的`增`、`删`、`改`、`查`**
  • 增加数据
sub InsetSql()
    Dim sql As String
    sql = "insert into TEST(测试1,测试2,测试3) values('a09','D文学员','6999')"
    ' 执行sql
    con.Execute (sql)
end sub
  • 删除数据
Sub DelSql()
    '删除数据： delete from 表名 [where 条件]
    Dim sql As String
    sql = "delete from TEST where id=2" '如果是字符串需要加''
    con.Execute (sql)
End Sub
  • 修改数据
Sub UpdateSql()
    '更新记录 update 表名 set 字段=值 [where 条件]
    ' 多条件更新 update 表名 set 字段1=值1，字段2=值2 where 条件
    ' 如果要使用变量的化，一定要在“号的外面
    ' "update 表名 set 字段1=值1，字段2='"& 变量 & "' where 条件" 注意'
    Dim sql As String
    sql = "update TEST set 测试1='a088' where 测试3='999'"
    con.Execute (sql)
End Sub
  • 查找数据
Sub SelectSql()
 	Dim sql As String
    sql = "select * from TEST"
	con.Execute (sql)
End Sub    
'书写sql命令字符串
sql = "select * from 院校"                                  	'查询全部字段
sql = "select 姓名，性别，职称 from 导师"        				 	'指定部分字段
sql = "select distinct 研究方向 from 学生"        		    	'提取不重复记录
sql = "select 课程代码，成绩 from 成绩 order by 成绩 asc" 		 	'排序
sql = "select *，2006 from 学生" 							   '生成新的字段
sql = "select 学号，姓名，性别，year(入学日期) as 年份 from 学生"  '根据年份查找对应字段
sql = "selcet 姓名，性别 from union select 姓名，性别 from 导师" 
' union：去除重复，并按照第一个字段升序排序
' union all 全部复制，重复的也不管，不排序
  1. 数据库记录集

通过查询或其他方式获取数据后，需要存放在一个记录集对象中

  • 创建一个记录集对象，创建记录集主要有两个方法(第二种用的最多)

  • 第一中不能修改记录集对象，第二种可以修改

  • 使用 Set rs = con.Execute(sql) 语句可以直接执行 SQL 查询，并将结果集赋值给记录集对象 rs。这种方法适用于执行 SQL 查询并将结果保存在记录集中。

  • 使用 rs.Open sql, con, adOpenReyset, adLicjOptimistic 语句可以在已经创建的记录集对象 (rs) 上打开一个结果集。这个方法可以用于在已经存在的记录集对象上重新打开一个新的结果集。

Dim rs As New ADODB.Recordset '创建了一个记录集对象
Set rs = con.Execute(sql)  '这是一个记录集
set rs = New ADODB.Recordset '创建记录集对象
rs.Open sql, con, adOpenRecordset, adLockOptimistic
  • 获取记录集中的字段，需要用Fields.name
'获取字段名需要一个循环
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
	Cells(1, i + 1) = rs.Fields(i).Name
Next
  • 将记录集返回到数据表
sheets("sheet1").Range("A2").CopyFromRecordset rs  '可以直接定位到当前的单元格，sheets("sheet1"）可加可不加
  • 释放对象变量空间
'释放对象变量空间
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing

四、多表查询（where连接)

  • 案例1：查询所有学生的姓名、性别、选修的课程名称及成绩
sql = "select 姓名，性别，课程名称，成绩" _
& " from 学生，课程，成绩" _
& " where 学生.学号=成绩.学号 and 课程.课程代码=成绩.成绩代码"
  • 案例2：查询“王维欣”同学的性别、选修的课程名称及考试成绩
sql = "select 性别，课程名称，成绩" _
& " from 学生，课程，成绩" _
& " where 学生，学号=成绩.学号 and 课程.课程代码=成绩.课程代码" _
& " and 姓名='王维欣'"

五、多表查询（内连接）

  • 案例1：查询所有课程的平均成绩，结果包含课程名称，平均成绩两个字段
sql = "select 课程名称，avg(成绩) as 平均成绩" _
& " from 课程 inner join 成绩 on 课程.课程代码=成绩.课程代码"
  • 案例2： 查询所有课程的平均成绩，结果包含课程名称，平均成绩两个字段，平均成绩要大于85分
sql = "select 课程名称，avg(成绩) as 平均成绩" _
& " from 课程 inner join 成绩 on 课程.课程代码=成绩.课程代码" _
& " group by 课程名称 having avg(成绩)>=85"

（sql语句的执行顺序是: from –> where –> group –> having –> select）

六、多表查询（外连接）

基本格式： from 左表 连接类型 右表 on 连接条件

  • 案例1：查询所有导师的院系信息，包含姓名，性别，职称，系号、系名

      `左连接：左边连接字段有的，而右表没有的，左表全部显示，右表留空`

(根据左表查右表)

sql = "select 姓名，性别，职称，系号，系名 " _
& " from 导师 left join 院系 on 导师.院系编号=院系.系号"
  • 案例2：查询所有院系的导师信息，包含系号、系名、姓名、职称

右连接：右表连接字段有的，而左表没有的，右边全部显示，左表留空

（根据右表查左表）

sql = "select 系号，系名，姓名，职称" _
& " from 导师 right join 院系 on 导师.院系编号=院系.系号"
  • 案例3：查询所有导师、所有院系的信息，包含姓名、性别、职称、系号、系名
sql = "select 姓名，性别，职称，系号，系名" _
& " from 导师 full join 院系 on 导师.院系编号=院系.系号"

七、多表查询（自连接）

含义：自己连接自己

  • 案例1：查询员工表中姓名有重复的员工记录(刷出全部记录看看)
sql = "select * from 员工 t1 inner join 员工 t2 on t1.姓名=t2.姓名"

八、子查询（嵌套查询）

  • 案例1：查询每个部门年龄在30岁以上的员工的平均年龄
sql = "select 部门，count(*) as 人数 from 员工 where 年龄>30 group by 部门"

  • 案例2： 查询年龄高于平均年龄的员工信息，包含姓名、身份证号、部门、年龄职务

  • 案例3： 查询年龄排在第5-10名的员工信息，包含姓名、身份证号、部门、年龄职务

本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可
, ,