常用SQL语言
查询数据
select * from [data$]
查询某几个字段
select 姓名,年龄 from [data$]
带条件的查询
select * from [data$] where 性别 = "男“
合并两个表的数据
select * from [data$] union all select *
from [data2$]
插入新纪录
insert into [data$] (姓名,性别,年龄) values
('AA','男',33)
修改一条数据
update [data$] set 性别=‘男’,年龄=16 where 姓名=‘张三‘
删除一条数据
delete from [data$] where姓名='张三'
使用LEFT JOIN …ON… (类似于VLOOKUP)
select [data3$].姓名,性别,年龄,月薪 from [data$]
left join [data3$] on [data$].姓名=[data3$].姓名
先UNION ALL 再LEFT
JOIN
select * from (select * from [data$] union
all select * from [data2$])a left join [data3$] on a.姓名=[data3$].姓名
将查询结果赋值到数组
arr=Application.WorksheetFunction.Transpose(conn.Execute("select
* from [data$]").GetRows)
连接代码
Dim conn As New ADODB.Connection
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended properties=""excel 12.0;HDR=YES"""
conn.Close
ACCESS文件
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Adata.accdb"
Mysql数据库
conn.Open "Provider=SQLOLEDB;DataSource=" & Path & ";Initial Catolog=" & strDataName
MSSQL数据库
conn.Open "Provider=MSDASQL;Driver={SQL Server};Server=" & Path & ";Database=" & strDataName
Oracle数据库
conn.Open "Provider=madaora;Data Source=MyOracleDB; User Id=UserID; Password=Password"
网友评论