mysql查询

数据库查询

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子句中

窗口函数的用法

1. 专用窗口函数

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问题

1. 相关业务问题：

• 每个类别下用户最喜欢的产品是哪个？
• 每个类别下用户点击最多的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 则不进行排序；