Oracle学习笔记

Oracle数据库
一、数据库分类

1、小型数据库：access、foxbase
2、中型数据库：informix、sql server、mysql
3、大型数据库：sybase、db2、oracle

---

Oracle简介
数据库（Database,简称DB）：是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。
Oracle Database，又名OracleRDBMS，或简称Oracle。是甲骨文公司的一款关系型数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的适应高吞吐量的数据库解决方案。
关系型数据库管理系统：
•描述两个元素之间的关联关系或对应关系
•使用关系模型把数据组织到二维数据表中
•一个关系数据库由多个数据表组成，数据表是关系数据库的基本存储结构。
•表是二维的，由行与列组成
•表的行，即横排数据也叫做记录
•表的列，即纵排数据也叫字段
•表与表之间存在关联关系
行： 记录

列： 字段

SQL可以分为：
数据定义语言（DDL）、数据操作语言（DML）
事务控制语言（TCL）、数据查询语言（DQL）、
数据控制语言（DCL）
数据定义语言(DDL):
•用于建立、修改、删除数据库对象
•包括：create：创建表或其他对象的结构
•Alter：修改表或其他对象的结构
•Drop：删除表或其他对象的结构
•Truncate：删除表数据，保留表结构
数据操作语言（DML）：
•用于改变数据表中的数据
•和事务相关，执行完毕后需要经过事务控制语句提交后才真正的将改变应用到数据库中
•包括：
•insert：将数据插入到数据库中
•Update：更新数据表中已经存在的数据
•Delete：删除表中的数据
事务控制语言（TCL）：
•用来维护数据一致性的语句
•包括：
•Commit：提交，确认已经进行的数据改变
•Rollback：回滚，取消已经进行的数据改变
数据查询语言（DQL）:
•用来查询所需的数据
•Select语句
数据控制语言（DCL）:
•执行权限的授予和回收操作
•包括：grant:授予，给予角色权利
•Revoke：用于收回角色权限
•Createuser：创建用户

定长 ：char:浪费空间 节省时间(存不满补空格) 最大取值2000字节，char可以不指定长度默认为1
变长 ：varchar2:浪费时间 节省空间（存多少占多少）最大取值4000字节，varchar2必须制定长度
long:varchar2加长版，存储变长字符串，最多达2GB的字符串数据
有诸多限制：每个表只能有一个LONG类型列；不能作为主键；不能建立索引；不能出现在查询条件中
CLOB：存储定长或变长字符串，最多达4GB的字符串数据

创建用户：
create user lsg（名） identified by admin;，代码的意思是：创建一个用户名为lsg的用户，密码为admin，同样的代码结束，需要加分号”;” 

授权;
授予connect，resource,dba权限 
grant connect,resource,dba to lsg;
•LENGTH(char)
•返回字符串的长度
•如果类型是varchar2，则返回实际长度，
•如果是字符串类型char，长度还要包括后补的空格
•Selectename,length(ename)from emp;

--UPPER转换为大写
SELECT UPPER(NAME) FROM MYEMP  ;
--LOWER转换为小写
SELECT LOWER(NAME) FROM MYEMP;
--INITCAP每个单词首字母大写
SELECT INITCAP(NAME) FROM MYEMP;
eg:
name:rrrosssrrr
--TRIM(c2 FROM c1) 从c1的前后截去c2
SELECT TRIM('r' FROM name) as t1 FROM MYEMP;
--LTRIM(c1,c2) 从c1的左边（Left）截去c2
SELECT LTRIM(NAME,'r') as t2 FROM MYEMP;
--RTRIM(c1,c2) 从c1的右边（Rrith）截去c2 如果没有c2,就去除空格
SELECT RTRIM(NAME,'r') as t3 FROM MYEMP;

补位函数：用于在字符串char1的左端或右端用char2补足到n位，char2可重复多次
LPAD(char1 ,n ,char2)左补位函数
RPAD(char1, n, char2)右补位函数
SELECT name,LPAD(SALARY,6,'*')as sal FROM MYEMP;【原：5500  后：**5500】

