[MySQL]复杂查询(进阶)-Toy模板网

这篇具有很好参考价值的文章主要介绍了[MySQL]复杂查询(进阶)。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

mysql 复杂查询,MySQL,数据库,sql,mysql

专栏简介 :MySql数据库从入门到进阶.

题目来源:leetcode,牛客,剑指offer.

创作目标:记录学习MySql学习历程

希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长.

学历代表过去,能力代表现在,学习能力代表未来!

1.新增

将查询结果作为values, 插入到指定表中.

语法:

insert into 表1 select * from 表2;

示例:

将学生表1中的数据插入到学生表2中.

mysql> insert into student1 select * from student2;

mysql> select * from student1;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
|    4 | 老六 |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from student2;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
|    4 | 老六 |
+------+------+
4 rows in set (0.00 sec)

Tips:

查询表与插入表的列数和类型要匹配.

所有select查询都可以和该操作组合使用.

2. 聚合查询

之前提到的条件查询都是基于列和列之间的查询, 而聚合查询针对某个列中所有的行来运算.

2.1 聚合函数

函数	说明
count	返回查询到的数据的数量
sum	返回查询到数据的总和 (不是数字没有意义)
avg	返回查询到数据的平均值 (不是数字没有意义)
max	返回查询到数据的最大值 (不是数字没有意义)
min	返回查询到数据的最小值 (不是数字没有意义)

count

语法:

select count(表达式) from 表名;

Tips: count(*)包含null所在的行.

示例:

统计班级有多少学生

mysql 复杂查询,MySQL,数据库,sql,mysql

sum

语法:

select sum(表达式) from 表名

示例:

统计数学总分

mysql 复杂查询,MySQL,数据库,sql,mysql

avg

语法:

select avg(表达式) from student;

示例:

统计平均总分

mysql 复杂查询,MySQL,数据库,sql,mysql

max

语法:

select max(表达式) from 表名;

示例:

返回英语最高分

mysql 复杂查询,MySQL,数据库,sql,mysql

min

语法:

select min(表达式) from 表名;

示例:

返回大于60分以上的数学最低分

mysql 复杂查询,MySQL,数据库,sql,mysql

3. 分组查询(grop by)

语法:

select 字段 from 表名 group by 字段

示例:

mysql 复杂查询,MySQL,数据库,sql,mysql

单个字段分组

查出学生等级的种类:(按等级划分,去除重复的)

select grade from student group by grade;

mysql 复杂查询,MySQL,数据库,sql,mysql

多个字段分组

按名字和等级划分去除重复的同学:

select name from student group by name,grade;

Tip:此时将名字和等级看做是一个整体, 只有名字和等级都相同的才能分成一组, 其中一个不同就不是一组.

mysql 复杂查询,MySQL,数据库,sql,mysql

搭配聚合函数分组

查看表中相同人名的个数:

select name,count(*) from student group by name;

mysql 复杂查询,MySQL,数据库,sql,mysql

4. having

where 与having都是设定筛选条件的语句,有相似点也有不同点.

group by子句进行分组以后,, 分组之前筛选用where, 分组之后筛选用having.

having必须和group by 一起使用.

where之后不能使用聚合函数, having之后可以使用聚合函数.

having可以看做是对where的补充, where筛选出合适的数据having才能进行聚合操作.

字名

作用

where 子句

1)对查询结果进行分组前, 将不符合where条件的行去掉, 即在分组之前过滤数据.

2)where 后面不可以使用聚合函数

3)过滤行

having 子句

1)having 子句的作用是筛选满足条件的组, 即在分组之后过滤数据,.

2)having 后面可以使用聚合函数

3)过滤组

4)支持所有的where操作.

MySQL语句执行顺序:

mysql 复杂查询,MySQL,数据库,sql,mysql

示例:

显示平均工资高于1500的人的平均工资.

select name,avg(salary) from student group by name having avg(salary)>1500;

mysql 复杂查询,MySQL,数据库,sql,mysql

5. 联合查询

实际开发中数据往往来自不同的表, 所以需要多表联合查询, 多表联合查询本质是对多张表的数据取笛卡尔积(也就是全排列).列数是两个表列数之和, 行数是两个表行数之积.因此其中只有一部分数据是有效的, 需要后续相关操作进行筛选.

