Oracle20的SQL语句.ppt

Oracle 的SQL语句,SQL语言,在数据库中，操纵数据库中数据的语言称为SQL(Structure Query Language的缩写)。该语言称为第三代语言。该语言使用简单。SQL语句包括：create，drop，insert，delete ，update，select等单词开头的语句。,1、删除表内容的语句,delete from 表名。例如：delete from t1 delete from 表名 where 条件。例如：delete from t1 where c1=1 注意：oracle不同时删除多个表的内容。如果删除一个名时，要参照另外一个表，则可以这样做：delete from t1 where exists (select * from t2 where t1.c1=t2.i1)。where后面的查询称为子查询。,更改表的结构,Alter table t1 add abc int; -增加一个列 Alter table t1 modify name varchar(20);-修改列的类型 Alter table t1 drop column abc;-删除一个列,2 、更新更中的数据,update 表名 set 。例如： update t1 set c1=1 update 表名 set where 条件。例如： update t1 set c1=2 where c1=1 注意：oracle不同时删除多个表的内容。如果删除一个名时，要参照另外一个表，则可以这样做： update t1 set c2 =3 where exists (select * from t2 where t1.c1=t2.i2)。where后面的查询称为子查询。,3、查询(select)语句,在数据库中，查询语句是指以select开头的SQL(Structure Query Langue的缩写)。 select是SQL的核心。也是最复杂的SQL语句。 select的作用就是从表中检索数据并显示给用户看。执行格式：select From Where ,3.1、查询(select)语句的执行过程,3.1、查询(select)语句的执行过程,如果现在这些步骤简介不那么容易让人明白请不要担心，因为它们是作为引用来提供的。在情景实例之后的章节会对这些步骤进行详细描述的。 FROM:在FROM子句中的前两个表之间作笛卡尔乘积（交叉连接），作为结果，会生成虚拟表VT1。 ON: 在VT1上应用ON筛选器。只有那些使为真的那些列才会被插入VT2。 OUTER(join):如果指定的是OUTER JOIN（和CROSS JOIN或INNER JOIN相对），对于保留表里没找到匹配项的行，将它们作为外部行添加到VT2中，这样我们就会得到VT3。如果在FROM子句中有多于两个表，那么会在上一个连接的结果和下一个表之间重复执行步骤1到步骤3直到所有的表都被处理过。 WHERE: 对VT3应用WHERE筛选器。只有那些使为真的行才被插入VT4。 GROUP BY:按照GROUP BY子句列出的列对VT4中的行进行分组，从而生成VT5. CUBE|ROLLUP:将超组（分组后再分组）插入VT5，从而产生VT6. HAVING:在VT6上执行HAVING筛选器。只有那些使为真的那些分组才会被插入VT7。 SELECT:处理SELECT列表，产生VT8 DISTINCT: 将重复的行从VT8中移除，产生VT9 ORDER BY: 将VT9中的行按照ORDER BY子句中列出的列进行排序，产生一个游标VC10； TOP:从VC10中选择指定数目或比例的前边几行产生VT11并返回给调用者。,3.2 简单的select语句,select * from t1 -查询t1表中所有的行和所有的列 select c1 from t1 - 查询t1表中c1列的所有的行 select c1 as mycol from t1 - 对查询的列取一个别名叫mycol select 1 ,c1 from t1 查询时增加辅助列 select c1+c2 ,hello from t1 将多列的内容相加形成新的列 select c1+c2 ,* from t1 这里的*表示取t1中的所有行和所有列。也可以写成select c1+c2 , t1.* from t1 ，如果取t1的某一列，可以写成select c1+c2 , t1.c3 from t1 为表加别名。如果表名太长，可以在select的from后面给表加一个别名，用起来很方便。例如： Select rownum, t20090563421.* from t20090563421 where t20090563421.c112 and t20090563421.c212 and mytab .c220,3.2 select语句中显示行号,Oracle有一个内部函数叫rownum，可以用来显示每一行的序号。 例如：select rownum ,* from t1 可以用rownum来提取表的前面的行数。 select * from t1 where rownum5，提取表t1的前4行。 如行实现提取表从m到n行的数据?,3.2 select语句的where子句,Where子句是指一串逻辑语句，这些逻辑语句最后得到的结果一定要是一个确定的值，这个值为true或false。 Where子句的逻辑语句有：and，or ,in ,not in,exists（非常有用），any，is null，is not null等。 Where子句后面可以跟模糊查询。,3.2 select语句的where子句,Where子句后面可以跟子查询（如果是等号，子查询只能返回一行结果，如果是in，则子查询可以返回多行结果）。 例如： select * from emp where deptno = ( select deptno from dept where dname =SALES ) 注意下面的语句： select * from emp where deptno in (select deptno from dept where dname=SALES or dname=CLERK ) 注意：子查询只能返回一列。,3.2 select语句的where子句关于NULL,关于NULL NULL是一种特殊的值，它表示不确定。 对NULL值的查询不可以用=， 等逻辑符号，只能用is null来判断某行中是否有NULL值 先创建一个表： create table t ( c1 int, c2 int ) 再向表中插入数据： Insert into t(c2) Values (2),3.2 select语句的where子句关于NULL,可以用下面的语句进行查询。 Select * from t where c1 is null 不能写成： Select * from t where c1 =null 注意：在创建表时，每个列的默认值为null。 如何为表的列指定不为NULL呢？看下面的例子： create table t ( c1 int not null, c2 int ),3.2 select语句的where子句关于NULL,在Oracle中，有一个函数叫decode，它的作用是来判断某个列的值，如果等于指定的值，就用指定的值来代替，否则，用其它指定的值来代替。 例如：如果c2中的值有为NULL ，则用10来代替这个值(并不会改变物理表中的值) select decode(c2,null,10) mycol,t.* from t 例如：如果c2中的值有为NULL ，则用10来代替这个值，如果不为NULL，用100来代替(并不会改变物理表中的值)。 select decode(c2,null,10,100) mycol,t.* from t,3.2 select语句的where子句,Where子句的模糊查询，模糊查询是指要查询的值并不完全知道，例如：要查询全部姓张的学生成绩，这就是一个模糊查询。 例如：查询以A开头的雇员信息 select * from emp where ename like A% 例如：查询以A开头，EN结尾的雇员信息 select * from emp where ename like A%EN 也可以用下划线“_”来代替一个字符以实现模糊查询。,3.3 Order by 子句,可以用Order by 来实现对查询的数据排序 想一下，是先做where子句还是先排序？ 按工资从低到高(升序，这是默的排序方式)对查询的数据排序 Select * from emp order by sal 按工资从高到低 (降序)对查询的数据排序 Select * from emp order by sal asc 可以用列的编号来指定排序的列(下面的例子是对emp表的第3列按降序排序)。 Select * from emp order by 3 asc,3.3 Order by 子句,注意order by的执行顺序，下面这个SQL执行的结果是怎么样的？ select rownum id ,empno ,ename from scott.emp order by ename,3.4 Oracle的聚合函数与排名函数,Oracle的基本聚合函数有：min(最小值)；max(最大值)；count(计算行数)；avg(计算平均值)；sum(计算总和) 例如：查询emp中sal列的最大值 select max(sal) from scott.emp; 例如： 聚合函数与其它列一起使用时，其它的列必须出现在group by 中。 例如下面的语句是错的： select ename, max(sal) from scott.emp 正确的做法是： select ename, max(sal) from scott.emp group by ename,3.5 Oracle的视图,视图是一张虚表。其本质是给一个select语句指定取一个名，即视图名(相当一个表名)，每次查询这个视图名就相当执行视图名所指向的select语句。创建视图的格式为： create view 视图名 as select 例如：create view v1 as select * from emp where ename like A%,3.5 视图的作用,1、视图能简化用户的操作 2、视图能使用户多角度看待同一数据 3、视图对重构数据库提供了一定程度的逻辑独立性。 4、视图对数据提供了很好保护。,3.6 oracle的排名函数,Oracle的排名函数极其复杂。这里只介绍两个排名函数：rank和row_number，基本用法：rank() over(partion by 列名 order by 列名)， row_number() over (partion by 列名 order by 列名)。 rank的作用：按partion by 后面的列分组，每一组按order by 后面的列排序，并对排序后每一组中进行编号(一组只有一个编号)。 row_number的作用：按partion by 后面的列分组，每一组按order by 后面的列排序，并对排序后每一组中各行进行编号(一组各行都有一个编号)。 注意：(1) rownum与row_number的区别? (2) 如何用row_number来提取m到n行的数据?,3.6 oracle的排名函数,排名函数的例子： (1) SELECT empid, qty, rank() over(order by qty) as rnk FROM Sales (2) select row_number()over(partition by deptno order by ename ) ,deptno,ename from scott.emp,3.7 主键,在关系数据库中，每一个实体在表中用一行来表示。但实体之间是不同的，例如：学生与学生之间是不一样的。如何在一行中反