oracle数据库基本操作

SQL语句
SQL是结构化查询语句，操作数据库需要向数据库
发送SQL语句，数据库会理解SQL语句的含义并执
行。

SQL语句分为：
DDL(数据定义语言):用来操作数据库对象
数据库对象:表，视图，索引，序列

DML(数据操作语言):用来操作表中数据
TCL(事务控制语言):维护数据操作一致性
TCL维护DML操作。

DQL(数据查询语言):查询表中数据
DCL(数据控制语言):用来管理数据库

DML：数据操作语言 如：insert，update，delete
DDL：数据定义语言 如：create table,drop table，truncate table，alter
DCL：数据控制语言 如：commit，rollback,grant,revoke,create user
DQL：数据查询语言 如：select

SQL语句不区分大小写，但应当养成一个
好习惯，通常将关键字与非关键字用大小写
区分开。

SELECT SYSDATE FROM dual


DDL语句:数据定义语言
用于增删改数据库对象

创建表:
CREATE TABLE employee(
  id NUMBER(4),
	name VARCHAR2(20),
	gender CHAR(1),
	birth DATE,
  salary NUMBER(6,2),
  job VARCHAR2(30),
  deptno NUMBER(2)
)

查看表结构
DESC employee

字段的默认值
数据库中所有字段无论是什么类型，默认值都
是NULL,可以使用DEFAULT为字段单独指定
默认值。
虽然SQL语句不区分大小写，但是字符串的值是
区分大小写的。
CREATE TABLE employee(
  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

NOT NULL约束
当一个字段被设置NOT NULL约束后，该字段的值不允许为空
CREATE TABLE employee(
  id NUMBER(4),
  name VARCHAR2(20) NOT NULL,
  gender CHAR(1) DEFAULT 'M',
  birth DATE,
  salary NUMBER(6,2),
  job VARCHAR2(30),
  deptno NUMBER(2)
)

修改表
1:修改表名
2:修改表结构

修改表名:
RENAME old_name TO new_name
例如:将employee改名为myemp
RENAME employee TO myemp


2:修改表结构
2.1 添加新的字段
需要注意，添加的字段只能被追加到表的最后一列中，不能
在现有字段中间插入一个字段。

为myemp表添加字段hiredate:
ALTER TABLE myemp 
ADD(
   hiredate DATE
)

2.2:删除字段
ALTER TABLE myemp
DROP(
    hiredate 
)

2.3:修改现有字段
可以修改字段的类型，长度，添加约束或设定默认值。
若表中已经存在数据，则不要修改字段的类型，长度尽可能只
增不减，否则可能会修改失败。

ALTER TABLE myemp
MODIFY(
    job VARCHAR2(40)
)
DESC myemp


DML语句:数据操作语言
用来增，删，改表中的数据。DML总是伴随事务(TCL)的。

1:INSERT语句
用于向表中插入新的记录

INSERT INTO myemp 
(id,name,salary,job)
VALUES
(1,'jack',3000,'CLERK')

COMMIT
SELECT * FROM myemp

插入数据时，可以不指定字段名，若不指定，则是全列插入
INSERT INTO myemp
VALUES
(2,'rose','F',SYSDATE,3000,'CLERK',10)

插入日期类型数据时，建议使用函数TO_DATE
该函数可以将一个字符串按照指定的日期格式转换为Date类型
INSERT INTO myemp
(id,name,salary,job,birth)
VALUES
(3,'mike',4000,'MANAGER',
 TO_DATE('1992-08-02','YYYY-MM-DD')
)

无法将NULL值插入到非空约束的字段name上。
INSERT INTO myemp
(id,salary,job)
VALUES
(4,5000,'CLERK')

SELECT * FROM myemp

2:UPDATE语句，修改表中数据
需要注意，修改语句通常要添加WHERE子句，这样可以限定
要求改的记录。若不添加WHERE，则是全表所有记录都进行
修改，添加了则只会将满足WHER条件的记录进行修改。

UPDATE myemp
SET salary=5000,deptno=20
WHERE gender='M'


3:DELETE语句，删除表中数据
需要注意，删除语句通常要添加WHERE子句，这样可以限定
要求删除的记录。若不添加WHERE，则是清空表操作，添加了
则只会将满足WHER条件的记录进行删除。

DELETE FROM myemp
WHERE gender='M'


作业:
1:完成emp,dept表的创建
2:向两张表中添加数据
3:将员工SCOTT的部门号修改为50

COMMIT

SElECT * FROM userinfo_csx
DESC emp_csx;
DROP TABLE userinfo_csx
DROP SEQUENCE seq_userinfo_id_csx
SELECT * FROM emp_csx


CREATE TABLE jdbc_devil( 
	id NUMBER(6), 
	username VARCHAR2(30), 
	gender VARCHAR(1), 
	job VARCHAR(30), 
	hiredate DATE, 
	sal NUMBER(7,2) 
);
DESC jdbc_devil; 
DROP TABLE jdbc_devil;



DQL语句：查询语句
DQL用于查询表中数据
DQL必须包含的两部分：SELECT子句和FROM子句
SELECT子句：指定要查询的内容，可以指定表中的具体字段，*号，函数，或者表达式
FROM子句：指定数据来源的表

 *  查看表中所有内容
SELECT * FROM  emp_csx1   

查看表中指定内容
SELECT ename,job,sal,deptno FROM emp_csx1

查看每个员工的年薪？
SELECT ename,sal,sal*12 FROM emp_csx1

DQL中使用WHERE子句可以添加过滤条件，来检索只符合过滤条件的记录

查看20号部门的员工信息？
SELECT ename,sal,JOB,deptno FROM emp_csx1 WHERE deptno=20
查看工资高于2000的员工？
SELECT ename,sal,job,deptno FROM emp_csx1 WHERE sal>2000

字符串函数：
1：CONCAT（c1:c2） 连接c1和c2

例如：连接ename和sal？
SELECT CONCAT(ename,sal) FROM emp_csx1

连接多个字符串：
1.不常用
SELECT CONCAT(CONCAT(ename,':'),sal) FROM emp_csx1

2.常用：
"||"也可以用来连接多个字符串
SELECT ename||':'||sal FROM emp_csx1

2:LENGTH(c) 查看指定字符串的长度

SELECT ename,LENGTH(ename) FROM emp_csx1

查看名字是6个字母的员工？
SELECT ename,sal,job FROM emp_csx1 WHERE LENGTH(ename)=6

3:UPPER,LOWER,INITCAP
将指定字符转换为全大写，全小写，以及首字母大写
INITCAP中的字符串可以用空格分割多个单词，这样每个单词的首字母都大写。
当查询的数据不与任何表数据有关系时，可以查询伪表：dual
SELECT UPPER('helloworld'),
        LOWER('HELLOWORLD'),
        INITCAP('hello world')
FROM dual

SELECT ename,sal,job  FROM emp_csx1 WHERE ename=UPPER('scott')

4：TRIM,LTRIM,RTRIM
去除字符串两边，单独左边，单独右边的指定重复字符
SELECT TRIM（'e' FROM 'eeeeliteee'） FROM dual
SELECT LTRIM('eeeeliteee','e') FROM dual

LTRIM,RTRIM在删除字符时，只要左面（右面）的每个字符是第二个参数字符串中的任意一个字符时就去除
SELECT LTRIM('esssffdddsdefsdefsdeeeliteee','efsd') FROM dual

5：LPAD,RPAD 补位函数
LPAD（c1,n,c2）:将c1显示n位长度，若c1不足n位，则左侧补充若干个c2字符，以达到n位长度，若超
过，则从左侧开始截取c1为n位长度并返回

SELECT ename,LPAD(sal,6,'$') FROM emp_csx1

SELECT ename,RPAD(sal,6,'$') FROM emp_csx1

截取都是从左侧开始的，只是补位有区别
LPAD是右对齐，RPAD是左对齐
SELECT ename,LPAD(sal,6,' ') FROM emp_csx1

SELECT ename,RPAD(sal,6,' ') FROM emp_csx1

6：SUBSTR（c,m[,n]）
截取字符串c，从第m个字符开始，连续截取n个字符。
若n不指定，则是截取到字符串末尾，若n超过实际可截取的字符个数也是截取到字符串末尾。
数据库中下标都是从1开始的。

SELECT SUBSTR（'thinking in java',-7,2）FROM dual 

7:INSTR(char1,char2[, n [,m]])
查找char2在char1中位置。
n:从第几个字符开始检索，不写默认为1
m：查找第几次出现，不写默认为1

SELECT INSTR（'thinking in java','in',4,1）FROM dual--6

数值函数：
1：ROUND(n[,m])
对n进行四舍五入，保留小数点后m位
若m不指定则默认为0,0表示保留到整数位
若m为负数，则是小数点前十位以上数字。
-1表示保留到十位，-2表示百位...

SELECT ROUND(45.678,2) FROM DUAL; --45.68

SELECT ROUND(45.678,0) FROM DUAL; --46

SELECT ROUND(45.678,2) FROM DUAL; --45.68

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

SELECT ROUND(55.678,-2) FROM DUAL; --100

2：TRUNC（n[,m]）
参数与ROUND意义相同，区别在于仅截取数字，不做四舍五入。
SELECT TRUNC（45.678,2）FROM dual--45.67
SELECT TRUNC（45.678,0）FROM dual--45
SELECT TRUNC（45.678,-1）FROM dual--40

3:MOD(m,n)
求余数，规则为m除以n
若n为0，则函数直接返回m

SELECT ename,sal,MOD(sal,1000) FROM emp_csx1

4:CEIL,FLOOR
向上取整，向下取整

SELECT CEIL（45.678）FROM dual --46
SELECT FLOOR（45.678）FROM dual --45


日期相关的关键字
SYSDATE,SYSTIMESTAMP
他们对应ORACLE内部的函数，返回一个当前系统时间。不同的在于一个返回DATE类型，另一个返回
时间戳类型。
SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual

日期转换函数
1：TO_DATE()
将一个字符串按照给定的日期格式解析为一个DATE类型数据

SELECT  TO_DATE('2008-08-08 20:08:08','YYYY-MM-DD HH24:MI:SS') FROM dual
 
DATE类型数据可以进行计算：
两个DATE类型之间相减,差为相差的天数。
对一个DATE加减一个数字，等于加减了天数。
日期之间可以比较大小，越晚的越大。

查看明天？
SELECT SYSDATE+1 FROM dual

查看每个员工入职到今天一共多少天？
SELECT ename,SYSDATE-hiredate FROM emp_csx1

SELECT SYSDATE-TO_DATE('1991-09-27','YYYY-MM-DD') FROM dual

SELECT TO_DATE('1991-09-27','YYYY-MM-DD') +10000 FROM dual

在日期格式字符串中除字母和符号外的其他字符都需要使用双引号括起来
SELECT TO_DATE('2008年08月08日','YYYY"年"MM"月"DD"日"')FROM dual

2：TO_CHAR()
可以将日期转换为字符串,TO_CHAR函数实际上可以将其他类型转换为字符串，常用
的是将日期转换为字符串。

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')FROM dual

日期格式中YY,RR的区别：
RR,YY都是用2位数字表示年。
他们在使用TO_DATE函数将一个字符串中2位数字的年解释为实际的年时对世纪的处理方式不同。
YY是根据当前系统所在世纪来解释世纪的。
RR则是根据具体情况判定。

 

SELECT TO_CHAR(TO_DATE('83-05-02','YY-MM-DD'),'YYYY-MM-DD')FROM dual--2083

SELECT TO_CHAR(TO_DATE('83-05-02','RR-MM-DD'),'YYYY-MM-DD')FROM dual--1983


日期常用函数：
1：LAST_DAY(date)
返回给定日期所在月的月底日期

SELECT LAST_DAY(SYSDATE)FROM dual

查看每个员工入职所在月的月底日期？
SELECT ename , LAST_DAY(hiredate)FROM emp_csx1

2:ADD_MONTHS(DATE,i)
对给定日期加指定的月，若i为负数，则是减去指定的月

查看每个员工入职20周年的纪念日？
SELECT ename,ADD_MONTHS(hiredate,12*20)FROM emp_csx1

3:MONTHS_BETWEEN(date1,date2)
计算两个日期之间所相差的月份

查看每个员工入职至今共多少月了？
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate))FROM emp_csx1

