题目2：MySQL练习【多表】

题目1

现有以下两张表：

第一张表名为cust,其表结构为：

字段名	字段说明	是否为主键
studentno	学号，整型	是
name	学生名字，字符串	否
address	学生住址，字符串	否
telno	电话号码，字符串	否

第二张表名为mark,其表结构为：

字段名	字段说明	是否为主键
studentno	学号，整型	是
english	英语成绩，数字型	否
math	数学成绩，数字型	否
computer	计算机成绩，数字型	否

1. 请写出计算所有学生的英语平均成绩的sql
2. 现有5个学生，其学号假定分别为11,22,33，44,55；请用一条sql语句实现列出这5个的数学成绩及其姓名、学生地址、电话号码；
3. 查询所有学生的姓名、计算机成绩，按照计算机成绩从高到低排序；
4. 查询所有总成绩大于240分的学生学号、姓名、总成绩，按照总成绩从高到低排序

-- 写出计算所有学生的英语平均成绩的sql
select avg(english) from mark;

-- 现有5个学生，其学号假定分别为11,22,33，44,55；请用一条sql语句实现列出这5个的数学成绩及其姓名、学生地址、电话号码
select mark.math, cust.name,cust.address,cust.telno
from mark left join cust
on cust.studentno = mark.studentno
where cust.studentno in(11,22,33,44,55);
-- 外连接： A [left] join B 【A主表，B从表  展示A表所有数据】 A right join B 【B主表，A从表  展示B表所有数据】
-- 员工表&部门表  没有部门的员工   没有员工的部门  null

-- 查询所有学生的姓名、计算机成绩，按照计算机成绩从高到低排序；
select cust.name,mark.computer 
from  mark left join cust 
on cust.studentno = mark.studentno
order by computer desc

-- 查询所有总成绩大于240分的学生学号、姓名、总成绩，按照总成绩从高到低排序
select cust.studentno,cust.name,mark.english+mark.math+mark.computer totalMark
from  cust left join mark 
on cust.studentno = mark.studentno
having totalMark>240
order by totalMark desc

-- where  having 共同点：都是条件筛选
-- where 只能对原表中存在的数据
-- having 对临时表/字段 进行条件筛选

题目2

表名	字段	备注
Student	id	学号
	name	学生姓名
Course	id	课程编号
	cname	课程名称
SC	sid	学号
	cid	课程编号
	score	成绩

1. 查询姓‘王’的学生的个数
2. 查询“数学”比“语文”成绩高的所有学生的学号
3. 查询平均成绩大于90分的同学的学号和平均成绩

-- 1.查询姓‘王’的学生的个数
select count(*) as '王姓学生' from stduent where name like '王%';
-- SQL匹配模式
-- MySQL
-- like ：%任意长度的任意字符  _匹配一个任意字符 
-- regexp: 正则 * \d ^ $ [] 匹配[]中有的数据  王[ABC]   [^]匹配除此字符之外的字符 王[^ABC]




-- 2.查询“数学”比“语文”成绩高的所有学生的学号
select chinese.sid from 
-- 语文成绩表
(select s.sid,c.score from sc s join course c on s.cid = c.id where c.cname='语文') as chinese 
join
-- 数学成绩表
(select s.sid,c.score from sc s join course c on s.cid = c.id where c.cname='数学') as math 
on chinese.sid = math.sid
where chinese.score < math.score


-- if(条件){代码块}
-- switch case return 

-- case when 条件 then 结果 else[结果N] end    每一行匹配一次
	-- 1.枚举  case when name='张三' then age end
	-- select (case when cname like '软[^45]' then count end) as count from class
	-- 2.搜索判断
		select 
			case 
				when count>40 then 'XXX'  	-- root  根
				when count<40 then 'YYY'	-- admin1  A
				else '无匹配条件'			 -- admin2	B
			end
		from class

	select * from (
		select s.sid,
			(case when c.cname='语文' then s.score end) chinese
			(case when c.cname='数学' then s.score end) math
		from sc s join course c
		on s.cid = c.id
		where math.score>chinese.score
	)



-- 3.查询平均成绩大于90分的同学的学号和平均成绩
select s.id,avg(score) as sag
from student s join sc c
on s.id = c.sid
group by s.id
having sag >90;

题目3

现有表student，结构和数据如下：

-- ----------------------------