and,or,in,distinct,like,@,is null,is not null,where,GROUP BY,聚合函数MAX,MIN,AVG
1.基本概念
SELECT 表上哪些列显示
*表示所有列
//投影运算 指的是选择部分列数据
//友好列标题 SELECT Sno as 学号
//top关键字 SELECT Top 3 * from Student 只显示前3条数据
FROM 这个表来源
WHERE 这个表的情况，进行筛选
2.简单匹配查询
2.1 检索年龄为20岁的学生信息
select * from student where Sage=20
2.2 and
连接多个条件
select * from student where ssex='女'and sage>21
2.3 or
表示达到一个条件即可
between and 在什么条件之间， not between and 取反条件
select sno,sname,ssex from student whrer sno between '2' and '4'
2.4 in
检索一系列的取值列表
select * from teacher where tropt in（'教授','副教授'）
2.5 distinct
表示不检索出相同信息
select distinct tropt from teacher
2.6 like
模糊检索
select sname，sdept from student where sdept like'%学%'
2.7 @
一个字符串与一个下划线进行匹配
select sname,sdept from student where emall like '__@%'
2.8 is null
字段为空
select *from student where low is null
2.9 is not null
字段不为空
select *from student where high is not null
2.10 where
等于单个值 查询有某科目考试分数为48分的学生信息
select *from student where sno=(select sno from sc where grade=48)
3.分组聚合查询
2.1 GROUP BY
进行分组 按照by之后的选择进行分组统计
2.2 实例
对course表，按照必修和选修进行分类，统计每种类别的课程数量
select xklb as 类别，count(cname) as 数量 from course group by xklb
HAVING
ORDER BY ASC||DESC
ASC表示升序，DESC表示降序
select *from student order by sage desc
select *from student order by sage desc,sno asc在sage相同情况下按snow升序对比排列
COUNT
count函数返回匹配行数
select count（*） from teacher where tropt=‘教授’
2.3 聚合函数MAX,MIN,AVG
显示教师的最大最小平均年龄
select max(tage),min(tage),avg(tage) from teacher
指定条件求和
select sum(credit) from course where xklb='必修' 对course表中的必修课的学分进行求和
2.4 混合应用
select smajor,ssex,count(sno) from student group by Smajor ,sex order by count(sno) desc
对student表，按照专业和性别进行分组，显示每个专业，每种性别的学生数量，按照学生数量的降序显示结果
select tropt,count(tropt) from teacher group by tropt having count(tropt)>=5
对teacher表，显示职称和对应的认识，要求只有统计人数大于等于5人才显示
