SQL查询

sql总结

//单表查询
 1. select * from student;
 2. select sname,ssex,class from student;
 3. select distinct depart from teacher;//去重查询
 4. select * from score where degree between 60 and 80;//区间查询
 5. select * from score where degree>60 and degree<80;
 6. select * from score where degree in(85,86,88);
 7. select * from student where class='95031' or class='95033';
 8. select * from student order by class desc;//降序
 9. select * from student order by class asc;//升序
 10. select * from score order by cno asc,degree desc;//班级升序，成绩降序
 11. select count(*) from student where class='95031';//某班级总人数
 12. select sno,cno from score where degree=(select max(degree) from score);//查询最高分数获得者的sno,cno

//分组查询

 1. select sno,cno,degree from score order by degree desc limit 0,1;//LIMIT r, n: 表示从第r行开始，查询n条数据
 2. select cno,avg(degree) from score group by cno;//求每个班级的平均分
 3. select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';

//多表查询
 1. select name,cno,degree from student,score where student.no=scores.s_no;//查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree
 2. select s_no,name as c_name,degree from score,course where score.c_no=course.no;//查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列
 3. select student.name as s_name,course.name as c_name,degree from student,score,course where student.NO = score.s_no and score.c_no = course.no;//查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree
 4. select c_no,avg(degree) from score where s_no in (select no from student where student.class='95301') group by c_no;//查询 95031 班学生每门课程的平均成绩
 5. select * from score where c_no='3-105' and degree>(select degree from score where s_no='109' and c_no='3_105');//查询在 3-105 课程中，所有成绩高于 109 号同学的记录

//YEAR函数与带IN关键字查询
select no,name,birthday from student where year(birthday) in (select year(birthday) from student where no in (101,108));//查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列

//多层嵌套子查询

 1. select * from score where c_no = (
	select no from course where t_no=(
		select no from teacher where name = '张旭')
	);//查询 '张旭' 教师任课的学生成绩表。
 2.select name from teacher where no in(
 	select t_no from course where no in(
 		select c_no from score group by c_no having count(*)>5
 	)
 );//查询某选修课程多于5个同学的教师姓名
 //执行顺序：where > group by >having
 //where只能用来约束来自数据库的数据，不能使用聚合函数；having可以使用聚合函数

//NION 和 NOTIN 的使用
//查询 计算机系 与 电子工程系 中的不同职称的教师。

 1. -- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);

// any 至少一个 ；all 所有

 1. SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
2. SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);

-- score a (b): 将表声明为 a (b)
SQL中where与having的区别
1.where和having的区别
2.聚合函数和group by
3.where 和having的执行顺序
4.where不能使用聚合函数、having中可以使用聚合函数
1.where和having的区别
where:
where是一个约束声明,使用where来约束来自数据库的数据;
where是在结果返回之前起作用的;
where中不能使用聚合函数。
having:
having是一个过滤声明;
在查询返回结果集以后，对查询结果进行的过滤操作;
在having中可以使用聚合函数。 
2.聚合函数和group by
聚合函数就是例如SUM, COUNT, MAX, AVG等对一组(多条)数据操作的函数，需要配合group by 来使用。
#如：
SELECT SUM(population),region FROM T01_Beijing GROUP BY region; //计算北京每个分区的人数
3.where和having的执行顺序
where 早于 group by 早于 having
where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选
4.where不能使用聚合函数、having中可以使用聚合函数
#筛选出北京西城、东城、海淀及各区学校数量
SELECT region,count(school)
FROM T02_Bejing_school
WHERE region IN ('海淀' , '西城' , '东城') GROUP BY region；
#筛选出北京西城、东城、海淀三个区中学校数量超过10所的区及各区学校数量。
SELECT region,count(school)
FROM T02_Bejing_school
WHERE region IN ('海淀' , '西城' , '东城')
GROUP BY region HAVING count(school) > 10;
注意！我们不能用where来筛选超过学校数量超过10的区，因为表中不存在这样一条记录。而HAVING子句可以让我们筛选成组后的各组数据．