人大金仓数据库的单表查询
查看表的内容
\d exam.course
select * from exam.course ;
select id,name exam.couese;
在人大进仓数据库中双引号用于起别名使用而单引号则大部分用于数输出字符段使用
连接运算
select iname || '的职称是' || title as "讲师信息" from exam.instructor;
算数运算符
select current_date()+7 as nextwork;
select id+5 from t03^;
条件表达式
select sno,sno,score,case when score >= 90 then '高' when score <90 and score <=70 then '中' else '低' END as score_level from exam.score ;
where 子句
select * from exam.score where score > 80;
select * from exam.student where city='Beijing';
select * from exam.score where sno=1001 and score>85;
逻辑运算
和其他语言一样逻辑运算包括与、或、非分别使用AND OR NOT 表示
逻辑运算的优化级NOT>AND>OR
select 1 from dual where 1=1 or 1=0 and 1=0;
select 1 from dual where not 1=0 and 1=0;
使用括号可以改变优先级
模糊查询
select * from exam.student where city like 'B%';
select * from exam.student where city like '%g';
select * from exam.student where city like '_e%';
范围查询
select * from exam.student where reg_date between '2021-01-01' and '2021-10-01';
空值查询
select * from exam.student where company is null;
order by 语句
select sname from exam.student order by reg_date ;
select sname from exam.student order by reg_date asc;
select sname as name,reg_date as date from exam.student order by date asc;
select sname,reg_date from exam.student order by 2;
select sname,reg_date,city from exam.student order by city,reg_date;
限制返回行数
select sname from exam.student limit 2;
select * from (select rownum rn, sname,city from exam.student) where rn<=3;
select sname,city from exam.student offset 3 rows fetch next 3 rows only;
去除重复值查询
select count(distinct city) from exam.student ;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)