CDA Level1知识点总结之数据库应用

策略分析

考试时都是选择题，不会让你直接写sql查询语句，但这一章节很多语法细节点，很多的知识盲区，需要高密度的积累。

考点总结

数据库概述

数据库是存储、调用、分析数据的仓库，分关系型数据库（Oracle、DB2、Microsoft SQL Server、MySQL）和非关系型数据库（MongoDB、Redis、HBase）。数据库系统是由数据库、数据库管理系统和用户组成。
SQL–结构化查询语言

数据定义语言DDL–增删改查

创建、使用、删除数据库 create、use、drop
创建、查看、删除数据表create、show、drop
修改表名、修改字段数据类型、修改字段名、增加字段、修改字段排列位置、删除字段
alter…rename、alter…modify、alter…change、alter…add、alter…modify、alter…drop
约束条件：主键约束、非空约束、唯一性约束、默认约束、自增字段
数值类型：int系列、float(M,D)、double(M,D)、decimal(M,D)、日期时间、字符串
删除delete/drop/truncate
delete/drop/truncate的比较：

delete	drop	truncate
删除表数据，保留表结构，还能删除单行	删除表数据和表结构	可删除整个表数据、保留表结构，不可单行删除，不可用where
DML	DDL	DDL
delete table table1/delete from table1 where …	drop table table1	truncate table table1

数据操作语言DML–数据填充、修改、删除

记录信息的添加insert、更新update、删除delete from table1 where…
检查表结构 desc table1;

数据库查询

执行顺序

from>where>group by >having >select >order by

select 语句的操作符

算术操作符（+、-、*、/）
比较操作符 （=、>、<、！=或<>等）
聚合类函数–avg/sum/max/min/count
多表查询–内连接inner join 或join、左连接、右连接、联合查询union、全连接查询full join(left join …union…right join…)
union 去重、union all 不去重

查询操作符与子查询

and、or、in、not in、between、like、not like、is null、distinct
其中在使用like时，%代表任意长度的字符串，_代表长度为1的字符。
子查询：写在()里，把内层查询结果当做外层查询的参照数据表来使用
any、all、exists（返回真假值）
select * from fruits where f_id=any(select f_id from fruits where f_price between 10 and 20);
as重命名
列的 SQL 别名语法
SELECT column_name AS alias_name FROM table_name;
表的 SQL 别名语法
SELECT column_name(s) FROM table_name AS alias_name;

limit限制查询结果行数

注意：表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询（带聚合函数）不能用表连接来替换。用来查询匹配值、非匹配值的子查询可以替换成表连接。

函数

group_concat(col)
cast(‘12’ as int)
逻辑函数：
ifnull空值函数、if判断函数、case when expr1 then expr2
开窗函数：
对数据的每一行，都使用与该行相关的行进行计算并返回计算结果，运算前后记录数不变。开窗函数格式： 函数名(列) OVER(选项)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
select fname, fcity, fsalary, count(*) over() 工资小于5000员工数 from t_person where fsalary < 5000
在上边的例子中，开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

PARTITION BY 子句：
开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独
立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。



ER图：实体-联系图，描述现实世界的概念模型

习题整理（易错题、难题）

1.关系型数据库在系统搭建以及数据分析过程中都发挥着重要作用，对于关系型数据库功能的核心定位应属于
• A.OLTP
• B.OLAP
• C.DW
• D.Cube
答：A.数据库主要功能是事务处理用的，所以定位是OLTP，联机事务处理。

2.别名是SQL中常用的内容，通过别名可以区别不同的表、可以让查询结果可读性增强。在SQL语句中，别名不能出现在哪个⼦句中呢（）？
• A.SELECT
• B.WHERE
• C.FROM
• D.JOIN
答：B.在SQL中可以设置表别名和列别名，SQL语句的执⾏顺序是FROM–>-->ON–>JOIN–>WHERE–>GROUP BY-- >HAVING–>SELECT–>ORDER BY，别名是在WHERE⼦句后计算的，所以WHERE⼦句中不能⽤列别名， SELECT、FROM和JOIN⼦句中可以⽤表别名。

3.子查询是SQL语句中常用语法，合理的使用子查询可以让代码更简洁。以下关于子查询，说法不正确的是()
• A.表子查询必须添加表别名
• B.引用表子查询中的计算字段，必须添加列别名
• C.所有的连接查询都可以替换为子查询
• D.所有的子查询都可以替换为连接查询
答：D. 当WHERE子句的查询条件是聚合函数时，子查询不能替换为连接查询。

