Hello, 今天我们来学数据库的应用~

数据库可以添加记录、编辑记录、删除记录、组织和查看记录

从Access开始学数据库，SQL语言是通用的

Access是小型数据库，和EXCEL兼容性比较好

ACCESS可以用来做什么？

SQL语句：

1. 字母大小写都ok，符号都是英文状态下输入

2.关键字（操作指令与操作对象）用空格（）分隔 

3. 字段（变量）/参数（变量值）用逗号（,）分隔

4. 字符型参数，用单引号（''）括起来

5. 最后的最后加分号（；）

6. 含有特殊字符的表名/字段名，用方括号（[]）括起来

7. SELECT *表示选中所有

8. 含有时间/日期类数据的参数/查询条件，用（#）括起来

1. 导入数据

A 将数据导入到当前数据库新表中（ACCESS的操作不会对源数据产生影响）

B 通过创建链接表链接到数据源（无法更改/删除源数据，可以编辑/增加纪录到源数据）

2. 数据合并

2.1 横向合并->添加同一批个案的其他变量（扩充列）

Situation 1  你有两个表，包含不同的变量，你要从另一个表抽一些变量到现有的表格中

菜单操作：

A 首先，你要建立两个表格的关系

【数据库工具】——【关系】 同时选中2个表格并添加 将左表中的用户ID拖动到右表的用户ID，然后松开 点击【联接类型】，选择联接属性 ，【确定】——【创建】

INNER JOIN/ LEFT JOIN / RIGHT JOIN

右键页面名称，选择保存，然后点击【关闭】——再次点击关系可查看

B 然后开始查询：

【创建】——【查询向导】 选择简单查询向导就好（查询向导可以交叉分析、查找重复项、查找不匹配项） 选择不同的表，然后选择不同的字段到选定字段框 你可以在上图的下一步中给这个查询取个名字，或者直接点击完成，然后得到联合查询结果 每双击一次左边的查询项.....系统就会重新执行一次查询——当数量很大时，请不要手贱

SQL查询：

只有查询项对应列表下才有SQL视图可选 点击SQL之后，可以看到与菜单栏查询操作对应的SQL语句

首先，内联接两个表格，联接规则是按照用户ID匹配，然后从联接表格中选取对应变量

2.2  纵向合并->添加同一批变量的其他个案（扩充行）

Situation 2  你有两个表，包含相同的变量，你要把一个表并到现有的表中

不需要建立关系，直接建立查询，这次我们用查询设计：

【创建】——【查询设计】

选中【要被添加到别的表格的表】，添加，关闭 在【设计】下点击【追加】 选择你想要保留所有CASE的那个表格 双击所有变量，会看到下框中出现了追加的具体信息 点击【设计】——【运行】，然后在弹出的窗口确认

Situation 3  你有两个表，包含相同的变量，你要把两个表的内容都插入到一个新的表中：

SQL

SELECT*INTO order_total

FROM

(SELECT * FROM order1

UNION ALL

SELECT * FROM order2)

3. 数据计算

Situation 4  你想根据现有的变量，生成新的变量：

3.1 简单计算

通过查询设计建立一个新的查询——添加需要进行操作的目标表格——依次双击所有相关变量（可勾选是否显示）

——在设计视图上的空白列中->输入新变量的表达公式：

冒号表示等于，方括号内是现有变量，*就是相乘 点击左上角的【运行】之后会得到含有新变量的查询表格 对应的SQL语句  用AS代替等于

3.2 函数计算

重新建立一个查询——添加目标表格 ——选择相关变量

A 进入SQL试图（点击右下角倒数第二个图标）——直接编辑SQL语句：

