MySQL学习第三天

第一部分1、字段表的别名的用法as别名是你自己取的任意名称写法字段 as 别名 表 as 别名as是可以省略的也可以写为字段 别名表 别名-----第一种写法select sidavgcmarkas 平均分 from group up sid having avgcmark80; -----第二种写法select sidavgcmarka from group up sid having avgcmark80; -----其他使用select sidavgcmarka from group up sid having avgcmark80 order by avgmk desc;2、case when 选择语句判断字段的值等于某个值就显示指定内容语法1case 字段when 值1 then 内容1when 值2 then 内容2......else 内容nend语法2 判断某个字段值在某个范围内case when 判断表达式1 then 结果1when 表达式2 then 结果2...else 其他结果end------查询各科成绩最高分、最低分和平均分以如下形式显示课程ID课程name最高分最低分平均分及格率中等率优良率优秀率 --及格为60中等为70-80优良为80-90优秀为90 select c.cid 课程id,c.cname 课程name,max(m.cmark) 最高分,min(m.cmark) 最低分,avg(m.cmark) 平均分, round(sum(case when m.cmark60 then 1 else 0 end)/count(*)*100,2) as 及格率, round(sum(case when m.cmark70 and m.cmark80 then 1 else 0 end)/count(*)*100,2) as 中等率, round(sum(case when m.cmark80 and m.cmark90 then 1 else 0 end)/count(*)*100,2) as 优良率, round(sum(case when m.cmark90 then 1 else 0 end)/count(*)*100,2) as 优秀率 from course c,mark m where c.cidm.cid GROUP BY c.cname,m.cid;----查询员工表并返回员工部门信息10研发部20市场部30财务部 select*case depton when 10 then 研发部 when 20 then市场部 when 30 then财务部 else其他部门 end as 部门名称 from emp ----查询返回每个学生的分数等级90分以上显示优秀80分以上显示中等70分以上表示良好60分以上显示及格其他显示不及格 select*case when cmark90 then 优秀 when cmark80 then 中等 when cmark70 then 良好 when cmark60 then 及格 else不及格 end as 等级 from emp----查询教师中40岁以上和40岁以下的老师分别有多少人 select case when tage40 then 40岁以上 else 40岁以下end as age_group,count(1) from teacher GROUP BY age_group;3、多表查询多表查询指的是查询数据的时候如果结果中的数据来自于多张表那么就需要将多张表联合起来关联后查询这种多张表联合查询的方式就称为多表查询比如查询学生的姓名和它的平均分笛卡尔积多张表中的行数量在联合后的一个乘积联合查询基本语法select*from 表1表2表3......where 表1.列1表2.列1 and 表2.列2表3.列2多表查询规则要先找出几张表以及设置关联条件留下关联的正确数据后再去查询需要的数据----查询学生的姓名和他的分数s和m使用了别名的命名 select s.sname, m.cmark from student smark m where s.sidm.sid; ----查询每个学生的姓名、课程名、分数 select s.snamec.cnamem.cmark from student smark mcourse c where s.sidm.sid and m.cidc.cid4、自连接查询如果查询的时候一张表的数据需要用到多次那么就可以使用这一张表和自身作关联-----查询10002号学生的哪些课程比10002号学生的分数高 select *from mark m1mark m2 where m1.cidm2.cid and m1.cid10002 and m2.sid10001 and m1.cmarkm2.cmark; -----查询10002号学生的哪些课程比10002号学生的分数高,返回课程名 select c.cname from mark m1mark m2 course c where m1.cidm2.cid and m1.cid10002 and m2.sidc.cid and m2.sid10001 and m1.cmarkm2.cmark; -- 查询张三的哪些课程分数比李四高返回课程名 -- SELECT s.sname,m.cmark -- FROM student s , mark m -- WHERE s.sid m.sid AND s.sname 张三; -- -- SELECT s.sname,m.cmark -- FROM student s , mark m -- WHERE s.sid m.sid AND s.sname 李四; SELECT c.cname FROM student s1 , mark m1 ,student s2 , mark m2 ,course c WHERE s1.sid m1.sid AND s1.sname 张三 and s2.sid m2.sid AND s2.sname 李四 and m1.cid m2.cid and m1.cmark m2.cmark and m1.cid c.cid;5、并集、交集、差集查询----union并 union all并所有的都返回可能会重复返回 intersect交 except 差使用方法查询结果1 并集/交集/差集 查询结果2----unionselect* from emp where deptno in10,20union select* from emp where deptno in30,20----union allselect* from emp where deptno in10,20union all select* from emp where deptno in30,20----intersectselect* from emp where deptno in10,20intersect select* from emp where deptno in30,20----exceptselect* from emp where deptno in10,20except select* from emp where deptno in30,20第二部分1、其他的连接查询1.1内连接查询inner join内连接可以设置条件将不符合条件的数据直接过滤只返回符合条件的数据语法select from 表1 inner join 表2 on 条件inner join 表3 on 条件......select*from student s inner join mark m on s.sidm.sid inner join course c on m.cidc.cid1.2左外连接 left join把左边的表设为基准表不管数据是否关联都会把基准表数据全部返回表1是在左边语法select from 表1 left join 表2 on 条件left join 表3 on 条件......1.3右外连接 right join把右边的表设为基准表不管数据是否关联都会把基准表数据全部返回表2/3是在右边语法select from 表1 right join 表2 on 条件right join 表3 on 条件......1.4全外连接full join MySQL中不支持把左外连接和右外连接用union 连接 就是全外连接1.5交叉连接cross join返回结果和普通连接一样create table a( aid int , aname varchar(10) ); create table b( bid int , bname varchar(10), aid int ); INSERT INTO a values(1,A),(2,B),(3,C),(6,D),(7,E); INSERT INTO b values(101,AA,1),(201,BB,2),(301,CC,3),(401,DD,4),(501,EE,5); ----查询a表和b表的关联数据 ----基础等值连接 select*from a,b where a.aidb.aid; ----内连接 select*from a inner join b on a.aidb.aid; ----左外连接 select*from a left join b on a.aidb.aid; ----右外连接 select*from a right join b on a.aidb.aid; ----全外连接 select*from a left join b on a.aidb.aid union select*from a right join b on a.aidb.aid;注意事项1、inner join 中的inner 可以省略不写就直接写成join 就可以了2、inner join 中的条件都可以写在on后面 不用写where 也可以3、left join 中的条件也可以写在on后面但是返回的结果会按照基准表数据返回----可能结果跟我们设想的不一样所以left join条件一般写在where 后面-- 查询1班的每个女生的姓名和平均分外连接是否加where是有区别的 -- 使用内连接 SELECT s.sname,avg(m.cmark) FROM student s join mark m on s.sid m.sid and sclass 1班 and ssex 女 GROUP BY s.sname , s.sid; -- 使用外连接 不加where SELECT s.sname,avg(m.cmark) FROM student s left join mark m on s.sid m.sid and sclass 1班 and ssex 女 GROUP BY s.sname , s.sid; -- 使用外连接 加where SELECT s.sname,avg(m.cmark) FROM student s left join mark m on s.sid m.sid where sclass 1班 and ssex 女 GROUP BY s.sname , s.sid;2、子查询子查询指的是在一个SQL嵌套另一个SQL语句子查询可以出现在select、from、where、having等语句中用于提供中间条件或结果让主查询可以得到需要的结果-----查询张三的英语分数 select sid from student where sname张三; select cid from course where cname英语; select cmark from mark where sid(select sid from student where sname张三) and cid(select cid from courese where sname英语);2.1子查询中用到的运算符①比较运算符 ! ②in和not in非关联子查询③exists 和not exists关联子查询④any和 all2.2子查询的分类2.2.1按照执行的依赖关系分为非相关子查询 和 相关子查询2.2.1.1非相关子查询独立子查询子查询可以独立运行不依赖于外层的主查询只执行一次查询结果给主查询使用。select *from mark where sid(select sid from student where sname李四) select *from mark where sid in (select sid from student where sclass 1班)-----查询1班学生的学号和平均分 select m.sid,avg(cmark) from mark m where sid in (select sid from student where s sclass1班) group by m.sid;2.2.1.2相关子查询关联子查询子查询会引用外层表多个表之间的列无法独立运行外层每执行一次子查询就执行一次一般会搭配exists关键字来使用exists使用的时候原理是判断子查询语句是否成立成立就执行整个句子不成立就不执行-----查询1班学生的学号和平均分 select m.sid,avg(cmark) from mark m where exists (select *from student s where s.sidm.sid and sclass1班) group by m.sid;2.2.2按照查询返回的结果的行数/列数分类2.2.2.1标量子查询结果只有一行一列只用1个值可以直接使用单行比较运算符 !select *from mark where sid(select sid from student where sname李四)2.2.2.2列子查询多行子查询结果是多行1列可以使用in / not in完成查询也可以使用any/ all配合单行比较运算符使用select *from mark where sid in (select sid from student where sclass 1班)-----查询比江苏地区学生年龄都大的其他地区的学生信息 select * from student where sageall (select sage from student where snativeplace江苏); -----查询比江苏地区任意一个学生年龄都大的其他地区的学生信息 select * from student where sageany (select sage from student where snativeplace江苏) and snativeplace !江苏;2.2.2.3行子查询单行多列使用较少子查询返回一行多列外层需要使用多个字段匹配select*from student where (sid,sage)(select sid,sage from student where sname李四)2.2.2.4表子查询多行多列子查询返回多行多列一般会当做一张临时表放在from后面当做表查询(t所在位置可以随便换成其他的任意字母)select*from(select sid,cmark from mark where cmark60)t;-- 使用子查询查询所有课程都及格的学生的姓名 select sname from student where sid in (select sid from mark where cmark60 group by sid having count(1) (select count(1) FROM course)); -- 查询1班学生的平均总分是多少 select avg(summ) from (select sid,sum(cmark) summ from mark where sid in (select sid from student where sclass1班) group by sid)t;3、开窗函数overrow_number() over(partition by 开窗字段 order by 排序字段 asc/desc)开窗字段是指将哪个字段单独分为一个小组在小组中按照指定字段排序找每个人的前三名成绩 select*from( select *,row_number() over(partition by sid order by cmark desc) px from mark)t where px3;