oracle查询安装语言,Oracle查询语句详解

Oracle终端登陆

以dba的身份登陆

sqlplus “/as sysdba”

创建用户

create user user0611 identified by user0611;

赋予权限

grant resource,connect to briup;

查看当前用户

show user;

以user0611 登陆(conn username/password;)

conn user0611 /user0611 ;

如果是windows系统需要修改日期格式

alter session set nls_date_language=english;

alter session set nls_language=english;

查询当前用户下所有的表

select table_name from user_tables;

oracle基本查询

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

FROM table;

SELECT 标识 选择哪些列。

FROM 标识从哪个表中选择。

SQL 语言大小写不敏感。

SQL 可以写在一行或者多行

关键字不能被缩写也不能分行

各子句一般要分行写。

使用缩进提高语句的可读性。

以oracle自带的scott用户登陆

conn scott/tiger

选择全部列

select * from emp;

选择特定的列

select empno,ename,job,mgr,hiredate,sal,comm,deptno

from emp;

使用 DESCRIBE 命令，表示表结构

DESC[RIBE] tablename

desc dept

oracle中的算术运算符

* / + -

乘除的优先级高于加减

优先级相同时，按照从左至右运算

可以使用括号改变优先级

使用数学运算符

select empno,ename,sal,sal*12,comm,sal*12+comm

from emp;

使用括号

SELECT last_name, salary, 12*(sal+100)

from emp;

SQL中的null值

空值是无效的，未指定的，未知的或不可预知的值

空值不是空格或者0。

1. 包含null的表达式都为null

2. null != null

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

from emp;

去重DISTINCT

select DISTINCT deptno,job from emp;

列的别名:

重命名一个列。

便于计算。

紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

AS可以省略

SELECT ename AS name, job j

FROM emp;

连接符:

把列与列，列与字符连接在一起。

用 ‘||’表示。

可以用来‘合成’列。

SELECT ename||job AS "Employees"

FROM emp;

字符串

字符串可以是 SELECT 列表中的一个字符,数字,日期。

日期和字符只能在单引号中出现。

每当返回一行时，字符串被输出一次。

SELECT ename ||' is a '||job

AS "Employee Details"

FROM emp;

过滤和排序数据

在查询中过滤行,使用WHERE 子句，将不满足条件的行过滤掉

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

FROM table

[WHERE condition(s)];

WHERE 子句紧随 FROM 子句。

查询部门号是10的员工

select *

from emp

where deptno=10;

字符和日期要包含在单引号中。

字符大小写敏感，日期格式敏感。

默认的日期格式是 DD-MON-RR

select *

from emp

where ename ='KING';

select *

from emp

where hiredate='17-11月-81'

修改日期格式

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

select * from emp where hiredate='1981-11-17';

比较运算

= 等于 > 大于 >= 大于、等于 < 小于 <= 小于、等于

<> 不等于 (也可以是 !=)

SELECT ename, sal

FROM emp

WHERE sal <= 3000;

其它比较运算

BETWEEN...AND... 在两个值之间 (包含边界)

IN(set) 等于值列表中的一个

LIKE 模糊查询

IS NULL 空值

select *

from emp

where sal between 1000 and 2000;

select *

from emp

where deptno in (10,20);

如果集合中 含有null，不能使用not in，但可以使用in

select *

from emp

where deptno not in (10,20,null)

select *

from emp

where ename like 'S%';

回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[_]，然后再加上[ESCAPE ‘\’] 即可

查询名字中含有下划线的员工

select *

from emp

where ename like '%\_%' escape '\'

逻辑运算

AND 逻辑并 要求并的关系为真

OR 逻辑或 要求或关系为真

NOT 逻辑否

SELECT ename, sal

FROM emp

WHERE sal >= 10000

AND　ename LIKE '%MAN%';

SELECT ename, sal

FROM emp

WHERE sal >= 10000

OR ename LIKE '%MAN%';

ORDER BY子句

使用 ORDER BY 子句排序

ASC(ascend): 升序

DESC(descend): 降序

ORDER BY 子句在SELECT语句的结尾。

select *

from emp

order by sal;

select ename,sal,sal*12 年薪

