ORACLE学习笔记系列（7）SQL语言分类

 SQL语言的五大组成部分DQL DML DDL DCL TC

 

SQL语言共分为五大类：

数据查询语言（DATA QUERY LANGUAGE, DQL）,

数据操纵语言（DATA MANIPULATION LANGUAGE, DML）

数据定义语言（DATA DEFINITION LANGUAGE, DDL）

数据控制语言（DATA CONTROL LANGUAGE, DCL）

事务控制（TRANSACTION CONTROL, TC）

一、数据查询语言DQL

查询语句的一般结构：

单表查询

多表查询

嵌套查询

分组查询

集合查询

 

查询语句的一般结构

SELECT [ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]···

FROM <表名或视图名>[，<表名或视图名>]···

[WHERE <条件表达式>]

[GROUP BY <列名1>[HAVING<条件表达式>]]

[ORDER BY <列名2>[ASC|DESC]]；

 

SQL语句的执行顺序  

SELECT DNAME,MAX(SAL)-- 5 对列筛选（分组字段或聚集函数）  

  FROM EMP

  LEFTOUTERJOIN DEPT -- 1 确定表  

 USING(DEPTNO)

 WHERE DEPTNO >0-- 2 确定行（记录）  

 GROUPBY DNAME -- 3 将行分组    

HAVINGMAX(COMM)ISNULLORMAX(COMM)>0-- 4 对组筛选    

 ORDERBY DNAME -- 6 对结果集排序   

单表查询:

1、无条件：

SELECT中的<目标表达式>可以是表中的列，也可以是表达式，包括算术表达式、字符串常数、函数等。（字符串用单引号定界）

 

1. 使用算术表达式（+、-、*、/）

SQL>SELECT EMPNO, SAL *0.8FROM EMP;

2. 使用字符常量

SQL>SELECT EMPNO,'NAME IS :', ENAME FROM EMP;

3.使用函数

SQL>SELECT EMPNO,UPPER(ENAME)FROM EMP;

4. 改变列标题

SQL>SELECT EMPNO EMPLOYEENAME,SAL AS SALARY, JOB, DEPT FROM EMP;

5.使用连接字符串

SQL>SELECT ENAME ||':'||'1'||'month salary='|| SAL AS MONTHY FROM EMP

6.消除重复行

SQL>select [ALL]  ename from emp;

SQL>selectDISTINCT ename from emp;

2、有条件：

WHERE常用的查询条件

查询条件	谓词
比较大小	=,  >,  <,  >=,  <=,  < >
确定范围	BETWEEN AND，NOT BETWEEN AND
确定集合	IN，NOT IN
字符匹配	LIKE，NOT LIKE
空值	IS NULL，IS NOT NULL
多重条件	AND，OR

 

 

确定范围：

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO BETWEEN10AND20;

确定集合：

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO IN(10,30);

等价于：

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO =10OR DEPTNO =30;

字符匹配：[NOT] LIKE ‘<匹配串>’

1.<匹配串>可以是一个完整的字符串，也可以含有通配符的字符串。通配符包括‘%’、‘_’。

2.%（百分号）代表任意长（长度为0）字符串。

3._（下划线）代表任意单个字符。

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE ENAME LIKE'S%';

SQL>SELECT ENAME FROM EMP WHERE ENAME LIKE'_S%';

空值：涉及空值查询时使用IS NULL或 IS NOT NULL，这里的IS不能用=替代

多重条件:用逻辑运算符NOT、AND和OR来联结多个查询条件。

优先级：NOT、AND、OR（用户可以用括号改变优先级）。

IN谓词实际上是多个OR运算的缩写。

 

SELECT的交互查询：

使用替代变量，以“&”开头。

SQL>SELECT*FROM EMP WHERE ENAME ='&NAME';

 

3、排序： ASC升序排序，DESC降序排序

ORDERBY从句要放在 SELECT语句的最后。

表达式排序：

SQL>SELECT EMPNO, ENAME, SAL，SAL *12FROM EMP ORDERBY SAL *12DESC;

多列排序:

SQL>SELECT EMPNO, DEPTNO, SAL FROM EMP ORDERBY DEPTNO ASC, SAL DESC;

显示为按部门号升序，相同部门号内的工资降序排列。

