oracle基础学习.ppt

第三章 SQL语言基础,SQL概述,SQL(Structured Query Language)结构化查询语言，是一种在关系数据库中定义和操纵数据库的标准语言，是用户与数据库之间进行交流的接口。SQL语言已经被大多数关系数据库管理系统采用。Oracle数据库提供的许多有用而强大的功能都需要通过SQL语言来体现。因此要使用Oracle数据库，一定要掌握SQL语言。,SQL 特点,综合统一高度非过程化面向集合的操作方式同一种语法结构提供两种使用方式语言简洁、易学易用,分类,Select 查询语句数据定义语言（Data Definition Language,DDL）：create,alter,drop,truncate数据操纵语言（Data Manipulation Language,DML):insert,update,delete,merge数据控制语言（Data Control Language,DCL):grant,revoke事务控制（Transaction Control）：commit，rollback,savepoint,SELECT语句,数据检索是数据库中最频繁执行的活动，所以SELECT是使用频率最高的语句，它具有强大的查询功能，有的用户甚至只需要熟练掌握SELECT语句的一部分，就可以轻松地利用数据库来完成自己的工作。可以说SELECT是SQL语言的灵魂。SELECT语句的作用是让数据库服务器根据客户端的要求搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。,查询数据,SELECT语句的语法格式如下：SELECT DISTINCT select_list FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC|DESC 其中：DISTINCT：用于删除结果集中重复的行。select_list 指明要查询的选择列表。列表可以包括若干个列名或表达式，列名或表达式之间用逗号隔开，用来指示应该返回哪些数据。表达式可以是列名、函数或常数的列表。,FROM table_source 指定所查询的表或视图的名称。WHERE search_condition 指明查询所要满足的条件。GROUP BY group_by_expression 根据指定列中的值对结果集进行分组。HAVING search_condition 对用FROM、WHERE或GROUP BY子句创建的中间结果集进行的筛选。它通常与GROUP BY子句一起使用。ORDER BY order_expression ASC|DESC 对查询结果集中的行重新排序。ASC 和DESC关键字分别用于指定按升序或降序排序。如果省略ASC或DESC，则系统默认为升序。,使用字段别名重命名查询结果中的字段，以增强可读性，可以使用AS为字段定义别名。用法举例：Select deptno as 部门编号,dname as 部门名称 from dept;,select ename 姓名,sal 工资 from emp where deptno=10;,取消重复行使用distinct关键字可以取消查询结果中出现的重复行。用法举例：Select deptno,job from emp;,Select distinct deptno,job from emp;,使用算术表达式 在Select语句中，对NUMBER型数据可以使用算术运算符来创建表达式。算术运算符+-*/用法举例：select empno,ename,sal,sal*12 from emp;,连接运算符 连接运算符|可以把列与字符、或其它表达式连接在一起，得到一个新的字符串，实现合成列的功能。用法举例：select ename|is a|job from emp where deptno=20;,select empno,ename|s annual salary is|sal*12 from emp where deptno=20;,使用WHERE子句,WHERE子句指定数据检索的条件,以限制返回的数据行.WHERE子句中的查询条件比较运算符:=（等于）、（大于）、=（大于等于）、（不等于）范围说明:between A and B,not between A and B可选值列表：IN，NOT IN模式匹配：LIKE，NOT LIKE是否空值：IS NULL，IS NOT NULL上述条件的逻辑组合：NOT（非）、AND（与）、OR（或）,1 使用比较运算符 select*from emp where deptno20 and job=CLERK;,2 使用BETWEEN关键字BETWEEN关键字总是与AND一起使用，用来检索在一个指定范围内的信息，NOT BETWEEN检索不在某一范围内的信息。,select*from emp where sal between 2500 and 3500 and hiredate between 01-1月-81 and 31-12月-81;,3 使用IN关键字IN关键字允许用户选择与列表中的值相匹配的行，指定项必须用括号括起来，并用逗号隔开，表示“或”的关系。NOT IN表示含义正好相反。用法举例：查询部门编号为20或30的员工信息。select*from emp where deptno in(20,30);等价于select*from emp where deptno=20 or deptno=30;,4 使用LIKE关键字LIKE关键字用于查询与指定的某些字符串表达式模糊匹配的数据行。LIKE后的表达式被定义为字符串，必须用单引号（）括起来.通配符%：可匹配任意类型和长度的字符串。_（下划线）：可匹配任何单个字符。例如：LIKE 刘%匹配以“刘”开始的字符串；LIKE%技术%匹配的是前后字符为任意，中间含有“技术”两个字的字符串；LIKE _秀%匹配的是第2个字为“秀”的任意字符串。,用法举例：查询姓名的首字符为A或第二个字符为Ade员工信息。select*from emp where ename like A%or ename like _A%;查询姓名中出现字符A的员工信息。select*from emp where ename like%A%;,5 使用IS NULL关键字 IS NULL 关键字可以检索数据列中没有赋值的行。用法举例：显示津贴为空值的员工信息。select*from emp where comm is null;,6 使用GROUP BY子句,本子句写在WHERE子句之后，用于对查询的结果集进行分组。用法举例：显示每个部门的平均工资和最高工资。select deptno,avg(sal),max(sal)from emp group by deptno;显示每个部门、每种岗位的平均工资和最高工资。select deptno,job,avg(sal),max(sal)from emp group by deptno,job;,7 使用HAVING子句,HAVING用于限定组或聚合函数的查询条件，通常用在GROUP BY子句之后。通常，其作用与WHERE子句很相似。区别在于其作用的对象不同。WHERE子句作用于表和视图，HAVING子句作用于组。用法举例：select deptno,avg(sal),max(sal)from emp group by deptno;显示平均工资高于2000元的部门编号、平均工资和最高工资。select deptno,avg(sal),max(sal)from emp group by deptno having avg(sal)2000;,8 使用ORDER BY子句,ORDER BY子句对查询结果集中的行进行重新排序。ASC 和DESC关键字分别用于指定按升序或降序排序。如果省略ASC或DESC，则系统默认为升序。可以在ORDER BY子句中指定多个排序列，即嵌套排序，检索结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2列排序依此类推。用法举例：以部门编号降序及姓名升序来查询工资在2000-3000元的员工信息。select*from emp where sal between 2000 and 3000 order by deptno desc,ename;,组处理函数,把查询出的数据组的值进行汇总运算，返回单值的函数。常用的组处理函数：平均值：AVG 总和：SUM 最小值：MIN 最大值：MAX 计数：COUNTCOUNT（*）、COUNT（DISTINCT）,用法举例：,select*from emp where deptno=20;,select avg(sal)as arg1,avg(distinct sal)as arg2,max(sal)as max,min(sal)as min,sum(sal)as sum,count(*)as cnt1,count(sal)as cnt2,count(distinct sal)as cint3,count(comm)as cnt4 from emp where deptno=20;,子查询,子查询是在查询中包含另一个查询的查询。它本身是一个SELECT查询，可以代替表达式出现在WHERE子句中。它返回单个值且嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询的 SELECT 查询总是使用圆括号括起来。子查询中一般不使用order by 子句，order by 子句只能对最终查询结果进行排序。,单行子查询单行子查询只返回一行数据。在where子句中使用单行子查询时，可以使用单行运算符（=,=,)。用法举例：显示工资最高的员工信息。select*from emp where sal=(select max(sal)from emp);,多行子查询 多行子查询可以返回多行数据。在where子句中使用多行子查询时，必须使用多行运算符（IN,NOT IN,EXISTS,NOT EXISTS,ALL,ANY).如果不能确认子查询会返回多少行，则在子查询中使用多行运算符比使用单行运算符更安全，否则容易失败。用法举例：显示与部门编号为20的岗位相同的员工信息。select*from emp where job in(select distinct job from emp where deptno=20);,显示高于部门编号为20的所有员工工资的员工信息。,select*from emp where salall(select sal from emp where deptno=20);,显示高于部门编号为20的任意员工的工资的员工信息。select*from emp where salany(select sal from emp where deptno=20);,在DDL中使用子查询,1 在CREATE TABLE语句中使用子查询通过在CREATE TABLE语句中使用子查询，可以在创建新表的同时插入表中的数据。create table dept1(deptno,dname,loc)as select deptno,dname,loc from dept;,create table emp1 as select*from emp;,2 在create view中使用子查询create or replace view dept_20 as select*from emp1 where deptno=20 order by empno;,在DML中使用子查询,1 在UPDATE语句中使用子查询将与WARD岗位相同的员工的工资、补贴更新为与WARD完全相同。然后确认更新的结果。update emp1 set(sal,comm)=(select sal,comm from emp1 where ename=WARD)where job=(select job from emp1 where ename=WARD);select ename,job,sal,comm from emp1 where job=(select job from emp1 where ename=WARD);,2 在DELETE子句中使用子查询删除ACCOUNTIBG部门的员工信息。然后确认删除的结果。delete from emp1 where deptno=(select deptno from dept1 where dname=ACCOUNTING);select ename,job,deptno from emp1 where deptno=(select deptno from dept1 where dname=ACCOUNTING);,3在INSERT子句中使用子查询。将emp表中ACCOUNTING部门的雇员信息插入到emp表中，然后确认插入的结果。insert into emp1 select*from emp where deptno=(select deptno from dept where dname=ACCOUNTING);select*from emp1 where deptno=(select deptno from dept1 where dname=ACCOUNTING);,连接查询,用户在前面所作的查询大多是对单个表进行的查询，而在数据库的应用中，经常需要从多个相关的表中查询数据，这就需要使用连接查询。由于连接涉及多个表及其之间的引用，所以列的引用均必须明确，对于重复的列名必须用表名限定。,在Oracle系统中，为了从多个表中检索数据，可以使用下面的语法格式：select table1.column,table2.columnfrom table1,table2where table1.column1=table2.column2,笛卡尔乘积,连接查询的理论基础是笛卡尔乘积。也就是说，连接运算建立在笛卡尔乘积的基础上，所有连接运算的结果都是笛卡尔乘积的子集。笛卡尔乘积是指两个或两个以上的表进行相乘运算，结果是这些表中所有行的任意组合。例如，emp表中有14行数据，dept表中有4行数据，其结果中有56行数据。,连接类型,在连接运算中，根据指定的连接条件，可以把连接查询划分成3种类型，即内连接外连接交叉连接,内连接 内连接是组合两个表的常用方法，它将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。内连接主要有两种形式，相等连接和不等连接。相等连接就是在连接列相等的条件下执行的连接操作。不等连接是指在连接列不相等的连接条件下执行连接操作。,相等连接,用法举例：select empno,ename,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and emp.deptno=20;,可以使用表的别名来简化查询语句。select empno,ename,a.deptno,dname,loc from emp a,dept b where a.deptno=b.deptno and a.deptno=20;,不等连接,用法举例：select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal and a.deptno=20;,外连接,在外连接中，某些不满足条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另外一个表中的行。这种连接形式在许多情况下是非常有用的。外连接分为左外连接、右外连接和全外连接。对于外连接，oracle中可以使用“(+)”来表示，语法格式如下：select table1.column,table2.columnfrom table1,table2where table1.column1(+)=table2.column2,1、左外连接 左外连接对连接条件中左边的表不加限制。返回结果集中将包括左表中所有的记录，而不仅仅是连接字段所匹配的记录。如果左表中的某条记录在右表中没有匹配的记录，则结果集相应记录的有关右表的所有字段将为空值。例：select ename,a.deptno,sal,dname from emp a,dept b where a.deptno=b.deptno(+)and sal between 2500 and 3000;,等价于select ename,a.deptno,sal,dname from emp a left outer join dept b on a.deptno=b.deptno where sal between 2500 and 3000;,2右外连接右外联接对右边的表不加限制。返回结果集中将包括右表中所有的记录，而不仅仅是连接字段所匹配的记录。如果右表中的某条记录在左表中没有匹配的记录，则结果集相应记录的有关左表的所有字段将为空值。例：select ename,a.deptno,sal,b.deptno,dname from emp a,dept b where a.deptno(+)=b.deptno;,等价于：select ename,a.deptno,sal,b.deptno,dname from emp a right outer join dept b on a.deptno=b.deptno;,3、全外连接全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。结果集将包含两个数据表中的所有记录，当某条记录在另一个数据表中没有匹配记录时，则将另一个数据表的选择列表字段指定为空值。例：select ename,a.deptno,sal,b.deptno,dname from emp a full outer join dept b on a.deptno=b.deptno;,自连接（SELF JOIN）,自连接就是一个表与它自身的不同行进行连接。因为表名要在FROM子句中出现两次，所以需要对表指定两个别名，使之在逻辑上成为两张表。在SELECT子句中引用的列名也要使用表的别名进行限定。,select empno,ename,mgr from emp where deptno=30;,select a.ename 雇员,b.ename 经理 from emp a,emp b where a.mgr=b.empno and a.deptno=30;,交叉联接（CROSS JOIN）交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。语法如下：SELECT 列 FROM 表1 CROSS JOIN 表2 或 SELECT 列 FROM 表1，表2,select deptno from dept where deptno30;select deptno,ename from emp where job=CLERK;select dept.deptno,emp.deptno,ename from dept,emp where dept.deptno30 and emp.job=CLERK;,事务控制,日常生活中我们需要一种能保证数据完整性的机制，比如学生父母给孩子打的生活费要么发送成功，要么发送失败（不应该出现一边发送成功，另一边接受不到的情况），这样才是安全的、可靠的。数据库管理中也需要类似的机制，那就是事务。事务是作为单个逻辑工作单元执行的一系列操作。这一系列操作或者都被执行或者都不被执行。在一个事务中可以包括一条或多条SQL语句，如果一个事务执行成功，则表明该事务中的所有语句均执行成功，只要事务中的一条语句出现错误，则会取消整个事务的执行。,事务做为一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。事务具有以下4个属性：（1）原子性：事务应作为一个逻辑工作单元看待，事务中的所有语句要么全部执行，要么全部取消。（2）一致性：事务中的任何操作都不能破坏数据库的完整性。事务如果能够执行成功，则事务所修改的数据必须遵循数据库中各种约束、规则的要求，所有数据应该处于一致性状态。（3）隔离性：当多个事务同时运行时，不同的事务互不干扰，独立运行。（4）永久性：当事务执行成功后，事务对数据库所做的操作结果会永久保持。,在Oracle 中，可以使用commit命令提交完成的事务，使用rollback回滚事务，使用savepoint设置保留点。事务开始于第一条可执行语句；事务结束：遇到commit 或rollback语句遇到DDL 或DCL 语句用户会话结束系统崩溃事务的提交和回滚显式的提交和回滚操作是为了更好地保证数据的一致性。下述情况下事务会被自动提交 执行一个DDL 语句 执行一个DCL 语句 正常结束会话会话异常终止/系统崩溃时事务会被自动回滚,Sql Plus的自动提交 Sql Plus中执行SQL语句时可以设置是否自动提交，缺省设置为非自动提交。查看设置：show autocommit;autocommit OFF autocommit IMMEDIATE改变设置：set autocommit on;set autocommit off;,一个使用rollback的例子：,一个使用savepoint的例子：,SQL函数,函数是指有0个或多个参数并且返回一个值的程序段。Oracle的函数有5种类型：单行函数；组处理函数；分析函数；对象参考函数；自定义函数 单行函数是指输入一行，输出也是一行，以及直接对单个数据进行操作的函数。一般分为：数值函数、字符函数、日期函数、转换函数、其他函数等。,数学函数1.abs(x)函数此函数用来返回一个数的绝对值。2.sign(x)函数此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1，若为0则仍返回0。3.ceil(x)函数和floor(x)函数这两个函数中一个是用来返回大于或等于x的最小整数，另一个是用来返回小于或等于x的最大整数。4.三角函数sin(x)函数用来计算x的正弦值。5.mod(x,y)函数此函数返回x除以y的余数。6.round(x,y)函数round(x,y)函数用来四舍五入。7.trunc(x,y)执行截取数字。,例：select ROUND(45.926,2),ROUND(45.926),ROUND(45.926,-1)from dual;,select TRUNC(45.926,2),TRUNC(45.926),TRUNC(45.926,-1)from dual;,select mod(1600,300)from dual;,字符函数1ASCII(c)返回字符c对应的ASCII（十进制数）2SUBSTR(c,m)返回C的子串，其中m是子串开始的位置，n是子串的长度。3LENGTH(c)返回字符串c的长度。4LOWER(c)返回字符串c的所有字符的小写。5UPPER(c)返回字符串c的所有字符的大写6.CONCAT(c1,c2)连接两个字符串c1和c27LPAD（c1,n,c2)在c1的左边填c2,直到字符串的总长度到达n.,例：select length(oracle)from dual;select lower(ename)from emp where deptno=10;select SUBSTR(ename,1,3)from emp where deptno=10;,select CONCAT(ename,job)from emp where deptno=10;,select LPAD(ename,10,*)from emp where deptno=10;,日期时间函数1.sysdate函数返回当前数据库的日期时间，没有参数。2.add_months(x,y)函数此函数返回在日期x基础上再加上y个月后新的日期。3.last_day(x)函数此函数返回日期x的本月最后一天的日期。4.months_between(x,y)函数此函数返回日期x和日期y之间的月数。5.next_day(d,dow)返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。,Oracle默认日期格式：DD-MON-YY select sysdate from dual;,select MONTHS_BETWEEN(sysdate,hiredate)/12 from emp where deptno=10;,转换函数1.to_char(x,format-model)按照format-model的格式把x转换为相应的字符串。2TO_DATE(c,fomat-model)c表示字符串，format-model表示一种特殊格式的字符串。返回format-model格式的字符串c对应的日期。函数将字符串c转换成date数据类型。3TO_NUMBER(c,fomat-model)c表示字符串，format-model表示一个特殊格式的字符串，函数返回format-model格式的数字。函数将字符串c转换为相应的数字。,例：select to_char(sysdate,YYYY-MM-DD HH24:MI:SS)from dual;,select ename,to_char(hiredate,yyyy-mm-dd)hiredate from emp where deptno=20;,insert into emp(empno,ename,hiredate)values(9005,WANG,TO_DATE(1999-12-12,YYYY-MM-DD);,其他函数大家知道，NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为NULL值，这个规则也适合很多函数，但是在数据库查询中如果查询的结果为NULL，就无法参与运算了。如：300+NULL，这是非法的。怎么办呢？函数NVL专门处理空值NULL。格式为：NVL(x1,x2)其中x1和x2都是表达式，当x1不为null时返回x1，否则返回x2。参数x1和x2可以是任意数据类型，但必须匹配。例：select ename,sal,comm,sal+nvl(comm,0)from emp where deptno=30 order by ename;,NVL2(x1,x2,x3)其中x1、x2和x3都是表达式。当x1不为null时返回x2，否则返回x3。参数x1可以是任意数据类型，但参数x2和x3不能是long数据类型，而且二者必须与x1匹配。例：select ename,sal,comm,NVL2(comm,sal+comm,sal)from emp where deptno=20 order by ename;,上机练习,打开SQL*PLUS，进行下述练习：1请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。2请在EMP表中查找部门号在1030之间的雇员的姓名、部门号、工资、工作。3请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。4请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。5请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。6查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。7在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。,7列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。8雇员中谁的工资最高。9查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字。10查询各部门中81年1月1日以后来的员工数。11建立一个表（my_emp），表结构与EMP相同，并将emp表中的记录插入到该表中。12将my_emp中与员工SCOTT工资相同的员工的工资都增加300元。13练习课本上事务控制的例子。,