4:NEXT_DAY(DATE,i)
返回给定日期第二天开始一周之内的指定周几的日期
SELECT NEXT_DAY(SYSDATE,1)FROM dual

5:LEAST,GREATEST
求最小值与最大值，在日期中使用则是求最早的日期与最晚的日期
SELECT LEAST（SYSDATE，TO_DATE('2008-08-08','YYYY-MM-DD')）FROM dual

SELECT GREATEST（SYSDATE，TO_DATE('2008-08-08','YYYY-MM-DD')）FROM dual

6:EXTRACT                                          
提取指定日期对应时间分量的值
SELECT EXTRACT（YEAR FROM SYSDATE）FROM dual

查看1982年入职的员工？
SELECT ename,hiredate FROM emp_csx1 WHERE EXTRACT(YEAR FROM hiredate)=1982

CREATE TABLE student_csx(
  id NUMBER(4),
  name char(20),
  gender char(1)
);
INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO STUDENT VALUES(1001,'林平之',NULL);
INSERT INTO student(id,name) VALUES(1002,'张无忌')；

INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO student VALUES(1001, '林平之', NULL);--显式插入NULL值
INSERT INTO student(id, name) VALUES(1002, '张无忌');--隐式插入NULL值

SELECT * FROM student_csx

COMMIT

