📜个人简介
⭐️个人主页：摸鱼の文酱博客主页🙋♂️
🍑博客领域：java编程基础,mysql
🍅写作风格：干货,干货,还是tmd的干货
🌸精选专栏：【Java】【mysql】 【算法刷题笔记】
🎯博主的码云gitee，平常博主写的程序代码都在里面。
🚀支持博主：点赞👍、收藏⭐、留言💬
🍭作者水平很有限，如果发现错误，一定要及时告知作者哦！感谢感谢！
👓查询
💦聚合查询
数据准备
先创建一个数据库,然后创建一个学生表,插入以下数据,后面操作时会用到
-- 创建学生表createtable student (id intprimarykeyauto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20),
classes_id int);-- 插入数据insertinto 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);
创建成绩表:
-- 创建考试成绩表DROPTABLEIFEXISTS exam_result;CREATETABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1));-- 插入测试数据INSERTINTO 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);
createtable emp(
id intprimarykeyauto_increment,
name varchar(20)notnull,
role varchar(20)notnull,
salary numeric(11,2));insertinto emp(name, role, salary)values('马云','服务员',1000.20),('马化腾','游戏陪玩',2000.99),('孙悟空','游戏角色',999.11),('猪无能','游戏角色',333.5),('沙和尚','游戏角色',700.33),('隔壁老王','董事长',12000.66);
🐾聚合函数
常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有:
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义
让我们来看一下这些函数的使用效果:
COUNT
案例:
-- 统计班级共有多少同学SELECTCOUNT(*)FROM student;-- 或者SELECTCOUNT(0)FROM student;-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果SELECTCOUNT(qq_mail)FROM student;
班级总人数为8人
有qq邮箱的为4人
SUM
案例:
-- 统计数学成绩总分SELECTSUM(math)FROM exam_result;-- 不及格 < 60 的总分，没有结果，返回 NULLSELECTSUM(math)FROM exam_result WHERE math <60;
数学成绩总分:
数学低于60分
AVG
案例:
-- 统计平均总分SELECTAVG(chinese + math + english) 平均总分 FROM exam_result;-- 统计数学平均分SELECTAVG(math) 数学平均分 FROM exam_result;
MAX
案例:
-- 返回英语最高分SELECTMAX(english)FROM exam_result;
MIN
案例:
-- 返回 > 70 分以上的数学最低分SELECTMIN(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
案例
-- 查询每个角色的最高工资、最低工资和平均工资select role,max(salary),min(salary),avg(salary)from emp groupby role;
分组还可以指定条件筛选,如果是分组之前指定条件,使用where,如果是分组之后指定条件,使用having.
🐾HAVING
GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING
案例:
-- 显示平均工资低于1500的角色和它的平均工资select role,max(salary),min(salary),avg(salary)from emp groupby role
havingavg(salary)<1500;
💦联合查询
🔮笛卡尔积
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积
笛卡尔积的执行效率虽然不高,但其本身是一个功能挺好用的操作,有事可借助它完成更复杂的操作.
但是,笛卡尔积也是一个危险的操作!!!,如果表内数据过多,贸然进行笛卡尔积,很可能会使数据库爆满.
所以,开发过程中一般不用笛卡尔积来处理数据.
笛卡尔积是两张表中的数据尽可能的排列组合得到的,在这些排列组合中的数据都是有用的吗?
答案当然是不一定,这些数据有的是我们需要的,有的则没有意义.
案例:
1.查找同学姓名和对应班级的名字
将两张表做笛卡尔积后得到:
从结果中我们可以看到:两张表中都有classid这一列.
classId中的值对应相等的记录,这其实就是应该要保留的记录~~,
像这样的classId相等的条件,就称为==“连接条件”==
我们注意到绿框内的数据相等时,才是我们需要的正确数据,给上面的命令多加一个判断条件:
select*from students,class where students.classId = class.classId;
去掉其他多余的数据数据,只保留我们要的姓名和对应班级:
select students.name,class.name from students , class where students.classId = class.classId;
初始化测试数据
insertinto classes(name,`desc`)values('计算机系2019级1班','学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2019级3班','学习了中国传统文学'),('自动化2019级5班','学习了机械自动化');insertinto 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);insertinto course(name)values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');insertinto 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 sco.score from student stu innerjoin score sco on stu.id=sco.student_idand stu.name='许仙';-- 或者select sco.score from student stu, score sco where stu.id=sco.student_id and stu.name='许仙';
2.
-- 查询所有同学的总成绩，及同学的个人信息：SELECT
stu.sn,
stu.NAME,
stu.qq_mail,sum( sco.score )FROM
student stu
JOIN score sco ON stu.id = sco.student_id
GROUPBY
sco.student_id;
3.查询所有同学的成绩，及同学的个人信息：
select*from student stu join score sco on stu.id=sco.student_id;
查询出来的都是有成绩的同学，“老外学中文”同学 没有显示
4.
-- 学生表、成绩表、课程表3张表关联查询SELECT
stu.id,
stu.sn,
stu.NAME,
stu.qq_mail,
sco.score,
sco.course_id,
cou.NAME
FROM
student stu
JOIN score sco ON stu.id = sco.student_id
JOIN course cou ON sco.course_id = cou.id
ORDERBY
stu.id;
🔮外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
语法:
左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件
案例:查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
只要把成绩表对应的那一侧完全显示即可
-- “老外学中文”同学 没有考试成绩，也显示出来了select*from student stu leftjoin score sco on stu.id=sco.student_id;-- 对应的右外连接为select*from score sco rightjoin 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
LEFTJOIN score sco ON stu.id = sco.student_id
LEFTJOIN course cou ON sco.course_id = cou.id
ORDERBY
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 =1AND 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 =1AND s2.course_id =3;
🔮子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询
返回一行记录的子查询
案例:
查询与“不想毕业” 同学的同班同学
select*from student where classes_id=(select classes_id from student where name='不想毕业');
多行子查询
[not] in关键字:
-- 使用INselect*from score where course_id in(select id from course where
name='语文'or name='英文');-- 使用 NOT INselect*from score where course_id notin(select id from course where
name!='语文'and name!='英文');
[NOT] EXISTS关键字
-- 使用 EXISTSselect*from score sco whereexists(select sco.id from course cou
where(name='语文'or name='英文')and cou.id = sco.course_id);-- 使用 NOT EXISTSselect*from score sco wherenotexists(select sco.id from course cou
where(name!='语文'and name!='英文')and cou.id = sco.course_id);
在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用
案例:
查询所有比“中文系2019级3班”平均分高的成绩信息
-- 获取“中文系2019级3班”的平均分，将其看作临时表SELECTavg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME ='中文系2019级3班';
🔮合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。
使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：
查询id小于3，或者名字为“英文”的课程：
select*from course where id<3unionselect*from course where name='英文';-- 或者使用or来实现select*from course where id<3or name='英文';
union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程
-- 可以看到结果集中出现重复数据Javaselect*from course where id<3unionallselect*from course where name='英文';
版权归原作者 摸鱼の文酱 所有， 如有侵权，请联系我们删除。