温故而知新：Oracle基础知识复习(一)

一、SQL基础

SQL：结构化查询语言，数据库查询和程序设计语言。可以在Oracle数据库中创建、存储、更新、检索和维护数据。

1.1.一般将SQL分为四个部分：

数据定义语言（DDL）：CREATE    DROP    ALTER

数据操作语言（DML）：SELECT    INSERT    UPDATE    DELETE

数据控制语言（DCL）：GRANT    REVOKE

数据库事务处理：COMMIT    ROLLBACK    SAVEPOINT

1.2.规范化设计（三范式）：

第一范式：实体的所有属性必须是单值的并且不允许重复；
第二范式：实体的所有属性必须依赖于实体的惟一标识；
第三范式：一个非惟一标识属性不允许依赖于另一个非惟一标识属性。

1.3.SQL中五种基本数据类型： 字符型、 文本型、数值型、逻辑型、日期型。

1.4.SQL的执行顺序：

SELECT                               （3）

        FROM                           （1）

            WHERE                    （2）

例子：显示各部门的平均工资，最高工资，最低工资和总工资列表，并按平均工资高低顺序排序。

SELECT dname 部门,AVG(sal) 平均工资,MAX(sal) 最高工资,MIN(sal) 最低工资,SUM(sal)总工资
                FROM emp,dept
                            WHERE emp.deptno=dept.deptno
                                    GROUP BY dname
                                        ORDER BY AVG(sal) DESC

二、数据查询

2.1.基本查询

查询指定列：SELECT deptno,dname FROM dept; 

显示行号：SELECT rownum,ename FROM emp;

显示计算列：SELECT ename,sal,sal*(1+20/100) FROM emp;

使用别名：SELECT ename AS 员工姓名，sal AS 员工工资 FROM emp;

去重复：SELECT DISTINCT job FROM emp;

升序排序：SELECT ename,sal FROM emp ORDER BY sal;

多列排序：SELECT ename,deptno,hiredate FROM emp ORDER BY deptno,hiredate;

条件查询：SELECT ename,job,sal FROM emp WHERE ename='bruce';

复合条件查询：SELECT ename,job,sal FROM emp WHERE sal>1500 AND sal<2000;

使用特殊运算符：SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1500 AND 2000;
              SELECT ename,job,sal FROM emp WHERE job IN ('SALESMAN','CLERK','MANAGER');
              SELECT ename,job,sal FROM emp WHERE ename LIKE 'S%';
              SELECT ename,mgr FROM emp WHERE mgr IS NULL; //显示经理编号未填写的员工

2.2.函数

2.2.1.日期函数：

                          ADD_MONTHS（返回把月份数加到日期上的新日期）；

                          MONTH_BETWEEN：返回两个日期之间的月份数；

                          NEXT_DAY（指定日期的下一个日期）；

                          LAST_DAY （返回指定日期对应月份的最后一天）；

                          ROUND（按指定格式对日期进行四舍五入）；

                          TRUNC（按指定方式对日期进行截断）；

                         

2.2.2.字符型函数：

                          ASCII：获得字符的ASCII码［Ascii('A')==>65］；

                          CHR：返回与ASCII码相应的字符［Chr(65)==>A］；

                          LOWER：将字符串转换成小写［lower('SQL')==>sql］；

                          UPPER：将字符串转换成大写［upper('sql')==>SQL］；

                          INITCAP：将字符串转换成首字母大写［initcap('SQL course')==>Sql Course］；

                          CONCAT：拼接两个字符串［concat('SQL','course')==>SQLcourse］；

                          SUBSTR：截取子字符串［substr('String',1,3)==>Str］；

                          LENGTH：返回字符串的长度［length('yaoming')==>7］；

                          INSTR：给出起始位置和出现的次数,求子字符串在字符串中出现的位置［instr('String','r',1,1)==>3］；

                          LPAD：用字符填充字符串左侧到指定长度［lpad('HHH',5,'-')==>--HHH］；

                          RPAD：用字符填充字符串右侧到指定长度［rpad('YYY',6,'+')==>YYY+++］；

                          TRIM：在一个字符串中去除另一个字符串［trim('S' FROM 'SSMITH')==>MITH］；

                          REPLACE：替换字符串［replace('ABC','B','D')==>ADC］；