--SUBSTR(char,m,n) 获取字符串的子串 m为0 从首字符开始；m为负数，从尾部开始
SELECT SUBSTR('djfdsngaja dhsjf fjasdk fjask',8,15)FROM DUAL;【DUAL为虚表】
 -----------------------
SYSDATE其本质是一个oracle内部函数，返回当前时间，精确到秒。
SYSTIMESTAMP内部函数，返回当前系统时间和日期，精确到毫秒。

日期转换函数：TO_DATE(char,fmt,nlsparams)
TO_CHAR:将其他类型额数据转换为字符类型
LAST_DAY:返回日期date所在月的最后一天
ADD_MONTHS(date,i):返回日期date加上i个月后的日期值
i可以是任何数字，如果是小数，取整运算
如果是负数，获得的是减去i个月后的日期值
MONTH_BETWEEB(date1,date2)
NEXT_DAY(date,char):返回date日期数据的下一个周几，周几是由参数char来决定的
NVL（expr1,expr2）:将NULL转换为非NULL值

varchar和varchar2区别：
1.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；
2.VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；
3.VARCHAR2字符要用几个字节存储，要看数据库使用的字符集，

创建用户：
create user 用户名 identified by 密码; 
【密码必须以字母开头，如果以数字开头，它不会创建用户】
给用户修改密码
概述：如果给自己修改密码可以直接使用
SQL> password 用户名或passw
如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限
SQL> alter user 用户名 identified by 新密码

修改表：
1.修改表名
RENAME EMP TO MYEMP;

 2.修改表结构
 2.1 添加新字段
 向表中添加新的字段，只能在当前表的末尾追加，可以同时追加多个列，
 只需要使用逗号隔开即可，与创建表 声明列的时候的语法一致
ALTER TABLE MYEMP ADD(
hiredate DATE DEFAULT SYSDATE 
)
 2.2 删除表中的现有字段
 删除MYEMP表中的hiredate ALTER：修改
ALTER TABLE MYEMP DROP(hiredate)
 查看表结构
DESC EMP;
 删除
DROP TABLE EMP;
 修改表
 1.修改表名
RENAME EMP TO MYEMP;
 2.修改表结构
 2.1 添加新字段
 向表中添加新的字段，只能在当前表的末尾追加，可以同时追加多个列，
 只需要使用逗号隔开即可，与创建表 声明列的时候的语法一致
ALTER TABLE MYEMP ADD
 2.2 删除表中的现有字段
 删除MYEMP表中的hiredate ALTER：修改
ALTER TABLE MYEMP DROP(hiredate)
 2.3 可以修改字段的类型、长度、默认值
 非空
ALTER TABLE MYEMP MODIEY(
job VARCHAR2(40) DEFAULT 'CLECK'
);
 【DML语句 】
 1.插入语句
INSERT INTO MYEMP(id,name,job,salary) VALUES(1,'JACK','CLECK',5000);

SELECT  * FROM MYEMP；
SELECT id,name,gender,birth,salary,job,deptno from MYEMP;


 插入日期类型，建议使用TO_DATE函数 ，可以使用字符串，但是格式必须是’DD-MON-RR‘
 友谊有语言差异，不推荐
INSERT INTO MYEMP (ID,NAME,JOB,BIRTH) 
VALUES(
                2,
                'ROSE',
                'CLERK',
                TO_DATE('1996-08-08','YYYY-MM-DD')
);

INSERT INTO MYEMP (ID,NAME,JOB,BIRTH) 
VALUES(
                2,
                'rrrosssrr',
                'CLERK',
                TO_DATE('1996-08-08','YYYY-MM-DD')
);

SELECT * FROM MYEMP;

 2.修改表中现有数据 将jack的工资改为5500
 修改表中数据通常要使用where限定条件，这样只有将满足条件的记录进行修改，
 否则全表所有的数据都修改
UPDATE MYEMP SET  SALARY=5000 WHERE NAME='JACK';

 3.删除表中数据 将rose删除 
 删除表中数据同样要使用where条件
DELETE FROM MYEMP WHERE name='ROSE';

--DELETE 'ROSE' FROM MYEMP;
 LENGTH(返回字符串的长度)