更新为NULL
UPDATE student SET gender=NULL WHERE id=1000

过滤条件判断NULL值：
判断NULL要使用IS NULL 或 IS NOT NULL (不能使用=号)
DELETE FROM student WHERE gender IS NULL

null的运算：
null与字符串连接等于什么也没有做。
null与数字计算，结果还是null。

SELECT ename||NULL FROM emp_csx1

查看每个员工的收入（工资+绩效）
SELECT ename,sal,comm,sal+comm FROM emp_csx1

空值函数
NVL（a1,a2）
当a1为NULL时，函数返回a2的值
当a1不为NULL,函数返回a1自身
所以NVL函数的意义是将NULL值替换为非NULL值
SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp_csx1

查看每个员工的绩效情况，即：
有绩效的则显示“有绩效”，绩效为NULL的则显示为“没有绩效”

NVL2（a1,a2,a3）
当a1不为NULL时，函数返回a2
当a1为NULL时，函数返回a3

SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM emp_csx1

SELECT * FROM userinfo_csx

DROP SEQUENCE seq_userinfo_id_csx

DROP TABLE userinfo_csx
SELECT * FROM userinfo_csx


字段的别名
SELECT子句中查询的内容若是函数或者表达式，那么
在结果集中对应的该字段的名字就是这个函数或表达式，
可读性会变差，为此可以为这样的字段添加别名。这样
在结果集中该字段的名字就是这个字段的别名

SELECT ename NAME,sal salary FROM emp_csx1

别名不区分大小写，若希望区分大小写或者含有空格，那么需要使用双引号将别名括起来。

SELECT ename "name",sal salary FROM emp_csx1

使用>, <, >=, <=, !=, <>, = (其中不等于建议使用<>)
SELECT ename,sal FROM emp_csx1 WHERE sal< 2000;
SELECT ename,sal,JOB FROM emp_csx1 WHERE deptno <>10;
SELECT ename,sal,hiredate FROM emp_csx1 WHERE hiredate >to_date('1980-2-1','YYYY-MM-DD');

使用AND,OR关键字
SELECT ename,sal,JOB FROM emp_csx1 WHERE sal>1000 AND JOB='CLERK';
SELECT ename,sal,JOB FROM emp_csx1 WHERE sal>1000 OR JOB='CLERK';

AND,OR可以连接多个条件，但是需要注意的是AND的优先级高于OR，所以可以通过添加
括号来提高OR的优先级
SELECT ename,sal,JOB FROM emp_csx1 WHERE sal>1000 AND (JOB='SALEMAN' OR JOB='CLERK');

LIKE用于模糊匹配字符串。
支持两个通配符:
%:任意个字符（0到多个）,区分大小写
_:单一的一个字符

SELECT ename,JOB,sal FROM emp_csx1 WHERE ename LIKE '%A%';--含有A的

SELECT ename,JOB,sal FROM emp_csx1 WHERE ename LIKE '_O%';--第二个字母为O

SELECT ename,JOB,sal FROM emp_csx1 WHERE ename LIKE '_O%E_';--第二个字母为O，倒数第二个字母为E

IN(LIST)与 NOT IN(LIST)
判断在列表中和不在列表中，
IN与 NOT IN常用于子查询中。

