CDA备考学习学习笔记——基础知识篇(二)
一、数据库
1.1 数据库概述
数据库概述: 关系型数据库管理系统(RDBMS)与结构化查询语言(SQL)
数据库: 是存储、调用、分析数据的仓库,主要分为 关系型数据库 与 非关系型数据库 两类。
- 关系数据库:是数据库应用的主流;
- 关系型数据库以行和列的形式存储数据,这一系列的行和列被称为“表”,一组表组成了数据库;
- 当前主流的关系型数据库有Oracle、DB2、Microsoft SQL Server、MySQL等。
SQL结构化查询语言: 操作关系型数据库时使用的语言叫结构化查询语言,简称“SQL结构化查询语言”。
MySQL是最流行的关系型数据库管理系统之一。
数据库: 数据库是表的集合,带有相关的数据。
- 表: 一个表是多个字段的集合;
- 字段: 一个字段是一列数据,由字段名和记录组成。
- 在商业数据分析中使用的绝大部分数据都来自于
企业数据库
; - 数据库负责
数据收集、数据整合、数据调用
等工作; - 几乎所有数据分析类工具都支持
调用数据库中数据
。
1.2 数据定义语言(DDL)
数据定义语言(DDL): 是用来对数据库管理系统中的对象进行 “增、删、改、查”操作 的SQL语句,一般应用DDL语言对数据库不同对象进行定义操作时,需要提前具备相应数据库管理权限。
1.2.1 针对数据库对象的操作语言
操作关系型数据库:
- 创建数据库:
create database 数据库名称;
例:创建名为test的测试数据库
create database test;
- 查看创建好的数据库:
show create database 数据库名称;
例:查看创建好的test数据库
show create database test;
- 查看所有数据库列表:
show databases;
- 使用数据库:
use 数据库名称;
例:使用创建好的test数据库
use test;
- 删除数据库:
drop database 数据库名称;
例:删除创建好的test数据库
drop database test;
1.2.2 针对数据表对象的操作语言
数据表:
- 数据库是由多个数据表构成的;
- 每张数据表存储多个字段;
- 每个字段由不同的字段名及记录构成,
每个字段有自己的数据结构及约束条件
。
创建、查看及删除数据表:
- 创建数据表:
create table 表名(...);
典型范例:
例:用SQL语句创建以下员工信息表:
1、使用test数据库:use test;
2、创建员工信息表:
create table emp(
depid char(3),
depname varchar(20),
peoplecount int
);
- 查看表是否创建成功:
show tables;
- 删除数据表:
drop table emp;
数据表中的数据类型:
例:会员注册信息表
- 注册时间:日期型;
- 用户名:文本型;
- 充值金额:小数型;
“位”与“字节”:
- 位: “位(bit)”是电子计算机中最小的数据单位。每一位的状态只能是0或1;
- 字节: 8个二进制位构成一个“字节(Byte)”,它是存储空间的基本计量单位;
- 数据类型: 不同的数据类型具有不同的字节长度。
字节长度越长能取值的数值区间也就越大
。
一个INT整数型数据占用四个字节长度,有符号大小-2147483648 ~ 2147483647,无符号大小0 ~ 4294967295。
数据表中的数据类型:
1. 数值类型(整型) :
- INT: 有符号的和无符号的。有符号大小-2147483648 ~ 2147483647,无符号大小0 ~ 4294967295。最大显示宽度为11-int(11)。
- TINYINT: 有符号的和无符号的。有符号大小-128 ~ 127,无符号大小为0 ~ 255。 最大显示宽度为4-tinyint(4)。
- SMALLINT: 有符号的和无符号的。有符号大小-32768 ~ 32767,无符号大小为0 ~ 65535。 最大显示宽度为6-smallint(6)。
- MEDIUMINT: 有符号的和无符号的。有符号大小-8388608 ~ 8388607,无符号大小为0 ~ 16777215。 最大显示宽度为9-mediumint(9)。
- BIGINT: 有符号的和无符号的。 最大显示宽度为20-bigint(20)。
备注:
在指定字段数据类型时如果在数据类型后加上UNSIGNED属性可以禁止负数。
2. 数值类型(小数型) :
- FLOAT(M,D): 只能为有符号的。4字节,默认为(10,2)。
- DOUBLE(M,D): 只能为有符号的。8字节,默认为(16,4)。
- DECIMAL(M,D): 只能为有符号的。
MySQL关于decimal(m,d)的说明:
\quad
decimal(m,n)的意思
是:m位数中,有n位是小数,即m-n位整数。例如:decimal(5,2)的意思是三位整数和两位小数。
\quaddecimal(5,2)的取值范围
是:-999.99 到 999.99,所以有7 个字节可用。decimal(m,d)的参数说明
:- (1)m:是数字的最大数(精度)【即:指定小数点左边和右边总共可以存储的十进制数字的最大个数】。其范围为1~65(在较旧的MySQL版本中,允许的范围是1~254)。
- (2)d:是小数点右侧数字的数目(标度)【即:指定小数点右边可以存储的十进制数字的最大个数。】。其范围是0~30,但不得超过M。
\quad
说明:float占4个字节,double占8个字节,decimail(M,D)占M+2个字节(M>D的时候)
3. 日期时间型 :
- DATE: YYYY-MM-DD格式,在1000-01-01到9999-12-31之间。例如:1973-12-30;
- DATETIME: YYYY-MM-DD HH:MM:SS格式,在1000-01-01 00:00:00到9999-12-31 23:59:59之间。例如:1973-12-30 15:30:00;
- TIME: HH:MM:SS格式,在-838:59:59到838:59:59之间。例如:15:30:00;
- TIMESTAMP: 称为【时间戳】,取值范围在1970-01-01 00:00:00和2037-12-31 23:59:59之间。例如:1973-12-30 15:30:00,则在数据库中存储为:19731230150000;
- YEAR(2|4): 以2位或4位格式存储年份值。如果是2位,1970 ~ 2069;如果是4位,1901 ~ 2155。默认长度为4。
4. 字符串型 :
- CHAR(M): 固定长度字符串,长度为1-255。如果内容小于指定长度,右边填充空格。如果不指定长度,默认为1;
- VARCHAR(M): 可变长度字符串,长度为1-255。定义该类型时必须指定长度;
- BLOB或TEXT: 最大长度65535。
存储二进制大数据,如图片
。不能指定长度。二者区别:BLOB大小写敏感
。 - TINYBLOB或TINYTEXT: 最大长度255。不能指定长度。
- MEDIUMBLOB或MEDIUMTEXT: 最大长度16777215字符。不能指定长度。
- LONGBLOB或LONGTEXT: 最大长度4294967295字符。不能指定长度。
- ENUM:
枚举
。例如:ENUM(‘A’,‘B’,‘C’)。NULL值也可以。
1.2.3 约束条件
约束条件:
- 约束是在表上强制执行的数据检验规则;
- 约束是用来保证创建的表的数据完整和正确;
- MySQL数据库常用约束条件。
在MySQL中作为主键的字段需满足如下约束条件:非空、唯一、自增
。
1.2.3.1 主键约束
主键约束: 保证表中每行记录都不重复。
主键: 又称为“主码”,是数据表中一列或多列的组合。主键约束要求主键列的数据必须是惟一的,并且不允许为空。使用惟一地标识表中的一条记录,还可以加速数据库查询的速度。
1.2.3.2 非空约束
非空约束: 指的是字段的值不能为空。
1.2.3.3 唯一性约束
唯一性约束: 要求该列的值必须是唯一。
- 允许为空,但只能出现一个空值;
- 一个表中可以有多个字段声明为唯一的;
- 唯一约束确保数据表的一列或几列不出现重复值;
1.2.3.4 默认约束
默认约束: 指定某个字段的默认值。
- 当插入记录时如果没有明确为字段赋值时,那么系统就会自动为这个字段赋值为默认约束设定的值。
1.2.3.5 自增字段
自增字段: 一个表只能有一个自增字段,自增字段必须作为主键的一部分。
默认情况下从1开始自增。
1.2.4 修改数据表
修改表: 指修改数据库中已经存在的数据表的结构。
MySQL使用alter table语句修改数据表结构,包括:修改表名
,修改字段数据类型
或者字段名
,增加和删除字段
,修改字段的排列位置
等。
1.3 数据操作语言(DML)
数据操作语言(DML): 是对表中记录进行 添加(INSERT)、更新(UPDATE)、删除(DELETE) 等操作的语言,与DDL语言相同,在使用DML语言对记录信息进行操作时,也需要提前拥有相应的操作权限。
1.3.1 数据填充
- 填充数据——(1)用insert into 语句为表插入数据
语法: insert into 表名(字段1, 字段2, ...) values .....
.
典型范例:
练习:创建以下大气质量表的MySQL执行代码:
- 填充数据——(2)导入外部数据
语法: load data local infile '文件路径.txt' into table 表名 fields terminated by '\t' ignore 1 lines;
典型范例:
检查表数据: 对导入表中的数据一般从 导入内容、导入数据总行数以及表结构 三方面进行检查。
- 检查导入内容: 以表Monthly_Indicator为例,执行MySQL语句:
Select * from Monthly_Indicator;
- 检查导入数据总行数: 以表Monthly_Indicator为例,执行MySQL语句:
Select count(*) from Monthly_Indicator;
- 检查表结构: 以表Monthly_Indicator为例,执行MySQL语句:
Desc Monthly_Indicator;
1.3.1 数据修改
为字段赋值update… set:
语法: update 表名 set 字段名=值
;
典型范例: 使用concat函数在f_name字段前添加"fruit_"信息。
update fruits set f_name = concat('fruit_', f_name);
1.3.1 数据删除
删除记录delete: delete 删除数据表中的数据。
语法: delete from 表名 [where Clause]
,如果省略where的话,则删除表中所有数据记录。
典型范例:
删除f_id为’b5’的数据记录。
delete from fruits where f_id = 'b5';
1.4 数据库查询
1.4.1 Select语句的操作符
算术操作符: +(加号)、-(减号)、(*乘号)和/(除号)
。
比较操作符: =(等号)、>(大于)、<(小于)、<=(小于等于)、>=(大于等于)、!=或<>(不等于)、!>(不大于)
,共9种操作符。
1.4.2 聚合类函数
聚合类函数:
1.4.3 SQL查询
SQL查询:
1.4.4 单表查询
单表查询:
1.4.5 多表查询
多表查询: 指的是将两个以上的数据表通过关键字连接在一起,并从不同表中取不同字段进行查询的方法。
关键字段: 用来连接两表的内容信息能够匹配的上的字段。
- 相连的两表中都需要有关键字段;
- 关键字段中的记录信息能够匹配的上;
- 最理想的连接状态是两表中的两个关键字段都是主键,而且两个主键的值能够一一匹配的上。
语法:
SELECT <select list> FROM <表1> XX join <表2> on 表1.key = 表2.key
通过上述MySQL语句连接两表时,注意事项如下:
XX
代表连接的方向
,可以是inner、left、right等关键字;- 在
连接语句前边
的表是“左表”,在连接语句后边
的表是“右表”。
典型范例:
select 学员信息表.*, 学员成绩表.* from 学员信息表 left join 学员成绩表 on 学员信息表.学号=学员成绩表.学号;
1.4.6 内连接查询(inner join)
内连接: 按照连接条件合并两个表,返回满足条件的行。
语法:
SELECT <select_list> FROM A INNER JOIN B ON A.key = B.key;
典型范例:
1.4.7 左连接查询(left join)
左连接: 结果中除了包括满足连接条件的行外,还包括左表的所有行。
语法:
SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key;
典型范例:
1.4.8 右连接查询(right join)
右连接: 结果中除了包括满足连接条件的行外,还包括右表的所有行。
语法:
SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key;
典型范例:
1.4.9 联合查询(inner join)
联合查询:
- union: 用于合并两个或多个select语句的结果集,并消去表中任何重复行。
典型范例:
用union合并t1与t2表
select t1.* from t1
union
select t2.* from t2;
- union all: 用于合并两个或多个select语句的结果集,保留重复行。
典型范例:
用union all合并t1与t2表
select t1.* from t1
union all
select t2.* from t2;
1.4.10 全连接查询(inner join)
全连接: 全连接会返回两张表中全部的记录,本质上是对两个表中的记录取并集。
全连接没有主附表的区别,按照连接条件可以匹配到的记录会返回匹配后的结果,匹配不到的记录用null进行填充。
语法:
select * from t1 left join t2 on key1 = key2
union
select * from t2 right join t2 on key1 = key2;
1.5 查询操作符与子查询
1.5.1 操作符
AND: 用来联合多个条件进行查询,条件与条件是“和”的意思。
语法:
条件表达式1 AND 条件表达式2 【...AND 条件表达式n】
典型范例:
例:用and操作符查询s_id为101并且f_id为a1的水果记录;
select * from fruits where s_id=101 and f_id='a1';
OR: 用来联合多个条件进行查询,条件与条件是“或”的意思。
语法:
条件表达式1 OR 条件表达式2 【...OR 条件表达式n】
典型范例:
例:用or操作符查询苹果或者橙子的相关记录;
select * from fruits where f_name='apple' or f_name='orange';
IN: 判断某个字段的值是否在指定的集合中,如果在集合中则满足查询条件,如果不在则不满足查询条件。
语法:
【NOT】 IN (元素1, 元素2, ..., 元素n)
※ 其中,NOT是可选参数,加上NOT表示不在集合内满足条件。
典型范例:
例:用in操作符查询苹果和橙子的相关记录;
select * from fruits where f_name in ('apple', 'orange');
例:用 not in操作符查询苹果和橙子之外的相关记录;
select * from fruits where f_name not in ('apple', 'orange');
BETWEEN: 判断某个字段的值是否在指定的范围内,如果在则满足查询条件,如果不在则不满足查询条件。
语法:
【NOT】 BETWEEN 取值1 AND 取值2
※ 其中,NOT是可选参数,加上NOT表示不在集合内满足条件。
典型范例:
例:用between … and操作符查询f_price在10元到20元之间的水果记录;
select * from fruits where f_price between 10 and 20;
LIKE: 用来匹配字符串是否相等,如果字段的值与指定的字符串相匹配,则满足查询条件,如果与指定的字符串不匹配,则不满足查询条件。
语法:
【NOT】 LIKE '字符串'
※ 其中,NOT是可选参数,加上NOT表示`指定的字符串不匹配时满足条件`,字符串参数的值可以是:一个完整的字符串,也可以是包含%或者_的。其中,
(1)%代表任意长度的字符串。例如,b%k表示以字母b开头,以字母k结尾的任意长度的字符串。比如,bak,book,break等都可以。
(2)_只能表示单个字符。例如,b_k表示以字母b开头,以字母k结尾的3个字符。只有上例中的bak是匹配项,而book与break均不满足匹配要求。
典型范例:
例:用like操作符查询所有f_id由b开头且字符长度为2位的水果记录;
select * from fruits where f_id like 'b_';
IS NULL: 用来判断字段的值是否为空值(NULL)。如果字段的值为空值,则满足查询条件,如果字段的值是非空值,则不满足查询条件。
语法:
IS【NOT】 NULL
※ 其中,NOT是可选参数,加上NOT表示字段不是空值时满足条件。
典型范例:
例:用is null操作符查询多有f_name为空的水果记录;
select * from fruits where f_name is null;
DISTINCT: 用来消除重复记录。
语法:
SELECT DISTINCT 字段名
典型范例:
例:查询fruits表中所有不重复的s_id;
select distinct s_id from fruits;
1.5.2 操作符与子查询的组合应用
子查询: 写在()中,把内层查询结果当做外层查询参照的数据表来用。
1.5.3 as重命名与limit限制查询结果行数
as: 可以将表或字段名重新命名为别的名称使用,只在查询中有效。
典型范例:
例:用as将fruits表名重命名为f后使用。
SELECT f.* from fruits as f;
limlit: 查询后只显示limit指定数字的行数结果。
典型范例:
例:显示f_price金额最大的前三名水果记录
SELECT * from fruits
order by f_price desc
limit 3;
1.6 函数
1.6.1 常用的数学函数
常用的数学函数: 主要用于处理数字值。
1.6.2 常用的字符串函数
常用的字符串函数: 主要用于处理字符串值。
1.6.3 常用的日期及时间函数
常用的日期及时间函数: 主要用于处理日期时间型数据。
1.6.4 其他函数
其他函数: 除上述函数之外的一些常用函数。
1.6.5 其他函数——逻辑函数
逻辑函数: 用来对表达式进行判断,根据满足的条件不同,执行相应的流程。
1.6.6 其他函数——开窗函数
开窗函数: 对数据的每一行,都使用与该行相关的行进行计算并返回计算结果,有几条记录执行完返回结果还是几条。
1.7 E-R图
E-R图: 也称【实体-联系图(Entity Relationship Diagram)】,用来描述现实世界的概念模型。
典型范例:
Question:
- 求出购买产品金额最多的前十名顾客;
- 求出购买产品金额最多的前十名顾客的所在城市;
- 求出购买力最强的前十个城市;
- 求出购买力最强的前十个城市以及他们所在的省份;
Answer:
执行的MySQL脚本如下: