GO
Select * Into sc1 from sc --复制表 use ST
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'sc2' AND type = 'U') DROP TABLE sc2 GO
Select * into sc2 from sc --复制表结构 Where not exists (select * from sc)
运行结果:
(6 行受影响)
(0 行受影响)
--[3_46] 合并表 ⑴先向表sc2输入数据 ⑵再将sc2合并到sc1中。insert into sc2 values('201313103','2',92); insert into sc2 values('201313102','5',75); insert into sc2 values('201313104','3',68); insert into sc2 values('201313102','7',80); insert into sc2 values('201313101','6',47); select * from sc1 union all select * from sc2
运行结果:
sno cno grade --------- ---- ------------------------- 201313101 1 92.0 201313101 2 35.0 201313101 3 88.0 201313102 2 90.0 201313102 3 50.0 201313103 2 92.0 201313102 5 75.0 201313104 3 68.0 201313102 7 80.0 201313101 6 47.0
--[3_47] 将信息系所有学生的年龄增加1岁。
UPDATE student
16
SET sage=sage+1 where sdept='IS';
运行结果: (1 行受影响)
--[3_48] 对计算机科学系(cs)全体学生选修'2'号课程分数>=36的按10*sqrt(grade)计算。
update sc
set grade=10*sqrt(grade) where cno='2' and grade>=36 and 'cs'=(select sdept from student
where student.sno=sc.sno) --相关子查询
运行结果: (1 行受影响)
--[3_49] 删除计算机科学系所有学生的选课记录。
DELETE FROM sc
WHERE 'cs'=(SELECT sdept FROM student
WHERE student.sno=sc.sno);
运行结果: (5 行受影响)
--[3_50] 删除未选修课程的学生。
use ST
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'student1' AND type = 'U') DROP TABLE student1 GO
Select * Into student1 from student --先复制表 --删除student1中未选修课程的学生 DELETE FROM student1 WHERE sno NOT IN (SELECT sno FROM sc)
运行结果:
(4 行受影响)
(3 行受影响)
--[3_51] 删除只选1门课且成绩不及格学生的选课记录。
17
DELETE FROM sc WHERE sno in (SELECT sno FROM sc
WHERE grade<60
GROUP BY sno HAVING COUNT(*)=1)
运行结果: (0 行受影响)
--[3_52] 删除计算机系只选1门课且成绩不及格学生的选课记录。
DELETE FROM sc WHERE sno IN (SELECT sno FROM student
WHERE sdept='cs' AND sno IN (SELECT sno FROM sc
WHERE grade<60 AND student.sno=sc.sno GROUP BY sno HAVING COUNT(*)=1))
运行结果: (0 行受影响)
(二) 自定内容
五、出现的问题及解决方法
实验4 索引与视图
完成以下实验报告:
《数据库原理与应用》实验报告
院名 学号 专业 姓名 年级 教师 班级 成绩 一、实验题目 实验4 索引与视图 二、实验环境
操作系统:Windows XP。
数据库管理系统:MS SQL Server 2000 或MS SQL Server 2008。 三、实验目的
1.理解索引的目的、维护与使用,唯一与非唯一索引、聚簇与非聚簇索引、单索引与复合索引的区别。
2.掌握建立与删除索引的方法。 3.掌握建立与删除视图的方法。
4.掌握对视图表进行查询与更新的操作方法。 四、实验内容
18
(一) 规定内容
1. 建立与删除索引
--[4_1] 查询姓“李”的学生,为其建立相应的索引。
if exists (select name from sysindexes where name='index_sname') drop index student.index_sname create index index_sname on student(sname)
--[4_2] 查询年龄20岁的女生,为其建立相应的索引。
if exists (select name from sysindexes where name='index_sageSex ') drop index student.index_sageSex
create index index_sageSex ON student(sage,ssex DESC)
--[4_3] 建立“教工(教工号,姓名,性别)”表结构,按“教工号”为该表建立聚簇索引,按“姓名”、“性别”为该表建立唯一值复合索引。
if exists (select name from sysobjects where name='教工') drop table 教工 create table 教工 (教工号 char(5), 姓名 char(8), 性别 char(2) )
create clustered index index_tno on 教工(教工号)
create unique index index_TnameSex on 教工(姓名, 性别) --[4_4] 删除“教工”表上建立的唯一值复合索引。 drop index 教工.index_TnameSex
--[4_5] (2004秋数据库系统工程师下午试题)
设有如下关系模式: 职工(职工号,姓名,年龄,月工资,部门号,电话,办公室) 假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引,如下的Select查询语句可能不会促使查询优化器使用索引,从而降低查询效率,请写出既可以完成相同功能又可以提高查询效率的SQL语句。
Select 姓名,年龄,月工资 from 职工 Where 年龄>45 or 月工资<1000;
解:
Select 姓名,年龄,月工资 from 职工
Where 年龄>45; UNION
Select 姓名,年龄,月工资 from 职工
Where 月工资<1000;
2. 建立与删除视图
--[4_6] 建立信息系学生视图,含学号、姓名、姓别等列。 use ST
IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is') drop view V_is --若V_is已存在,则删除 go
CREATE VIEW V_is
19
AS
SELECT sno,sname,ssex --行列子集视图 FROM student WHERE sdept='is'
--[4_7] 建立信息系学生的视图,并要求透过该视图进行的更新操作只涉及信息系学生。
use ST
IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is1') drop view V_is1 --若V_is已存在,则删除 go
CREATE VIEW V_is1 AS
SELECT *
FROM student WHERE sdept='is'
WITH CHECK OPTION --WITH CHECK OPTION的视图 --[4_8] 建立信息系选修了1号课程的学生视图。
IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is2') drop view V_is2 --若V_is已存在,则删除 go
CREATE VIEW V_is2(sno,sname,cno,grade) AS
SELECT student.sno,sname,cno,grade
FROM student,sc --基于多个基表的视图 WHERE student.sno=sc.sno AND sdept='is' AND sc.cno='1'
--[4_9] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is3') drop view V_is3 --若V_is已存在,则删除 go
CREATE VIEW V_is3 AS
SELECT sno,sname,grade
FROM V_is2 --基于视图的视图 WHERE grade>=90
--[4_10] 先建立高考(考号,姓名,语文,数学,英语,综合)基本表, 在此基础上建立含总分列的视图。
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'GK') drop table GK --若GK已存在,则删除 go
create table GK
(考号 char(5) primary key, 姓名 char(8),
语文 decimal(5,1) check(语文 between 0 and 150), 数学 decimal(5,1) check(数学 between 0 and 150),
20