ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 254198|回复: 477

[原创] 从零开始学习SQL(图文教程)

    [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-10-9 13:25 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:SQL应用
本帖最后由 335081548 于 2013-10-11 10:10 编辑

  学习SQL一步一步开始,跟着本文的脚印带你走向SQL方向。
本分先分别介绍Microsoft Query 方法书写SQL语句,和VBA方法书写SQL语句。(注:VBA语句中只需修改其中的SQL语句即可,其他只需要复制粘贴即可)。
下面先上图:

进入VBA界面
进入VBA方法 1.jpg

2.插入模块
插入模块.jpg

3.双击模块
插入模块 2.jpg
4.在代码区码 写入VBA代码.jpg

写入VBA代码

代码如下:

  1. Sub Test4()
  2.     Dim Conn As Object, Rst As Object
  3.     Dim strConn As String, strSQL As String
  4.     Dim i As Integer, PathStr As String
  5.     Set Conn = CreateObject("ADODB.Connection")
  6.     Set Rst = CreateObject("ADODB.Recordset")
  7.     PathStr = ThisWorkbook.FullName   '设置工作簿的完整路径和名称
  8.     Select Case Application.Version * 1    '设置连接字符串,根据版本创建连接
  9.     Case Is <= 11
  10.         strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
  11.     Case Is >= 12
  12.         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
  13.     End Select
  14.     '设置SQL查询语句
  15.     strSQL = "请写入SQL语句"
  16.     Conn.Open strConn    '打开数据库链接
  17.     Set Rst = Conn.Execute(strSQL)    '执行查询,并将结果输出到记录集对象
  18.     With Sheet3
  19.         .Cells.Clear
  20.         For i = 0 To Rst.Fields.Count - 1    '填写标题
  21.             .Cells(1, i + 1) = Rst.Fields(i).Name
  22.         Next i
  23.         .Range("A2").CopyFromRecordset Rst
  24.         .Cells.EntireColumn.AutoFit  '自动调整列宽
  25.         .Cells.EntireColumn.AutoFit  '自动调整列宽
  26.     End With
  27.     Rst.Close    '关闭数据库连接
  28.     Conn.Close
  29.     Set Conn = Nothing
  30.     Set Rst = Nothing
  31. End Sub
复制代码

介绍Microsoft Query的方法
1.从数据---自其他来源--来自Microsoft Query
通过Microsoft Query学习SQL 1.jpg
2.选择Excel类型
通过Microsoft Query学习SQL 2.jpg
3.选择工作簿(文件)
通过Microsoft Query学习SQL 3.jpg
4.选择工作表
通过Microsoft Query学习SQL 4.jpg
5.选择需求数据的列
通过Microsoft Query学习SQL 5.jpg
6.筛选条件,本例只接选下一步
通过Microsoft Query学习SQL 6.jpg
7.排序方法,本教程也直接下一步
通过Microsoft Query学习SQL 7.jpg
8.返回工作表或Microsoft Query编辑器。(本例选 Microsoft Query,以便以编写SQL语句)
通过Microsoft Query学习SQL 8.jpg
9.编写SQL语句窗口。
通过Microsoft Query学习SQL 9.jpg

通过以上二种方法,能知道SQL书写到什么地方。
接下来,可以开始学习SQL语句语法。

评分

47

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 13:39 | 显示全部楼层
本帖最后由 335081548 于 2013-10-9 14:42 编辑

1.基于一张工作表的查询语法                                       
格式:        select[DISTINCT] [TOP<数值> [PERCENT] <列标题>[[as]]<别名列标题>]                                                
            from <表或查询1>[AS]<别名1>],<表或查询2>[AS]<别名2>],                                                
           [where<筛选条件>][order by<排序项>[asc ▏desc]]                                
                                                        
说明:        1、<>表示必选项,"[]"表示可选项,"▏"表示多选一。                                
        2、        DISTINCT:消除取重复的行                        
        3、        TOP 数值:显示前几条记录                        
        4、        TOP 数值 percent:显示前面分之多少条记录                        
        5、        <列标题>[[as]]<别名列标题>:给标题列重新命一个新名称                        
        6、        where<筛选条件>:条件语句                        
        7、排序,如果要按两个或两个以上字段,那么字段与字段之间用豆号隔开,asc升序,为默认值,desc降序。        

