oracle数据库的select,Oracle数据库select语句

select * from EMp--all data in EMP table

select * from EMP where ename in('SMITH')--the data where ename is SNITH in the EMP table

select ename||'is a' as INAME from EMP where eNAME = 'SMITH'--rename ENAME pius 'is a' to INAME

select sal || '_'||ename as employees from emp --sal plus ename to employees

select distinct sal from emp--delete the same rows

select * from emp order by sal desc--show the all data in the sal's data to up order

select * from emp where hiredate = '03-12月-81'--show the data when the hiredate is '1981-12-03'

select *fromempwhere sal between 1100 and 3000 --select the data of sal 1100 to 3000

select * fromempwhere deptno in (10,20)--where the deptno is 10 or 20

select * from emp where ename like '%_'--maybe

select * from emp where comm is null--nothing

select * from emp where lower (ename) = 'smith' --change the word to lowercase

select initcap(ename) from (select lower(ename) as ename from emp)--change the first letter to capital

select concat('hello','world')from dual--attach the two word

select substr('goodgoodstudydaydayup',5,9)from dual--cut nine letter start for five from the words

select length('goodgoodstudydaydayup')from dual--how many letter in the words

select instr('goodgoodstudydaydayup','s')from dual--the index of the letter 's' in the words

select lpad('goodgoodstudydaydayup',30,'-')from dual--make the words has 30 letters with the assignation words in the end,others fill in '-'

select rpad('goodgoodstudydaydayup',30,'-')from dual--make the words has 30 letters with the assignation words in the beginning,others fill in '-'

select trim('p' from 'goodgoodstudydaydayup')from dual--delete the letter 'p' from the starting or the end of the words

select round(3.1415926535,4)from dual--retention 4 decimal point with rounding of the number

select trunc(3.1415926535,4)from dual--cut 4 decimal point from the number

select mod(10,3)from dual --get the remainder

select sysdate from dual--get the currentdate of the system

select hiredate+5 from emp--the date plus 5,result is new date

select months_between('01-8月-95','11-7月-94')from dual--the defference of the two date

select add_months('01-7月-90',7)from dual--the date add to 7 months

select * from emp order by sal desc--descending order

select next_day(sysdate,'星期一')from dual--the date of the next monday

select last_day(sysdate)from dual--the last day of the month of the assignation date

select replace('goodgoodstudydaydayup','d','y')from dual--replace 'd' to 'y'

select to_char(sysdate,'yyyy')from dual--the year of currentdate

select to_char(sysdate,'fmyyy-mm-dd')from dual--format the system date to year-month-day

select to_char(sal,'$999,999,999')from emp--format the currency

select to_number('13')+to_number('23')from dual--transform varchar to number

select to_date('19900725','yyyymmdd')from dual--transform from varchar to date

select *from emp where hiredate = last_day(hiredate)-2--reciprocal 3 days

select *from emp where hiredate <= add_months(sysdate,-25*12)--25years ago

select initcap(concat('dear',ename)) from emp--the front of all ename add to 'dear',the change the first letter to capital

select * from emp where length(ename) = 5--whose the length of name is 5

select *from emp where ename not like ('%R%')--whose name without the letter 'R'

select substr(ename,0,1) from emp --select the first letter of name