Take the emP table under user Scott as an example
——————————————————————————————————–where
Query the non-repetitive work of the EMP table
select distinct job from emp
Select * from employees whose salary does not equal 1500;
select * from emp where sal <> 1500
Query employees with salaries between 1300 and 1600, including 1300 and 1600
select * from emp where sal between 1300 and 1600;
Select * from employees whose salary is not between 1300 and 1600, excluding 1300 and 1600
select * from emp where sal NOT between 1300 and 1600;
Query the employees whose start time is within the specified period
select * from emp where hiredate between ‘1981/6/9’ and ‘1987/4/19’;
or
select * from emp where hiredate between to_date(‘1981/2/20′,’yyyy/mm/dd’) and to_date(‘1987-4-19′,’yyyy/mm/dd’);
Select * from emp where (deptno=20) or (deptno=30); select * from emp where (deptno=20) or (deptno=30); Select * from emp where deptno in (30,20);
Select * from emp where deptno NOT in (30,20);
Select * from employees whose names contain ‘_’, use \ escape to return the following characters to their original meaning [like ‘%\_%’ escape ‘]
select * from emp where ename like ‘%\_%’ escape ‘\’;
Select * from emp where comm is null; Note: null cannot operate the number/date/varchar2 type operation \
——————————————————————————————————–order by
Query employee information, descending sort by date entry, use the column names to select empno, ename, sal, hiredate, sal * 12 “salary” from emp order by hiredate desc;
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — a single function
Select lower(‘www.BAIdu.COM’) from dual; select upper(‘www.BAIdu.COM’) from dual; select initcap(‘www.BAIdu.COM’) from dual;
Select concat(‘hello’,’ hello’) from dual; Select concat(‘hello’,’ hello’,’ world ‘) from dual; Error select ‘hello’ | | ‘hello’ | | ‘world’ from dual; Select concat(‘hello’,concat(‘ hello’, ‘world ‘)) from dual; Select substr(‘hello ‘,5,3) from dual; 5 indicates the number of consecutive characters. The first character is 1. 3 indicates the number of consecutive characters
Select length(‘hello ‘) from dual select length(‘hello ‘) from dual select length(‘hello ‘); Select lengthb(‘hello ‘) from dual;
Select instr(‘ helloWorld ‘,’o’) from dual; select instr(‘ helloWorld ‘,’o’) from dual; Select LPAD(‘hello’,10,’#’) from dual; – left the ‘#’ length 10 select RPAD (‘ hello ‘, 10, ‘#’) from dual; — Right add ‘#’ length 10
Select trim(‘ ‘from’ he ll ‘) from dual; select replace(‘hello’,’l’,’L’) from dual;
Select round(3.1415,3) from dual; The select trunc (3.1415, 3) from dual; Select the mod (10, 3) from dual;
Current date: sysdate = 2016/5/24
Select round(sysdate,’month’) from dual; – 2016/6/1
Select round(sysdate,’year’) from dual; – 2016/1/1
Select trunc(sysdate,’month’) from dual – 2016/5/1
Select trunc(sysdate,’year’) from dual; – 2016/1/1
Select sysdate-1 “yesterday “,sysdate” today “,sysdate+1 “tomorrow” from dual;
Select ename,round(sysdate-hiredate,0)/365 “days” from emp select ename,round(sysdate-hiredate,0)/365 “days” from EMp select ename,round(sysdate-hiredate,0)/365 “days” from EMp;
Select Months_BETWEEN (’31- December -15′,sysdate) from dual select months_between(’31- December -15′,sysdate) from dual;
Select ename “name “, months_BETWEEN (sysdate,hiredate)” Months_length “from EMp;
Select months(sysdate,1) from dual; select months(sysdate,1) from dual;
Select add_months(sysdate,-1) from dual; select months(sysdate,-1) from dual;
【 英 文 版 】 select next_day(sysdate,’ Wednesday ‘) from dual;
Select next_day(sysdate,’ Wednesday ‘) from dual; select next_day(sysdate,’ Wednesday ‘) from dual;
Select next_day(next_day(sysdate,’ Wednesday ‘),’ Sunday ‘) from dual;
Select last_day(sysdate) from dual select last_day(sysdate) from dual;
Select last_day(sysdate)-1 from dual select last_day(sysdate)-1 from dual select last_day(last_day)-1 from dual
Select last_day(add_months(sysdate,1)) from dual; select last_day(last_day(add_months(sysdate,1)) from dual;
Select last_day(add_months(sysdate,-1)) from dual select last_day(last_day(add_months(sysdate,-1)) from dual;
Note: 1) date – date = days 2) Date +- Days = date
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — three type conversion
(1) varchar2/char fixed length –>number, e.g. ‘123’ –> 123 (2) varchar2/char–>date, e.g. ’25 April – 15′ – > ’25 April – 15′ (3) the number — — — — > varchar2 / char, for example: 123 – > ‘123’ (4) the date — — — — — – > varchar2 / char, for example: ’25 April – 15′ – > ’25 to April 15′
Select * from emp where hiredate = ’17-12 -80′;
Use the to_char(date, ‘lattice’ constant ‘) function to convert the date to a string in the following format: Select TO_char (sysdate,’yyyy ‘) from dual; select to_char(sysdate,’ yyyY-MM-dd ‘) from dual;
Use the to_char(date, ‘format ‘) function to convert the date to a string, as shown in the format: Select to_char(sysdate,’ YYYY-MM-DD ‘,’ hh24:mi:ss’) from dual; Select to_char(sysdate,’ YYYY-MM-DD ‘today is “day HH12:MI:SS AM’) from dual;
Select to_char(1234,’$9,999′) from dual; select to_char(1234,’$9,999′) from dual;
Use the to_char(value, ‘format ‘) function to convert the value to a string in the format ￥1,234
Select to_char (1234, ‘$9999) from dual; L9, select to_char (1234, ‘999’) from dual;
Use to_date(‘ string ‘,’ format ‘) to query for employees who started on December 17, 1980 (option 2: Select * from emp where hiredate = to_date(‘ yyyy-dd ‘,’ yyyy-DD ‘); Select * from emp where hiredate = to_date(‘1980#12#17′,’ YYYY “#”mm”#” DD ‘); Select * from emp where hiredate = to_date(‘1980-12-17′,’ YYYY-MM-DD ‘);
Select to_number(‘123’) from dual select to_number(‘123’) from dual;
Select ‘123’ + 123 from dual; 246 select ‘123’ || 123 from dual; 123123