oracle中常用sql语句.ppt

1、SQL语句,ORACLE数据库规划与实施,ORACLE为信息系统提供了一个开放、综合、集成的方法。使用ORACLE数据库进行信息系统开发的步骤可以归纳为如下过程：,概述,本课内容 1、SQL语言 2、使用SELECT查询数据 3、插入、更新、删除数据 4、事务的概念 5、使用简单的数据定义语言,SQL语言,1、什么是SQL语言 Oracle完全遵从ANSI的SQL标准SQL-99 SQL(Structure Query Language)是关系数据库的基本操作语言，是应用程序与数据库进行交互操作的接口，它集数据查询、数据操纵、数据定义和数据控制功能于一体。,SQL语言,2、SQL语言类型： 数

2、据查询语言：用于检索数据库数据 数据定义语言（DDL）：建立、修改和删除数据库对象 数据操纵语言(DML)：改变数据库数据，包括insert,update和delete语句 事务控制语句（TCL）：用于维护数据的一致性，它包括COMMIT,ROLLBACK和SAVEPOINT 数据控制语言(DCL):用于执行权限授予和收回操作,SQL语言,3、SQL语言： Sql关键字不区分大小写 对象名和列名不区分大小写 字符值和日期值区分大小写,简单查询语句,基本语法： Desc可以显示表结构 查询所有列:(select语句后指定*） 查询指定列：(select语句后指定列名，如果是多列，列名之间用逗号隔

3、开）,Select from table;,Select * from company.employee_BASIC;,Select emp_no,emp_name from company.employee_BASIC;,简单查询语句,查询日期列（默认的日期格式为DD-MON-YY，如果语言为SIMPLIFIED CHINESE，则月名显示为中文格式(如5月),如果为American,则月名显示为英文简写格式(如MAY)。 取消重复列：使用distinct关键字，该关键字跟在select子句的后面，列名的前面。 使用算术表达式：在数字列上使用算术表达式(+-*/),Select distin

4、ct sal,job,deptno from emp;,Select ename,sal*12 from emp;,简单查询语句,使用列别名：列别名要跟在列名后面，二者之间可以加as关键字。 处理NULL： NULL表示未知值，当给表插入数据时，如果没有给某列提供数据，并且该列没有默认值，那么其数据为NULL,当表达式包含NULL时，其结果也是NULL.使用函数nvl和nvl2可以处理null值。 连接字符串使用“|”操作符来完成。,Select ename “姓名”,sal*12 AS “年收入” from emp;,Select ename,sal,sal+nvl(comm,0) “年收入

5、” from emp;,Select ename | ia a | job from emp;,使用where子句,基本语法:(where用于指定条件子句,condition对应具体的条件,如果条件为true,则返回该行数据,否则,不返回该行数据),Select distinct from table where condition(s);,使用where子句,当编写条件子句时,需要使用各种比较操作符,使用where子句,在WHERE条件中使用数字值:即可以用单引号引住,也可以直接引用 在WHERE条件中使用字符值:必须用单引号引住,字符值区分大小写. 在WHERE条件中使用日期值:必须用单引

6、号引住,且日期值必须符合日期格式,Select ename ,sal from emp where sal3000;,Select job ,sal from emp where ename=scott;,Select ename ,sal,birthday from emp where birthday01-1月-82;,使用where子句,在WHERE条件中使用BETWEENAND: BETWEEN后指定较小值,AND后指定较大值. 在WHERE条件中使用LIKE操作符:LIKE执行模糊查询,%表示0或多个字符,-表示单个字符. 在WHERE条件中使用IN操作符:IN操作符用于执行列表匹配

7、操作,当列或表达式结果匹配于列表中的任一值时,条件为true,Select ename ,sal,birthday from emp where sal between 2000 and 2500;,Select ename ,sal from emp where ename LIKE s%;,Select ename ,sal from emp where sal IN (800,1250);,使用where子句,在WHERE条件中使用IS NULL操作符:IS NULL 操作符用于检测列或表达式的结果是否为NULL,当与NULL进行比较时不要使用=,操作符. 在WHERE条件中使用逻辑操作

8、符(AND,OR和NOT):用于指定多个查询条件,Select ename ,mgr from emp where mgr IS NULL;,Select ename ,sal,job,deptno from emp where deptno=20 and job=CLERK;,使用ORDER BY子句,基本语法: 升序排序:默认为升序排序,可指定ASC关键字,NULL列排在最后 降序排序:必须在排序列后指定DESC关键字, NULL列排在最前,Select distinct from table where condition(s) ORDER BY column;,Select ename

9、 ,sal from emp order by sal;,Select ename ,sal from emp order by sal DESC;,使用ORDER BY子句,使用多列排序：首先按照第一列进行排序，当第一列存在相同数据时，再以第二列进行排序。 使用非选择列表进行排序：选择列表中也可以不包含任何排序列。,Select ename ,deptno,sal from emp order by deptno ASC,sal DESC;,Select ename from emp order by sal DESC;,使用ORDER BY子句,使用列别名排序：当为列或表达式定义了别名，可

10、以使用列或表达式，也可以使用别名。 使用列位置编号排序：,Select ename,sal*12 “全年工资” from emp order by “全年工资” DESC;,Select ename,sal*12 “全年工资” from emp order by 2 DESC;,数据分组,在实际应用中，经常需要对数据进行统计，需要将表的数据划分成几个组，最终统计每个组的数据结果，如不同部门的雇员总数、雇员平均工资。 数据分组是通过GROUP BY子句、分组函数和HAVING子句实现的。GROUP BY子句用于指定分组的列，分组函数用于显示统计结果（COUNT、AVG、SUM等），HAVING子

11、句用于限制分组显示结果。,Select column,group_function from table where condition GROUP BY group_by_expression HAVING group_condition;,数据分组（分组函数）,一般分组函数要与GROUP BY子句结合使用，如果忽略GROUP BY子句，那么会汇总所有列。常用分组函数： MAX和MIN：用于取得列或表达式的最大值和最小值 AVG和SUM：用于取得列或表达式的平均值和总和 COUNT：用于取得总计行数 VARIANCE：用于取得列或表达式的方差 STDDEV:用于取得列或表达式的标准偏差,数据

12、分组（GROUP BY 子句和HAVING子句）,GROUP BY子句用于对查询结果进行分组统计，HAVING 子句用于限制分组显示结果。 单列分组是指基于单列生成分组统计结果 多列分组是基于两个或两个以上的列生成分组统计结果 使用having 子句显示分组显示结果,Select deptno,avg(sal),max(sal) from emp group by deptno;,Select deptno,job,avg(sal),max(sal) from emp group by deptno,job;,Select deptno,avg(sal),max(sal) from emp g

13、roup by deptno having avg(sal)2000;,连接查询,连接查询是指基于两个或两个以上表或视图的查询。 相等连接：相等连接是指使用=指定连接条件的连接查询,Select table1.column,table2.column from table1,table2 where table1.column1=table2.column2;,子查询,子查询：嵌入到其他SQL语句中的SELECT语句，也被称为嵌套查询 根据子查询返回结果的不同，可以将子查询分为单行子查询、多行子查询和多列子查询。 单行子查询：只返回一行数据的子查询语句。可以使用单行比较符(=、=、),Sele

14、ct ename,sal,deptno from emp where deptno= (select deptno from emp where ename=scott);,子查询,多行子查询：指返回多行数据的子查询语句。必须使用多行比较符(IN、ALL、ANY),注意：ANY和ALL操作符不能单独使用，只能与单行比较符(=、=、)结合使用。,Select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);,子查询,多列子查询：查询返回多个列数据的子查询语句。,Se

15、lect ename,sal,deptno from emp where salall (select sal from emp where deptno=30);,Select ename,sal,deptno from emp where salany (select sal from emp where deptno=10);,Select ename,job,sal,deptno from emp where (deptno,job)= (select deptno,job from emp where ename=SMITH);,合并查询,为了合并多个SELECT语句的结果，可以使用

16、集合操作符UNION、UNION ALL、INTERSECT和MINUS。注意：不同查询列个数和数据类型要匹配 UNION：用于取得两个结果集的并集，去掉结果集中的重复行，并以第一列进行排序。,Select 语句1 UNION | UNION ALL| INTERSECT| MINUS select 语句2 ;,Select ename,sal,job from emp where sal2500 Union Select ename,sal,job from emp where job=MANAGER;,合并查询,Union all：与union相似，只是不会取消重复值，而且也不会以任何列进

17、行排序。 INTERSECT ：取得两个结果集的交集，并以第一列进行排序后显示结果。 MINUS：用于取得两个结果集的差集，以第一列进行排序。,Select ename,sal,job from emp where sal2500 MINUS(UNION ALL)(INTERSECT) Select ename,sal,job from emp where job=MANAGER;,插入、更新、删除数据,1、INSERT插入数据 2、UPDATE更新数据 3、DELETE删除数据,插入数据,注意事项： 数字直接提供数字值，如果为字符或日期列，则必须用单引号引住。 数据必须满足约束规则 数据必须

18、与列的个数和顺序保持一致 语法:,INSERT INTO (column,column,) Values(value,value,);,插入数据,不使用列列表插入数据: 使用列列表插入数据: 使用子查询插入数据,INSERT INTO (column,column,) SubQuery;,更新数据,语法: 使用子查询更新数据:,UPDATE SET = ,=where ;,UPDATE emp SET (job,sal,comm)=(SELECT job,sal,comm FROM emp WHERE ename=SMITH) WHERE ename=SCOTT;,删除数据,语法: 删除满足条件数据： 删除表的所有数据:注意与TRUNCATE TABLE的区别 使用子查询删除数据,DELETE FROM where ;,DELETE FROM emp where ename=SMITH;,DELETE FROM emp;,DELETE FROM emp WHERE deptno=(select deptno from dept where dname=sales);,事务的概念,事务：逻辑上作为一个单元完成的一些列查询或修改语句。当在应用程序中执行第一条SQL语句时，会开始事务，当执行commit或r