use srs
select * from t
where tn like '李%'
--从数据表 C 中查询所有课程名中含有“原理”两个字的课程全名。
select cn from c
where cn like '%原理%'
--从数据表C中查询课程编号以“C”开头或者以“P”开头的所有课程信息
select * from c
where cno like 'C%' or cno like 'p%'
select * from c
where cno like '[cp]%'
--从数据表C中查询课程编号以“C”到“D”开头的所有课程信息
select * from c
where cno like '[c-d]%'
--从数据表C中查询课程编号不是以“C”开头或者以“D”开头的所有课程信息
select * from c
where cno like '[^c-d]%'
select * from c
where cno not like '[c-d]%'
--有效成绩
select * from sc
where grade is not null
--聚合函数
select count(Grade) as 有效成绩,max(Grade) as 最高分, min(Grade) as 最低分 from sc
where cno='C0201'
--分组查询
select cno ,COUNT(sno) as num
from sc
group by cno
--1.查询T表中各系的教师人数。
select dept, COUNT(*) as num
from t
group by dept
--2.在SC表中统计每位学生的总分
select sno ,SUM(Grade) as sum
from sc
group by sno
--3、在S表中分别统计各系别男女生的人数
select dept ,sex,COUNT(*) as num
from s
group by dept ,sex
--4,查询SC表C0201课程的最低分(显示课程号、最低分)
select cno ,MIN(grade) as min
from sc
where cno='c0201'
group by CNo
--having
select cno,AVG(grade) 平均分
from sc
group by cno
having AVG(grade)>80
select sno,sum(grade) 总分
from sc
group by sno
having sum(grade)>150