今天来说一下数据库中对数据的查询,分别用例子来讲解
一、 单表无条件查询
- 查询指定列
select sno,sname,ssex
from student; - 查询所有列
select *
from student; - 使用计算
select sno,cno,grade*0.6
from sc; - 使用列别名
select sno,cno,grade*0.6 as 期末成绩
from sc; - 补充
(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; - 去掉重复
select distinct sno
from sc; - 排序(默认为升序)
select *
from student
order by sage ;
select *
from student
order by sage desc;(降序)
二、 条件查询
- 比较运算符 > < = >= <= <>
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);
三、 统计
- 聚合函数
select sum(grade),avg(grade),max(grade),min(grade),count(grade)
from sc;
查询有多少同学选课了
select count(distinct sno)
from sc; - 分组
查询每位同学平均成绩
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 - 筛选
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 <> ‘刘晨’;