别名排序：

SQL>SELECT EMPNO, ENAME,SAL *12 ANNSAL FROM EMP ORDERBY ANNSAL DESC;

多表查询

1.  等值查询:

在存在主外键关系的表中，使用等号建立表之间的连接

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A, DEPT B

    WHERE A.DEPTNO = B.DEPTNO;

 

   SQL99新标准:内连接(innerjoin)

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A

    INNERJOIN DEPT B -- inner可省略

       ON A.DEPTNO = B.DEPTNO;

2.  非等值查询:

使用非等号关系运算符进行多表的连接

   SELECT A.ENAME, A.SAL, B.GRADE

     FROM EMP A, SALGRADE B

    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;

3.  外连接(outerjoin):

类似等值查询，使用特定的符号(+)

   (+)符号如果出现在where子句等号的左边，叫左外连接，左外连接会显示右表的全部数据；

   (+)符号如果出现在where子句等号的右边，叫右外连接，右外连接会显示左表的全部数据；

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A, DEPT B

    WHERE A.DEPTNO(+)= B.DEPTNO;

 

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A, DEPT B

    WHERE A.DEPTNO = B.DEPTNO(+);

 

   SQL99新标准：

   左外连接：

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A

     LEFTOUTERJOIN DEPT B -- outer可省略

       ON A.DEPTNO = B.DEPTNO;

   右外连接：

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A

    RIGHTOUTERJOIN DEPT B -- outer可省略

       ON A.DEPTNO = B.DEPTNO;

   全外连接:外连接是在等值(内)连接的基础上将左表和右表的未匹配数据都加上

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

     FROM EMP A

     FULLOUTERJOIN DEPT B -- outer可省略

       ON A.DEPTNO = B.DEPTNO;

4.  自连接:

特殊的多表连接，连接的多个表来自于同一个表

   SELECT A.EMPNO, A.ENAME, B.ENAME MANAGER

     FROM EMP A, EMP B

    WHERE A.MGR = B.EMPNO;

5.  自然连接(naturaljoin):

 

基于多个表有一个或多个相同字段名，且这些字段的类型相同

   由oracle自行决定哪些列作为连接的条件:将不同表中的那些具有相同名称和数据类型的字段用等值连接起来。

   SELECT A.ENAME, B.DNAME FROM EMP A NATURALJOIN DEPT B;

6.  交叉查询(crossjoin):笛卡尔集

   SELECT A.ENAME, A.SAL, B.GRADE FROM EMP A, SALGRADE B;

7.  join...using:

   使用 USING可以在natural join时有多个列满足条件，指定具体的字段做等值连接

   如果列名相同但是数据类型不同，这时不能使用natural join可以使用 using来连接。

   当多列匹配时，using只能匹配一列。

   在引用的列前面不能有表名或者表别名。

   natural join和using 是相互排斥的

   SELECT A.ENAME, B.DNAME FROM MYEMP A JOIN DEPT B USING(DEPTNO);

嵌套查询

概念：

  查询块：一个select-from-where语句称为一个查询块。

  嵌套查询：将一个查询块嵌套在另一个查询块中的查询，称为嵌套查询，也称为子查询。

处理步骤：

  一般由里向外进行处理。

注意：

子查询可以有多层，所存取的表可以是父查询没有存取的表；

子查询作为查询列时显示子查询选出的记录，其他情况子查询选出的记录不显示；

一个子查询必须放在圆括号中；

将子查询放在比较条件的右边以增加可读性；

子查询不包含 ORDER BY 子句，对一个 SELECT 语句只能用一个 ORDER BY 子句。

子查询位置：

可放在SELECT后面，子查询选出的记录作为列传显示；

可放在FROM后面，子查询选出的记录作为结果集的一部分；

可放在WHERE后面，子查询选出的记录作为条件表达式的一部分；

可放在HAVING后面，子查询选出的记录作为分组结果的过滤条件；

可放在ORDER BY后面，子查询选出的记录作为结果集的排序内容；

 

SQL>SELECT(SELECT DNAME FROM DEPT WHERE DEPT.DEPTNO='10') DNAME ,EMP.*FROM EMP;

