MySQL数据库增删查改进阶
source link: https://blog.51cto.com/u_15132397/5113852
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
MySQL数据库增删查改进阶
原创这篇博客主要是数据库增删查改的的进阶，我们在这里要学习聚合查询和联合查询，其中联合查询是我们经常使用的，并且有一定的难度，我们需要经常练习。
查找到的数据插入到另一张表中
我们以前使用过select查询一张表中的数据，那么我们是否可以把查到的数据保存到另一张表里面呢，事实上，我们可以做到
创建一张 原始表
-- 创建一张 原始表
create table user(
id int primary key auto_increment,
name varchar(20),
decription varchar(1000)
);
-- 插入一些数据
insert into user values
(null,'曹操','乱世枭雄'),
(null,'刘备','仁德之主'),
(null,'孙权','年轻有为');
创建保存表
create table user2(
name varchar(20),
decription varchar(1000)
);
开始查找并保存
insert into user2 select name,decription from user;
-
子查询得到的 列顺序 和 **类型 **要相同
-
列名一不一样没有挂巴西
-
我们查询得到的数据保存时一定要相匹配
聚合查询很简单，就是借助我们SQL的内置的函数就可以了，我们不关心他们是如何实现的，熟练掌握就可以了。下面我们来看看都有哪些函数。我们就一些例子来说，由于他们使用的方法都是一样的，我们就说一部分就可以了
下面是内置函数的分类和说明，我们看一看看就可以了，用着用着就会了。这些内置函数的得到的都是一张临时表
函数 说明
count() 返回查询到的数据的 数量
max() 返回查询到的数据的 最大值，不是数字没有意义
min() 返回查询到的数据的 最小值，不是数字没有意义
avg() 返回查询到的数据的 平均值，不是数字没有意义
sum() 返回查询到的数据的 总和，不是数字没有意义
count
count 这个函数很奇怪，我们在使用的的时候一定要小心，下面让我们来看看它的用法
select count(id) from user; -- 计算 id 的行数
这个是是计算 字段 id 所占据的函数，但是要是 id 的值位 NULL ，这一行就不计算
select count(*) from user; -- 计算 所有列 的行数 去最大值
一般情况下，得到的值和任意列的行数相同，但是有 NULL 的话就不一定了
只要默认值不是NULL 都计算
create table types(
type_id int,
type_name varchar(20) default 'unknow'
);
insert into types values (1,'1');
insert into types (type_id)values (2);
- count 不计算NULL的值 可以计算其他默认值
- count后不能有空格
sum 是计算指定列的和，我们也可以使用where语句进行一部分的求和
create table student(
id int primary key auto_increment,
name varchar(20),
score decimal(4,2)
);
insert into student values
(null,'刘备',84),
(null,'曹操',80),
(null,'孙权',90),
(null,'张飞',60);
select sum(score) from student; -- 允许指定别名
select avg (score) from student;
select avg (score) from student where score < 85; -- 计算 分数 小于85 的同学的平均数
group by 子句
group by 子句主要是为了分组,我们指定字段,将字段内容相同的分为一组,这和去重不一样,后面用到是的时候你就会发现
使用 group by 进行分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在select 中则必须包含在聚合函数中
我先创建一个环境
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);
查询每个角色的最高 最低 平局工资
这就要使用分组,分组后还可以使用内置函数
select role ,max(salary),min(salary),avg(salary) from emp group by role;
having
having 和 where语句作用是一样的,只不过它使用与group by 语句中
查询平均工资大于1000的角色
select role,avg(salary) from emp group by role having avg(salary) > 1000;
联合查询才是我们今天的正菜，这就要求高了，联合查询是多张表之间的查询，我们会经常使用
## 笛卡尔积
很抱歉，即使在计算机学习中我们也要听到这个“恶魔”的名字，不错他就是那个笛卡尔坐标系的笛卡尔，很庆幸我们不会学那么难的，就一个很简单的知识点，下面我画图表示
笛卡尔积的计算很简单，只需要表的函数相乘就可以了，不过这是我们学习联合查询的基础
-- 创建班级表
create table classes(
id int primary key auto_increment,
name varchar(30),
`desc` varchar(100)
);
-- 创建课程表
create table course (
id int primary key auto_increment,
name varchar(30)
);
-- 创建分数表
create table score(
score decimal(5,2),
student_id int,
course_id int
);
-- 创建学生表
create table student(
id int primary key auto_increment,
sn varchar(20),
name varchar(30),
qq_mail varchar(30),
classes_id int
);
插入一些数据 班级
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','[email protected]',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','[email protected]',1),
('00054','不想毕业',null,1),
('51234','好好说话','[email protected]',2),
('83223','tellme',null,2),
('09527','老外学中文','[email protected]',2);
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);
查询每一个学生的每一门成绩
分析我们这个要求
- 学生在 student表中
- 课程在 course表中
- 成绩在score表中
下面我们就可以写出下面代码,
select student.id,student.name ,course.id,course.name ,score.score from student,course,score;
我们发现结果有 960行,这肯定是不对的,我们得到了一些无效的数据.后面要加判断语句
我们发现要取的有效数据,学生的id一定要和成绩表的学生的id一定要相等
select student.id,student.name ,course.id,course.name ,score.score from student,course,score
where student.id = score.student_id;
只有120行了,还要加判断条件,课程id和成绩表的课程id也要相等
select student.id,student.name ,course.id,course.name ,score.score from student,course,score
where student.id = score.student_id and score.course_id = course.id;
这样就完成了,我们呢也可以简单一下表
select student.id,student.name ,course.name as course_name,score.score from student,course,score
where student.id = score.student_id and score.course_id = course.id;
计算每一名同学的总成绩
配合使用聚合函数
select student.id,student.name ,course.name as course_name,sum(score.score) from student,course,score
where student.id = score.student_id and score.course_id = course.id group by student.name;
我们会了基本的联合查询后,就要开始下面几个比较难的的知识点了
这个知识点我们了解就可以了,不过要记住 join on 的查询方法
我们看一下语法,只需要看看就可以,重点结合例子
select 字段 from 表1 别名1 inner join 表2 别名2 on 连接条件 and 其他条件; -- inner 可以省略
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
select sco.score from student stu inner join score sco on stu.id = sco.student_id
and stu.name = '许仙';
-- 或者
select sco.score from student stu, score sco where stu.id = sco.student_id and
stu.name = '许仙';
观察下面的结果,我们有8个学生,但是结果只有7个,原因就是 score 中没有老外学中文的id
select * from student stu join score sco on stu.id = sco.student_id;
我们可以这样理解,当我们对笛卡尔积的结果进行筛选,筛选的结果一定在两张表中都存咋,这就是内链接,可以立即为交集
谈完内连接,一定会有外连接,实际工作中,主要以内连接为主,不过我们也要了解我找了一篇文章,很不错,大家可以看看 理解内连接外连接
我们不谈外连接，这里就要说说什么是自连接。自连接是指在同一张表连接自身进行查询。我们可以把一张表看作是两张一摸一样的表来进行多表查询，这里我们仍然通过例子来演示.
问题 : 显示所有“计算机原理”成绩比“Java”成绩差的成绩信息
-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 差的信息
select s1.*,s2.* from score s1 ,score s2 where s1.student_id = s2.student_id and (s1.course_id = 1 and s2.course_id = 3) and s1.score > s2.score;
当然我们也可以使用 join on 语句
子查询倒是很简单，我们可以把几个select在同一条语句中使用，下面是定义
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询
我们用例子来演示
问题 : 查询与“不想毕业” 同学的同班同学
我们可以先使用普通的查询方法
-- 查找 “不想毕业” 同学的 班级id
select classes_id from student where name = '不想毕业';
-- 查找所有学生班级id 与 “不想毕业” 同学 班级id 相同的学生
select * from student where classes_id = 1;
使用子查询
我们只需要一步就可以了
select * from student where classes_id = (select classes_id from student where name = '不想毕业');
有时侯，单行查询的结果是远远不够的，你看看下面的问题
问题：查询“语文”或“英文”课程的成绩信息
我们无法使用上面的的语句，这就需要多行行查询的结果，下面有两种方式可以解决
我们直接使用，解释在下面
select * from score where course_id in (select id from course where (name = '语文' or name = '英文'));
使用 exists
这个方法我们有点难理解，记住就。一般情况下我们都是使用第一种方法
select * from score sco where exists (select sco.course_id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);
现在让我们来解释一下这些原理
- 对于 in ： SQL是先执行where语句后面的得到语文和英文课程的id 然后就是我们可以理解的查询语句了
- 对于 exists ： SQL先执行where前面的，得到的每一条数据看看是否符合后面的判断语句。
这是最后一个查询。很简单的
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用union
和union all时，前后查询的结果集中，字段需要一致
问题:查询id小于3，或者名字为“英文”的课程
我们使用平常的方法
select * from course where id < 3 or name = '英文';
union
我们也可以通过使用union来解决
select * from course where id < 3 union select * from course where name = '英文';
union all
我们可能会疑惑union all这个是什么玩意，看看下面的现象你就会明白了
问题: 查询id小于3，或者名字为“Java”的课程
-- 使用 union
select * from course where id < 3 union select * from course where name = 'java';
-- 使用 union all
select * from course where id < 3 union all select * from course where name = 'java';
这里你就会发现，当我们使用union all的时候，即使得到重复的数据也不会省略
- 赞
- 收藏
- 评论
- 分享
- 举报
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK