泰 州 职 业 技 术 学 院 数 据 库 技 术 实 验 指 导 书 （第一版） 蔡伯峰 编著 电 子 工 程 系 计 算 机 专 业 教 研 室 二 O O 四 年 八 月 泰 州 职 业 技 术 学 院 数据库技术实验指导书 （第一版） 蔡 伯 峰 编 tzcbf@163.com 电 子 工 程 系 计 算 机 专 业 教 研 室 二 O O 四 年 八 月 目录 实验1： SQL Server 2000关系数据库系统 1 实验2： SQL Server 2000的配置与使用 4 实验3： SQL Server 2000的系统和数据管理1 7 实验4： SQL Server 2000的系统和数据管理2 10 实验5～6： 数据表的创建与维护（分2次） 14 实验7～9：数据库中数据表的操作（SQL查询） (分3次) 19 实验10：数据类型操作 31 实验11：Transact-SQL变量、运算符与函数 33 实验12：Transact-SQL程序设计 36 实验13：索引和视图 39 实验14：事务处理、存储过程 42 实验15：触发器与数据库安全 44 实验16：数据完整性 47 实验1： SQL Server 2000关系数据库系统 一、实验目的 掌握关系型数据库的基本概念； 能用ACCESS进行基本数据库操作； 了解安装SQL Server 2000所需的软硬件要求； 能熟练安装SQL Server 2000。 二、实验内容和步骤 1、ACCESS数据库相关知识 一个Access类型的数据库就是由一个文件构成的，其扩展名为mdb。文件名就是该数据库的名称，数据库中每一个表也必须有一个名称。 表中同一字段的数据由相同类型的值构成，代表相同的意义，如姓名字段，类型为文本（或字符串）。 2、利用Access建立一个数据库，并在数据库中创建表 （1）创建3个表： 学生表（姓名、学号、性别、出生日期） 课程表（课程名、课程号、学分） 成绩表（学号、课程号、成绩）。 （2）自行输入多条记录。 （3）在数据库中创建3个窗体，分别用于输入学生信息、课程信息、成绩信息。 （4）在数据库中创建查询，显示所有男生信息、所有课程信息、某一课程的所有成绩。 3、SQL Server 2000安装前的注意事项 确保计算机满足SQLServer2000的软硬件要求； 在Win NT/2000中安装还需要一个或多个域用户帐号，以便SQLServer2000与其它客户端、服务器端进行通信； 以本地管理员权限的用户帐号登录系统(安装后可添加‘新登录’用户)； 关闭与SQLServer2000有关的所有服务，包括ODBC（开放数据库互连）的服务。 4、安装、运行SQL Server 2000的硬件环境要求 计算机 Intel及其兼容机，Pentium 166Mz或者更高处理器或DEC Alpha和其兼容系统。 内存（RAM） 最少64MB内存（个人版：Windows 98上最少需要32MB内存）。 硬盘空间 完全安装：180MB，典型安装：170MB，最小安装：65MB 客户端工具：90MB，Analysis Services：50MB，English Query：80MB 建议预留500MB的程序空间、500MB的数据空间 显示器 800X600及以上分辨率才能使用图形工具 5、安装、运行SQL Server 2000的软件环境要求 SQL Server 2000企业版：必须运行于安装Windows NT Server Enterprise Edition 4.0或者Windows2000 Advanced Server以及更高版本的操作系统下。 SQL Server 2000标准版：必须运行于安装Windows NT Server Enterprise Edition 4.0、Windows NT Server 4.0、Windows 2000 Server以及更高版本的操作系统下。 SQL Server 2000 个人版：可在多种操作系统下运行，如可运行于Windows 9x， Windows NT 4.0或Windows 2000的服务器版或工作站版的操作系统下。 SQL Server 2000 开发版：可运行于上述Windows 9x以外的所有操作系统下。 Internet软件：IE5.0以上。 IIS（Internet Information Services）：Internet信息服务，用于编写XML程序。 6、SQL Server2000的安装路径 共享文件路径：不允许改变其安装路径，被安装在系统盘的Program Files\Microsoft SQL Server\80文件夹中。 程序文件和数据文件的默认实例路径：分别被安装在系统盘的Program Files\Microsoft SQL Server\MSSql\binn及data文件夹中。可以改变其安装路径。 程序文件：指可执行文件等。 数据文件：指数据库文件、日志文件、以及系统日志、备份数据和复制数据的目录。 程序文件和数据文件的命名实例路径：分别被安装在系统盘的Program Files\Microsoft SQL Server\MSSql$InstanceName\binn及data文件夹中。可以改变其安装路径。 7、安装中的一些概念 安装 Analysis Service：包含联机分析处理（OLAP）和功能强大的数据挖掘。 安装 English Query：安装后用户可以创建应用程序，直接使用英文进行数据库查询，从而代替了繁琐的 SQL语句。 在本地计算机上可使用所有安装、升级选项以及高级选项（注册表重建、无值守安装和升级到群集等）；在远程安装模式中这些选项都不可用。其中【虚拟服务器】选项是在安装了 Microsoft Cluster Service系统的 Windows NT／2000企业版环境中可选。 SQL Server 2000支持在同一台计算机上最多同时运行16个SQL Server数据库引擎实例（一个为默认实例，名称为“计算机名”；15个命名实例，名称为“计算机名\命名”）。每个实例操作都与同一台计算机上的其它实例分开，每个SQL Server实例都有自己的一套系统及用户数据库、MSSQLServer、MSSQLServerAgent及注册表键，它们是不被其他实例共享的，但它们共用一个程序组，可通过服务管理器启/停、企业管理器管理。应用程序连接本机默认实例、命名实例、其他计算机上的实例的方式是没有多大差别的，一般我们在配置服务器时安装SQL Server 2000的一个或多个实例，而在客户端安装SQL Server的一个实例就行了。 在win98下安装时无“服务帐户”界面。也不支持Windows身份验证模式。 8、安装 根据上述要求进行安装，选择典型安装，并分别安装默认实例和命名实例。 9、检查 打开程序组，应能看到企业管理器、查询分析器、服务管理器等，否则需寻找原因重新安装。 实验2： SQL Server 2000的配置与使用 一、实验目的 1、掌握数据库服务器的各种启动、暂停、停止方法； 2、能用企业管理器进行基本管理操作； 3、能通过登录打开查询分析器并进行简单操作； 4、掌握导入导出数据、分离附加数据库的方法。 二、实验内容和步骤 1、数据库服务器的启动 SQL Server服务可以自动启动，也可以通过手工启动。 自动启动（当操作系统启动时自动启动）：（1）在服务管理器中选择“当启动OS时自动启动服务”；或（2）在企业管理器中打开左边的树形结构，右击服务器名并执行“属性”/“常规”/“在操作系统启动时自动启动策略”（注：本项功能限于Windows 2000/NT）。 再注销系统，重新进入系统后查看SQL Server服务有无启动。 手工启动：（1）从服务管理器中启动；或（2）从企业管理器启动（右击服务器名进行）；或（3）从控制面板“管理工具”/“服务”中启动（注：限于Windows 2000/NT）。 2、数据库服务器的暂停、停止 在服务管理器中进行。 3、修改系统默认的服务 右击服务管理器的标题并选“选项”，将服务管理器界面上显示的默认服务更改为SQL Server、SQL Server Agent、MSDTC、Microsoft Search之一。再注销系统并重新进入查看默认服务情况。 4、设置状态检测的时间间隔 在企业管理器中，执行控制台的“工具”/“选项”，将4中不同服务轮询时间间隔分别设置为5、10、15、20秒。 在服务管理器中，打开“选项”，设置时间间隔为8秒（每种服务的间隔均相同）。 注意：在企业管理器和服务管理器中设置的间隔互不相干。 5、运行SQL服务的DOS方法 进入DOS环境，输入： net start ：检测SQL服务是否启动 net start mssqlserver ：启动SQL Server服务 net stop mssqlserver ：停止SQL Server服务 net start sqlserveragent ：启动SQL ServerAgent服务 net stop sqlserveragent ：停止SQL ServerAgent服务 net start mssql$cbf ：启动SQL Server命名实例cbf服务 net start sqlagent$cbf ：启动SQL ServerAgent命名实例cbf服务 6、企业管理器中管理SQL Server服务器 运行注册向导新建一注册，开始管理SQL Server 2000服务器。从“安全性”中查看有无登录名（注册名自动映射为登录名）。 再新建另一登录名，用于另一用户登录SQL Server服务器。 7、从客户端登录数据库服务器 打开查询分析器，在“连接到SQL Server”对话框中用登录名填入（若不出现，说明用的是Windows身份验证模式，此时打开“文件/连接”就会看到该对话框）。再输入： use master select * from sysusers 注意：SQL Server下拉列表框：输入所要登录的数据库服务器名称。下拉列表框中存放的是以前曾访问过的数据库服务器名称。 8、启动、暂停、停止3种服务状态的验证 通过SQL Server服务管理器反复启动、暂停、停止SQL Server、SQL Server Agent、MSDTC、Microsoft Search四种服务（当服务器停止时，任何已经连接的访问均被中断，并且不接受其它访问。当服务器暂停时，已有的连接仍然保持，但不再接受其它连接请求）。 打开SQL查询分析器，连续输入如下2行： use Northwind select * from orders 测试当SQL Server服务处于上述三种状态时的可用情况。 9、导入、导出数据 将上一实验中建立的ACCESS数据库中所有表导入到SQL Server自带的pubs数据库中。 将SQL Server自带的Northwind数据库中的表orders导入到SQL Server自带的pubs数据库中。 10、分离、附加数据库 将northwind数据库从SQL Server中分离，再拷贝到“我的文档”中。 将“我的文档”中的northwind数据库附加到SQL Server中，取名为“新northwind”。 实验3：SQL Server 2000的系统和数据管理1 一、实验目的 1、了解SQL Server2000系统管理的常见工具； 2、能熟练用企业管理器创建数据库及进行常规操作； 3、能通过SQL语句创建符合要求的数据库； 二、实验内容和步骤 1、在企业管理器中创建数据库 在企业管理器的列表中右击“数据库”后，选择“新建数据库”，在数据库属性中输入数据库名称、数据文件和日志文件的各种属性。 当建立数据库后，仍可以修改数据库的属性：右击某数据库，选择“属性”。可以设置数据文件的各种属性。 创建一个逻辑文件名称为“学生管理”，实际文件名称为“学生管理.mdf”，初始容量为1MB，最大容量为50MB，且每次以5MB空间增长的数据库；创建一个逻辑文件名称为“学生日志”，实际文件名称为“学生日志.ldf”，初始容量为1MB，最大容量为5MB，且每次以20%空间增长的日志文件。 2、使用SQL语句创建数据库 读懂以下SQL程序，再依次在查询分析器中输入并运行之，然后在企业管理器中检查数据库属性情况： （1） create database 学生管理数据库 on (name='学生管理数据库', filename='d:\学生管理数据库.mdf', size=1, filegrowth=5MB, maxsize=50) log on (name='学生管理日志', filename='d:\学生管理日志.ldf', size=1, maxsize=5, filegrowth=20%) （2） Create database data2 on (name=d20, filename='d:\d20.mdf'), filegroup g1(name=a,filename='d:\g21.mdf') , (name=b, filename='d\g22.ndf' ) （3） create database data3 on primary (name=d30, filename='d\d30.mdf'), (name=d31, filename='d\d31.ndf'), filegroup g1 (name=g31, filename='d\g31.ndf'), (name=g32, filename='d\g32.ndf'), filegroup g2 (name=g33, filename='d\g33.ndf'), (name=g34, filename='d:\g34.ndf') log on (name=l31, filename='d:\l31.ldf'), (name='l32', filename='d:\l32.ldf') （4） create database data4 on ( name=d41, filename='d:\d41.ndf' ), primary ( name=d40, filename='d:\d40.mdf'), filegroup g1 ( name=g41, filename='d:\g41.ndf' ) log on ( name=l41, filename='d:\l41.ldf') （5）for attach的使用 先将“学生管理数据库”从企业管理器中分离，再输入： create database 学生管理数据库10 on (name='学生管理数据库',filename='d:\学生管理数据库.mdf') for attach 然后应可看到学生管理数据库附加进企业管理器中。 三、现场综合练习 以下结果请保存在自定义的文件夹中！ 1、使用SQL语句建立一个数据库mydata1，数据文件逻辑名为my1，OS名为my1.mdf，大小为2MB，最大12MB，以15%的速度增长。不指定日志文件。 2、使用SQL语句建立一个数据库mydata2，主数据文件逻辑名为my2,OS名为my2.mdf，文件组G1中包含2个文件：myg21(myg21.ndf),g22(myg22.ndf)。2个日志文件：myl21(myl21.ldf)（大小为3MB，最大20MB，以2MB的速度增长）、myl22(myl22.ldf)。 3、使用SQL语句建立一个数据库mydata3：将上题中的2个次要数据文件分别放在文件组G1和G2中（请适当地改名！）。 实验4：SQL Server 2000的系统和数据管理2 一、实验目的 巩固上一实验用SQL创建数据库的操作方法； 能熟练用企业管理器修改、删除数据库； 能通过SQL语句修改、删除数据库； 二、实验内容和步骤 1、在企业管理器中修改数据库 利用数据库属性对话框，可以设置数据库的数据文件、日志文件、选项等属性。 要求：按默认大小自行建立一数据库“通讯录”，包含一个主要文件和一个属于文件组g1的次要文件，再进行修改，使主文件最大大小为45MB，按5MB进行增长；次文件最大大小不受限制，初始大小为3MB；g1为只读。其余属性不变。 2、使用SQL语句修改数据库 读懂如下各程序段，说出其功能，再依次在查询分析器中输入并运行之，然后在企业管理器中检查数据库属性情况： （1） create database data1 on (name=d10, filename='d:\d10.mdf'), filegroup g1(name=g10,filename='d:\g10.mdf') log on ( name=l10, filename='d:\l10.ldf') go alter database data1 add file ( name=d11, filename='d:\d11.ndf') go alter database data1 add log file ( name=l11, filename='d:\ll.ldf') （2） alter database data1 add file ( name=g11, filename='c:\g11.ndf' )to filegroup g1 go alter database data1 add filegroup g2 go alter database data1 add file ( name=g20, filename='d:\g20.ndf' ) to filegroup g2 (3) alter database data1 remove file d11 go alter database data1 remove filegroup g1 (4) alter database data1 modify file ( name=d10, newname=d100,size=4,filegrowth=3) go alter database data1 modify filegroup g2 name=g20 go alter database data1 modify filegroup g2 readonly --readwrite为可读写 (5) alter database data1 modify name =最终数据库 3、压缩数据库（使用数据库一致性检查器DBCC命令实现） 用DBCC SHRINKDATABASE (database_name,15,TRUNCATEONLY)命令将数据库data1压缩，使剩余空间为15%。 4、删除数据库 （1）创建“学生成绩”数据库：包含一个主要文件、一个次要文件、一个日志文件。 （2）用DROP DATABASE 命令删除之。 三、现场综合练习 在查询分析器中进行： 创建“学生管理”数据库：包含一个主要文件、一个次要文件、一个日志文件；再添加文件组“我的文件组”并在其中添加一个次要文件；添加另一个日志文件。 将“学生管理”数据库属于主文件组的次要文件删除；将“我的文件组”中的次要文件逻辑名更改为“文件组文件1”，初始大小为4MB；将第一、二个日志文件逻辑名分别改为“日志1”、“日志2”，最大大小均为45MB。 将“学生管理”数据库中“我的文件组”改为只读。 实验5～6： 数据表的创建与维护（分2次） 一、实验目的 1、能熟练用企业管理器创建、修改、删除数据表； 2、能通过SQL语句创建、修改、删除数据表； 3、能熟练用SQL语句进行表中数据的操作。 二、实验内容和步骤 1、在企业管理器中创建、修改数据表 （1）要求在上一实验中创建的“学生管理”数据库中创建一个“基本情况” 表，结构如下： 字段名 数据类型 能否取空值  学号 整型 No，且为主键  姓名 字符串类型，8字节 Yes  性别 字符串类型，2字节 Yes  年龄 短整型，1字节 Yes  专业 字符串类型，30字节 Yes  家庭地址 字符串类型，50字节 Yes   （2）将“姓名”改为不允许取空值，添加“备注”字段，“学号”改为字符型。 2．使用CREATE TABLE语句创建表 （1）说出下列程序的功能并运行之： use 学生管理 create table 学生表 (学号 char(9) not null primary key, 姓名 char(8) not null, 性别 char(2) default '男', 专业 char(30) ) （2）说出下列各程序的功能并运行之： create table 学生管理.dbo.score (score1 tinyint not null, score2 tinyint not null, total as score1+score2 ) （3）创建两个表：课程表、成绩表，一个用于保存各门课程的信息（课程编号、课程名称、学分），一个用于保存学生选修课程的成绩（学号、所选课程号、成绩、新成绩）。其中“新成绩=成绩+10” 3．使用ALTER TABLE语句修改表 （1）说出下列程序的功能并运行之： alter table 学生表 add 备注 varchar(100) , 电话 bigint （2）说出下列程序的功能并运行之： alter table 学生表 alter column 备注 char(200) （3）说出下列程序的功能并运行之： alter table 学生表 drop column 电话 （4）给“学生表”添加“地址”（NULL）、“年龄”（默认值为20）字段；删除“备注”字段；修改“姓名”为 char(10) 类型。 4．约束的应用 说出下列程序的功能并运行之： （1）create table 学生_a (学号 char(9) 姓名 char(8) not null, constraint yueshu1 primary key (学号) ) （2）create table 学生_b (学号 char(9) constraint yueshu2 foreign key references 学生_a(学号) , 成绩 int constraint yueshu3 default 60) （3）alter table 学生_a add constraint yueshu4 unique(xm) alter table 学生_b add constraint yueshu5 check(成绩>0 and 成绩<100') （4）alter table 学生_b drop constraint yueshu3 （5）创建“任课教师”表（序号，教师名，性别，课程号，班级，学时数），并用约束加上如下限制条件： 序号：主键、非聚蔟索引 （表级约束） 教师名：唯一 性别：默认值为“男” 学时数：在16----96之间 课程号：外键（相对于“课程表”） 5．使用INSERT、UPDATE、DELETE 语句插入、修改、删除记录 （1）说出下列程序的功能并运行之，有错请修改： insert into 学生表 (学号，专业，年龄，姓名，地址) values (200208110，'机械'，default, '张宁宁'，'北京市王府井大街2208号') insert into 学生表 (学号，专业，年龄，姓名，地址) values (200208111，'艺术'， '李宁宁'，'济南市府前路2208号') insert into 学生表 (学号，年龄，姓名，地址) values (200208112，'医学'，21, '王柠柠'，'泰州市东进路2208号') （2）运行下列语句，若有错请修改： insert score values (10,30,40) （3）说出下列程序的功能并运行之： update 学生表 set 姓名='李玲玲' where 学号=200208111 update 学生表 set 年龄=年龄+1 delete from 学生表 where 年龄<22 （4）插入如下记录： 学号 姓名 性别 年龄 专业 地址  200209012 汪晓丽 女 22 计算机 上海市邯郸路220号  200308215 张键 男 21 计算机 青岛市高雄路1号  200307121 曲竹菲 女 21 会计学 厦门市屯溪路291号   再将“张键”改名为“张箭”；所有学生年龄减1；删除学号为200307121的学生。 三、现场综合练习 1、分别用企业管理器、查询分析器在“学生管理” 数据库中创建“学生专业表bak”、“学生专业表”： 学号 char(9) NOT NULL、姓名 char(9) NOT NULL、年龄 tinyint、性别 char(2)、所在院系 varchar(30)、专业 varchar(40) 2、使用企业管理器在“学生专业表bak”中插入一组记录，然后在所有的学号前均加上“2003”。 3、用查询分析器在“学生专业表”中插入一组记录，内容自定义。 4、用查询分析器在“学生专业表”中修改一组记录，条件自定。 5、用查询分析器删除“学生专业表bak”中所有记录。 6、删除“学生专业表bak”。（用“drop table 表名”） 实验7～9：数据库中数据表的操作（SQL查询） (分3次) 一、实验目的 1、能根据要求用3个基本子句从1个或多个表中筛选数据； 2、能正确运用集合函数进行操作； 3、掌握GROUP BY子句、ORDER BY子句的使用方法； 掌握基本的嵌套查询使用方法； 能将SELECT语句嵌套进数据更新语句中进行数据更新操作。 二、实验内容和步骤 1、按照下述要求建立一个“学生课程”数据库及4个表，并输入数据，以备下面使用。 学生表 学号 姓名 性别 年龄 所在院系 班级名 入学年份  200009001 葛文卿 女 22 国际贸易 国贸2班 8/29/2000  200104019 郑秀莉 女 21 会计学 会计1班 9/2/2001  200203001 刘成锴 男 18 计算机 软件2班 8/27/2002  200206001 李涛 男 19 电子学 电子1班 8/27/2002  200203002 沈香娜 女 18 计算机 软件2班 8/27/2002  200206002 李涛 男 19 电子学 电子1班 8/27/2002  200203003 肖一竹 女 19 计算机 软件2班 8/27/2002   课程表 课程号 课程名 先修课  C801 高等数学   C802 C++语言 C807  C803 数据结构 C802  C804 数据库原理 C803  C805 操作系统 C807  C806 编译原理 C803  C807 离散数学   成绩表 学号 课程号 成绩  200203001 C801 98  200203002 C804 70  200206001 C801 85  200203001 C802 99  200206002 C803 82   授课表 教师名 课程号 学时数 班级名  苏亚步 C801 72 软件2班  王立山 C802 64 软件2班  何珊 C803 72 软件2班  王立山 C804 64 软件2班  苏亚步 C801 72 电子1班   说出下列程序的功能并运行之（有错请修改），或编写程序： 2、基于单表的查询 （1）select 学号, 姓名, 所在院系, 年龄 from 学生表 （2）select * from 成绩表 （3）要求查询全部学生的所有信息，且姓名输出在最左边（第一列）。 （4）select 姓名, 出生年份 as 2003 - 年龄 from 学生表 （5）要求计算各们课程的学分（设学分=学时数/16） （6）select distinct 姓名, 2003-年龄 ‘出生年份’ from 学生表 order by 年龄 （7）要求列出学生所在的所有院系名称（不含重复内容）。 （8）select * from 学生表 where 姓名 like '李%' （9）select * from 成绩表 where 成绩 not between 85 and 95 order by 成绩 DESC （10）列出所有非软件2班的班级名称。( not like ) （11）要求列出课程号在 'C802' 与 'C804'之间的所有课程信息；列出无先修课的所有课程的课程名。 （12）select * from 课程表 where 课程名 in ('高等数学','操作系统','编译原理') （13）要求查询所有2002年元旦之前入学的学生名单。（日期使用字符串的形式表示，日期的大小可以比较。）并按年龄排序。 （14）要求查询年龄在19岁以下或者是女生的学生姓名、年龄、性别。 （15）同时按学号(从小到大)和课程号（从大到小）的顺序列出所有学生课程成绩 2、数据统计（也称SQL的集合函数）的应用 （1）select count(distinct所在院系) as 系部的个数from 学生表 （2）要求统计19岁以上女生的人数；查询所有年龄的总和。。 （3）select max(成绩) 最高分,min(成绩) 最低分 from 成绩表 （4）要求查询’C801’课程的平均分。 （5）要求统计选修了课程的学生人数（用成绩表）。 3、使用GROUP BY子句对结果分组 （1）select性别, avg(年龄) 平均年龄 from 学生表 group by 性别 （2）select 入学年份 from 学生表 group by 入学年份 （3）select month(入学年份) as 月份 , count(*) from 学生表 group by month(入学年份) having count(*)>3 （4）要求显示每个入学年份中入学人数在2人以上的入学年份及人数；求相同月份入学的人数（不考虑年份）。 （5）筛选出平均成绩在80分以上的课程及平均成绩。 4、基于多表的查询 （1）select 学生表.*, 成绩表.* from 学生表, 成绩表where 学生表.学号=成绩表.学号 （2）将（1）中去掉重复的字段：学号。（需列出各个字段名） （3）要求列出学生选修课程的情况（姓名，课程号，成绩） （4）列出所有课程的先修课程的名称： Select a.课程名,b.课程名from 课程表 a,课程表 b where a.先修课=b.课程号 （5）要求列出所有课程的间接先修课程号。 （6）Select 学生表.学号,姓名 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 课程号='C801' and 成绩>=90 （7）select 姓名,课程名 from 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 成绩表.课程号=课程表.课程号 （8）要求列出选修了高等数学的学生学号、姓名、成绩、课程名 5、嵌套查询 （1）select * from 学生表 where 所在院系=(select 所在院系from 学生表where 姓名='沈香娜') （2）select 姓名 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 成绩表.课程号 in ('C801','C804') （3） select * from 学生表 where 所在院系 =(select 所在院系from 学生表 where 姓名='李涛') （4）查询何珊所教班级的所有任课教师的姓名、所教班级名。 （5）列出选修了C802和C803的学生姓名、课程名和成绩。（要求用in） （6）select 姓名from 学生表 where 年龄>any(select 年龄 from 学生表 where 所在院系='计算机') and 所在院系<>'计算机' （7）将（6）用min改写。 （8）select 姓名from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 成绩表.成绩>all(select 成绩 from 成绩表 where 学号='200203001') （9）列出所有院系中比电子学系学生年龄都小的学生姓名。 （10）列出所有院系中比电子学系某一学生年龄小的学生姓名。 （11）select 姓名 from 学生表 where 年龄=(select max(年龄) from 学生表) （12）按年龄降序列出所有超过平均年龄的学生姓名和年龄。 6．UNION查询 select * from 学生表 where 性别='女' union select * from 学生表 where 所在院系='计算机' （2）将（1）用OR运算符改写（去掉?union） （3）显示合并后的学生表和成绩表中的学号。 （4）Select 教师名,课程名,班级名 from 授课表 a,课程表 b where a.课程号=b.课程号 and a.课程号='C801' union select 教师名,课程名,班级名 from 授课表 a,课程表 b where a.课程号=b.课程号 and a.课程号='C803' （5）列出讲授C802或C801课程的所有教师名、课程号和班级名。 7．INTO子句 （1）select 学号 number，姓名 name ，所在院系 into 计算机系学生 from 学生表 where 所在院系='计算机' （2）select * into 空学生表 from 学生表 where 所在院系='泰州系' （3）新建一个表，用于存放计算机和国际贸易系的所有学生名单。 （4）重新将上例的表创建为临时表：“#临时表”，再输入： select * from #临时表 退出SQL Server重新进入、并输入： select * from #临时表 结果如何？为什么？ 8、数据更新（高级操作） （1）create table 新学生表 (学生学号 char(9) not null primary key, 学生姓名 char(8), 学生性别 char(2), 所在院系 char(10) ) insert into 新学生表 select姓名,学号,性别,所在院系 from 学生表 where 所在院系='计算机' and 性别='女' select * from 新学生表 （2）将电子学系的学生插入到“新学生表”中。 （3）update 成绩表 set 成绩=成绩+8 where '电子学'=(select 所在院系 from 学生表 where 学生表.学号=成绩表.学号) select * from 成绩表 （4）update 成绩表 set 成绩=成绩-8 where 学号 in(select 学号 from 学生表 where 所在院系='电子学') select * from 成绩表 （5）将性别为“男”的成绩加5。 （6）删除电子学系所有学生的选修记录。 三、现场综合练习 检索学号不是[200108011]且入学月份为8月的学生的学号、姓名、年龄。 列出年龄在18~20岁之间且选修了高等数学的男同学的详细信息。 列出所有不是姓“刘”的学生清单。 统计[学生表]中一共有多少名20岁以上的女同学。 查找年龄最大和最小的男学生年龄。 6）计算选修课门数在1门以上的学生学号、门数。 7）计算每个班学生的平均年龄和最小年龄。 8）列出所有成绩大于学号为200206002的学生成绩的学生姓名、课程名和成绩。 9）创建一个[简单学生表]，要求：包含4个字段“学生学号、学生姓名、学生性别和所在院系”，然后将[学生表]中“国际贸易”系或者男学生的记录全部插入到[简单学生表]中，最后显示插入的记录。 10）列出所有没有选修课程“C804”的学生清单。 11）计算每个学生的选修课门数。 12）建一新表：包括姓名、课程、成绩，从已有表中插入所有相关记录。再删除新表中成绩低于70分的记录。 13）将计算机系的学生成绩均加5。 14）列出所有入学年份在电子学系任一学生入学之后入学的其他院系学生。 附：创建表及插入记录的参考代码如下： create database 学生课程 --建立数据库 on (name=studentcourse, filename='c:\studentcourse.mdf') log on( name=studentcourselog,filename='c:\ studentcourse.ldf') -------注意：上下2部分语句需分开执行！！！ use学生课程 create table 学生表 --建立学生表 ( 学号 char(9) not null primary key, 姓名 char(8), 性别 char(2),年龄 tinyint, 所在院系 char(10),班级名 char(10),入学年份 datetime) go insert into 学生表 --向学生表中插入记录 values('200009001','葛文卿','女',22,'国际贸易','国贸2班','2000/8/29') insert into 学生表 values('200104019','郑秀莉','女',21,'会计学','会计1班','2001/9/2') insert into 学生表 values('200203001','刘成锴','男',18,'计算机','软件2班','2002/8/27') insert into 学生表 values('200206001','李涛','男',19,'电子学','电子1班','2002/8/27') insert into 学生表 values('200203002','沈香娜','女',18,'计算机','软件2班','2002/8/27') insert into 学生表 values('200206002','李涛','男',19,'电子学','电子1班','2002/8/27') insert into 学生表 values('200203003','肖一竹','女',19,'计算机','软件2班','2002/8/27') go create table 课程表 --建立课程表 ( 课程号 char(4) not null primary key, 课程名 char(12),先修课 char(4)) go insert into 课程表 (课程号,课程名) --向课程表中插入记录 values('C801','高等数学') insert into 课程表 (课程号,课程名,先修课) values('C802','C++语言','C807') insert into 课程表 (课程号,课程名,先修课) values('C803','数据结构','C802') insert into 课程表 (课程号,课程名,先修课) values('C804','数据库原理','C803') insert into 课程表 (课程号,课程名,先修课) values('C805','操作系统','C807') insert into 课程表 (课程号,课程名,先修课) values('C806','编译原理','C803') insert into 课程表 values('C807','离散数学' ,NULL) go create table 成绩表 --建立成绩表 ( 学号 char(9) not null, 课程号 char(4) not null,成绩 tinyint) go --向成绩表中插入记录 insert into 成绩表 values('200203001','C801',98) insert into 成绩表 values('200203002','C804',70) insert into 成绩表 values('200206001','C801',85) insert into 成绩表 values('200203001','C802',99) insert into 成绩表 values('200206002','C803',82) go create table 授课表 --建立授课表 (教师名 char(8),课程号 char(4) not null,学时数 tinyint, 班级名 char(10) not null) go --向授课表中插入记录 insert into 授课表 values('苏亚步','C801',72,'软件2班') insert into 授课表 values('王立山','C802',64,'软件2班') insert into 授课表 values('何珊','C803',72,'软件2班') insert into 授课表 values('王立山','C804',64,'软件2班') insert into 授课表 values('苏亚步','C801',72,'电子1班') go   实验10：数据类型操作 一、实验目的 1、了解各个数据类型的使用场合； 2、能根据问题的需要选择合适的数据类型并熟练运用。 二、实验内容和步骤 1、用SQL语句创建“正式编制人员”表，包含如下信息： 序号（用整型且自动编号：identity、主键）、姓名（用varchar）、出生时间（用smalldatetime）、是否团员（用bit）、工资（用smallmoney）、培训成绩（用decimal或numeric）、简历（用text）、照片（用image）、修改标记（用timestamp） 2、向“正式编制人员”表中插入如下信息：（需加上字段名！） ('周利利','3/18/1956 8:30:0',0,1325.65,98.5,'曾多次被评为先进生产工作者',0x43a23efd22f3d) ('张宁','3-18-1980 13:25:15',1,890.10,83,'表现良好',0xa2324cc367f3d) ('李宏灵','1976.11.18',0,1132,99.5,'工作较积极',0x2243a23efd22f3cc) ('周涛','3/18/1966',0,1125.90,78.5,'曾多次被评为三八红旗手',0x3333efd22fa) ('吴红奋','5/28/1956 18:30:0',0,1333.25,87.5,'曾多次被评为先进生产工作者',0xcaaefd22fd3d) ('钱金金','3/1980/18 10:11:11',1,779.60,91.5,'上进心不强，经常犯错误',0xbb423efd22f3) 3、说出如下程序的作用及结果，并运行之： CREATE TABLE 特殊数据 ( xh int，Sql_variant_data sql_variant ) INSERT INTO 特殊数据 VALUES(1,'This is a sql_variant data type test') INSERT INTO 特殊数据 VALUES(2,123456) INSERT INTO 特殊数据 VALUES(3,'April 15，1998') G0 SELECT * FROM 特殊数据 4、说出如下程序的结果并运行之： DECLARE @Table_Example TABLE (C0l_num int PRIMARY KEY, C0l_text char(50)) INSERT INTO @Table_Example VALUES (1,'this is a') INSERT INTO @Table_Example VALUES (2, 'table data type example') GO SELECT * FROM @Table_Example 5、用SQL创建2个用户自定义数据类型：telephone、address，分别用于存放8位电话号码、地址。再创建表，包括：编号、姓名、电话号码。并插入2条记录。 6、删除自定义数据类型address。 实验11：Transact-SQL变量、运算符与函数 一、实验目的 1、掌握各种运算符的使用方法； 2、能正确地定义和使用局部变量，了解常用全局变量使用场合； 3、了解6类内建函数的基本功能； 4、能正确使用常见的函数； 5、重点掌握常见系统函数、字符串函数的含义及使用方法。 二、实验内容和步骤 调试程序并说出主要功能或根据要求编写程序： 1、 declare @aaa VARCHAR(9),@bbb float,@ccc int,@ddd int select @aaa=姓名,所在院系 from 学生表 where 学号='200203001' select @bbb=12.3456, @ccc=1 set @ccc=5,@ddd=6 print @aaa,@bbb select 'xm' =@aaa,@bbb, 'tel：'+'6664032' 电话 select @aaa as xm,@bbb select @aaa xm,@bbb 2、 create table 二进制在位运算符中使用 (x binary, y binary , z int) insert into 二进制在位运算符中使用 values(1,4, 3) select x | z from 二进制在位运算符中使用 select x & z from 二进制在位运算符中使用 select x | y from 二进制在位运算符中使用 3、 declare @aaa CHAR(9) select @aaa=姓名 from 学生表 问：返回哪一条记录的值？ 4、将所有学生的平均年龄放入“@平均年龄”变量中。 5、显示姓“李”的学生，要求用变量保存姓“李”的这一条件 6、 select * from学生表 declare @x int update 学生表 set @x=年龄 问：是哪一条记录的信息？ 7、用update方法将学生的最大年龄保存到局部变量中。 8、 declare @x int update 学生表 set @x=(select 年龄 from 学生表) 问：错在哪儿？ 9、 select * from 学生表 declare @x int select @x=@@rowcount if 2=2 print 2 if @@rowcount=0 print 'rowcount is 0'。 10、 select @@spid,system_user 登录用户,user 用户名 11、 create table 标识表(aa int identity（4,2） , 姓名 char(9)) insert 标识表(姓名) values('?Mike') insert 标识表(姓名) values('John') 问：此时 @@identity为多少？@@rowcount呢？ 12、求每个学生的“所在院系”所占的内存字节数。（用datalength函数） 13、创建一个新表：C801课程成绩名次表，用于保存‘C801’课程的各个学生成绩及名次。要求字段为：姓名、学号、成绩、名次。（用select….into….及identity(int,1,1)） 14、写一个select语句，显示所有学生的姓名、入学日期，入学日期必须是与当前日期相差550天以上的。并要求用convert函数将字符型的日期转换成datetime或相反。 15、显示各个课程及先修课程情况，要求对无先修课的课程，其先修课字段值应显示成“无”而不是“NULL”。 16、打印xxxx年xx月xx日。 17、显示每个学生的学号、课程成绩。其中“课程成绩”为：课程号+“的成绩是：”+成绩。（用str函数） 18、将10个连续的“笑哈哈”倒过来显示。 19、将所在院系中“贸易”替换成“金融”。（要求用replace函数）。 实验12：Transact-SQL程序设计 一、实验目的 1、掌握流控制命令的使用方法； 2、能根据要求编写程序； 3、掌握游标的使用方法； 4、了解错误的处理方法。 二、实验内容和步骤 调试程序并说出主要功能或根据要求编写程序： 1、declare @a real,@b real,@c real,@d real,@x real select @a=20,@b=40,@c=80 set @d=@b*@b-4*@a*@c if @d<0 print 'no root' else if @d=0 begin print 'one root:' set @x=-@b/(2*@a) print @x end else begin print 'two root:' set @x=(-@b+sqrt(@d))/(2*@a) print @x set @x=(-@b-sqrt(@d))/(2*@a) print @x end 2、if all(select 年龄 from 学生表)<22 print '没有超过22岁的' else print '有' 3、if exists(select * from 学生表 where 姓名='李涛') print '不存在' else print '存在' 4、如果没有“艺术系”的学生则显示“无”，否则显示“有”。 5、update 成绩表 set 成绩=成绩+ case 学号 when '200203001' then 5 when '200206001' then 8 else 3 end 6、用select…into…将学生表复制到新表“学生表bak”，同时增加一个字段“年龄段”，若年龄<18，显示“少年”，若年龄>=18，显示“成年”。 ７、显示100以内能被７整除的数。 ８、等待10秒钟后显示100以内能被７整除的数。 9、 DECLARE PM_Cursor CURSOR READONLY FOR SELECT * FROM 学生表 WHERE 性别='男' ORDER BY 姓名 OPEN PM_Cursor FETCH NEXT FROM PM_Cursor WHILE @@FETCH_STATUS=0 BEGIN FETCH NEXT FROM PM_STATUS END 10、set statistics time on select * from 学生表 set statistics time off set rowcount 3 select * from 学生表 set rowcount 50 set nocount on select * from 学生表 set noexec on select * from 学生表 set nocount off 实验13：索引和视图 一、实验目的 1、能通过企业管理器创建索引和视图； 2、能使用SQL语句创建、删除索引； 3、能使用SQL语句创建、删除各种视图； 4、能熟练使用SQL语句对视图进行操作。 二、实验内容和步骤 说出下列程序的功能并运行之（有错请修改），或编写程序： 1、索引操作 （1）通过企业管理器创建聚簇索引（按学号）、非聚簇索引（按姓名）。 （2）create clustered index 课程号索引1 on 课程表(课程号 desc) create index 课程号索引2 on 课程表(课程号) 问：上述2句有何不同？ （3）按学号为学生表创建一个唯一索引“学号唯一索引”。 问：上述索引属于聚簇索引还是非聚簇索引？ （4）按学号、姓名、性别为学生表创建一个复合索引“复合索引”。 （5）删除“课程号索引2”。 2、基本视图创建 （1）通过企业管理器创建视图：它只显示学生表中性别是女生的学生的学号、姓名、年龄和所在院系。 （2）create view 计算机学生视图(学号,姓名,年龄) as select 学号,姓名,年龄,所在院系 from 学生表 where 所在院系='计算机' go select * from 计算机学生视图 问：“select * from 计算机学生视图”有何作用？ （3）创建选修了C801课程的学生详细信息视图：学生选修C801信息。 （4） create view 男生选修C801信息 as select 学号,姓名,年龄,性别,所在院系 from 学生选修C801信息 where 性别=’男’ 3、表达式视图创建（即视图中的某些字段来自于子查询中的字段表达式）、分组视图（即集合函数视图） （1）create view 出生年份(学号,姓名,出生年份) as select 学号,姓名,2003-年龄 from 学生表 学生的学号和平均成绩定义为一个视图：学号分组成绩。 （3）create view 计算机学生 as select 学号,姓名,年龄,所在院系 from 学生表 where 所在院系='计算机' with check option 再通过insert语句向视图中插入记录('200203011','MIKE',18,'计算机') 和('200203012','joe',18,'电子学')，查看运行结果。为什么？“with check option”有何作用？ 4、查询视图（即对视图进行查询） （1）在“计算机学生”视图上，列出年龄小于平均年龄的学生。 （2）在“学号分组成绩”视图上，列出平均成绩大于80分的学号和平均成绩。 （3）根据“计算机学生”视图及相关表，查询计算机系学生选修了C804的学生清单。 5、更新视图 对视图所作的数据更新操作，最终影响的是基本表。最好在创建视图时加上“WITH CHECK OPTION”以防止不符合条件的数据进入基本表。 （1）向“计算机学生”视图中插入2条新记录('200303007','KKK',17,'物理学')、('200303009','LLL',19,'物理学')。 （2）将“计算机学生”视图中学号为'200203001'的姓名改为’MMM’。 （3）将“计算机学生”学号为'200303009'的学生删除。 （4）update 出生年份 set 出生年份=1978 where 学号='200203003'对吗？为什么？ 三、现场综合练习 在课程表上创建一个唯一、非聚簇复合索引（按学号升序、课程号降序）。 创建视图“教师信息”，用于保存何珊所教班级的所有任课教师的姓名、学时数、课程号。再将C802的学时数改为80。 创建视图“选修门数”，用于保存选修课门数在1门以上的学生学号、门数。再显示选修课门数为2门的学生学号。 实验14：事务处理、存储过程 一、实验目的 1、了解显式事务与隐式事务的异同； 2、能编写显式事务程序； 3、掌握存储过程的使用方法； 能根据需要编写存储过程。 二、实验内容和步骤 调试程序并说出主要功能或根据要求编写程序： １、 begin tran update 成绩表 set 成绩=成绩*1.15 where 课程号='c804' save tran abc update 成绩表 set 成绩=成绩*1.10 where 课程号='c807' commit tran ２、将每个学生的年龄加１，再将每个人的入学年份减１，最后撤消。 ３、将每个学生的年龄加１，再将每个人的入学年份减１，再撤消后一个操作。 ４、将“沈香娜”的成绩加５，如果更新成功则显示“更新成功”及沈香娜的全部信息，否则撤消之。 创建存储过程“求商”：根据2个实数输入参数，求其商。再根据具体数据调用之并打印运行结果。 创建存储过程“求商;2”：根据2个整数输入参数，求其商。再根据具体数据调用之并打印运行结果。 创建存储过程“查找学生”：根据输入的姓名查找有无该学生，若有打印“已找到”并显示该学生的信息，若无打印“没找到”，若输入姓名为空打印“姓名未输入”。 重新创建上题的存储过程，但要求在存储过程中根据输入的姓名情况用多个return 返回不同的状态值，再在调用时根据不同的状态值打印相应信息。 实验15：触发器与数据库安全 一、实验目的 1、了解触发器的工作原理； 2、掌握触发器的使用方法； 3、能根据需要编写触发器； 4、掌握保障数据库安全的方法。 二、实验内容和步骤 调试程序并说出主要功能或根据要求编写程序： 1、创建触发器“监视姓名和年龄”：若有人的姓名或年龄被修改，则显示“姓名或年龄已改动！”，否则显示“没改动！”。再任意执行一条insert、update或delete语句看效果如何。 创建触发器“监视学生表删除及级联删除”：若学生表中有记录被删除，则显示“学生表中有记录被删除！”同时从成绩表中删除有关记录。再任意执行一条insert、update或delete语句看效果如何。 3、inserted表的作用： create trigger td on 学生表 for insert as print '插入了记录！' select * from inserted rollback tran go insert into 学生表(学号,年龄) values('900011001',24) select * from 学生表 4、deleted表的作用： create trigger te on 学生表 for delete as insert into 学生表 select * from deleted go delete from 学生表 where 姓名='李涛' select * from 学生表 5、 create trigger tg on 学生表 for update as print 'deleted表' select * from deleted print 'inserted表' select * from inserted rollback tran go update 学生表 set 年龄=年龄+1 where 性别= '女' 请解释每个select的用途，最后表中结果有无变化？ 分别用企业管理器、备份向导、Transact-SQL语句对“学生课程”数据库进行备份与恢复。 创建一个SQL Server登录帐号，并用“查询分析器”进行验证。 创建一个标准角色dzx并添加一个用户cbf，使之能够对“学生课程”数据库中的“学生表”进行各种操作，但只能对“成绩表”进行检索。 给用户cbf添加“db_accessadmin”数据库角色。 10、给用户cbf施加语句权限“CREATE PROCEDURE”，拒绝“BACKUP DATABASE”及“BACKUP LOG”。 实验16：数据完整性 一、实验目的 1、了解数据完整性的含义； 2、能根据要求实施数据的完整性； 重点掌握规则和默认的使用方法。 二、实验内容和步骤 1、分别用企业管理器和T-SQL语句创建如下规则并加以绑定，然后插入一些记录进行验证： （1）学生表中的“性别”只能是“男”或“女”之一 （2）学生表中的“年龄”，必须在18—24之间 （3）学生的“姓名”至少2个汉字 2、用T-SQL语句解除对“姓名”字段的绑定，并删除该规则。 3、分别用企业管理器和T-SQL语句创建如下默认并加以绑定，然后插入一些记录进行验证： （1）学生表中的“性别”默认“女” （2）学生表中的“年龄”，默认是20 4、用T-SQL语句解除对“年龄”字段的绑定，并删除该默认。