编程题目1

select 查询结果    如: [学号,平均成绩：组函数avg(成绩)]
from 从哪张表中查找数据   如:[涉及到成绩：成绩表score]
where 查询条件    如:[b.课程号='0003' and b.成绩>80] BETWEEN       IS NULL        NOT  IN AND OR
group by 分组    如:[每个学生的平均：按学号分组]
having 对分组结果指定条件    如:[大于60分]
order by 对查询结果排序    如:[增序: 成绩  ASC / 降序: 成绩 DESC];
limit   使用limt子句返回topN（对应这个问题返回的成绩前两名）如:[ limit  2 ==>从0索引开始读取2个]
limit==>从0索引开始 [0,N-1]
组函数: 去重 distinct()  统计总数sum()   计算个数count()  平均数avg()  最大值max() 最小数min() 

多表连接: 
内连接等值链接，只显示关联数据，自链接   FROM tablea AS A INNER JOIN tableb AS B  ON A.key = B.key;  
左外连接就是左表参照显示所有，无关联行null  LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
右连接right join  
组合两个查询union(无重复(过滤去重))和union all(有重复[不过滤去重])

视图：虚拟表本身不包含数据，操作和普通表一样，使用实际表一部分，给用户视图权限保证数据安全
存储过程：一系列SQL操作的批处理，代码封装，复用，预先编译性能好
触发器：触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE  CREATE TRIGGER 语句创建触发器

查找班级语文英语不及格的人数
SELECT 
	class as 班级ID,
	sum(case when 语文score < 60 then 1 else 0 end) as 不及格人数,
	sum(case when 英语score < 60 then 1 else 0 end) as 不及格人数
FROM
	class_score
GROUP BY
	class;0
	
查课程1比课程2高学生学号
SELECT t1.sid,t1.score,t2.score
FROM
	(select sid,score
	from sc
	where cid = '01') as t1
JOIN
	(select sid,score
	from sc
	where cid = '02') as t2
ON t1.sid = t2.sid and t1.score>t2.score

查询平均成绩大于60分的同学的学号和平均成绩
SELECT sid,AVG(score) AS AVG
FROM sc
GROUP BY sid
having avg(score)>60

查询所有同学的学号、姓名、选课数、总成绩
SELECT t1.sid,t1.sname,t2.cnt,t2.total_score
FROM
	(SELECT sid,sname
	FROM student
	GROUP BY sid) AS t1
LEFT JOIN
	(SELECT sid,COUNT(*) AS cnt,SUM(score) AS total_score
	FROM sc
	GROUP BY sid) AS t2
ON t1.sid = t2.sid

查询所有课程成绩小于60分的同学的学号、姓名
SELECT DISTINCT s.sid,s.sname
FROM student AS s
JOIN(
	SELECT sid
	FROM sc
	GROUP BY sid
	HAVING MAX(score) < 60
)AS t
ON s.sid = t.sid

查询没有学全所有课的同学的学号、姓名；
SELECT s.sid,s.sname
FROM student AS s
LEFT JOIN sc	
on s.sid = sc.sid
GROUP BY s.sid
HAVING COUNT(sc.cid)<3

查询学生的总成绩并进行排名
SELECT 
	sid
	,SUM(score) AS total_score
	,rank() over (ORDER BY SUM(score) DESC) AS srank
FROM sc 
GROUP BY sid

找姓氏包含字符：en的所有员工
SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    last_name LIKE '%en%';
    
触发器是一种特殊的存储过程，就相当于是一个事件的触发装置,它在试图更改触发器所保护的数据时自动执行。
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
create trigger tri_stu after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;

查重名的名字
SELECT * FROM employer WHERE name IN 
(SELECT name FROM employer GROUP BY name HAVING COUNT(*) > 1