数据库查询

这篇博客详细介绍了数据库查询的各种方法,包括单表查询、条件查询、统计分析、连接查询和子查询,涵盖比较运算符、逻辑运算符、区间查询、模糊查询、空值判断、聚合函数、分组和HAVING子句等多个方面。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

  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 <> ‘刘晨’;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值