【数据分析师 Level 1 】9.MySQL简介

 

主流的关系型数据库

• Oracle ：运行稳定，可移植性高，功能齐全，性能超群，适用于大型企业

• DB2：速度快，可靠性好，适用于海量数据，恢复性极强，适用于大中型企业

• MySQL：开源、体积小、速度快，适用于中小型企业

• SQL server：全面高效、界面友好易操作，但是不跨平台，适用于中小型企业

MySQL发展历程

最早是由瑞典MySQL AB公司开发，仅供内部使用，2000年基于GPL协议开放源码，2008年MySQL AB公司被Sun 公司收购，2009年Sun公司又被Oracle公司收购，有了Oracle公司的技术支持，MySQL在2010年以后发布了多个版本，在各方面加强了企业级的特性。

MySQL 下载与安装

数据库、数据库管理系统和SQL之间的关系

• 数据库是长期存储在计算机内，有组织的，统一管理的相关数据的集合

• 数据库管理系统是用于管理数据库的软件，它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性

• SQL是一种结构化查询语言（Structure Query Language）它是国际化标准组织（ISO）采纳的标准数据库语言

SQL 分类

• 数据定义语言DDL

  • 用来创建、修改、删除数据中的各种对象，包括数据库、表、视图等等

  • 用于创建，修改，删除数据库中的各种对象（表、视图、索引等），常用命令有CREATE，ALTER，DROP

  • 数据库的增删选查

    • 查看数据库：show databases;

      • 系统数据库：sys 库：MySQL5.7 增加了 sys 系统数据库，通过这个库可以快速的了解系统的元数据信息。information_schema 库：提供了访问数据库元数据的方式。

      • 用户数据库

    • 创建数据库：create database 数据库名称; 数据库名称不能与SQL关键字相同，也不能重复

    • 查看创建好的数据库：show create database 数据库名称; alter database test character set utf8;

    • 选择进入数据库：use 数据库名称; use test;

    • 删除数据库：drop database 数据库名称; drop database test;

    • 数据库基本结构：

    • 

      • 数据库：组织、存储和管理相关数据的集合，同一个数据库管理系统中数据库名必须唯一

      • 表：由固定列数和任意行数构成的数据集，同一个数据库中表名必须唯一

      • 列：一个字段，同一个表中列名必须唯一

      • 行：一条记录

  • 数据表的增删改查

    • 创建表：create table 表名(字段名1 数据类型1 [约束条件1],字段名2 数据类型2 约束条件2);

      • 建表之前要先选择进入数据库：use test;

      • 建表时可以不指定约束条件，但是必须指定字段的数据类型

      • 表名不能与SQL关键字相同，同一个数据库下的表名不能重复

    • 查看创建好的表： show create table 表名; show create table emp;

    • 查看当前数据库中所有表： show tables;

    • 查看表结构：desc 表名; desc emp;

    • 修改表：修改数据库中已经存在的数据表的结构

      • 修改表名：alter table 原表名 rename 新表名;

      • 修改字段名：alter table 表名 change 原字段名 新字段名 数据类型[ 约束条件];

      • 修改字段类型：alter table 表名 modify 字段名 数据类型[ 约束条件];

      • 添加字段：alter table 表名 add 新字段名 数据类型 约束条件;

      • 修改字段的排列位置

        • 将某个字段改为第一列：alter table 表名 modify 字段名 数据类型[ 约束条件] first;

        • 将某个字段改到另一个字段后面：alter table 表名 modify 要排序的字段名 数据类型[ 约束条件] after 参照字段;

      • 删除字段：alter table 表名 drop 字段名;

      • 删除数据表： drop table 表名; drop table emp;

  • 数据类型

    • 数值型

    • 

    • 字符串型

    • 

    • 日期时间型

    • 

    • 常用数据类型

      • 字符串类型和日期时间类型都需要用引号括起来

      • int：4字节整数值

      • float：单精度浮点型，float(5,2)表示最多5位数，其中有两位小数

      • char：固定长度字符串型，如char(10)，‘abc ’

      • varchar：可变长度字符串型，如varchar(10)，‘abc’

      • text：长文本字符串型

      • date：日期型，‘yyyy-MM-dd’

      • time：时间型，‘hh:mm:ss’

      • datetime：日期时间型，‘yyyy-MM-dd hh:mm:ss’

  • 数据完整性

    • 主体完整性

      • 主键约束

        • 特点

          • 能够唯一地标识表中的一条记录

          • 非空不重复，要求主键列的数据必须是唯一的，并且不允许为空

          • 可以设置单字段主键，也可以设置多字段联合主键

        • 添加方法

          • create table 表名(字段1 数据类型 primary key,字段2 数据类型[,…]);

          • create table 表名(字段1 数据类型,字段2 数据类型[,…], primary key(主键字段));

          • create table 表名(字段1 数据类型,字段2 数据类型[,…], primary key(主键字段1, 主键字段2[,…]));

        • 删除方法

          • alter table 表名 drop primary key;

      • 唯一约束

        • 特点

          • 要求该列的值必须是唯一的

          • 允许为空，但只能出现一个空值

        • 添加方法

          • create table 表名(字段1 数据类型,字段2 数据类型 unique[,…]);

        • 删除方法

          • alter table 表名 drop index 唯一约束名;

          • 如果单个字段没有指定唯一约束名，则默认的唯一约束名为字段名。

          • 如果是多个字段组合为唯一约束时候，默认的唯一约束名为第一个字段的名称。

          • 如果指定了约束名，则删除的时候写约束名。

      • 自增字段

        • 特点

          • 指定字段的数据自动增长

          • 配合主键一起使用，并且只适用于整数类型

          • 默认从1开始，每增加一条记录，该字段的值会增加1

        • 添加方法

          • create table 表名(字段1 数据类型 primary key auto_increment,字段2 数据类型[,…]);

        • 设置自增字段的初始值

          • 设置自增字段的初始值：Alter table 表名 auto_increment=n;

        • 删除方法

          • alter table 表名 modify 字段名 数据类型[ 约束条件];

    • 域完整性

      • 非空约束

        • 特点

          • 字段的值不能为空

        • 添加方法

          • create table 表名(字段1 数据类型,字段2 数据类型 not null[,…]);

        • 删除方法

          • alter table 表名 modify 字段名 数据类型[ 约束条件];

      • 默认约束

        • 特点

          • 指定某个字段的默认值，如果新插入一条记录时没有为默认约束字段赋值，那么系统就会自动为这个字段赋值为默认约束设定的值。

        • 添加方法

          • create table 表名(字段1 数据类型1,字段2 数据类型2 default 默认值[ ,…]);

        • 删除方法

          • alter table 表名 modify 字段名 数据类型[ 约束条件] ;

    • 参照完整性

      • 外键约束

        • 特点

          • 某一表中某字段的值依赖于另一张表中某字段的值

          • 主键所在的表为主表，外键所在的表为从表

          • 每一个外键值必须与另一个表中的主键值相对应

        • 添加方法

          • 先创建主键所在的主表，再创建外键所在的从表

          • creat table 表名(字段名1 数据类型1,字段名2 数据类型2,……, foreign key(字段名) references 主表(主键字段));

        • 删除方法

          • alter table 表名 drop foreign key 外键约束名;

          • 先删除从表，再删除主表。

          • 先删除外键约束，再删除表。

    • 用户自定义完整性

      • 检查约束

        • 特点：指定需要检查的限定条件

  • 视图

    • 视图是存储在数据库中的虚拟表，视图中不保存数据，内部封装了一条SELECT语句,数据来源于查询的一个或多个基本表。

    • 创建视图：create view 视图名 as select 查询语句;

    • 查看视图结构：desc 视图名;

    • 查询视图中的记录：select 字段名1[,字段名2,...] from 视图名[ where 筛选条件];

    • 修改视图：create [or replace or alter] view 视图名 as select 查询语句;

    • 删除视图：drop view 视图名;

  • 索引

    • 索引的意义

      • 用于快速找出在某个字段中有特定值的行。

      • 如果不使用索引，MySQL必须从第一条记录开始检索表中的每一条记录，直到找出相关的行，那么表越大，查询数据所花费的时间就越多。

    • 索引的优缺点

      • 优点

        • 通过索引对数据进行检索，大大提高了数据的查询效率

      • 缺点

        • 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

        • 索引也需要占空间的，创建的索引太多，索引文件也会占用数据库的存储空间

        • 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度

    • 索引的分类

      • 普通索引

        • 一个索引只包含一个字段

      • 唯一索引

        • 索引字段的取值不能重复，可以有空值，但空值也只能出现一次

      • 主键索引

        • 索引字段的取值不能为空，也不能重复

      • 组合索引

        • 一个索引包含多个字段

        • 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

      • 全文索引

        • 仅限MyISAM引擎，只能在CHAR,VARCHAR,TEXT类型的字段上使用

        • 通过关键字符，就能找到该字段所属的记录行

      • 空间索引

        • 仅限MyISAM引擎

        • 对空间数据类型的字段建立的索引，且要求索引字段的取值不能为空

    • 索引的操作

      • 创建索引：create index 索引名 on 表名(字段名);

      • 自动创建索引

        • 在表中定义了主键约束时，会自动创建一个对应的主键索引

        • 在表中定义了外键约束时，会自动创建一个对应的普通索引

        • 在表中定义了唯一约束时，会自动创建一个对应的唯一索引

      • 查看索引：show index from 表名;

      • 删除索引：drop index 索引名 on 表名;

      • 创建索引的原则

        • 需要创建索引的情况

          • 主外键和唯一约束的字段自动创建索引

          • 频繁作为查询条件的字段应该创建索引

          • 查询中排序的字段应该创建索引

          • 查询中分组或统计的字段应该创建索引

        • 不需要创建索引的情况

          • 表中记录太少不需要创建索引

          • 需要频繁增删改的字段不适合创建索引

          • where子句中用不到的字段不需要创建索引

          • 重复值较多的字段不需要创建索引