SELECT name,length(name) from MYEMP;
 UPPER转换为大写
SELECT UPPER(NAME) FROM MYEMP  ;
 LOWER转换为小写
SELECT LOWER(NAME) FROM MYEMP;
 INITCAP每个单词首字母大写
SELECT INITCAP(NAME) FROM MYEMP;
 TRIM(c2 FROM c1) 从c1的前后截去c2 去除字符串两边的指定字符
SELECT TRIM('r' FROM name) as t1 FROM MYEMP;
 LTRIM(c1[,c2]) 从c1的左边（Left）截去c2
SELECT LTRIM(NAME,'r') as t2 FROM MYEMP;
 RTRIM(c1[,c2]) 从c1的右边（Rrith）截去c2 如果没有c2,就去除空格
SELECT RTRIM(NAME,'r') as t3 FROM MYEMP;

 补位函数：用于在字符串char1的左端或右端用char2补足到n位，char2可重复多次
 LPAD(char1 ,n ,char2)左补位函数
 RPAD(char1, n, char2)右补位函数
SELECT name,LPAD(SALARY,6,'*')as sal FROM MYEMP; **5500
 DUAL： 伪表，当查询的内容与任何一张表中的数据没有关系 可以查询伪表
 SUBSTR(char,m,n) 获取字符串的子串 m为0或1 都从首字符开始；m为负数，从尾部开始
SELECT SUBSTR('djfdsngaja dhsjf fjasdk fjask',8,15)FROM DUAL; aja dhsjf fjasd
 ===================================
 INSTR(str, substr)  || INSTR(ch1,ch2[n,m])
 查找char2在char1中的位置 n为从指定位置开始查找，可以不写 m为第几次出现，可以不写
 n m不写默认为1
SELECT INSTR('THINKING IN JAVA' ,'IN',4,2) FROM DUAL; 10 第4位开始 出现第二次的位置

SELECT NAME,SALARY,DEPTNO FROM MYEMP WHERE SUBSTR(NAME,1,1)='J';

SELECT NAME FROM MYEMP WHERE NAME LIKE 'J%';
 ===================================
 LAST_DAY返回该月最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
 ===================================================================
  DQL
  select语句
  查询表的所有字段的所有记录
SELECT * FROM MYEMP;
  查询指定字段的值
SELECT NAME,SALARY,JOB,DEPTNO FROM MYEMP WHERE NAME='JACK';
  DQL语句中必须包含的部分是select子句与from子句，select用来确定查询的字段
  可以使用的有：表字段
  函数、表达式from子句用来确定查询的表
  select中使用表达式：查看每个员工的年薪
SELECT NAME,SALARY*12 FROM MYEMP;

  字符串函数
  "CONCAT"(ch1, ch2)
SELECT CONCAT(NAME, SALARY) FROM MYEMP;
  JACK : 5000
SELECT CONCAT(CONCAT(NAME,' : '),SALARY) FROM MYEMP;
  连接字符串还有一种简单的方式
  使用‘||’注意与java区别
SELECT NAME||' : '||SALARY FROM MYEMP;

SELECT LTRIM('ersersersesesesee','esr') FROM DUAL; null

 ========================================
 数字函数
 1. ROUND(n,m) 对n进行四舍五入，保留到小数点后m位 
 m可以不写，不写默认为0 m为0则保留整数，-1为保留到10位，以此类推
SELECT ROUND(45.678, 2)FROM DUAL; 45.68
SELECT ROUND(45.678, 1)FROM DUAL; 45.7
SELECT ROUND(45.678, 0)FROM DUAL; 46
SELECT ROUND(45.678, -1)FROM DUAL; 50

 TRUNC()函数，参数作用域 用于截取  不进行四舍五入
SELECT TRUNC('45.2156', 2) FROM DUAL; 45.21
SELECT TRUNC('45.2156', 0) FROM DUAL; 45
SELECT TRUNC('45.2156', 1) FROM DUAL; 45.2
SELECT TRUNC('45.2156', -1) FROM DUAL; 40

 MOD()函数取余
