unit2:限制条件查询，模糊查询，多条件查询，别名，排序
--创建表
CREATETABLEEMPLOYEE(
EMIDNUMBER(6),
NAMEVARCHAR2(10),
AGENUMBER(3),
JOBVARCHAR2(10),
SALARYNUMBER(10)
)
--插入数据
INSERTINTOEMPLOYEE(EMID,NAME,AGE,JOB,SALARY)VALUES(1,'TOM',20,'PROGRAMMER',2000)
INSERTINTOEMPLOYEE(EMID,NAME,AGE,JOB,SALARY)VALUES(2,'HARRY',20,'PROGRAMMER',3000)
INSERTINTOEMPLOYEE(EMID,NAME,AGE,JOB,SALARY)VALUES(3,'ANN',20,'CODING',1000)
INSERTINTOEMPLOYEE(EMID,NAME,AGE,JOB,SALARY)VALUES(4,'SUNNY',25,'CODING',1500)
INSERTINTOEMPLOYEE(EMID,NAME,AGE,JOB,SALARY)VALUES(5,'LEE',27,'TEAMLEADER',8000)
INSERTINTOEMPLOYEE(EMID,NAME,AGE,JOB,SALARY)VALUES(6,'BLACK',18,'BOSS',10000)
INSERTINTOEMPLOYEE(EMID,NAME)VALUES(6,'JOR')
COMMIT
--查询
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
--条件查询
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGE=20
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGE>20
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGE>=25
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGE!=25
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGEISNULL
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGEISNOTNULL
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGEISNOTNULL
--BETWEENAND,NOTBETWEENAND
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGEBETWEEN20AND25
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGENOTBETWEEN20AND25
--IN,NOTIN
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGEIN(25,27)
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGENOTIN(25,27)
--模糊查询
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHERENAMELIKE'A%'
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHERENAMELIKE'%Y'
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHERENAMELIKE'HARR_'
--多条件查询
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGE>=25ORSALARY>3000
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEWHEREAGE>=25ANDSALARY>3000
--使用算术表达式别名
SELECTEMID,NAME,AGE,JOB,SALARY,SALARY*12FROMEMPLOYEE
SELECTEMID,NAME,AGE,JOB,SALARY,SALARY*12ASANNUALFROMEMPLOYEE
SELECTEMID,NAME,AGE,JOB,SALARY,SALARY*12ANNUALFROMEMPLOYEE
--使用连接符号
SELECTNAME||'SALARYIS'||SALARYASINFOFROMEMPLOYEE
--显示单一记录
SELECTDISTINCTAGEFROMEMPLOYEEWHEREAGE=20
--排序
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEORDERBYAGE
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEORDERBYSALARY
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEORDERBYAGE,SALARY
--对查询结果排序
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEORDERBYSALARY
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEEORDERBYSALARYDESC
--ANDOR优先级别
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
WHEREAGE=20OR
AGE=25ANDSALARY>1500
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
WHEREAGE=20OR
(AGE=25ANDSALARY>1500)
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
WHEREAGE=20ANDSALARY>1500
ORAGE=25
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
WHERE(AGE=20ANDSALARY>1500)
ORAGE=25
--别名，排序
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
WHERESALARY>2000
ORDERBYSALARYDESC
SELECTEMID,NAME,AGE,JOB,SALARYFROMEMPLOYEE
WHERESALARY>2000
ORDERBYSALARYDESC
--查询工资排名为6到10的信息
select*
from(selecta.*,rownumasrownum1
from(select*fromemporderbysaldesc)a
whererownum<=10)
whererownum1>5
