单表查询与多表查询

单表查询遵循的语法：

　　from 表

　　where 约束条件

　　group by 聚合查询（运算法则）

　　having 对于group by 的过滤

　　distinct 去重（select distinct post from emp; ）

　　order by (asc 默认排序，desc默认降序）

　　limit （限制展示条数）

　　正则 select * from emp  where name regexp "^.*(n|y)$"; 

　　

　　

-- 1、查询所有的课程的名称以及对应的任课老师姓名

SELECT * FROM teacher INNER JOIN course 
ON teacher.tid=course.teacher_id;

1	张磊老师	1	生物	1
2	李平老师	2	物理	2
2	李平老师	4	美术	2
3	刘海燕老师	3	体育	3

　

-- 2、查询学生表中男女生各有多少人

SELECT COUNT(sid) from student
GROUP BY gender;
count（sid）
6
10

-- 3、查询物理成绩等于100的学生的姓名

思路：1首先先拿到 物理课程 等于100 的 所有 信息 2.我们只要拿到符合等于物理100分的学生虚拟表的学生student_id 3. 将学表的真实表的学生student_id 进行判断 是否在在我们拿到的这个虚拟表学student_id里面  ------》》》相当于把第一一个条件的得到的虚拟表的最终结果当做另一个表的子查询  就是 子查询 中 in 的使用

select student.snmae from student where sid in

(select student_id from score inner join course 
 on score.course_id =course.cid 
where course.cname="物理” and score.num="100");

　　

--

4、查询平均成绩大于八十分的同学的姓名和平均成绩：
解题思路：1、首先我们要第一步拿到所有平均分大于80的学生所有id 通过学生id进行分组 having筛选 2.我们得到属于这个平均分段的学生id后 3.通过起别名将
这张虚拟进行 内连接 id 字段一样的 student.sid=t.student_id 

SELECT
	student.sname,
	t.平均分 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS 平均分
 FROM score GROUP BY student_id HAVING AVG( num ) > 80 )
 AS t ON student.sid = t.student_id;

　　#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)解题思路：1.和上面4一样的思路首先我们通过分数表的student_id 进行分组 会得到我们id 一一对应 的 学生id 的总分数和所选课程的总数 2，表内链接通过 分数变产生的虚拟表的学生id链接我们学生表的i的 也就是拼表 

SELECT
	student.sname,
	t.total_num,
	t.total_course 
FROM
	student
	INNER JOIN ( SELECT student_id, COUNT( course_id ) AS
 total_course, SUM( num ) AS total_num FROM score GROUP BY student_id ) 
AS t ON student.sid = t.student_id;

理解 85   3
钢蛋 175 3
张三 329 4
张一 257 4
张二 257 4

-- #6、 查询姓李老师的个数
SELECT COUNT(*) FROM teacher WHERE teacher.tname LIKE "%李%";这个比较简单

-- #7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)

解题思路：1---> 我们先得到所有李平老师的课程icourses_id ,2-----> 从分数表名中获取所有 选择了李平老师的课程 3.对于所有选的学生student_id  去重  4.通过学生表判断 我们的学生 是否 等于 分数布表名的学生 id 然后进行取反

SELECT
	student.sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "李平老师" ) 
	);

　　

#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)
解题思路：
1.首先获得两个课程的de id 
2---》然后 可以得到分数表的学生id和对应的学生分数 
3----》 将两个表的数据分数 通过关联外键然后  将两个变得分数进行比较

SELECT
	t1.student_id 
FROM
	( SELECT student_id, num FROM score  
WHERE course_id = ( SELECT cid FROM course WHERE cname = "物理" ) ) AS t1

	INNER JOIN ( SELECT student_id, num FROM score 
WHERE course_id = ( SELECT cid FROM course WHERE cname = "生物" ) ) AS t2 
ON t1.student_id = t2.student_id 
WHERE
	t1.num > t2.num;

#9、查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，
思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)

9解题思路：
我们需求是没有同事选择体育和物理的学员名字  首先我们先明先获得物理和体育课程的学生信息（1 获得物理和体育课程的id 通过分数表  ---以学生id 字段进行分组 having 进行过滤 得到只选择了课程其中一门的学生信息）


select student.scname from student where sid in 
(select studnt_id from score where courses_id in(
select cid from course  where cname="物理“ or 
 cname="体育”) group by student_id having count(courses_id)=1)；

　

#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)

　

SELECT student.sname from student WHERE sid in(
SELECT student_id from score GROUP BY  student_id 
HAVING count(course_id)=(SELECT count(cid) FROM course));

 

#12、查询李平老师教的课程的所有成绩记录

-- #12、查询李平老师教的课程的所有成绩记录
SELECT * FROM score WHERE course_id in(
SELECT cid from course INNER JOIN teacher on 
course.teacher_id=teacher.tid where teacher.tname="李平老师")
 ORDER BY student_id asc;

　　

#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出

count(student_id)等于学生数即可)
SELECT cid,cname FROM course WHERE cid in (
SELECT course_id FROM score 
GROUP BY course_id 
HAVING count(student_id) =(SELECT count(sid) from student));

　　

#14、查询每门课程被选修的次数
select course_id,count(student_id) from score group  by course_id;
#15、查询之选修了一门课程的学生姓名和学号


SELECT sname,sid FROM student WHERE sid in(
SELECT student_id from score GROUP BY student_id HAVING count(course_id)=1);

#16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select distinct num from score  order by desc;
-- #17、查询平均成绩大于85的学生姓名和平均成绩

1.先找出分数表平均分大于85 的学生id   和学生通过外键 连表 可以得到结果

select student.sname,avg_num  from student inner join

(select student_id ,avg(num) as avg_num from score grouo by student_id having  avg (num)>85) as t1 on student_id=t1.student_id;