SELECT NAME,SALARY,MOD(SALARY,1000) FROM MYEMP; 500

 CEIL(n)和FLOOR 向上 向下取整
SELECT CEIL(21.562)FROM DUAL; 22
SELECT FLOOR(21.562)FROM DUAL; 21

 日期类型
 DATE ：7个字节，保存世纪，默认显示年月日，
关键字：SYSDATE：对应一个内置函数，返回一个表示当前系统时间的DATE
SELECT SYSDATE FROM DUAL; 2018-08-14 10:45:18


 TIMESTAMP：时间戳，比DATE多4个字节，可以保存秒以下的精度，前7个字节与DATE一致，
SYSTIMESTAMP：内部函数，返回当前系统时间和日期，
精确到毫秒。时间戳显示的内容更多
SELECT SYSTIMESTAMP FROM DUAL;  2018-08-14 10:44:47:458000 +08:00


 TO_CHAR通常用于转换日期，可以将日期按照指定的日期格式转换为字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:ss') FROM DUAL; 2018-08-14 13:45:20
SELECT TO_CHAR(SYSDATE,'YY-MM-DD') FROM DUAL;

SELECT TO_DATE('2008-08-08','YYYY-MM-DD') FROM DUAL;

 TO_DATE 将日期字符串按照指定 格式转换为DATE类型
SELECT TO_DATE('03-09-01','RR-MM-DD')FROM DUAL;

SELECT TO_CHAR(
TO_DATE
('03-09-01','RR-MM-DD'),'YYYY-MM-DD'
)
FROM DUAL;

 查看每个员工生日所在月的月底
SELECT NAME, LAST_DAY(BIRTH) FROM MYEMP;

 ADD_MONTHS 对给定的日期增加指定的月数,若i为负数，则为减法
 查看每个员工18岁的生日
SELECT ADD_MONTHS(BIRTH,18*12)  FROM MYEMP;

 MONTHS_BETWEEN 计算两个日期之间相差的月数 计算方式是使用date1-date2的结果
 查看每个员工至今活了多少个月？
SELECT NAME,MONTHS_BETWEEN(SYSDATE,BIRTH) FROM MYEMP;

 NEXT_DAY 返回给定日期之后一周内的周几
SELECT NEXT_DAY(SYSDATE, 2)FROM DUAL; 8月20 周一

 "LEAST"(expr1, ... exprn)
 "GREATEST"(expr1, ... exprn)
 求最大值和最小值
 这组函数的参数不限制数量，两个以上即可
SELECT LEAST(SYSDATE,
TO_DATE('2008-08-08','YYYY-MM-DD')
)
FROM DUAL;

SELECT LEAST(SYSDATE)
FROM DUAL;
 82年以后出生的员工
 若是82年以前的，则显示为1982-01-01
SELECT NAME, GREATEST(BIRTH,
TO_DATE('1982-01-01','YYYY-MM-DD')
)
FROM MYEMP;

 ""EXTRACT"(time_unit FROM expr)
 提取指定日期时间分量的值

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

 查看81年出生的员工

SELECT NAME, BIRTH FROM MYEMP WHERE EXTRACT(YEAR FROM BIRTH) =1981;

 NULL 
CREATE TABLE STUDENT1(
ID NUMBER(4),
NAME VARCHAR2(20),
GENDER CHAR(1) 
)
INSERT INTO STUDENT1 VALUES(1000，'张三1','M');
INSERT INTO STUDENT1(ID,NAME) VALUES(1001，'张三5');
INSERT INTO STUDENT1 VALUES(1002，'张三3',NULL);
SELECT * FROM STUDENT1;

DROP TABLE STUDENT1
 要使用is null 或is not null
 性别为空的设置性别
UPDATE STUDENT1 SET GENDER='F' WHERE GENDER IS NULL;

 NULL的运算NULL与字符串相连等于什么也没有做，结果是字符串
 NULL与数字运算的结果是NULL
SELECT CONCAT(NULL,'SQL') FROM DUAL; SQL
SELECT (NULL||'SQL') FROM DUAL; SQL
SELECT (NULL+1) FROM DUAL; NULL

 NVL(E1，E2) 若E1为NULL，返回E2 ;若E1不为NULL，函数返回E1本身
 查看每个员工的收入
