《SQL语言及TSQL的应用(上海电力学院).ppt》由会员分享，可在线阅读，更多相关《SQL语言及TSQL的应用(上海电力学院).ppt（75页珍藏版）》请在三一办公上搜索。
1、第三章SQL语言及T-SQL的应用(3),实例分析:,Student,Course,SC,返回,3.4数据操纵语言,SQL中数据更新包括插入数据、修改数据和删除数据三条语句。3.4.1 插入数据INSERT语句两种形式：（1）插入一个元组。（2）插入子查询结果。一、插入单个元组INSERT INTO(,)VALUES(,)；注：INTO子句中没有出现的属性列，新记录在这些列上将取空值。,例1、向学生基本情况表（student）中插入一条记录(学号：20041020；姓名：陈林；性别：取默认值男；出生年月：1978-01-11;所在系：CS；)。INSERT INTO student VALUE
2、S(20041020,陈林,default,1978-01-11,CS)思考:下例执行语句正确吗?INSERT INTO student VALUES(20041020,陈林,1978-01-11,CS)不正确!,实例,另一正确语句:INSERT INTO student(sno,sname,birthday,sdept)VALUES(20041020,陈林,1978-01-11,MA)例2、插入一条选课记录,学号:20041020,课程号:001。INSERT into SC(Sno,Cno)VALUES(20041020,001);思考：插入后，grade列上取何值？插入后，在该记录的gr
3、ade列上取空值。,二、插入子查询结果 子查询可以嵌套在INSERT语句中，用以生成要插入的批量数据。INSERTINTO（,）子查询；,例3、对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：在数据库中创建一个新表，关系模式为Dep(Sdept,Avgage)。Create table Dep(Sdept CHAR(2),Avgage SMALLINT);第二步：对Dep插入对student表按系组求平均年龄的查询结果值。INSERT INTO Dep(Sdept,Avgage)select Sdept,AVG(year(getdate()-year(birthday)from st
4、udentgroup by Sdept;,可以不写这列属性吗？,不可以！,实例,3.4.2 删除数据 删除命令比较简单，删除是对记录操作，不能删除记录的部分属性。一次可以删除一条和若干条记录，甚至将整个表的内容删空，只保留表的结构定义。删除命令格式为：DELETE FROM WHERE；,例4、删除学号为20041020的学生记录。if exists(select*from student where sno=20041020)delete from studentwhere sno=20041020例5、删除所有学生的选课信息。DELETE FROM SC,实例,与例5功能等价的另一T-SQ
5、L语句：TRUNCATE TABLE SC功能：删除表中的所有数据 优点：更快、使用系统和日志资源少。,带子查询的删除语句例6、删除计算机系所有学生的选课记录。DELETEFROM SCWHERE sno in(SELECT sno FROM Student WHERE sdept=计算机系);思考:采用相关子查询,子查询结果集为计算机系该如何实现？,实例,另一种正确方法如下：DELETEFROM SCWHERE 计算机系=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);,3.4.3 修改数据修改数据的语句格式一般是：UPDATE SE
6、T=,=WHERE;,例7、将学生20041020的系改为计算机系。UPDATE Student SET Sdept=计算机系WHERE Sno=20041020;例8、将成绩60分以上（含60分）的学生的成绩乘上70%，再加上平时成绩28分。UPDATE SCSET Grade=Grade*0.7+28WHERE Grade=60,实例,带子查询的修改语句例12、将数学系全体学生的成绩置零。UPDATE SC SET Grade=0WHERE sno in(SELECT sno FROM Student.where sdept=数学系);注：对数据库进行更新操作要保证数据的一致性。,实例,3
7、.5 视图,视图是关系数据库系统提供给以多种角度观察数据库中数据的重要机制。它就象一个窗口，透过它可以看到数据库中用户感兴趣的数据及其变化。3.5.1 视图的特点视图是一种虚表，是逻辑表（物理上不存在的）实际数据源于各基本表；其视图定义描述在数据字典中。思考：基本表中的数据发生变化后，视图中的数据会变吗？基本表中的数据发生变化，从视图中查询出的数据也随之改变。,操作语句,back,视图可以屏蔽表中的某些信息，有利于数据库的安全性。一个基本表可以建立多个视图，一个视图也可以在多个基本表或视图上利用查询结果建立。拥有基本表的几乎所有操作，但有一定的限制条件。有利于应用程序的独立性、数据一致性。,操
8、作语句,back,使用视图的优点1）查询的简单性：简化用户的操作。2）安全保护：对机密数据提供了安全保护。3）掩盖数据库的复杂性：使用户能以多中角度看待同一数据。4）对重构数据库提供了一定程度的逻辑独立性。使用视图的缺点1）性能的降低：DBMS必须把对视图的查询转化成对基本表的查询。2）修改的限制：对于复杂的视图，可能是不可修改的。,操作语句,back,3.5.2 视图的建立和撤销1、视图的建立建立视图的语句格式：CREATE VIEW(,)AS WITH CHECK OPTION;其中查询子句可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY和DISTINCT短语。WITH
9、CHECK OPTION子句是为了防止用户通过视图对数据进行增加、删除、修改时，对不属于视图范围内的基本表数据进行误操作。加上该子句后，当对视图上的数据进行增、删、改时，DBMS会检查视图中定义子查询的条件表达式，若不满足，则拒绝执行增、删、改。,back,注意：下列三种情况下须明确指定组成视图的所有列名。（1）目标列是集函数或列表达式。（2）多表连接时选出了几个同名列作为视图的字段。（3）需要在视图中为某个列重命名（使名字更合适）。,back,注：查看视图定义的文本信息的存储过程：sp_helptext sss等价于SELECT语句：select text from sysobjects s
10、1,syscomments s2 where name=sssand s1.id=s2.id,例1建立所有计算机系学生的关于学号、姓名、出生年月、所在系信息视图。Create view CS_student as select Sno,Sname,birthday,sdept from Student Where Sdept=计算机系 1）问题1：请问CS_student的列名有哪些？2）DBMS执行CREATE VIEW语句的结果只是把视图的定义存入数据字典中，并不执行其中SELECT语句。在对视图查询时，才按视图的定义从基表中将数据查出。,视图特点,例2建立所有计算机系学生的关于学号、姓名
11、、出生年月、所在系信息视图.对该视图进行修改和插入操作时,保证仍是计算机系的学生的信息。create view CS_studentasselect sno,sname,birthday,sdeptfrom studentwhere sdept=计算机系with check option注：with check option子句的作用！,视图特点,返回,下列语句可以成功执行：update cs_student set sname=李丽 where Sno=20041001下列语句不能成功执行：Update CS_student set Sdept=信息系 where Sno=20041001,
12、运行结果:,update cs_student set sname=李五 where Sno=20071002思考:能运行成功吗?不能!,建立在多个基本表上的视图：例3建立计算机系选修了00号课程的学生的关于学号、姓名、成绩的视图。CREATE view CS_S2(Sno,Sname,Grade)AS SELECT Student.Sno,Sname,Grade FROM Student,SC WHERE Sdept=计算机系 and Student.Sno=SC.Sno and SC.Cno=002,视图特点,建立在一个或多个已定义好的视图上的视图：例4建立计算机科学系选修了002号课程且
13、成绩在90分以上的学生（学号、姓名、成绩）的视图。CREATE VEIW CS_S3 AS SELECT Sno,Sname,Grade FROM CS_S2 WHERE Grade=90,带表达式的视图：带虚拟列的视图。例5建立一个反映学生年龄的视图，包含学号、姓名、年龄信息。CREATE VEIW S_birthday(Sno,Sname,Sage)AS SELECT Sno,Sname,year(getdate()-year(birthday)FROM Student,分组视图：带有集函数GROUP BY子句的查询来定义的视图。例6将学生的学号及其平均成绩定义为一个视图。CREATE V
14、EIW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno,思考：这个视图可作修改吗？,注：若视图是由子查询“SELECT*”建立的。如果原基本表的结构被修改后，则该视图与原基本表的映象关系被破坏，须修改基本表后删除原生成的视图，重建这些视图。、删除视图删除视图语句格式：DROP VIEW;例7删除上例建立的视图CS_student。DROP VIEW CS_student;注：删除视图后，由该视图导出的视图并未从数据字典中删除，但已无效，须同时也删除这些视图。,3.5.3 视图数据操作1、查询视图当视图被定义之后，就可以象对基本
15、表一样对视图进行查询了。例8查询S_birthday视图中年龄小于20岁的学生。Select*from S_birthday where Sage20;,2、更新视图由于视图是不实际存储数据的虚表，因此对视图的更新，最终是通过转换为对基本表的更新进行的。例9将计算机系名叫李丽同学的出生年月改为1987-01-01;。UPDATE CS_student SET birthday=1987-01-01 WHERE Sname=李丽;,返回,实际操作为：UPDATE student SET birthday=1987-01-01 WHERE Sname=李丽 and sdept=计算机系,运行结果：
16、select*from CS_student,SQL SERVER中修改视图中的数据会受哪些限制？1）无论是视图的创建、修改、删除，还是视图数据的查询、插入、更新、删除，都必须由具有权限的用户进行。2）对由多个表连接成的视图修改数据时，不能同时影响一个以上的基本表，也不允许删除视图中的数据。3）对视图上的某些列不能进行修改。如是计算值、系统函数和集函数。,视图特点,4）对具有NOT NULL的列进行修改时可能会出错。在通过视图修改或插入数据时，必须保证未显示的具有NOT NULL属性的列有值，可以是缺省、IDENTITY等，否则不能向视图中插入数据行。5）如果某些列因为规则或者约束的限制而不能
17、接受从视图插入数据的时候，则插入数据可能会失败。6）删除基本表并不删除视图。建议采用与表明显不同的名字命名视图。,3.6数据控制 在数据库系统中实现安全性除了通过物理方法对数据库进行加密等方法外，主要是通过授予和检验权限的手段。SQL有授权语句，通过该语句可以实现对数据库的使用控制。授权 SQL语句通过GRANT语句向用户授予操作权限，GRANT语句的格式为：GRANT，ON TO，WITH GRANT OPTION;语义：将某作用在指定操作对象上的操作权限，限授予指定的用户。（即数据库对象的访问权限的管理）,不同类型的操作对象有不同的操作权限，常见的操作权限如表所示。,知识点补充SQLser
18、ver中关于基本表、视图、存储过程、触发器等数据库对象操作的权限有：SELECT、INSERT、UPDATE、DELETE、EXECUTE等。关于数据库对象定义的权限有：BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW。,“红色”部分也可用于属性列上。,如果指定WITH GRANT OPTION子句，则获得某种权限的用户可以把这种权限在授予其他用户。如没有指定该子句，获得授权的用户将不能传播
19、权限。(转授)授权的用户可以是一个或多个具体用户，也可以是PUBLIC即全体用户。,例1、在数据库student中将创建基本表的权限授予用户dZW。(设用户已存在)标准SQL语句：GRANT CREATTAB ON DATABASE student TO dZW;T-SQL中为：USE studentGRANT CREATE TABLETO dZW;,用户的创建方法,思考1：创建数据库的权限，应该在哪个数据库下授权？须用T-SQL语句：USE masterGRANT CREATE DATABASE TO 用户名思考2：在数据库student中将创建基本表、备份数据库的权限授予用户ZW。T-SQ
20、L：USE studentGRANT CREATE TABLE,BACKUP DATABASETO dZW;,例2、将查询Student表的权限授予全体用户。标准SQL为：GRANT SELECT ON TABLE Student TO public;T-SQL中为：grant select on student to public,例3、将在SC表上进行UPDATE的权限授予用户dZW，并允许他传播该权限。标准SQL中：Use studentGRANT UPDATE ON TABLE SC TO ZW WITH GRANT OPTION;T-SQL中为：GRANT UPDATE ON SC
21、TO dZW WITH GRANT OPTION;注：dZW获得该权限后，其可以在将此权限授予s2。Use studentGRANT UPDATE ON TABLE SC TO s2;T-SQL中为：GRANT UPDATEON SC TO S2;,例4、把对Student表的全部操作权限授予用户 dZW和dbo。标准SQL：GRANT ALL PRIVILEGSON TABLE Student TO dZW,dbo;T-SQL中为：GRANT ALLON Student TO dZW,dbo;,例5、把查询Student表和修改学生学号的权限授给用户dZW。标准SQL：GRANT UPDAT
22、E(Sno),SELECTON TABLE Student TO dZW;T-SQL中为：GRANT UPDATE(Sno),SELECTON Student TO dZW;,3.6.2 回收权限SQL语句通过REVOKE语句向用户授予操作权限，REVOKE语句的格式为：REVOKE，ON FROM，;说明：当涉及多个用户传播权限时，收回上级用户某权限的同时也收回所有下级的该权限。,CASCADE当指定删除相应安全帐户的权限时，也将删除由这些安全帐户授权的任何其它安全帐户。,例6、将用户dzw查询student表的权限收回。标准SQL：REVOKE SELECT ON TABLE studen
23、t FROM dzw;T-SQL:REVOKE SELECT ON student FROM dZW CASCADE,例7、将用户dZW更新SC表的权限收回，同时s2的更新权也被收回。（若 s2由dZW 授权的）T-SQL:Revoke update on sc from dZW cascade;例8、收回授予ZW的数据表创建权限。T-SQL：revoke create table from dzw,数据完整性实现知识点回顾与补充(实验四参用）约束有6种类型：非空约束、默认值约束、Check约束、主键约束、外键约束、唯一性约束。1、添加约束命令基本格式：ALTER TABLE ADD CONS
24、TRAINT（1）、利用企业管理器创建或添加各约束（2）、使T-SQL语句,实例1：添加主键约束use studentAlter table student add constraint PK_student primary key(sno),-创建基本表时也可定义约束Create table student(Sno char(6)constraint PK_student primary key,.),-实例2：添加check约束Alter table student add constraint CK_student check(ssex=男 or ssex=女)-实例3：添加唯一性约束A
25、lter table student add constraint UN_student unique(sname)回顾：删除该约束？alter table student drop constraint UN_student,-实例4:添加外键约束Alter table sc add constraint FK_sc_student foreign key(sno)references student(sno)回顾：创建时给该属性列添加外键约束的方法？Create table SC(sno char(8)constraint FK_sc_student foreign key referen
26、ces student(20),),-实例5：添加默认值约束 alter table student add constraint DF_ssex default 男for ssex 回顾：创建时添加默认值约束 create table student(,ssex char(2)constraint DF_ssex default 男,),-实例6:添加具有默认值的可为空的列-下例添加可为空的、具有 DEFAULT 定义的列，-并使用 WITH VALUES 为表中的各现有行提供值。-如果没有使用 WITH VALUES，那么每一行的新列中都将具有 NULL 值。ALTER TABLE stu
27、dent ADD income smalldatetime NULL constraint DF_income DEFAULT getdate()with values,-例7、添加非空约束-在student表中，对sname添加非空约束（原有请删除）注：只能用alter column 来实现，且在修改列时不能添置其它约束，可参见帮助中的语法格式。alter table student alter column sname varchar(10)not null思考：想将该列设置为允许为空，如何实现？alter table student alter column sname varchar(
28、10)null,2、自动增长列的设置（identity实例）：create table st(id_NUM int identity(1,2),sname varchar(20),fname char(2)思考：如何插入数据？,-元数据函数（数据对象的信息）IF objectproperty(object_ID(st),TableHasIdentity)=1 print(该表使用了identity列！)if columnproperty(object_ID(st),ID_NUM,IsIdentity)=1print(id_num列为identity列!),-思考：如何借助局部变量，获得st表第
29、三列属性的列名。declare zy varchar(10)select zy=col_name(object_id(st),3)select zy as 第3列列名select IDENTITY-返回最后插入的标识值。,-例：给st表中的fname列添加默认值约束，-值为女；同时添加check约束，-使仅能取男、女。alter table st add constraint default_fname default(男)for fname,constraint check_fname check(fname=男or fname=女),-下面的示例将来自 pubs 数据库中 employee
30、 表的所有行都插入到名为 employees 的新表。使用 IDENTITY 函数在 employees 表中从 100 而不是 1 开始编标识号。IF EXISTS(SELECT*FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=employees)DROP TABLE employeesGO,SELECT IDENTITY(smallint,100,1)AS job_num,emp_id AS emp_num,fname AS first,minit AS middle,lname AS last,job_lvl AS job_level,pu
31、b_id,hire_dateINTO employees FROM employee,检查当前标识值语句：DBCC CHECKIDENT(st)例：补空缺的标识-delete from st where id_num=5SET IDENTITY_insert st ONinsert into st(id_num,sname,fname)values(5,xx,default),3、禁止及启用check约束。-例：对sc表的成绩列添加check约束，满足百分制。alter table sc add constraint check_grade check(grade=0 and grade=10
32、0)-错：insert sc values(20041020,003,200)-有check约束。注：添加的约束不能与原有的约束相矛盾。alter table sc add constraint check_grade3 check(grade0)-无法实现,-禁止check约束，使一些不满足条件的数据能插入。alter table sc nocheck constraint check_gradeinsert sc values(20041020,003,200)思考：该数据能插入到sc表中吗：能！,-重新启用某一check约束。alter table sc check constraint
33、 check_grade思考：insert sc values(20041020,004,200)能插入？不能！,4、使用with nocheck，在更改添加约束时，对原数据不做检验。下例向表中的现有列上添加约束。该列中存在一个违反约束的值；利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。CREATE TABLE doc_exd(column_a INT)GOINSERT INTO doc_exd VALUES(-1)GOALTER TABLE doc_exd WITH NOCHECKADD CONSTRAINT exd_check CHECK(column_a
34、1),级联更新、级联删除1、方法一：打开企业管理器-打开数据库-选中表-击右键-选设计表-选查看关系工具-选择相关的级联操作。2、T-SQL语句：alter table sc add constraint fk_sno foreign key(sno)references student(sno)on delete cascade on update cascade注：操作前要注意,并未在企业管理器中设置过,否则会导致循环或多重级联错误。思考：可以只做一种级联操作吗？可以。如：上例，仅实现级联删除，则为：,通用默认值的创建与实施方法：一、T-SQL语句：（1）为数据库（如student）创建一
35、个名为sex_default,值为男的默认值。Create default sex_default as 男（2）将默认值sex_default绑定到表student的ssex列。Sp_bindefault sex_default,student.ssex,（3）解除表student的ssex列的默认值绑定。Sp_unbindefault student.ssex（4）删除默认值sex_default。Drop default sex_default二、使用企业管理器实施默认值（1）启动企业管理器，选中相应的数据库（如student）。（2）击右键，选“新建”-“默认值”，弹出创建默认值窗口。
36、创建完毕。,（3）绑定方法：启动企业管理器-选中要操作的数据库-单击“默认”按扭，单击右边窗格里的所需绑定的默认值，在弹出快捷菜单中选“属性”。（4）单击“绑定列”，在绑定默认值窗口中选定表、相应的绑定列，按“添加”按扭即可。（5）删除操作同绑定方法类似，在绑定列窗口中选定后，按“删除”按扭即可。（6）删除默认值：启动企业管理器-单击“默认”按扭-在右边窗格中选中相应默认值，击右键，按“删除”即可。,规则（也是数据库对象之一）的创建与实施方法：一、T-SQL语句：（1）为数据库（如Student）创建一个名为grade_rule,值要求大于等0，小于等于是100的规则。Create rule grade_rule as grade=0 AND grade=100（2）规则grade_rule绑定到表sc的grade列。Sp_bindrule grade_rule,sc.grade,（3）解除表sc的grade列的规则绑定。Sp_unbindrule sc.grade（4）删除规则grade_rule。Drop rule grade_rule二、使用企业管理器实施规则方法类似实施默认值。说明：一个规则可以是：值的清单或值的集合、值的范围、可以用like子句定义的编辑掩码。,自定义数据类型T-SQL预定义函数,练习：第三章课后习题5:(8)-(11)作业,ok！,