显示所有字段的记录(全部列的内容)
语句:
语句1:

  1. select * from [sheet1$]
复制代码
语句2:

  1. select 编号,姓名,性别,出生年月,职称,部门,基本工资,婚否,奖金 from [sheet1$]
复制代码
这二个语句具有同样的功能,出来的结果都是一样的。
语句1中的*号是代表全部列,语句 2中,是写上全部列字段的名称。
如果工作表没有列标题,用F1,F2,F3,F4.....这样代替
先看原表
原表.jpg                      
使用SQL语句出来的结果:
结果1.jpg

Microsoft Query方法:
返回工作表.jpg

将结果返回工作表:
返回工作表 2.jpg

返回工作表后样式:
结果2.jpg

Microsoft Query方法返回工作表后修改SQL语句方法:
“数据”选项卡---属性---连接属性--定义---命令文本
修改SQL语句.jpg

补充说明vba启用宏方法:
1.调出开“发工具栏”
调出开发工具栏 1.jpg
2.在自定义功能区,勾选“开发工具栏”
调出开发工具栏 2.jpg
3.设置宏安全性
宏安全性.jpg
宏安全性 2.jpg
4.启用宏
启用宏 1.jpg

5.进入VBA界面2
进入VBA方法2.jpg

6.运行子过程,把光标放于代码中间任意行,按F5执行,或绿色小三角执行。
按F8是逐步执行,是学VBA中重要的调试技术。
运行子过程.jpg


SQL工作簿.rar

10.18 KB, 下载次数: 3354

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 13:44 | 显示全部楼层
本帖最后由 335081548 于 2013-10-9 20:52 编辑

使用SQL语句获取多列数据,及别名的使用
在Excel中所使用的SQL语句具体语法结构如下:
  1. SELECT 列字段名1,列字段名2,列字段名3 FROM [工作表名称$]
复制代码
在本例 中SELECT子句后列出查询的字段即可查询出结果,如输入以下的SQL语句:
  1. SELECT 姓名,  性别,  职称,部门 FROM [Sheet1$]
复制代码
原图.png
使用SQL语句后结果如下图:
结果图.png

使用别名
1.字段别名
  1. SELECT 字段名1 AS 重命名字段1,字段名2 AS 重命名字段2 FROM [Sheet1$]
复制代码
本例中SQL语句如下:
  1. SELECT 姓名 AS Name FROM [Sheet1$]
复制代码




2.工作表别名
  1. SELECT 工作表别名.* FROM [工作表名$] 工作表别名
复制代码
本例中SQL语句如下:
  1. SELECT A.*  FROM [Sheet1$] A
复制代码
所有字段列表
可能到这里就有人会问,你这不是多此一举吗?
2楼不是介绍了
  1. SELECT * FROM [Sheet1$]
复制代码
就行了吗?还整这么复杂干什么?
其实(原来)SQL的代码是应该这样写的,上面的是简写。或者说只有一个表的情况,以后介绍多表的情况会用到表的别名。所以就在此一次把别名介绍了。
  1. SELECT [工作表名].字段名 FROM [工作表名]
复制代码
实际中SQL语句应该如下:
  1. SELCT [Sheet1$].*  FROM [Sheet1$]
复制代码

这样的使用环境中,使用表名,就太大缩短SQL语句的字符和书写方便。

