【MySQL表的增删改查】（进阶二）

最新推荐文章于 2024-09-26 15:45:00 发布
最新推荐文章于 2024-09-26 15:45:00 发布
阅读量597 收藏 32
点赞数 26
分类专栏： 数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2201_75724363/article/details/137055389
版权

目录

📒查询

🥂聚合查询

前面的表达式查询,处理列与列的运算
而处理行与行之间的运算 需要用到聚合查询

🐳聚合函数

聚合函数中,如果遇到了NULL 就会跳过

函数说明
COUNT([DISTINCT] expr)返回查询到的数据的 数量
SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义

案例:

INSERT INTO exam_result (id,name, chinese, math, english) VALUES
 (1,'唐三藏', 67, 98, 56),
 (2,'孙悟空', 87.5, 78, 77),
 (3,'猪悟能', 88, 98.5, 90),
 (4,'曹孟德', 82, 84, 67),
 (5,'刘玄德', 55.5, 85, 45),
 (6,'孙权', 70, 73, 78.5),
 (7,'宋公明', 75, 65, 30);

在这里插入图片描述

  • count
select count(*) from exam_result;
-- 此处就得到了结果的行数
-- 聚合函数的函数名和括号之间，不能有空格
-- 括号里面也可以写具体的列
-- 先查询，再聚合

在这里插入图片描述

注意点：
针对某一列进行查询的时候，就是在看这一列有多少个 非 NULL 的结果
而 select 则不关注 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) 平均总分 FROM exam_result;

在这里插入图片描述

  • max
-- 返回英语最高分
SELECT MAX(english) FROM exam_result;
  • min
-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

🍬 GROUP BY子句

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

-- 语法
select column1, sum(column2), .. from table group by column1,column3;

案例：

创建一张新表

  • 准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）
create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);

插入数据：

insert into emp(name, role, salary) values
('王','服务员', 1000.20),
('李','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
-- role岗位 可以按岗位进行分组
  • 查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

在这里插入图片描述

🍾HAVING(注意跟WHERE的使用)

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

  • 显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

🏀联合查询(多表查询)（重要）

实际开发中往往数据来自不同的表，所以需要多表联合查询。
列数：就是原来两个表列数之和
行数：就是原来两个表行数之积
多表查询是对多张表的数据取笛卡尔积：

在这里插入图片描述

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

-- 创建班级表
create table class(
       classId int,
       className varchar(20)
);

-- 创建学生表
create table student(
       id int,
       name varchar(20),
       classId int
);

-- 插入数据
insert into class values (101,'java101'),(102,'java102');

insert into student values 
       (1,'张三',101),
       (2,'李四',101),
       (3,'王五',102),
       (4,'赵六',102);

在这里插入图片描述

-- 进行笛卡尔积操作
select * from student,class;

在这里插入图片描述

笛卡尔积是全排列过程，在尝试穷举出所有的可能性
存在一部分，无效数据 / 无意义的数据

  • 修改
select * from student,class where student.classId = class.classId;

在这里插入图片描述

  • 初始化测试数据
-- 创建表
CREATE TABLE classes (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 `desc` VARCHAR(100)
);
-- 插入信息
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

-- 创建表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);
-- 插入信息
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);

-- 创建表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
   id INT PRIMARY KEY auto_increment,
   name VARCHAR(20)
);
-- 插入信息
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

--创建表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
   id INT PRIMARY KEY auto_increment,
 score DECIMAL(3, 1),
   student_id int,
 course_id int,
 FOREIGN KEY (student_id) REFERENCES student(id),
 FOREIGN KEY (course_id) REFERENCES course(id)
);
-- 插入信息
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),
-- tellme
(80, 7, 2),(92, 7, 6);

🐋内连接

在这里插入图片描述

语法：

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

案例：

  • 查询“许仙”同学的 成绩
 select student.name,score.score 
 from 
        student,score 
 where 
        student.id = score.student_id 
 and 
        student.name = '许仙';
        
-- 还有另一种写法 join on
 select student.name,score.score 
 from 
        student join score on 
        student.id = score.student_id 
 and
        name = '许仙';
  • 查询所有同学的总成绩，及同学的个人信息（学生名字）：
    在这里插入图片描述
select name,sum(score) 
from 
       student,score 
where 
       student.id = score.student_id 
group by
       student.name;

  • 查询所有同学的成绩，及同学的个人信息：
