MySQL高级查询

1、exists和not exists子查询

/*exists的常用用法*/
drop table if exists student;

　　此外，exists也可以作为where的子查询：

/*exists的参数是一个任意的子查询*/
/*若有返回行，exists的结果为true，会执行外层语句*/
/*若无返回行，exists结果为false，外层语句不再执行*/
select colums from tableName where exists(子查询)；

　　注意，exists和not exists的结果只取决于是否有返回记录，不取决于记录的内容。

 

　　2、子查询注意事项

　　（1）子查询语句可以嵌套在SQL语句中任何表达式出现的位置

　　子查询可以被嵌套在select语句的列、表和查询条件中，即select子句、from子句、where子句、group by子句和having子句。

/*select子句*/
select(子查询）from 表名;

/*from子句*/
/*这里表的别名不能省略*/
select * from（子查询 ）as 表的别名；

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

 

　　3、分组查询

　　（1）group by——将查询结果按照一个、多个字段分组

/*单独使用group by*/
select * from student group by major

　　当像上面那样单独使用group by时，结果中每一个不同的major只会显示一个（即每组中挑选一个显示，貌似为每组的第一个）。这样是没有太多意义的，或者说，它只显示了有多少个不同的major。

　　所用我们在分组查询中会与数学统计函数结合一起使用。

　　单字段分组查询：

/*查看每门课程的平均成绩*/
select subjectNo,AVG(studentResult) as 课程平均成绩
    from result group by subjectNo;

/*查询男、女学生的人数各是多少*/
/*这里的可以这样使用*号吗？为何不直接指明列名？*/
/*可见的是表中的一行就仅代表一个人，所以这里用*表示的是对这种情况按行统计*/
select count(*) as 人数,sex from student group by sex;

/*查询每个年级的人数*/
/*这里的count(*)也是一样*/
select count(*) as 年纪人数,gradeId from student group by gradeId;

/*查询每个科目的平均分，并且按照由高到低的顺序排列显示*/
/*这里要思考的是order by的位置，注意要对哪个数据排序*/
select subjectN,AVG(studentResult) AS 平均课程成绩
   from result
   group by subjectNo;
   order by AVG(studentResult) desc;

　　多字段分组查询：

/*在一张学生信息表中有来自不同年纪、不同性别的学生，学号也都不同*/
/*现在统计每个年纪的男、女生人数*/
select count(*) as 人数，gradeId as 年级，sex as 性别 from student
    group by gradeId,sex
    order by gradeId;

　　注意到了，使用group by关键字时，在select列表中可以指定的列是有限制的，仅允许group by子句后的列，聚合函数计算出的列。

 

　　4、使用having子句进行分组筛选

　　简单来说，having子句用来对分组后的数据进行筛选，即将组看成新的列，来进行筛选。而where子句只能对没有分组统计前的数据进行筛选。

　　（1）查询年纪总人数超过1的年级——先按年级分组，再按条件筛选

select count(*) as 人数,gradeId as 年级 from student 
    group by grade
    having count(*)>2;

　　（2）select语句中，where、group by、having子句和聚合函数的执行次序如下：

　　where子句从数据源中去除不符合条件的数据；然后group by子句搜集数据行到各个组中；

　　接着统计函数为各个组计算统计值；最后having子句去掉不符合其组搜索条件的各组数据行。

　　如，查询每门课程及格总人数和及格平均分在80分以上的记录：

select count(*) as 人数，AVG(studentResult) as 平均分，subjectNo as 课程 from result
    where studentResult>=60
    group by subjectNo
    having AVG(studentResult)>=80;

 

　　5、使用limit分页查询数据——基于多个表的数据查询

　　（1）多表连接查询——从多个表中选择or比较数据项

　　　　通过各个表之间共同列的关联性查询数据，是关系数据库查询最主要的特征。

　　（2）内连接查询，最典型、常用的连接查询，根据表中共同的列来进行匹配。特别当两个表存在主、外键关系时通常使用内连接查询。

　　（3）外连接查询，至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录。有左外连接、右外连接。

 

　　6、内连接查询

　　内连接通常使用“=”or“<>”等比较运算符来判断两列数据值是否相等。

　　在内连接查询中，参与的表的地位是相等的。

　　实现内连接的两种方式：

　　（1）where子句中指定连接条件

/*查询学生姓名和成绩，成绩表中只有学号和成绩，名字在学生信息表中*/
/*from后面有两个表名*/
select student.studentName,result.subjectNo,result.studentResult
    from student,result
    where student.studentNo=result.studentNo;

　　（2）在from子句中使用inner join...on

/*inner join连接两个表，且inner可以省略*/
/*on设置条件*/
/*as指定表的别名，如果查询的列名在多个表中的名字不重复，则可以不使用表名限定*/
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)

 

　　7、外连接查询

　　参与外连接查询的表有主、从之分，以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；而对不符合连接条件的列，会填上null值后再返回到结果集中。（内连接中会忽略不符合条件的数据）

　　（1）左外连接——left join...on，left outer join...on

　　结果集包括left join中指定的左表的所有行，若左表的某行在右表中没有匹配行，则在相关的结果集行中右表的所有选择列为空值。

/*显示所有考试名单上的学生的成绩，没有参加的也要打印出来*/
/*主表（左表）为学生信息表，学生成绩表为从表*/
/*没有参加任何科目考试的学生，在成绩表中也没有相关记录*/
/*他们对应的科目编号和成绩均会以null填充*/
select S.studentName,R.subjectNo,R.studentResult
　　from student as S
　　left outer join result as R on(S.studentNo=R.studentNo)

　　（2）右外连接——right outer join...on，right outer join...on

　　结果集要包含右表中所有匹配的行，若右表中有的项在左表中没有对应的项，以null值填充。

　　在这里，join指定的表为主表，即右表为主表。（哪个表中所有行都显示了，就是主表）