from emp

order by 3 desc;

select *

from emp

order by deptno,sal;

单行函数:

操作数据对象

接受参数返回一个结果

只对一行进行变换

每行返回一个结果

可以转换数据类型

可以嵌套

参数可以是一列或一个值

select lower('Hello WOrld') 转小写,upper('Hello WOrld') 转大写,

initcap('hello world') 首字母大写

from dual;

--substr(a,b)从a中 第b位开始取

select substr('Hello World',3) from dual;

--substr(a,b,c) 从a中 第b位开始取,取c位

select substr('Hello World',3,4) from dual;

--length 字符数 lengthb 字节数

select length('Hello World') 字符,

lengthb('Hello World') 字节

from dual;

--一个中文字符占两个字节

select length('中国') 字符,

lengthb('中国') 字节

from dual

--instr(a,b) 在a中 查找b

select instr('Hellow World','ll') from dual;

--lpad 左填充 rpad右填充

select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右

from dual;

--trim 去掉前后指定的字符

select trim('H' from 'Hello WorldH') from dual;

--replce 替换

select replace('Hello World','l','*') from dual;

--数值函数

select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三,

ROUND(45.926, -1) 四, ROUND(45.926, -2) 五

from dual;

select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,

TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五

from dual;

--日期函数

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--昨天 今天 明天

select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天

from dual;

--30月后

select add_months(sysdate,30) from dual;

select LAST_DAY(sysdate) from dual;

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

select ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR')

from dual;

--查询员工的薪水： 货币符号 两位小数 千位符

select to_char(sal,'L9,999.99') from emp;

--nvl2(a,b,c) 当a=null时 返回c ，否则返回b

select sal*12+nvl2(comm,comm,0) from emp;

--NULLIF(a,b) 当a=b时 返回null；否则返回a

select nullif('abcd','abcd') from dual;

select nullif('abcd','abcde') from dual;

--COALESCE : 从左到右 找到第一个不为null的值

select comm,sal,COALESCE(comm,sal) from emp;

--IF-THEN-ELSE 逻辑

select ename,job,sal 涨前,

case job when 'PRESIDENT' then sal+1000

when 'MANAGER' then sal+800

else sal+400

end 涨后

from emp;

-- decode

select ename,job,sal 涨前,

decode(job,'PRESIDENT',sal+1000,

'MANAGER',sal+800,

sal+400 ) 涨后

from emp;

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。组函数忽略空值

语法：

SELECT [column,] group_function(column), ...

FROM table

[WHERE condition]

[GROUP BY column]

[ORDER BY column];

AVG(平均值)和 SUM (合计)函数

COUNT(计数)函数

MIN(最小值) 和 MAX(最大值)函数

select deptno,avg(sal)

from emp

group by deptno;

select sum(sal) from emp;

select count(*) from emp;

select min(sal),max(sal) from emp;

可以使用GROUP BY 子句将表中的数据分成若干组

在SELECT 列表中所有未包含在组函数中的列都应该包含

在 GROUP BY 子句中。

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

不能在 WHERE 子句中使用组函数(注意)。

可以在 HAVING 子句中使用组函数。

使用 HAVING 过滤分组:

1.行已经被分组。

2.使用了组函数。

3.满足HAVING 子句中条件的分组将被显示。

select deptno,avg(sal)

from emp

group by deptno;

select deptno,avg(sal)

from emp

group by deptno

having avg(sal)>2000;

多表查询

-- 等值连接

select e.empno,e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno=d.deptno;

/*外连接

希望：在最后的结果中 包含某些不成立的记录

左外连接: where e.deptno=d.deptno不成立的时候，等号左边代表的表任然被包含

写法: where e.deptno=d.deptno(+)

右外连接: where e.deptno=d.deptno不成立的时候，等号右边代表的表任然被包含

写法：where e.deptno(+)=d.deptno

*/

select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数

from emp e,dept d

where e.deptno(+)=d.deptno

group by d.deptno,d.dname;

--通过表的别名，将同一张表视为多张表，自连接

select e.ename||'的老板是'||b.ename

from emp e,emp b

where e.mgr=b.empno;