2.2.3.数值型函数：

                          ABS：求绝对值［abs(-5) ==>5］；

                          SQRT：求平方根［sqrt(2)==>1.41421356］；

                          POWER：求幂函数［power(2,3)==>8］；

                          COS：求余弦三角函数［cos(3.14159)==>-1］；

                          MOD：求除法余数［mod(160,30)==>10］；

                          CEIL：求大于等于某数的最小整数［ceil(2.35)==>3］；

                          FLOOR：求小于等于某数的最大整数［floor(2.35)==>2］；

                          ROUND：指定精度对十进制数四舍五入［round(45.926,1)==>45.9; round(45.926,0)==>46］；

                          TRUNC：指定精度截断十进制数［trunc(45.962,1)==>45.9; trunc(45.962)==>45］；

2.2.4.类型转换函数：

                          TO_CHAR：转换成字符串类型；

                          TO_DATE：转换成日期类型；

                          TO_NUMBER：转换成数值类型；

2.2.5.其他函数：                                 

                          NVL：空值转换函数［NVL（p1,p2）==>  if  p1  is  NULL  then  return  p2;  else  retrun  p1;  end if］；

2.3.高级查询

2.3.1.多表查询：

内连接：SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
       SELECT e.ename,e.deptno,d.dname FROM emp e JOIN dept d ON (e.deptno=d.deptno);
       SELECT e.ename,e.deptno,d.dname FROM emp e JOIN dept d USING(deptno); 

外连接：SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno(+);
       SELECT e.ename,e.deptno,d.dname FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

不等值连接：SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

自连接：SELECT worker.ename '员工', manager.ename '经理' FROM emp worker, emp manager WHERE worker.mgr=manager.empno;

三张表做外连接：SELECT * FROM test1 LEFT JOIN test2 ON test1.coo=test2.coo  LEFT JOIN test3 ON test1.coo=test3.coo;
              SELECT * FROM test1,test2,test3 WHERE test1.coo=test2.coo(+) AND test1.coo=test3.coo(+);

2.3.2.统计查询：

2.3.2.1.组函数：

a.概念：组函数又称统计函数。可对分组的数据进行求和、求平均等运算。只能用于select子句、group/having子句或order by子句。

b.常用的组函数：

                        AVG：求平均值；｜｜   SUM：求和；

                        MAX：求最大值；｜｜  MIN：求最小值；

                        COUNT：取得结果集的总行数，返回非空行；

------------------------------------

c.数据分组：使用group by子句可以对数据进行分组。

    where子句指定条件；group by用于指定分组；having限制分组结果显示；order  by用于排序数据。

单列分组：SELECT deptno,avg(sal) FROM emp GROUP BY deptno;

多列分组：SELECT deptno,job,avg(sal),max(sal) FROM emp GROUP BY deptno,job;

分组排序：SELECT deptno,sum(sal) FROM emp GROUP BY deptno ORDER BY sum(sal) DESC;

限制分组结果：SELECT deptno,avg(sal),max(sal) FROM emp GROUP BY deptno HAVING avg(sal)<2500;

*使用分组函数需要注意的：

1.只能出现在选择列表，ORDER BY子句和 HAVING子句中；

2.分组函数忽略NULL行；

3.可以指定ALL或DISTINCT指定是否统计重复的行；

4.如果同时包含，ORDER BY子句必须放在最后；

5.选择列表中出现的列表，表达式和分组函数都必须出现在GROUP BY子句中；

6.限制分组结果只能使用HAVING子句；

7.若有GROUP BY子句，SELECT后可跟GROUP BY后面跟的表达式及组函数，其他会报错。若无GROUP BY子句，

    SELECT后面有一个是组函数，则其他都必须是组函数。

*有HAVING子句的SELECT语句的执行过程：

1.行被分组；

2.将HAVING子句的条件应用到每个分组上；

3.只有符合HAVING条件的组被保留，再应用SELECT后的组函数对每组数据进行处理。

*WHERE子句与HAVING子句的区别：

1.WHERE子句过滤的是行记录，是分组以后的聚合结果；

2.HAVING子句只能包含GROUP BY后的表达式、组函数；

3.WHERE子句执行在前，HAVING执行在后；

4.WHERE子句和HAVING都不允许用列别名。

2.3.3.子查询

概念：

        子查询一般出现在查询语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，

结果作为主查询的条件，要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询

可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。子查询按照返回数据的类型可以分为单行子查询、多行子查询

和多列子查询。

2.3.3.1.单行子查询（只返回一行数据的子查询语句 ）：

SELECT ename,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='bruce');

2.3.3.2.多行子查询（返回多行数据的子查询）：

IN（处理匹配子查询任一个值的行）：

SELECT ename,job,sal,deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10);