4.在关系型数据库中，简历数据库表时，将年龄字段值限制在12-40岁之间的这种约束属于（）
A.视图完整性约束
B.域完整性约束
C.参照完整性约束
D.实体完整性约束
答：B.
实体完整性是是指每个表都有唯一的标识符–主键，主键不能为空或重复。
参照完整性是指外键的取值必须是另一个表的有效值或空值。
用户定义完整性是指满足用户的某个需求，字段不为空等。
域完整性约束：域代表当前单元格，限制此单元格的数据正确。

5.下列哪个关于追加查询的陈述是错误的（）
A.字段名称相同的进行纵向合并，不相同的另添加一列
B.返回表中的列名将与主表的列名匹配
C.重复的行会被删除
D.纵向合并的字段数据类型要一致
答：D.追加查询可将符合条件的数据追加到一个已存在的表中，该表可以是当前数据库中的一个表，也可以是另一个数据库中的表，没有要求这两个表必须结构一致。

6.下列关于SQL语句的说法错误的是（）
A. SQL对大小写不敏感
B. SQL为非过程化编程语言
C. 不同的数据库的SQL完全一致
D. 一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统
答：C。
SQL是非过程化编程语言，允许用户在高层数据结构工作，无需用户指定数据的存放方法，也不需了解具体的数据存放方式。

7.设计用户表时，身份证号为固定18位长，对该字段最好采用（）数据类型。
A.int(18)
B.char(18)
C.varchar(18)
D.text(18)
答：B.text不能指定长度。

8.去除列表l=[‘b’,‘c’,‘d’,‘c’,‘b’,‘a’,‘a’]中的重复元素，下列哪种方法不能实现（）
A. list(set(L))
B. set(L).tolist()
C.L1=[][L1.append(x) for x in L if x not in L1] print(L1)
D. d=dict() for i in L: d[i]=1 list(d.keys())
答：B
我选D，没理解D选项的含义，它在建立字典的过程中就排除了重复的键。而B看起来是对的，但tolist只能用于数组矩阵转化成list，对于set，只能用list(set(L))的方法。

9.在多个业务表连接构成的多维模型中，如果需要进行跨表筛选应满足___的逻辑
• A.维度与主键是父子级关系
• B**.维度与公共字段是父子级关系**
• C.维度与度量是父子级关系
• D.度量与主键是父子级关系
B跨表筛选应满足B的条件才能保证按照维度下汇总计算的度量的方式进行计算，所以选B

10.某电商平台进行分析时，不能直接描述商品销售情况好坏的指标是
• A.库存周转率
• B.库销比
• C.动销金额
• D.动销天数
答：D.动销天数是在一段期间内产品的销售天数，用来辅助描述销售行为的风险程度而不是好坏程度。动销次数是订单数量。

11.建立如下数据库表： CREATE TABLE department( departid int not null primary key, deptname varchar(20) not null ); CREATE TABLE employee( employeeid int not null, deptid int not null, ename varchar(20) not null,job varchar(10),sal decimal(10,2)); 以下SQL语句中，错误的是
• A.SELECT AVG(sal) FROM employee;
• B.SELECT deptid,AVG(sal) FROM employee GROUP BY deptid;
• C.SELECT deptid,job,AVG(sal) FROM employee GROUP BY deptid,job;
• D.SELECT deptid,AVG(sal) FROM employee GROUP BY deptid HAVING job=’clerk’
答：D。没有错选，但对于having使用的限制产生疑虑。 HAVING 子句中的筛选字段必须是可以出现在分组结果中的字段。
HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）
select uid,email,count(*) as ct from edm_user081217 GROUP BY email HAVING ct > 1

12.表格结构数据与表结构数据是进行分析时主要使用的结构化数据，在表结构数据下得到指标值结果描述正确的是
• A.对表结构业务数据进行汇总计算
• B.前端系统导出
• C.IT人员提供
• D.前边都不对
将表结构数据进行字段整体的汇总计算可以求得指标结果，所以选A

13：什么是交叉连接？
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

14.指标分析是业务描述性分析中观测业务行为结果的重要方法，以下选项中不属于基本指标的是
• A.求和类指标
• B.比较类指标
• C.计数类指标
• D.业务场景相关指标
答：D是场景相关的特殊指标，不属于基本指标。

15.student表中有4个字段：StudentID（学生编号）,Class（班级），CourseID（课程编号），Score（分数） student 表中记录了同学每一次考试的成绩。那么以下哪组 sql 代码取数的结果中，StudentID字段取值不会出现重复
• A.select StudentID,max(score) from student group by StudentID;
• B.select distinct StudentID,Score from student;
• C.select StudentID from student;
• D.select StudentID from student where studentID is not null;
答：A。疑问在于A和B。要知道DISTINCT 用在所有的检测列之前，并且它是作用于所有列，不能部分使用。即只有 （StudentID,Score）这个组合的数据都相同时候，才会被“去重”，否则 还是会保留。所以B是错的。