注意事项:
1.在SQL语句中SQL语句英文不区分大小写,但标点符号必须是英文半角状态下输入,字段名也必须跟原来的一样。
2.使用SQL语句的时候,必须避免列字段中使用下面的特殊字符:
空格、双引号(")、撇(')、数字标记(#)、百分号(%)、大于号(>)、小于号(<)、叹号(!)、句号(.)、方括号([或])、星号(*)、美元符号($)、分号(;)、脱字号(^)、圆括号((或))、加号(+)、反斜杠(\或/)。
如果在源数据表的列字段使用了这些特殊字符,那么在使用SQL语句列出各字段的数据时,就会发生错误。为了规范使用SQL语句,在对数据源字段命名时,尽量避免使用这些特殊字符。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 13:46 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 335081548 于 2013-10-10 21:52 编辑

SQL语句中的关键词DISTINCT的使用,功能是去重复值只保留一条记录。语法结构为:
  1. SELECT DISTINCT 要去重复值的字段1,要去重复值的字段2 FROM [工作表名$]
复制代码
实例中使用语法
  1. Select Distinct 用户姓名,用户卡号 From [sheet1$]
复制代码

数据原图:

执行SQL语句后的效果图:


SQL去除重复值.rar






SQL去除重复值.rar

14.49 KB, 下载次数: 2058

评分

4

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 13:48 | 显示全部楼层
本帖最后由 335081548 于 2013-10-10 20:53 编辑

按条件筛选 使用SQL关键词 WHERE查询中的条件指定要满足什么标准信息,去掉不满足条件的数据(删除用户不要的数据)。
WHERE语句中可以有多个条件,条件之间可以用操作符AND 或者OR进行连接。
WHERE语句的语法结构如下:
  1. SELECT 列字段名称 FROM [表名称$] WHERE 列字段名 运算符 值
复制代码

运算符包含大于、小于、等于、不等于、大于或等于、小于或等于、IN、 BETWEEN、AND等 。应用实例进行说明:
提取消费金额等于100的数据。(是不是比函数公式和VBA简单,语句也容易懂。)
本例使用的SQL语句如下:
  1. SELECT * FROM [Sheet1$] where 消费金额=100
复制代码
WHERE 筛选 1.png


TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 13:49 | 显示全部楼层
本帖最后由 335081548 于 2013-10-10 21:46 编辑

ORDER BY的语句使用对于ORDER BY 语句而言,默认值是升序排列,通常是不指定它。但升序的关键词为ASC,降序为DESC
语法结构如下:
  1. SELECT 列字段名 FROM [工作表名称$] ORDER BY 指定列字段名 升序(降序)
复制代码
下面用具体的实例进行说明
按成绩进行升序排序本例中SQL语句如下:
  1. Select * FROM [Sheet1$] ORDER BY 成绩 ASC
复制代码
执行后效果如图
按成绩排序.png

按成绩进行降序排序
本例中SQL语句如下:
  1. Select * FROM [Sheet1$] ORDER BY 成绩 DESC
复制代码
执行后效果如图:
按成绩降序排序.png


二列数据按降序排序:SQL语句如下:
  1. Select * FROM [Sheet1$] ORDER BY 英语 DESC,数学 DESC
复制代码
效果如图:
二列数据按降序排序.png

单列排序.rar

13.48 KB, 下载次数: 1162

二列排序.rar

13.9 KB, 下载次数: 1123

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 14:17 | 显示全部楼层
本帖最后由 335081548 于 2013-10-12 22:06 编辑

TOP在SQL语句中的作用
TOP的应用按顺序提取记录

  1. SELECT TOP 3 * FROM [工作表名$]
复制代码
提取前面3条记录.改变3为想要提取的数字,就可以实现想得到的记录
本例中使用的SQL语句如下:
  1. SELECT TOP 3 * FROM [Sheet1$]
复制代码
结果如图:
TOP 1.png

按字段名排序提取前几名的语法
升序
  1. SELECT TOP 3 字段名 FROM [工作表名] ORDER BY 字段名
复制代码
降序
  1. SELECT TOP 3 字段名 FROM [工作表名] ORDER BY 字段名 DESC
复制代码
以下用实例进行说明
按成绩降序排列提取前三名:
SQL语句如下:
  1. Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY 成绩 DESC
复制代码
结果如图所示:
TOP 2.png
升序:
  1. Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY 成绩
复制代码
结果如图:
TOP 3.png
补充说明:
SQL的VBA语句如下:
  1. Sub Test4()
  2.     Dim Conn As Object, Rst As Object
  3.     Dim strConn As String, strSQL As String
  4.     Dim i As Integer, PathStr As String
  5.     Set Conn = CreateObject("ADODB.Connection")
  6.     Set Rst = CreateObject("ADODB.Recordset")
  7.     PathStr = ThisWorkbook.FullName   '设置工作簿的完整路径和名称
  8.     Select Case Application.Version * 1    '设置连接字符串,根据版本创建连接
  9.     Case Is <= 11
  10.         strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
  11.     Case Is >= 12
  12.         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
  13.     End Select
  14.     '设置SQL查询语句
  15.     'strSQL = "Select top 3 * FROM [Sheet1$] " '按顺序提取数据
  16.     strSQL = "Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY 成绩 DESC"
  17.     Conn.Open strConn    '打开数据库链接
  18.     Set Rst = Conn.Execute(strSQL)    '执行查询,并将结果输出到记录集对象
  19.     With Sheet1.Range("E:G")
  20.         .Cells.Clear
  21.         For i = 0 To Rst.Fields.Count - 1    '填写标题
  22.             .Cells(1, i + 1) = Rst.Fields(i).Name
  23.         Next i
  24.         .Range("A2").CopyFromRecordset Rst
  25.         .Cells.EntireColumn.AutoFit  '自动调整列宽
  26.         .Cells.EntireColumn.AutoFit  '自动调整列宽
  27.     End With
  28.     Rst.Close    '关闭数据库连接
  29.     Conn.Close
  30.     Set Conn = Nothing
  31.     Set Rst = Nothing
  32. End Sub
复制代码

其中 [Sheet1$A1:C17]这个表示工作表名Sheet1的工作表A1:C17的这个单元格区域,这个上面介绍的区别就是突出结果在同一个工作表上。所以加上了指定的单元格区域为数据。可以根据自己的实际情况,来改变.
不在同一张表上显示结果,而且数据源规范,就可以直接用[工作表名$]。或者书写的时候写上列名不用星号("*")
本例也可以用如下的语句达到同样的效果:
  1. Select top 3 姓名,班级,成绩 FROM [Sheet1$]  ORDER BY 成绩 DESC
复制代码
TOP 与 PERCENT 组合在SQL语句中的使用
功能:是按百分比提取
用实例进行说明
按成绩降序排列提取前30%
SQL语句如下:
  1. Select TOP 30 PERCENT * FROM [Sheet1$A1:C17]  ORDER BY 成绩 DESC
复制代码
结果如图所示:
TOP 4.png
注:
数据源16条数据,16*0.3=4.8 四舍五入等于 5 数据提取是整数所以结果是提取5条数据。


SQL_Top.rar

13.86 KB, 下载次数: 1332

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 14:18 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 335081548 于 2013-10-14 20:45 编辑

SQL语句中的一些简单计算函数:
COUNT函数,统计函数
语法如下:
  1. SELECT COUNT(列字段) AS 别名1 FROM [工作表名$]
复制代码
本示例SQL语句如下:
  1. Select count(姓名) as 人数  FROM [Sheet1$]
复制代码
按姓名统计人数,结果如图:

Count.png

SUM函数,求和函数。SQL语句语法如下:
  1. Select Sum(列字段) as 别名 FROM [工作表名$]
复制代码
求总分,本例 SQL语句如下:
  1. Select sum(成绩) as 总分  FROM [Sheet1$]
复制代码
结果如图:
SUM.png

AVG函数,求平均。SQL语句语法如下:
  1. Select AVG(列字段) as 别名 FROM [工作表名$]
复制代码
本例中求平均分,SQL语句如下:
  1. Select AVG(成绩) as 平均分  FROM [Sheet1$]
复制代码
结果如图:
AVG.png

现对上面的语句来个小变通,求成绩大于60的人数、总分、平均分
SQL语句如下:
  1. Select AVG(成绩) as 大于60的平均分,SUM(成绩) as 大于60的总分,COUNT(成绩) as 大于60的人数  FROM [Sheet1$] WHERE 成绩>60
复制代码
结果如图所示:
加条件应用.png

MAX函数,求最大值。SQL语句语法如下:
  1. Select MAX(列字段) as 别名 FROM [工作表名$]
复制代码
本例中SQL语句如下:
  1. Select MAX(成绩) as 最高分  FROM [Sheet1$]
复制代码
结果如图所示:
MAX.png

MIN,求最小值函数,SQL语句语法如下:
  1. Select MIN(列字段) as 别名 FROM [工作表名$]
复制代码
本例中使用的SQL语句如下:
  1. Select MIN(成绩) as 最低分  FROM [Sheet1$]
复制代码
结果如图所示:

MIN.png

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 14:26 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助




占位

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-9 14:31 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 23:42 , Processed in 0.066557 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表