基于Oracle数据库操作的SQL基础语法

这是在某内培训的基于Oracle的SQL的笔记，分享给大家
从这里开始

Day01

–创建一个表

CREATE  TABLE employee_cmj(id NUMBER(10),name VARCHAR2(20),gender CHAR(1),birth DATE,salsry NUMBER(6,2),job VARCHAR2(30),deptid NUMBER(2)  );

神，对软件开发技术有浓厚的兴趣具有良好的沟通能力和耐压能力，能够快速融入团队在性格方面，个性温和，稳定和富有较强责任心

–数据库中无论字段是什么类型，默认值都是NULL，也就是说当我们向表中插入一条数据时，若对应的字段没有给定值，
–那么默认就是将NULL作为值插入到该字段指定默认值，这样就会用指定的值插入当前字段。

–SQL语句本身不区分大小写，但是行业中使用时，通常都会将关键字全部大小写，其他内容全部小写形成差异来增强可读
–性。但是需要支援，字符串在数据库中使用单引号括起来，并且内容是区分大小写的！

–查询表

DESC employee_cmj

–删除表

DROP TABLE rmpployee_cmj;

DROP TABLE employee_cmj;

DROP TABLE rmpployee_cmj;CREATE TABLE employee_cmj(id NUMBER(4),name VARCHAR2(20),gender CHAR(1) DEFAULT'M',birth DATE,salary NUMBER(6,2),job VARCHAR2(30),deptno NUMBER(2));

DROP TABLE employee_cmj;

/*NOT NULL 约束
该约束可以使某个字段必须给值，任何情况下给字段的值不能为NULL*/

CREATE TABLE employee_cmj(  id NUMBER(4),  name VARCHAR(20) NOT NULL,  gender CHAR(1) DEFAULT'M',  birth DATE,  salary NUMBER(6,2),  comm NUMBER(6,2),  job VARCHAR(30),  deptno NUMBER(2));DESC employee_cmj

/*
修改表名
RENAME old_name TO new_name
*/

RENAME employee_cmj TO myemp_cmjDESC employyee_cmj

/*
修改表结构
*/
/*
–1.添加新的字段
ALTER TABLE TABLE_name
ADD(….)
*/

ALTER TABLE myemp_cmjADD(  HIREDATE DATE  DEFAULT  SYSDATE);

DESC myemp_cmj
/2.删除现有字段，建议是表中没有数据的时候操作/

ALTER TABLE myemp_cmjDROP(hireDATE)DESC myemp_cmj

/*3.修改现有字段
修改已存在的字段的长度，类型，默认值
*/

ALTER TABLE myemp_cmjMODIFY(job VARCHAR2(40) DEFAULT'CLERK')DESC myemp_cmj

–DML语句
/*
DML可以对表中的数据进行“增，删，改”
DML语句是伴随事物控制的
*/

–插入语句

FROM DUAL myemp_cmj (id,name,job,salary)values(1001,'rosr','PROGRAMMER',5500)

–查看插入数据

SELECT * FROM myemp_cmj

–提交数据

commit

–INSERT语句中可以不指定针对那些插入数据，入不指定，则是全列插入，这时候VELUES中给定的值
–顺序，类型，个数必须与表中字段一致才可以

–插入一条日期类型数据建议使用TO-DATE函数

FROM DUAL myemp_cmj(id, name, job, birth)VALUES(1003,'donna','MANAGER',TO_DATE('2009-09-01','YYYY-MM-DD'));SELECT * FROM myemp_cmj

/*
修改表中数据，需要注意，不加WHERE条件，证张表中所有记录都会被修改
*/

UPDATE myemp_cmjSET gender = 'F',salary = 7000WHERE name='rosr'

–删除表中数据

DELETE FROM myemp_cmjWHERE name='rosr'

DAY02

SELECT empno,ename,job,sal FROM emp_cmj;

–CONCAT 返回两个字符串连接

SELECT CONCAT(ename,sal)FROM emp_cmj;SELECT CONCAT(CONCAT(ename,':'),sal) FROM emp_cmj;SELECT ename||':'||sal FROM emp_cmj;

–查看字符串元素个数

SELECT ename,LENGTH(ename) FROM emp_cmj;

/*
DUAL:伪表
为了满足SELECT语法，但是要查询的数据库又与现有的表中数据没有关系时使用。
*/
–eg

SELECT sysdate FROM DUAL;

–大小写转换

SELECT   --UPPER('hello')   --LOWER('HELLO')   INITCAP('HELLO WORLD')FROM DUAL;

/*
TRIM函数
去除字符串左右两面重复的给定的字符
给定的只能是单一的一个字符串
*/

SELECT TRIM ('a' FROM 'aaaaaaaaatimeaaaaaaaaa') FROM DUAL;

–LTRIM与RTRIM 分别去除字符串左，右边的字符,可以给定多个字符串

SELECT LTRIM('aaaaaaatimeaaaaaa','a') FROM DUAL;SELECT RTRIM('aaaaaaatimeaaaaaa','a') FROM DUAL;