• 数据操作语言DML

  • 用来对数据库中的表进行更改操作，比如增加数据、删除数据、修改数据等

  • 添加数据

  • 字段名与字段值的数据类型、个数、顺序必须一一对应

    • 指定字段名插入：insert into 表名(字段1[,字段2,…]) values(字段值1[,字段值2,…]), (字段值1[,字段值2,…])[,…]);

    • 字段列的数量和值的数量必须相同，且列和值要对应上

    • 不指定字段名插入：insert into 表名 values(字段值1[,字段值2,…]), (字段值1[,字段值2,…])[,…]);

    • 需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同

    • 批量导入：load data infile ‘文件路径.csv’into table 表名 fields terminated by ‘,' ignore 1 lines;

    • 路径中不能有中文，并且要用反斜杠

  • 更新数据

    • update 表名 set 字段名1=字段值1, 字段名2=字段值2,…;

    • 设置数据库安全权限： set sqlsafeupdates=0;

  • 删除数据

    • delete from 表名[ where 条件字段=条件值];

    • truncate 表名;

    • delete和truncate区别

      • delete可以添加删除条件删除表中部分数据，truncate只能删除表中全部数据。

      • delete删除表中数据保留表结构，truncate直接把表删除(drop)然后再创建一张新表，执行速度比delete快。

