2020-09-19sql练习笔记

只输出指定结果：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'