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,deptnofrom emp;

使用 DESCRIBE 命令，表示表结构

DESC[RIBE] tablename

desc dept

oracle中的算术运算符

 * / + -

乘除的优先级高于加减
优先级相同时，按照从左至右运算
可以使用括号改变优先级

使用数学运算符

select empno,ename,sal,sal*12,comm,sal*12+commfrom 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 jFROM   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 empwhere deptno=10;

字符和日期要包含在单引号中。
字符大小写敏感，日期格式敏感。
默认的日期格式是 DD-MON-RR

select *from empwhere ename ='KING';

select *from empwhere hiredate='17-11月-81'

修改日期格式

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

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

比较运算

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

SELECT ename, salFROM   empWHERE  sal <= 3000;

其它比较运算

BETWEEN...AND...  在两个值之间 (包含边界)    IN(set)          等于值列表中的一个 LIKE                模糊查询 IS NULL         空值

select *from empwhere sal between 1000 and 2000;select *from empwhere deptno in (10,20);如果集合中 含有null，不能使用not in，但可以使用inselect *from empwhere deptno not in (10,20,null)select *from empwhere ename like 'S%';

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

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

select *from empwhere ename like '%\_%' escape '\'

逻辑运算
AND 逻辑并 要求并的关系为真
OR 逻辑或 要求或关系为真
NOT 逻辑否

SELECT ename, salFROM   empWHERE  sal >= 10000AND　ename LIKE '%MAN%';SELECT ename, salFROM   empWHERE  sal >= 10000OR  ename LIKE '%MAN%';

ORDER BY子句
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。

select *from emporder by sal;select ename,sal,sal*12 年薪from emporder by 3 desc;select *from emporder 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中  查找bselect 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 ，否则返回bselect sal*12+nvl2(comm,comm,0) from emp;--NULLIF(a,b) 当a=b时 返回null；否则返回aselect 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+1000when 'MANAGER' then sal+800else sal+400end 涨后from emp;-- decodeselect 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 empgroup 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 empgroup by deptno;select deptno,avg(sal)from empgroup by deptnohaving avg(sal)>2000;

多表查询

-- 等值连接select e.empno,e.ename,e.sal,d.dnamefrom emp e,dept dwhere 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 dwhere e.deptno(+)=d.deptnogroup by d.deptno,d.dname;--通过表的别名，将同一张表视为多张表，自连接select e.ename||'的老板是'||b.enamefrom emp e,emp bwhere e.mgr=b.empno;