2021-09-18大数据学习日志——MySQL基础（下）

07_多表操作

阶段目标:掌握高级查询的用法--多表查询, 子查询

学习路径:

• 数据表之间的关系

• 多表查询语句

• 子查询语句

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系。

7.1 多表关系

7.1.1 表与表之间的关系

一对多关系:

常见实例：客户和订单，分类和商品，部门和员工

一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键

7.1.2 外键约束

现在有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

category是分类表 products是商品表 分类与商品属于一对多的关系(举例:生活用品是分类,包含毛巾,牙刷,洗发水等商品)

可以在多的一方 products 中添加一个外键 category_id 指向(用来记录)少的一方(主表)的主键

此时“分类表category”称为：主表，”cid” 我们称为主键。

“商品表products”称为：从表，”category_id” 称为外键。

通过主表的主键和从表的外键来描述主外键关系，呈现一对多关系。

外键特点：

• 从表外键的值是对主表主键的引用

• 从表外键类型，必须与主表主键类型一致

通过修改表添加外键约束

alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称] 通常是用来删除外键约束的，一般建议”_fk”结尾

删除外键语法:

alter table 从表 drop foreign key 外键名称;

在数据表中使用外键目的：保证数据完整性

7.1.3 一对多练习

（1）分析

category分类表，为一方，也就是主表，必须提供主键cid

products商品表，为多方，也就是从表，必须提供外键category_id

（2） 实现：分类和商品

sql语句:

# 创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100)  #分类名称
);

# 创建商品表
CREATE TABLE products (
  pid varchar(32) PRIMARY KEY  ,
  name VARCHAR(40) ,
  price DOUBLE ,
  category_id varchar(32)
);

# 添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);

（3）操作

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

7.2 多表查询

7.2.1 前期准备

# 分类表
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
# 商品表
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),    #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

7.2.2 多表查询

• 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

  语法：select * from A,B;

• 内连接查询(使用的关键字 inner join -- inner可以省略)

  • 隐式内连接：select * from A,B where 条件;

  • 显示内连接：select * from A inner join B on 条件;

• 外连接查询(使用的关键字 outer join -- outer可以省略)

  • 左外连接：left outer join

    select * from A left outer join B on 条件;

  • 右外连接：right outer join

    select * from A right outer join B on 条件;

示例:

#1.查询哪些分类的商品已经上架
#隐式内连接
SELECT DISTINCT c.cname FROM category c , products p 
 WHERE c.cid = p.category_id AND p.flag = '1';

#内连接
SELECT DISTINCT c.cname FROM category c 
 INNER JOIN products p ON c.cid = p.category_id 
 WHERE p.flag = '1';

#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(category_id) FROM category c 
 LEFT OUTER JOIN products p 
  ON c.cid = p.category_id 
 GROUP BY cname;

7.3 子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）

select ....查询字段 ... from ... 表.. where ... 查询条件

示例:

#子查询：查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.* FROM products p , category c 
 WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询
##作为查询条件
SELECT * FROM products p 
 WHERE p.category_id = 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品'
 );
 ##作为另一张表
 SELECT * FROM products p , 
   (SELECT * FROM category WHERE cname='化妆品') c 
  WHERE p.category_id = c.cid;

#查询“化妆品”和“家电”两个分类上架商品详情
SELECT * FROM products p 
 WHERE p.category_id in 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品' or c.name='家电'
 );

---

08_MySql索引

阶段目标:掌握MySql中索引的使用

学习主线:

1. 索引的介绍

2. 索引的分类

3. 索引的基本操作

4. 通过案例展现索引的价值

5. 索引使用时的注意事项

8.1 概述

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

大白话:索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

在数据库中查询表中数据，必须从第一条记录开始读完整个表，直到找出相关的行

出现的问题：表越大，查询数据所花费的时间就越多（从第1条记录查找整张表）

针对表中数据过多，又希望提升查询的效率，此时可以使用MySQL提供的：索引

索引：

• 索引是建立在表中的某一个或某多个列上

• 当进行select查询，且所查询的列上有建立索引时，数据库会通过索引快速定位到数据行位置，从而快捷的查找到数据，避免了检索表中所有数据

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如:

在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率

8.2 索引的分类

8.2.1 根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。

（1）B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行

（2）哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行

【注】哈希索引不能按照范围进行查找

8.2.2 根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 3 类：

（1）普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

（2）唯一性索引

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引

（3）主键索引

主键索引是一种唯一性索引特殊形式，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。

8.3 索引操作

8.3.1 普通索引

普通索引是最基本的索引类型, 目的就是加快对数据的访问速度

（1）创建索引

方式1:直接创建

create index 索引名称 on 表名(字段名[(length)]);
-- 字段名,如果是char, varchar类型, length可以小于字段的实际长度
-- 字段名,如果是blob和text类型, 必须指定length

方式2:修改表结构(添加索引)

alter table 表名 add index 索引名称(列名);

方式3:创建表的时候直接指定

create table 表名(
	列名1 int not null,
    列名2 varchar(16) not null,
    index 索引名称(列名3[(length)])
);

（2） 查询索引

查看索引:

-- 查看表中所有索引
show index from 表名;

查看数据库下所有索引：

-- 查看数据库所有索引
select * from  mysql.`innodb_index_stats` a where a.`database_name` = '数据库名';