SELECT ename,JOB FROM emp_csx1 WHERE JOB IN('MANAGER','CLERK');

SELECT ename,JOB FROM emp_csx1 WHERE deptno NOT IN(10,20);

BETWEEN....AND....
判断在一个区间范围内

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

ANY（List）,ALL(LIST)
ANY,ALL是配合>,>=,<,<=使用的

>ALL(List):大于列表中所有的(大于最大的)
>ANY(LIST):大于列表中之一即可(大于最小的)
<ALL(LIST):小于列表中所有的(小于最大的)
<ANY(LIST):小于列表中之一即可(小于最小的)
ANY,ALL通常不会给确定值，这样没有意义，通常使用在判断一个子查询的结果。

SELECT empno,ename,JOB,sal,deptno FROM emp_csx1 WHERE sal>ANY(800,4000,4500);
SELECT empno,ename,JOB,sal,deptno FROM emp_csx1 WHERE sal<ANY(3500,4000,4500);
SELECT empno,ename,JOB,sal,deptno FROM emp_csx1 WHERE sal>ALL(3500,4000,4500);
SELECT empno,ename,JOB,sal,deptno FROM emp_csx1 WHERE sal<ALL(3500,4000,4500);
SELECT * FROM emp_csx1;

查询条件中使用表达式和函数
查询条件中使用函数
SELECT ename,sal,JOB FROM emp_csx1 WHERE ename=UPPER('clerk');
查询条件中使用表达式
select ename,sal,job from emp_csx1 where sal*12>100000;

DISTINCT关键字
DISTINCT可以对结果集中指定字段的重复记录去除
DISTINCT必须紧跟在SELECT关键字之后！

查看公司总共有多少种职位？
SELECT DISTINCT JOB FROM emp_csx1;

DISTINCT对多个字段去重，去重不再保证每个字段
一定没有重复值，去重的原则是这些字段值的组合没
有重复记录。

SELECT DISTINCT job,deptno FROM emp_csx1;

ORDER BY子句
ORDER BY 子句是对结果集按照指定的字段的值进行升序或者降序排列。
ORDER BY 支持ASC,DESC
ASC为升序，从小到大，不写默认就是升序
DESC为降序。
需要注意：ORDER BY 只能定义在DQL的最后一个子句上

对工资进行升序排列：
SELECT ename,job,sal FROM emp_csx1 ORDER BY sal;

对工资进行降序排列：
SELECT ename,job,sal FROM emp_csx1 ORDER BY sal DESC;

ORDER BY 按照多个字段排序时，排序存在优先级，
首先将结果集按照第一个字段的排序方式进行排序，当第一个
字段有重复值的时候，第一个字段值相同的记录之间再按照第二个
字段的方式排序，依此类推。
SELECT ename,deptno,sal FROM emp_csx1 ORDER BY deptno DESC,sal DESC;

查看20号部门的员工工资（从大到小排列）
SELECT ename,deptno,sal FROM emp_csx1 WHERE deptno=20 ORDER BY sal desc;

ORDER BY 排序的字段中若含有NULL值,那么NULL被认定为最大值。

SELECT ename,comm FROM emp_csx1 ORDER BY comm;

聚合函数
聚合函数又称为多行函数，分组函数。
聚合函数是用来对结果集进行统计的。
其中有4个针对值的统计：MAX,MIN,AVG,SUM
还有一个是针对记录数的统计：COUNT
COUNT是统计指定字段不为NULL的记录共有多少条?
count：计算表中数据条数或列中值的个数
count(*)：是表中行的总数


查看公司的最高工资?
SELECT MAX(sal) FROM emp_csx1;

SELECT MAX(sal),MIN(sal),AVG(sal),SUM(sal) FROM emp_csx1;

查看公司员工人数?
SELECT COUNT(ename) FROM emp_csx1;

常见的统计表中记录数的方法:
SELECT COUNT(*) FROM emp_csx1;
SELECT COUNT(1) FROM emp_csx1;

聚合函数忽略NULL值
SELECT COUNT（comm）FROM emp_csx1;--4
SELECT SUM(comm),AVG(comm) FROM emp_csx1;

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

SELECT * FROM emp_csx1;

分组
GROUP BY子句可以将结果集按照指定的字段值一样的
记录进行分组，配合聚合函数可以进行组内统计的工作。
当SELECT中含有聚合函数时,凡不在聚合函数中的单独字段，都需要出现在GROUP BY子句中。

查看每个部门的平均工资?
SELECT AVG（sal),deptno FROM emp_csx1 GROUP BY deptno;

SELECT MAX（sal）,job FROM emp_csx1 GROUP BY job;

GROUP BY按照多个字段分组的原则：这些字段值都一样的记录被划分为一组。

查看同部门同职位的员工各多少人?
SELECT COUNT(*),deptno,job FROM emp_csx1 GROUP BY deptno,job;

查看每个部门的平均工资，前提是该部门的平均工资高于2000?
SELECT AVG(sal),deptno FROM emp_csx1 WHERE AVG(sal)>2000 GROUP BY deptno;
执行上述SQL会出现:此处不允许使用分组函数的错误，原因在于AVG(sal)>2000不应当定义在WHERE中。
过滤的时机不对。
 
WHERE不能使用聚合函数作为过滤条件，原因是过滤时机不对。
WHERE实在查询表中数据的时候逐行进行过滤，将满足条件的记录形成结果集。
而使用聚合函数的结果进行过滤的前提是分组统计，分组是建立在结果集上，而WHERE是用来形成结果集的
过滤。所以使用聚合函数过滤应当是在WHERE之后进行的。

