oracle常用查询语句

 

select count(*)人数 from tb_student where sage having sage<23;
select *from tb_student;( 按关键字排序)
selcet * from tb_student order by sage desc;(按年龄排序)
selcet * form tb_student order by (字段名) desc;

select sname NAME,2010-sage 出生年月 from tb_student;
select sname 姓名,'出生：',2010-sage 出生年，lower(sdept) from tb_student;
select sname 姓名,'出生：' ,2010-sage 出生年，lower(sdept) from tb_student;
select distinct username 姓名 from tb_user;//消除姓名相同的行
select username,sdept,sage from tb_student where sage<22;
select username,sdept,sage from tb_student where not sage>=22;
select sname,sage from tb_student where sage between 20 and 23;
select sname,sage from tb_student where sdept in('部门名'，'部门名');
select snmae,sage sdept from tb_student where sdept='部门名' or sdept='部门名';
select sname,sage,sdept from tb_student where sname like '王%';//查询所有姓王的信息
==select sname,sage,sdept from tb_student where sname = '王%';
select sname,sage,sdept from tb_student where sname like '%王%';//查询姓名中有王字的人
select sname,sage,sdept from tb_student where sname like '_青%';//查询名为青的人（下划线表单个字）
select sname,sage,sdept from tb_student where sname like '_王%';//查询第二个字为王的人
select * from tb_course where cname like 'DB/_%' escape '/';//查询以‘DB'开头
select * from tb_course where cname like 'DB/_%i_ _' escape '/';//查询以‘DB'开头，且倒数第3个字符为i
select * from tb_course where cname like 'DB/_%i____' escape '/';//查询以‘DB'开头，且倒数第四个字符为n
select sno,cno,grade from tb_sc where grade is not null; //查询grade字段非空
select sno,sname from tb_student where sno in(select sno from tb_sc);//两张表合起来使用
select * from tb_student where sname='胡毅' and ssex='男' ;//and的使用方法

select * from tb_student order by sdept asc, sage desc;//降序排列
select count(sno) 记录数 from tb_student;//求sno总数量
select count(sno) 记录数 ,sum(grade) 分数总和,min(grade) 最低分,max(grade) 最高分,avg(grade) 平均分
from tb_sc where grade is not null;//求记录数，求分数总和，求分数中的最低分，求分数中的最高分，求平均分

select tb_student.*,tb_sc.* from tb_student,tb_sc; //广义笛卡尔积,不带连接词
select tb_student.*,tb_sc.* from tb_student,tb_sc where tb_student.sno=tb_sc.sno;//通过隐含条件主键和外键进行连接
select a.*,b.* from tb_student a,tb_sc  b where a.sno=b.sno;用表的别名进行替换
select tb_student.sno,tb_student.ssex,tb_student.sname,tb_student.sdept,tb_student.scome,tb_sc.cno,tb_sc.grade
from tb_student,tb_sc where tb_student.sno=tb_sc.sno;
select a.sno 编号,a.ssex 姓名,a.sname,a.sdept,a.scome,b.cno,b.gradefrom tb_student a,tb_sc  b where a.sno=b.sno;
select a.sno 编号,a.ssex 姓名,a.sname,a.sdept,a.scome,b.cno,b.grade
from tb_student a,tb_sc  b where a.sno=b.sno and grade is not null;//多表进行连接
select first.cno,second.cpno from tb_course first,tb_course second where first.cpno=second.cno ;//自身连接必须新建别名
select a.sno,a.sname,a.ssex,a.sage,a.sdept,b.cno,b.grade from tb_student a left join tb_sc b on a.sno=b.sno
and b.grade is not null; //左连接，left join ...   on...
select a.sno,a.sname,a.ssex,a.sage,a.sdept,b.cno,b.grade from tb_student a right join tb_sc b on a.sno=b.sno
and b.grade is not null;//右连接   right join ...on
select tb_student.sno,tb_student.sname from tb_student,tb_sc where tb_student.sno=tb_sc.sno and
tb_sc.cno='200806003' and tb_sc.grade>80;//复合条件连接（查询选修2号课程且成绩在90分以上的所有学生的学号、姓名）
select a.sno 编号,a.sname  姓名 from tb_student a where a.sno in(select sno from tb_sc b where
cno='200806001' and b.grade<80);//对上条语句 的优化
//多表连接（查询每个选修了课程的学生的学号、好名、选修的课程名及成绩）
select sname from tb_student where sno in(select sno from tb_sc where cno='200806003');//嵌套查询，将一个select ..from..where
语句嵌套在另一个select..from ..where 中须用到in
select sno,sname from tb_student where sno in(
select sno from tb_sc where cno in(
select cno from tb_course where tb_course.cname='数学'));
select sname,sno,sdept from tb_student  where sdept>all(select sdept from tb_student where sname='张飞' );//ALL的用法（所有值）
select sname,sno,sdept from tb_student  where sdept>any(select sdept from tb_student where sname='张飞' );//any的用法（表示任意一个）
select sname,sage from tb_student where sage<any(select sage from tb_student where sdept='数学') and sdept<>'数学';
select sname from tb_student where exists(
select sno from tb_sc where sno=tb_student.sno and cno='200806001');//exists的用法，表示存在的意思，返回结果只有真值和假值之分
select sname from tb_student where not exists (select * from tb_sc where sno=tb_student.sno and cno='200806001');//not exists
select * from tb_student where sdept='数学'
union
select * from tb_student where sage<22;//union的用法，联合类型，可用or来取代，即求并集
select * from tb_student where sdept='数学'
intersect
select * from tb_student where sage<23;//intersect的用法，取两条select 语句的交集，可用and来取代，
select * from tb_student where sdept='数学'
minus
select * from tb_student where sage<23;//minus的用法，差集的用法
//order by子句只能用于对最终查询结果排序，不能对中间结果进行排序
--desc dual;
select 18*27 from dual;//Dual表是一个实际存在的表，任何用户均可操作，主要用于测试、
//同时插入到多表
MERGE//表的合并
select sname ||'出生年份是' ||sage from tb_student where sdept like '%数学%';// 将多个文本段合并起来，显示结果是sname的出生年份是sage
select * from tb_student where scome=to_date('2008-9-1','yyyy-mm-dd');//查询时日期格式的用法
select tb_student.sname,tb_sc.cno from tb_student natural join tb_sc;//自然连接（natural join）会把重复的字段去掉
select sysdate from dual;//查询系统默认时间
select systimestamp from dual;//查询详细时间
select current_date from dual;//当前时间
select current_timestamp from dual;//当前详细时间
select user from dual;//当前用户
select trunc(sysdate) from dual;//只查询日期
select last_day(sysdate)+10 from dual;
select months_between(sysdate,to_date('1989-03-19','yyyy-mm-dd')) from dual;//查询自己出生了多少个月
select sname,to_char(2011-sage,'9999.99') from tb_student;//数据类型转换
select sno,sname,decode(ssex,'男',1,'女'，0),sdept from tb_student;
create table tb_sc1 as(select * from tb_sc where grade is not null);//在当前表上创建新表，注意as 关键字的使用