/*
可以连续去除左边或者右边出现的给定字符的内容给定的字符不关心顺序，
只要当前字符串中包含给定的字符就去除
*/

SELECT LTRIM('aeaeaeaeaeaeaetimeaaaaaa','eat')FROM DUAL;

/*
–补位函数
LPAD,RPAD
LPAD(str1，n,str2)
显示str1的内容，要求显示n为长度，若str1的长度不足n，则在左侧补充若干个str2
字符以达到长度。需要注意str2必须是单一的字符。
LPAD常用于右对齐
RPAD常用于左对齐
当显示的位数不足时补充字符，若显示的内容长度超过要显示的位数时，
只从左边开始保留要显示的位数个数显示，剩下的会被去除
*/
–一般用于右对齐

SELECT ename,LPAD(sal,6,'$') FROM emp_cmj;

–一般用于左对齐

SELECT ename，RPAD(sal,6,'$') FROM emp_cmj;

–SUBSTR函数 截取字符串
–截取给定字符串，从指定的位置对应的字符开始连续截取指定个字符
–数据库中的下标都是从1开始的！！！要注意与java的区分

–第三个参数可以不指定（实例中的4），若不指定则是截取到字符串末尾，
–若第三个字符的大小超过可截取的字符长度时，也是截取到字符串末尾

–第二个参数可以写作0（实例中5），0也相当于从字符串第一个字符串开始截取
–还可以是负数，负数则是从倒数第几个字符开始截取

SELECT SUBSTR('THINKING IN JAVA',5，4) FROM DUAL;

/*
–INSTR函数
INSTR(char1，char2[,n[,m]])
查看char2在char1中的位置
n：从第几个字符处开始查找，若不写默认为1；
m：查看出现第几次，若不写默认为1；

*/

SELECT INSTR('thinking in java','in',4,3) FROM DUAL;

/*
数字类型函数
ROUND(n[,m])
四舍五入，对n进行操作，保留小数点后m位。m可以不写，不写默认为0，相当于保留到整数位，
m可是负数，保留十位以上数字
*/

SELECT ROUND(45.678,2) FROM DUAL;SELECT ROUND(45.678,0) FROM DUAL;SELECT ROUND(45.678,-1) FROM DUAL;

/*
TRUNC函数，参数的含义与ROUND一致，效果仅仅是不进行四舍五入，所以是截取数字
*/

SELECT TRUNC(45.678,2) FROM DUAL;SELECT TRUNC(45.678,0) FROM DUAL;SELECT TRUNC(45.678,-1) FROM DUAL;

–MOD求余 MOD（m，n）：m/n求余数
–若n为0则直接返回m

SELECT ename,MOD(sal,1000) FROM emp_cmj;

–CEIL和FLOOR
–CEIL：返回大于给定参数的最小整数（向上取整）
–FLOOR：返回小于给定参数的最大整数（向下取整）

SELECT CEIL(45.678) FROM DUAL;SELECT FLOOR(45.678) FROM DUAL;

–SYSDATE
–该关键字对应ORCALE的一个内部函数，表示一个DATE类型的当前系统时间的值

SELECT SYSDATE FROM DUAL;

–SYSTIMESTAMP
–表示当前系统时间的时间戳类型的值

SELECT SYSTIMESTAMP FROM DUAL;

–TO_DATE()函数
–将一个字符串按照给定的日期格式转换为DATE类型的值
/*
ORACLE中的日期格式需要注意事项：
若日期格式字符串中出现了非英文与符号的其他字符时，这些字符需要使用双引号括起来。
否则会抛出日期格式无法识别的错误
*/