• 数据查询语言DQL

  • 用来查询数据库表中的记录，由select 组成的查询语句块

  • 用来查询数据库表中的记录，基本结构：SELECT <字段名> FROM <表或视图名> WHERE <查询条件>

    • 单表查询

      • 虚拟结果集

        • select语句执行后服务器会按照要求检索表中数据，并将检索结果发送到客户端，这个以表的形式展示出来的临时结果集，

        • 它是存放在内存中，不是在磁盘中的，执行其他操作之后这个结果集就没有了，所以它是临时存在的虚拟结果集，而不是一个真实的表。

      • 全表查询

        • 全表查询：select * from 表名;

      • 查询指定列

        • 查询指定列：select 字段1[,字段2,…] from 表名;

      • 查询不重复的数据

        • 查询不重复的数据：select distinct 字段名 from 表名;

      • 设置别名

        • 当我们的列名或表名很长的时候，可以使用as关键字给一个列或者表添加别名，增强SQL语句的可读性。

        • 设置别名：select 字段名 as 列别名 from 原表名 [as ]表别名;

      • 条件查询

        • 条件查询：select 字段1[,字段2,…] from 表名 where 筛选条件;

        • 运算符

          • 算术运算符

          • 

          • 比较运算符

          • 

          • 逻辑运算符

          • 

      • 空值查询

        • 空值查询：select 字段1[,字段2,…] from 表名 where 空值字段 is[ not] null;

      • 模糊查询

        • 模糊查询：select 字段1[,字段2,…] from 表名 where 字符串字段[ not] like 通配符;

          • 百分号（%）通配符：匹配0个或多个字符

          • 下划线（_）通配符：匹配一个字符

      • 聚合运算

        • 聚合运算：将多行数据进行聚集计算为一行

          • 聚合函数会对null以外的数据进行聚合运算

          • 

      • 分组查询

      • 分组后筛选

      • 查询结果排序

      • 限制查询结果数量

      • select语句书写顺序

      • select语句执行顺序

    • 多表查询

      • 连接查询

        • 表之间关系

          • 一对一

          • 

          • 一对多

          • 

          • 多对多

          • 

        • 连接方式

          • 内连接

            • 按照连接条件连接两个表，返回满足条件的行

            • 

            • select 字段1[,…] from 表1[ inner] join 表2 on 连接条件;

            • 

          • 左连接

            • 结果中除了包括满足连接条件的行外，还包括左表的所有行

            • 

            • select 字段1[,…] from 表1 left join 表2 on 连接条件;

            • 

          • 右连接

            • 结果中除了包括满足连接条件的行外，还包括右表的所有行

            • 

            • select 字段1[,…] from 表1 right join 表2 on 连接条件;

            • 

          • 笛卡尔积

            • 假设集合A={a,b}，集合B={1,2,3}，则两个集合的笛卡尔积为{(a,1),(a,2),(a,3),(b,1),(b,2),(b,3)} 

            • select 字段1[,…] from 表1,表2[,…];

            • 

            • 消除笛卡尔积：select 字段1[,…] from 表1,表2[,…] where 筛选条件;

            • 逐行判断，相等的留下，不相等的排除

            • 

            • 消除笛卡尔积

              • 

          • 自连接

        • 连接条件

          • 等值连接

            • 通过设置表别名，将同一张表虚拟为多张表进行连接

            • 表1.key=表2.key

          • 不等值连接

            • 表1.key 比较 表2.key

      • 合并查询

        • 把多条select语句的查询结果合并为一个结果集

        • 

        • 被合并的结果集的列数、顺序和数据类型必须完全一致

        • union去重：select 字段1[,字段2,…] from 表名 union select 字段1[,字段2,…] from 表名; select * from t1 union select * from t2;

        • 

        • union all不去重： select 字段1[,字段2,…] from 表名 union all select 字段1[,字段2,…] from 表名; select * from t1 union all select * from t2;

    • 子查询

      • 一个select语句中包含另一个或多个完整的select语句

      • 子查询分类

        • 标量子查询：返回的结果是一个数据(单行单列)

        • 行子查询：返回的结果是一行(单行多列)

        • 列子查询：返回的结果是一列(多行单列)

        • 表子查询：返回的结果是一张临时表(多行多列)

      • 子查询常用操作符

      • 

    • 常用函数

      • 字符串函数

      • 

      • 数字函数

      • 

      • 日期时间函数

      • 

      • 开窗函数

      • 

    • 查询结果保存

      • 保存到一个新的表：create table 新表名 as select 查询语句;

      • 保存为外部文件：select 查询语句 into outfile 保存路径;