SQL>SELECT*FROM(SELECT*FROM DEPT WHERE DEPT.DEPTNO='10') DEPT, EMP WHERE EMP.DEPTNO = DEPT.DEPTNO;

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =  (SELECT DEPTNO FROM DEPT WHERE DEPT.DNAME ='SALES');

SQL>SELECT DEPTNO,AVG(SAL)FROM EMP GROUPBY DEPTNO HAVINGAVG(SAL)>(SELECTMIN(SAL)FROM EMP );

SQL>SELECT*FROM EMP ORDERBY(SELECT DEPTNO FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);

 

子查询的类型：

 单行子查询：从内 SELECT语句只返回一行的查询，可采用单行比较符：=、<、>、<=、>=等；

 多行子查询：从内 SELECT语句可返回多行的查询，需采用多行比较符：IN、>ANY、>ALL、<ANY、< ALL等。

 相关子查询（Correlated Sub-QUERY）：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

非相关子查询（Uncorrelated Sub-QUERY）：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

 

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =(SELECT DEPTNO FROM DEPT WHERE DEPT.DNAME ='SALES');

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT );

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT );

 

子查询返回结果对主查询的影响：

如果子查询返回的是零值，不会对主程序造成影响；

如果子查询返回的是空值，会影响主程序的返回值；

如果单行子查询返回的是多行，则会查询错误“单行子查询出现多行”；

 

SQL>SELECT*FROM EMP WHERE EMP.COMM IN(SELECT COMM FROM EMP);--有结果数据

SQL>SELECT*FROM EMP WHERE EMP.COMM IN(SELECTNULLFROM EMP);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(10,0);--有结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(NULL);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(10,NULL);--有结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO NOTIN(NULL);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO NOTIN(10,NULL);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =(SELECT DEPTNO FROM DEPT );--错误“单行子查询出现多行”

 

多列子查询适应于：成对比较；非成对比较。

非成对的子查询：

SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID

  FROM EMPLOYEES

 WHERE(MANAGER_ID, DEPARTMENT_ID) IN

       (SELECT MANAGER_ID, DEPARTMENT_ID

          FROM EMPLOYEES

         WHERE EMPLOYEE_ID IN(178,174))

   AND EMPLOYEE_ID NOTIN(178,174);

 

只有要查询的东西和子查询返回的东西一一对应上了，查询条件才能满足，

如果有一个对应不上那么查询不会满足。

 

非成对的子查询：

SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID

  FROM EMPLOYEES

 WHERE MANAGER_ID IN

       (SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN(174,141))

   AND DEPARTMENT_ID IN

       (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN(174,141))

   AND EMPLOYEE_ID NOTIN(174,141);

 

多行嵌套查询(子查询的结果是多行的)

多值比较运算符

          in、exists、not in、not exists之间的区别

          all、any（some替代）、not in、in之间的区别

来看下面这样一条SQL语句：

select*from student where20<all(select score from student)

其中，(select score from student)返回的是所有分数的记录集

只有当记录集中的所有分数都大于20后，才会执行前面的SQL语句。显然，任何一条记录都大于20.

如果把它改成50，则查不到任何数据，因为只要有一条记录小于50,条件都不成立。

但是，如果把all改面any就不一样了：

select*from student where50<any(select score from student)

它的意思是，只要记录集中的记录有一条大于50,条件就成立。

1、ALL与ANY的关系就是，AND与 OR的关系。

2、ANY与SOME等价，据说搞这两个不同的词出来是为了迁就英语语法。例如，在用=ANY的地方在（英语） 语法上就应该是=SOME。 some是新版本的SQL中取代any的关键字，用法基本一样。

3、 IN与= ANY等价 ，均表示，变量在（子查询）列表之中，即 a IN(table B)表示 a =ANY B.b

4、 NOTIN与 <>ALL等价，而不等于<>ANY，前两者均表示，变量不在（子查询）列表之中，即 a NOTIN(table B)表示 a <>ALL B.b。而如果a <>ANY B.b，则只要任意一个b<>a就true了。

5、 IN与 EXISTS的性能区别主要来自，IN会编列子查询的每行记录，然后再返回，而EXISTS 则只要遇到第一个满足条件的记录就马上返回。

6、NOTIN与 NOTEXISTS并不能完全等价，只有当子查询中，select关键字后的字段有not null约束或者有这种暗示时才用NOT IN。

 

 