ALL（必须匹配所有子查询结果，不能单独使用）：

SELECT ename,sal,deptno FROM emp WHERE sal>all (SELECT sal FROM emp WHERE deptno=30);

ANY（匹配子查询任一结果，不能单独使用，与IN类似）：             

SELECT ename,sal,deptno FROM emp WHERE sal>any (SELECT sal FROM emp WHERE deptno=30);

2.3.3.3.多列子查询：

SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='word');

2.3.3.4.在FROM子句中使用子查询：

SELECT ename,sal FROM (SELECT rownum AS num,ename,sal FROM emp WHERE rownum<=9)  WHERE num>=6;

2.3.3.5.在CREATE语句中使用子查询：

CREATE TABLE new(id,name,sal,job,deptno) AS SELECT empno,ename,sal,job,deptno FROM emp;

2.3.3.6.关联子查询：

SELECT ename,sal,deptno FROM emp outer WHERE sal>(SELECT avg(sal) FROM emp WHERE deptno=outer.deptno);

2.3.3.7.UPDATE中使用关联子查询：

ALTER TABLE emp ADD dept_name varchar2(20);
UPDATE emp e SET dept_name=(SELECT dname FROM dept d WHERE d.deptno=e.deptno);

2.3.3.8.在子查询中使用EXISTS操作符：

SELECT ename,sal,deptno FROM emp e WHERE EXISTS (SELECT 1 FROM dept WHERE deptno=e.deptno AND loc='His');

2.3.3.9.在子查询中使用NOT EXISTS操作符：

SELECT ename,sal,deptno FROM emp e where NOT EXISTS (SELECT 1 FROM dept WHERE deptno=e.deptno AND loc='His');

EXISTS操作符说明：

SELECT test1.id FROM test1 WHERE EXISTS （SELECT 1 FROM test2 WHERE test1.id=test2.id）；
                                                           // test1表中匹配test2表id的集合
SELECT test1.id FROM test1 WHERE test1.id IN (SELECT test2.id FROM test2)；
                                                           // 查找可以匹配的集合
                                                           // no exists,not in类似。效率上，数据量大时，EXISTS函数效率更好

2.3.4.集合运算：

概念：多个查询语句的结果可以做集合运算。

UNION：并集。合并两个操作的结果，去掉重复的部分；

UNION ALL：并集。合并两个操作的结果，保留重复的部分；

MINUS：差集。从前面的操作结果中去掉与后面操作结果相同的部分；

INTERSECT：交集。取两个操作结果中相同的部分；

SELECT job FROM emp WHERE deptno=10 UNION SELECT job FROM emp WHERE deptno=20;

三、数据操作

3.1.增加数据（行）：INSERT

3.1.1.增加单行数据：

不使用列列表，按列顺序插入：

INSERT INTO dept VALUES(20,'store','flour4')；

使用列列表，增加指定列：

INSERT INTO dept (deptno,dname,postion) VALUES(1001,'MARK','flour3')；

使用特定格式插入日期：

INSERT INTO emp(empno,ename,job,hiredate) VALUES(1356,'MARY','CLERK',to_date('1993-10-20','YYYY-MM-DD'))；

使用 default 提供数据：

INSERT INTO dept VALUES(60,'MARKET',DEFAULT)；

3.1.2.使用变量插入数据：

3.1.3.使用子查询插入数据：

INSERT INTO(SELECT empno,ename,sal,deptno FROM emp WHERE deptno>20) VALUES(2,'MARY',2000,30);

3.1.4.多表插入：

        创建表 test1，test2：

CREATE TABLE test1 AS SELECT Tname,T_date,sal FROM test WHERE 1=0；
CREATE TABLE test2 AS SELECT Tname,T_date,sal FROM test WHERE 1=0；

3.1.5.无条件 INSERT ALL：

3.1.6.有条件 INSERT ALL：

3.2.更新数据：UPDATE

        修改数据的语句UPDATE对表中指定字段的数据进行修改，一般需要通过添加WHERE条件来限定要进行修改的行，如果不

添加WHERE条件，将对所有的行进行修改。

3.2.1.表达式更新：

UPDATE emp SET sal=2600 WHERE ename='Gucci'；
UPDATE emp SET sal=sal*1.1,comm=sal*0.1 WHERE empno=201；
UPDATE emp SET date=to_date('1984/10/27','YYYY/MM/DD') WHERE empno=88；
UPDATE emp SET job=default  WHERE ename='scott；

3.2.2.使用子查询更新数据：