mysql 复杂查询,MySQL,数据库,sql,mysql

Tips:关联表查询时可以对关联表使用别名, 更加直观.

筛选前:

mysql 复杂查询,MySQL,数据库,sql,mysql 筛选后:

mysql 复杂查询,MySQL,数据库,sql,mysql

Tips:如果两张表中字段重名, 不加成员访问修饰符" . ", 会报错 ambigous(模糊不清的)

mysql 复杂查询,MySQL,数据库,sql,mysql 为了使查询数据更加直观明了, 本文提供以下代码供后续案例使用.

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','张三','zhangsan@qq.com',1),
('00835','李四',null,1),
('00391','王五',null,1),
('00031','老六','xuxian@qq.com',1),
('00054','老师我作业忘带了',null,1),
('51234','芝士雪豹','xuebao@qq.com',2),
('83223','tellme',null,2),
('09527','老外学英文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高等数学'),('英语');

insert into score(score, student_id, course_id) values
-- 张三
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 李四
(60, 2, 1),(59.5, 2, 5),
-- 王五
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 老六
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 老师我作业忘带了
(81, 5, 1),(37, 5, 5),
-- 芝士雪豹
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 老外学英文
(80, 7, 2),(92, 7, 6);

5.1 内连接

内连接就是指结果仅包含符合连接条件的行, 参与连接的两个表都应符合连接条件.

mysql 复杂查询,MySQL,数据库,sql,mysql

语法:

1.from多个表简单明了,但只能实现内连接不能实现外连接.

select * from 表1,表2...;

2.join on可以既可以实现内连接也可以实现外连接.

select * from 表1 join 表2 on...;

select * from 表1 inner join 表2 on...;

示例:

查询老六同学的全科成绩.

查询步骤:

分析需要联合的表(学生表和分数表), 然后计算笛卡尔积.

合法性筛选(分数表id对应学生id).

根据需求加入必要条件.xue

去要的列.

1.计算学生表和分数表的笛卡尔积.(160多行数据, 大部分无效)

mysql 复杂查询,MySQL,数据库,sql,mysql

2.分数表中id对应学生表中id

mysql 复杂查询,MySQL,数据库,sql,mysql

3.学生姓名为老六

mysql 复杂查询,MySQL,数据库,sql,mysql

4.去除不必要的列

mysql 复杂查询,MySQL,数据库,sql,mysql

查询所有同学的成绩, 及同学的个人信息.(学生表,课程表,分数表)

select student.name as 姓名,course.name as 课程名称,score.score from student,course,score where student.id = score.student_id and score.course_id = course.id;

mysql 复杂查询,MySQL,数据库,sql,mysql

5.2 外连接

连接结果不仅包含符合连接条件的行, 同时也包含不符合连接条件的行, 分为左外连接和右外连接.

mysql 复杂查询,MySQL,数据库,sql,mysql

语法:

左外连接:左表有多少数据就显示多少数据, 右边没有的就用null表示

select * from 表1 left join 表2 on 连接条件;

右外连接:右表有多少数据就显示多少数据, 左表没有的就用null表示

select * from 表1 right join 表2 on 条件;

示例:

假如王五同学转专业, 数据表还未同步他的成绩信息.

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
+------+------+
mysql> select * from score;
+------------+-------+
| student_id | score |
+------------+-------+
|          1 |    90 |
|          2 |    89 |
|          4 |    77 |
+------------+-------+
--左外连接
mysql> select * from student left join score on student.id = score.student_id;
+------+------+------------+-------+
| id   | name | student_id | score |
+------+------+------------+-------+
|    1 | 张三 |          1 |    90 |
|    2 | 李四 |          2 |    89 |
|    3 | 王五 |       NULL |  NULL |
+------+------+------------+-------+
--右外连接
mysql> select * from student right join score on student.id = score.student_id;
+------+------+------------+-------+
| id   | name | student_id | score |
+------+------+------------+-------+
|    1 | 张三 |          1 |    90 |
|    2 | 李四 |          2 |    89 |
| NULL | NULL |          4 |    77 |
+------+------+------------+-------+

5.3 自连接

自连接顾名思义就是自己和自己笛卡尔积, 自连接的效果就是把行转成列.不管和where子句还是having子句都是针对不同列之间的行进行操作, 如果只有一个列并且想要行与行之间进行比较, 就必须自连接.

