简单查询 where 条件 分组查询

简单查询 where 条件 分组查询

6.简单查询

--①详细查询
select *
from Student;

--②列别名 空格 + '别名'
select Sno '学号',Sname '名字'
from Student;

--③在学生每个姓名后显示额外内容 逗号 + '显示内容'
select Sname,Sname,'出生日期'
from Student;

--④运算的表达式
select age 年龄,2020-Sage 出生年份
from Student;

--⑤函数
select *
from SC;
--最大成绩
select MAX(grade) 
from SC;

--⑥ 字符串
select Sname,'2020' '入学年份'
from Student;

--⑦取消重复
select  Sno
from SC;

select distinct Sno
from SC;

--学号数量
select COUNT(distinct Sno)
from SC;

7.where 条件

--1.确定大小
select Sname,Sage
from Student
where Sage>20;

select Sname,Sage
from Student
where not Sage<20;

--2.确定范围
select Sname,Sage
from Student
where Sage between 20 and 23;

--3.确定集合
select Sname,Sdept
from Student
where Sdept not in ('MA','IS');

--4.字符串匹配
select Sname,Sdept
from Student
where Sname like '张%';

--5.一个下划线代表一个汉字 两个字符
select Sname
from Student
where Sname like '欧阳%';

select Sname
from Student
where Sname like '欧阳_';

select Sname
from Student
where Sname like '欧阳__';

--6.指明通配符 \
select Sname 
from Student
where Sname like '%雄%';

select Sname 
from Student
where Sname like '%\_%' escape '\';

--7.空值查询 is null
select *
from Course
where Cpno is null;

select *
from Course
where Cpno is not null;

--8.多重条件查询 and or
select Sname,Sage,Sdept
from Student
where Sdept = 'CS' and Sage<20;

select Sname,Sage,Sdept
from Student
where Sdept = 'CS' or Sage<20;

select Sname,Sage,Sdept
from Student
where Sdept = 'MA' or Sdept = 'IS';

--9 .排序
select *
from SC
order by grade asc;--从小到大

select *
from SC
where Sno = '201215121'
order by grade desc;--从大到小

select *
from Student
order by Sdept,Sage desc;

--10.聚集函数
select COUNT( distinct Sdept)
from Student;

select AVG(Grade)
from SC
where Cno = '1';

select sum(ccredit)
from SC,Course
where Sno = '201215121' and SC.Cno = Course.Cno;

select MIN(Grade),MAX(Grade)
from SC;

8分组查询 先分组 再查询

select Cno,COUNT(Sno) '学生人数'
from SC
group by Cno;

--count(*)代表元组个数
select Sno 
from SC
group by Sno
having count(*) > 2;

--查询最低成绩学号 error  'SC.Sno' 无效，因为出现了聚集函数,该列没有包含在聚合函数或 GROUP BY 子句中。
select sno,MIN(grade)
from SC;

select sno,grade
from SC
where Grade in (select MIN(grade)
						from SC);

select *
from Student
where Sage > (select AVG(Sage)
						from Student );	

select Sno,AVG(grade)
from SC
group by Sno;