http://www.javaask.com/database/oracle/2011/0305/3471.html
select count(*)人数 from tb_student where sage having sage<23;
select *from tb_student; ( 按关键字排序)
selcet * from tb_student order by sage desc; (按年龄排序)
select sname NAME,2010-sage 出生年月 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 * 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'开头,且倒数第四个字符为i
select sno,cno,grade from tb_sc where grade is not null;
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总数量,,,count 计算非空列值
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.grade
from 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 关键