• 数据控制语言DCL

  • 用来定义数据库的访问权限和安全级别

    用来定义数据库访问权限和安全级别，常用命令：GRANT，REVOKE

SQL 书写要求

SQL语句可以单行或多行书写，用分号结尾。

SQL关键字用空格分隔，也可以用缩进来增强语句的可读性。

SQL对大小写不敏感。

用#或-- 单行注释，用/* */多行注释，注释语句不可执行。

MySQL 客户端常用工具

• MySQLd

  • MySQL 服务核心进程。在 linux 环境下，启动时会被 MySQLd_safe 调用。只有它成功启动， 数据库实例才能正常运作。

• MySQLd_safe

  • MySQL 实例的安全启动脚本，在 unix 和 netware 中推荐使用 MySQLd_safe 来启动

  • MySQLd 服务器。MySQLd_safe 增加了一些安全特性，例如当出现错误时重启服务器并向错误日志文件写入运行时间信息。它是 MySQLd 的守护进程。

• 命令行（CMD）

• 图形化工具（IDE）

  • MySQL workbench

  • Navicat

  • SQLyog

修改密码

存储引擎

• InnoDB：事务型数据库的首选引擎，也是MySQL的默认事务型引擎，支持事务安全表（ACID），支持行锁定和外键

  • 使用场景：由于其支持事务处理、外键、支持崩溃修复能力和并发控制。

  • 如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。

  • 如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MyISAM：在Web，数据仓储和其他应用环境下最常使用的存储引擎之一，拥有较高的插入、查询速度，但是不支持事务和外键

  • 使用场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

• MEMORY：将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问

  • 使用场景：如果需要该数据库中一个用于查询的临时表。

• ARCHIVE：仅仅支持最基本的插入和查询，它使用zilib压缩率，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用

  • 使用场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。