（3） 删除索引

方式1:直接删除索引

drop index [索引名称] on 表名;

方式2:通过修改表结构删除索引8.

alter table 表名 drop index 索引名称;

8.3.2 唯一索引

（1）创建索引

方式1:直接创建

create unique index 索引名称 on 表名(列名[(length)]);
-- 字段名, 如果是char varcahr类型, length可以小于字段实际长度
-- 字段名, 如果是blob, text类型,必须指定长度

方式2:修改表结构(添加索引)

alter table 表名 add unique 索引名称(列名);

方式3:创建表的时候直接指定

create table 表名(
	列名1 int not null,
    列名2 varchar(16) not null,
    unique 索引名称(列名[(length)])
);

（2）删除索引

方式1:直接删除索引

drop index [索引名称] on 表名;

方式2,通过修改表结构删除索引

alter table 表名 drop index 索引名称;

8.3.3 主键索引

主键索引的操作就是主键约束的操作，之前已经讲过，在这里不做描述。

8.4 索引示例

测试:索引是否可以提高查询效率

第一步:创建表

-- 创建表 
create table t_indextable 
(
  id  int(11) ,
  name  varchar(30) 
);

第二步:向表中插入100W数据

-- 编写SQL程序，插入100W条数据 
create procedure proc_insert()
begin
declare i int; -- 声明一个变量
  set i=1; -- 初始化变量的值
  -- 循环100W次
  while i <=1000000 do
    -- 插入数据
    INSERT INTO t_indextable VALUES	(i, CONCAT('AA',i));
    COMMIT;
    set i = i+1; -- 修改循环条件的值
  end while; 
end

-- 调用SQL程序
call proc_insert()

大概需要10分钟左右 插入数据 耐心等待!

查询测试

select * from t_indextable where name = 'AA998495';

可以看到 查询一条数据 耗时387毫秒(1000毫秒等于1秒)

第三步:创建索引

create index index_testindex on t_indextable(name);

第四步:再次执行查询 查看执行时间

select * from t_indextable where name = 'AA998495';

可以看到查询速度有了很大的提升

8.5 索引的使用原则和注意事项

创建索引时，需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。建立索引会占用磁盘空间的索引文件

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

• 除了数据表占数据空间之外，每一个索引还要占一定的物理空间

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护(因为更新表时，MySQL不仅要保存数据，还要保存索引文件)，这样就降低了数据的维护速度

• 对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求 

---

 09_MySQL开窗函数

阶段目标:掌握MySQL中开窗函数的应用

学习主线:

• 什么是开窗

• 开窗函数的语法结构

• 三种开窗函数的介绍

• 开窗函数的案例

MySql在8.0的版本增加了对开窗函数的支持，现在可以在MySql使用开窗函数了

9.1 概述

准备数据：

-- 创建员工表
create table employee 
(
empid int, -- 员工编号
ename varchar(20) , -- 员工编号
deptid int , -- 员工姓名
salary decimal(10,2) -- 员工薪水
);
-- 插入测试数据
insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'关羽',10,4500.00);
insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);
insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);
insert into employee values(10,'赵云',10,4500.00);
insert into employee values(11,'张飞',10,3500.00);

需求1: 统计每个部门的最高薪资的人员信息

select deptid as '部门',max(salary) as '最高薪资' from employee group by deptid;

需求2: 统计每个部门薪资最高的前两名

select deptid,salary from employee group by deptid order by salary desc limit 2;

按上面的方法查询会报错：

如果有分组，那么select后面的列要么是分组的列，要么是聚合函数得到的列

原因：

分组过程中，一组只会返回一条

order by和limit是对整个数据进行操作的，而不是对分组内的数据进行操作的

解决方案：使用开窗函数

什么是开窗函数?

开窗函数用于对一组值进行操作, 不需要使用Group by子句对数据进行分组, 就能够对每组组内进行分区排序（开窗函数可以求TOPN）

开窗函数的语法结构:

-- 分区并排序 
<开窗函数> over ([PARTITION] by <列清单>] Order by <排序用列清单>)
-- partition by：按照哪一列进行分区
-- order by：对每个分区内部按照什么排序

开窗函数大体分为两种:

1. 能够作为开窗函数的聚合函数：sum、avg、count、max、min

2. 专用开窗函数：==rank, dense_rank, row_number==

9.2 开窗函数介绍

row_number()，rank()，dense_rank()这三个函数都是用于返回结果集的分组内每行的排名

row_number：不管排名是否有相同的，都按照顺序1，2，3…..n

rank：排名相同的名次一样，同一排名有几个，后面排名就会跳过几次

dense_rank：排名相同的名次一样，且后面名次不跳跃

小结：

row_number：对每个分区内部做编号

rank：对每个分区内部做编号，如果排序的值相同，排名也相同，但是会留下空位

dense_rank：对每个分区内部做编号，如果排序的值相同，排名也相同，但是不会留下空位

9.3 案例

示例：对employee表中按照deptid进行分组，并对每一组的员工按照薪资进行排名

select empid, ename, deptid, salary,
       row_number() over (partition by deptid order by salary desc ) as row_number1,
       rank() over (partition by deptid order by salary desc) as rank2,
       dense_rank() over (partition by deptid order by salary desc) as dense_rank3
from employee;