实验解答.ppt
数据库实验解答实验2.2练习下面简单的查询语句：a)查询每个雇员的所有信息select*from Employeeb)查询每个雇员的地址和电话select mployeeID,Address,PhoneNumber from Employeec)查询EmployeeID为000001的雇员的地址和电话。select Address,PhoneNumber from Employeewhere EmployeeID=000001实验2.2练习下面简单的查询语句：d)查询女雇员地址和电话，并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。select EmployeeID,Address as 地址,PhoneNumber as 电话from Employeewhere sex=falsee)计算每个雇员的实际收入。select EmployeeID,Income-OutCome from Salaryf)找出所有姓王的雇员的部门号。select distinct DepartmentID from Employee where Name like王%3、练习多表连接查询和嵌套查询：查询每个雇员的情况及工资情况（工资=Income-Outcome）select Employee.*,Income-Outcomefrom Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID查询财务部工资在2200元以上的雇员姓名及工资情况select Name,Income-Outcome as 工资from Employee,Salary,Departmentswhere Employee.EmployeeID=Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentIDand Income-Outcome22003、练习多表连接查询和嵌套查询：查询研发部在1966年以前出生的雇员姓名及其工资详情select Name,Income-Outcome as 工资from Employee,Salary,Departmentswhere Employee.EmployeeID=Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentIDand DepartmentName=研发部 and year(Birthday)2500求财务部雇员的总人数select count(*)from Employee,Departmentswhere Employee.DepartmentID=Departments.DepartmentIDand DepartmentName=财务部 求财务部雇员的平均工资select avg(Income-Outcome)from Employee,Salary,Departmentswhere Employee.EmployeeID=Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentIDand DepartmentName=财务部 查找比所有财务部的雇员工资都高的雇员的姓名select Namefrom Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and Income-Outcome(select max(Income-Outcome)from Employee,Salary,Departmentswhere Employee.EmployeeID=Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentIDand DepartmentName=财务部)查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名查找在财务部工作的雇员的情况2.4 SQL数据更新语句数据更新语句设设有有一一个个SPJ数数据据库库，包包括括S，P，J，SPJ四个关系模式：四个关系模式：供供应应商商S(SNO,SNAME,STATUS,CITY)零件零件P(PNO,PNAME,COLOR,WEIGHT)工程工程J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)2.4 SQL数据更新语句数据更新语句1在S表中插入元组“s6，华誉，40，广州”2在J表中插入元组“j8，传感器厂”3对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库insert into testselect sno,sum(qty)from spjgroup by sno 4将P表中PNO值为p6的元组的color属性值改为绿，weight属性值改为605将SPJ表中前4个元组的qty属性值统一修改为300update top(4)spjset qty=300 6将S表中city属性名含有“京”或“津”的相应status属性值增加1002.4 SQL数据更新语句数据更新语句7将供应商s2为“一汽”工程项目所供应的零件数量修改为20008将全部红色零件的颜色修改为浅红色9由s5供给j4的零件p6改为由s3供应10在SPJ表中新增一名为SDATE的属性列，对该表中的每一元组在SDATE属性列上填上实验当时的日期和时间alter table SPJ add SDATE datetimeupdate spjset SDATE=getdate()11删除所在城市为“广州”的供应商记录12删除所有零件名称中第一个字为“螺”字的零件记录，并在供应情况表中删除相应的记录13删除s3和s4两供应商为“三建”工程供应“螺母”或“螺丝刀”零件的相应供应情况数据信息3.1 数据完整性管理数据完整性管理1、建立课程的实体完整性和课程号cno的参照完整性;alter table JADD primary key(jno)ADD foreign key(jno)references j(jno)2、对HRM数据库，练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定义的约束（参考HRM数据库表定义图中说明）；3、建立salary表的Income字段限定在0-9999之间。3.2 数据库安全性管理 1、创建角色ProgramerRole，拥有创建表，存储过程，视图权限，拥有对Salary表的查询、修改、插入权限2、创建一个登录账号Testlogin3、创建对应于这个登录账号的数据库用户TestUser4、将用户TestUser添加到TestRole角色中1、创建角色ProgramerRole，拥有创建表，存储过程，视图权限，拥有对S表的查询、修改、插入权限create role ProgramerRolegrant create table,create procedure,create view to ProgramerRole grant select,insert,update on Salary to ProgramerRole 2、创建一个登录账号Testlogin-create login username with password=password 3、创建对应于这个登录账号的数据库用户TestUsercreate user username for login username 4、将用户TestUser添加到TestRole角色中exec sp_addrolemember ProgramerRole,username create role ProgramerRolegrant create table,create procedure,create view to ProgramerRole grant select,insert,update on salary to ProgramerRole create login Testlogin with password=123 create user TestUser for login Testlogin exec sp_addrolemember ProgramerRole,TestUser 4.1触发器实验1.在Student表中编写insert的触发器，假如每个班的学生不能超过30个，如果低于此数，添加可以完成；如果超过此数，则插入将不能实现。2.在SC表上编写update触发器，当修改SC表中的grade字段时将其修改前后的信息保存在SC_log表中。4.2存储过程实验对应于student数据库(1)创建一个无参存储过程StuScoreInfo,查询以下信息:学号,姓名,性别,课程名称,考试成绩.(2)创建一个带参数的存储过程stu_info,该存储过程根据传入的学生编号在student表中查询此学生的信息.(3)创建一个带参数的存储过程StuScoreInfo2,该存储过程根据传入的学生编号和课程名称查询以下信息:姓名,课程名称,考试成绩.(4)编写带参数的存储过程,根据传入的课程名称统计该课程的平均成绩.(5)编写存储过程,根据传入的课程名统计这门课的成绩分布情况,即按照各分数段统计人数.5 数据库备份和恢复以管理员帐号登录SQL Server Management Studio，以原有数据库student为基础，请使用Management Studio界面方式或T-SQL 语句实现以下操作：针对数据库stu创建完全数据库备份集stu.bak，目标磁盘为D：user stu.bak；在数据库stu中新建数据表ceshi，内容自定，然后针对数据库stu创建差异备份；向数据库stu的数据表ceshi插入部分记录，然后针对数据库stu创建事务日志备份；根据需要，将数据库恢复到数据库stu的最初状态；根据需要，将数据库恢复到创建数据表ceshi后的状态；根据需要，将数据库恢复到在ceshi表插入记录后的状态；针对现有数据库stu创建完全文件和文件组备份集stu_file，目标磁盘为D：user stu_file.bak；在当前数据库中新建数据表ceshi2，然后针对数据库stu创建差异文件和文件组备份；向数据库stu的数据表ceshi2插入部分记录，然后针对数据库stu创建事务日志文件和文件组备份；根据需要，将数据库以文件和文件组方式恢复到创建数据表ceshi2后的状态；根据需要，将数据库以文件和文件组方式恢复到数据表ceshi2插入记录后的状态；backup database student TO DISK=e:userstu.bakbackup database student TO DISK=e:userstu.bak with DIFFERENTIALbackup log student TO DISK=e:userstu.bak with noinitrestore database student from DISK=e:userstu.bak with norecoveryrestore database student from DISK=e:userstu.bak with file=2,norecoveryrestore log student from DISK=e:userstu.bak with file=3文件和文件组备份与恢复BACKUP DATABASE student FILE=student,FILEGROUP=primary TO DISK=e:userstu_file.bak BACKUP DATABASE student FILE=student,FILEGROUP=primary TO DISK=e:userstu_file.bak with DIFFERENTIAL backup log student TO DISK=e:userstu_file.bak with noinit RESTORE DATABASE student FILE=student,FILEGROUP=primary FROM DISK=e:userstu_file.bak WITH NORECOVERYRESTORE DATABASE student FILE=student,FILEGROUP=primary FROM DISK=e:userstu_file.bak with file=2,norecoveryRESTORE LOG student FROM DISK=e:userstu_file.bak with file=3
