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中索引的使用
学习主线:
-
索引的介绍
-
索引的分类
-
索引的基本操作
-
通过案例展现索引的价值
-
索引使用时的注意事项
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:对每个分区内部按照什么排序
开窗函数大体分为两种:
-
能够作为开窗函数的聚合函数:sum、avg、count、max、min
-
专用开窗函数:==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;