索引和查询

索引

因为我们经常按特定字段查找或排序表中的记录。所以我们可以通过对字段创建索引来加快这些操作的速度。
创建索引后，在表中查找数据时，Access就会在索引中搜索数据的位置，从而提高查找效率。

在这里插入图片描述

自动创建索引
为单字段创建索引

无：不创建索引或删除现有索引
有（有重复）：创建索引，字段中的值可以重复（如销售表中的编码会重复）
有（无重复）：创建索引，字段中的值不可以重复

为多字段创建索引
如果同时按照两个或多个字段进行搜索或排序

一个多字段索引最多可以包含10个字段
主索引【是】：就是主键
唯一索引【是】：索引中不能包含重复值
忽略空值【是】：索引字段中具有空值的记录被排除掉了。

查询

没有建立关系的话，无法合并查询

查询向导

对不同表格进行简单的合并查询引导

简单查询

将学生表里的两个标签，成绩表里的两个标签进行合并

效果如图

创建-查询向导-简单查询向导

将相应的表格标签放在一起-点击下一步-完成

可以看到合并后的表格

关闭文件后还能对其重命名

可以导出sql语句： 开始-视图-sql

重复查询

注意：对1端表进行操作

查询重复项（对一端表）
例如：查找生日相同的人员名单，可以显示满足条件的所有记录，并指定显示它的指定字段。

问：如果你对多端表进行操作，想一想还有意义吗？
答：有意义！处理多对多关系时，将多对多关系处理成两个一对一关系，这时就要这么做了。

案例：如找到相同生日的同学
在这里插入图片描述
创建-查询向导-查找重复项

选择想要查询的表

点击有重复的字段（点完下一步后会发现出生日期的标签消失了）-点击下一步

原因是需要查询的东西不一样了，需要注意把自己另外查询的非重复项放进去-点击下一步-完成
在这里插入图片描述

查找不匹配项

简单的说：就是1端表中有的，但是多端表中没有的。

例如：商品表中有100种商品，但是很多商品这个季节没有货，销售表里只有90种商品有销售记录，那么另外10种就是1端表中有，但是多端表中没有的。（即想查找销售表里面没有记录的商品，就是卖不出去的商品）

在向导中，第一步指定1端表，第二步指定多端表，确定二者连线关系，选择显示的字段。

创建-查询向导-查找不匹配项

在这里插入图片描述
选择商品表（第一步指定1端表）-下一步

选择销售表（第二步指定多端表）-下一步

选择左边和右边都有的商品编码，原因是要靠他来进行连接（确定二者连线关系）

选择想要查询的字段-完成

空表的原因是多端表（销售表）中并没有一端表（商品表）中没有的商品

如果是在一端表中添加一个多的，再重复此过程去查询，可以查到这个重复的项

成功找到

查询设计之简单查询

步骤：
1.创建-查询设计
2.添加需要查询的表，多表需要建立关系
3.选择字段，（*）代表所有字段
4.运行
5.保存查询
案例：创建-查询设计

自动创建了关系

添加关系：如果是整张表的字段都需要，则双击对应表上的*号，其他的也双击

点击返回正常视图，可以发现已经创建完成

保存重命名

和查询向导里面的简单查询的区别：查询向导里面的查询需要提前建立好关系，其实是不利于数据表的储存；而查询设计里的查询是临时建立的查询，对底层的逻辑没有影响

查询设计之条件查询

查询设计-点击相应表格字段-排序-升序

排序成功
勾的作用为是否显示，取消勾选则隐藏起来
增加条件

切记：
条件：字符写法为 “xxx”，日期 #2020/1/1#（文本类的双引号会自动添加）
可以配合：and与、or或、not非

not #2020/1/1# 除了2020/1/1日
#2020/1/2# Or #2020/1/3# 2020/1/2 或 2020/1/3
跟当天日期比 Date()
数字判断 >=60 <=85 =60 （等号可省略）
是/否类型的筛选，写-1/0 或 True/False
如果体现当天日期:Date()
dateserial(年,月.日)可以设置动态的年月日
year(date())提取年
month(date())提取月day(date())提取天

跨字段条件

在这里插入图片描述

and“与”判断
也可以用：between…and 两个值或日期之间

例1：150到250之间
BETWEEN 150 AND 250 等价于 >= 150 and <= 250
注意事项：
（1）between…and 这两个值是包含本身的，就相当于是大于等于或小于等于。
（2）这两个值的位置不能交换，他的意思是大于等于左边，小于等于右边。