HAVING子句，HAVING子句可以使用聚合函数作为过滤条件。
HAVING必须跟在GROUP BY子句后面（不定义GROUP BY不能单独定义HAVING）。
HAVING是用来添加过滤条件以去除不满足条件的分组的。
SELECT AVG(sal),deptno FROM emp_csx1 GROUP BY deptno HAVING AVG(sal)>2000;

查看平均工资高于2000的那些部门的最高工资是多少?
SELECT MAX(sal),deptno FROM emp_csx1 GROUP BY deptno HAVING AVG(sal)>2000;

关联查询：
关联查询是指关联多张表联合查询记录，结果集中的字段可能来自多张表。
关联查询的关键点在于连接条件，数据库是根据连接条件找到这些表中记录之间的对应
关系，从而从这些记录中获取要查询的字段来构成结果集中的各个字段。

两表关联查询的语法:
select .... from emp,dept where emp.deptno=dept.deptno;
等价于
select .... from emp join dept on emp.deptno=dept.deptno;

三表关联查询的语法:
select ...
from a,b,c where a.id=b.id
and b.id=c.id
等价于
select ...
from a join b on a.id=b.id
join c on b.id=c.id

当某个字段在查询中发现多个表都存在时，要求必须指明该字段所属哪张表。可以通过：
表名.字段名 的形式标注，也可以为表添加别名，然后通过：表别名.字段名 的形式标注，
这样可以降低编写SQL语句的复杂度，增加可读性。
查询每个员工的名字以及其所在部门的名字?
SELECT e.ename,d.deptno,d.dname 
FROM emp_csx1 e,dept_devil d
WHERE e.deptno = d.deptno;

关联查询中要求所有过滤条件必须与关联条件同时成立。
查看NEW YORK工作的员工?
SELECT e.ename,e.deptno,d.dname,d.loc
FROM emp_csx1 e,dept_devil d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK';

N张表关联查询，至少要有N—1个连接条件。
不指定连接条件或者连接条件无效时，会产生笛卡尔积，这通常
是一个无意义的结果集，应当避免。

SELECT e.ename,d.dname
FROM emp_csx1 e,dept_devil d;

内连接也是关联查询的一种。

SELECT e.ename,d.dname
FROM emp_csx1 e,dept_devil d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK';

SELECT e.ename,d.dname
FROM emp_csx1 e JOIN dept_devil d
ON e.deptno=d.deptno
WHERE d.loc='NEW YORK';

关联查询中，不满足连接条件的记录不会被查询出来。

外连接
外连接在进行关联查询时，除了可以将满足连接条件的记录查
询出来之外，还可以将不满足连接条件的记录也查询出来。

外连接分为：左外连接，右外连接，全外连接
左外连接：以JOIN左侧表作为驱动表，该表中的所有记录都会查询出来，当某条记录不满足
连接条件时，结果集中该条记录中来自JOIN右侧表的字段的值全部为NULL。

SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp_csx1 e LEFT OUTER JOIN dept_devil d
ON e.deptno=d.deptno;

SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp_csx1 e RIGHT OUTER JOIN dept_devil d
ON e.deptno=d.deptno;

SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp_csx1 e FULL OUTER JOIN dept_devil d
ON e.deptno=d.deptno;

SELECT * FROM emp_csx1;
SELECT * FROM dept_devil;

自连接
当前表的一条记录可以对应当前表自己的多条记录。
自连接的设计目的是为了解决属性相同，但是数据间又存在上下级关系的树状结构数据时使用

查询每个员工的名字和上司的名字？
SELECT e.ename,m.ename
FROM emp_csx1 e LEFT OUTER JOIN emp_csx1 m
ON e.mgr=m.empno;

SELECT * FROM emp_csx1;






子查询
子查询是一条查询语句，它是嵌套在其他SQL语句当中的，目的是为外层的SQL语句提供数据。

子查询在DDL,DML,DQL中都可以使用

查看谁的工资高于CLARK?
SELECT ename,sal
FROM emp_csx1
WHERE sal>(SELECT sal FROM emp_csx1 WHERE ename='CLARK');

查看CLARK同部门的员工?
SELECT ename,deptno
FROM emp_csx1
WHERE deptno=(SELECT deptno FROM emp_csx1 WHERE ename='CLARK');

SELECT * FROM emp_csx1;

查看哪些员工的工资是高于公司平均工资的?
SELECT ename,sal
FROM emp_csx1
WHERE sal>(SELECT AVG(sal) FROM emp_csx1);

DDL中使用子查询，可以将一个子查询的结果集当作一张表快速创建出来。

CREATE TABLE employee_csx2
AS
SELECT e.empno,e.ename,e.sal,e.job,e.deptno,d.dname,d.loc
FROM emp_csx1 e LEFT OUTER JOIN dept_devil d
ON e.deptno=d.deptno;
SELECT * FROM employee_csx2;
DESC employee_csx2;

将CLARK所在部门的员工工资上浮10%
UPDATE emp_csx1
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp_csx1 WHERE ename='CLARK');

SELECT * FROM emp_csx1;

子查询根据查询结果分为：
单行单列子查询，多行单列子查询，多行多列子查询

其中单列子查询常用在过滤条件中，而多列子查询常当做表使用。
对于多行单列子查询，在进行过滤判断是要配合IN，ANY，ALL使用

查看与职位是SALESMAN同部门的员工有哪些?
SELECT ename,deptno
FROM emp_csx1
WHERE deptno IN(SELECT deptno FROM emp_csx1 WHERE job='SALESMAN');

查看比职位是CLERK和SALESMAN工资都高的员工?
SELECT ename,sal
FROM emp_csx1
WHERE sal>ALL(SELECT sal FROM emp_csx1 WHERE job IN('CLERK','SALESMAN'));
 
