• MySQL表的增查(进阶)


    目录

    1.插入查询结果

    2.查询

    2.1聚合查询

    2.1.1聚合函数

     2.1.2GROUP BY子句

    2.1.3HAVING

    2.2联合查询

     2.2.1内连接

    2.2.2外连接 

    2.2.3自连接 

     2.3子查询

    2.4合并查询 


    1.插入查询结果

    在一张表中插入另一张表的查询结果。

    语法为:

    insert into  表名 (列名,列名……) select   列名,列名……  from 被引用的表名;

    本质:把查询的临时数据转换成了永久数据。 

    注意:

    (1)插入的列名与查询的列名是否一致不影响。

    (2)插入的列数与类型要与查询的列数与类型一致。

     (3)varchar(n)中的n不需要一致。

    案例:创建一张用户表,设计有 name 姓名、 email 邮箱、 sex 性别、 mobile 手机号字段。需要把已有的 学生数据复制进来,可以复制的字段为name
    1. -- 创建用户表
    2. DROP TABLE IF EXISTS test_user;
    3. CREATE TABLE test_user (
    4. id INT primary key auto_increment,
    5. name VARCHAR(20) comment '姓名',
    6. age INT comment '年龄',
    7. email VARCHAR(20) comment '邮箱',
    8. sex varchar(1) comment '性别',
    9. mobile varchar(20) comment '手机号'
    10. );

    演示:

    展示student

    生数据复制进来,可以复制的字段为name


    2.查询

    2.1聚合查询

    2.1.1聚合函数

    常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:

    • COUNT

    (1)select count(id) from test_user; 

     (2)select count(id) as 'id的总数' from test_user;

    (3)select count(*) from test_user;

    (4)select count(email) from test_user;

    对于上述4个的演示:

    (1)(2)

    (3)

    (4)

     

    总结:

    count(*)与 count(列名)

    count(列名):如果查询结果带有null,则null的那一行不会被记录计算。

    但count(*):即使全列为null也会被记录计算。

    以下的聚合函数使用同理,不做过多解释。 

    • SUM

    -- 统计数学成绩总分
    SELECT SUM(math) FROM exam_result;

    -- 不及格 < 60 的总分,没有结果,返回 NULL
    SELECT SUM(math) FROM exam_result WHERE math < 60;

    • AVG

    -- 统计平均总分
    SELECT AVG(chinese + math + english) as 平均总分 FROM exam_result;

    • MAX

    -- 返回英语最高分
    SELECT MAX(english) FROM exam_result;

    • MIN

    -- 返回 > 70 分以上的数学最低分
    SELECT MIN(math) FROM exam_result WHERE math > 70;

    重点:

     使用聚合函数的时候,列与列之间的顺序已经被打散了

    正常来说,一行数据,每个列都是对应的。

    如果查询中包含聚合函数与非聚合函数的列,则每一列的信息各自是各自的

     2.1.2GROUP BY子句

    SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时,SELECT 指定的字段必须是“分组依据字段”,其他字段若想出现在SELECT 中则必须包含在聚合函数中

    语法:

    select 列名,聚合函数(列名)之类……form 表名group by 列名……; 

    接下来我们用一个案例进行说明

    比如:需要按照岗位进行查询与统计。

    • 准备测试表及数据:职员表,有id(主键)、name(姓名)、role(角色)、salary(薪水)
    1. create table emp(
    2. id int primary key auto_increment,
    3. name varchar(20) not null,
    4. role varchar(20) not null,
    5. salary numeric(11,2)
    6. );
    7. insert into emp(name, role, salary) values
    8. ('马云','服务员', 1000.20),
    9. ('马化腾','游戏陪玩', 2000.99),
    10. ('孙悟空','游戏角色', 999.11),
    11. ('猪无能','游戏角色', 333.5),
    12. ('沙和尚','游戏角色', 700.33),
    13. ('隔壁老王','董事长', 12000.66);
    • 查询每个角色的最高工资、最低工资和平均工资

    select role,max(salary),min(salary),avg(salary) from emp group by role;

    演示:

     

    role(角色)有相同部分可以总结,固有上述操作。

    但换作其他就不行,比如name。

    各不相同,故全部展示。

    但如果还把role(角色)分为一组,查询name

    游戏角色只展示了孙悟空。

    2.1.3HAVING

    GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用HAVING

    案例如下:(1)对上述emp表显示平均工资低于1500的角色和它的平均工资

    1. select role,max(salary),min(salary),avg(salary) from emp group by role
    2. having avg(salary)<1500;

    查询结果如下:

    (2)对上述emp表显示工资低于1500的角色的平均工资

    select role,max(salary),min(salary),avg(salary) from emp where  salary<1500 group by role;

    总结:

    (1)分组之前的条件筛选用where。 

    (2)分组之后的条件筛选用having。 


    2.2联合查询

    实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积什么时笛卡尔积呢?请看下图

    解释:

     笛卡尔积得到的是一个更大的表

    (1)新表的列数是之前两个表之和。

    (2)新表的行数是之前两个表之积。

    我们需要注意的是:关联查询可以对关联表使用别名

    首先我们先初始化一些数据,方便后续查询

    1. create table classes (
    2. id int primary key auto_increment,
    3. name varchar(20),
    4. desc1 varchar(100)
    5. );
    6. create table student (
    7. id int primary key auto_increment,
    8. sn varchar(20),
    9. name varchar(20),
    10. qq_mail varchar(20),
    11. classes_id int
    12. );
    13. create table course(
    14. id int primary key auto_increment,
    15. name varchar(20)
    16. );
    17. create table score(
    18. score decimal(3,1),
    19. student_id int,
    20. course_id int
    21. );

    接下来我们插入一些数据:

    1. insert into classes(name, desc1) values
    2. ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
    3. ('中文系2019级3班','学习了中国传统文学'),
    4. ('自动化2019级5班','学习了机械自动化');
    5. insert into student(sn, name, qq_mail, classes_id) values
    6. ('09982','黑旋风李逵','xuanfeng@qq.com',1),
    7. ('00835','菩提老祖',null,1),
    8. ('00391','白素贞',null,1),
    9. ('00031','许仙','xuxian@qq.com',1),
    10. ('00054','不想毕业',null,1),
    11. ('51234','好好说话','say@qq.com',2),
    12. ('83223','tellme',null,2),
    13. ('09527','老外学中文','foreigner@qq.com',2);
    14. insert into course(name) values
    15. ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
    16. insert into score(score, student_id, course_id) values
    17. -- 黑旋风李逵
    18. (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
    19. -- 菩提老祖
    20. (60, 2, 1),(59.5, 2, 5),
    21. -- 白素贞
    22. (33, 3, 1),(68, 3, 3),(99, 3, 5),
    23. -- 许仙
    24. (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
    25. -- 不想毕业
    26. (81, 5, 1),(37, 5, 5),
    27. -- 好好说话
    28. (56, 6, 2),(43, 6, 4),(79, 6, 6),
    29. -- tellme
    30. (80, 7, 2),(92, 7, 6);

    展示:


        

     

     

     2.2.1内连接

    语法:

    select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
    select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

    依旧采用案例来讲解:

    • 查询“许仙”同学的 成绩
    1. select sco.score from student stu inner join score sco on stu.id=sco.student_id
    2. and stu.name='许仙';
    3. -- 或者
    4. select sco.score from student stu, score sco where stu.id=sco.student_id and
    5. stu.name='许仙';

    查询结果为: 

    • 查询所有同学的总成绩,及同学的个人信息
    1. -- 成绩表对学生表是多对1关系,查询总成绩是根据成绩表的同学id来进行分组的
    2. SELECT
    3. stu.sn,
    4. stu.NAME,
    5. stu.qq_mail,
    6. sum(sco.score)
    7. FROM
    8. student stu
    9. JOIN score sco ON stu.id = sco.student_id
    10. GROUP BY
    11. sco.student_id;

    查询结果为: 

    • 查询所有同学的成绩,及同学的个人信息
    1. -- 学生表、成绩表、课程表3张表关联查询
    2. SELECT
    3. stu.id,
    4. stu.sn,
    5. stu.NAME,
    6. stu.qq_mail,
    7. sco.score,
    8. sco.course_id,
    9. cou.NAME
    10. FROM
    11. student stu
    12. JOIN score sco ON stu.id = sco.student_id
    13. JOIN course cou ON sco.course_id = cou.id
    14. ORDER BY
    15. stu.id;

    查询结果为: 

     重要:

    查询出来的都是有成绩的同学, 老外学中文 同学 没有显示
    select * from student stu join score sco on stu .id =sco .student_id ;

    查询结果为: 

     

     对于内连接来说,得到的结果,必须是在两个表中都存在的数据。

    2.2.2外连接 

    外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。

     

    解释(以左外连接为例) :

    左外连接,就是以左表为主,保证左侧的表的每一个记录都体现在最终表里。如果这个记录在右表中没有匹配,就把对应的列自动填充为空。

    语法:

    -- 左外连接,表1完全显示
    select 字段名 from 表名1 left join 表名2 on 连接条件;

    -- 右外连接,表2完全显示
    select 字段 from 表名1 right join 表名2 on 连接条件;

    案例:

     查询出来的都是有成绩的同学

    进行左连接

    select * from student stu left join score sco on stu.id=sco.student_id;

    查询结果为: 

    2.2.3自连接 

    自连接是指在同一张表连接自身进行查询

    想要进行条件查询,条件一定是列与列之间的。但有的时候就想指定行与行之间的条件,此时可以通过自连接把行关系转换成列关系。

    举个例子吧

    查询显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

    1. -- 先查询“计算机原理”和“Java”课程的id
    2. select id,name from course where name='Java' or name='计算机原理';
    3. -- 也可以使用join on 语句来进行自连接查询
    4. SELECT
    5. s1.*
    6. FROM
    7. score s1
    8. JOIN score s2 ON s1.student_id = s2.student_id
    9. AND s1.score < s2.score
    10. AND s1.course_id = 1
    11. AND s2.course_id = 3;

    -- 再查询成绩表中,“计算机原理”成绩比“Java”成绩 好的信息

    以上查询只显示了成绩信息,并且是分布执行的。要显示学生及成绩信息,并在一条语句显示:

    就可以使用以下内连接来进行查询:

    1. SELECT
    2. stu.*,
    3. s1.score Java,
    4. s2.score 计算机原理
    5. FROM
    6. score s1
    7. JOIN score s2 ON s1.student_id = s2.student_id
    8. JOIN student stu ON s1.student_id = stu.id
    9. JOIN course c1 ON s1.course_id = c1.id
    10. JOIN course c2 ON s2.course_id = c2.id
    11. AND s1.score < s2.score
    12. AND c1.NAME = 'Java'
    13. AND c2.NAME = '计算机原理';

    查询结果为: 


     2.3子查询

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

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

    查询与“不想毕业” 同学的同班同学

    1. select * from student where classes_id=(select classes_id from student where
    2. name='不想毕业');

    查询结果为: 


    2.4合并查询 

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

    • union

    该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。

    案例如下:

    查询id小于3,或者名字为“英文”的课程

    1. select * from course where id<3
    2. union
    3. select * from course where name='英文';
    4. -- 或者使用or来实现
    5. select * from course where id<3 or name='英文';

    查询结果为: 

     注意:

    union左右两侧sql可以查询两个不同的表,但用or不行。

    • union all

    该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。

     


    以上为我个人的小分享,如有问题,欢迎讨论!!! 

    都看到这了,不如关注一下,给个免费的赞 

  • 相关阅读:
    vue中的h函数与JSX语法
    SPI : ServiceLoader如何打破双亲委派
    Hadoop基础学习笔记
    基于matlab的一级倒立摆直线运动稳定性控制仿真
    Internet协议栈 TCP/IP模型 物理层、链路层、网络层、传输层、应用层的作用
    Android 多点触控
    【使用Cpolar将Tomcat网页传输到公共互联网上】
    高忆管理:突破22万亿!五大保险巨头总资产创历史新高
    Java 面试八股文 —— SSM 框架常见面试题
    基于Springboot的新闻资讯发布系统设计与实现(源码+lw+部署文档+讲解等)
  • 原文地址:https://blog.csdn.net/WHabc2002/article/details/134426800