例2：除了150到250之间
NOT BETWEEN 150 AND 250
例3：2020/1/1 至 2020/1/3
BETWEEN #2020/1/1# AND #2020/1/3#

in 指定范围
in 指定条件范围

例1：只需要店号是1，3，4的
“1” Or “3” Or “4” 等价于 In (“1”,“3”,“4”)
例2：只包含2020/1/1和2020/1/2
In (#2020/1/1#,#2020/1/2#)
例3：除非2020/1/1和2020/1/2

Not In (#2020/1/1#,#2020/1/2#)不在范围内
is Null 或 is Not Null为空或者非空
例4：查询销售数量字段的空值或非空值

查询空值

查询非空

查询空值时注意空值和空字符的区别（如果是文本列需要注意，如果是数值列，可以不理会）

用以上两个单元格做例子，使用is null的话，只能找到空值列

只能找到170这个

所以在找的时候可以加上""进行筛选，如下

成功找到
like 使用通配符
通配符：*代表0至多个字符，？代替一个字符，#代替一个数字

【01】商品编码是A开头，1结束，中间可以有任意多个字符
Like "A*1"
【02】如果商品编码是A00*,*号本身是通配符
Like "A*[*]"
【03】如果商品编码是A00[*],如何查询
Like "A*[[*]]"  (外面的[]为转义字符)案例：
Like "A??3" 找到商品编码A123
Like "A*3" 找到商品编码A123和A03
Like "A*6" 找到商品编码A06
Like "A[*]6" 找到商品编码A*6
Like "A123" 找到商品编码A123
Not Like "A123" 找到除了A123以外的
Like "[A,B]*" 找到A或B开头的
Not Like "[A,B]*" 等价于 Like "[!A,B]*" 找到不是A或B开头的

【01】

在这里插入图片描述
【02】

【03】

在这里插入图片描述

参数查询

输入[请输入商品名称]
范围查询
输入>[大于几] and <[小于几]-分两次输入范围大值和小值

在这里插入图片描述

查询到100-200的商品

拓展：查询2020/1/2至2020/1/3日某家店铺的数据
>=[开始时间] And <=[结束时间]
输日期时注意：
只能输入 2020/1/1 或 2020-1-1 这样的类型不能是2020.1.1

坑：文字虽然是提示但不能是一样的
>=[开始时间] And <=[开始时间]

查询该关键词“溪风”时发现无法查询

在这里插入图片描述
原因是该两表通过商品编号来建立关系，而“溪风”商品编号在销售表中是不存在的

参数查询使用通配符

例如：商品表中，小类名称包含“肉”的

Like "*肉*"
等价于
Like "*" & "肉" & "*"
改成参数查询
Like "*" & [请输入小类名中包含的文字] & "*"

在这里插入图片描述
改成参数查询

在这里插入图片描述

如果不想显示“猪肉”，可以将后面的[*]改成[?]

只有猪肉两个字的商品就没有找到了

只显示部分查询结果

计算字段

语法：新字段名: [字段1]*[字段2]

输入“销售成本: [进价]*[销售数量]”

凡是你创建出来的字段，后来可以反复使用这个字段
例如：计算毛利额
毛利额:[销售金额]-[销售成本]

计算售价总额

销售金额: [售价]*[销售数量]
在这里插入图片描述

部分销售金额（前面创造的字段，后面可以引用）
部分销售金额: [销售金额]*0.35

在这里插入图片描述

计算毛利
毛利金额: [销售金额]-[销售成本]

在这里插入图片描述

拓展知识：已知出生日期，算年龄
年龄:Year(Date())-Year([出生日期])
Date()返回当前日期
Year(日期)提取年份

解决查询结果小数点问题

修改显示方式，注意：这只是改变了显示，并没有改变值本身
右键-属性

格式选标准-小数位数选2

成功

Format函数就是格式化

方法二：使用Format函数
注意：被Format转换的都会变成文本型，无论原来是数值，日期都被变成文本型了。
如果售价和销售数量字段是没有重复项的：

销售金额: Format([售价]*[销售数量],"固定")

在这里插入图片描述

如果售价和销售数量有重复的字段，以上写法等价于：

销售金额: Format([商品表]![售价]*[销售表]![销售数量],"固定")

在这里插入图片描述

小结

从身份证号中提取出生日期

从身份证号中提取出生日期
当天日期：Date()
dateserial(年,月,日)可以设置动态的年月日
year(日期) 提取年
month(日期) 提取月
day(日期) 提取天

出生日期: Mid([身份证号],7,8)
在这里插入图片描述

在这里插入图片描述
出生日期:DateSerial(Mid([身份证号],7,4),Mid([身份证号],11,2),Mid([身份证号],13,2))

年龄:year(date())-Mid([身份证号],7,4)

常用文本函数

1.Asc
说明:返回字母的Acsii值
举例:Asc(“A”)返回65
2.Chr
说明:将ascii值转换到字符
举例:chr(65)返回"A"
3.Format
说明:格式化字符串
举例:Format(now(),“yyyy-mm-dd”)返回类似于"2008-04-03"
Format(3/9,“0.00”)返回0.33
4.InStr
说明:查询子串在字符串中的第一个出现的位置,没有返回0
举例:Instr(“abc”,“a”) 返回1
5.LCase
说明:返回字符串的小写形式
举例:LCase(“ABC”) 返回"abc"
6.Left
说明:左截取字符串
举例:Left(“ABC”,1) 返回"A"
7.Len
说明:返回字符串长度
举例:Len(“ABC你好”)返回5
8.LTrim
说明:左截取空格
举例:LTrim(" 111") 返回"111"
9.Mid
说明:取得子字符串
举例:mid(“abcd”,1,2) 返回"ab"
10.Right
说明:右截取字符串
举例:Right(“ABC”,1) 返回"C"
11.RTrim
说明:右截取空格
举例:RTrim(“ABC “) 返回"ABC”
12.Space
说明:产生空格
举例:Space(5) 返回5个空格
13.StrComp
说明:比较两个字符串是否内容一致(不区分大小写)
举例:StrComp(“abc”,“ABC”)返回0
StrComp(“abc”,“123”)返回-1
14.Trim
说明:截取字符串两头的空格
举例:Trim(” ABC “) 返回"ABC”
15.UCase
说明:将字符串转大写
举例:UCase(“abc”) 返回"ABC"
16.字段连接 &
店号和店名: [店号] & [店名]
店号和店名: [店号] & “-” & [店名]

常用数学函数

【01】计算绝对值：
abs(-7) 返回7
【02】返回小于等于参数的最大整数
int(3.9) 返回3
int(-3.9) 返回4
【03】返回参数的整数部分
fix(3.9) 返回3
fix(-3.9) 返回-3
【04】四舍五入
round(3.567,2) 返回3.57
round(3.567,0) 返回4
round(3.567) 返回4
【05】开根号
sqr(4) 返回2
【06】返回符号，正数返回1，0返回0，负数返回-1
sgn(20) 返回1
sgn(0) 返回0
sgn(-20) 返回-1
【07】取0~1之间的随机数
rnd()

例如：
如要选择一个200个记录表里面随机的100个记录
如果你表中有id的话就这么用，但是id要是数字型
SELECT top 100 * from 表名 order by rnd(id)
如果你要引用一个非数字型字段
SELECT top 100 * from 表名 order by rnd(len(字段名))

日期时间函数

date() 返回当前日期，例如 2021/2/14
time() 返回当前时间，例如 7:30
now() 返回当前日期和时间，例如 2021/2/14 7：30
year(日期) 提取年份
month(日期) 提取月份
day(日期) 提取日
dateserial(年,月,日) 返回组合成的日期，例如 dateserial(2020,7,30) 返回2020/7/30
hour(时间) 提取小时
minute(时间) 提取分钟
second(时间) 提取秒
weekday(日期,[参数指定一周从哪天开始])
当Sunday（星期日）为1、Saturday（星期六）为7时，该参数为1（默认）
当Monday（星期一）为1、Sunday（星期日）为7时，该参数为2（这种情况符合中国人的习惯）
当Monday（星期一）为0、Sunday（星期日）为6时，该参数为3。
DateAdd(单位，间隔数，日期)
“yyyy” 年
“q” 季度
“m” 月
“d” 日
“y” 一年中的天数
“w” 一周的第几天
“ww” 周
“h” 小时
“n” 分钟
“s” 秒
DateAdd(“ww”,1,#2021/2/1#) 返回：2021/2/8
DateAdd(“m”,-1,#2021/2/1#) 返回：2021/1/1
DateAdd(“yyyy”,1,#2021/2/1#) 返回：2022/2/1