语法:

select * from 表名 as 别名1,表名 as 别名2;

Tips:自连接必须给表起个别名, 否则两个相同的表连接会报错.

mysql 复杂查询,MySQL,数据库,sql,mysql

示例:

查询java成绩高于计算机原理成绩的同学.

由图可以看出, java成绩和计算机原理成绩之间的比较在同一列, 所以需要行转列.

mysql 复杂查询,MySQL,数据库,sql,mysql

自连接之后可以发现, 可以进行列与列之间的比较了, 本题针对的是同一同学不同课程, 所以筛选条件是两表的学生id相同.

mysql> select * from score as s1, score as s2 where s1.student_id = s2.student_id;

mysql 复杂查询,MySQL,数据库,sql,mysql 最后添加条件完成比较.

mysql> select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score>s2.score;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  98.5 |          1 |         3 |  70.5 |          1 |         1 |
|  68.0 |          3 |         3 |  33.0 |          3 |         1 |
+-------+------------+-----------+-------+------------+-----------+

6. 子查询

子查询是指嵌套在其他sql语句中的select语句, 也叫嵌套查询.(本质上就是套娃)

单行子查询:返回一行记录的子查询

示例:

返回与"老六"同学同班的同学.

--分步骤
mysql> select * from student where name = "老六";
+----+-------+------+---------------+------------+
| id | sn    | name | qq_mail       | classes_id |
+----+-------+------+---------------+------------+
|  4 | 00031 | 老六 | xuxian@qq.com |          1 |
+----+-------+------+---------------+------------+


mysql> select * from student where classes_id = 1 and name!="老六";
+----+-------+------------------+-----------------+------------+
| id | sn    | name             | qq_mail         | classes_id |
+----+-------+------------------+-----------------+------------+
|  1 | 09982 | 张三             | zhangsan@qq.com |          1 |
|  2 | 00835 | 李四             | NULL            |          1 |
|  3 | 00391 | 王五             | NULL            |          1 |
|  5 | 00054 | 老师我作业忘带了 | NULL            |          1 |
+----+-------+------------------+-----------------+------------+

--子查询一步完成
mysql> select * from student where classes_id = (select classes_id from student where name = "老六" ) and name!="老六";
+----+-------+------------------+-----------------+------------+
| id | sn    | name             | qq_mail         | classes_id |
+----+-------+------------------+-----------------+------------+
|  1 | 09982 | 张三             | zhangsan@qq.com |          1 |
|  2 | 00835 | 李四             | NULL            |          1 |
|  3 | 00391 | 王五             | NULL            |          1 |
|  5 | 00054 | 老师我作业忘带了 | NULL            |          1 |
+----+-------+------------------+-----------------+------------+

多行子查询:返回多行记录的子查询

示例:

查询语文或英语课程的成绩信息.

1. (not) in 关键字

--分步骤查询
mysql> select * from course where name = "语文" or name = "英语";
+----+------+
| id | name |
+----+------+
|  4 | 语文 |
|  6 | 英语 |
+----+------+

mysql> select * from score where course_id = 4 or course_id = 6;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+

--多行子查询
mysql> select * from score where course_id in (select id from course where name = "语文" or name = "英语");
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+

2. (not) exists 关键字

由于 in 关键字查询结果在内存中, 如果内存中存不下可以考虑使用exists关键字, 但exists关键字执行效率低下, 且可读性差.不如分步查询.

7. 合并查询

在实际应用中, 为了合并多个select的执行结果, 可以使用集合操作符 union, union all, 使用union和union all时, 前后查询结果集中, 字段要一致.

Tips:

union查询结果会去重, union all 可以保留多份.

or 只能联合一个表中的结果, union 可以联合多个表中的.

union

示例:

查询id<3 或者名字为"英文"的课程

mysql> select * from course where id<3 union select * from course where name = "英语";
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中国传统文化 |
|  6 | 英语         |
+----+--------------+

union all

示例:

查询id<3 或者名字为"java"的课程文章来源地址https://www.toymoban.com/news/detail-596939.html

mysql> select * from course where id<3 union all select * from course where name = "Java";
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中国传统文化 |
|  1 | Java         |
+----+--------------+

到了这里，关于[MySQL]复杂查询(进阶)的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！