SELECT NAME,SALARY+NVL(COMM,0) FROM MYEMP;
 收入用哪个NVL2表示
SELECT NAME, NVL2(COMM,SALARY+NVL(COMM,0),SALARY) FROM MYEMP;


 "NVL2"(expr1, expr2 ,expr3)当expr1不为null返回expr2，为null返回expr3
 查看每个员工的奖金情况 若不为null,显示‘有奖金’
 若为null,则显示’没有奖金‘
SELECT NAME, NVL2(COMM,'有奖金','没有奖金') FROM MYEMP;

 NVL2可以完全实现NVL功能，但是反过来不行

                               -
 聚合函数 MAX MIN 


SELECT ID,USERNAME,PASSWORD,ACOUNT,EMAIL 
FROM USERINFO 
WHERE USERNAME='JACK'PASSWORD ='123'

可以对字段使用别名，当SELECT字句中出现了函数或者表达式时，结果集中就会用
这个函数或表达式作为字段名，这样可读性差，这时我们通常会给字段加别名。若希望
区别大小写或者包含空格，那么可以使用双引号将别名括起来

【查询员工的年薪】
SELECT NAME,SALARY*12 "年薪" FROM MYEMP;

【查看3号部门的员工信息】
SELECT * FROM MYEMP WHERE DEPTNO=3 

【查看职位是CLECK的员工信息】
SELECT * FROM MYEMP WHERE JOB='CLECK';

AND的优先权高于OR，可以通过使用括号提高OR的优先权
【查看工资高于3000，并且职位是CLECK的员工信息或SALESMAN的员工信息】
SELECT * FROM MYEMP WHERE SALARY>3000 AND (JOB='CLECK' OR JOB='SALESMAN');

LIKE模糊查询 支持两个通配符
_：表示单一的一个字符
%:表示任意个字符（0-多个）
【查看名字中第二个字母是A的员工信息】
SELECT * FROM MYEMP WHERE NAME LIKE '_A%';

IN（LIST）,NOT IN(LIST)
判断是否在列表中，常用在子查询中
【查看职位是CLECK或SALESMAN的员工信息】
SELECT * FROM MYEMP WHERE JOB IN ('CLECK','SALESMAN');

BETWEEN  AND 判断一个范围内
【查看工资在1500-6000之间的员工】
SELECT * FROM MYEMP WHERE SALARY BETWEEN 1500 AND 6000;

ANY ALL
综合：>,>=,<,<=,一个列表使用
大于ALL：>ALL(LIST):大于列表中最大的（大于所有），小于ALL：<ALL(LIST):小于最小的
小于ANY：>ANY(LIST):大于列表中最小的（大于其中之一），小于ANY：<ANY(LIST):小于最大的


后面的列表几乎不会使用确定值，常用于判断子查询的结果
下面的SQL仅用于演示，不用于开发（开发不这样写）
SELECT NAME ,SALARY,JOB FROM MYEMP WHERE SALARY >ALL(5500,8500,4500);--大于8500的结果
SELECT NAME ,SALARY,JOB FROM MYEMP WHERE SALARY <ALL(5500,8500,4500);--小于4500的结果
SELECT NAME ,SALARY,JOB FROM MYEMP WHERE SALARY >ANY(5500,8500,4500);--大于4500的结果
SELECT NAME ,SALARY,JOB FROM MYEMP WHERE SALARY <ANY(5500,8500,4500);--小于8500的结果

过滤条件中可以使用函数和表达式
SELECT NAME,SALARY,JOB FROM MYEMP WHERE NAME=UPPER('jack');
SELECT NAME,SALARY,JOB FROM MYEMP WHERE SALARY *12>50000;

DISTINCT 用于去重,DISTINCT必须紧跟在关键字之后，DISTINCT可以对多列去重
去重原则就是这些列的组合没有重复值
【查看公司共有多少种职位】
SELECT DISTINCT(JOB) FROM MYEMP;
SELECT DISTINCT JOB,DEPTNO FROM MYEMP;