EXISTS关键字
用于在WHERE中作为过滤条件使用。其后跟一个子查询，只要该子查询可以
查询出一条记录，那么EXISTS就认为满足条件。

查看有员工的部门信息?
SELECT deptno,dname 
FROM dept_devil d
WHERE EXISTS(SELECT *FROM emp_csx1 e WHERE e.deptno=d.deptno);

SELECT * FROM emp_csx1;

SELECT * FROM dept_devil;

查看谁是别人的上司?
SELECT m.empno,m.ename,m.sal,m.job
FROM emp_csx1 m
WHERE EXISTS(SELECT *FROM emp_csx1 e WHERE e.mgr=m.empno);

查看部门的最低工资?前提是该部门的最低工资要高于30号部门的最低工资?
SELECT MIN(sal),deptno
FROM emp_csx1
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp_csx1 WHERE deptno=30);

COMMIT;

查看哪些员工的工资是高于其所在部门的平均工资的?
SELECT e.ename,e.sal,e.deptno
FROM emp_csx1 e,(SELECT AVG(sal) avg_sal,deptno
                FROM emp_csx1
                GROUP BY deptno)t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal;

查看每个部门最高工资是谁?
列出该员工名字，职位，工资，部门号
SELECT e.ename,e.job,e.sal,e.deptno
FROM emp_csx1 e,(SELECT MAX(sal) max_sal,deptno 
                FROM emp_csx1
                GROUP BY deptno)t
WHERE e.deptno=t.deptno
AND e.sal=t.max_sal;

子查询在SELECT部分：
SELECT e.ename,e.sal,
                (SELECT d.dname FROM dept_devil d
                WHERE d.deptno=e.deptno)dname
FROM emp_csx1 e;


分页查询
分页查询就是将一个查询语查询的数据分批分段查询出来。
这样做的好处在于，当一个查询语句可以查询的结果集非常大时，
有效的减少网络传输的数据量，提高响应速度，降低系统开销。

ORACLE中使用ROWNUM解决分页。
ROWNUM
ROWNUM是一个伪列，实际不存在于任何表中，但是每张表都可以查询该字段，该字段的值为
查询的结果集中每条记录的行号。该字段的值是伴随查询的过程中动态生成的，只要可以从表
中查询出一条记录，该字段的值就是这条记录的行号，行号从1开始递增。

在使用ROWNUM字段为结果集遍行号的过程中，不要使用ROWNUM做>1以上数字的判断为过滤条件，
否则将得不到任何结果。

SELECT ROWNUM,ename,sal,job,deptno
FROM emp_csx1
WHERE ROWNUM<10;

查看第6-10行的记录?
SELECT * 
FROM(SELECT ROWNUM rn,ename,sal,job,deptno FROM emp_csx1)
WHERE rn BETWEEN 6 AND 10;

查看公司工资排名的第6-10名?
SELECT *
FROM(SELECT ROWNUM rn,t.*
      FROM (SELECT ename,sal,job,deptno 
           FROM emp_csx1
           ORDER BY sal DESC)t)
WHERE rn BETWEEN 6 AND 10

SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT ename,sal,job,deptno
          FROM emp_csx1
          ORDER BY sal DESC) t
     WHERE ROWNUM <=10)     
WHERE rn>=6

计算分页公式
PageSize:每页显示的条目数
Page:要显示的页数
Start:(Page-1)*PageSize+1
end:PageSize*Page

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

DECODE函数，可以实现分之结构。

统计公司人数，所有ANALYST与MANAGER看做一组，其他职位员工看做另一组，统计人数？
SELECT COUNT（1）,DECODE(job,
                        'MANAGER','VIP',
                        'ANALYST','VIP',
                        'OTHER')
FROM emp_csx
GROUP BY DECODE(job,
                'MANAGER','VIP',
                'ANALYST','VIP',
                'OTHER');

SELECT ename,job,sal,
  CASE job WHEN 'MANAGER' THEN sal*1.2
           WHEN 'ANALYST' THEN sal*1.1
           WHEN 'SALESMAN' THEN sal*1.05
           ELSE sal END
  bonus
FROM emp_csx1;  
    
DECODE函数在分组查询中的应用       
SELECT deptno,dname,loc
FROM dept_devil
ORDER BY
    DECODE(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3);

排序函数
排序函数允许按照指定字段分组，再按照指定字段排序，然后生成组内编号。

查看每个部门的工资排名：
ROW_NUMBER函数:生成组内连续且唯一的数字
SELECT
  ename,deptno,sal,
  ROW_NUMBER() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
  ) rank
FROM emp_csx1;

RANK函数:生成组内不连续也不唯一的数字
SELECT
  ename,deptno,sal,
  RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
  ) rank
FROM emp_csx1;

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

创建sales_heihei表格：
CREATE TABLE sales_heihei(
  year_id NUMBER NOT NULL,
  month_id NUMBER NOT NULL,
  day_id NUMBER NOT NULL,
  sales_heihei_value NUMBER(10,2) NOT NULL
  );
  INSERT INTO sales_heihei
  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 month_id,
    TRUNC(DBMS_RANDOM.value(1,100),2) AS sales_heihei
  FROM dual
  CONNECT BY level<=1000;
  
SELECT * FROM sales_heihei;
COMMIT;

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并，交，差

UNION 并集
SELECT ename,job,sal FROM emp_csx1
WHERE job='MANAGER'
UNION
SELECT ename,job,sal FROM emp_csx1
WHERE sal>2500;

UNION ALL 全并
SELECT ename,job,sal FROM emp_csx1
WHERE job='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_csx1
WHERE sal>2500;