SELECT persons.用户ID, persons.注册日期, DateDiff("d",[注册日期],#12/31/2011#) AS 注册天数

FROM persons;

B 进入设计试图（点击右下角最后一个图标）——在新的一列中输入用函数定义的新变量：

注册天数: DateDiff("d",[注册日期],#2011/12/31#)

NOTE 1："d" 表示按天计算，如果是m就是按月计算，yyyy才是按年计算

                w 按周计算 q 按季度计算 h/n/s 对应的是小时、分钟、秒

NOTE 2： ## 是原来指明这是一个时间变量

然后附上常用的一些函数，你可以自己都试试：

Access常用函数

4. 数据分组

SITUATION 5  “你想根据定距变量生成一个分组的名称/定序变量”

方法1：

IIF（表达式，成立时返回的值，不成立时返回的值）

新建一个查询——>点击进入SQL视图窗口——>输入以下:

SELECT 用户ID,年龄,

IIF(年龄<=20,"20-",

     IIF(年龄<=30,"30-",

           IIF(年龄<=40,"40-",

                                 "40+")))

AS 年龄分组

FROM persons

NOTE 1 注意全部要用英文标点符号  中间的空格、缩进都是不必要的，只是为了直观

NOTE 2 如果只用SQL进行操作，新建查询时可以不必添加目标表格，直接执行SQL命令

IIF函数得到的年龄分组

方法2：

CHOOSE  (表达式返回的参数，参数为1时返回的结果1，参数为2时返回的结果2,...)

新建一个查询——>点击进入SQL视图窗口——>输入以下:

SELECT 用户ID,年龄,

CHOOSE( (年龄-1)/10+1, "10-","10+","20+","30+","40+")

AS 年龄分组

FROM persons

CHOOSE 函数得到的年龄分组

方法3：

SWITCH  (条件1，条件1为True返回的结果1，条件2，若条件2为True返回的结果2，...)

新建一个查询——>点击进入SQL视图窗口——>输入以下:

SELECT 用户ID,年龄,

SWITCH(年龄<=20,"20-",

               年龄<=30,"30-",

               年龄<=40,"40-",

               年龄>40, "40+") 

AS 年龄分组

FROM persons

SWITCH函数得到的年龄分组

方法4：

PARTITION  (数值参数/变量名，开始值，结束值，组距)  ->返回变量值所处区间的上下限

新建一个查询——>点击进入SQL视图窗口——>输入以下:

SELECT 用户ID,年龄,

PARTITION(年龄,1,100,20)

AS 年龄分组

FROM persons

PARTITION 得到的年龄分组

SITUATION 6   “你想根据一个日期变量生成几个具体的时间变量”

新建一个查询——>点击进入SQL视图窗口——>输入以下:

SELECT 订单编号,订购日期,

FORMAT(订购日期,"yyyy")AS 年,

FORMAT(订购日期,"q")AS 季,

FORMAT(订购日期,"m")AS 月,

FORMAT(订购日期,"d")AS 日,

FORMAT(订购日期,"dddd")AS 星期,

FORMAT(订购日期,"h")AS 小时,

FORMAT(订购日期,"n")AS 分,

FORMAT(订购日期,"s")AS 秒

FROM orders;

NOTE 1 SELECT子句内部是用逗号分隔，但是结尾是没有符号的——From之前没有逗号! 

NOTE 2 规范的操作建议写成 [订购日期]

然后你得到对应的各个变量

5. 重复数据   

SITUATION 7  “你想知道重复出现的 CASE各出现了几次”

菜单操作：

【创建】——【查询向导】——【查找重复项查询向导】 选择需要查重的表格 选择需要查重的变量，如果点击下一步可以选择第二优先级的查重变量 如果上图直接点击完成，就会得到这样，第一列是完全没有重复的CASE，第二列是对应的频数

NOTE 1 菜单操作默认只呈现重复次数大于1的CASE，可以通过HAVING语句来调整

NOTE 2 菜单操作默认按照查重变量排序，可以通过ORDER语句来调整

对应SQL：

SELECT First(orders.[用户ID]) AS [用户ID 字段], Count(orders.[用户ID]) AS NumberOfDups

FROM orders

GROUP BY orders.[用户ID]

HAVING (((Count(orders.[用户ID]))>1));

如果需要按照出现频率倒序排序：

SELECT First(用户ID) AS 所有用户, Count(用户ID) AS 重复次数

FROM orders

GROUP BY 用户ID

HAVING COUNT(用户ID)>1

ORDER BY COUNT(用户ID) DESC

默认从低到高，倒序则从高到低

SITUATION 8  “你想得到去重后的CASE”

方法1：

SELECT First(用户ID) AS 所有用户, Count(用户ID) AS 重复次数

FROM orders

GROUP BY 用户ID

第一列就是去重后的用户ID

方法2：

SELECT DISTINCT 用户ID

FROM orders

直接选取去重后的数据

6. 数据分析 

6.1 简单统计 

SITUATION 9 “你想对特定变量进行简单的描述性统计”

SELECT

COUNT(订单编号) AS 订单总数,

SUM(订购金额) AS 订购金额总额,

AVG(订购金额) AS 平均订单金额 

FROM orders;                      

You gonna get this

6.2 分组统计   GROUP BY

SITUATION 10 “你想统计不同产品对应的订单数与订单金额”

SELECT 产品,

COUNT(订单编号) AS 订单总数,

SUM(订购金额) AS 订购金额总额,

AVG(订购金额) AS 平均订单金额

FROM orders

GROUP BY 产品

然后就从一个总的行 变成了各个产品对应的各行

SITUATION 11 “你想统计不同（新生成的）时间段内的订单数”

SELECT FORMAT(订购日期,"h") AS 时段,

COUNT(订单编号) AS 订单数

FROM 订购明细

GROUP BY FORMAT(订购日期,"h")

SITUATION 12 “你想统计不同年龄段的（去重之后）客户数”

SELECT 年龄分组,COUNT(用户ID) AS 用户数

FROM

(SELECT DISTINCT A.用户ID,PARTITION(B.年龄,1,100,5) AS 年龄分组

FROM orders A, persons B

WHERE A.用户ID=B.用户ID)

GROUP BY 年龄分组

不同年龄阶段的用户数

NOTE 1 ACCESS的去重处理必须使用嵌套查询：把去重结果作为子查询

NOTE 2 SELECT 后的Count的对象必须是无重复值的！

如果知道目标变量没有重复的值，无须去重：

SELECT  B.省份, COUNT(A.订单编号) AS 订单数

FROM orders A, persons B

WHERE A.用户ID=B.用户ID

GROUP BY B.省份

得到不同省份的订单数

6.3 交叉分析

SITUATION 13 “你想统计不同省份、不同性别的用户数”

菜单操作：

【创建】——【查询向导】——【交差表查询向导】 1. 选择表格 2. 选择行变量 3. 选择列变量 4. 选择行列交叉点统计值 5. 命名 然后，你得到了不同省份不同性别的CELL的用户数量统计

SQL：

假如你只是想对一个变量分组统计：

SELECT  定义默认列（省份+总计）

GROUP BY 定义分组 （省份）

你得到的是行变量

假如你想在此基础上多加一个分组变量：

TRANSFORM COUNT(用户ID)

SELECT省份,  COUNT(用户ID) AS 总计

FROM persons

GROUP BY 省份

PIVOT  性别

你得到了列变量-性别的两个值得对应列

DONE.