sql select 语句大部.docVIP

sql select 语句大部 --------------mytieba---niit/niit----------- select * from t_tieba_dir1; select CLUB_DIR1_NAME ,COUNT_CLUBS from t_club_dir1; create table t as select CLUB_DIR1_NAME ,COUNT_CLUBS from t_club_dir1; select * from t_club_dir1 where count_clubs=0; select * from t_club_dir1 where 1=1; select * from t_club_dir1 where 1=2; -----------scott/tiger------------- select * from emp where deptno=20; select * from emp where sal2000; select * from emp where comm is null; select * from emp where comm is not null; select emp.*,sal+comm from emp; select emp.*,sal+nvl(comm,0) from emp; select emp.*,nvl2(comm,comm,0) from emp; select emp.*,to_char(hiredate,yyyy-mm-dd) from emp; select emp.*,to_char(hiredate,yyyy年mm月dd日) from emp; select emp.*,to_char(hiredate,yyyy年mm月dd日) as chinese日期 from emp; insert into emp values(8888,9527,clerk,7698,to_date(1999-9-9,yyyy-mm-dd),888,88,20); select emp.* from emp where hiredateto_date(1981-1-1,yyyy-mm-dd) and hiredateto_date(1982-1-1,yyyy-mm-dd) select emp.* from emp where hiredate between to_date(1981-1-1,yyyy-mm-dd) and to_date(1982-1-1,yyyy-mm-dd) select emp.* from emp where trunc(hiredate,yyyy)= trunc(to_date(1981,yyyy),yyyy) select trunc(sysdate,yyyy) from dual select sysdate from dual; -----聚合函数------- select count(*) from emp; select count(job) from emp; select distinct(job) from emp; select count(distinct(job)) from emp; select max(sal),min(sal),avg(sal) from emp; -----模糊查询----%--_------ select * from emp where empno like %8% select * from emp where empno like %8% or empno like %9% select * from emp where ename like %A% or ename like %O% select * from emp where ename like %A% and ename like %O% select * from emp where ename not like %A% and ename not like %O% select * from emp where sal99 and sal1000; select * from emp where sal like ___; ---------集合操作符------------------- create table t as select * from emp where deptno in(20,30) create table m as select * from emp where deptno in(10,30) ------ select * from t minus s