数据库查询
select
select distinct * from table_name
where
group by
having
order by
limit a,b
必须存在的有:
select
* 可以换成任意的一个或多个字段名称
from
table_name
#注意: 关键字的顺序是固定的不能随意变化
执行的顺序:
from -> where -> group by -> having -> select -> order by -> limit
where
select * from table_name
where
where 后面可以是
1.比较运算符
> < >= <= = !=
2.成员运算符
in not in 后面是一个set
3.逻辑运算符
and or not
not 要放在表达式的前面 and 和 or 放到两个表达式中间
4.模糊查询
like
% 表示 任意个数的任意字符
_ 表示一个任意字符
#
请查询 姓小的 数学小于 80 分 并且 英语 > 20分 的人的 数学成绩
select math,name from stu where math < 80 and english > 20 and name like "小%";
distinct
select distinct * from stu;
# 注意仅当查询结果中所有字段全都相同时 才算重复的记录
指定字段
1.星号表示所有字段
2.手动指定需要查询的字段
3.还可也是四则运算
4.聚合函数
#请查询 英语及格的人的 平均分
select name,(math+english) / 2 平均分 from stu where english >= 60;
统计函数
求和 sum(字段名)
平均数 avg(字段名)
最大值 max(字段名)
最小值 min(字段名)
个数 count(字段名) # 字段名称可以使用* 代替 另外如果字段为空会被忽略
位置:可以用在 字段的位置 或是分组的后面
例如: 查询所有人的平均工资
select avg(salary) from emp
错误案例: 查询工资最高的人的姓名
select name,max(salary) from emp;
#默认显示的第一个name 因为name有很多行 而max(salary) 只有一行 两列的行数不匹配
# 不应该这么写 逻辑错误
select name from emp where salary = max(salary);
# 报错
# 原因: 伪代码
for line in file:
if salary = max(salary) #
#分析 where 读取满足条件的一行 ,max()先要拿到所有数据 才能求最大值,
#这里由于读取没有完成所有无法 求出最大值
#结论 where 后面不能使用聚合函数
group by分组
将一个整体按照某个特征或依据来分为不同的部分
语法:
select xxx from table_name group by 字段名称;
需求:统计每个性别有几个人
select sex,count(*) from emp group by sex;
需求: 查询每个性别有几个 并且显示名字
select name,sex,count(*) from emp group by sex;
# mysql 5.6下 查询的结果是name仅显示该分组下的第一个
# 5.7以上则直接报错 ,5.6也可以手动开启这个功能
# 我们可以用group_concat 将分组之外的字段 做一个拼接 ,但是这是没有意义
# 如果要查询某个性别下的所有信息 直接使用where 即可
#结论: 如果使用group by后,select后的字段,必须是group by 出现的或者是以聚合函数的形式出现
having
用于过滤,但是与where不同的是,having使用在分组之后
# 求出平均工资大于500的部门信息
select dept,avg(salary) from emp group by dept having avg(salary) > 5000;
#查询 部门人数少于3的 部门名称 人员名称 人员个数
select dept,group_concat(name),count(*) from emp group by dept having count(*) < 3;
order
根据某个字段排序
语法:
select * from table_name order by 字段名称;
# 默认是升序
# 改为降序
select * from table_name order by 字段名称 desc;
# 多个字段 第一个相同在按照第二个 asc 表示升序
select * from table_name order by 字段名称1 desc,字段名称2 asc;
案例:
select * from emp order by salary desc,id desc;
limit (主要用于数据分页)
用于限制要显示的记录数量
语法1:
select * from table_name limit 个数;
语法2:
select * from table_name limit 起始位置,个数;
# 查询前三条
select * from emp limit 3;
# 从第三条开始 查询3条 3-5
select * from emp limit 2,3;
# 注意:起始位置 从0开始
# 经典的使用场景:分页显示
1.每一页显示的条数 a = 3
2.明确当前页数 b = 2
3.计算起始位置 c = (b-1) * a
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
# django 提供了现成的分页组件 但是它是先查询所有数据 丢到列表中 再取出数据 这样如果数据量太大可能会有问题
注意:
1.数据库数据计算是从0开始的
2.OFFSET X是跳过X个数据,LIMIT Y是选取Y个数据
3.LIMIT X,Y 中X表示跳过X个数据,读取Y个数据
4.当LIMIT和OFFSET组合使用的时候,LIMIT后面只能有一个参数,表示要取的的数量,OFFSET表示要跳过的数量 。
子查询
将一个查询语句的结果作为另一个查询语句的条件或是数据来源
当我们一次性查不到想要数据时就需要使用子查询
in 关键字子查询
当内层查询 (括号内的) 结果会有多个结果时, 不能使用 = 必须是in ,另外子查询必须只能包含一列数据
1.查询出 平均年龄 大于25的部门编号
select dept_id from emp group by dept_id having avg(age) > 25;
2.再根据编号查询部门的名称
select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);
子查询的思路:
1.要分析 查到最终的数据 到底有哪些步骤
2.根据步骤写出对应的sql语句
3.把上一个步骤的sql语句丢到下一个sql语句中作为条件
exists 关键字子查询
当内层查询 有结果时 外层才会执行
案例:
select * from dept where exists (select * from dept where id = 1);
# 由于内层查询产生了结果 所以 执行了外层查询dept的所有数据
多表查询
内连接
本质上就是笛卡尔积查询
语法:
select * from table1 inner join table2;
案例:
select * from emp inner join dept where dept_id = dept.id;
inner可以省略
select * from emp join dept where dept_id = dept.id;
左外连接查询
左边的表无论是否能够匹配都要完整显示
右边的仅展示匹配上的记录
需求: 要查询所有员工以及其所属的部门信息
select * from emp left join dept on dept_id = dept.id;
注意: 在外连接查询中不能使用where 关键字 必须使用on专门来做表的对应关系
右外连接查询
右边的表无论是否能够匹配都要完整显示
左边的仅展示匹配上的记录
需求: 要查询所有部门以及其对应的员工信息
select * from emp right join dept on dept_id = dept.id;
表之间的关系
外键
外键约束,用于指向另一个表的主键字段
创建表时,需要先创建主表,在创建从表
创建主表
create table dept(id int primary key auto_increment,
mananger char(10),
content char(100)
);
创建表的时候添加外键
# 创建表的时候添加外键
create table student3(id int primary key auto_increment,
name char(10),
gender char(10),
dept_id int,
# 绑定外键,绑定主表的id
foreign key (dept_id) references dept(id)
);
foreign key (dept_id) references dept(id)
# dept_id 表示当前的外键字段
# dept 表示要关联哪个表
# dept(id) id 表示关联的dept表的id 字段
# 删除从表时,要先删除从表,否则会报错
表建好后需要在添加外键或者级联操作
alter table 表名 add constraint [外键名称] foreign key (外键字段) references 关系表名(关系表内字段)
alter table student add constraint class_id foreign key(class_id) references class(id) on update cascade on delete cascade;
foreign key 带来的约束作用
-
在从表中插入一条记录,关联了一个主表中不存在的id,会报错;必须保证从表中外键的值是在主表中存在的
-
插入数据的顺序
先插入主表记录,在插入从表记录
-
从表更新外键时,也必须保证外键的值再主表是存在的
-
删除主表记录前,要保证从表中没有外键关联到要删除的id
必须先删除从表,再删除主表
-
更新主表记录的主键时,要保证从表中没有外键关联到要删除的id
-
必须先创建主表
foreign key 就是用来保证两种表之间的关联是正确的
级联操作 (cascade)
级联操作指的就是,当你操作主表是,自动的操作从表
两种级联操作
-
级联的删除
当删除主表时自动删除从表中相关数据
-
级联更新
当主表的主键更新时,会自动更新关联的从表数据.
# 创建从表,绑定级联关系
create table student(id int primary key auto_increment,
name char(10),tea_id int,
foreign key(tea_id) references teacher(id)
on update cascade
on delete cascade
);
# on update cascade 绑定级联更新
# on deletc cascade 绑定级联删除
# 两个可以单独使用,也可以一起使用,用空格隔开即可
Query OK, 0 rows affected (0.65 sec)
# 添加信息
insert into student values (null,"jack",1),(null,"rose",1),(null,"rayn",2);
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 删除老师表中第一个信息
delete from teacher where id = 1;
Query OK, 1 row affected (0.08 sec)
mysql> select * from teacher;
+----+------+
| id | name |
+----+------+
| 2 | nick |
+----+------+
1 row in set (0.00 sec)
# 学生表中,绑定的对应id的信息也会自动删除
mysql> select * from student;
+----+------+--------+
| id | name | tea_id |
+----+------+--------+
| 3 | rayn | 2 |
+----+------+--------+
1 row in set (0.00 sec)
# 表建好后需要在添加外键或者级联操作,可以使用
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 关系表名(关系表内字段)
alter table student add constraint class_id foreign key(class_id) references class(id) on update cascade on delete cascade;
外键的使用
什么时候使用外键?
表之间存在关联关系
首先就要明确表之间的关系
多对多
如何确定多对多关系
例如: 老师表 和学生表
老师角度:一个老师可以对应多个学生
学生角度:一个学生也可以对应多个老师
如果双方都是一对多的关系,那么两者是多对多关系
处理方式:
- 创建两个主表 如 学员 和老师
- 创建关系表 包含两个字段,分别设置外键, 指向对应的表
- 将两个字段,作为联合主键
建立一个中间表,用于存储关系,至少具备两个字段,分别指向老师和学生的主键,两个字段都是外键,如下:
一定要先建立两个主表,才能建立关系表
#先创建老师表和学生表,再创建关系表
create table teacher(id int primary key auto_increment, name char(10));
create table student(id int primary key auto_increment, name char(10));
create table tea_stu_a(
tea_id int,
stu_id int,
foreign key (tea_id) references teacher(id),
foreign key (stu_id) references student(id),
primary key (tea_id,stu_id)
);
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| tea_id | int(11) | NO | PRI | 0 | |
| stu_id | int(11) | NO | PRI | 0 | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 插入老师信息
insert into teacher values (null,"jerry"),(null,"nick");
# 插入学生信息
insert into student values (null,"jack"),(null,"rose");
# 添加关系表信息
insert into tea_stu_a values (1,1);
insert into tea_stu_a values (1,2);
insert into tea_stu_a values (2,1);
insert into tea_stu_a values (2,2);
+--------+--------+
| tea_id | stu_id |
+--------+--------+
| 1 | 1 |
| 2 | 1 |
| 1 | 2 |
| 2 | 2 |
+--------+--------+
# 如何通过关系表查找信息,比如要找出Jerry老师教过的学生
# 1.通过名字获取Jerry老师的id
# 2.拿着id去关系表中拿到学生的id
# 3.通过学生的id取出学生的信息
select * from student where id = any(
select stu_id from tea_stu_a where tea_id =any(
select id from teacher where name = "jerry")
);
+----+------+
| id | name |
+----+------+
| 1 | jack |
| 2 | rose |
+----+------+
# 在id=后面加any,否则会报错
ERROR 1242 (21000): Subquery returns more than 1 row
一对一关系
站在两个表的角度都是一对一的关系
处理方式
- 确定先后顺序,
- 将先存在的数据作为主表
- 后存在的作为从表
- 使两个表id保持一一对应
- 方法1:从表的id即是主键又是外键
- 方法2:从表的id设置为外键,并保证唯一
# 人员表
create table person(
id int primary key auto_increment,
name char(10),
age int
);
# 详情表
create table person_info(
id int primary key,
height float,
weight float,
foreign key(id) references person(id)
);
#再这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据
#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!
多对一(一对多)
处理方式
如何处理一对多(多对一)? 在老师表中存储 部门id 即多的一方存储 一的一方的id
在多的一方,即teacher表中保存相应部门(一的一方)的编号
#部门:
create table dept(
id int primary key auto_increment,
name char(20),
job char(50),
manager char(10)
);
#老师表:
create table teacher(
id int primary key auto_increment,
name char(20),
gender char(1),
dept_id int,
foreign key(t_id) references teacher(id),
);
聚合函数
聚合函数用于对一组值进行计算并返回一个汇总值,使用聚合函数可以统计记录行数、计算某个字段值的总和以及这些值的最大值、最小值和平均值等。
函数名称 | 功能 |
---|---|
sum | 返回选取的某列值的总和 |
max | 返回选取的某列的最大值 |
min | 返回选取的某列的最小值 |
avg | 返回选取的某列的平均值 |
count | 返回选取的某列或记录的行数 |
窗口函数
作用:
- 解决排名问题,e.g.每个班级按成绩排名
- 解决TOPN问题,e.g.每个班级前两名的学生
语法:
select 窗口函数 over ([partition by 用于分组的列名], [order by 用于排序的列名])
分类:
- 专用窗口函数:rank(),dense_rank(),row_number()
- 汇总函数:max(),min(),count(),sum(),avg()
***注意:***窗口函数是对where后者group by子句处理后的结果进行操作,因此按照SQL语句的运行顺序,窗口函数一般放在select子句中
窗口函数的用法
- 专用窗口函数
rank()函数
#按班级分类,将成绩降序排序
SELECT*,
rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class;
说明
- rank()是排序函数,括号中不需要有参数;
- 通过partition by将班级分类,相当于之前用过的group by子句功能,但是group by子句分类汇总会改变原数据的行数,而用窗口函数自救保持原行数;
- 通过order by将成绩降序排列,与之前学的order by子句用法一样,后边可以升序asc或者降序desc;
总结:
- 窗口函数这里的“窗口”表示范围,可以理解为将原数据划分范围,即分组,然后用函数实现某些目的
- 窗口函数有分组和排序的功能
- 不减少原表的行数
2. 其他专用窗口函数:dense_rank/row_number
- 用法与rank()函数相同
SELECT*,
dense_rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class;
SELECT*,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class;
- 当成绩相同时,会存在并列的情况,主要区别是三个函数如何处理并列情况:
在rank()函数,如果有并列情况,会占用下一个名次的位置,比如,成绩为100的学生有三个并列第一,那么99分的学生是第二名,通过rank()函数,名次是:1,1,1,4;
在dense()函数中,如果有并列的情况,不会占用下一个名词,同用上个例子,名次是:1,1,1,2;
在row_number()函数中,会忽略并列的情况,同用上述例子,名次是:1,2,3,4;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gLbBCnzk-1642166506486)(C:\Users\zjy\Desktop\xiangmu\数据库相关\图片\窗口函数例子)]
经典面试问题-topN问题
- 相关业务问题:
- 每个类别下用户最喜欢的产品是哪个?
- 每个类别下用户点击最多的5个商品是什么?
这类问题就需要分组取最大值,最小值,每组最大的n条记录
- 分组取最大值(用关联子查询)
#查询每个学号成绩是最大的所有信息
SELECT* FROM score AS a
WHERE 成绩=( SELECT MAX(成绩)
FROM score AS b
WHERE a.`学号`=b.`学号`);
- 分组取最小值
#查询每个学号成绩是最小的所有信息
SELECT*
FROM score AS a
WHERE 成绩=( SELECT MIN(成绩)
FROM score AS b
WHERE a.`学号`=b.`学号`);
- 分组取最大N条记录
-- 查询每个学生成绩最高的两个科目
SELECT *
FROM (SELECT*,row_number() over (PARTITION BY 姓名 ORDER BY 成绩 DESC) AS ranking
FROM test1) AS newtest
WHERE ranking<=2;
思路:先构建一个新表,增加上排序这个字段,然后再次进行选择
说明
- 为了不受并列的影响,该题用row_number()
- 注意在子查询后边加别名
- 易错的写法:select*,row_number() over(partition by 姓名 order by 成绩 desc) as ranking from test where ranking<=2;按照sql运行顺序,where后边不能加别名,因为select子句在where子句之后运行
- 涉及到既要分组又要排序的情况,要想到用窗口函数
聚合函数作为窗口函数
作用:聚合函数作为窗口函数,是起到"累加/累计"的效果,比如,就是截止到本行,最大值?最小值是多少
与专用窗口函数的区别:括号中需要有指定列,不能为空
用法:与专用窗口函数相同
#查询成绩的累加
SELECT*, SUM(成绩) over (ORDER BY 成绩 DESC) AS '求和'
FROM class;
案例
#查找单科成绩高于该科目平均成绩的学生名单
法一解题思路:
- 单科成绩,表示需要按科目进行分组,分组有两种:group by子句和窗口函数的partition by;
- 要求平均值,需要通过avg()实现,并且找到大于平均值的学生,那么不能减少行数,则用partition by;
步骤:
-
聚合函数作为窗口函数
select * from (select *, avg(成绩) over (partition by 科目) as 分组平均分 from tests3) as a where 成绩>分组平均分
法二关联子查询
思路
- 单科成绩:需要对每门科目进行分组
- 平均成绩:avg()求每组的平均值
- 学生名单:输出信息中需要有学生姓名
步骤1:求分组平均值
SELECT AVG(成绩) AS 平均值,科目
FROM test3
GROUP BY 科目;
步骤2:比较
SELECT*
FROM test3 AS a
WHERE 成绩>(SELECT AVG(成绩) AS 平均值
FROM test3 AS b
WHERE a.`科目`=b.`科目`);
因为是按照科目分组,所以应该将科目进行关联
窗口函数的移动平均(以平均值为例)
**作用:**通过preceding,following,current row等调整作用范围,基本语法为
ROWS BETWEEN 一个时间点 AND 一个时间点
时间点可以表示为:
- n PRECEDING : 前n行
- n FOLLOWING:后n行
- CURRENT ROW : 当前行
- UNBOUNDED PRECEDING:窗口第一行
- UNBOUNDED FOLLOWING:窗口的最后一行
用法:
#查询前两行到当前行的平均成绩
SELECT *,
AVG(成绩) over (ORDER BY 姓名 ROWS 2 preceding) AS result
FROM test3;
解释:
rows N proceding—N表示在当前行的前N行,比如,N=2,当前行在第4行,那么该平均值是第2行,第3行,第4行,这三行数据的平均值
注意事项
- 窗口函数中的如果省略partition by,则结果不进行分组,则以整个表为范围,也就是窗口为整个表;
- 如果省略order by 则不进行排序;