分类： Oracle
2012-04-03 22:04:57
目录：
1、 Select语句基础
(基本语法、单表查询、分组查询)
2、 Select语句高级应用
(多表查询、子查询、嵌套查询、集合操作、用SQL创建SQL)
3、 DML语句(Insert、Update、Delete)
4、 DDL语句(Create、Alter、Drop)
5、 DCL语句(Grant、Revoke)
章节内容:
1. Select语句基础(基本语法、单表查询、分组查询)
SELECT语句的功能：
1.1 select语句的基本语法和基本运算符：
SELECT column1,column2.、、（distinct，expression，alias，||，as）
/*指定提取的列
FROM table1，table2、、 /*指定表提取
WHERE 条件设置： /*设置条件范围
GROUP BY /*分组
HAVING /*设置分组条件
ORDER BY ASC/DESC /*设置排序
1.1.1 各种运算符及运算等级：
(算术运算符,连接运算符,关系运算符,其他运算符,逻辑表达式)
1、(),+,-,*,/ 数字和日期数据可以使用进行计算
2、|| （=concat） 连接运算符
3、=,>,>=,<,<= 关系运算符
4、IS [NOT] NULL,LIKE,[NOT] IN 其他运算符
5、[NOT] BETWEEN AND 其他运算符
6、 NOT 逻辑表达式
7、 AND 逻辑表达式
8、 OR 逻辑表达式
注释:
① NULL值定义
NULL值是未赋值的未知数
NULL值是与零或空串不同的
NULL值运算
算术表达式中含有NULL值，表达式的计算结果为NULL（NULL值传播）
查找NULL使用IS NULL字句
②使用别名表示表或列 （AS）
利用表别名可以进行自查询(多个别名)和方便程序编写,利用列别名可以简化列显示标题
③使用连接操作（||）
可以归并多列显示为一列
④重复行
默认的查询结果包括重复行
要去掉重复行需在SELECT语句中使用DISTINCT关键字
⑤字符型与日期型
字符型与日期型的值要用单引号标识
字符型值大小写敏感,日期型值格式敏感
⑥使用LIKE子句
使用LIKE子句执行一个含有通配符的模糊查找。
％ 任意长度的字符
_ 一个字符
1.2、书写规则：
⑴、SQL语名对大小写不敏感
⑵、SQL语句允许换行
⑶、关键字不能缩写或跨行
⑷、子句一般要换行书写
⑸、使用缩进格式书写可以提高可读性
1.3 常用函数
1.3.1 字符函数（11个）许多SQL解释器都提供了字符和字符串的处理功能。本部分覆盖了大部分字符串处理
函数，这一部分的例子使用字符函数示范表。
输入\输出：
SQL> select * from 字符函数示范;
姓 名 M CODE
---------- ---------- ---------- ----------
PURVIS KELLY A 32
TAYLOR CHUCK J 67
CHRISTINE LAURA C 65
ADAMS FESTER M 87
COSTALES ARMANDO A 77
KONG MAJOR G 52
1.3.1.1 CHR该函数返回与所给数值参数等当的字符，返回的字符取决于数据库所依赖的字符集。
例如，示例的数据库采用了ASCLL字符集。示例数据库的代码列的内容为数字。
输入：
SQL> SELECT CODE,CHR(CODE)FROM 字符函数示范;
CODE CHR (CODE)
---------- ---------
32
67 C
65 A
87 W
77 M
52 4
l 在数值32处显示为空白，因为32在ASCLL码表中是空格。
1.3.1.2 CONCAT我们在以前学到过一个与这个函数所执行的功能相当的操作，|| 符号表示将两个字符串连接起来，CONCAT也是完成这个功能的。使用方法如下：
输入：
SQL> SELECT CONCAT(姓,名)"姓 名" FROM 字符函数示范;
姓 名
--------------------
PURVISKELLY
TAYLORCHUCK
CHRISTINELAURA
ADAMSFESTER
COSTALESARMANDO
KONGMAJOR
l 当用多个词来做为别名时可以它们使用引号。
1.3.1.3 INITCAP该函数将参数的第一个字母变为大写，此外其它的字母则转换成小写。
输入：
SQL> SELECT 姓 前,INITCAP(姓) 后 FROM 字符函数示范;
前 后
---------- ----------
PURVIS Purvis
TAYLOR Taylor
CHRISTINE Christine
ADAMS Adams
COSTALES Costales
KONG Kong
1.3.1.4 LOWER和UPPERLOWER将参数转换为全部小写字母，而UPPER则把参数全部转换成大写字母。
下例是用LOWER函数和UPDATE函数来把数据库的内容转变为小写字母。
输入：
SQL> UPDATE 字符函数示范 SET 名='kelly' WHERE 名='KELLY';
输出：
1 row updated
输入：
SQL> SELECT 名 FROM 字符函数示范;
输出：
名
----------
kelly
CHUCK
LAURA
FESTER
ARMANDO
MAJOR
输入\输出：
SQL> select 名,upper(名),lower(名) from 字符函数示范;
名 UPPER(名) LOWER(名)
---------- ---------- ----------
kelly KELLY kelly
CHUCK CHUCK chuck
LAURA LAURA laura
FESTER FESTER fester
ARMANDO ARMANDO armando
MAJOR MAJOR major
现在你明白这两个函数的作用了吧！
1.3.1.5 LPAD与RPAD这两个函数最少需要两个参数最多需要三个参数，每一个参数是需要处理的字符串。
第二个参数是需要将字符串扩充的宽度，第三个参数表示加宽部分用什么字符来做填补。
第三个参数的默认值为空格，但也可以是单个的字符或字符串。
输入\输出：
SQL> SELECT 名,LPAD(名,20,'*') FROM 字符函数示范;
名 LPAD(名,20,'*')
--------------- -----------------------------------------
KELLY ***************KELLY
CHUCK ***************CHUCK
LAURA ***************LAURA
FESTER **************FESTER
ARMANDO *************ARMANDO
MAJOR ***************MAJOR
输入\输出：
SQL> SELECT 名,RPAD(名,20,'*') FROM 字符函数示范;
名 RPAD(名,20,'*')
--------------- -----------------------------------------
KELLY KELLY***************
CHUCK CHUCK***************
LAURA LAURA***************
FESTER FESTER**************
ARMANDO ARMANDO*************
MAJOR MAJOR***************
1.3.1.6 LTRIM与RTRIMLTRIM和RTRIM至少需要一个参数，最多允许两个参数。第一个参数与LPAD和RPAD
类似，是一个字符串；第二个参数也是一个字符或字符串，默认则是空格。如果第二个参
数不是空格的话，那么该函数将会像剪除空格那样剪除所指定的字符。如下例：
输入\输出：
SQL> SELECT 姓,RTRIM(姓) FROM 字符函数示范;
姓 RTRIM(姓)
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG你可以用下边的语句来确认字符中的空格已经被剪除了;
输出证明的确已经进行了剪除工作现在请再试一个LTRIM；
输入\输出：
SQL> SELECT 姓,LTRIM(姓,'C') FROM 字符函数示范;
姓 LTRIM(姓,'C')
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE HRISTINE
ADAMS ADAMS
COSTALES OSTALES
KONG KONG
l 注意第三行和第五行的C已经没有了。
1.3.1.7 REPLACE该函数需要三个参数，第一个参数是需要搜索的字符串，第二个参数是搜索的内容，第三个参数则是需要替换成的字符串。如果第三个参数省略或者是NULL，那么将只执行搜索操作而不会替换任何内容。
输入\输出：
SQL> SELECT 名,REPLACE(名,'ST','**') 替换后 FROM 字符函数示范;
名 替换后
--------------- ------------------------------
KELLY KELLY
CHUCK CHUCK
LAURA LAURA
FESTER FE**ER
ARMANDO ARMANDO
MAJOR MAJOR
1.3.1.8 SUBSTR这个函数有三个参数，允许你将目标字符串的一部份输出。第一个参数为目标字符串，
第二个字符串是将要输出的子串的起点，第三个参数是将要输出的子串的长度。
输入\输出：
SQL> SELECT 名,SUBSTR(名,2,3) FROM 字符函数示范;
名 SUBSTR(名,2,3)
--------------- --------------
KELLY ELL
CHUCK HUC
LAURA AUR
FESTER EST
ARMANDO RMA
MAJOR AJO
l 如果第二个参数为负数，那么将会从源串的尾部开始向前定位至负数的绝对值的位置。
1.3.1.9 TRANSLATE这一函数有三个参数，目标字符串、源字符串和目的字符串。在目标字符串与源字符
串中均出现的字符，将会被替换成对应的目的字符串的字符。
输入\输出：
SQL>SELECT 名,TRANSLATE(名,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','NNNNNNNNNN
AAAAAAAAAAAAAAAAAAAAAAAAA') FROM 字符函数示范;
名 TRANSLATE(名,'0123456789ABCDEF
--------------- ------------------------------
kelly kelly
CHUCK AAAAA
LAURA AAAAA
FESTER AAAAAA
ARMANDO AAAAAAA
MAJOR AAAAA
6 rows selected
l 这个函数对大小写是敏感的。
1.3.1.10 INSTR如果需要知道在一个字符串中满足特定的内容的位置可以使用INSTR，它的第一个参数是目标字符串，第二个参数是匹配的内容，第三和第四个参数是数字，用以指定开始搜索的起点以及指出第几个满足条件的将会被返回。下例将从字符串的第二个字符开始搜索，并返回第一个以O开头的字符的位置。
输入\输出：
SQL> SELECT 姓,INSTR(姓,'O',2,1) FROM 字符函数示范;
姓 INSTR(姓,'O',2,1)
--------------- -----------------
PURVIS 0
TAYLOR 5
CHRISTINE 0
ADAMS 0
COSTALES 2
KONG 2
默认第三个与第四个参数的数值均为1，如果第三个数值为负数，那么将会从后向前搜索。
1.3.1.11 LENGTHLENGTH将返回指定字符串的长度。例如：
输入\输出：
SQL> SELECT 名,LENGTH(RTRIM(名)) FROM 字符函数示范;
名 LENGTH(RTRIM(名))
--------------- -----------------
kelly 5
CHUCK 5
LAURA 5
FESTER 6
ARMANDO 7
MAJOR 5
l 这里使用了函数RTRIM，否则LENGTH将全部返回15。
1.3.2 数字函数（6个）大多数情况下，你所检索到的数据在使用时，需要用到数学函数，大多数SQL的解释器都
提供了与这里相类似的一些数学函数。这里的例子使用的表名字叫数学函数示范内容如下：
输入\输出：
SQL> SELECT * FROM 数学函数示范;
A B
---------- ----------
3.1415 4
-45 0.707
5 9
-57.667 42
15 55
-7.2 5.3
1.3.2.1 ABSABS函数返回给定数字的绝对值。例如：
输入\输出：
SQL> SELECT ABS(A) FROM 数学函数示范;
ABS(A)
----------
3.1415
45
5
57.667
15
7.2
1.3.2.2 CEIL和FLOORCEIL返回与给定参数相等或比给定参数在的最小整数。FLOOR则正好相反，它返回
与给定参数相等或比给定参数小的最大整数。例如：
输入\输出：
SQL> SELECT CEIL(B) FROM 数学函数示范;
CEIL(B)
----------
4
1
9
42
55
输入\输出：
SQL> SELECT FLOOR(A) FROM 数学函数示范;
FLOOR(A)
----------
3
-45
5
-58
15
-8
1.3.2.3 MODMOD返回数A与数B相除后的余数。例如：
输入\输出：
SQL> SELECT A,B,MOD(A,B) FROM 数学函数示范;
A B MOD(A,B)
---------- ---------- ----------
3.1415 4 3.1415
-45 0.707 -0.459
5 9 5
-57.667 42 -15.667
15 55 15
-7.2 5.3 -1.9
1.3.2.4 SIGN如果参数的值为负数，那么SIGN返回-1。如果参数的值为正数，那么SIGN返回1。
如果参数为零，那么SIGN也返回零。请看下例：
输入\输出：
SQL> SELECT A,SIGN(A) FROM 数学函数示范;
A SIGN(A)
---------- ----------
3.1415 1
-45 -1
5 1
-57.667 -1
15 1
-7.2 -1
1.3.2.5 ROUNDROUND的作用是四舍五入至指定小数位数。例如：
输入\输出：
SQL> SELECT A,ROUND(A) FROM 数学函数示范;
A ROUND(A)
---------- ----------
3.1415 3
-45 -45
5 5
-57.667 -58
15 15
-7.2 -7
1.3.2.6 TRUNCTRUNC的作用是截断指定小数位数。例如：
输入\输出：
SQL> SELECT A,TRUNC(A,1) FROM 数学函数示范;
A TRUNC(A,1)
---------- ----------
3.1415 3.1
-45 -45
5 5
-57.667 -57.6
15 15
-7.2 -7.2
1.3.3日期函数（7个）在学习日期函数之前，我们先要了解一下基本内容：
ORACLE数据库对日期数据在内部存贮格式:世纪,年,月,日,时,分,秒
默认日期显示格式为：DD-MON-RR.
允许你以两位数字表示年份。
SYSDATE函数返回当前系统日期时间。
格式串：
| YYYY | 年份用四位数字表示 |
| MM | 月份用两位数字表示 |
| DD | 日期用两位数字表示 |
| HH24:MI:SS AM | 用24小时制表示时分秒，AM表示要输出上下午标志 |
1.3.3.1 SYSDATE
SYSDATE函数返回当前系统日期时间。例如：
输入\输出：
SQL> select sysdate from dual;
SYSDATE
-----------
2005-08-02
1.3.3.2 MONTHS_BETWEEN如果你想知道在给定的两个日期中有多少个月可以像这样来使用
MONTHS_BETWEEN。例如：
输入\输出：
SQL> select months_between(to_date('2005-8-2','yyyy-mm-dd'),
to_date('2004-8-2','yyyy-mm-dd')) 月数 from dual;
月数
----------
12
1.3.3.3 ADD_MONTHS该函数的功能是将给定的日期增加一个月。举例：
输入\输出：
SQL> select to_date('2005-8-2','yyyy-mm-dd') 原计划,add_months
(to_date('2005-8-2','yyyy-mm-dd'),2) 修改后计划 from dual;
原计划 修改后计划
----------- -----------
2005-08-02 2005-10-02
1.3.3.4 NEXT_DAYNEXT_DAY将返回与指定日期在同一个星期或之后一个星期内的，你所要求的星期天
数的确切日期。如果你想知道你所指定的日期的星期五是几号可以这样做：
输入\输出：
SQL> select SYSDATE 原日期,next_day(SYSDATE,'星期五') from dual;
原日期 NEXT_DAY(SYSDATE,'星期五')
----------- --------------------------
2005-08-02 2005-08-05 18:25:52
l 本例中的星期五，同样可以用数字5来代替。
1.3.3.5 LAST_DAYLAST_DAY可以返回指定月份的最后一天。例如，如果你想知道在本月的最后一天是几号时你可以输入：
输入\输出：
SQL> select last_day(SYSDATE) 月末 from dual;
月末
-----------
2005-08-31
1.3.3.6 ROUNDROUND可以四舍五入日期。
输入\输出：
select sysdate 当前时间,round(sysdate,'year') 格式化后时间 from dual;
当前时间 格式化后时间
----------- ------------
2005-08-03 2006-01-01
1.3.3.7 TRUNCTRUNC可以截断制定日期。
输入\输出：
select sysdate 当前时间,trunc(sysdate,'year') 格式化后时间 from dual;
当前时间 格式化后时间
----------- ------------
2005-08-03 2005-01-01
l 请比较ROUND和TRUNC的输出结果的差异。
1.3.4 转换函数（3个） 1.3.4.1 TO_NUMBER该函数的作用是将一个字符串转换成数值。TO_NUMBER(X,Y[,Z])，例如：
输入\输出：
SQL> select to_number('2005') year from dual;
YEAR
---------
2005
1.3.4.2 TO_CHAR该函数作用是将一个日期或数字转换成一个字符串。TO_CHAR(date,’format_model’)。例如：
输入\输出：
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2005/08/03 09:14:41
1.3.4.2 TO_DATE该函数作用是将字符串转化为ORACLE中的一个日期。TO_DATE(string,'format')。例如：
输入\输出：
SQL> select to_date('2005-08-03 09:21:00','yyyy-mm-dd hh24:mi:ss') from dual;
TO_DATE('2005-08-0309:21:00','
------------------------------
2005-08-03 9:21:00
1.3.5 聚合函数（5个） 1.3.5.1 COUNT该函数将返回满足WHERE条件子句中记录的个数。例如：
SQL> select count(*) from dual;
COUNT(*)
----------
1
1.3.5.2 SUM该函数将返回某一列的所有数值的和。例如：
SQL> select sum(病人id) from 病人信息;
SUM(病人ID)
-----------
25
1.3.5.3 AVGAVG可以返回某一列的平均值。例如：
SQL> select avg(实收金额) from 病人费用记录 where 门诊标志=1;
AVG(实收金额)
-------------
98.67
1.3.5.4 MAXMAX(DISTINCT|ALL)
求最大值，ALL表示对所有的值求最大值，DISTINCT表示对不同的值求最大值，相同的只取一次。例如：
SQL> select max(年龄) from 病人信息;
MAX(年龄)
----------
79
1.3.5.5 MINMIN(DISTINCT|ALL)
求最小值，ALL表示对所有的值求最小值，DISTINCT表示对不同的值求最小值，相同的只取一次。例如：
SQL> select min(年龄) from 病人信息;
MIN(年龄)
----------
4
1.3.6 其他函数（3个） 1.3.6.1 USER该函数返回当前使用数据库的用户的名字。例如：
SQL> select user from dual;
USER
-------------------
ZLHIS
1.3.6.2 NVL该函数转换NVL值， 数据类型必须匹配：例如：
NVL(实收金额,0)
NVL(填制日期,‘2005-08-03')
NVL(开单人,‘无名氏')
SQL> select distinct(nvl(开单人,'无名氏')) 开单人姓名 from 病人费用记录 where 门诊标志=1 and 开单人 is null;
开单人姓名
----------
无名氏
1.3.6.3 DECODEDECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数
解释:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
例如：
decode(收费细目,'西药费','药费','成药费','药费','草药费','药费',收费细目)
1.4 常用的分组函数：
1.4.1 GROUP BY 子句的使用
SQL无法把正常的列和汇总函数结合在一起，这时就需要GROUP BY子句。它可以对SELECT的结果进行分组后在应用汇总函数。例如：
SQL> select 姓名,sum(实收金额) from 病人费用记录 where rownum<7 group by 姓名;
姓名 SUM(实收金额)
-------------------- -------------
秉仁 3.11
杜子滕 3.5
李卫国 70
毛鸭鸭 3.5
王复玉 2
赵卫生 1
6 rows selected
1.4.2 HAVING 子句的使用
HAVING子句允许你将汇总函数作为条件，使用在查询语句中。HAVING子句为一组记录设置查询的条件，通常having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组例如：
select 姓名,sum(实收金额) from 病人费用记录 where rownum<20 group by 姓名 having sum(实收金额)>2;
姓名 SUM(实收金额)
-------------------- -------------
秉仁 3.11
杜子滕 3.5
李卫国 70
毛鸭鸭 3.5
4 rows selected
2、Select语句高级应用
(多表查询、子查询、嵌套查询、集合操作、用SQL创建SQL)
2.1多表查询
2.1.1、多表之间的连接的应用
多表查询允许你在查询的FROM条件后面跟多个表，然后把表之间的关系在WHERE条件后进行连接，如果没有进行连接，所查询的记录将是所有表记录的笛卡儿乘积记录数。
例如：select A.* from 收费项目别名 A收费项目目录 B where A.收费细id=B.ID
2.1.2、外连接
上面的多边查询我们是定的等值连接，而在实际应用过程中，我们可能需要列出一个表的全部记录，如我们做报表的时候需要把所有的部门都显示出来，于是我们引入外连接技术，通过在等式连接的一边加上（+）来表示外连接，（+）放在取全部记录的表的另一边。
例子：
select A.名称,SUM(B.结帐金额) as 结帐金额 from 部门表 A,病人费用记录 B where A.ID=B.病人科室ID(+) AND 登记时间>sysdate-2 group by a.名称
2.2子查询可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询
.where中可以包含一个select语句的子查询
.where中可以包含in,exists语句
.最多可以嵌套16层
.层次过多会影响性能
例如：select * from 收费项目别名 where 收费细id in (select id from 收费项目目录)
2.3嵌套查询select查询语句里可以嵌入select查询语句，称为嵌套查询。嵌套查询实际上也是子查询。
例子：
select rownum as 序号,药品名称,实际数量 from (select A.名称 as 药品名称,SUM(nvl(b.实际数量,0)) AS 实际数量 from 收费项目目录 A,药品库存 B WHERE A.ID=B.药品ID
group by a.名称 order by 实际数量)
2.4集合操作集合查询有UNION , INTERSECT及 MINUS。
UNION 表示求两个查询的合集，会去掉重复的记录，如果重复的记录也需要这用UNION ALL连接符，注意连接的两个查询必须要有相同类型查询字段。
INTERSECT表示返回查询结果中相同的部。
MINUS表示返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。。
例子：
SELECT 库房ID,药品ID,可用数量 FROM 药品库存 where 库房ID=52 AND 可用数量<>0 and 药品ID=1
UNION
SELECT 库房ID,药品ID,可用数量 FROM 药品库存 where 库房ID=53 AND 可用数量<>0 and 药品ID=1;
查询结果为：
库房ID 药品ID 可用数量
---------- ---------- ----------
52 1 -24
53 1 -3094
把UNION 换成INTERSECT后的结果为没有记录。
把UNION 换成MINUS后的结果为第一个查询的记录：
库房ID 药品ID 可用数量
---------- ---------- ----------
52 1 -24
2.5、用SQL创建SQL
从SQL中生成SQL语句的意思简而言之就是写一个SQL语句。它可以其它形式的SQL语句或命令。
为什么我们需要从查询中生成SQL语句呢？最初这样做的目的是为了简单和有效。你并不一定需要生成SQL语句，但是如果你不这样做，你可能会忽视SQL的最为强大的特性。有许多人甚至根本就不知道有这样的功能存在。
几个SQL*PLUS命令
在今天的例子中我们要使用几个新的命令：
SET ECHO ON/OFF
当你set echo on时，你会在执行的时候看到你的SQL语句。set echo off的意思就是你
不想在执行的时候看到SQL语句——你只想看到输出的结果。
SET ECHO [ ON | OFF ]
SET FEEDBACK ON/OFF
FEEDBACK就是你的查询所输入的行数。例如如果你运行的SELECT语句返回30行数据，那么你的FEEDBACK将会是：
30 rows selected
SET FEEDBACK ON会显示对行的计数，SET FEEDBACK OFF则在你的结果输出时，不会对行进行计数。
SET FEEDBACK [ ON | OFF ]
SPOOL FILENAME/OFF
SPOOL可以将你的查询结果直接地输入到一个文件中。要想打开SPOOL文件你需要输入
spool filename
如果想关掉SPOOL文件你应该输入
spool off
START FILENAME
大多数的我们所学习的SQL命令都是在SQL>下运行的。另外的一种运行SQL语句的方法是创建SQL执行文件。在SQL*PLUS中，运行SQL文件的命令是START FILENAME。
START FILENAME
例如：我想生成一个统计所有表中，每个表的记录行数的语句，生成到一个统计.sql的文件中。可以如下操作：
输入\输出：
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL 统计.SQL
SQL> select 'select count(*) from '||TNAME||' ;' from tab where rownum<10;
'SELECTCOUNT(*)FROM'||TNAME||'
-----------------------------------------------------
select count(*) from H病历打印记录 ;
select count(*) from H病人病历标记图 ;
select count(*) from H病人病历标记图_OLD ;
select count(*) from H病人病历记录 ;
select count(*) from H病人病历记录_OLD ;
select count(*) from H病人病历内容 ;
select count(*) from H病人病历内容_OLD ;
select count(*) from H病人病历所见单 ;
select count(*) from H病人病历所见单_OLD ;
9 rows selected
l 因为示例原因，限制行数为10行以内。
再例如，现在需要往B表中按照ID对应插入A表的时间
| 表A |
| 表B | |||
| ID | 时间 |
| ID | 姓名 | 时间 |
| 1 | 2005-8-3 |
| 5 | AA |
|
| 2 | 2005-8-4 |
| 2 | BB |
|
| 3 | 2005-8-5 |
| 4 | CC |
|
| 4 | 2005-8-6 |
| 1 | DD |
|
| 5 | 2005-8-7 |
| 3 | EE |
|
输入\输出：
SQL> select 'update B set b.时间=TO_DATE('||CHR(39)||TO_CHAR(A.时间,'YYYY-MM-DD')||CHR(39)||','||CHR(39)||'YYYY-MM-DD'||CHR(39)||') where b.id='||b.id||';' from a,b where a.id=b.id;
'UPDATEBSETB.时间=TO_DATE('||C
--------------------------------------------------------------------------------
update B set b.时间=TO_DATE('2005-08-03','YYYY-MM-DD') where b.id=1;
update B set b.时间=TO_DATE('2005-08-04','YYYY-MM-DD') where b.id=2;
update B set b.时间=TO_DATE('2005-08-05','YYYY-MM-DD') where b.id=3;
update B set b.时间=TO_DATE('2005-08-06','YYYY-MM-DD') where b.id=4;
update B set b.时间=TO_DATE('2005-08-07','YYYY-MM-DD') where b.id=5;
2.6 两种优化表查询的方法:
1、表的查询顺序(针对多表查询)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
例如： 表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒
如果有3个以上的表连接查询， 那就需要选择交叉表(intersection table)作为基础表， 交叉表是指那个被其他表所引用的表。
例如： EMP表描述了LOCATION表和CATEGORY表的交集。
SELECT *
FROM LOCATION L,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
将比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
2、不是用IN
用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。
低效：
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
高效：
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例如：
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=’A’);
为了提高效率，改写为：
(方法一： 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’
(方法二： 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
3. DML语句
数据操纵语言（DML）语句，用来在数据库中操纵各种对象，检索和修改数据，这些语句包括INSERT、UPDATE、DELETE等。
3.1 Insert语句
从字面意思久可以看出来insert是往数据表里插入记录的语句。
语法：
INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT 字段名1, 字段名2, …… FROM 另外的表名;
注意：
1、字符串类型的字段值必须用单引号括起来, 例如: ‘GOOD DAY’
2、如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.
3、字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.
4、INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,方法借用ORACLE里自带的DBMS_LOB程序包。
5、INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记)
INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL
例如：
1、 插入人员表一条记录
insert into 人员表(编号,姓名,性别,年龄) values ('001','张三','男','是');
2、 往病案的部门表中插入标准版中的部门表信息：
insert into 部门表(id,上级id,编号,名称,简码,位置,建档时间,撤档时间)
(select id,上级id,编码 编号,名称,简码,位置,建档时间,撤档时间 from zlhis.部门表)
3.2 Update语句UPDATE 的作用是修改数据表里记录的语句。
语法：
UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
注意：
1、 如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;
2、 值N超过定义的长度会出错, 最好在插入前进行长度校验。
3、 以上SQL语句对表都加上了行级锁,
确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效, 否则改变不一定写入数据库。如果想撤回这些操作, 可以用命令 ROLLBACK 复原.
4、 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段。
5、 程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成,其间加上COMMIT 确认事物处理。
例如：
Update 人员表 set 年龄＝28 where 姓名=’张三’；
3.3 Delete语句DELETE的作用是删除数据表里记录的语句。
语法：
DELETE FROM表名 WHERE 条件;
注意：
1、删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused。
2、如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;
此操作不可回退。
例如：delete from 人员表 where 姓名=’张三’；
4.DDL语句
数据定义语言（DDL）语句，用来执行数据库的任务，创建数据库以及数据库中的各种对象，这些语句包括CREATE、ALTER、DROP等语句。
4.1 Create语句
CREATE (创建表， 索引， 视图， 同义词， 过程， 函数， 数据库链接等)
约定说明：
ORACLE常用的字段类型有
CHAR 固定长度的字符串
VARCHAR2 可变长度的字符串
NUMBER(M，N) 数字型M是位数总长度， N是小数的长度
DATE 日期类型
1、创建表时要把较小的不为空的字段放在前面， 可能为空的字段放在后面
2、创建表时可以用中文的字段名， 但最好还是用英文的字段名
3、创建表时可以给字段加上默认值， 例如 DEFAULT SYSDATE，这样每次插入和修改时， 不用程序操作这个字段都能得到动作的时间
4、创建表时可以给字段加上约束条件；例如 不允许重复 UNIQUE， 关键字 PRIMARY KEY
语法：create table table_name(column1 datatype [not null] [not null primary key], column2 datatype [not null],..)
datatype - 资料的格式
not null - 不可以允许资料有空的
primary key - 是本表的主键
例如：create table人员表 (姓名 varchar2(10),年龄 integer);
语法：create [unique] index index_name on table_name (column_name)；
例如：CREATE INDEX 人员表_IX_姓名 on 人员表(姓名) ；
4.1.3 视图语法：Create Or Replace View view_name As （column）
Create Or Replace View 病人信息 As （select 姓名，年龄 from 人员表）
4.1.4用户语法：CREATE USER user_name INDENTIFIED BY password；
例如：create user zlhis identified by his；
4.1 Alter语句 4.1.1 表
语法：改变表的名称
ALTER TABLE 表名1 TO 表名2;
在表的后面增加一个字段.(没有删除一个字段的用法)
ALTER TABLE表名 ADD 字段名 字段名描述;
修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;
把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;
例如：alter table人员表 add 地址 varchar（100）；--在实际应用中不能有column关键字
4.1.2 索引语法：alter index index_name [storage-clause][initrans 整数][maxtrans 整数]；
alter index index_name rebuild；
例如：ALTER INDEX 人员表_IX_姓名 STORAGE (NEXT 512K MAXEXTENTS UNLIMITED) ；
ALTER INDEX人员表_IX_姓名 REBUILD;
4.1.3 约束语法：增加约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
失效约束
ALTER TABLE 表名 DISABLE 约束名 [CASCADE]
启用约束
ALTER TABLE 表名 ENABLE CONSTARAINT约束名
修改约束：
ALTER TABLE 表名 MODIFY CONSTARAINT ENABLE 约束名 [CASCADE]
例如：alter table 人员表 add constraint 人员表_uq_姓名 unique（姓名）；
4.1.4 用户语法：ALTER USER user_name INDENTIFIED BY password；
例如：alter user zlhis identified by his；
4.2 Drop语句
删除表 drop table 表名
删除索引 drop index 索引名
删除用户 drop user 用户名
例如：drop table 人员表;
drop index 人员表; --删除时需要指明table 或 index 关键字
Drop user zlhis；
5. DCL语句
数据控制语言（DCL）语句，用来进行安全性管理，可以确定哪些用户可以查看或者修改数据，这些语句包括GRANT、DENY、REVOKE等语句。
5.1 Grant语句GRANT的作用是赋于权限：
常用的系统权限集合有以下三个:
CONNECT(基本的连接)， RESOURCE(程序开发)， DBA(数据库管理)；
常用的数据对象权限有以下六个:
ALL ON 数据对象名， SELECT ON 数据对象名， UPDATE ON 数据对象名，
DELETE ON 数据对象名， INSERT ON 数据对象名， ALTER ON 数据对象名
例如：
GRANT CONNECT， RESOURCE TO 用户名；
GRANT SELECT ON 表名 TO 用户名；
GRANT SELECT， INSERT， DELETE ON表名 TO 用户名1， 用户名2；
GRNAT DBA TO ZLHIS;
5.2 Revoke语句REVOKE的作用是回收权限：
例如：
REVOKE CONNECT， RESOURCE FROM 用户名；
REVOKE SELECT ON 表名 FROM 用户名；
REVOKE SELECT， INSERT， DELETE ON表名 FROM 用户名1， 用户名2；
REVOKE DBA FROM ZLHIS；
