SQL语言及TSQL的应用(上海电力学院).ppt

第三章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 VALUES(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列上取何值？ 插入后，在该记录的grade列上取空值。,二、插入子查询结果 子查询可以嵌套在INSERT语句中，用以生成要插入的批量数据。 INSERT INTO （,） 子查询；,例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 student group by Sdept;,可以不写这列属性吗？,不可以！,实例,3.4.2 删除数据 删除命令比较简单，删除是对记录操作，不能删除记录的部分属性。 一次可以删除一条和若干条记录，甚至将整个表的内容删空，只保留表的结构定义。删除命令格式为： DELETE FROM WHERE ；,例4、删除学号为20041020的学生记录。 if exists(select * from student where sno=20041020) delete from student where sno=20041020 例5、删除所有学生的选课信息。 DELETE FROM SC,实例,与例5功能等价的另一T-SQL语句： TRUNCATE TABLE SC 功能：删除表中的所有数据 优点：更快、使用系统和日志资源少。,带子查询的删除语句 例6、删除计算机系所有学生的选课记录。 DELETE FROM SC WHERE sno in (SELECT sno FROM Student WHERE sdept= 计算机系); 思考:采用相关子查询,子查询结果集为计算机系该如何实现？,实例,另一种正确方法如下： DELETE FROM SC WHERE 计算机系= (SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);,3.4.3 修改数据 修改数据的语句格式一般是： UPDATE SET = , = WHERE ;,例7、将学生20041020的系改为计算机系。 UPDATE Student SET Sdept = 计算机系 WHERE Sno= 20041020; 例8、将成绩60分以上（含60分）的学生的成绩乘上70%，再加上平时成绩28分。 UPDATE SC SET Grade=Grade*0.7+28 WHERE Grade=60,实例,带子查询的修改语句 例12、将数学系全体学生的成绩置零。 UPDATE SC SET Grade = 0 WHERE sno in (SELECT sno FROM Student. where sdept= 数学系); 注：对数据库进行更新操作要保证数据的一致性。,实例,3.5 视图,视图是关系数据库系统提供给以多种角度观察数据库中数据的重要机制。它就象一个窗口， 透过它可以看到数据库中用户感兴趣的数据及其变化。 3.5.1 视图的特点 视图是一种虚表，是逻辑表（物理上不存在的） 实际数据源于各基本表；其视图定义描述在数据字典中。 思考：基本表中的数据发生变化后，视图中的数据会变吗？ 基本表中的数据发生变化，从视图中查询出的数据也随之改变。,操作语句,back,视图可以屏蔽表中的某些信息，有利于数据库的安全性。 一个基本表可以建立多个视图，一个视图也可以在多个基本表或视图上利用查询结果建立。 拥有基本表的几乎所有操作，但有一定的限制条件。 有利于应用程序的独立性、数据一致性。,操作语句,back,使用视图的优点 1）查询的简单性：简化用户的操作。 2）安全保护：对机密数据提供了安全保护。 3）掩盖数据库的复杂性：使用户能以多中角度看待同一数据。 4）对重构数据库提供了一定程度的逻辑独立性。 使用视图的缺点 1）性能的降低：DBMS必须把对视图的查询转化成对基本表的查询。 2）修改的限制：对于复杂的视图，可能是不可修改的。,操作语句,back,3.5.2 视图的建立和撤销 1、视图的建立 建立视图的语句格式： CREATE VIEW (,) AS WITH CHECK OPTION; 其中查询子句可以是任意复杂的SELECT语句， 但通常不允许含有ORDER BY和DISTINCT短语。 WITH CHECK OPTION子句是为了防止用户通过视 图对数据进行增加、删除、修改时，对不属于视图 范围内的基本表数据进行误操作。加上该子句后， 当对视图上的数据进行增、删、改时，DBMS会检查 视图中定义子查询的条件表达式，若不满足，则拒 绝执行增、删、改。,back,注意：下列三种情况下须明确指定组成视图的所有列名。 （1）目标列是集函数或列表达式。 （2）多表连接时选出了几个同名列作为视图的字段。 （3）需要在视图中为某个列重命名（使名字更合适）。,back,注：查看视图定义的文本信息的存储过程： sp_helptext sss 等价于SELECT语句： select text from sysobjects s1,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建立所有计算机系学生的关于学号、姓名、出生年月、所在系信息视图.对该视图进行修改和插入操作时,保证仍是计算机系的学生的信息。 create view CS_student as select sno,sname,birthday,sdept from student where sdept=计算机系 with check option 注：with check option子句的作用！,视图特点,返回,下列语句可以成功执行： update cs_student set sname=李丽 where Sno= 20041001 下列语句不能成功执行： Update CS_student set Sdept= 信息系 where Sno= 20041001,运行结果:,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号课程且成绩在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 VEIW 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、查询视图 当视图被定义之后，就可以象对基本表一样对视图进行查询了。 例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= 计算机系,运行结果：select * from CS_student,SQL SERVER中修改视图中的数据会受哪些限制？ 1）无论是视图的创建、修改、删除，还是视图数据的查询、插入、更新、删除，都必须由具有权限的用户进行。 2）对由多个表连接成的视图修改数据时，不能同时影响一个以上的基本表，也不允许删除视图中的数据。 3）对视图上的某些列不能进行修改。如是计算值、系统函数和集函数。,视图特点,4）对具有NOT NULL的列进行修改时可能会出错。在通过视图修改或插入数据时，必须保证未显示的具有NOT NULL属性的列有值，可以是缺省、IDENTITY等，否则不能向视图中插入数据行。 5）如果某些列因为规则或者约束的限制而不能接受从视图插入数据的时候，则插入数据可能会失败。 6）删除基本表并不删除视图。建议采用与表明显不同的名字命名视图。,3.6数据控制 在数据库系统中实现安全性除了通过物理方法对数据库进行加密等方法外，主要是通过授予和检验权限的手段。SQL有授权语句，通过该语句可以实现对数据库的使用控制。 3.6.1授权 SQL语句通过GRANT语句向用户授予操作权限，GRANT语句的格式为： GRANT ， ON TO ， WITH GRANT OPTION; 语义：将某作用在指定操作对象上的操作权限，限授予指定的用户。（即数据库对象的访问权限的管理）,不同类型的操作对象有不同的操作权限，常见的操作权限如表所示。,知识点补充 SQLserver中 关于基本表、视图、存储过程、触发器等数据库对象操作的权限有：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子句，则获得某种权限的用户可以把这种权限在授予其他用户。如没有指定该子句，获得授权的用户将不能传播权限。(转授) 授权的用户可以是一个或多个具体用户，也可以是PUBLIC即全体用户。,例1、在数据库student中将创建基本表的权限授予用户dZW。(设用户已存在) 标准SQL语句： GRANT CREATTAB ON DATABASE student TO dZW; T-SQL中为： USE student GRANT CREATE TABLE TO dZW;,用户的创建方法,思考1：创建数据库的权限，应该在哪个数据库下授权？ 须用T-SQL语句： USE master GRANT CREATE DATABASE TO 用户名 思考2：在数据库student中将创建基本表、备份数据库的权限授予用户ZW。 T-SQL： USE student GRANT CREATE TABLE, BACKUP DATABASE TO 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 student GRANT UPDATE ON TABLE SC TO ZW WITH GRANT OPTION; T-SQL中为： GRANT UPDATE ON SC TO dZW WITH GRANT OPTION; 注：dZW获得该权限后，其可以在将此权限授予s2。 Use student GRANT UPDATE ON TABLE SC TO s2; T-SQL中为： GRANT UPDATE ON SC TO S2;,例4、把对Student表的全部操作权限授予用户 dZW和dbo。 标准SQL： GRANT ALL PRIVILEGS ON TABLE Student TO dZW,dbo; T-SQL中为： GRANT ALL ON Student TO dZW,dbo;,例5、把查询Student表和修改学生学号的权限授给用户dZW。 标准SQL： GRANT UPDATE(Sno),SELECT ON TABLE Student TO dZW; T-SQL中为： GRANT UPDATE(Sno),SELECT ON Student TO dZW;,3.6.2 回收权限 SQL语句通过REVOKE语句向用户授予操作权限，REVOKE语句的格式为： REVOKE ， ON FROM ，; 说明：当涉及多个用户传播权限时，收回上级用户某权限的同时也收回所有下级的该权限。,CASCADE当指定删除相应安全帐户的权限时，也将删除由这些安全帐户授权的任何其它安全帐户。,例6、将用户dzw查询student表的权限收回。 标准SQL： REVOKE SELECT ON TABLE student 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 CONSTRAINT （1）、利用企业管理器创建或添加各约束 （2）、使T-SQL语句,实例1：添加主键约束 use student Alter 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：添加唯一性约束 Alter 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 references 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 student 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(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)=1 print(id_num列为identity列!),-思考：如何借助局部变量， 获得st表第三列属性的列名。 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 表的所有行都插入到名为 employees 的新表。使用 IDENTITY 函数在 employees 表中从 100 而不是 1 开始编标识号。 IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = employees) DROP TABLE employees GO,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, pub_id, hire_date INTO employees FROM employee,检查当前标识值语句： DBCC CHECKIDENT (st) 例： 补空缺的标识 -delete from st where id_num=5 SET IDENTITY_insert st ON insert 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=100) -错： insert sc values(20041020,003,200) -有check约束。 注：添加的约束不能与原有的约束相矛盾。 alter table sc add constraint check_grade3 check(grade0)-无法实现,-禁止check约束，使一些不满足条件的数据能插入。 alter table sc nocheck constraint check_grade insert sc values(20041020,003,200) 思考：该数据能插入到sc表中吗： 能！,-重新启用某一check约束。 alter table sc check constraint check_grade 思考： insert sc values(20041020,004,200) 能插入？ 不能！,4、使用with nocheck，在更改添加约束时，对原数据不做检验。 下例向表中的现有列上添加约束。该列中存在一个违反约束的值；利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。 CREATE TABLE doc_exd ( column_a INT) GO INSERT INTO doc_exd VALUES (-1) GO ALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a 1),级联更新、级联删除 1、方法一：打开企业管理器-打开数据库-选中表-击右键-选设计表-选查看关系工具-选择相关的级联操作。 2、T-SQL语句：