数据库的视图(二).ppt

第4章 数据库的查询和视图,4.3 数据库视图,4.4 格式化输出结果,问题： 找出选修所有课程的学生？,没有一门课学生没有选修 不存在这样的课程c，学生没有选修 对要找的学生而言，没有他们不选的课程,Select * from xsb where not exists ( select * from kcb c Where not exists (select * from cjb where xsb.xh=xh and kch=c.kch);,问题：找出选修所有课程的学生？,Select * from xsb Where xh not in ( select s.xh From xsb s,kcb c Where(s.xh,c.kch) not in (select xh,kch from cjb) ) ;,select xh,kch from cjb返回所有有效的（学号，课程号）组合； 中间的子查询使用了学生表和课程表的笛卡尔积，除掉有效的（学号，课程号）组合，剩下虚构的（学号，课程号）组合； 最外层的查询从表中返回：学号不属于中间子查询返回的值,在子查询中,NOT IN子句将执行一个内部的排序和合并,无论哪种情况下,都低效. 因为要全表遍历.建议改成外连接或NOT EXISTS. 若NOT IN后面的列表小,还是可以使用NOT IN子查询的.,窗口函数的调用格式: count(*) over(). 对于查询返回的每一行，它返回了表中所有行的计数。 窗口函数都是最后一步执行，仅位于ORDERBY子句之前。,分区子句的结果是为每个学生执行count，同一分区（同个学生）的每门课程的个数相同。 分区的优点是同一个select中，一个窗口函数的计算独立于其他窗口函数的计算,Partition by :定义行的分区或组，以完成聚集计算。类似GROUPBY。,请问表示什么含义？,select xh,xm From ( select s.xh,s.xm, count(t.kch) over (partition by s.xh) as cnt, count(distinct c.kcm)over() as total, row_number() over (partition by s.xh order by c.kch) as rn from kcb c left join cjb t on(c.kch=t.kch) left join xsb s on(t.xh=s.xh) ) x where cnt=total and rn=1;,窗口函数：返回每个学生选取的课程数,count(distinct c.kch)over() as total：KCB表中总的课程数 若学生选了所有课，则他的CNT应该等于total。 ROW_NUMBER（）可以从最终结果集筛选掉重复而不需要使用DISTINCT。,select distinct xh,xm From ( select s.xh,s.xm, count(t.kch) over (partition by s.xh) as cnt, count(distinct c.kcm)over() as total from kcb c,cjb t,xsb s where (c.kch=t.kch) and (t.xh=s.xh) ) x where cnt=total;,视图是表中数据的逻辑表示。视图也可以说成是一段存储的SELECT 语句，它用于从数据库中检索视图要表示的数据。视图并不存放数据，数据真正存放在表中。它只是基表数据展现的窗口。 视图不使用物理存储位置存储数据，因而不能被索引。视图的定义储存在数据字典中。,4.3 数据库视图,视图一经定义以后，就可以像表一样被查询、修改、删除和更新。使用视图有下列优点。 （1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。 （2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。 （3）简化用户权限的管理，加强了表的安全管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，同时也增加了安全性。,Create view s_view as Select sname From amy.student Where sno10;,s_view视图的名字 amy基表的拥有者 select语句，用于从基表中取出数据。,4.3.2 创建视图,1使用OEM创建视图 启动OEM，在“方案”属性页中单击“视图”选项进入“视图搜索”页面，单击“创建”按钮，进入“视图创建”页面。,2在SQL Developer中创建视图 在SQL Developer中创建视图CS_XS的操作步骤如下。 启动SQL Developer，展开system_ora连接，右击“Views”节点选择“New View”菜单项，在Schema和Name栏输入方案和视图名，在“SQL Query”选项卡中输入创建视图的SQL语句,3使用CREATE VIEW语句创建视图 PL/SQL中用于创建视图的语句是CREATE OR REPLACE VIEW语句。 语法格式： CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name ( column_name ,n ) AS select_statement WITH CHECK OPTIONCONSTRAINT constraint_name WITH READ ONLY,视图可以非常复杂。在下列一些情况下，必须指定列的名称。 由算术表达式、系统内置函数或者常量得到的列。 共享同一个表名连接得到的列。 希望视图中的列名与基表中的列名不同的时候。,4.3.2 创建视图,【例4.49】 创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。 CREATE OR REPLACE VIEW CS_KC AS SELECT XSB.XH, KCH, CJ FROM XSB, CJB WHERE XSB.XH=CJB.XH AND ZY= 计算机 WITH CHECK OPTION;,【例4.50】 创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。 CREATE OR REPLACE VIEW CS_KC_AVG(num, score_avg) AS SELECT XH, AVG(CJ) FROM CJB GROUP BY XH;,如何得到创建的SQL语句呢？,4.3.3 查询视图,视图定义后，如同查询基表那样对视图进行查询。 【例4.52】 查找平均成绩在80分以上的学生的学号和平均成绩。 本例首先创建学生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。 CREATE OR REPLACE VIEW XS_KC_AVG ( num,score_avg ) AS SELECT XH, AVG(CJ) FROM CJB GROUP BY XH;,再对XS_KC_AVG视图进行查询。 SELECT * FROM XS_KC_AVG WHERE score_avg=80;,4.3.4 更新视图,1可更新视图 要通过视图更新基表数据，必须保证视图是可更新视图。可更新视图满足以下条件： （1）没有使用连接函数、集合运算函数和组函数； （2）创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、START WITH子句及DISTINCT关键字； （3）创建视图的SELECT语句中不包含从基表列通过计算所得的列； （4）创建视图没有包含只读属性。 例如，前面创建的视图CS_XS和CS_KC是可更新视图，而CS_KC_AVG是不可更新的视图。,【例4.53】 在XSCJ数据库中使用以下语句创建可更新视图CS_XS1。 CREATE OR REPLACE VIEW CS_XS1 AS SELECT * FROM XSB WHERE ZY= 通信工程;,CREATE OR REPLACE VIEW CS_KC_AVG(num, score_avg) AS SELECT XH, AVG(CJ) FROM CJB GROUP BY XH;不可更新的视图,2插入数据 使用INSERT语句通过视图向基本表插入数据。 【例4.54】 向CS_XS视图中插入一条记录：（101115，刘明仪，计算机，男，1984-3-2，50，三好学生） INSERT INTO CS_XS VALUES(101115, 刘明仪, 男,TO_DATE(19890302,YYYYMMDD), 计算机,50, 三好学生); 使用SELECT语句查询CS_XS依据的基本表XSB： SELECT * FROM XSB;,为什么不能插入数据呢？,Insert into cj_view values(006,89,201);,4.4 格式化输出结果,4.4.1 替换变量 1,为了在执行变量替换之前，显示如何执行替换的值，可以使用SET VERIFY命令。,图4.9 查询结果,替换变量不仅可以用在WHERE子句中，而且还可以用在下列情况中。 （1）ORDER BY子句。 （2）列表达式。 （3）表名。 （4）整个SELECT语句。,【例4.61】 查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。 SELECT XSB.XH, 执行过程及结果图如图4.10所示。,图4.10 查询结果,2,3DEFINE和ACCEPT命令 （1）DEFINE命令用来创建一个数据类型为CHAR用户定义的变量。相反的，使用UNDEFINE命令可以清除定义的变量。 语法格式： DEFINE variable=value 【例4.63】 定义一个变量specialty，并为它赋值“通信工程”。然后，显示该变量信息。 DEFINE specialty=通信工程 DEFINE specialty 显示结果为： DEFINE SPECIALTY = “通信工程“ (CHAR) 【例4.64】 查询专业为“通信工程”的学生情况，引用上例中定义的变量specialty。 SELECT XH, XM, XB, CSSJ, ZXF FROM XSB WHERE ZY= ,（2）使用ACCEPT命令可以定制一个用户提示，用来提示用户输入指定的数据。在使用ACCEPT定义变量时，可以明确地指定该变量是NUMBER数据类型还是DATE数据类型。为了安全性，还可以隐藏用户的输入。 语法格式： ACCEPT variable datatype FORMAT format PROMPT text HIDE 【例4.65】 使用ACCEPT定义一个变量num，且指定提示文本。根据这个变量的值查询选修该课程的学生学号、课程名和成绩情况。 ACCEPT num PROMPT 请输入课程号：; Select * from cjb where kch=,4.4.2 定制SQL*Plus环境,1页和行的大小 命令SET LINESIZE指定页宽是多少，最常用的设置为80和132。例如，设置行宽为50，设置页的长度为30。 SET PAGESIZE命令指定页的长度是多少，常用设置为55和60。为了更容易地看到分页，可以使用下例所示的设置，把页长指定为30。 命令如下： SET LINESIZE 50 SET PAGESIZE 30,2格式化输出表列 使用COLUMN命令可以格式化实际的表列数据，以满足用户的不同需求。例如，下列命令设置表XSB的XH和XM列的格式。 COLUMN XH FORMAT a8 WRAP HEADING 学号 COLUMN XM FORMAT a8 HEADING 姓名 按照上面介绍的方法，设置指定页头标、页脚标等，然后执行如下语句： SELECT XH, XM, XB, CSSJ, ZY FROM XSB WHERE ZXF=50;,create table test ( id int identity(1,1), name varchar(6) ),请解释identity含义！,在Oracle中，使用序列实现字段的自增。,序列,序列（Sequence)又叫序列生成器，它用于提供一系列的数字。可以使用序列生成器生成唯一键。 序列的定义储存在SYSTEM表空间中。不占磁盘空间。,创建序列,利用SQL命令创建序列 语法格式： CREATE SEQUENCE schema. sequence_name/*将要创建的序列名称*/ INCREMENT BY integer /*递增或递减值*/ START WITH integer /*初始值*/ MAXVALUE integer | NOMAXVALUE /*最大值*/ MINVALUE integer | NOMINVALUE /*最小值*/ CYCLE | NOCYCLE /*序列到达最大值是否循环*/ CACHE integer | NOCACHE /*高速缓冲区设置：缓存多少值在内存*/ ORDER | NOORDER /*序列号是否按照顺序生成*/,创建一个降序序列。 CREATE SEQUENCE S_TEST INCREMENT BY -2 START WITH 4500 MAXVALUE 4500 MINVALUE 1 CYCLE CACHE 20 NOORDER;,序列的使用,得到序列的新值： S.NEXTVAL 例如，设置一条SQL语句，作用是向表USERS中插入一个新的记录： INSERT INTO USERS (USER_ID, USER_NAME) values(S.NEXTVAL , test);,.获取当前的序列值 USER_S.currval 例如，设置一条SQL语句，作用是向表USERS中插入一个新的记录 INSERT INTO USERS (USER_ID, USER_NAME) VALUES(USER_S.currval , test);,.查看当前的序列值： select USER_S1.currval from dual;,.修改序列参数 创建序列之后可以修改以调整参数。 ALTER SEQUENCE USER_S increment by 10 maxvalue 2000 cycle nocache;,要改变序列的起始值start，需要先删除序列，在重新创建。 drop sequence USER_S;,查询序列 序列定义在USER_SEQUENCE数据字典中，可以通过数据字典查询序列结构。 desc user_sequences; select * from user_sequences;,序列生成器与任何一个表都是无关的，一旦创建，每个表都可以引用，每引用一次，序列值就会改变一次，对于多用户对同一个表的插入操作非常有效，每个用户引用的序号