Oracle学习总结

-- 查询表结构
DESC   DEPT;

别名查询：使用as  关键字  ，可以省略
表名中不能有特殊字符或者关键字，如果有就加双引号

--dual
Oracle中的虚表，伪表，主要是用来补齐语法结构
select  SYSDATE  from dual;


-- distinct   去除重复数据
多列去除重复：每一列都一样才能够算作是重复

select  DISTINCT   job,DEPTNO from  emp;  //多列去除重复的

--日期函数
select  SYSDATE  from dual;
select CURRENT_DATE from dual;

-- 日期可以进行加减
select  SYSDATE+1  from dual;
select  SYSDATE-e.HIREDATE     from  EMP e

-- ADD_MONTHS  添加指定的月份
select  ADD_MONTHS(SYSDATE, 1) from  dual;

-- 获取最后一天
select  LAST_DAY(SYSDATE) from  dual;


--TO_CHAR 转换函数
select  * from EMP where  TO_CHAR(HIREDATE,'yyyy-mm-dd') ='1980-12-17' 


--BETWEEN 
select  * from EMP where  SAL<500 or   SAL>2000;
select  * from EMP where  SAL  not BETWEEN 500  and 2000;

--MONTHS_BETWEEN
--查询出所有员工入职距离现在多长时间
select  MONTHS_BETWEEN(SYSDATE, e.HIREDATE)  from  EMP e
--查询出所有员工入职距离现在多少年
select  MONTHS_BETWEEN(SYSDATE, e.HIREDATE)/12  from  EMP e

-- ESCAPE用法  转义字符
 


-- 通用函数 nvl函数(将空值转换成一个已知的值)
如果第一位是null,则使用第二位，否则第一位
可以使用的数据类型有日期，字符，数字
函数的一般形式：
nvl(emp.sal,0)


-- VNL2函数
VNL2（expr1,expr2,expr3） //expr1不为空，返回expr2;若为null,返回expr3

-- NULLIF函数
NULLIF(expr1,expr2):相等返回NULL,不等返回expr1


--length  长度
select  LENGTH('tom') from dual;

--ROUND 保留几位小数(进位)
select ROUND(3.56, 1)  from dual;

--取模
select  mod(10,4) from  dual;
select  mod(-10,4) from  dual;
--向上取整
select  ceil(12.12) from dual;
--向下取整
select FLOOR(12.12) from dual;
-- 绝对值
select abs(-100) from dual;
--获取正负值
select sign(100) from dual;


-- 多行函数
多行函数就是聚合函数 sum count  min avg
单行函数：作用于单行，返回一个值
多行函数：作用于多行，返回一个值


--STDDEV 
    
--VARIANCE



--GROUP BY(分组查询) 
select   DEPTNO,SUM(sal) from  emp  HAVING  SUM(sal)  >9000  GROUP BY  DEPTNO;

select   DEPTNO,SUM(sal) from  emp   GROUP BY  DEPTNO  HAVING  SUM(sal)  >9000;

--HAVING(过滤)
HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足


条件表达式
在sql语句中使用if-then-else逻辑
使用两种方法：
-case表达式
-decode函数



-- 解锁scott 用户
alter  user  scott  account  unlock;
--解锁 scott 用户的密码（也可以用来重置密码）
alter  user   scott   identified  by  tiger;

-- 创建表空间
create  tablespace  lixiaoyao datafile  'd:\lixiaoyao.dbf' size  100m   autoextend  on  next  10m;

-- 删除表空间
drop  tablespace  lixiaoyao;


--创建用户
create  user  lixiaoyao  identified  by  lixiaoyao  default  tablespace  lixiaoyao;
-- 给用户授权
-- oracle 数据库中常用角色
connect --连接角色，基本角色
resource  --开发者角色
dba --超级管理员角色
--给lixiaoyao用户授权dba
grant  dba to   lixiaoyao;


--修改表结构
--添加一列
alter  table  person add(gender   number(1));

--修改列类型
alter table  person   modify gender  char(1);

--修改列名称
alter table  person rename column   gender  to  sex;

--删除一列
alter  table   person  drop  column  sex;

--隐式内连接和显式内连接：
隐式内连接：通过where条件进行条件
显式内连接：通过on进行条件  inner可以省略

select   e1.ENAME,d1.LOC from  SCOTT.emp  e1 INNER JOIN SCOTT.dept d1 on  e1.DEPTNO=d1.DEPTNO  //显示内连接
select   e1.ENAME,d1.LOC from  SCOTT.emp  e1,SCOTT.dept d1  where  e1.DEPTNO=d1.DEPTNO        //隐式内连接


--外连接
左外连接：left outer  join  左表中所有的记录，如果右表没有对应记录，就显示为空
右外连接：right outer  join  右表中的所有记录，如果左表没有对应记录，就显示为空
outer 关键字可以省略

Oracle中的外连接：（+）实际上是如果没有对应的记录就加上空值
select   e1.ENAME,d1.LOC from  SCOTT.emp  e1 , SCOTT.dept d1 where   e1.DEPTNO=d1.DEPTNO(+)

-- 查询语句创建表
create  table  emp2  as  select * from  SCOTT.emp;
select  * from  emp2;


-- 多表查询
 笛卡尔积：实际上是两张表的乘积，但是在实际开发中没有太大意义

--分页查询
rownum 前面不能带别名（固定格式）

SELECT
	* 
FROM
	( select ROWNUM rn, tt.* FROM ( SELECT * FROM SCOTT.emp ORDER BY SAL DESC ) tt WHERE ROWNUM < 11 ) 
WHERE
	rn >5

空值不同于0，凡是空值参与的运算，结果都为空（null）

给别名加双引号  字段不会变
日期和字符串只能在单引号中出现



--Sequence
定义好sequence后，你就可以用currVal，nextVal取得值。
    CurrVal：返回 sequence的当前值
    NextVal：增加sequence的值，然后返回 增加后sequence值
  得到值语句如下：                      SELECT Sequence名称.CurrVal FROM DUAL;
  如得到上边创建Sequence值的语句为：    select seqtest.currval from dual

--查询表里有多少张表
SELECT COUNT(1) FROM USER_TABLES;


-- 可以查询表使用的编码格式
select userenv('language') from dual;

-- 可以查询一个汉字占用的字节长度
select lengthb('好') from dual;