ORDER BY 字句，排序结果集，可以按照指定的字段进行升序或降序排列
ASC:升序（可以不写，默认）
DESC：降序
需要注意ORDER BY 只能定义在SELECT语句的最后一个字句上
【查看公司工资排名】
SELECT NAME,SALARY FROM MYEMP ORDER BY  SALARY DESC;


ORDER BY 可以多列进行排序，排序具有优先权，先按照第一个字段进行排序结果集，
当第一个字段有重复值的时候，再按照第二个字段进行排序，以此类推
SELECT NAME,SALARY,DEPTNO FROM MYEMP ORDER BY DEPTNO DESC,SALARY DESC,NAME;

若排序的字段中出现NULL的情况，NULL被视为最大值
SELECT NAME,COMM FROM MYEMP ORDER BY COMM DESC;

聚合函数，又称为多行函数，聚合函数是用来统计结果的
【MAX MIN】：统计最大值与最小值
【查看公司最高与最低的工资】
SELECT MAX(SALARY) "最高工资",MIN(SALARY) "最低工资" FROM MYEMP ;


【AVG SUM】:平均值和计和
SELECT AVG(SALARY) "平均工资",SUM(SALARY) "工资总计" FROM MYEMP;

【COUNT】:统计的是记录的条数而不关注具体该字段的取值
【查看公司总人数】
SELECT COUNT(NAME) "公司总人数" FROM MYEMP;

聚合函数忽略NULL值
SELECT COUNT(COMM) FROM MYEMP;
SELECT SUM(COMM) FROM MYEMP;
SELECT AVG(NVL(COMM,0)),SUM(COMM) FROM MYEMP;
SELECT AVG(NVL(SALARY,0)),SUM(SALARY) FROM MYEMP;等价于SELECT AVG(SALARY) "平均工资",SUM(SALARY) "工资总计" FROM MYEMP;

统计表中的记录数，常用COUNT(0)
SELECT COUNT(0) FROM MYEMP;--5

GROUP BY 字句允许将结果集按照给定的字段值相同的记录看成一组
然后配合聚合函数对每组记录进行统计
【查看每个部门的最高工资】
SELECT MAX(SALARY),DEPTNO FROM MYEMP GROUP BY DEPTNO;

当SELECT中含有聚合函数时，凡是不在聚合函数中的单独字段必须出现在GROUP BY字句中
【查看每个职位的平均工资和工资总和】
SELECT AVG(SALARY) "平均工资",SUM(SALARY+NVL(COMM,0)) "工资总和" FROM MYEMP GROUP BY JOB;
【查看每个部门各多少人】
SELECT DEPTNO,COUNT(0) FROM MYEMP GROUP BY DEPTNO;
【查看每个部门的平均工资】
SELECT DEPTNO,AVG(SALARY) "部门平均工资" FROM MYEMP GROUP BY DEPTNO;
【查看每个部门的平均工资，前提是该部门AVG高于2000】
SELECT AVG(SALARY) "部门平均工资" FROM MYEMP GROUP BY DEPTNO HAVING AVG(SALARY)>2000;

WHERE 中不允许使用聚合函数作为过滤条件，原因在于过滤时机不同
WHERE 的过滤时机是在第一次从表中检索数据时添加过滤条件用来确定哪些数据可以被查询出来以确定结果集
HAVING字句：HAVING必须跟在GROUP BY的后面，可以使用聚合函数作为过滤条件使之可以对分组进行过滤，
将满足条件的分组保留，不满足的去除.
【查看平均工资高于3000的部门的最高工资和最低工资】
SELECT DEPTNO,AVG(SALARY),MAX(SALARY
),MIN(SALARY) FROM MYEMP GROUP BY DEPTNO HAVING AVG(SALARY)>3000
【查询列出最低薪水高于部门1的最低薪水的部门信息】
SELECT DEPTNO  FROM MYEMP GROUP BY DEPTNO HAVING MIN(SALARY) >(
  SELECT MIN(SALARY) FROM MYEMP WHERE DEPTNO=1
)
--------------------------------------------------------------------------------
【查看工资高于自己所在部门平均工资的员工信息】
SELECT E.* FROM MYEMP E WHERE SALARY >(
SELECT AVG(SALARY) FROM MYEMP WHERE E.DEPTNO=DEPTNO
) 