INTERSECT 交集
SELECT ename,job,sal FROM emp_csx1
WHERE job='MANAGER'
INTERSECT
SELECT ename,job,sal FROM emp_csx1
WHERE sal>2500;

MINUS 差集
SELECT ename,job,sal FROM emp_csx1
WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp_csx1
WHERE sal>2500;



查看每天的营业额?
SELECT year_id,month_id,day_id,
       SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id

查看每月营业额?
SELECT year_id,month_id,
       SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id

每年营业额?
SELECT year_id,
       SUM(sales_value)
FROM sales_tab
GROUP BY year_id
ORDER BY year_id

所有营业额?
SELECT SUM(sales_value)
FROM sales_tab

查看每天，每月，每年，以及总额?
SELECT year_id,month_id,day_id,
       SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,NULL,
       SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,NULL,NULL,
       SUM(sales_value)
FROM sales_tab
GROUP BY year_id
UNION ALL
SELECT NULL,NULL,NULL,
       SUM(sales_value)
FROM sales_tab

高级分组函数:
1:ROLLUP函数
GROUP BY ROLLUP(a,b,c)
等同于
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表

SELECT year_id,month_id,day_id,
       SUM(sales_heihei_value)
FROM sales_heihei
GROUP BY 
   ROLLUP(year_id,month_id,day_id)
   
   
2:CUBE函数
CUBE(a,b,c)
CUBE分组方式是全方位的。
分组方式是2的参数个数次方。
a,b,c
a,b
a,c
b,c
a
b
c
全表

SELECT year_id,month_id,day_id,
       SUM(sales_heihei_value)
FROM sales_heihei
GROUP BY 
   CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id

3:GROUPING SETS函数:
可以自定分组方式，每个参数为一种分组方式，然后将这些分组统计的结果并在一个结果集显示。

查看每天与每月的营业额?
SELECT year_id,month_id,day_id,SUM(sales_heihei_value)
FROM sales_heihei
GROUP BY GROUPING SETS(
  (year_id,month_id,day_id),(year_id,month_id)
)
ORDER BY year_id,month_id,day_id;



视图：
视图是数据库对象之一
视图在SQL语句中体现的角色与表一致，但它不是一张真实存在的表，
只是对应了一个查询语句的结果集。

创建视图：
CREATE VIEW v_emp_10_csx
AS
SELECT empno,ename,sal,deptno
FROM emp_csx1
WHERE deptno=10;

查看视图的结构
DESC v_emp_csx_10;
查看视图数据
SELECT * FROM v_emp_10_csx;
删除视图
DROP VIEW v_emp_10_csx;

当视图对应的子查询中含有函数或者表达式时，那么必须指定别名。
CREATE VIEW v_emp_10_csx
AS
SELECT empno id,ename name,sal*12 sal,deptno
FROM emp_csx1
WHERE deptno=10;

DESC v_emp_10_csx;
SELECT * FROM v_emp_10_csx;

视图根据对应的子查询分为：简单视图，复杂视图，连接视图
简单视图:对应的子查询不含有函数，表达式，去重，分组。
复杂视图：不是简单视图的都是复杂视图
连接视图：对应的子查询有多表关联查询，连接视图算作复杂视图
简单视图可以进行DML操作，但是复杂视图不允许进行DML操作。

修改视图：视图的结构取决于对应的子查询，所以修改的视图就是替换
对应的子查询。（OR REPLACE 的含义是若视图存在，就替换，若不存在，就创建出来）
CREATE OR REPLACE VIEW v_emp_10_csx
AS
SELECT empno id,ename name,sal,deptno
FROM emp_csx1
WHERE deptno=10;

对视图进行DML操作就是对视图数据来源的基础表进行的操作。
虽然可以对简单视图进行DML操作，但是DML操作也不能违反基表的约束。
INSERT INTO v_emp_10_csx
(id,name,sal,deptno)
VALUES
(1001,'jack',2000,10);

SELECT * FROM v_emp_10_csx;
SELECT * FROM emp_csx1;

UPDATE v_emp_10_csx
SET sal=3000
WHERE id=1001;

SELECT * FROM v_emp_10_csx;
SELECT * FROM emp_csx1;

DELETE FROM v_emp_10_csx
WHERE id=1001;

对简单视图的不当DML操作会污染基础表数据
对简单视图进行DML操作后，视图对基础表做对应操作，但是影响的
数据视图对其不可见时，就是对基表的数据污染。
(其中INSERT，UPDATE都有污染，只有删除DELETE没有污染)
INSERT INTO v_emp_10_csx
(id,name,sal,deptno)
VALUES
(1001,'jack',2000,20);

UPDATE v_emp_10_csx
SET deptno=20;

DELETE v_emp_10_csx
WHERE deptno=20;

SELECT * FROM v_emp_10_csx;
SELECT * FROM emp_csx1;

为视图添加检查选项可以避免对基础表产生的数据污染。
检查选项要求对视图进行的DML操作后该记录视图对其可见，否则
不允许操作。
CREATE OR REPLACE VIEW v_emp_10_csx
AS
SELECT empno id,ename name,sal,deptno
FROM emp_csx1
WHERE deptno=10
WITH CHECK OPTION;--约束视图

对视图添加只读选项后，该视图不允许进行DML操作
CREATE OR REPLACE VIEW v_emp_10_csx
AS
SELECT empno id,ename name,sal,deptno
FROM emp_csx1
WHERE deptno=10
WITH READ ONLY;--只读选项

数据字典：
数据字典是一系列的表，这些表的数据由数据库自行维护，记录的
是一些清单信息，方便随时查询。
USER_OBJECTS:记录用户创建的所有数据库对象
USER_VIEW:记录用户创建的所有视图
USER_TABLES:记录用户创建的所有表

SELECT * FROM user_objects;

