初始MYSQL数据库（4）—— “不一样的“新增与查询

我要学编程(ಥ_ಥ)

于 2024-09-09 20:39:55 发布

阅读量944

点赞数 68

分类专栏： MYSQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_80854132/article/details/141950796

版权

MYSQL 专栏收录该内容

8 篇文章 3 订阅

订阅专栏

找往期文章包括但不限于本期文章中不懂的知识点：

个人主页：我要学编程(ಥ_ಥ)-CSDN博客

所属专栏： MYSQL

新增

前面我们学习的新增是插入一条记录，那有没有复制粘贴类的操作呢？有的，我们下面就来学习。

语法：

insert into 新表 (新表的字段) select 旧表的字段 from 旧表

注意：

1、新表必须存在；

2、旧表的字段要和新表的字段相对应。这里的对应是指数量和数据类型。

例如：

查询

前面我们学习的查询操作是操作的一条记录中的列与列，没有办法查询行与行。例如：我们前面学习的插入某人的成绩总分是把这条记录的列与列相加，没办法做到查询这个班级或者这个学生表中的所有人的某一学科总分或是平均分。因此，下面我们就来学习查询行与行之间的关系。

聚合查询

聚合查询时利用聚合函数来操作数据行的。

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

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

注意：这里的不写 distinct ，查询的就是非空的数据值；加上 distinct 就是查询不同的数据值。

聚合查询的相关练习

创建一个学生表，插入他们的相关信息（学号、姓名、性别、主科成绩），最后再利用聚合函数查询这个学生表的相关信息。

代码实现：

-- 创建数据表
create table student (
	id bigint primary key auto_increment,
	name varchar(50) not null,
	gender bit(1) not null,
	chinese decimal(5,2),
	math decimal(5,2),
	english decimal(5,2)
);

-- 为数据表插入数据
insert into student values (NULL,'张三', 1, 90, 90, 80), (NULL,'李四', 0, 90, 80, 98), 
(NULL,'王五', 1, 70, 100, 60), (NULL,'赵六', 1, 98, 100, 100);

-- 开始使用聚合函数进行查询操作
-- 1.统计学生表的人数
select count(id) from student; -- 还可以使用count(*)或者count(1)
-- 2.统计男生的数量
select count(gender) from student where gender = 1;
-- 3.统计语文、数学、英语的总分
select sum(chinese) 语文总分, sum(math) 数学总分, sum(english) 英语总分 from student;
-- 4.统计语文、数学、英语的平均分
select avg(chinese) 语文平均分, avg(math) 数学平均分, avg(english) 英语平均分 from student;
-- 5.统计每科最高分和最低分
select max(chinese) 语文最高分, min(chinese) 语文最低分, max(math) 数学最高分, 
min(math) 数学最低分, max(english) 英语最高分, min(english) 英语最低分 from student;

这里就不演示其效果了，大家可以自己试试。

GROUP BY子句

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

语法：

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

-- column1是要分组的字段，sum(column2)是要显示的字段，只能用聚合函数来实现
-- column1,column3 分组的依据

例如：使用公司人员关系表来看看这个group by子句的使用。

-- 创建员工关系表
create table employee (
	id bigint primary key auto_increment,
	name varchar(50) not null,
	salary decimal(10,2) not null,
	position varchar(50) not null
);

-- 为表添加数据
insert into employee values (NULL, '马xx', 1000, '游戏陪玩'), 
(NULL, '马x', 800, '购物助理'), (NULL, '马喽1号', 100000, '系统架构师'),
(NULL, '马喽2号', 50000, '软件工程师'), (NULL, '马喽3号', 35000, '高级程序员'), 
(NULL, '马喽4号', 20000, '程序员'),(NULL, '马喽5号', 10000, '码农');

-- 根据员工之间的职位来计算平均工资
select position 职位, round(avg(salary),2) 平均工资 from employee 
group by 职位 order by 平均工资 ASC;

如果我们要对分组后的结果进行筛选的话，不能够用 where子句，得使用 HAVING。