SELECT M.*
FROM MYEMP M,(
SELECT AVG(SALARY) AVG_SAL,DEPTNO FROM MYEMP GROUP BY DEPTNO
) T
WHERE M.DEPTNO=T.DEPTNO AND M.SALARY > T.AVG_SAL;
在SELECT字句中使用子查询通常是外连接的一种写法
---------------------------------------------------------------------------------
【分页查询】
通常一个查询语句查询的数据量过大时，都会使用分页机制。分页就是将数据分批查询出来。
一次查询部分数据这样的好处可以减少系统响应时间，减少系统资源开销
分页由于没有在SQL中定义，所以不同的数据库语法不同（方言）
OARCLR中使用ROWNUM这个伪列来实现分页。ROWNUM该列不存在与数据库任何表中，但是任何表都可以查询该列，
该列在结果集中的值是每条记录的行号，注意行号从1开始，编号是在查询过程中进行的，只要可以从表中查询
出一条数据，那么记录的ROWNUM字段值即为这条记录的行号。
SELECT ROWNUM ,NAME,JOB FROM MYEMP ;
---------------------------------------------------------------------------------
在使用ROWNUM对结果集编号的查询过程中不用使用做>1以上数字的判断，否则查不出结果
例子：
SELECT ROWNUM,NAME ,JOB,SALARY FROM MYEMP WHERE ROWNUM BETWEEN 6 AND 10;
【查询6-10条数据】
SELECT ROWNUM,NAME ,JOB,SALARY FROM MYEMP WHERE ROWNUM<6;

SELECT * FROM (
SELECT ROWNUM RN,NAME,JOB,SALARY FROM MYEMP
) WHERE RN BETWEEN 6 AND 10;

SELECT * FROM (
SELECT ROWNUM RN,NAME,JOB,SALARY 
FROM MYEMP 
WHERE ROWNUM<=10
) WHERE RN >=6;
---------------------------------------------------------------------------------
【取公司员工工资排名的6-10位,倒序排名】


SELECT * FROM(
SELECT NAME,SALARY,JOB 
FROM MYEMP 
ORDER BY SALARY DESC)T
WHERE ROWNUM <=10)
WHERE RN >=6;


SELECT * FROM(
SELECT ROWNUM RN,T.* FROM (
SELECT NAME,SALARY,JOB FROM MYEMP 
ORDER BY SALARY DESC)
T)
WHERE RN BETWEEN 6 AND 10;

INSERT INTO  USERINFO (ID,USERNAME,PASSWORD,ACCOUNT,EMAIL)  VALUES (SEQ_USERINFO_ID.NEXTVAL,'12','111','12')


---------------------------------------------------------------------------------
换算范围：
PageSize:每一页显示的最大条数
Page:页数
START=(Page-1)*PageSize+1;
END=PageSize*Page;
实际开发的时候分页参数
WHERE ROWNUM BETWEEN START AND END 
---------------------------------------------------------------------------------
【视图】
视图：数据库对象之一 在SQL语句中体现的与表的角色相同 ，
但视图并不是一张实际存在的表 他只是对应了一条SELECT语句查询的结果集，
使用视图可以重用子查询， 并且简化SQL语句的复杂度
【创建包含3号部门员工信息的视图】
CREATE VIEW V_MYEMP_3
AS
SELECT * FROM MYEMP WHERE DEPTNO=3;

SELECT * FROM V_MYEMP_3;
【查询视图结构】
DESC V_MYEMP_3;

视图对应的子查询字段可以使用别名 那么该视图对应的字段就是这个别名，  
若字段含有函数或表达式，必须指明别名