SELECT TO_DATE('2008-08-08 20：08：00','YYYY-MM-DD HH24:MI:SS') FROM DUAL;SELECT TO_DATE('2008年08月08日 20时08分00秒','YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM DUAL;

/*
数据库中的日期是可以比较大小的
时间晚的大，早的小
可以对一个日期的值进行加法操作，加上一个数字等于加上了对应的天数。结果为计算后的日期。
两个日期时间也可以进行减法操作，结果为相差的天数
*/
–查看员工入职多少天了

SELECT TRUNC((SYSDATE-hireDATE),3) FROM emp_cmj;

–查看到今天活了多少天了

SELECT TRUNC((SYSDATE-hireDATE),3)FROM emp_cmj;SELECT TRUNC((SYSDATE-TO_DATE('1991-07-19','YYYY-MM-DD')),3)FROM DUAL;

SELECT ename,hireDATE FROM emp_cmjWHERE hireDATE>TO_DATE('1982-01-01','YYYY-MM-DD');

/*
TO_CHAR函数
可以将给定的日期按照给定的格式转换为一个字符串
*/
–将当前系统时间按照年月日时分秒显示

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')FROM DUAL;SELECT TO_CHAR(TO_DATE('05-05-21','RR-MM-DD'),'YYYY-MM-DD')FROM DUAL;

–LAST_DAY函数
–返回给定日期对应的月底日期，返回值依然是DATE类型
–查看当月月底是哪天

SELECT LAST_DAY(SYSDATE)FROM DUAL;

–查看09年2月的月底

SELECT LAST_DAY(TO_DATE('20-02-09','DD-MM-RR'))FROM DUAL;

–查看每个员工入职当月的月底是是哪天

SELECT ename,LAST_DAY(hireDATE)FROM emp_cmj;

–ADD_MONTHS函数
–对给定的日期加上给定的月
–查看每个员工入职20周年纪念日

SELECT ename,ADD_MONTHS(hireDATE,20 * 12)FROM emp_cmj;

–MONTHS_BETWEEN函数
–计算两个日期之间相差多少月
–计算是依靠第一个日期减去第二个日期的差计算的
–查看每个员工至今工作了多少月？

SELECT  TRUNC(MONTHS_BETWEEN(SYSDATE,hireDATE),3)FROM emp_cmj;

–NEXT_DAY函数
–返回距离给定日期最近的还没有过得周几是哪天？
–若给定日期与要计算的周几正好是一天，则算出的是下周
–第二个参数建议使用数字表示周几。
–1表示周日，2表示周一，以此类推

SELECT NEXT_DAY(SYSDATE,4)FROM DUAL;

–LEAST,GREATEST函数
–LEAST返回给定参数中最小的
–GREATEST返回给定参数中最大的
–参数多少都可以

SELECT LEAST(SYSDATE,'10-10月-08')FROM DUAL;SELECT GRANTEST(SYSDATE,'10-10月 -08')FROM DUAL;

–EXTRACT函数，提取系统时间中年，月，日
–提取当前系统时间中的年

SELECT EXTRACT(YEAR FROM SYSDATE)FROM DUAL;

CREATE TABLE student_cmj(id NUMBER(4),name CHAR(20),gender CHAR(1));FROM DUAL student_cmj VALUES(1000,'李莫愁','F');FROM DUAL student_cmj VALUES(1001,'林平之',NULL);FROM DUAL student_cmj (id,name) VALUES(1002,'张无忌');

UPDATE student_cmj SET gender=NULL;DELETE student_cmj;SELECT * FROM student_cmj WHERE gender IS NOT NULL;

–NULL与任何数字运算结果还是NULL

SELECT ename,sal,comm,sal+comm FROM emp_cmj;

/*
NVL(arg1,arg2)
该函数会判断第一个参数是否为NULL，若是，该函数返回第二个参数的值，
否则将第一个参数值返回，所以该函数的作用是将NULL值替换为非NULL值
*/

SELECT ename,sal,comm,sal+NVL(comm,0)FROM emp_cmj;

–查看员工的奖金情况，若有则显示为“有奖金”没有则显示为“没有奖金”
–NVL2(arg1,arg2，arg3)
–当arg1不为NULL时，函数返回arg2，否则返回arg3

SELECT ename,NVL2(comm,'有奖金','没有奖金')FROM emp_cmj;SELECT ename,sal,comm,NVL2(comm,sal+comm,sal)FROM emp_cmj;

Day03

/*
当SELECT语句中药查询的内容不是单纯的字段，而是一个函数或者表达式时，查询出来的结果集中对应的
该字段的字段名就是这个函数或者表达式，这样不利于读取，为此我们会为这样的列加一个别名，使得查
询出来的该字段的名字为这个别名增加可读性，甚至在子查询中对这样的情况还要求必须写别名。

别名可以在字段之后使用空格分隔开，然后定义或者在字段名之后使用“AS”然后在后面定义。但是通常
不需要书写“AS”
别名不区分大小写，且不能使用空格，若有需要在别名中区分大小写或者加空格，那么别名需要被双引号括起来。
*/

SELECT ename,sal*12 sal FROM emp_cmj;

/*
WHERE子句，与DML中使用一致，WHERE是用来限定条件。在SELECT语句中使用，可以只将满足WHERE要求的记录查询出来
*/

–查询10号部门的员工信息：

SELECT * FROM emp_cmj WHERE deptno=10;

–查看职位是SALESMAN员工信息

SELECT * FROM emp_cmj WHERE job='SALESMAN';

–查看职员表中薪水低于2000元的职员信息

SELECT ename,sal FROM emp_cmj WHERE sal<2000;

–查询职员表中不属于部门10的员工信息（！=等价于<>）

SELECT ename,sal,job FROM emp_cmj WHERE deptno<>10;

–查询职员表中在2002年1月1号以后入职的职员信息，比较日期类型数据

SELECT ename,sal,hireDATE FROM emp_cmj WHERE hireDATE > TO_DATE('2002-1-1','YYYY-MM-DD');

–查询薪水查看工资高于1000的职位为CLERK或SALESMAN的员工信息

SELECT ename,sal,job FROM emp_cmj WHERE sal>1000 AND job='CLERK';SELECT ename,sal,job FROM emp_cmj WHERE sal>1000 OR job = 'CLERK';

–AND的优先级高于OR，所有要提高优先级可以使用括号

SELECT ename,sal,job FROM emp_cmj WHERE sal>1000 AND (job='SALESMAN' OR  job = 'CLERK');

–LIKE关键字
–用于模糊查询字符串内容，通配符：%：0到多个任意字符 _:1个任意符
–查看名字第二个字母是A的员工

SELECT ename,job FROM emp_cmj WHERE ename LIKE '_A%';

/*
IN(list)
判断指定内容的值是否等于列表中任意一项

NOT IN（list）
判断指定内容的不值不等于列表中任意一项
*/

SELECT ename,job,sal FROM emp_cmj WHERE job IN('MANAGER','CLERK');

–IN常被用于作判断指定值是否与子查询的结果内容一致这是OR不可替代的

–BETWEEN…AND…
–判断指定内容的值在一个区间范围内
–工资在1500到3000之间的员工信息

SELECT ename,sal FROM emp_cmj WHERE sal BETWEEN 1500 AND 3000;

/*
ANY与ALL
判断指定内容>,>=,<,<=一个列表内容时使用

ANY:大于最小

SELECT empno,ename,job,sal,deptno FROM emp_cmj WHERE sal>ANY(3500,4000,4500);

/*
ANY与ALL列表中通常不会写死几个数据，否则没有意义，通常列表中是一个查询的结果，
所有常在判断子查询结果中使用
*/

–WHERE子句中可以使用函数或者表达式的结果作为过滤条件

SELECT ename,sal,job FROM emp_cmj WHERE UPPER(ename) = UPPER('rose');SELECT ename,sal,job FROM emp_cmj WHERE sal * 12>50000;

–DISTINCT关键字
–去除重复行，DISTINCT后面的字段值相同的将在查询结果集中被去除
–查看公司总共有哪些职位

SELECT DISTINCT job FROM emp_cmj;

–DISTINCT关键字必须紧跟在SELECT关键字之后。
–DISTINCT后面可以有多个字段，若是多个字段，那么则是这些字段值的组合没有重复的

SELECT DISTINCT job,deptno FROM emp_cmj;

–ORDER BY子句
–用来排序查询结果集，ORDER BY子句必须写在SELECT语句的最后面。
–ORDER BY语句可以根据后面指定的字段的值进行升序或者降序进行排列。
–降序使用DESC，升序使用ASC，但升序的ASC通常不写，因为不写是默认就是升序
/*
若按照多字段排序时，排序的优先级是先按照第一个字段的排序方式排序，然后若第一个字段值一样，
那么这些记录在按照第二个字段的排序方式排列，以此类推。
*/
–公司部门排名

SELECT ename,deptno FROM emp_cmj ORDER BY deptno;

–公司工资排名

SELECT ename,sal,deptno FROM emp_cmj ORDER BY deptno DESC,sal DESC;

/*
聚合函数
又叫做多行函数，分组函数
作用：将若干行记录进行统计，然后产生一个结果。
MAX(),MIN()
统计指定内容中所有参与统计记录中的最大值与最小值
*/
–查看公司中最高工资与最低工资？(增加别名)

SELECT MAX(sal) max_sal,MIN(sal) min_sal FROM emp_cmj ;

–AVG(),SUM()
–统计平均值与总和
–查看公司的平均工资，与工资总和

SELECT AVG(sal),SUM(sal) FROM emp_cmj;

–聚合函数忽略NULL值。
–若希望null值的记录也参与统计，可以使用NVL函数先将这些记录转换成非NULL值

SELECT AVG(NVL(comm,0))FROM emp_cmj;

–COUNT(）该函数统计的是非空的记录有多少条，而并不关心具体每条记录的值是多少。

SELECT COUNT(comm)FROM emp_cmj;SELECT COUNT(*)FROM emp_cmj;

/*
-GROUP BY(GROUP BY)关键字后面可以跟若干字段，作用是根据给定的字段值相同的记录看做一组，
若是多个字段，则这些字段值的组合相同的记录看做一组。
*/

–SELECT语句中若使用了组函数，那么不在组函数中的其他字段必须出现在GROUP BY 子句中！

SELECT MAX(sal) max_sal,MIN(sal) min_sal ,deptno FROM emp_cmj GROUP BY deptno ;

–查看公司每个职位都有多少人？

SELECT COUNT(*),job FROM emp_cmj GROUP BY job;

–查看平均工资高于2000的部门的最高工资和最低工资
/*
下面的做法是不可以的，WHERE子句中不允许使用分组函数

SELECT MAX(sal)，MIN(sal)FROM emp_cmj GROUP BY deptno

原因：过滤时机不同，我们若想判断平均工资大于3000，这说明我们已经将表中的数据查询出来，并先
进行统计，在统计的结果基础上再进行判断，而WHERE的过滤时机是在查询表的过程中进行的，表中的数
据查询出那条记录是由WHERE的判断结果为准，所以这里WHERE已经在实际需要判断的地方之前就进行完毕了*/

/*
HAVING子句，该子句必须跟在GRONP BY子句之后，不能单独定义，作用是在分组后，做统计，并用统计结果进行过滤
*/

SELECT deptno,max(sal),min(sal) FROM emp_cmj GROUP BY deptno HAVING AVG(sal)>2000;

/*
关联查询
联合多张表进行查询
需要注意的是，N张表关联查询至少要N-1个连接条件，连接条件的目的在于让数据库知道表中记录与另一张表中的记录是如何对应上的*/
–查看SALES部门的员工名字？

SELECT emp_cmj.ename FROM emp_cmj,dept_cmj WHERE emp_cmj.deptno=dept_cmj.deptno AND dept_cmj.dname='SALES';

–查看每个员工的名字以及所在的部门的名称以及所在地？(可以使用别名)

SELECT e.ename,d.dname,d.loc FROM emp_cmj e,dept_cmj d WHERE e.deptno=d.deptno;

–不添加连接条件，或者连接条件无效，都会产生笛卡尔积，笛卡尔积会产生大量无用的管连。对于系统
–资源产生严重破坏，严重时可导致系统瘫痪
–查看每个员工的名字以及所在部门的名称以及所在地？
–使用内连接,这样的写法好处在于连接条件定义在on子句中，WHERE里只定义过滤条件即可，条理更清晰

SELECT e.ename,d.dname.d.loc FROM emp_cmj JOIN dept d ON e.deptno = d.deptno;UPDATE emp_cmj SET deptno=50 WHERE ename='SCOTT';

–查看每个员工名字，部门号，部门名称
–查看每个员工名字以及部门号，部门名称，没有部门也要将员工信息列出来

SELECT e.ename,e.deptno,d.dname FROM emp_cmj e JOIN dept_cmj d ON e.deptno=d.deptno;

/*
外连接分为:左外连接，右外连接，全外连接

左外连接:以左边的表为主，左边表中的所有记录都查询出来，当有不满足连接条件的记录时，来自右边表中的字段全部为NULL。
右外连接:右表为主，左边补NULL
全外连接:哪边不满足连接条件，哪边补NULL*/

SELECT e.ename,e.deptno,d.dnameFROM emp e LEFT|RIGHT|FULL OUTER JOIN dept dON e.deptno=d.deptno

/*
自连接:
自己表中的一条数据对应自己表中的多条数据。自连接为了解决数据一样，但是之间又存在父子关系的情况。而且层级关系中有多少层不确定的情况。自连接简单说就是解决了树状结构的数据关系。
例如：员工表，都是员工，但是员工间有存在上下级关系商品类别表，都是类别，但是有存在上下级关系*/
–查看员工，以及其上司的名字?

SELECT e.ename,m.enameFROM emp e JOIN emp mON e.mgr=m.empno

DAY04
–子查询
–子查询的作用是为其他SQL语句提供数据，以便其他SQL可以根据该数据进行操作。子查询可以嵌套在
–DQL，DML,DDL中使用，最常见的地方是在DQL中使用

–谁的工资比CLARK的工资高？

SELECT ename,sal FROM emp_cmj WHERE sal>(SELECT sal FROM emp_cmj WHERE ename='CLARK');      

–查看与JONES相同部门的员工

SELECT ename,sal,deptno FROM emp_cmj WHERE deptno=(SELECT deptno FROM emp_cmj WHERE ename='JONES');

–DDL中使用子查询可以根据子查询的结果集快速创建一张表：

CREATE TABLE newemp AS SELECT e.empno,e.ename,e.sal,e.job,e,deptno,d.dname,d.loc FROM emp_cmj e JOIN dept d ON e.deptno=d.deptno;      DESC newemp;    SELECT * FROM newemp;

–DML语句中使用子查询删除CLARK所在部门的所有员工

DELETE FROM emp_cmj WHERE deptno=(SELECT deptno FROM emp_cmj WHERE ename='CLARK');SELECT * FROM emp_cmj;

–提高CLARK所在部门所有员工的工作20%

UPDATE emp_cmj SET sal = sal*1.2 WHERE deptno=(SELECT deptno FROM emp_cmj WHERE ename='CLARK');

–查找薪水比公司平均薪水高的员工

SELECT sal,ename FROM emp_cmj WHERE sal>(SELECT AVG(sal)FROM emp_cmj);

–查看与SALESMAN 不同职位但是相同部门的其他员工？

SELECT ename,deptno FROM emp_cmj   where deptno in(SELECT deptno FROM emp_cmj WHERE job='SALESMAN');

–查看谁的工资比20号部门随便一个员工工资高

SELECT ename,sal,deptno FROM emp_cmj  WHERE sal>any(SELECT sal FROM emp_cmj where deptno = 20);

/*EXISTS关键字
用在WHERE子句中，其后要跟一个子查询，而EXISTS是否返回为TRUE取决于后面的子查询是否能查询到数据
*/
–查看所有部门信息(加入not后，意思与上相反)

SELECT deptno,dname FROM dept_cmj d   WHERE not EXISTS(SELECT * FROM emp_cmj e WHERE d.deptno = e.deptno);SELECT MIN(sal) deptno FROM emp_cmj   deptno HAVING MIN(sal)>(SELECT MIN(sal)FROM emp WHERE deptno = 30);

–多列子查询，通常当做一张表看待，定义在外层查询的FROM子句使用
–查询出薪水比本部门平均薪水高的员工信息

SELECT e.ename,e.sal,e.deptno     FROM emp_cmj e,(SELECT deptno,AVG(sal) avg_sal FROM emp_cmj GROUP BY deptno) t      WHERE e.sal>t.avg_sal AND e.deptno=t.deptno;SELECT e.ename ,e.sal,(SELECT d.dname FROM dept_cmj d where d.deptno=e.deptno) dname FROM emp_cmj e;

–分页查询
/*分页的目的是，当数据量大的时候，若一次全部查询出来，会对系统资源造成不必要的开销，而且处理
速度会变慢，为此我们可以分段式的将数据一点一点的取出，分页的语句SQL没有定义，所有不同的数据分
页语句不一样
–ROWNUM伪列，并不是表中的一格真实字段，但是在任何表中的查询中都可以使用该列，该列的值为结果
集每一条记录编一个号。只要可以查询出一条记录该字段的值就会+1，行号从1开始
*/

SELECT ROWNUM,ename,sal,job,deptno FROM emp_cmj;

/*以下案例
由于只有从表中查询出一条数据，ROWNUM才会使用1进行编号，然后其自动涨2.所有，不要在第一次查询
数据使用rownum编号的时候在where中做大于1以上的数字判断，否则不会查询出任何数据！
ROWNUM一开始不大于1，所以where不满足条件，不满足where条件就查询不出数据。查询不出数据ROWNUM
就不会涨，不涨就不大于1，所以where不满足条件,所以下面操作查不到数据:
*/

SELECT ROWNUM,ename,sal,job,deptno FROM emp_cmj WHERE RUWNUM>1;

SELECT * FROM(SELECT ROENUM rn,ename, sal,job,deptno FROM emp) t  WHERE t.rn BETWEEN 6 AND 10;

/*
分页的同时，数据还有排序需求时：
查看公司中工资排名的6-10位的与昂信息：由于使用ROWNUM编号是早查询的过程中完成的，而排序时在
查询出数据以后进行的，所有者就导致排序后之前的编号就失去意义了。
下面并不会的到实际想要的数据：
*/

SELECT * FROM(SELECT ROWNUM rn,ename,sal,job,deptno FROM emp ORDER BY sal DESC) t  WHERE t.rn BETWEEN 6 AND 10;

–查看公司中工资排名的6-10位员工信息

SELECT * FROM  (SELECT rownum rn,ename,sal,job,deptno FROM emp_cmj ORDER BY sal DESC) t  where t.rn between 1 and 6;

/*
所有若有排序需求，分页需要三次查询才可以实现
1.排序 2.编号 3.取范围
*/

SELECT * FROM(SELECT ROWNUM rn,t.* FROM(SELECT ename,sal,job,deptno         FROM emp_cmj ORDER BY sal DESC) t)WHERE rn BETWEEN 6 AND 10;

/*分页时，BETWEEN中两个数字的公式：
page:页数
pagesize：每页显示的条数

–根据上面两个值计算：
start:(page-1) * pagesize+1
end:page*pagesize;*/

SELECT ename,job,sal,  DECODE(job,  'MANAGER',sal * 1.2,  'ANALYST',sal * 1.1,  'SALESMAN',SAL * 1.05,sal) bonus  FROM emp_cmj;

/*GROUP BY 中使用DECODE函数，可以将字段值不同的记录看成一组，只要使用DECODE将需要划为一组
的不同值转变为相同值即可*/

SELECT COUNT(*) job_cnt,DECODE(job,'ANALYST','VIP','MANAGER','VIP','OPERATION')  FROM emp_cmj  GROUP BY DECODE  (job,'ANALYST','VIP','MANAGER','VIP','OPERATION');

SELECT deptno,dname,loc  FROM dept_cmj   ORDER BY DECODE  (dname,'OPERATIONS',1,'ACCOUNTIONG',2,'SALES',3);

/*ROW_NUMBER函数，可以根据指定的字段分组，在根据指定的字段排序，然后生成组内连续且唯一事物
数字。*/
–查看每个部门的工资排名情况？

SELECT ename,sal,deptno,ROW_NUMBER()  OVER(PARTITION BY deptno       ORDER BY sal DESC) rank  FROM emp_cmj;

–RANK函数：生成组内不连续且不唯一的数字

SELECT ename,sal,deptno,  RANK()  OVER(PARTITION BY deptno       ORDER BY sal DESC) rank  FROM emp_cmj;--DENSE_RANK函数：生成组内连续但不唯一的数字SELECT ename,sal,deptno,  DENSE_RANK()  OVER(PARTITION BY deptno       ORDER BY sal DESC) rank  FROM emp_cmj;

–union(联合，并集的意思)显示两个集合的内容，删去重复的。

SELECT ename,job,sal FROM emp_cmj  WHERE job = 'MANAGER'  UNION   SELECT ename,job,sal FROM emp_cmj  WHERE sal>2500;

–union all (联合，并集的意思)显示两个结果集全部内容，包括重复的，

SELECT ename,job,sal FROM emp_ljWHERE job='MANAGER'UNION ALLSELECT ename,job,sal FROM emp_ljWHERE sal> 2500;

–intersect（相交，交集的意思）显示两个结果集重复的

SELECT ename,job,sal FROM emp_ljWHERE job='MANAGER'INTERSECTSELECT ename,job,sal FROM emp_ljWHERE sal> 2500;

–minus（减去，差集的意思）显示在第一个结果集中有，第二结果集中没有的

SELECT ename,job,sal FROM emp_ljWHERE job='MANAGER'MINUSSELECT ename,job,sal FROM emp_ljWHERE sal> 2500;CREATE TABLE sales_cmj(  year_id NUMBER NOT NULL,  month_id NUMBER NOT NULL,  day_id NUMBER NOT NULL,  sales_value NUMBER(10,2) NOT NULL);INSERT INTO sales_cmj  SELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,         TRUNC(DBMS_RANDOM.value(1,13))AS month_id,         TRUNC(DBMS_RANDOM.value(1,32))AS day_id,         TRUNC(DBMS_RANDOM.value(1,100))AS sales_value      FROM dual      CONNECT BY level <= 1000;SELECT * FROM sales_cmj;      

–查看每天的销售总额

SELECT SUM(sales_value)  FROM sales_cmj  GROUP BY year_id,month_id,day_id  ORDER BY year_id,month_id,day_id;

–查看每个月的销售总额

SELECT SUM(sales_value)  FROM sales_cmj  GROUP BY year_id,month_id  ORDER BY year_id,month_id;

–查看每年的销售总额

SELECT year_id,SUM(seles_value)  FROM sales_cmj  GROUP BY year_id   ORDER BY year_id;

–全年的销售总额

SELECT SUM(sales_value) FROM sales_cmj;

/*ROLLUP高级分组函数
ROLLUP函数用在GROUP BY 子句中，ROLLUP 函数中可以传入若干参数，作为参数逐个递减，每次进行一
回分组统计结果，然后将这些结果并在一起显示
*/

SELECT year_id,month_id,day_id,  SUM(sales_value)   FROM sales_cmj   GROUP BY ROLLUP(year_id,month_id,day_id)  ORDER BY year_id,month_id,day_id;

–CUBE()高级分组函数
/*
会将给定的字段的每一种组合都进行一次分组，然后将所有结果并在一起显示。分组的次数是2的参数个
数次方。
*/

SELECT year_id,month_id,day_id,  SUM(sales_value)  FROM sales_cmj  GROUP BY CUBE(year_id,month_id,day_id)  ORDER BY year_id,month_id,day_id;

–GROUPING SETS 高级分组函数
/*可以按照给定的分组方式进行分组，然后结果并在一起显示，每个参数表示一种分组方式，参数可以使
用括号将要分组的字段组合括起来表示一种*/

SELECT year_id,month_id,day_id,  SUM(sales_value)  FROM sales_cmj  GROUP BY GROUPING SETS(year_id,month_id,day_id),  (year_id,month_id)  ORDER BY year_id,month_id,day_id;

DAY05
/*视图：也称为虚表（虚拟的表），视图在SQL语句中体现的地方和表一致，但是其不是数据库中真实存在
的表，而是一个SELECT语句的查询结果集。所以视图中有哪些字段，对应的都有哪些记录完全取决于
SELECT查询的结果*/

CREATE VIEW v_emp_10_cmj  AS SELECT empno,ename,sal,deptno     FROM emp_cmj     WHERE deptno=10;DESC v_emp_10_cmj;

/*
当我们通过SELECT语句查询视图时，数据库会先使用该视图对应的SELECT语句将数据查询出来，
然后将这个结果当做一张表，在进行和我们的SELECT语句，将要查询的数据查询数量，所以，视图
就是在FROM中使用子查询
*/

SELECT * FROM v_emp_10_cmj;

/*视图中对应的子查询中，查询的字段若含有表达式函数，那么必须要添加别名，单纯的字段也可以添加别名
单纯的字段也可以添加别名，这样创建出来的视图对应的字段名就是该别名*/

CREATE OR REPLACE VIEW v_emp_10_cmj  AS   SELECT empno id,ename name,sal salary,deptno  FROM emp_cmj  WHERE deptno=10;SELECT * FROM  v_emp_10_cmj;

–对视图进行DML操作只能对简单视图进行。对视图进行DML操作就是对视图数据来源的基表进行的
–向视图中插入数据，数据书记上是插入视图数据来源的基表中，而且，新增的记录中只有视图看到的字段
–会有值，其它字段插入默认值

–视图有可能插入一条视图看不见的数据，这会对基表产生数据污染！

0INSERT INTO v_emp_10_cmj  VALUES(1001,'JACK',5000,20);SELECT * FROM v_emp_10_cmj;

–更新视图数据就是更新基表中对应的数据,与insert一样，更新视图数据有可能导致视图无法再次查询到该数据！

UPDATE v_emp_10_cmj SET deptno=20;  SELECT * FROM v_emp_10_cmj;  SELECT * FROM emp;

–DELETE视图中的数据就是删除表中数据但是删除视图看到的数据

DELETE FROM v_emp_10_cmj WHERE deptno=20;SELECT * FROM emp; SELECT * FROM v_emp_10_cmj;

–当视图添加该选项后，对视图进行INSERT:必须插入的数据视图可见
–对视图进行UPDATE:必须更新后视图随其可见

CREATE OR REPLACE VIEW v_emp_10_cmjASSELECT empno id,ename,sal salaryFROM v_emp_10_cmjwhere deptno = 10with check option;

–with read only选项
–将视图设置为只读的，那么就不允许对视图进行DML操作了

CREATE OR REPLACE VIEW v_emp_10_cmj  AS   SELECT empno id,ename,sal salary  FROM v_emp_10_cmj  where deptno=10  with read only;

–查看数据字典，得知用户创建过得所有数据库对象

SELECT object_name,object_type FROM user_objects;

–只看视图，可以查看视图的数据字典

SELECT text,view_name FROM user_views;

–只看表

SELECT TABLE_name FROM user_TABLEs;

–定义复杂视图
–定义一个可以查看各部门薪资情况的视图

CREATE VIEW v_emp_cmjAS SELECT d.dname,    avg(e.sal) avg_sal,    sum(e.sal) sum_sal,    max(e.sal) max_sal,    min(e.sal) min_sal    FROM emp_cmj e join dept_cmj d     on e.deptno=d.deptno    GROUP BY d.dname;SELECT * FROM v_emp_cmj;drop view v_emp_cmj;  

–序列
/*作用：根据设定的方式，生成一组数字
通常序列被用来为表中的主键字段提供值*/

CREATE SEQUENCE seq_emp_cmj_id START WITH 100 INCREMENT BY 10;

/*
序列有两个伪列：
NEXTVAL:向序列要下一个数字
若序列还没有生成数字，则第一次获取的数字为START WITH 指定的数字，
否则是用之前生成过的数字加上步长得到的。
一旦获取了下一个数字，就无法再通过序列获取之前生成的数字了！
CURRVAL:向序列要最后一次生成的数字，该伪列可以调用若干次，每次返回的数字一样，
并不会导致序列获取下一个值，除非使用nextval，
需要注意，序列刚创建完毕后，至少调用nextval一次生成数字后才可以使用该伪列
*/

SELECT seq_emp_cmj_id.NEXTVAL FROM DUAL;SELECT seq_emp_cmj_id.CURRVAL FROM DUAL;

INSERT INTO emp_cmj  (empno,ename,sal,job,deptno)  values  (seq_emp_cmj_id.NEXTVAL,'ROSE',6000,'CLERK',20);SELECT * FROM emp_cmj;

–删除序列

DROP SEQUENCE seq_emp_cmj_id;

–索引：提高表的查询效率

CREATE INDEX idx_emp_cmj_ename ON emp_cmj(ename);

–索引算法与应用是数据库自行决定的

–复合索引

CREATE INDEX idx_emp_cmj_job_sal ON emp_cmj(job,sal);

–创键基于函数的索引

CREATE INDEX emp_cmj_ename_upper_idx ON emp_cmj(UPPER(ename));

–查询

SELECT empno,ename,sal,job FROM emp_cmj ORDER BY job,sal;

–重建索引

ALTER INDEX idx_emp_cmj_ename REBUILD;

–删除索引

DROP INDEX idx_emp_cmj_ename;

CREATE TABLE employee_cmj(eid NUMBER(6),name VARCHAR2(30),salary NUMBER(7,2),hireDATE DATE CONSTRAINT employee_cmj_hireDATE_nn NOT NULL);DESC employee_cmj;ALTER TABLE employee_cmj MODIFY (eid NUMBER(6) NOT NULL);

–取消非空约束

ALTER TABLE employee_cmj MODIFY (eid NUMBER(6) NULL);

–添加唯一性约束

CREATE TABLE employee0_cmj(eid NUMBER(6)  UNIQUE,name VARCHAR2(30),email VARCHAR2(50),salary NUMBER(7,2),hireDATE DATE ,CONSTRAINT employee0_cmj_uk UNIQUE (email));insert into employee0_cmj(eid,name) values(null,'TOM');SELECT * FROM employee0_cmj;

–在建表之后增加唯一性约束条件

ALTER TABLE employee_cmj  ADD CONSTRAINT employee_cmj_uk  UNIQUE(name);

–添加主键约束

CREATE TABLE employee2_cmj(eid NUMBER(6) primary key,name VARCHAR2(30),email VARCHAR2(50),salary NUMBER(7,2),Hiredate  DATE );