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 

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/4244.html

oracle