MySQL基础及数据分析实践

数据库基础，MySQL数据库发展历史，MySQL特性，SQL基本语法及高级用法，窗口函数，SQL查询及MySQL在数据分析案例中的简单实践

• 数据库的基本概念
  • 什么是数据库
    • 数据库（DataBase ） 是按照数据结构来组织、存储和管理数据的仓库
    • 数据库是一个数据的集合
    • 其本质是一个文件系统，以文件的方式, 将数据保存在电脑上
  • 数据存储方式优缺点
    • 内存
      • 优点：速度快
      • 缺点：容量有限，不能够永久保存, 数据是临时状态的
    • 文件（Excel）
      • 优点：数据是可以永久保存的
      • 缺点：
        • 大量数据使用程序IO 流操作文件 , 不方便；
        • 同一时间多人操作某一个文件也可能出现问题
    • 数据库
      • 优点
        • 海量数据存储，提供不错的查询效率
        • 数据可以永久保存
        • 方便存储和管理数据
        • 使用统一的方式操作数据库
      • 缺点
        • 占用资源（重型武器）
        • 有些数据库需要付费
  • 数据库与数据库管理系统
    • 数据库（DB）：指的是按照一定结构组织的数据集合，由存储数据的文件组成。
    • 数据库管理系统（DBMS)：是一款管理软件。
      • Oracle 公司的 Oracle 和 MySQL
      • IBM 公司的 DB2
      • Microsoft 公司的 Access 和 SQL Server
  • 数据库分类
    • 关系型数据库 (RDB: Relationship DataBase)
      • 关系型数据库可以使用SQL语言/SQL语句操作
      • MysQL、 Oracle、 Microsoft SQL Server、 Access、 PostgreSQL、 DB2等。
    • 非关系型数据库(NoSQL)
      • 常规情况我们是不能使用SQL进行操作的，它不是关系模型
      • 非关系型数据库也被称为 NoSQL数据库
      • NosQL 并不是某个具体数据库，它泛指所有非关系数据库
      • Hbase、 MongoDB. Redis (k/v)、CouchDB等
  • 数据库排行
    • Oracle：有钱的大企业采用，互联网企业之外使用第一。
    • MySQL：互联网高速发展，互联网企业使用第一。
  • 关系型数据库主要对象
    • 表
      • 表是包含数据库中所有数据的数据库对象
      • 由行和列组成（二维表格模型，就类似于Sheet)，用于组织和存储数据。
    • 字段
      • 表中每一列称为一个字段，字段有自己的属性，如字段类型、字段大小等。
      • 字段类型是字段最重要的属性，它决定了字段能够存储哪种数据。
    • 索引
      • 索引是一个单独的、物理的数据库结构。它是依赖于表建立。
      • 在数据库中使用索引，无须对整个表进行扫描，就可以找到需要的数据。
    • 视图
      • 视图是从一张或多张表中导出的表（也称虚拟表），是用户查看数据表中数据的一种方式。
