高级查询、内外连接

1.EXISTS子查询

语法：

select .......from 表名 where exists(子查询)

子查询有返回结果： EXISTS子查询结果为TRUE，则执行外层查询 子查询无返回结果： EXISTS子查询结果为FALSE,外层查询不执行 当数据量大的时候使用exists,如数据量于一万以上使用，数据量少时可以使用in

示例：

/*1.检查‘logic java’ 课程最近一次考试成绩*/ /*2.如果有80分以上，显示分数排前5名的学员学号和分数*/ select * from result; select studentNo as 学生学号, studentResult as 学生成绩 from result where exists ( select studentNo,studentResult from result where examDate in ( select max(r.examDate) from result as r inner join subject as s on r.subjectNo = s.subjectNo where subjectName = 'logic java' ) )and studentResult>80 order by studentResult desc limit 5;

2.not exists 子查询

可以采用NOT EXISTS检测是否全部未通过考试，即不存在“成绩>=60分”的记录

3.子查询注意事项

（1）任何允许使用表达式的地方都可以使用子查询

（2）嵌套在父查询SELECT语句的子查询可包括：

SELECT子句 FROM子句 WHERE子句 GROUP BY子句 HAVING子句

（3）只出现在IN子查询中而没有出现在父查询中的列不能包含在输出列中

4.分组查询用法

SELECT列表中只能包含：

1.被分组的列

2.为每个分组返回一个值的表达式，如聚合函数

掌握GROUP BY子句实现分组查询语法：

SELECT …… FROM <表名> WHERE …… GROUP BY ……

示例：

SELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩 FROM `result` GROUP BY `subjectNo`;

/*制作学生成绩单*/ select s.studentName as 学生姓名, (select gradeName from grade where id = su.gradeID) as 课程所属年级, su.subjectName as 课程名称,r1.examDate as 考试日期,r1.studentResult as 成绩 from result as r1 inner join student as s on r1.studentNo = s.studentNo inner join subject as su on su.subjectNo = r1.subjectNo where r1.examDate in ( select max(examDate) from result as r2 where r1.subjectNo = r2.subjectNo group by r2.subjectNo ) order by su.gradeID; /*分组查询没门课平均分*/ select subjectNo as 科目编号, avg(studentResult) as 平均分 from result group by subjectNo ORDER BY AVG(studentResult) desc;

/*分别统计每个年级男、女生人数*/ select gradeId as 年级编号,sex as 性别,count(1) as 人数 from student group by gradeId,sex order by gradeId;

5.分组筛选语句

语法：

SELECT …… FROM <表名> WHERE …… GROUP BY …… HAVING……

示例：

/*分组筛选出课程平均分大于等于60分的课程*/ select subjectNo as 课程编号, avg(studentResult) as 平均分 from result group by subjectNo having 平均分>=60;

6.WHERE与HAVING对比

（1）WHERE子句

用来筛选 FROM 子句中指定的操作所产生的行

（2）GROUP BY子句

用来分组 WHERE 子句的输出

（3）HAVING子句

用来从分组的结果中筛选行

7.count（*）和count (1) 的区别

count(*)统计表里的所有数据条数，效率较低， count（1）也是统计表里的所有数据，但效率比count(*) 高

8.常用的多表连接查询

（1）内连接(INNER JOIN)

内连接语句

SELECT …… SELECT …… FROM 表1 等价于 FROM 表1，表2 INNER JOIN 表2 WHERE …… ON ……

示例：

SELECT `student`.`studentName`,`result`.`subjectNo`,`result`.`studentResult` FROM `student`,`result` WHERE `student`.`studentNo` = `result`.`studentNo`;

SELECT S.`studentName`,R.`subjectNo`,R.`studentResult` FROM `student` AS S INNER JOIN `result` AS R ON (S.`studentNo` = R.`studentNo`);

三表内连接

SELECT S.studentName AS 姓名,SU.subjectName AS 课程,R.studentResult AS 成绩 FROM student AS S INNER JOIN `result` AS R ON (S.`studentNo` = R.`studentNo`) INNER JOIN `subject` AS SU ON (SU.subjectNo=R.subjectNo);

（2）外连接

左外连接 (LEFT JOIN)

主表（左表）student中数据逐条匹配表result中的数据

1.匹配，返回到结果集 2.无匹配，NULL值返回到结果集

示例：

/*左外连接 left join 前面的表为主表，以主表里的字段为依据，把从表里的数据填充给主表，从表里面没有的字段以null填充 left join 后面的表为从表*/ select s.studentName as 学生姓名, r.subjectNo as 科目编号, r.studentResult as 学生成绩 from student as s left join result as r on s.studentNo = r.studentNo;

右外连接 (RIGHT JOIN)

右外连接的原理与左外连接相同 右表逐条去匹配记录；否则NULL填充

示例：

/*右外连接 right join前面的表为主表，以主表里的字段为依据，把从表里的数据填充给主表，从表里面没有的字段以null填充 right join 后面的表为从表 */ select r.subjectNo as 科目编号, r.studentResult as 学生成绩, s.studentNo as 学生编号 from result as r right join student as s on r.studentNo = s.studentNo;

9.使用临时表保存查询结果

create temporary table表名（查询语句）

提示：

临时表只在当前连接可见，连接关闭自动删除，修改临时表数据不影响原表数据

10.MySQL 如何把varchar类型转换为int类型，再做加法运算

cast(字段1 as int) + cast(字段2 as int)

示例：

select trunk_breakers_num,branch_breakers_num,(cast(trunk_breakers_num as int) +