select student.name,course.name,score 
from 
       student,course,score 
where 
       student.id = score.student_id 
and 
       course.id= score.course_id;

-- 或者用 join on 
select student.name,course.name,score.score 
from 
       student join score on 
       student.id = score.student_id 
       join course on 
       course.id = score.course_id;

在这里插入图片描述

🍎外连接

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

语法：

-- 左外连接，表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;
-- 对应的右外连接为：
select * from score sco right join student stu on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
FROM
 student stu
 LEFT JOIN score sco ON stu.id = sco.student_id
 LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
 stu.id;

🏐自连接

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

  • 案例：
  • 显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
SELECT
 s1.* 
FROM
 score s1,
 score s2 
WHERE
 s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;
-- 也可以使用join on 语句来进行自连接查询
SELECT
 s1.* 
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;
  • 以上查询只显示了成绩信息，并且是分布执行的。要显示学生及成绩信息，并在一条语句显示：
SELECT
 stu.*,
 s1.score Java,
 s2.score 计算机原理
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 JOIN student stu ON s1.student_id = stu.id
 JOIN course c1 ON s1.course_id = c1.id
 JOIN course c2 ON s2.course_id = c2.id
 AND s1.score < s2.score
 AND c1.NAME = 'Java'
 AND c2.NAME = '计算机原理';
  • 也可以只显示一个同学的信息
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;

在这里插入图片描述

✨子查询

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

  • 单行子查询：返回一行记录的子查询
    查询与“不想毕业” 同学的同班同学：
select * 
from 
       student 
where 
       classes_id=(
select classes_id from student where name='不想毕业'
);
  • 多行子查询：返回多行记录的子查询
  • 案例：查询“语文”或“英文”课程的成绩信息
  • [NOT] IN关键字：
-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!= '语文' and name!='英文');
  • 可以使用多列包含：
-- 插入重复的分数：score, student_id, course_id列重复
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);
-- 查询重复的分数
SELECT
 * 
FROM
 score 
WHERE
 ( score, student_id, course_id ) IN ( SELECT score, student_id, 
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( 0 ) > 1 );
  • [NOT] EXISTS关键字：
-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

🍾合并查询

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

  • union
    该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
    案例：查询id小于3，或者名字为“英文”的课程：
select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

注意点：
在这里插入图片描述

