数据库学习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 删除SQL

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中使用子查询，可以根据子查询结果快速创建一张表，基于你现有表的数据创建一张有数据的表

CREATE TABLE empdatils
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

查询工资在第六到第十的员工