Oracle数据库select语句
时间：2021-07-01 10:21:17
帮助过：9人阅读
* 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 *from emp
where sal
between 1100 and 3000 --select the data of sal 1100 to 3000
select * from emp
where 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
注：每个Select后为一个单独语句，需在结束后加分号“ ; ”，否则无法一起运行
Oracle数据库select语句
标签：har api transform res sda distinct out arc cas