[例]查询所有雇员的工资都不低于1000的部门的所有人员信息

SQL>select ename,sal,job from emp where deptno notin(select deptno from emp where sal <1000);

SQL>select ename,sal,job from emp where deptno !=all(select deptno from emp where sal <1000);

结果

ENAME              SAL       JOB

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

CLARK               2450     MANAGER

KING                5000     PRESIDENT

MILLER              1300      CLERK

[例2]查找有工资在4000-5000之间的雇员所在部门的所有人员信息。

SQL>  select ename,sal,job from emp where deptno  =any(select deptno from emp where sal between4000and5000);

结果

  ENAME                 SAL     JOB

  ---------------- ---------- -----------------

  CLARK                  2450   MANAGER

  KING                     5000   PRESIDENT

  MILLER                   1300    CLERK

 

[例3]查找那些雇员的部门号在部门表中没有的雇员的信息。

SQL>select ename, sal, job from  emp  where  notexists(select  *  from dept where deptno = emp.deptno);

分组查询

（一般句中有每个、各个字样的要用到分组查询）

Having后一般是统计函数,不能是emp.depno=dept.depno这样的等式

常用统计函数常与分组查询一块用

常用统计函数（一般where后不用统计函数）

 函数AVG 和 SUM 应用于数值型

MIN 和 MAX 可用于任意类型.

函数AVG 和 SUM 应用于数值型

COUNT(*) 返回符合条件的记录数.(包括含null的所有行数)

COUNT(expr) 返回的非NULL的行数.

集合函数忽略列中的 null 值(COUTN除外).

SQL> SELECT AVG(comm)  FROM   emp;

在组函数中使用 NVL函数，NVL 函数强制集合函数包括 null 值.

SQL> SELECT AVG(NVL(comm,0))  FROM   emp;

总结：当NVL(comm,0)时，佣金值赋0值，显示结果为0，不空

      当NVL(comm,-1)时，佣金值赋-1值，不显示结果，为空

NVL 函数：

功能：把可能包含了空值的表达式1，转换成有实际意义的数据表达式2 。