明志学编程-
关注
专栏目录
【MySQL】增删改查进阶
qq_61862008的博客
11-04 2329
mysql增删改查进阶
Mysql表增删改查进阶—入门+实战面面俱到
元宝的博客
11-11 1516
Mysql增删改查(进阶) 1.约束类型 NOT NULL - 指示某列不能存储 NULL 值。 UNIQUE - 保证某列的每行必须有唯一的值。 DEFAULT - 规定没有给列赋值时的默认值。 PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。 FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。 CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对
MySQL表的增删改查(进阶)
qq_63983125的博客
11-15 3773
MySQL表的增删改查(进阶)
MySQL 增删改查进阶 — 数据库约束
超烦豆先森
11-20 642
MySQL 数据库增删改查进阶操作 - 数据库约束
MySQL增删改查进阶 — 表的设计
超烦豆先森
11-19 602
MySQL数据库的增删改查进阶 - 表的设计
mysql 增删改查 （进阶）
2401_83177222的博客
09-04 1283
创建表时，可以指定某列不为空：为id的列 不能为null，否则报错使用指定列插入时，如果没有修改默认值，默认值就是null；顾名思义：被unique修饰的列只能出现一次，否则报错1️⃣通过这个约束，来指定某个键为主键：不能为null不能重复. 是not null 和uniqu 的结合2️⃣整数类型的主键，常配搭自增长auto_increment来使用自增主键 id默认为null，意思让数据库自行发配一个id涉及到两个表之间的约束。
MySQL数据库增删改查进阶（联合查询聚合查询）
qq_61903414的博客
08-10 690
笛卡尔积就是两张表的记录进行排列组合，排列组合之后就会形成一张新表，我们查询时对这张新表加入一系列的连接条件与其他条件就可以得到我们想要得到的数据，笛卡尔积之后的行数就是两个表行数相乘，列数就是相加。自连接就是自己与自己连接，顾名思义 就是自己与自己进行笛卡儿积，然后再根据连接条件和其他条件进行筛选，比如我们要想知道学科id为1 的成绩比学科id为2成绩高的人的信息，就可以使用自链接。这样我们就可以看到同学的成绩信息，现在如果要查询这位同学的总成绩时，只需要使用前面聚合查询里的分组查询即可。...
MySQL数据库基础（MySQL表的增删改查--进阶）
Josvin
05-27 1044
MySQL表的增删改查（进阶） 1. 数据库约束 约束：数据库针对数据进行一系列的校验，如果发现插入的数据不符合约束中的描述的校验规则，就会插入失败从而保证数据的正确性。 1.1 约束类型 NOT NULL - 指示某列不能存储 NULL 值。 UNIQUE - 保证某列的每行必须有唯一的值。 DEFAULT -规定没有给列赋值时的默认值。 PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。
MySQL增删改查(万字详解 基础+进阶)
qq_54469537的博客
11-28 7177
万字详解,一文掌握mysql关于增删改查等常用知识点.
mysql增删改表查询统计数量_MySQL表的增删改查（进阶）下
weixin_35740588的博客
02-02 1564
4.查询4.1聚合查询4.1.1聚合函数在MySQL中常见的统计可以使用聚合函数来实现，常见的聚合函数有：函数说明count[dintinct] expr返回查询到的数量(行数)sum[dintinct] expr返回查询到的数据的总和avg[dintinct] expr返回查询到的数据的平均值max[dintinct] expr返回查询到的数据的最大值min[dintinct] expr返回查询...
【MySQL系列】 MySQL表的增删改查（进阶）
m0_67402774的博客
07-31 553
老规矩~~为了本篇博客的学习，先把MySQL数据库里面的表全部都删除掉~~只留下自己创建的java数据库（自带的数据库肯定是不动的了）~~
mysql增删改查进阶语句
06-06
好的，以下是 MySQL 的增删改查进阶语句： 1. 插入数据 - 插入单行数据： ``` INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); ``` - 插入多行数据： ```...
基于SpringBoot+Vue+MySQL的养老院管理系统
程序员大金的博客
09-20 837
基于SpringBoot+Vue+MySQL的养老院管理系统，附源码。
Mysql梳理7——分页查询
haozihua的博客
09-22 441
注意：MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。第二个参数“行数”指示返回的记录条数。
MYSQL SWAP 内存 vm.swappiness
jnrjian的博客
09-20 920
关于 vm.swappiness 的困惑来自于这样一个事实：在较旧的 Red Hat 内核中，vm.swappiness 的值为 0 会导致最少量的交换以避免内存不足的情况。在较新的内核中（从 RHEL 内核 2.6.32-303 开始​​），值为 0 将完全禁用交换，但值为 1 将提供最少量的交换以避免内存不足的情况。在较旧的内核中，0 和 1 之间的实际差异并不大，但 1 是避免较新内核中出现 OOM 情况的安全设置（当然，假设有一些交换空间可用）。考虑到系统自动将未使用的页面交换到磁盘。
Linux递归找出目录下最近被修改文件（最近一段时间内被修改过的最新文件）（最近修改文件、最新文件、查找文件）（监控目录、监控mysql文件）
Dontla的博客
09-26 484
目录下最近60分钟内修改的最新文件。如果你需要查找的时间范围不同，可以调整。表示最近10分钟）。
二、MySQL环境搭建
weixin_44063529的博客
09-23 1705
这篇文章是关于MySQL数据库的安装、配置和使用的详细指南。它包括了MySQL的卸载步骤、不同版本的下载和安装过程、登录方法、基本的数据库操作演示以及图形化管理工具的使用。此外，文章还提供了解决安装失败问题的方法和MySQL编码设置的说明。对于MySQL8.0，特别强调了默认字符集的变化和新密码验证插件的问题。
Java项目实战II基于Java+Spring Boot+MySQL的IT技术交流和分享平台的设计与实现（源码+数据库+文档）
最新发布
2401_86524610的博客
09-26 1106
语言：Java使用框架：Spring Boot前端技术：JS、Vue 、css3开发工具：IDEA/Eclipse数据库：MySQL 5.7/8.0数据库管理工具：phpstudy/NavicatJDK版本：jdk1.8前端环境：Node.Js 12\14\16。
【MySQL】获取最近7天和最近14天的订单数量，使用MySQL详细写出，使用不同的方法
2301_81133727的博客
09-23 1035
要获取最近7天和最近14天的订单数量，我们可以使用不同的方法来优化查询性能。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值