oracle---相关的select

来源：互联网发布：淘宝在哪里投诉卖家编辑：程序博客网时间：2024/05/16 15:10

Select专题

一、基础

1.基本的Select 查询语句

语法：

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

（1）查询所有的列：

select * from dept;

select dname,deptno,loc from dept;

*的效率低

（2） Sql语句的书写规范：

1.大小写不敏感

2.可以分行写

3.关键字不能分行和和缩写

4.条件一般分行写

select * FROM EMP;

SELECT *

FROM emp;

（3）数学表达式：+ - * /

select ename,sal+300 from emp;

运算符的优先级：

*、/ > + - 同级从左到右进行运算，( )优先级最高

select ename,sal,12*sal+100 from emp;

select ename,sal,12*(sal+100) from emp;

（4） NULL 是一个不可用的值，不同于0或空行

select ename,sal,comm from emp;

select ename,sal,12*sal*comm from emp;

（5）定义字段column别名 alias

可以直接在字段column名后，也可以是column名 as 别名alias

需要双引号如果包含空格或特殊字符，再者是为了大小写敏感

select ename name,sal,12*sal*comm from emp;

select ename name,sal,12*sal*comm as "Annual Salary" from emp;

（6）连接字符 ||

select ename||job as "Employees" from emp;

select ename||' is a '||job as "Employees" from emp;

(7)常量：字符，数字，日期等（在Select中）

日期和字符必须要单一行单引号''包围起来

select ename ||' is a'||job as "Employee Details" from emp;

（8）处理重复的行 distinct

select deptno from dept;

select distinct deptno from emp;

二、约束和排序数据

语法：

SELECT*|{[DISTINCT] column|expression [alias],...}

FROMtable

[WHEREcondition(s)]

[ORDER BY];

（1） WHERE 条件

日期和字符必须要单一行单引号''包围起来，

字符串的值是大小写敏感的，日期date的值是格式敏感的（默认：DD-MON-RR）

1. 约束列

select * from emp where deptno=30;

select * from emp where ename='KING';

2. 比较：=, >,>=, <,<=,<>

select * from emp where sal>2000;

3. between ... and ...,in(set),like, is null

select * from emp where sal between 2000 and 2500;

select * from emp where mgr in (7698,7839,7788);

//%>=0 _=1

select * from emp where ename like 'A%';

select * from emp where ename like '_A%';

//转义字符 ESCAPE

SELECT last_name, job_id

FROM employees

WHERE job_id LIKE '%SA\_%' ESCAPE '\';

select * from emp where mgr is null;

4. 逻辑条件： and ,or,not

select * from emp where sal>1000 and job like '%MAN%';

select * from emp where sal>1000 or job like '%MAN%';

select * from emp where job not in ('SALESMAN','CLERK');

5.约束的优先级

1Arithmetic operators 算术运算符

2Concatenation operator 链接运算符

3Comparison conditions 比较条件

4IS [NOT] NULL, LIKE, [NOT] IN

5[NOT] BETWEEN

6NOT logical condition

7AND logical condition

8OR logical condition

select * from emp where job='SALESMAN' or job='CLERK' and sal >=1300;

select * from emp where (job='SALESMAN' or job='CLERK') and sal >=1300;

（二） ORDER BY 排序，放在SELECT语句的最后

ASC: ascending order, default 升序

DESC: descending order 降序

1. select * from emp order by hiredate;

2. select * from emp order by hiredate desc;

3. 按照别名排序

select ename,sal,12*sal annsal from emp order by annsal;

4. 利用多列进行排序

select ename,deptno,sal annsal from emp order by deptno,sal desc;

三、函数

（1）字符串函数

select ename,lower(ename) from emp; //转化为小写

select upper('Sql course') from dual;//转化为大写

select initcap('sql course') from dual;//转为驼峰写法

dual table ：是和数据字典以前生成的一张表，dual是在SYS的Schema中，但能被所有的用户使用

dual表有一个DUMMY的字段，有一X的值

select * from emp where ename='smith'; //没记录

select * from emp where lower(ename)='smith';//有记录

select concat('Hello','World') from dual; // 字符串连接

select substr('HelloWorld',1,5) from dual;

select length('HelloWorld') from dual;