格式：NVL(表达式1，表达式2）

作用的数据类型：可用于字符型、数值型、日期型.

注意事项：转换前后的类型必须一致：

NVL(comm,0)

NVL(hiredate,'01-JAN-97')

NVL(job,'No Job Yet')

 

在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除聚集函数外），均应该包含在GROUP BY 子句中。

在 GROUP BY 从句中使用多个列

SQL > SELECT   deptno, job, sum(sal)  FROM  emp  GROUP BY deptno, job

使用 HAVING 从句选择满足条件分组

执行次序：

对行进行分组.

对每组数据执行组函数.

返回符合 HAVING 从句的分组结果.

WHERE与HAVING的区别

作用对象不同。

WHERE 作用于基本表或视图，从中选择满足条件的元组

HAVING短语作用于组，从中选择满足条件的组。

[例]查询平均工资超过2000的部门，并按部门号进行排序

SQL> SELECT  deptno, avg(sal)  from emp 

     GROUP BY deptno 

     HAVING  AVG(SAL)>2000  

     ORDER BY   deptno;

结果

DEPTNO   AVG(SAL)

               10    3725

               20    2175

集合查询：

查询结果的集合运算

并集（UNION [ ALL]）

交集（INTERSECT [ALL]）

差集（MINUS [ALL] ）

 

1.  union[all]:

取多个查询结果的并集,不要all表示重复的记录只保留一条

   注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE JOB ='CLERK'

   UNIONALL-- ALL可以省略

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE DEPTNO =20

    ORDERBY ENAME;

2.  minus:取多个查询结果的差集

   注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE JOB ='CLERK'

   MINUS

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE DEPTNO =20

    ORDERBY EMPNO;

 

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE DEPTNO =20

   MINUS

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE JOB ='CLERK'

    ORDERBY EMPNO;

 

3.  intersect:取多个查询结果的交集

   注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE JOB ='CLERK'

   INTERSECT

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

     FROM EMP

    WHERE DEPTNO =20

    ORDERBY EMPNO;

二、数据操纵语言DML

数据操纵语言DML主要有四种形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 删除：DELETE

4）合并：MERGE（插入或修改）

INSERT 语句

插入单个元组

格式：

INSERT INTO <表名>[(<属性列1>[,<属性列2>]…)]VALUES (<常量1>[,<常量2>]…); 

注意：

1）在表定义时说明了NOT NULL的属性列不能取空值，否则会出错。

2）如果INTO子句中没有指明任何列名，则新插入的纪录必须在每个属性列上均有值。

3）指定列名时，列名顺序任意，列值与列名对应.

4）字符型和日期型数据在插入时要加单引号。

 

[例]

SQL>INSERTINTO DEPT VALUES(60,'PRODUCTION','SAN FRANCISCO');

SQL>INSERTINTO DEPT(DNAME,DEPTNO)VALUES('TEST',70);

 

插入子查询结果

格式：

INSERT INTO <表名>[(<属性列1>[,<属性列2>]…)]SELECT [(<属性列1>[,<属性列2>]…)] FROM <表名>;

 

[例]向表中插入一条与SMITH的内容相同的记录，但姓名改为了FAN,雇员号改为了8000.

SQL>INSERT  INTO  EMP  (EMPNO,  ENAME, JOB, MGR ,HIREDATE, SAL,, COMM, DEPTNO) 

SELECT8000,'FAN',JOB,MGR, HIREDATE, SAL, COMM, DEPTNO  FROM EMP WHERE  ENAME ='SMITH';

 

UPDATE语句

修改某一个元组的值

格式：UPDATE <表名>SET <列名>=<表达式>[,<列名>=<表达式>]…[WHERE<条件>];

[例]将雇员号为8000的雇员提升为部门20的经理，工资增加1000。.

SQL> UPDATE EMP SET JOB = 'MANAGER',SAL = SAL + 1000,DEPTNO = 20 WHERE EMPNO = 8000;

修改多个元组的值

[例]将部门号为20的所有的雇员的工资加10%.

SQL> UPDATE EMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20;

带子查询的修改语句

[例]将20号部门的所有雇员的工资都变为该部门平均工资加1000.

SQL> UPDATE EMP SET SAL = 1000 + (SELECT AVG (SAL) FROM EMP WHERE DEPTNO = 20) WHERE DEPTNO = 20;

 

DELETE语句

删除某一个元组的值

格式：DELETE FROM<表名> [WHERE<条件>];      

 

[例]删除名字为FAN的雇员.

SQL> DELETE FROM EMP WHERE ENAME = 'FAN';

删除多个元组的值

[例]删除EMP表中的所有数据.

SQL> DELETE FROM EMP ；

带子查询的删除语句

[例]删除所有与雇员FAN相同部门的雇员信息。

SQL> DELETE FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ‘FAN');

 

MERGE语句（合并，插入或修改）

 

Mergeinto详细介绍

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。

通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，

连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。

这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。

 

 

语法格式：

MERGE [INTO [schema .] table [t_alias]

USING [schema .] { table|view| subquery } [t_alias]

ON( condition )

WHENMATCHEDTHEN merge_update_clause

WHENNOTMATCHEDTHEN merge_insert_clause;

 

 

语法说明：

MERGEINTO [your table-name] [rename your table here]

USING( [write your query here] )[rename your query-sqlandusing just like a table]

ON([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can executesomeupdatesqlor something else ]

WHENNOT MATHED THEN [execute something else here  ]

 

 

[示例]

MERGEINTO EMP --emp表是需要更新的表

USING(SELECT*FROM EMP1 WHERE EMP1.DEPTNO ='10') T -- 关联表

ON(EMP.EMPNO = T.EMPNO)--关联条件

WHENMATCHEDTHEN--匹配关联条件，作更新处理

  UPDATE

     SET EMP.ENAME ='newname', EMP.COMM =888--此处只是说明可以同时更新多个字段。

WHENNOTMATCHEDTHEN--不匹配关联条件，作插入处理。如果只是作更新，下面的语句可以省略。

  INSERTVALUES(T.EMPNO, T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO);

 

 

[示例]不能更新ON (EMP.EMPNO = T.EMPNO)关联条件中的字段

MERGEINTO EMP --emp表是需要更新的表

USING(SELECT*FROM EMP1 WHERE EMP1.DEPTNO ='10') T -- 关联表

ON(EMP.EMPNO = T.EMPNO)--关联条件

WHENMATCHEDTHEN--匹配关联条件，作更新处理

  UPDATE

     SET EMP.EMPNO =888--不能更新ON (EMP.EMPNO = T.EMPNO)关联条件中的字段

WHENNOTMATCHEDTHEN--不匹配关联条件，作插入处理。如果只是作更新，下面的语句可以省略。

  INSERTVALUES(T.EMPNO, T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO);

三、数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

| | | | |

表 视图 索引 同义词 簇

创建表（CREATE）的一般格式：

CREATE TABEL <表名>

(<列名><数据类型>[列级完整性约束条件]

[,<列名><数据类型>[列级完整性约束条件]]

………

[,<列名><数据类型><表级完整性约束条件>]

);

CREATE TABEL 的总结：

1、实体完整性规则：主键（所有主属性）非空。

2、参照完整性规则：不得引用不存在的外键（定义外键）。

3、用户定义的约束（CHECK,UNIQUE,NOT NULL)。

 

[例]  创建学生表

SQL>  CREATE TABLE STUDENT

(sno   CHAR(5)             primary key,

sname CHAR(20)         not null,

ssex CHAR(2)           CHECK(ssex IN(‘男’,‘女’)) ,

sage NUMBER(2)         CHECK（sage between 16 and 20),

Sclass  CHAR(7)         not null,

);

创建课程表

SQL> CREATE TABLE COURCE

     (CNO   CHAR(4)        PRIMARY  KEY,

      CNAME  CHAR(16)       NOT NULL);

      创建成绩表

SQL>CREATE TABLE score

     ( SNO   CHAR(4)， 

       CNO  CHAR(16) ，

       SCORE1  NUMBER（5，2），

       PRIMARY KEY（SNO，CNO)，

       FOREIGN   KEY（SNO）

       REFERENCES  STUDENT（SNO），

       FOREIGN   KEY（CNO）

       REFERENCES  COURCE（CNO））；

[例]创建新表，结构与STUDENT相同，并拷贝数据。

SQL> CREATE TABLE Student_COPY AS SELECT * FROM STUDENT;

[例]创建新表，结构与STUDENT相同，不拷贝数据。

SQL> CREATE  TABLE   Student_COPY AS  SELECT *  FROM STUDENT   where1=2;

基本表的修改

一般格式：

ALTER TABLE <表名>

[ADD <新列名><数据类型>[完整性约束]]

[DROP <完整性约束名>]

[MODIFY <列名><数据类型>];

说明

ADD子句用于新增列及其完整性约束条件;

DROP子句用于删除指定的完整性约束条件;

MODIFY子句用于修改原有的列定义,包括修改列名和数据类型。

SQL没有提供删除属性列的语句，用户只能间接实现这一功能。方法是先将表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重新命名为原表。

[例]删除STUDENT表上SNAME列上的唯一约束。

           ALTER TABLE STUDENT DROP UNIQUE(SNAME);

[例]删除STUDENT表上建立的主键约束。

           ALTER TABLE STUDENT DROP PRIMARY KEY;

[例]为表STUDENT添加一个新的列。

            ALTER TABLE STUDENT ADD(SID CHAR(18) NULL);

注意：新增加的字段只能作为表的最后一个字段。

[例]将刚添加的列SID改为DATE类型。

             ALTER TABLE STUDENT MODIFY SID DATE;

 

基本表的删除

DROP TABLE <表名>；

   ALTER TABLE的补充：（constraint <定义主建的约束名>或constraint <定义外建的约束名>）

 索引的创建（INDEX）一般格式：（unique对应表，distinct对应列）

CREATE [UNIQUE] INDEX <索引名>ON <表名>(column1,column2 ···)

[tablespace 表空间名]

[storage storage_clause);

[说明]

<表名>是要建立索引的基本表名字。

索引可建立在一列或多列上，割裂名之间用逗号分隔。

索引值的排列次序缺省为ASC。

[UNIQUE]指明此索引的每一个索引值只对应唯一的数据记录。（唯一性）

[例]为表EMP建立以ENAME为索引列的索引，索引名为emp_index, 索引存放的表空间为index 。

SQL> CREATE INDEX emp_index  ON  EMP(ENAME DESC) tablespace index ;

[例]在表DEPT的DNAME列上建立唯一性索引，索引名为ON_DNAME。

SQL> CREATE UNIQUE INDEX ON_DNAME ON DEPT(DNAME DESC);

当表中有数据时，建立索引将检查数据的唯一性，

如果出现重复，将有下列提示：

ORA-01452: 无法 CREATE UNIQUE INDEX；

找到重复的关键字

建立了唯一性索引后，以后对数据的修改将自动进行唯一性验证。

索引的删除：

一般格式：

DROP INDEX <索引名称>;

总结索引：索引一旦建立，就由系统使用和维护，不需要用户干预。

索引的建立是为了减少查询时间，但如果数据增删频繁，系统将会花费很多时间来维护索引，因此应该删除一些不必要

 

视图的创建（VIEW）一般格式：

CREATE VIEW <视图名>[(<列名>[,<列名>]…)]

AS  SELECT 语句

[WITH CHECK OPTION];

说明

其中子查询可以是任意复杂的SELECT 语句，但通常不允许含有ORDER BY子句和DISTINCT短语。

WITH CHECK OPTION表示对视图进行UPDATE, INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件。

属性列名或者全部省略或者全部指定，没有第3种选择。

[例]建立一个视图，使它包含所有工资高于3000的雇员的姓名、工资、部门以及部门地址

SQL> CREATE VIEW HIGHSAL (NAME, SAL, DEPT, LOC)

AS

SELECT ENAME, SAL, DNAME, LOC

FROM EMP, DEPT

WHERE SAL>3000 AND DEPT.DEPTNO =EMP.DEPTNO;

 

视图的删除

一般格式：

DROP VIEW <视图>;

[例]删除TEST视图。

DROP VIEW TEST;

      总结：视图是保存select语句的，为了下一次懒得写而存在的，下一次只需

select * from HIGHSAL;

四、数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权。DCL语句主要有如下两种：

1) GRANT：授予其他用户对数据库结构的访问权限。