UPDATE emp SET (job,sal)=(SELECT job,sal FROM emp WHERE ename='SMITH') WHERE ename='SCOTT'；

3.2.3.复制表数据：

UPDATE emp SET deptno=(SELECT deptno FROM emp WHERE empno=78)  WHERE job=(SELECT job FROM emp WHERE empno=78)；

3.2.4.其他：

修改表：ALTER TABLE
增加列：ALTER TABLE emp ADD empno number(4);
修改列定义：ALTER TABLE emp MODIFY job varchar2(15) DEFAULT 'CLERK';
删除列：ALTER TABLE emp DROP COLUMN deptno;
修改列名：ALTER TABLE emp RENAME COLUMN empno TO eno;
修改表名：RENAME emp TO employee;
增加注释：COMMENT ON TABLE  employee IS '存放雇员信息';
         COMMENT ON COLUMN employee.name IS '描述雇员姓名'; 

3.3.删除数据：DELETE

3.3.1.删除满足条件的数据：

DELETE FROM emp WHERE ename='SMITH'；

3.3.2.删除表的所有数据：

DELETE FROM new；

3.3.3.截断表（TRUNCATE TABLE）：

TRUNCATE TABLE employee；

说明：DELETE可以回滚，但TRUNCATE TABLE不能回滚（不记录日志）。它不仅删除数据，而且释放表空间。

3.3.4.使用子查询删除数据：

CREATE TABLE copy AS SELECT * FROM emp；
DELETE FROM copy WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES')；

3.4.事务控制

        数据库事务处理可分为隐式和显式两种。显式事务操作通过命令实现，隐式事务由系统自动完成提交或撤销(回退)工作，

无需用户的干预。

3.4.1.提交事务（COMMIT）：

UPDATE emp SET sal=200 WHERE ename='SCOTT'；  COMMIT；

3.4.2.回退事务（ROLLBACK）：

3.4.3.设置保存点（SAVEPOINT  point）：

3.4.4.取消部分事务（ROLLBACK to point）：

3.4.5.取消全部事务（ROLLBACK）：

3.4.6.设置只读事务：SET TRANSACTION read only   (必须是第一条语句)；

3.4.7.设置顺序事务：SET TRANSACTION isolation level serializable  (必须是第一条语句)；

        

3.5.锁

3.5.1.概念：

        锁出现在数据共享的场合，用来保证数据的一致性。当多个会话同时修改一个表时，需要对数据进行相应的锁定。

锁有“只读锁”、“排它锁”，“共享排它锁”等多种类型，而且每种类型又有“行级锁”(一次锁住一条记录)，“页级锁”(一次锁住一页，

即数据库中存储记录的最小可分配单元)，“表级锁”(锁住整个表)。

        若为“行级排它锁”，则除被锁住的行外，该表中其他行均可被其他的用户进行修改(update)或删除(delete)。若为“表级排它锁”，

则所有其他用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行

提交(commit)或回滚(rollback)后，锁住的资源便会得到释放，从而允许其他用户进行操作。

        有时，由于程序的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，调出需要修改的数据后，未及时

修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现“死机”，而服务器端却并未检测到，从而造成锁定的资源

未被及时释放，影响到其他用户的操作。

        若两个事务，分别锁定一部分数据，而都在等待对方释放锁才能完成事务操作，这种情况下就会发生死锁。

3.5.2.隐式锁和显式锁：
        在oracle数据库中，修改数据操作时需要一个隐式的独占锁，以锁定修改的行，直到修改被提交或撤销为止。若一个会话锁定

了数据，那么第二个会话要想对数据进行修改，只能等到第一个会话对修改使用COMMIT命令进行提交或使用ROLLBACK命令进行

回滚撤销后，才开始执行。因此应养成一个良好的习惯：执行修改操作后，要尽早地提交或撤销，以免影响其他会话对数据的修改。

3.5.2.1.表的显式锁定操作语句：

SELECT FOR UPDATE（行锁）  ==>  锁定行，防止其他会话对行的修改。

SELECT empno,ename,job,sal FROM emp WHERE deptno=10 FOR UPDATE;

LOCK TABLE（表锁）  ==>  锁定表，防止其他会话对表的修改。

对表加独占锁：LOCK TABLE emp IN EXCLUSIVE MODE；//使用COMMIT或ROLLBACK命令可以释放锁
对表加共享锁：LOCK TABLE emp IN SHARE MODE；

3.5.2.2.隐式锁：

修订：

1.2014年11月20日，纠正部分错误。

参考：

注：