《mysql篇》--查询(进阶)

目录

将查询结果作为插入数据

聚合查询

聚合函数

count

sum

group by子句

having

联合查询

笛卡尔积

多表查询

join..on实现多表查询

内连接

外连接

自连接

子查询

合并查询

---

将查询结果作为插入数据

Insert into 表2  select * from 表1//将表1的查询数据插入到表2

查询出来的结果集合，要和插入的这个表的类型一致

举例：insert into student2 (select * from student1);

//student1也可以选择部分数据插入只要类型对应就行

聚合查询

聚合函数

mysql在查找时给我们提供了一些基础的处理数据的函数

表达式查询是针对列和列之间进行运算

聚合查询是行和行之间进行运算

count

举例：select count(math) from score;

还可以使用别名

这里是先执行select math from score在针对结果集合进行统计

//count用来表示查询到的数据的个数

不过这时你可能会有疑问 

这个已经可以看到行数了，为啥还要count呢？

7 rows in set是mysql客户端内置的功能，如果你是通过代码来操作mysql服务器，可就没这个功能了，另外count()得到的结果还可以参与各种算术运算，还可以搭配其他sql使用

如果当前查询的列里有null，count(*)和count(具体列)是不同的，如下：

//count(具体列)不会把null算在内

指定具体列时是可以去重的

sum

 //用来计算查询的结果集合的总和

举例：

//如果有null也会自动去除

也可以进行表达式查询：

//查询的逻辑是，select Chinese + English + math把对应的列相加，得到一个临时表，再把这个临时表的行与行相加

//当加和的类型是字符串时也是可以加和的，如果字符串是数字字符串则直接转为对应的dobule类型数字，如果不能转则为0；

group by子句

Select 聚合函数(列名) from 表名 group by 列名(根据这个列分组)

举例：

//如果不用聚合函数每一个组对应的数据默认为，在原表中第一次出现的那个；

//如果针对分组后不使用聚合函数，此时的结果就是查询出每一组中的某个代表数据

//往往还是要搭配聚合函数使用，否则这里查询结果就没有意义

having

还可以配条件进行使用

分组之前的条件

这样就把张三排除在外了

分组之后的条件(having  一般写在  group by 后面)

//把平均值大于10000的岗位排除在外

//就是针对select role,avg(salary) from emp group by role;的查询结果进行条件筛选

同时使用两种条件

//即把张三排除在外，又把大与10000的排除在外

联合查询

笛卡尔积

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积

笛卡尔积：笛卡尔积的计算很简单，就类似于排列组合。笛卡尔积是针对任意两张表之间的运算。

可以看作两个表相乘，在数学符号中，如果A = {a, b} 且 B = {1, 2}，那么A × B的笛卡儿积为{(a, 1), (a, 2), (b, 1), (b, 2)}。

注意：关联查询可以对关联表使用别名。

使用方法：

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

创建两张表

计算笛卡尔积:select * from student,course;

可以看见生成了一个很大的表，这个表就是两张表的笛卡尔积

//计算笛卡尔积时要注意如果两张表数据量都比较大时，就要考虑能不能使用笛卡尔积，因为笛卡尔积的表的数据量是，两张表的乘积如果数据量过大，可能会卡死服务器；

多表查询

我们再创建四张表

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','张三','[e-mail]',1),
('00835','李四',null,1),
('00391','王五',null,1),
('00031','田六','[e-mail]',1),
('00054','赵七',null,1),
('51234','胡八','[e-mail]',2),
('83223','刘九',null,2),
('09527','唐十','[e-mail]',2);insert into course(name) values
('Java'),
('中国传统文化'),
('计算机原理'),
('语文'),
('高阶数学'),
('英文');insert into score2(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);

我们再计算两张表的笛卡尔积时会有很多无效的数据，可以使用where条件进行筛选，筛选出那些有效数据，那么我们基于这个条件就可以进行两张表的查询

比如，我们来查询一下王五的成绩

1.先计算笛卡尔积

select * from student,score2 where student.id = score2.student_id;

//因为可能两张表会有名字一样的列，所以要使用‘.’来明确具体的列

查询结果：

//以上现实的是所有人的成绩

2.筛选出王五

select * from student,score2 where student.id = score2.student_id and name = '王五';

3.筛选出需要的列

上面的表看着还是不太方标，我们可以进一步进行筛选，只要我们想看到的

select student.name,score2.score,score2.course_id from student,score2 where student.id = score2.student_id and name = '王五';

这样就完成了在两个表之间查询

join..on实现多表查询

我们还可以直接使用join..on关键字进行查询

select student.name,score2.score,score2.course_id from student join score2 on student.id = score2.student_id and name = '王五';

//查询结果是一样的

//不过还是更推荐使用第一种

join on还可以一次查询多个只需要在前一个join on后面再加上一个join on就行

比如：

select student.name, course.name, score.score from studentjoin score on student.id = score.student_id 
join course on score.course_id = course.id;

内连接

内连接就是要求查询的两个表中都要有数据，如果有一个没有就不会查询

不管使用where，还是join on都默认是内连接

举例：查询所有同学成绩，以及QQ邮箱

 select name,qq_mail,score from student,score2 where student.id = score2.student_id;

可以看见，因为唐十没有成绩，所以这里并不会显示他的QQ邮箱

//select 列 from 表1 inner join 表2 on 条件

//这里的inner只有在join on中才能添加，意思是表示内连接(可以省略，因为默认就是内连接)

外连接

外连接和内连接正好相反，外连接是尽可能显示较多的数据，即使表1没有数据为了显示表2数据，也会以null的形式显示表1；

//where不能表示外连接，只有join on可以

外连接分为左外连接和右外连接

左外连接

使用left join on，会以左边表的记录为主，尽可能的把左表记录都列出来，对于没有的右边数据，就改成 null

举例：select name,qq_mail,score from student left join score2 on student.id = score2.student_id;

可以看见即使唐十没有成绩，也会显示他的QQ邮箱

//如果是左表没有数据，那么查询结果回合内连接一样

右外连接

和左外连接相反，右外连接是以右表为基准，尽可能多的显示数据，如果左表没有数据则会显示null；(这里就不多做赘述了)

select 列 from 表1 right/left join 表2 on 条件

//这里的felt和right不可省略

自连接

自连接就是，自己和自己笛卡尔积，也就是左表和右表都是同一个表，用来解决一些特殊问题，可以将行转化成列；

//要将表指定别名，不然自连接就会重名，导致报错。

//在自连接的时候，使用多个条件来完成筛选。

举例：查询即选了1号课程又选了五号课程的同学

select * from score2 as s1,score2 as s2 
where s1.student_id = s2.student_id ands1.course_id = 1 and s2.course_id= 5;

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

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

举例：查找张三的同学

//一般我们要先查找张三的班级id

现在将两个sql结合在一起也就是子查询

 select name from student where classes_id = 
(select classes_id from student where name = '张三');

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

//要把‘=’换成in/not in或者exists/not exists

举例：查询java课程成绩，高阶数学课程成绩

select * from score2 where course_id in 
(select id from course where name = 'Java' or name = '高阶数学');

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。

//就是将查询到的两个结果集合并到一起

//使用union 和union all时，前后查询的结果集中，字段需要一致。

union

union会自动对查询到的两个结果集进行去重

举例：查询Chinese大于90 并且 English 大于 80的学生

    select * from score where Chinese > 90unionselect * from score where English > 80;

//相当于 select * from score where Chinese > 90 or English > 80;

union all

union all会显示全部数据不去重

举例：查询Chinese大于90 并且 English 大于 80的学生

select * from score where Chinese > 90union allselect * from score where English > 80;

以上就是博主对mysql查询(进阶)的分享如果有不懂的或者有其他见解的欢迎在下方评论或者私信博主，也希望多多支持博主之后和博客！！🥰🥰