这里之所以使用round，是为了将小数值精简为两位。

-- 这里的n就是小数部分的个数
round(数据, n)

round(avg(salary), 2) —— 把平均工资保留两位小数。

HAVING

例如：显示平均工资在10000以下的职位以及其平均工资。

select position 职位, round(avg(salary),2) 平均工资 from employee
group by 职位 having avg(salary) < 10000 ORDER BY 平均工资 ASC;

注意：

1、在where子句中不能使用别名，但是在 having子句中是可以的。究其原因是两者的执行顺序不同。having子句是在分完组后再去执行的，而此时select操作早已执行完；where子句是在找完表之后就开始执行了，在select操作之前。

2、where子句和having子句的相对位置不同。where子句是紧跟在from子句之后的。但是having子句是在group by子句后面的。

联合查询

内连接

我们前面学习了数据表设计的原则：三大范式，在第二范式和第三范式中，为了消除部分函数依赖和部分函数传递依赖，我们是把其分成多张表，虽然确实能避免数据冗余、更新异常、插入异常、删除异常等问题，但是也造成了数据的不完整。就像原来的课程成绩表，被划分成两张表之后，如果我们要去查询某个学生的某个课程成绩的话，得分别查询两个表（一个表的数据不完整），就很麻烦。因此，就产生了联合查询的概念。即将要查询的多张表全部显示出来。

首先是将多张表中的数据进行排列组合或者说笛卡尔积。具体过程如下：

按照上面的匹配方式肯定有数据是错误，因此接下来就是开始对数据进行筛选。那怎么筛选呢？两张表之间肯定是有一定的联系的（通过主外键进行匹配，当两者相等时，也就说明是正确的数据）。例如：张三的选修课程是3，那么只需要在选修课程表中找到课程编号为3的即可。即学生表的选修课程和选修课程表的编号相等即可。

上面就是基本信息的查询了，如果想要简化信息的话，就可以通过指定列和取别名的方式进行。

现在我们就根据上述图片中的关系来编写代码：

-- 创建一个学生表和一个选修课程表
create table course1 (
	id bigint primary key auto_increment,
	name varchar(50) not null
);

create table student1 (
	id bigint primary key auto_increment,
	name varchar(50) not null,
	course_id bigint not null
);

-- 选修课程表插入数据
insert into course1 values (NULL, 'C语言'),
(NULL, 'JAVA'), (NULL, '数据结构'), (NULL, 'MYSQL');

-- 学生表插入数据 
insert into student1 values (NULL, '张三', 3), 
(NULL, '李四', 2), (NULL, '王五', 1),(NULL, '赵六', 4), 
(NULL, '孙七', 1), (NULL, '周八', 1), (NULL, '吴九', 4), (NULL, '赵十', 3);

-- 开始执行联合查询操作
select student1.id, student1.name, course1.name from student1, course1 
where course1.id = student1.course_id;

注意：

1、当联合查询的表中有相同的字段的时候，一定得得表明在那个表下的字段，否则就会报错。

2、并不是说只有两者建立的主外键的关系才能用使用联合查询。使用主外键主要是为了插入时，保证数据的准确性。而联合查询是为了数据的完整性。

联合查询除了上面这种写法之外，还有一种写法：

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

注意：在 join 前面可以选择加上 inner，表示这是内连接的意思。

既然有内连接，肯定也有外连接。首先，我们得了解什么是内连接？什么是外连接？内连接就是两张表（或者多张表）内部数据之间的连接；而外连接是指当一张表中某些字段与另一张表（或者多张表）没有任何关系时，也会被当做是有关系从而进行笛卡尔积计算。

外连接

根据显示的结果，又分为左外连接和右外连接。左外连接就是在 join 左边的加上关键字 left 或者 right 来指明全部显示哪个表。

现在在 course 表中加入了另外一门课程：C++，但是还没有学生（要下一届才开始执行）。因此我们现在去使用内连接的方式的话，就看不到这个课程，但实际上是有的。

下面用内连接的方式实现一下：

