Excel VBA 操作 MySQL(十一，十二，十三)

原创

種法龍

发布于 2023-12-28 15:03:57

2960

发布于 2023-12-28 15:03:57

文章被收录于专栏：未知

在Excel VBA中对MySQL数据库中的表格进行操作，包括重命名和删除等，需要执行相应的SQL语句。以下是示例代码，演示如何执行这些操作：

重命名表格

要重命名MySQL数据库中的表格，可以使用RENAME TABLE语句。以下是一个示例：

Sub RenameMySQLTable()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' MySQL数据库连接信息
    Dim serverName As String
    Dim dbName As String
    Dim userName As String
    Dim password As String
    
    serverName = "localhost" ' MySQL服务器地址
    dbName = "mydatabase" ' 数据库名称
    userName = "myuser" ' 数据库用户名
    password = "mypassword" ' 数据库密码
    
    ' 构建连接字符串
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                            "SERVER=" & serverName & ";" & _
                            "DATABASE=" & dbName & ";" & _
                            "USER=" & userName & ";" & _
                            "PASSWORD=" & password & ";"
    
    ' 打开数据库连接
    conn.Open
    
    ' 定义重命名表格的SQL语句
    Dim renameSQL As String
    renameSQL = "RENAME TABLE OldTableName TO NewTableName"
    
    ' 执行重命名表格的SQL语句
    conn.Execute renameSQL
    
    ' 关闭数据库连接
    conn.Close
    Set conn = Nothing
End Sub

在上面的代码中，将OldTableName替换为要重命名的表格的当前名称，将NewTableName替换为新的表格名称。

删除表格

要删除MySQL数据库中的表格，可以使用DROP TABLE语句。以下是一个示例：

Sub DeleteMySQLTable()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' MySQL数据库连接信息
    Dim serverName As String
    Dim dbName As String
    Dim userName As String
    Dim password As String
    
    serverName = "localhost" ' MySQL服务器地址
    dbName = "mydatabase" ' 数据库名称
    userName = "myuser" ' 数据库用户名
    password = "mypassword" ' 数据库密码
    
    ' 构建连接字符串
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                            "SERVER=" & serverName & ";" & _
                            "DATABASE=" & dbName & ";" & _
                            "USER=" & userName & ";" & _
                            "PASSWORD=" & password & ";"
    
    ' 打开数据库连接
    conn.Open
    
    ' 定义删除表格的SQL语句
    Dim deleteSQL As String
    deleteSQL = "DROP TABLE TableNameToDelete"
    
    ' 执行删除表格的SQL语句
    conn.Execute deleteSQL
    
    ' 关闭数据库连接
    conn.Close
    Set conn = Nothing
End Sub

在上面的代码中，将TableNameToDelete替换为要删除的表格的名称。

要获取有关MySQL数据库模式的信息，如表格结构、列信息等，可以使用SQL查询语句查询系统表格（System Tables）或信息模式（Information Schema）。以下是一些示例代码，演示如何在Excel VBA中获取这些信息：

###获取表格结构和列信息

要获取MySQL数据库中表格的结构和列信息，可以查询information_schema数据库中的表格，具体如下：

Sub GetTableStructure()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' MySQL数据库连接信息
    Dim serverName As String
    Dim dbName As String
    Dim userName As String
    Dim password As String
    
    serverName = "localhost" ' MySQL服务器地址
    dbName = "mydatabase" ' 数据库名称
    userName = "myuser" ' 数据库用户名
    password = "mypassword" ' 数据库密码
    
    ' 构建连接字符串
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                            "SERVER=" & serverName & ";" & _
                            "DATABASE=" & dbName & ";" & _
                            "USER=" & userName & ";" & _
                            "PASSWORD=" & password & ";"
    
    ' 打开数据库连接
    conn.Open
    
    ' 查询表格结构和列信息
    Dim strSQL As String
    strSQL = "SELECT table_name, column_name, data_type " & _
             "FROM information_schema.columns " & _
             "WHERE table_schema = '" & dbName & "'"
    
    ' 创建Recordset对象
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 执行查询并将结果存储在Recordset中
    rs.Open strSQL, conn
    
    ' 将结果写入Excel工作表
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 使用工作表的名称，你可以根据需要更改
    
    Dim iRow As Integer
    iRow = 2 ' 从第二行开始，根据需要更改
    
    Do Until rs.EOF
        ws.Cells(iRow, 1).Value = rs("table_name").Value
        ws.Cells(iRow, 2).Value = rs("column_name").Value
        ws.Cells(iRow, 3).Value = rs("data_type").Value
        iRow = iRow + 1
        rs.MoveNext
    Loop
    
    ' 关闭Recordset和数据库连接
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

上面的代码查询了information_schema.columns表格，以获取数据库中所有表格的结构和列信息，并将结果写入Excel工作表。

可以根据需要修改SQL查询语句和数据的显示方式，以满足不同的需求。这个示例只是一个基本的框架。

在Excel VBA中生成MySQL数据库中的数据透视表需要使用PivotTable对象和数据透视表字段。以下是一个示例代码，演示如何执行此操作：

Sub CreatePivotTableFromMySQLData()
    ' 建立与MySQL数据库的连接
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' MySQL数据库连接信息
    Dim serverName As String
    Dim dbName As String
    Dim userName As String
    Dim password As String
    
    serverName = "localhost" ' MySQL服务器地址
    dbName = "mydatabase" ' 数据库名称
    userName = "myuser" ' 数据库用户名
    password = "mypassword" ' 数据库密码
    
    ' 构建连接字符串
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                            "SERVER=" & serverName & ";" & _
                            "DATABASE=" & dbName & ";" & _
                            "USER=" & userName & ";" & _
                            "PASSWORD=" & password & ";"
    
    ' 打开数据库连接
    conn.Open
    
    ' 创建查询SQL语句
    Dim strSQL As String
    strSQL = "SELECT * FROM MyTable"
    
    ' 创建Recordset对象
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 执行查询并将结果存储在Recordset中
    rs.Open strSQL, conn
    
    ' 创建新的工作表用于数据透视表
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add
    
    ' 将查询结果写入新工作表
    ws.Cells(1, 1).CopyFromRecordset rs
    
    ' 关闭Recordset和数据库连接
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    ' 添加数据透视表
    Dim pt As PivotTable
    Dim pc As PivotCache
    
    ' 设置数据透视表缓存
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.UsedRange)
    
    ' 添加数据透视表
    Set pt = pc.CreatePivotTable(TableDestination:=ws.Cells(5, 1), TableName:="MyPivotTable")
    
    ' 在数据透视表中添加字段
    With pt.PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With pt.PivotFields("Age")
        .Orientation = xlDataField
        .Function = xlAverage ' 更改聚合函数，根据需要更改
        .Position = 1
    End With
    
    ' 设置数据透视表样式
    pt.TableStyle2 = "PivotStyleMedium9" ' 更改样式，根据需要更改
    
End Sub

上面的代码执行以下操作：