数据库查询

今天来说一下数据库中对数据的查询，分别用例子来讲解
一、 单表无条件查询

1. 查询指定列
   select sno,sname,ssex
   from student;
2. 查询所有列
   select *
   from student;
3. 使用计算
   select sno,cno,grade*0.6
   from sc;
4. 使用列别名
   select sno,cno,grade*0.6 as 期末成绩
   from sc;
5. 补充
   （1） 查询系统当前日期
   select current_date from dual;
   （2） 查询系统当前年份
   select year(current_date) from dual;
   （3） 查询系统当前月份
   select month(current_date) from dual;
   （4） 查询系统当前日子
   select day(current_date) from dual;
   （5） 根据年龄查询学生出生年份
   select sno,sname,year(current_date)-sage as 出生年份
   from student;
6. 去掉重复
   select distinct sno
   from sc;
7. 排序(默认为升序)
   select *
   from student
   order by sage ;

select *
from student
order by sage desc;（降序）
二、 条件查询

1. 比较运算符 > < = >= <= <>
   select *
   from student
   where ssex <> ‘男’;

select *
from student
where sage >= 20;
2. 逻辑运算符 and or not
select *
from student
where sage >= 20 and ssex = ‘男’;

select *
from student
where sage >= 20 or ssex = ‘男’;
3. 区间运算符
select sno,cno,grade
from sc
where grade between 60 and 90;

select sno,cno,grade
from sc
where grade not between 60 and 90;
4. 模糊查询 like 通配符： % _
select *
from student
where sname like ‘张%’;

select *
from student
where sname like ‘张_’;

select *
from student
where sname like ‘张__’;

select *
from course
where cname like ‘%数据%’;
5. 空值
select *
from student
where sage is null;

select *
from student
where sage is not null;
6. 列表运算符 in
年龄：18 19 21 22
Sage In(18, 19, 21, 22)
Dno in(‘CS’,’IS’)
select *
from student
where sage in(18,19,21,22);
三、 统计

1. 聚合函数
   select sum(grade),avg(grade),max(grade),min(grade),count(grade)
   from sc;
   查询有多少同学选课了
   select count(distinct sno)
   from sc;
2. 分组
   查询每位同学平均成绩
   select sno,avg(grade)
   from sc
   group by sno;
   查询每门课平均成绩
   select cno,avg(grade)
   from sc
   group by cno;
   查询男女生各有多少人
   select ssex, count(sno)
   from student
   group by ssex;
   查询每个系男女生各有多少人
   Select dno, ssex, count(sno)
   from student
   group by dno,ssex;
   查询的语法顺序
   Select
   From
   Where
   Group by
   having
   Order by
3. 筛选
   Having：当条件中含有聚合函数时
   是对group by结果的排查
   跟在group by后面
   Where 对表里原始数据的排查
   跟在from后面
   查询选修了3门以上课程的学生
   select sno,count(cno)
   from sc
   group by sno
   having count(cno) >= 3;
   四、 连接查询
   Select
   From
   Where
   Group by
   having
   Order by
   查询刘晨的成绩
   select grade
   from student join sc on student.sno = sc.sno
   where sname = ‘刘晨’;
   数据库课程的成绩
   Select grade
   From sc join course on sc.cno = ourse.cno
   Where cname = ‘数据库’;
   五、 子查询
   谁和刘晨同岁？
   select sno,sname
   from student
   where sage = (select sage
   from student
   where sname = ‘刘晨’)
   and sname <> ‘刘晨’;