select instr('HelloWorld','W') from dual;

select lpad(sal,10,'*') from emp;

select rpad(sal,10,'*') from emp;

select trim('H' from 'HelloWorld') from dual;

（2）数值函数

select round(45.926,2),round(45.923,0),round(45.923,-1) from dual;

select trunc(45.926,2),trunc(45.923,0),trunc(45.923,-1) from dual;

select mod(1600,300) from dual;

（3）日期函数

可以+ - 一个日期；

两个日期相减得到两日期间的间隔天数；两日期不能相加

select sysdate from dual;

select sysdate+1 from dual;

select sysdate-1 from dual;

select ename,hiredate, (sysdate-hiredate)/365 from emp;

select sysdate+24/24,sysdate+33/24 from dual;

months_between(date1,date2)= (date1-date2)

select months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.

29','yyyy.mm.dd')) from dual;

add_months(date,number)=(date.month+number)

select sysdate as today ,add_months(sysdate,1) as next_month,

add_months(sysdate,-1) as last_month from dual;

next_day(date,'day')

select next_day(sysdate,5) from dual;

select next_day(sysdate,'星期五') from dual;

last_day(date)

select last_day('01-4月-95') from dual;

round(date,'format')

select round(sysdate,'month') from dual;

select round(sysdate,'year') from dual;

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'year') from dual;

（4）转化函数

a) to_char(date,'format_model')

format_model: 必须加单引号'' ，大小写敏感

YYYY(Full year in numbers)

YEAR(Year spelled out)

MM(Two-digit value for month)

MONTH(Full name of the month)

MON(Three-letter abbreviation of the month)

DY(Three-letter abbreviation of the day of the week)

DAY(Full name of the day of the week)

DD(Numeric day of the month)

HH or HH12 or HH24

select ename,to_char(hiredate,'fmDD Month YYYY') from emp;

b) to_char(number,'format_model')

9 代表数字

$ 美元符号

L 本地货币符号

. 小数点

, 千分割符

select ename,to_char(sal,'L999,999.00') from emp;

c) to_number(char[,'format_model'])

select to_number('1234') from dual;

select to_number('1234','9999') from dual;

d) to_date(char[,'format_model'])

select to_date('020908','ddmmyyyy') from dual;

（5）通用函数

a) nvl(expr1,expr2) ，如果expr1为null，则用expr2代替

select ename,sal,nvl(comm,0) from emp;

b) nvl2(expr1,expr2,expr3)，如果expr1不为null，则用expr2代替，否则用expr3代替

select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp;

c) nullif(expr1,expr2)

case when expr1=expr2 then null else expr1 end

d) case表达式

语法：

case expr when comparison_expr1 then return_expr1

[when comparison_expr2 then return_expr2

[when comparison_exprn then return_exprn

else else_expr]

end

select ename,job,sal,case job when 'CLERK' then 1.10*sal

when 'MANAGER' then 1.20*sal

else sal end "revised_salary"

from emp;

select ename,job,sal,case when job='CLERK' then 1.10*sal

when job='MANAGER' then 1.20*sal

else sal end "revised_salary"

from emp;

四、多表连接

连接类型：等值连接，不等值连接，外连接，自连接

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

a) 等值连接 equijoins

select ename,dname

from emp,dept

where emp.deptno=dept.deptno;

select ename,dname

from emp,dept

where emp.deptno=dept.deptno

and emp.deptno=30;

select e.ename,d.ename

from emp e,dept d

where e.deptno=d.deptno;

select ename,dname

from emp e,dept d,

where e.deptno=d.deptno

Joining More than Two Tables

hr>select e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY

from employees e,departments d,locations l

where e.department_id=d.department_id and d.location_id=l.location_id

b) 不等值连接 nonequijoins

select ename,sal,grade

from emp e,salgrade s

where e.sal between s.losal and s.hisal

c)外连接 outer joins

展示两表不满足条件的column,+在那边表示多展示此表不满足的column

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column = table2.column(+);

select e.ename,e.deptno,d.dname

from emp e,dept d

where e.deptno(+)=d.deptno;

select ename,dname

from emp,dept

where emp.deptno(+)=dept.deptno

and emp.deptno(+)=30;

原文来自http://dampce032.iteye.com/blog/1008408