只输出指定结果：having
having作用和where一样，但having只能用于group by
select gender,count(*) from students group by gender having count(*)>2;
只输出两条记录以上的性别分组
select gender,count(*) from students group by gender having avg(age)>30;
只输出平均年龄大于30岁的性别分组
group by + with rollup
在最后新增一行，来记录当前列里所有记录的总和（with rollup）
select gender,count(*) from students group by gender with rollup;
4.1分页查询
获取部分行 limit(此语句应放在句子最后)
select * from students where gender=1 limit 0,3;
获取第1行（位置0，若是第二行开始则为1，3）
开始的3行记录
4.2内连接（ inner join 内连接 On表一.*=表二.*的条件）
关联查询（取两表交集）
select * from students inner join classes on students.cls_id = classes.id;
4.3左连接left join（以左表为基准，无交集也输出所有左表内容）
select * from students as s left join classes as c on s.cls_id = c.id;
4.4子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select语句称之为子查询语句
9.11练习题补充
1.去重 distinct
select distinct depart from teachers;
查询表格教师中不重复的Depart列
2.查询值为某集合 WHERE * IN (85,86,88);
SELECT * FROM Scores WHERE Degree IN (85,86,88);
查询Score表中成绩为85，86或88的记录。
3.复合语句 同时用where 和 having
SELECT Cno,AVG(Degree) FROM Scores WHERE Cno LIKE '3%' GROUP BY Cno HAVING COUNT(Sno) >= 5;
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
4.内连接复合
SELECT Sname,Cname,Degree FROM Students INNER JOIN Scores ON(Students.Sno=Scores.Sno) INNER JOIN Courses ON(Scores.Cno=Courses.Cno) ORDER BY Sname;
查询所有学生的Sname、Cname和Degree列。
5.用区间（不等式）做连接
mysql> SELECT Sno,Cno,rank FROM Scores INNER JOIN grade ON (Scores.Degree >= grade.low AND Scores.Degree <= grade.upp );
查询所有同学的Sno、Cno和rank列。
6.同一表格数据大小对比
mysql> SELECT s1.Sno , s1.Degree FROM Scores AS s1 INNER JOIN Scores AS s2 ON(s1.Cno=s2.Cno AND s1.Degree>s2.Degree) WHERE s1.Cno='3-105' AND s2.Sno='109';
查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
7.非最高查询
mysql> SELECT * FROM Scores GROUP BY Sno HAVING COUNT(cno)>1 AND Degree!=MAX(Degree);
（只去掉了MAX(Degree)所在组，错误）
查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT sc1.* from scores sc1 INNER JOIN scores sc2 on (sc1.sno=sc2.sno and sc1.degree<sc2.degree);
（活用不等式）
8.在内连接时给table起别名时as可省略（参上）
9.数据类型datetime 中的时间可以用year函数提取
SELECT s1.Sno,s1.Sname,s1.Sbirthday FROM Students s1 INNER JOIN Students s2 ON(YEAR(s1.Sbirthday)=YEAR(s2.Sbirthday)) WHERE s2.Sno='108'
网友评论