• MySQL数据库
  • MySQL起源与发展
    • MySQL是一个关系型数据库管理系统(RDBMS)，由瑞典MySQLAB公司开发
    • MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中
    • 是最好的 RDBMS (Relational Database Management System， 关系数据库管理系统)应用软件之一。
    • 主要发展历史
      • 1979 年
        • 研发出ISAM 存储引擎算法的前身
      • 1985年
        • ISAM (Indexed Sequential Aecess Method) 存储引擎工具研发
      • 1996年10月
        • MySQL 3.1发布了，没有2.x版本。
      • 1999-2000年
        • Monty.Allan 和 David三人在瑞典创建了 MySQL AB 公司
        • 并且与 Sleepycat合作开发出引入了 BDB 引擎，MySQL 从此开始支持事务处理了。
      • 2000年4月
        • MySQL 对旧的存储引擎ISAM进行了整理，命名为 MyISAM。
      • 2008年1月
        • MySQL AB 公司被 Sun 公司以10亿美金收购，MySQL 数据库进入Sun 时代。
      • 2009年4月
        • Oracle 公司以74 亿美元收购 Sun 公司，
      • 2015年10月
        • MySQL 5.7 首个GA 正式版5.7.9发布。
  • MySQL特性
    • 1. MySQL使用C和C++编写，并使用了多种编译器进行测试，保证了源代码的可移植性。
    • 2. 支持 AlX、 HP-UX、 Linux、 Mac Os、 OpenBsD、 Solaris、 Windows等多种操作系统。
    • 3.为多种编程语言提供了 AP1,包括 C、 C++、 Python、 Java、 Perl、 PHP、 Ruby,.NET等。
    • 4. 支持多线程，充分利用CPU资源。
    • 5. 优化的SQL查询算法，有效地提高查询速度。
    • 6.既可作为单独的应用程序应用在客户端服务器网络环境中，也可作为一个库而嵌入到其他软件中。
    • 7.提供TCP/P、ODBC和DBC等多种数据库连接途径。
    • 8. 提供用于管理、检查、优化数据库操作的管理工具。
    • 9. 支持大型的数据库，可以处理拥有上干万条记录的大型数据库。
    • 10. 支持多种存储引擎(类似于发动机）。
    • 11.MySQL是开源的，所以你不需要支付额外的费用。
    • 12. MySQL使用标准的SQL数据语言形式。
    • 13. MySQL对PHP有很好的支持，PHP是比较流行的Web开发语言。
  • MySQL存储引擎
    • InnoDB:
      • InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。
      • 缺点是读写效率较差，占用的数据空间相对较大。
      • 1.事务处理、回滚、崩溃修复能力和多版本并发控制
      • 2. 自增长AUTO INCREMENT
      • 3. 外键 (FOREIGN KEY)
    • MyISAM：
      • MyISAM的优势在于占用空间小，处理速度快。
      • 缺点是不支持事务的完整性和并发性。
    • Memory:
      • 1.数据全部放在内存中
      • 2.哈希索引
  • MySQL数据类型
    • 数值类型
      • 整数型
        • INT
        • TINYINT
        • BIGINT
      • 小数型
        • FLOAT
        • DOUBLE
    • 字符串类型
      • 普通类型
        • CHAR
        • VARCHAR
      • 可变类型
        • TEXT
        • BLOB
      • 特殊类型
        • SET
        • ENUM
    • 日期和时间类型
      • DATE
      • TIME
      • DATETIME
      • TIMESTAMP
      • YEAR
• 单表与多表查询
  • 排列
    • 单列排序
      • 只按照某一个字段进行排序, 就是单列排序
    • 组合排序
      • 同时对多个字段进行排序，如果第一个字段相同 就按照第二个字段进行排序
  • 聚合函数
    • count(字段）
    • sum(字段）
    • max(字段）
    • min(字段）
    • avg(字段）
  • 分组
    • SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 〔HAVING 条件]；
    • where与having的区别
      • where 进行分组前的过滤
      • where 后面不能写 聚合函数
      • having 是分组后的过滤
      • having后面可以写 聚合函数
  • limit关键字
    • (imit是限制的意思,用于限制返回的查询结果的行数(可以通过limit指定查询多少行数据）
    • limit 语法是 MySql的方言,用来完成分页
    • limit offset, length;
      • offset 起始行数，从0开始记数，如果省略则默认为0.
      • length 返回的行数
      • 关键字可以接受一个 或者两个 为0 或者正整数的参数
  • SQL约束
    • 添加主键约束
      • 字段名 字段类型 primary key
    • 主键的自增
      • AUTO_INCREMENT 表示自动增长（字段类型必须是整数类型）
    • DELETE和TRUNCATE对自增长的影响
      • DELETE
        • 只是删除表中所有数据,对自增没有影响
      • TRUNCATE
        • truncate 是将整个表删除掉,然后创建一个新的表
        • 自增的主键,重新从1开始
    • 非空约束
      • 字段名字段类型 not nul1
    • 唯一约束
      • 字段名 字段值 unique
    • 外键约束
      • FOREIGN KEY 表示外键约束
  • 多表查询
    • 笛卡尔积
      • SELECT 字段名 FROM 表1，表2；
    • 级联删除操作
      • 级联删除 ON DELETE CASCADE
    • 多表查询的分类
      • 内连接查询
        • 隐式内链接
          • SELECT 字段名 FROM 左表，右表 WHERE 连接条件；
        • 显式内连接
          • SELECT 字段名 FROM 左表 IINNER JOIN 右表 ON 条件
      • 外连接查询
        • 左外连接
          • SELECT 字段名 FROM 左表 LEFT LOUTER] JOIN 右表 ON 条件
        • 右外连接
          • 右外连接，使用 RIGHT OUTER JOIN ， OUTER 可以省略
          • SELECT 字段名 FROM 左表 RIGHT [OUTER JJOIN 右表 ON 条件
  • 合并查询
    • SELECT column1 [, column2 〕 FROM table1 [， table2 ] [WHERE condition] UNION SELECT column1 [，column2 ] FROM table1 [, table2 ] [WHERE condition]
  • 子查询
    • SELECT 查询字段 FROM 表 WHERE 字段=（子查询）
    • SELECT 查询字段 FROM（子查询）表别名 WHERE 条件；
    • SELECT 查询字段 FROM 表 WHERE 字段 IN（子查询）；
• SQL语句操作详解
  • SQL通用语法
    • SQL语句可以单行 或者 多行书写，以分号结尾；（Sqlyog中可以不用写分号）
    • 可以使用空格和缩进来增加语句的可读性。
    • MySql中使用SQL不区分大小写，一般关键字大写，数据库名 表名列名 小写。
  • 注释方式
    • --空格 单行注释
    • /* */ 多行注释
    • # 单行注释
  • DDL操作数据库
    • 创建数据库
      • create database 数据库名；
      • create database 数据库名 character set 字符集；
    • 查看/选择数据库
      • use 数据库
      • select database();
      • show databases;
      • show create database 数据库名；
    • 修改数据库
      • alter database 数据库名 character set 字符集；
    • 删除数据库
      • drop database 数据库名
  • DDL操作数据表
    • 创建数据表
      • CREATE TABLE 表名（ 字段名称1 字段类型（长度）， 字段名称2 字段类型 注意 最后一列不要加逗号 )；
    • 查看表
      • show tables;
      • desc 表名；
    • 修改表
      • 修改表名
        • rename table 旧表名 to 新表名；
      • 向表中添加列
        • alter table 表名 add 字段名称 字段类型
      • 修改表中列的数据类型或长度
        • alter table 表名 modify 字段名称 字段类型
      • 修改列名称，关键字 CHANGE
        • alter table 表名 change 旧列名新列名 类型（长度）；
      • 删除列，关键字 DROP
        • alter table 表名 drop 列名；
    • 删除表
      • drop table 表名；
      • drop1 able if exists 表名；
  • DML操作表中数据
    • 插入数据
      • insert into 表名（字段名1，字段名2...） values(字段值1，字段值2...)；
    • 更改数据
      • update 表名 set 列名 = 值 [ where 条件表达式：字段名 = 值
    • 删除数据
      • delete from 表名 [ where 字段名 = 值]
  • DQL查询表中数据
    • 简单查询
      • FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY
    • 条件查询
      • select 列名 from 表名 where 条件表达式
• 窗口函数
  • 什么是窗口函数
    • 窗口的概念非常重要，它可以理解为记录集合，
    • 窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。
    • 对于每条记录都要在此窗口内执行函数，窗口大小都是固定的
    • 不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。
  • 语法
    • 函数名（ [expr]） over(partition by <要分列的组> order by <要排序的列> rowsbetween <数据范围>）
    • A：需要被加工的字段名称
    • B：分组的字段名称
    • C：排序的字段名称
    • D:计算的行数范围
  • 应用
    • 专有窗口函数
      • rank（）
      • dense_rank（）
      • row_number(）
    • 聚合类窗口函数
      • sum(）
      • count（）
      • avg(）
      • max（）
      • min(）
    • 排序函数
      • row number() over(......
      • rank( over(......)
      • dense_rank( over(.......
    • 偏移分析函数
      • lag(...) over(.....
      • lead(...) over(......)
• MySQL索引
  • 什么是索引
    • 索引可以提升查询速度，会影响where条件查询
    • 索引是针对字段的，需要添加到字段上
    • 索引在大量数据场景下效果明显
  • 索引的分类
    • 从索引的存储结构划分：
      • B Tree索引
      • Hash索引
      • fulltext全文索引
      • R Tree索引
    • 从应用层次划分：
      • 主键索引
      • 唯一索引
      • 普通索引
      • 复合索引
    • 从索引的键值（字段）类型划分：
      • 主键索引
      • 辅助索引（二级索引）
    • 从索引数据和内容数据逻辑关系划分：
      • 聚集索引（聚簇索引）
      • 非聚集索引（非聚簇索引）
  • 删除索引
    • ALTER TABLE table_name DROP INDEX index_name；
• MySQL视图
  • 什么是视图
    • 1. 视图是一种虚拟表。
    • 2. 视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
    • 3.向视图提供数据内容的语句为 SELECT 语句， 可以将视图理解为存储起来的 SELECT 语句.
    • 4.视图向用户提供基表数据的另一种表现形式
  • 视图的作用
    • 权限控制时可以使用
    • 简化复杂的多表查询
  • 视图的使用
    • create view 视图名 [column_1ist] as select语句；
  • 视图与表的区别
    • 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
    • 通过视图不能改变表中数据 （一般情况下视图中的数据都是表中的列经过计算得到的结果，不允许更新）
    • 删除视图，表不受影响，而删除表，视图不再起作用
• MySQL数据分析实践
  • 招聘网站岗位数据分析实践
    • 数据清洗：数据导入与缺失数据处理
    • 数据清洗：通过窗口函数实现数据去重
      • select distinct * from v_data_clean_null；
      • select row number () over (partition by company name, job name order by issuedate des） From v_data clean_ null
    • 数据清洗：限定招聘地区与过滤周边岗位
      • with p as (select (case when workarea text 1ike 号北京名， then，北京， when workarea text like ‘%上海%’then 上海， when workarea text like ‘%广州%’then 广州 end) workplace eron v_clean_data_distinct),
    • 分析数据：市场需求量与企业类型分布
    • 分析数据：工作年限、企业类型与平均薪资
      • eselect (case when unit = 1000 then cast (substring index( substring index (providesalary text,！千/月"，1)，"-'"， aS decimal (6, 2)) unit …
    • 分析数据：岗位核心技能点
• MySQL基础及数据分析实践