-- 因为的id设置了自增，因此我们就只需要指定插入的name即可
insert into course (name) values ('C++');

-- 开始内连接查询(下面两种方式任选一种)
select student.id, student.name, course.name from student inner join 
course on student.course_id = course.id; 

select student.id, student.name, course.name from student, course 
where student.course_id = course.id;

接着再用右外连接的方式实现一下：

-- 开始用外连接查询 
select student.id, student.name, course.name from student right join
course on student.course_id = course.id;

select student.id, student.name, course.name from course left join 
student on student.course_id = course.id;

注意：

1、左右位置的选择是看我们要全部显示是哪个表。如果要全部显示的表在 join 的左侧，那么我们就使用 left 关键字；反之，则使用 right 关键字。

2、全部显示的表没有数据时，就会用NULL值来填充。下面就用命令行来展示：

3、在MYSQL中是不支持全外连接的，也就是说不能把两张表（或者多张表）都全部显示出来。

自连接

自连接是指在同一张表连接自身进行查询。自连接的作用：通过自连接用列与列之间的比较实现为行与行之间的比较。

例如：在一张学生成绩表，找到语文比英语好的。

-- 创建学生成绩表
DROP TABLE IF EXISTS student_score;
CREATE TABLE student_score (
    id bigint,
    name VARCHAR(50),
    chinese DECIMAL(4,1),
    math DECIMAL(4,1),
    english DECIMAL(4,1)
);
-- 插入数据
INSERT INTO student_score (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);

有下面两种方式进行查询：

select name, chinese, english from student_score where chinese > english;

select s_c1.name from student_score s_c1, student_score s_c2 
where s_c1.id = s_c2.id and s_c1.chinese > s_c2.english;

注意：

1、由于是自查询，这里的表名都是一样的，因此得起别名来进行区分；

2、起别名可以在 from 子句中进行，这样即使是 where 子句依然可以识别。

子查询

子查询是指嵌入在其他SQL语句中的select语句，也叫嵌套查询。即查询的结果为下一个查询的起始条件（即where子句中的内容）。

例如：

drop table if exists books;

create table books (
	id bigint primary key auto_increment,
	name varchar(50) not null,
	author varchar(50) not null,
	price decimal(6,2) not null,
	sort varchar(50) not null
);

insert into books (name, author, price, sort) values
('大话数据结构', '程杰', 59, '计算机类'),
('长恨歌', '白居易', 10, '文学类'),
('论语', '孔子及再传弟子', 60, '文学类');

-- 找到与长恨歌是同一类的书籍
select * from books where sort = (select sort from books where name = '长恨歌');

注意：

1、子查询的上一级查询的结果一定要和 where 子句中的查询条件相符合。就像上面一样，查询的是同一个类的书籍，即先把长恨歌的书籍类别查出来，把这个再当作下一级查询的依据。

2、子查询的结果可能是一个集合，那么此时我们就得用 in 或 not in 来筛选。

3、子查询中，where 子句中的where后面跟着exists的意思是如果返回的结果集不为空，就执行下一级查询；反之，则不执行。not exists 的效果刚好相反。

合并查询

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

例如：在学生成绩表中查询语文成绩高于英语成绩和数学成绩高于英语成绩，并将两表进行和并操作。

select * from student_score where chinese > english 
union select * from student_score where math > english;

简单理解就是：一个结果是并集，一个结果是交集。

union 与 union all 的区别是：union 会将重复出现的值只会显示一次，而 union all 便会将所有值全部显示出来。

注意：不管是 union 还是 union all 这里合并都是临时表（就是查询出来的数据表，不存在于数据库中），并不是我们数据库中真实的表。

以上就是联合查询的全部内容啦！

好啦！本期初始MYSQL数据库（4）—— “不一样的“新增与查询的学习之旅就到此结束啦！我们下一期再一起学习吧！

我要学编程(ಥ_ಥ)

关注

68
点赞
踩
45

收藏

觉得还不错? 一键收藏
打赏
78
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录