在数据字典USER_OBJECT中查询csx视图名称
SELECT object_name
FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE'%CSX';

SELECT object_name
FROM user_objects
WHERE object_name LIKE'%CSX%'
OR object_name LIKE'%DEVIL';

SELECT view_name,text
FROM user_views
WHERE view_name LIKE '%CSX';

创建一个含有各个部门工资情况的视图
CREATE VIEW v_emp_dept_sal_csx
AS
SELECT MAX(e.sal) max_sal,
       MIN(e.sal) min_sal,
       AVG(e.sal) avg_sal,
       SUM(e.sal) sum_sal,
       d.deptno,d.dname
FROM emp_csx e JOIN dept_csx d
ON e.deptno=d.deptno
GROUP BY d.deptno,d.dname;
 
SELECT * FROM v_emp_dept_sal_csx;

查看高于所在部门平均工资的员工？
SELECT e.ename,e.sal,e,deptno
FROM emp_csx e JOIN v_emp_dept_sal_csx v
ON e.deptno=v.deptno
WHERE e.sal>v.avg_sal;

序列
序列也是数据库对象之一，序列的作用是根据指定的规则生成一系列
数字。序列通常是为某张表的主键字段提供值使用。
主键：通常每张表都会有主键字段，该字段的值要求非空且唯一，使用
该字段来确定表中的每一条记录使用。

创建序列
CREATE SEQUENCE seq_emp_id_csx
START WITH 1
INCREMENT BY 1;

序列提供了两个伪列：
NEXTVAL：获取序列的下一个数字，第一次获取时返回的是START WITH指定的数字，
以后则是最后获取的数字加上步长得到的。
NEXTVAL会导致序列发生步进，且不可回退。

CURRVAL：获取序列当前数字，即：最后一次生成的数字。新创建的
序列需要至少调用一次NEXTVAL以后才可以使用。CURRVAL不会发生序列的步进。

SELECT seq_emp_id_csx.NEXTVAL
FROM dual;

SELECT seq_emp_id_csx.CURRVAL
FROM dual;

INSERT INTO emp_csx
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id_csx.NEXTVAL,'jack',3000,'CLECK',10)

SELECT * FROM emp_csx;

删除序列
DROP SEQUENCE seq_emp_id_csx;

索引
索引是数据库对象之一，作用是加快查询效率
索引的创建与使用是数据库自行完成的。
经常出现在WHERE中的字段，或者去重，排序，关联条件的字段可以添加索引。
（模糊查询LIKE不适合用索引）

在EMP_CSX表中的ENAME列上建立索引
CREATE INDEX idx_emp_csx_ename ON emp_csx(ename);

复合索引
CREATE INDEX idx_emp_csx_job_sal ON emp_csx(job,sal);

创建基于函数的索引
CREATE INDEX emp_csx_ename_upper_idex ON emp_csx(UPPER(ename));

重建(修改)索引
ALTER INDEX idx_emp_csx_ename REBUILD;

删除索引
DROP INDEX idx_emp_csx_ename;

约束：
建表时创建非空约束
CREATE TABLE employees_csx(
  eid NUMBER(6),
  name VARCHAR2(30) NOT NULL,
  salary NUMBER(7,2),
  hiredate DATE CONSTRAINT employees_csx_hiredate_nn NOT NULL);

DESC employees_csx;

修改表时添加非空约束
ALTER TABLE employees_csx
MODIFY(eid NUMBER(6) NOT NULL);

取消非空约束
ALTER TABLE employees_csx
MODIFY(eid NUMBER(6) NULL);

唯一性约束
唯一性约束要求该字段在整张表中每条记录的值都不允许重复，NULL除外。

添加唯一性约束
CREATE TABLE employees1_csx(
  eid NUMBER(6) UNIQUE,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hiredate DATE,
  CONSTRAINT employees1_csx_email_uk UNIQUE(email)
);

INSERT INTO employees1_csx
(eid,name,email)
VALUES
(NULL,'jack',NULL);

INSERT INTO employees1_csx
(eid,name,email)
VALUES
(2,'jack','110614259@qq.com');

SELECT * FROM employees1_csx;

DELETE FROM employees1_csx;

添加唯一性约束
在建表之后增加唯一性约束条件
ALTER TABLE employees1_csx
ADD CONSTRAINT employees1_csx_name_uk UNIQUE(name);

主键约束
主键约束要求字段非空且唯一，且一张表只能有一个主键约束。

添加主键约束
CREATE TABLE employees2_csx(
  eid NUMBER(6) PRIMARY KEY,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hiredate DATE
);

添加检查约束
员工的薪水必须大于2000元
ALTER TABLE employees2_csx
ADD CONSTRAINT employees2_csx_salary_check
CHECK(salary>2000);
正常插入
INSERT INTO employees2_csx(eid,name,salary)
VALUES(1236,'donna noble',2500);
试图修改职员的薪水为1500元，更新失败
UPDATE employees2 SET salary=1500 WHERE eid=1236;


将一张表中的某一记录(行)一次性全部插入到另外一张表中?
语法:insert into 目标表名 select * from 源表名
insert into wf_task(id,bind_id,owner,target,status,title,begintime,expiretime,workflowtype,priority,from_point,wf_style,read_task,owner_dpt_id,wfid,wfsid,orgid,dptid,roleid,oa)
select id,bind_id,owner,target,status,title,begintime,expiretime,workflowtype,priority,from_point,wf_style,0,owner_local,wfid,wfsid,orgid,dptid,roleid,oa from wf_task_log where id=2934930;

oracle中的case when...then...实例?
update tbs_hse_danger_factor set 
status=case when status='0' then '1' else '0' end "
where factorid='100';