2) REVOKE：收回用户访问数据库结构的权限。

五、事务控制 TC

用于将对行所做的修改永久性地存储到表中，或者取消这些修改操作。

控制数据库操纵事务发生的时间及效果，对数据库实行监视等。TC语句主要有如下几种：

1) COMMIT：永久性地保存对行所做的修改。

2) ROLLBACK [WORK] TO [SAVEPOINT]：取消对行所做的修改，或回退到某一点。

3）SAVEPOINT：设置一个“保存点”，可以将对行的修改回液滚到此处。

 

回滚命令使数据库状态回到上次最后提交的状态。其格式为：

SQL>ROLLBACK;

ROLLBACK后的数据状态：

数据改变被取消(Undo).

数据恢复到以前状态.

被影响的行的锁被释放.

SQL>DELETEFROM EMP WHERE EMP.DEPTNO =88;

SQL>ROLLBACK;

 

Rollback到某一点：

用SAVEPOINT在当前事务中指定该点.

使用ROLLBACK TO SAVEPOINT 回退至该点.

SQL> UPDATE EMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20;

SQL>SAVEPOINT UPDATE_done

SQL> INSERT INTO DEPT(DNAME,DEPTNO) VALUES('TEST',70);

SQL>ROLLBACK To UPDATE_done

COMMIT [WORK]：提交

COMMIT/ROLLBACK前数据的状态：

        被修改的数据可以修改.

当前用户可以看到DML操作的数据改动结果.

其他用户不能看到DML操作的数据改动结果.

被影响的行被锁定(lock); 其他用户不能修改被影响的行.

COMMIT后的数据状态：

被改变的数据被持久写入DB.

以前的数据状态不可恢复.

所有用户都可以看到.

被锁定的行解锁—其他用户可以操作这些行.

全部savepoint被清除.

总结：一旦commit就不能rollback

提交数据有三种类型：显式提交、隐式提交及自动提交。

 

显式提交：

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT；

[例子]修改

SQL>UPDATE EMP SET EMP.COMM =888WHERE EMP.DEPTNO =88;

执行

SQL>COMMIT;

 隐式提交：

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，

EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

自动提交：

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，

系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON;