实验解答.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,数据库实验解答,实验,2.2,练习下面简单的查询语句：,a),查询每个雇员的所有信息,select*from Employee,b),查询每个雇员的地址和电话,select mployeeID,Address,PhoneNumber from Employee,c),查询,EmployeeID,为,000001,的雇员的地址和电话。,select Address,PhoneNumber from Employee,where EmployeeID=000001,实验,2.2,练习下面简单的查询语句：,d),查询女雇员地址和电话，并用,AS,子句将结果中各列的标题分别指定为“地址”和“电话”。,select EmployeeID,Address as,地址,PhoneNumber as,电话,from Employee,where sex=false,e),计算每个雇员的实际收入。,select EmployeeID,Income-OutCome from Salary,f),找出所有姓王的雇员的部门号。,select distinct DepartmentID from Employee where Name like,王,%,3,、练习多表连接查询和嵌套查询：,查询每个雇员的情况及工资情况（工资,=Income-Outcome,）,select Employee.*,Income-Outcome,from Employee,Salary,where Employee.EmployeeID=Salary.EmployeeID,查询财务部工资在,2200,元以上的雇员姓名及工资情况,select Name,Income-Outcome as,工资,from Employee,Salary,Departments,where Employee.EmployeeID=Salary.EmployeeID,and Employee.DepartmentID=Departments.DepartmentID,and Income-Outcome2200,3,、练习多表连接查询和嵌套查询：,查询研发部在,1966,年以前出生的雇员姓名及其工资详情,select Name,Income-Outcome as,工资,from Employee,Salary,Departments,where Employee.EmployeeID=Salary.EmployeeID,and Employee.DepartmentID=Departments.DepartmentID,and DepartmentName=,研发部,and year(Birthday)2500,求财务部雇员的总人数,select count(*),from Employee,Departments,where Employee.DepartmentID=Departments.DepartmentID,and DepartmentName=,财务部,求财务部雇员的平均工资,select avg(Income-Outcome),from Employee,Salary,Departments,where Employee.EmployeeID=Salary.EmployeeID,and Employee.DepartmentID=Departments.DepartmentID,and DepartmentName=,财务部,查找比所有财务部的雇员工资都高的雇员的姓名,select Name,from Employee,Salary,where Employee.EmployeeID=Salary.EmployeeID,and Income-Outcome(,select,max,(Income-Outcome),from Employee,Salary,Departments,where Employee.EmployeeID=Salary.EmployeeID,and Employee.DepartmentID=Departments.DepartmentID,and 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 test,select,sno,sum(qty,),from,spj,group by,sno,4,将,P,表中,PNO,值为,p6,的元组的,color,属性值改为绿，,weight,属性值改为,60,5,将,SPJ,表中前,4,个元组的,qty,属性值统一修改为,300,update top(4),spj,set qty=300,6,将,S,表中,city,属性名含有“京”或“津”的相应,status,属性值增加,100,2.4 SQL,数据更新语句,7,将供应商,s2,为“一汽”工程项目所供应的零件数量修改为,2000,8,将全部红色零件的颜色修改为浅红色,9,由,s5,供给,j4,的零件,p6,改为由,s3,供应,10,在,SPJ,表中新增一名为,SDATE,的属性列，对该表中的每一元组在,SDATE,属性列上填上实验当时的日期和时间,alter table SPJ,add SDATE,datetime,update,spj,set SDATE=,getdate,(),11,删除所在城市为“广州”的供应商记录,12,删除所有零件名称中第一个字为“螺”字的零件记录，并在供应情况表中删除相应的记录,13,删除,s3,和,s4,两供应商为“三建”工程供应“螺母”或“螺丝刀”零件的相应供应情况数据信息,3.1,数据完整性管理,1,、建立课程的实体完整性和课程号,cno,的参照完整性,;,alter table J,ADD primary key(jno),ADD foreign key(jno)references j(jno),2,、对,HRM,数据库，练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定义的约束（参考,HRM,数据库表定义图中说明）；,3,、建立,salary,表的,Income,字段限定在,0-9999,之间。,3.2,数据库安全性管理,1,、创建角色,ProgramerRole,，拥有创建表，存储过程，视图权限，拥有对,Salary,表的查询、修改、插入权限,2,、创建一个登录账号,Testlogin,3,、创建对应于这个登录账号的数据库用户,TestUser,4,、将用户,TestUser,添加到,TestRole,角色中,1,、创建角色,ProgramerRole,，拥有创建表，存储过程，视图权限，拥有对,S,表的查询、修改、插入权限,create role,ProgramerRole,grant 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,、创建对应于这个登录账号的数据库用户,TestUser,create user username for login username,4,、将用户,TestUser,添加到,TestRole,角色中,exec,sp_addrolemember,ProgramerRole,username,create role,ProgramerRole,grant 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.bak,backup database student TO DISK=,e:userstu.bak,with DIFFERENTIAL,backup log student TO DISK=,e:userstu.bak,with,noinit,restore database student from DISK=,e:userstu.bak,with,norecovery,restore database student from DISK=,e:userstu.bak,with file=2,norecovery,restore 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 NORECOVERY,RESTORE DATABASE student,FILE=student,FILEGROUP=primary,FROM DISK=,e:userstu_file.bak,with file=2,norecovery,RESTORE LOG student,FROM DISK=,e:userstu_file.bak,with file=3,