`
weishaoxiang
  • 浏览: 96216 次
  • 性别: Icon_minigender_1
  • 来自: 广西
社区版块
存档分类
最新评论

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 ANDNOT BETWEEN AND

确定集合

INNOT IN

字符匹配

LIKENOT LIKE

空值

IS NULLIS NOT NULL

多重条件

ANDOR

 

 

确定范围:

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不能用=替代

多重条件:用逻辑运算符NOTANDOR来联结多个查询条件。

优先级:NOTANDOR(用户可以用括号改变优先级)。

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

 

SELECT的交互查询:

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

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

 

3、排序: ASC升序排序,DESC降序排序

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

表达式排序:

SQL>SELECT EMPNO, ENAME, SALSAL *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 joinusing 是相互排斥的

   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);

 

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

多值比较运算符

          inexistsnot innot exists之间的区别

          allanysome替代)、not inin之间的区别

来看下面这样一条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,条件就成立。

1ALLANY的关系就是,ANDOR的关系。

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

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

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

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

6NOTINNOTEXISTS并不能完全等价,只有当子查询中,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 从句的分组结果.

WHEREHAVING的区别

作用对象不同。

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表示重复的记录只保留一条

   注意:unionminusintersect做连接查询时,多个查询结果的字段和类型要一致

   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:取多个查询结果的差集

   注意:unionminusintersect做连接查询时,多个查询结果的字段和类型要一致

   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:取多个查询结果的交集

   注意:unionminusintersect做连接查询时,多个查询结果的字段和类型要一致

   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新增的语法,用来合并UPDATEINSERT语句。

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

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

这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERTUPDATE

 

 

语法格式:

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)         CHECKsage 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  NUMBER52),

       PRIMARY KEYSNOCNO)

       FOREIGN   KEYSNO

       REFERENCES  STUDENTSNO),

       FOREIGN   KEYCNO

       REFERENCES  COURCECNO));

[]创建新表,结构与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 ;

[]在表DEPTDNAME列上建立唯一性索引,索引名为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, INSERTDELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件。

属性列名或者全部省略或者全部指定,没有第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]:取消对行所做的修改,或回退到某一点。

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

 

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

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命令间接完成的提交为隐式提交。这些命令是:

ALTERAUDITCOMMENTCONNECTCREATEDISCONNECTDROP

EXITGRANTNOAUDITQUITREVOKERENAME

自动提交:

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

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

SQL>SET AUTOCOMMIT ON;

分享到:
评论

相关推荐

    Oracle学习笔记 PDF

    ### Oracle学习笔记知识点详解 #### 一、Oracle简介 Oracle是一家知名的软件公司,以其数据库管理系统闻名全球。该公司成立于1977年,总部位于美国加利福尼亚州。Oracle不仅提供数据库解决方案,还涉及中间件、...

    Oracle学习笔记精华版

    Oracle学习笔记精华版是针对数据库管理系统Oracle的一份重要学习资源,涵盖了从基础概念到高级特性的全面知识。Oracle,作为全球广泛使用的大型企业级数据库系统,对于IT专业人员尤其是数据库管理员(DBA)来说,是...

    Oracle SQLServer数据库 学习笔记

    3. **PL/SQL**:Oracle特有的过程化语言,用于编写存储过程、函数和触发器,增强数据库的业务逻辑。 4. **数据仓库与OLAP**:Oracle提供强大的数据仓库功能和在线分析处理(OLAP)工具,支持复杂的数据分析。 5. **...

    Oracle学习笔记.pdf

    以下是对Oracle学习笔记中提到的一些关键知识点的详细解释: 1. **SQL执行顺序**: SQL语句的执行顺序是:`FROM` -&gt; `WHERE` -&gt; `SELECT` -&gt; `GROUP BY` -&gt; `HAVING` -&gt; `ORDER BY`。首先从`FROM`子句开始,确定...

    oracle_sql笔记

    Oracle SQL是数据库管理员和开发人员在Oracle数据库系统中进行数据查询和管理的重要工具。这篇笔记主要涵盖了Oracle SQL的...这两份“Oracle SQL笔记”文档应包含了上述各个方面的详细解释和实例,值得仔细阅读和学习。

    精通 ORACLE SQL高级编程 学习笔记

    精通Oracle SQL【第2版】ORACLE SQL高级编程【第二版】学习笔记

    Oracle 10g sql 学习笔记

    Oracle 10g SQL 学习笔记涵盖了Oracle数据库的基础知识,包括版本信息、数据库系统特点、关系型数据库结构、SQL语言、操作环境以及常见的数据库操作。以下是对这些知识点的详细说明: 1. **Oracle 9i基础知识**: ...

    oracle学习笔记整理

    以下是对Oracle学习笔记整理的主要知识点的详细说明: 1. **数据库选择**: 在决定使用哪种数据库时,通常需要考虑项目的规模、性能需求、安全性要求以及可用资源。Oracle数据库因其稳定性、可扩展性和高性能而被...

    oracle学习笔记sql练习

    本资料包是针对Oracle SQL的学习笔记,包含了丰富的练习题及解答,旨在帮助学习者深入理解和掌握SQL在Oracle环境中的运用。 1. **SQL基本查询操作** "1.SQL基本查询操作.txt"这部分内容可能涵盖了简单的SELECT语句...

    Oracle学习笔记 Oracle学习笔记

    根据提供的信息,我们可以总结出以下Oracle数据库学习的关键知识点: ...以上是基于提供的内容整理出的Oracle学习笔记中的关键知识点。通过理解这些基础知识,可以更好地管理和操作Oracle数据库。

    oracle学习笔记下载

    ### Oracle 学习笔记知识点概览 #### 一、Oracle 数据库系统参数查询与管理 在 Oracle 数据库的学习过程中,了解如何查看和管理数据库的系统参数是非常重要的。这些参数直接影响着数据库的性能和稳定性。 ##### ...

    Oracle学习笔记

    从提供的标题、描述以及部分文本内容来看,这份“Oracle学习笔记”主要围绕Oracle数据库中的查询语句及其各种类型进行深入探讨。尽管描述部分重复了标题的文字,但主要内容段落包含了丰富的信息,下面将对这些知识点...

    MSDN oracle学习笔记

    在“MSDN Oracle学习笔记”中,我们可以期待找到关于Oracle数据库的详细讲解和实践指导。 首先,Oracle数据库的基础知识是必不可少的。这通常涵盖数据库系统的基本概念,如SQL(结构化查询语言)的使用,数据类型,...

    Oracle 10g 学习笔记

    │ Oracle学习笔记.pdf │ Oracle学习笔记.wps │ 安装Oracle后myEclipse不能正常使用.txt │ 手工配置listener.ora【避免出现ORA-12514错误】.txt │ 贴子树状态存储结构.jpg │ 贴子树状态存储结构.sql │ ├─01...

    java+SSH+oracle+sqlserver学习笔记

    【Java SSH Oracle SQLServer 学习笔记】 Java是一种广泛使用的面向对象的编程语言,它以其跨平台、开源和丰富的库而闻名。SSH是三个开源框架的缩写,分别是Spring、Struts和Hibernate,它们在Java Web开发中起着至...

    oracle学习笔记-入门基础

    索引类似于书籍的目录,是Oracle数据库为了加速SQL查询而创建的一种独立于表的物理结构。它们直接指向包含查询值的行,减少了磁盘I/O操作,从而提高查询速度。创建索引的语法为`CREATE INDEX index ON table (column...

    ORACLE经典学习笔记

    ### ORACLE经典学习笔记知识点概览 #### 第一章 ORACLE 命令 本章节主要介绍了Oracle数据库中常用的命令及其使用方法。 1. **查看参数文件**: `Desc v$parameter` - 这个命令用于查看Oracle的参数文件信息,通过...

    (转)最全的SQL(Oracle)笔记集录

    这篇资源是一个全面的SQL,特别是针对Oracle数据库的学习笔记集合。Oracle是世界上最广泛使用的数据库管理系统之一,它提供了丰富的功能来处理各种类型的数据和满足复杂的业务需求。SQL,全称Structured Query ...

Global site tag (gtag.js) - Google Analytics