CREATE OR REPLACE VIEW
V_MYEMP_3
AS
SELECT NAME V_NAME,SALARY V_SAL,JOB V_JOB
FROM MYEMP
WHERE DEPTNO=3
---------------------------------------------------------------------------------
对视图进行DML操作  对视图进行DML操作就是对视图数据来源的基础表进行操作，
只能对简单的视图进行DML操作，复杂的视图不允许使用DML操作，
对简单视图进行DML操作时，也不能违背基础表的约束条件
INSERT INTO V_MYEMP_3(V_NAME,V_SAL,V_JOB) VALUES('LINDA',9800,'CXY');

SELECT V_NAME,V_SAL,V_JOB FROM V_MYEMP_3;

SELECT * FROM MYEMP;
---------------------------------------------------------------------------------
当通过视图插入一条数据 该数据对视图不可见，就是对基础表的污染
插入会造成数据的污染，修改也会造成数据污染，但是删除不会造成
UPDATE V_MYEMP_3 SET V_NAME='LINDA';
DELETE FROM V_MYEMP_3 WHERE V_NAME='LINDA';
---------------------------------------------------------------------------------
序列：序列也是一种数据库对象 序列用来生成一系列数字的
序列通常为表的主键提供值使用 
创建序列
CREATE SEQUENCE SEQ_MYEMP_ID
START WITH 1
INCREMENT BY 1
---------------------------------------------------------------------------------
序列支持两个伪列：
NEXTVAL:使序列生成下一个数字（用最后生成的数字加上步长得到的）
新创建的序列则返回START WITH
指定的数字 序列是不能回退的，所以通过NEXTVAL获取了下一个数字后就无法再获取之前生成的数字了
CURRVAL:获取序列生成的最后一个数字，无论用多少次都不会导致序列生成新的数字，但是创建的序列
至少调用一次NEXTVAL后才可以用CURRVAL.
SELECT SEQ_MYEMP_ID.NEXTVAL 
FROM DUAL;

SELECT SEQ_MYEMP_ID.CURRVAL 
FROM DUAL;

使用SEQ_MYEMP_ID序列为MYEMP表的主键生成值
INSERT INTO 
MYEMP(ID,NAME,JOB,SALARY,DEPTNO) 
VALUES (SEQ_MYEMP_ID.NEXTVAL,'LUBAN','WORK',9000,4);

SELECT * FROM MYEMP;
-------------------------------------------------------------------------------
删除序列
DROP SEQUENCE SEQ_MYEMP_ID ;
---------------------------------------------------------------------------------
UUID  永不重复  
字符串类型的主键值，32位永不重复
ORACLE 提供了一个函数可以生成UUID
SELECT SYS_GUID() FROM DUAL;
---------------------------------------------------------------------------------
不要在小表上建立索引 不要在经常做DML操作的表上建立索引（维护）
索引：提高查询效率
索引的统计维护和应用都是自动的
我们对索引的操作权限于 是否添加索引
创建索引
CREATE INDEX IDX_MYEMP_NAME ON MYEMP(NAME);

删除索引
DROP  INDEX IDX_MYEMP_NAME;
---------------------------------------------------------------------------------
约束
唯一性约束
当某个字段使用了唯一性的约束后该字段的值在表中是不允许有重复值
但是NULL值除外
--CONSTRAINT 外联 设置约束
CREATE TABLE EMP_LD(
EID NUMBER(6) UNIQUE,
NAME VARCHAR2(30) ,
EMAIL VARCHAR2(50),
SALARY NUMBER(7,2),
CONSTRAINT EMP_EMAIL_UK UNIQUE(EMAIL)       
)
INSERT INTO EMP_LD(EID,NAME,EMAIL,SALARY) VALUES(1,'LD','456453',12000);
INSERT INTO EMP_LD(EID,NAME,EMAIL,SALARY) VALUES(1,'LD','1351',12000);--ERR
INSERT INTO EMP_LD(EID,NAME,EMAIL,SALARY) VALUES(NULL,'LD',NULL,12000);
---------------------------------------------------------------------------------
主键约束：非空唯一
PRIMARY KEY,
NOT NULL


SELECT M.ID,M.NAME,JOB,M.SALARY,M.DEPTNO,D.ADDRESS FROM MYEMP M,DEPT D
WHERE M.DEPTNO=D.ID