前言:
🌟🌟本期讲解关于MySQL表增删查改进阶篇,希望能帮到屏幕前的你。
🌈上期博客在这里:http://t.csdnimg.cn/8SiWF
🌈感兴趣的小伙伴看一看小编主页:http://t.csdnimg.cn/8SiWF
目录
📚️1.引言
Hello!!!家人们,小编上期期讲解了关于增删查改的进阶之约束条件,关于数据库表的增删查改有了一定的认知,小编这期就将深入讲解另一部分内容,即关于数据库增删查改的进阶操作之查询进阶,大家准备好了吗~~~🥳🥳🥳;
且听小编进行讲解,包你学会!!!
📚️2.查询搭配插入
SQL执行语句:insert into 表名1 select * from 表名2
当然这里的意思是将表2的所有值插入到表1当中去;
代码如下:
mysql> insert into student values(1,'孙悟空'),(2,'猪八戒'),(3,'沙悟净');
mysql> create table student2(student2_id int,name varchar(10));
mysql> insert into student2 select * from student;
此时就将表student的所有值插入到了表student2当中去;
注意:此时的被插入的表和插入的表的列数,和类型都要匹配,否则会报错;
📚️3.聚合查询
由于我们之前所说的表达式查询是针对表达式进行列和列的操作,所以在行和行之间我们就要用到聚合查询,所以聚合查询是针对行和行之间的运算;
3.1 聚合函数
1.count函数
SQL执行语句:select count(*) from 表名
代码实例如下:
mysql> insert into student values(1,'孙悟空'),(2,'猪八戒'),(3,'沙悟净');
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
注意:这里的count计算的是有多少行,并且也会把null算进行数;但是当指定列的时候,如果存在null则null不算入行数中;并且在指定列的时候可以通过distinct进行去重操作;
2.sum函数
SQL执行语句:select sum(int类型的列名) from 表名
代码实例如下:
mysql> select sum(student_id) from student;
+-----------------+
| sum(student_id) |
+-----------------+
| 6 |
+-----------------+
注意:这里的sum代表的是每行指定列的数值相加,这里会自动排除掉NULL,若这里的指定的列不为整型,那么输出为0,并发出警告;
如下:
mysql> select sum(name) from student;
+-----------+
| sum(name) |
+-----------+
| 0 |
+-----------+
1 row in set, 4 warnings (0.00 sec)
这时候,进入警告查看,可以了解到MySQL会自动将指定列转为double类型的数据,如果转失败了,就会报错,即发出警告;
3.avg函数,max函数,min函数
这里的三个函数的操作基本和上述的写法基本一致
SQL执行语句:select avg(整数类型的列) from 表名;
SQL执行语句:select max(整数类型的列) from 表名;
SQL执行语句:select min(整数类型的列) from 表名;
注意:这里的函数分别代表求平均值,最大值,最小值;这里的类型要求都为数字类型,不要搞错了,否则这里的函数就没有意义了;
3.2GROUP BY子句
这里的group by代表的是分组查询,一般来搭配聚合查询函数来使用;
SQL执行语句:select 列名,列名 from 表名 group by 列名;
代码实现如下:
mysql> select * from emp;
+------+-----------------+--------------+--------+
| id | name | role | salary |
+------+-----------------+--------------+--------+
| 1 | 孙悟空 | 程序猿 | 10000 |
| 2 | 猪八戒 | 程序猿 | 11000 |
| 3 | 沙悟净 | 程序猿 | 14000 |
| 4 | 唐三藏 | 老板 | 100000 |
| 5 | 白龙马 | 产品经理 | 9000 |
| 6 | 女儿国国王 | 产品经理 | 10000 |
+------+-----------------+--------------+--------+
mysql> select role,avg(salary) from emp group by role;
+--------------+-------------+
| role | avg(salary) |
+--------------+-------------+
| 产品经理 | 9500.0000 |
| 程序猿 | 11666.6667 |
| 老板 | 100000.0000 |
+--------------+-------------+
首先前面是小编创建的一张表,通过分组查询,会将指定的分组列进行分组,让后通过聚合函数将对应的角色的薪水求平均值;
注意:
📍若直接求平均值,由于老板的薪水很高,会造成很大误差,分完组后,就不会产生误差;
📍若指定了分组的列,那么在进行查找时也应该写进去,方便阅读;
📍分组查询也可以搭配where条件函数,只不过要分清条件在分组之前还是分组之后,若条件在 分组之前那么就可以使用where
3.3having条件函数
SQL执行语句:select 列名,列名 from 表名 group by 列名 having 条件
上述说到若条件在分组之后,就要用到having条件描述:
mysql> select role,avg(salary) from emp group by role having avg(salary)<20000;
+--------------+-------------+
| role | avg(salary) |
+--------------+-------------+
| 产品经理 | 9500.0000 |
| 程序猿 | 11666.6667 |
+--------------+-------------+
注意:这里就是当进行分组查询,并求平均薪资完成后,想要得到薪水小于两万的角色,此时就可以在分组查询完成后,进行having函数描述条件;
📚️4.联合查询
4.1内连接
所谓联合查询就是多表查询,作用于非一个表数据,通过建立笛卡尔集,然后进行筛选得出有效数据,得到最终的表;
例如:
得到的笛卡尔积就是:
但是其中也包括了无效的数据(即红色的×),这就是笛卡尔积,就是每条数据的组合;
SQL执行语句:selecct * from 表名,表名;
小编创建的表如下:
班级表:
学生表:
分数表:
课程表:
那么此时我们要查询许仙同学的成绩:那么就是学生表+成绩表;
1.创建笛卡尔积:
mysql> select * from student,score;
数据过多就不展示了;
2建立链接条件,筛选有效数据:
这里的链接条件就是,两个表的学生id要相同:
mysql> select * from student,score where student.id=score.student_id;
注意:通过where来建立链接条件,这里的id区分是通过表名.id的方式;
3.结合需求,添加条件:
通过需求我们只需要名字为‘许仙’即可:
mysql> select * from student,score where student.id=score.student_id and student.name='许仙';
+------+------+--------+---------------+------------+-------+------------+------------+
| id | sn | name | qq_email | classes_id | score | student_id | classes_id |
+------+------+--------+---------------+------------+-------+------------+------------+
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 67 | 4 | 1 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 23 | 4 | 3 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 56 | 4 | 5 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 72 | 4 | 6 |
+------+------+--------+---------------+------------+-------+------------+------------+
此时大体的需求就已经完成了;
4.精简表:
删除不需要的列名,达到表的简化:
mysql> select name,score from student,score where student.id=score.student_id and student.name='许仙';
+--------+-------+
| name | score |
+--------+-------+
| 许仙 | 67 |
| 许仙 | 23 |
| 许仙 | 56 |
| 许仙 | 72 |
+--------+-------+
此时我们就要通过之前讲过的指定列查询进行简化表的列的数量;
5.笛卡尔积总结:
在两张或以上的表进行操作时,先通过两张表建立笛卡尔积,然后通过特殊条件进行1条件链接,然后通过需求设置条件进行简化;
当然不同的条件对应不同的条件函数,这里的笛卡尔积还可以使用group by来进行分组,和聚合函数来完成特殊的需求。
4.2外连接
当表中的数据一一对应的时候内外连接一致,但是存在不对应的数据的时候就会有区别了:
两个表如下:
mysql> select * from stu;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from sco;
+------+-------+
| id | score |
+------+-------+
| 1 | 44 |
| 2 | 77 |
| 4 | 88 |
+------+-------+
这里的id为4的数据是不对应的;
在上述的内连接中也可以写作如下:
mysql> select * from stu,sco where stu.id=sco.id;
+------+--------+------+-------+
| id | name | id | score |
+------+--------+------+-------+
| 1 | 张三 | 1 | 44 |
| 2 | 李四 | 2 | 77 |
+------+--------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from stu inner join sco on stu.id=sco.id;
+------+--------+------+-------+
| id | name | id | score |
+------+--------+------+-------+
| 1 | 张三 | 1 | 44 |
| 2 | 李四 | 2 | 77 |
+------+--------+------+-------+
此时两种写法,代表的意思都是一致的;下面的写法是将“逗号改为join,where改为on,并且在join前加入inner(可以省却)”
1.左外连接
SQL执行语句:select * from 表名 left join 表名 on 连接条件;
代码实例如下:
mysql> select * from stu left join sco on stu.id=sco.id;
+------+--------+------+-------+
| id | name | id | score |
+------+--------+------+-------+
| 1 | 张三 | 1 | 44 |
| 2 | 李四 | 2 | 77 |
| 3 | 王五 | NULL | NULL |
+------+--------+------+-------+
注意:所谓的左外连接就是,将前一个表的所有行展示,在另一个表中没有对应的数据,那么就用NULL来代替;
2.右外连接
SQL执行语句:select * from 表名 right join 表名 on 连接条件;
代码实例如下:
mysql> select * from stu right join sco on stu.id=sco.id;
+------+--------+------+-------+
| id | name | id | score |
+------+--------+------+-------+
| 1 | 张三 | 1 | 44 |
| 2 | 李四 | 2 | 77 |
| NULL | NULL | 4 | 88 |
+------+--------+------+-------+
注意:所谓的右外连接就是,将后一个表的所有行展示,在另一个表(前一个表)中没有对应的数据,那么就用NULL来代替;
4.3自连接
所谓的自连接就是自己和自己建立笛卡尔积,主要是通过需求进行自己表之间行和行之间的操作
SQL执行语句:select * from 表名,表名(同一个)
例如通过上述分数表中,查询谋和成绩比另一个成绩高的表时;
建立笛卡尔积:
select * from score as s1,score as s2
注意:由于分不清哪个score,那么就需要进行别名的操作,否则就会发生报错;
然后合上述一样,建立条件连接,根据需求设置条件简化表;
4.4子查询
所谓的自查询就是套娃,但是小编不建议这么去编写MySQL数据库;
1.单行子查询
代码实例:
假如我们要查询许仙同学的同班同学:
第一步:找到许仙同学的班级id;
第二部:根据班级id找到其他同学;
mysql> select classes_id from student where name='许仙';
+------------+
| classes_id |
+------------+
| 1 |
+------------+
mysql> select name from student where classes_id=1;
+-----------------+
| name |
+-----------------+
| 黑旋风李逵 |
| 菩提老祖 |
| 白素贞 |
| 许仙 |
| 不想毕业 |
+-----------------+
那么此时就找到了许仙同学的同班同学;
而子查询实例:
mysql> select name from student where classes_id=(select classes_id from student where name='许仙');
注意:单行子查询就是返回一行数据;这里就是将id=1中的1换成了上述通过许仙查询班级id的过程;
2.多行子查询
查询语文和英文的成绩信息;
返回两个课程id,然后再成绩表中找到对应的成绩信息;
代码如下:
mysql> select id from course where name='语文' or name='英文';
+------+
| id |
+------+
| 4 |
| 6 |
+------+
2 rows in set (0.00 sec)
mysql> select * from score where score.classes_id in(4,6);
两者结合后就为多行子查询;
mysql> select * from score where score.classes_id in(select id from course where name='语文' or name='英文');
注意:在多行子查询中在嵌套时要用到‘in’这个关键词;
4.5合并查询
所谓合并查询,就是通过union操作符,用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
1.在一个表中
SQL执行语句:select * from 表名 where 条件 union select * from 同一个表名where 条件;
代码实例:
mysql> select * from course where id<3 union select * from course where name='英文';
+------+--------------------+
| id | name |
+------+--------------------+
| 1 | Java |
| 2 | 中国传统文化 |
| 6 | 英文 |
+------+--------------------+
这里表示,取名字是英文或者课程id小于3的数据;
2.在两个表中
SQL执行语句:select * from 表名 union select * from 表名;
代码实例:
mysql> select * from stu union select * from stu2;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+------+--------+
这里就是两个表的所有数据;
注意:合并查询时,会将两个表相同的数据去重,若不想去重就使用union all操作符;
📚️5.总结
💬💬小编本期讲解了数据库表的增删查改的进阶之查询,包括聚合查询的查询函数,和group by子句和针对两个表之间的联合查询包括内连接,外连接,自连接,子查询,合并查询都做了详细的解说,并且都附上了代码供小伙伴们参考。
🌅🌅🌅~~~~最后希望与诸君共勉,共同进步!!!
💪💪💪以上就是本期内容了, 感兴趣的话,就关注小编吧。
😊😊 期待你的关注~~~