数据库学习day1-3
之前大二的时候学过SQL server ，现在过了一年，学习ORCLE ，拾起以前的知识，一个星期一个知识块。。
DDL Data definition Language 数据库定义语言：
对数据库对象操作的语言
表，视图，索引，序列。
DML Data 数据库操纵语言：
对数据的操纵的语言，增删改
TCL Transaction Control Language 事务控制语言：
用来维护数据一致性的语句(原子性)
提交 COMMIT
回滚 ROLLBACK
保存点 SAVEPOINT
每次的DML操作都伴随着TCL的操作
客户端向DBMS发送的SQL DML语句在DBMS上都是以事务来进行的，
TRANS BEGIN
SQL
COMMIT / ROLLBACK
TRANS FINISH
只有当你COMMIT 或者ROLLBACK 之后DML 的SQL才能真正生效，否则就只是假的操纵，也就是只是在缓存中执行了SQL，而没有真正执行到数据库中
DQL Data Query Language 数据查询语言：(难点)
SELECT
用来查询所需的数据
DCL: Data Control Language 数据控制语言：
用于执行权限的授予和收回操作
GRANT:授予
REVOKE:回收
CREATE USER:创建用户
dual 虚表
SELECT SYSDATE FROM dual
不存在结构的一个系统表
SELECT ，后面可以跟要查询的字段，可以是表中的具体字段，函数，或者表达式
字符串函数：
CONCAT(列1，列2.。。。) 将N列合并 成一列， 数据连接起来
LENGTH（varchar类型的字段） 求字符串的长度
UPPER() LOWER() INITCAP() 将字符串 转化大写、小写、首字母大写
TRIM（） 取出字符串两边指定重复字符
SELECT TRIM('E' FROM 'EElisEE') FROM dual
LPAD() RPAD()
左右补齐函数
SELECT LPAD(sal,5,'$') FROM emp
如果补齐的是空格，就是左对齐的效果，RPAD（）右对齐
数字函数：
MOD()
SELECT ename,sal,MOD(sal,1000) FROM emp
MAX()最大
MIN()最小
SQL 时间类型：
1、DATE 最多精确到秒 7个字节 世纪，年，月，日，时，分，秒
2、TIMESTAMP 时间戳 可以精确到ns 前面7个字节和DATE一样，或者11个字节，更精确
3、SYSDATE ORCLE 的一个内部系统时间函数，表示的是当前系统的时间，精确到秒
显示格式为“DD-MON-RR”
4、SYSTIMESTAMP 同样是一个内部系统时间戳函数，返回当前系统时间戳
到毫秒
5、TO_DATE()将时间格式以某种形式显示出来
SELECT TO_DATE('2017-11-12 20:10:20','YYYY-MM-DD hh24:MI:SS') FROM dual
2017-11-12 20:10:20
6、时间可以相加减，得出的结果为相差的天数
SELECT SYSDATE-hiredate FROM emp
SELECT SYSDATE-TO_DATE('1996-2-29','YYYY-MM-DD') FROM dual
把字符串转成日期
算算自己活了多少天、、、
7911.912372685185185185185185185185185185
7911天。。 感慨一下。
反过来 TO_CHAR（）是把一个日期时间按给定形式转换成字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM dual
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH21:MI:SS') FROM dual
7、LAST_DAY()
返回某个月的最后一天
SELECT LAST_DAY(SYSDATE) FROM dual
2017-10-31 22:13:32
8、ADD_MONTHS(DATE,I) 给指定的日期加上指定的月数
I可以为负数
SELECT ADD_MONTHS(SYSDATE,1) FROM dual
2017-11-27 22:15:52
9、MONTHS_BETWEEN(DATE1，DATE2) 得到两个时间之间相差的月份
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2000-11-11','YYYY-MM-DD')) FROM dual
203.546145086618876941457586618876941458
10、NEXT-DAY(DATE,CHAR) 查询下一个周几是哪天 具体周几由CHAR来决定
SELECT NEXT_DAY(SYSDATE,'WEDNESDAY') FROM dual
或者可以直接用1-7来表示周日到周六
11、LEAST(VAR1，VAR2，...)
GREATEST(VAR1，VAR2，....)
比较函数，可以有多个参数，返回的是最大/最小的那个
--SELECT LEAST(1,2,3,4,5) FROM dual
--最小
SELECT GREATEST(1,2,3,4,5) FROM dual
--最大
SELECT LEAST(SYSDATE,TO_DATE('2011-11-11','YYYY-MM-DD')) FROM dual
返回 2011-11-11
SELECT LEAST(SYSDATE,TO_DATE('2011-11-11','YYYY-MM-DD'),TO_DATE('2000-11-11','YYYY-MM-DD')) FROM dual
返回 2000-11-11
12、EXTRACT()
提取指定日期的时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual
SELECT EXTRACT(DAY FROM SYSDATE) FROM dual
13、NVL(arg1，arg2) 空值函数
若arg1 为NULL 则返回arg2的值
若arg1不为NULL 返回本身
-- SELECT NVL(NULL,1) FROM dual
1
SELECT NVL(1,2) FROM dual
1
-- SELECT ename,sal,comm,sal+comm FROM emp
-- WHERE comm is NOT NULL
等同
SELECT ename,sal,comm,(sal+NVL(comm,0)) FROM emp
14、NVL2(arg1,arg2,arg3) 根据arg1是否为NULL 显示不同结果
arg1 is not NULL -> arg2
arg2 is NULL --> arg3
SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM emp
INSERT 插入SQL
INSERT INTO table_name(cols) VALUES()/VALUE()
插入时VALUES 和 VALUE 的区别
VALUE插入一行
VALUES插入一行
可以看出在插入单行时，使用VALUES比较快
VALUE插入多行
VALUES插入多行
对比之下，插入多行时，用VALUE比较快
根据所得出的结论，应该在插入单行的时候使用VALUES，在插入多行的时候使用VALUE
DELETE FROM table_name WHERE
删除的判断条件是NULL时 应该用is / is not 而不是=
NULL 表示的是一种状态，而不是值
UPDATE 跟新SQL
UPDATE table_name SET cols=?...
WHERE
SQL查询：
SELECT clos FROM table_name
WHERE 条件
和与 AND OR
LIKE 模糊匹配
_ 匹配一个
% 匹配0个或多个
SELECT ename FROM emp
WHERE ename LIKE '_M%'
查找emp 中 ename 字段 M前只有一个字符的数据
IN or NOT IN 在或者不在列表中
SELECT ename,sal FROM emp
WHERE sal IN (1000,2000,3000,4000)
--查看工资在列表中的数据项
当然可以嵌套查询 所要查询的数据IN or NOT IN 子查询的结果集中
ANY ALL 条件查询满足ANY列表的任意一个 或者 满足 ALL列表的每一个 的数据项
-- SELECT ename,sal FROM emp
-- WHERE sal > ANY(1000,2000,3000)
--ANY 条件符合ANY列表中的任意一个
SELECT ename,sal FROM emp
WHERE sal > ALL(1000,2000,3000)
--ALL 条件符合ALL列表中的每一个
查询条件中使用函数或者表达式：
-- SELECT ename,sal,comm FROM EMP
-- WHERE ename=UPPER('allen')
-- 函数
SELECT ename,sal,comm FROM EMP
WHERE sal * 12 > 30000
--表达式
ORDER BY 对结果集排序
ASC 不写默认就是ASC 升序
DESC 降序
eg:
SELECT ename,sal FROM emp
ORDER BY sal
ORDER BY sal DESC
ORDER BY 可以有多个字段，因为可能有时候某些字段可能会有重复的值，这时候就可以根据后面的字段和排序方法来排
ORDER BY clos1 sort1，cols2 sort2,...... 当然也可以不指定排序方式，就会用前一个的排序方式
聚合函数：用来统计的函数，返回一行数据，如，最大，最小，平均，等等。。。。有时也叫分组函数
对某字段进行统计的函数
1、MAX() MIN() AVG() 分别是求指定字段的最大和最小值
SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp
用AVG时如果字段有NULL值，是不会算入除数的，所以有时如果为了保证平均，用上NVL（字段，0），来保证平均正确
2、COUNT() 查看某字段或者某些字段的非NULL记录数
SELECT COUNT(*) FROM emp 查看表有多少非NULL数据
3、SUM() 对字段求和
SELECT SUM(sal) FROM emp
分组：GROUP BY
将查询出来的结果集按照某种限定分成若干个组（分表），分组是和聚合函数一起配合来使用的，
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
根据区号来统计相同区号的职工的平均薪资
求某一个字段相同的一组数据的聚合函数值、
SELECT 的只能是分组依据以及聚合函数 这里的deptno,AVG（sal） 其他字段不能出现
可以根据多个条件分组
SELECT job,deptno,AVG(sal) FROM emp
GROUP BY job,deptno
GROUP BY 分组后可在在后面加上限制条件
HAVING 限制条件 （筛选条件）
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
只对分组后平均薪资大于2000的显示
--HAVING deptno=10
SELECT deptno,MAX(sal),MIN(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000
查看平均薪资大于2000的部门的最高薪资和最低薪资
WHERE 条件是不能加聚合函数的，他只能接表达式或者子查询，WHERE 只能在一个已知列表中判断
SELECT 条件的执行顺序
FROM 1
WHERE 2
GROUP BY 3
HAVING 4
SELECT 5
ORDER BY 6
中 尽量少用* 数据库在遇见*之后会通过查询字典依次转换成列名，消耗时间
SQL 关联查询：
多表查询
根据多张表之间的对应关系，将所需的不在一张表的数据查询展示出来，这个对应关系也叫连接条件
SELECT ename,dname
FROM emp,dept
WHERE EMP.deptno=DEPT.deptno
可以在连接多表的时候给表取别名， FROM emp a,dept b
JOIN ON 内连接
SELECT ename,DNAME
FROM EMP JOIN DEPT
ON EMP.deptno = DEPT.DEPTNO
多少个表就多少个JOIN ON
这种方式和WHERE 中加是一样的，但是JOIN ON 的层次更清晰，就是你表连接和你的筛选条件是分开的，当然这只是两种不同标准而已，
具体用哪种速度快，还是和用的数据库版本有关。
外连接：
左外连接，右外连接，全外连接
左：左边表和右边表连接时，左表为主表，当没找到满足连接条件的右表数据时，连接的时候右表的除了公共属性外的属性都填NULL
右：同左，主表为右
全，结合左右
SELECT ename,dname
FROM EMP
LEFT | RIGHT | FULL OUTER JOIN dept
ON EMP.deptno = dept.deptno
或者也可以：
SELECT ename,dname
FROM EMP
JOIN dept
ON EMP.deptno(+) = dept.deptno 右外
ON EMP.deptno= dept.deptno(+) 左外
没有全外。。
自连接：
当前表的一条记录可以对应
当前表自己的多条记录
自连接是为了解决同类型数据
但是又存在上下级关系的树状结构数据时使用
应用：多级目录
含有上下级关系的都应该用自连接。
eg:
SELECT a.name,b.name
FROM employee a,employee b
WHERE a.pid=b.id
子连接：
嵌套查询
扩展应用，子查询不但可以在DQL中，也可以在DDL，DML中，
在DDL中使用子查询，可以根据子查询结果快速创建一张表，基于你现有表的数据创建一张有数据的表
AS (
SELECT a.empno,a.ENAME,a.job,a.MGR,
a.HIREDATE,a.sal,a.comm,a.deptno,
b.dname,b.loc
FROM emp a,dept b
WHERE a.DEPTNO=b.DEPTNO
)
省去了你去一条一条INSERT 。。很方便
SELECT ename name FROM emp 类似给表的别名，也可以对列赋别名，
当子查询中有表达式时，就必须给别名
在DML中使用子查询：
DELETE FROM emp
WHERE deptno=(
SELECT deptno FROM emp
WHERE ename='CLARK'
)
高级查询： 续
1、子查询
2、EXISTS 关键字 存在 只要EXISTS 后面的子查询 查询的数据有效，至少能返回一条记录，就返回真，
SELECT deptno,dname FROM dept a
WHERE EXISTS (
SELECT * FROM emp b
WHERE a.deptno=b.deptno
)
在dept 表中取一条数据 ，判断EXISTS 是否为真， 是就保留，否则跳过，知道最后一条
每次判断都会查找EXISTS 的子查询中的表遍历一遍
EXISTS 关注的点是 有没有满足的数据。
3、子查询在FROM语句中 父查询查询范围在一个虚表中，也称作内视图或者匿名视图
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avg,deptno FROM emp
GROUP BY deptno) t
WHERE e.deptno=t.DEPTNO
AND e.sal > t.avg
和多表查询是一个意思，但是这里要JOIN 的表没有实际存在，需要你查询出来作为一个虚表来进行连接查询。
4、子连接在SELECT 段， 可以认为是外连接的一种表现形式，更灵活
SELECT e.ename,e.sal,(SELECT dname FROM dept d
WHERE e.deptno=d.deptno),e.deptno
FROM emp e
这里应该取个别名。。。。
分页查询：
分页查询是将查询表中数据时分段查询，而不是一次性将数据全部查询出来
有时查询的数据十分庞大，这会导致系统资源消耗大，响应时间长，数据冗余严重
数据库一般都支持分页，但是不同数据库的分页方式（语法）不同（方言）。
减缓数据库数据传输到客户端的压力
MYSQL : SELECT cols FROM tables_name limits begin,everyount
ORACLE: ORACLE中分页是基于伪列ROWNUM实现的
ROWNUM 不存在于任何一张表中，但是所有表都可以查询该字段，该字段是随着查询自动生成的，
每当可以从表中查询出一条记录时，该字段的值即为该条记录的行号，从1开始递增
SELECT ename FROM emp
SELECT ROWNUM FROM emp
查询需要的数据段
SELECT ename FROM emp
WHERE ROWNUM BETWEEN 1 AND 5
要注意的是 ROWNUM 是在你查询到这条数据判断为满足全部约束之后才被编上号，
如果
SELECT * FROM emp
WHERE ROWNUM BETWEEN 2 AND 4
或者说 ROWNUM > 1 是查询不出任何数据的
当你查到第一条数据时，他的ROWNUM 为1 ，但是你的WHERE 中的判断条件为ROWNUM > 1
所以就没取着，又查询下一条记录，下一条ROWNUM依旧是1（因为说过只有在查询出满足全部
条件的数据时候，ROWNUM才被赋值++），所以就查询不出任何数据
这就很麻烦，因为你每次分页的时候就无法分了，
所以就可以 把编号和属性当成一个新表，查询出分页数据，
1、排序
2、加编号
3、取范围
SELECT * FROM (SELECT ROWNUM cols,t.* FROM
(SELECT ename,sal FROM emp
ORDER BY sal DESC) t)
WHERE cols BETWEEN 6 AND 10
查询工资在第六到第十的员工