四川师范大学数据库实验原理实验报告..doc

四川师范大学计算机学院实验报告册院系名称:计算机科学学院课程名称:数据库原理概论实验学期2014年至2015年第二学期专业班级:软件工程4班姓名:刘世麟学号:2013110424指导教师:俞晓实验最终成绩:实验一熟悉SQL SERVER的环境(验证型实验2学时1.目的要求:1了解SQL Server management studio的使用2.实验内容:回答下面每一个问题,写出实验步骤1在“已注册服务器窗口”中注册sql server数据库服务器展开【数据库引擎】节点,右击Local Server Group节点,选择【新建服务器注册】命令,设置相应属性,单击【保存】即可。2在“对象资源管理器”中创建名字为sc的数据库展开节点,右击【数据库】节点,选择【新建数据库】命令,在【数据库名称】后填写sc,设置相应属性,单击【确定】即可。3在sc数据库中创建一个名字为student的基本表展开【数据库】节点,选择数据库并展开,右击表节点,选择【新建表】命令,设置行列信息,保存即可。4在查询窗口中里创建名为S_C的数据库CREATE DATABASE S_C5在查询窗口中使用sql语言创建名字为course的基本表CREATE TABLE course3.主要设备及软件1PC2Microsoft SQL Server2005实验二建立表格,并插入若干记录(验证型实验2学时1.目的要求:1学会使用Create Table和Insert语句2.实验内容1使用sql语言建立student,course和sc共三张表格(包括主键,外码的指定,分析具体情况适当给出一些用户自定义的约束.CREATE TABLE Student(Sno char(9PRIMARY KEY,Sname char(20NOT NULL,Ssex CHAR(2,Sage SMALLINT,Sdept char(20;CREATE TABLE Course(Cno char(4PRIMARY KEY,Cname char(14,Cpno char(4,Ccredit smallint,FOREIGN KEY(CpnoREFERENCES Course(Cno,;CREATE TABLE SC(Sno char(9,Cno char(4,Grade smallint,PRIMARY KEY(Sno,Cno,FOREIGN KEY(SnoREFERENCES Student(Sno,FOREIGN KEY(CnoREFERENCES Course(Cno,;2使用Insert语句向这三张表格里添加至少10条记录(数据如教材56页所示,如果出现错误,分析错误原因INSERT INTO Student VALUES(95001,李勇,男,20,CS;INSERT INTO Student VALUES(95002,刘晨,女,20,IS;INSERT INTO Student VALUES(95003,王敏,女,20,MA;INSERT INTO Student VALUES(95004,张立,男,20,IS;SELECT*FROM Student; INSERT INTO Course(Cno,Cname,CcreditVALUES(1,数据库,4;INSERT INTO Course(Cno,Cname,CcreditVALUES(2,数学,2;INSERT INTO Course(Cno,Cname,CcreditVALUES(3,信息系统,4;INSERT INTO Course(Cno,Cname,CcreditVALUES(4,操作系统,3;INSERT INTO Course(Cno,Cname,CcreditVALUES(5,数据结构,4;INSERT INTO Course(Cno,Cname,CcreditVALUES(6,数据处理,2;INSERT INTO Course(Cno,Cname,CcreditVALUES(7,PASCAL语言,4;UPDATE Course SET Cpno=5WHERE Cno=1;UPDATE Course SET Cpno=1WHERE Cno=3; UPDATE Course SET Cpno=6WHERE Cno=4; UPDATE Course SET Cpno=7WHERE Cno=5; UPDATE Course SET Cpno=6WHERE Cno=7; SELECT*FROM Course; INSERT INTO SC VALUES(95001,1,92; INSERT INTO SC VALUES(95001,2,85; INSERT INTO SC VALUES(95001,3,88; INSERT INTO SC VALUES(95002,2,90; INSERT INTO SC VALUES(95002,3,80; SELECT*FROM SC; 3在“对象资源管理器”中实现(1题中的三张表展开【数据库】节点,单击【表】节点,选择【新建表】,填入如下数据 保存的时候保存为Student就行了展开【数据库】节点,单击【表】节点,选择【新建表】,填入如下数据 保存的时候保存为Course设置外键如图所示 展开【数据库】节点,单击【表】节点,选择【新建表】,填入如下数据 保存的时候保存为Course设置外键如图所示 4在“对象资源管理器”中向这(1题中的三张表添加至少10条记录(数据如教材56页所示,如果出现错误,分析错误原因第一张表中插入数据 第二张表中插入数据 第三张表插入数据 3.主要设备及软件1PC2Microsoft SQL Server2005实验三修改表格结构,修改和删除表格中的数据(验证型实验4学时1.目的要求:1用ALTER语句修改表结构:添加列,修改列定义,删除列。使用UPDATE和DELETE语句修改和删除Student,sc,course表格中的数据。2.实验内容如下所示,创建表s,并完成随后的操作Create table s(sno char(2primary key,sname char(10;1向表中添加属性列status,数据类型为intALTER TABLE s ADD status int;2向表中添加属性列city,数据类型为varchar(20,并限定其取值范围为“上海”,“北京”,“天津”之一ALTER table s ADD city varchar(20;Alter table s add constraint CK_city check(city=上海or city=北京or city=天津;3修改属性列status的数据类型为smallintALTER table s ALTER COLUMN status smallint;4删除(3题中取值范围的约束ALTER table s DROP CK_city;5删除属性列statusALTER table s DROP CK_city;如教材85页所示,建立student,sc,course等表,并插入若干数据,完成如下操作:列出没有成绩的学生的学号和课程号SELECT Sno,Cno FROM sc WHERE Grade IS NULL;列出2号课程成绩在70分到80分学生的学号SELECT Sno FROM sc WHERE Grade=2AND Grade between70AND80;查询所有2005级的学生的姓名,性别和所在系SELECT Sname,Ssex,Sdept FROM student WHERE Sno LIKE2005%;查询计科系2004级全体学生的所有信息SELECT*FROM student WHERE Sdept=jsAND Sno LIKE2004%;查询计科系2006级3班和4班学生的姓名和性别SELECT Sname,Ssex FROM student WHERE Sdept=jsAND(Sno LIKE2006_3%OR Sno LIKE 2006_4%;查询数学系所有学生的姓名,性别和出生年份|SELECT Sname,Ssex,2012-Sage FROM student WHERE Sdept=math;将course表中名为PASCAL语言的课程更名为“C语言”UPDATE Course SET Cname=C语言WHERE Cname=PASCAL;将所有课程的学分增加1分UPDATE Course SET Ccredit=Ccredit+1;删除没有选课成绩的选课记录DELETE FROM sc WHERE Grade IS NULL;删除“IS”系的所有学生信息DELETE FROM student WHERE Sdept=IS;删除所有的课程信息DELETE FROM Course;3.主要设备及软件1PC2Microsoft SQL Server2005实验四查询(多表查询,嵌套查询,分组查询(验证型实验12学时1.目的要求:1实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和HAVING子句实现分组查询.2.实验内容有如下关系模式,分析每个关系模式的主码,外码,完成后面的查询职员表:Emp(eid:integer;ename:string,salary:real部门表:Dept(did:integer,dname:string,managerid:integer,floornum:integer职员与部分的关系表:Works(eid:integer,did:integer;Works表表示:一个职员可以在多个部门工作,一个部门有多个职员Dept表中managerid可以取值null,表示尚未任命部门经理,floornum可以取值null,表示尚未分配工作地点用单表查询完成如下操作:1输出所有员工的姓名和工资SELECT ename,salary FROM Emp; 2输出薪水少于10000或者大于100000的雇员的名字SELECT ename FROM Emp WHERE salary100000; 3输出所有姓“欧阳”,且全名为四个字的雇员的姓名和工资SELECT ename,salary FROM Emp WHERE ename LIKE欧阳_; 4输出薪水在20000和50000之间的雇员的名字SELECT ename FROM Emp WHERE salary BETWEEN20000AND50000; 5输出部门名字中含有“_”的所有部门的名字和楼层号SELECT dname,floornum FROM Dept WHERE dname LIKE%_%ESCAPE; 6查询公司的员工数SELECT COUNT(*FROM Emp; 7查询所有还没有部门经理的部门的名字和编号SELECT dname,did FROM Dept WHERE managerid is NULL;8查询所有已分配楼层的部门的所有信息SELECT*FROM Dept WHERE floornum is NOT NULL; 用连接查询完成如下操作:1查询“电视”部门的职工人数SELECT COUNT(didFROM Works WHERE Works.did=(SELECT did FROM Dept WHERE dname=电视; 2输出每个部门的名字和平均工资select dname,avg(salaryfrom emp,dept,works where emp.eid=works.eid and dept.did=works.did group by dname; 3查询每个部门的部门编号,及其拥有的雇员的人数select Dept.did,Count(eidfrom Dept,Works WHERE Dept.did=Works.did GROUP BY Dept.did; 4查询在第10层工作,同时薪水少于￥50000的所有雇员的名字SELECT DISTINCT ename FROM Emp,Dept,Works WHERE Emp.eid=Works.eid AND Works.did=Dept.did AND Dept.floornum=10AND salary50000; 5输出同时管理三个或者更多部门的管理者的名字select ename from dept,emp where dept.managerid=emp.eid group by managerid,ename having count(did=3; 6输出管理在同一层上10个以上部门的所有管理者的名字SELECT ename FROM Dept,Emp WHERE Emp.eid=Dept.managerid group by managerid, floornum,ename having COUNT(did10; 7输出雇员“刘丽”工作的部门的名字SELECT dname FROM Emp,Dept,Works WHERE Emp.ename=刘丽AND Emp.eid=Works.eid AND Works.did=Dept.did; 用嵌套查询完成如下操作:1查询工资最高的雇员的名字SELECT ename FROM Emp WHERE salary=(SELECT MAX(salaryFROM Emp; 2查询工资最低的雇员的名字及其所在部门的编号和名字SELECT ename,Dept.did,dname FROM Emp,Dept,Works WHERE salary=(SELECT MIN(salary FROM EmpAND Emp.eid=Works.eid AND Works.did=Dept.did; 3输出与Santa工作部门相同的所有雇员的所有信息SELECT Emp.eid,ename,salary FROM Emp,Dept,Works WHERE Emp.eid=Works.eid AND Works.did=Dept.did AND Dept.did in(SELECT did FROM Works WHERE eid=(SELECT eid FROM Emp WHERE ename=Santa; 4找出薪水在20000以上,并且在电视部门或者玩具部门工作的雇员的名字SELECT DISTINCT ename FROM Emp,Dept,Works WHERE Emp.eid=Works.eid AND Works.did =Dept.did AND salary20000AND dname in(SELECT dname FROM Dept WHERE dname=电视or dname=玩具; 5输出与刘丽在同一层工作的雇员的名字SELECT DISTINCT ename FROM Emp,Dept,Works WHERE Emp.eid=Works.eid AND Works.did =Dept.did AND floornum in(SELECT DISTINCT floornum FROM Dept,Emp,Works WHERE Dept.did=Works.did AND Works.eid=Emp.eid AND Emp.ename=刘丽; 6输出比所在部门的经理挣的还要多的雇员的名字SELECT DISTINCT ename FROM Emp,Dept,Works WHERE salaryAll(SELECT salary FROM Emp,Dept WHERE Emp.eid=Dept.managerid AND Emp.eid=Works.eid AND Works.did=Dept.did AND ename in(SELECT ename FROM Emp; 7输出满足如下条件的各个部门的名字:经理的姓为张,同时他的薪水既不是本部门最高也不是最低SELECT DISTINCT dname FROM Works,Dept,emp where Dept.managerid=emp.eid and ename like张%AND salary(SELECT min(salaryfrom emp where eid in(select eid from works where works.did=dept.did; 8输出比“玩具”部门所有职工工资都高的雇员的姓名SELECT ename FROM Emp WHERE salary(SELECT max(salaryFROM Emp,Works,Dept WHERE Emp.eid=Works.eid AND Works.did=Dept.did AND Dept.dname=玩具; 9输出比“电视”部门职工平均工资高的雇员的姓名SELECT ename FROM Emp WHERE salary(SELEcT AVG(salaryFROM Emp,Works,Dept WHERE Emp.eid=Works.eid AND Works.did=Dept.did AND Dept.dname=电视GROUP BY Dept.did; 10找出所有有职工的部门的名字和楼层号SELECT dname,floornum FROM Dept WHERE did in(SELECT DISTINCT did FROM Works; 11查询所有没有职工的部门编号和名字SELECT did,dname FROM Dept WHERE did not in(SELECT DISTINCT did FROM Works; 12输出同时在玩具部门和糖果部门工作的雇员的名字和薪水SELECT DISTINCT ename,salary FROM Emp,Works,Dept WHERE Emp.eid=Works.eid AND Works.eid in(SELECT eid FROM Works,Dept WHERE Works.did=Dept.did AND Dept.dname=玩具AND Works.eid in(SELECT eid FROM Works,Dept WHERE Works.did=Dept.did AND Dept.dname=糖果; 3.主要设备及软件1PC2Microsoft SQL Server2005实验五为表格建立约束,修改约束和查询约束(验证型实验4学时1.目的要求:1使用ALTER语句和CREATE语句建立、修改、删除和查询约束2.实验内容:执行以下SQL语句,完成随后的操作,若有错误,分析错误原因并改正错误:create table student(sno char(9not null,sname char(10,ssex char(2,sage tinyint,sdept varchar(40;create table course(cno char(4not null,cname varchar(30,cpno char(4,credit tinyint create table sc(sno char(9,char(5,grade numeric(3,1;出错第三句改为create table sc(sno char(9,cno char(5,grade numeric(3,11在student表中,使sdept只能取值“计算机科学学院”,“数软学院”,“电子工程学院”,“化学与材料科学学院”Alter table student add constraint CK_sdept check(sdept=计算机科学学院or sdept=数软学院or sdept=电子工程学院or sdept=化学与材料科学学院;2在student表中sage有默认值18ALTER TABLE student ADD DEFAULT(18FOR sage;3为student表建立主键ALTER TABLE student ADD PRIMARY KEY(Sno;4为course表建立主键和外键,其中外键约束名为C_FK_CPNOALTER TABLE course ADD primary key(cno,C_FK_CPNO CHAR(4FOREIGN KEY(cpno REFERENCES course(cno;5为course表建立检查约束,限定credit的取值只能取3,2,4,5;ALTER TABLE course ADD CHECK(credit in(3,2,4,5;6为course表建立唯一约束,确保每们课程名字唯一ALTER TABLE course ADD UNIQUE(cname;7为sc表建立主键和外键,并给出相应的约束名ALTER TABLE sc ADD constraint PK_sno_cno PRIMARY KEY(sno,cno,FK_sno CHAR(9 FOREIGN KEY(snoREFERENCES student(sno,FK_cno CHAR(4FOREIGN KEY(cnoREFERENCES course(cno;8在course表中插入元组(1,数据库,5,4和(2,数学,null,2,若不能正确插入,分析原因,给出解决办法不能正确插入,因为数据类型不对,更改方案INSERT INTO course(cno,cname,cpno,creditVALUES(1,数据库,5,4;INSERT INTO Course(cno,cname,cpno,creditVALUES(2,数学,null,2;若前面的题中建立了约束关系,那删掉9在sc表中插入元组(95001,1,92和(95001,3,88,若不能正确插入,分析原因,给出解决办法INSERT INTO Course(cno,cpno,creditVALUES(95001,1,92;INSERT INTO Course(cno,cpno,creditVALUES(95001,3,88;若前面的题中建立了约束关系,那删掉使用系统存储过程,sp_help,sp_helpconstraint等对约束进行查询和管理1查阅联机帮助文档,选中“索引”选项卡,分别输入sp_help,sp_helpconstraint,阅读其帮助信息。使SC数据库成为当前数据库,执行如下命令,简要解释执行结果Exec sp_helpsp_help过程仅在当前数据库中查找对象。Exec sp_help student列出student中所有对象的对象名称、所有者和对象类型。Exec sp_help course列出course中所有对象的对象名称、所有者和对象类型。Exec sp_helpconstaint scExec sp_helpconstraint student3对存储过程sp_help和sp_helpconstraint进行总结,简要解释其用途sp_help:报告有关数据库对象(sys.sysobjects兼容视图中列出的所有对象、用户定义数据类型或SQL Server2008提供的数据类型的信息。sp_helpconstraint:返回一个列表,其内容包括所有约束类型、约束类型的用户定义或系统提供的名称、定义约束类型时用到的列,以及定义约束的表达式(仅适用于DEFAULT和CHECK约束。修改约束:关闭和打开某个约束。1在course表中插入元组(4,数据结构,6,4,能够插入吗?为什么?不能,首先数据格式不定,其次6不符合外键约束2禁用约束C_FK_CPNO,再次插入元组(4,数据结构,6,4,能够插入吗?为什么?可以插入,因为约束已经删除3重新启用约束C_FK_CPNOALTER TABLE course check constraint FK_course_C_FK_CPN_0F975522;3.主要设备及软件1PC2Microsoft SQL Server2005实验六Sql Server数据库安全管理(验证型实验4学时1.目的要求:1建立用户,为用户赋权限,收回权限,建立角色,给用户赋角色2.实验内容:分别在对象资源管理器和查询窗口中完成下列操作1创建建登录帐号john,并使其成为固定服务器角色dbcreator的一个成员CREATE LOGIN john WITH PASSWORD=123456;2授予john服务器权限:alter any login,create any database张开【系统数据库】节点,选择【master】数据库,新建查询执行grant alter any login,create any database to john;3John可以创建登录帐号吗?为什么?,若能,创建安全登录帐号帐户Mary可以,因为上题已经赋予了alter any login的权限CREATE LOGIN mary WITH PASSWORD=123456;4创建用户Mary123,使用安全登录帐号Mary展开【系统数据库】节点,选择【master】数据库,新建查询执行GRANT ALTER ANY LOGIN TO mary;create user Mary123for login Mary;5授予帐户Mary123查询和修改student表的权限GRANT SELECT,ALTER on student TO Mary123;6创建角色teachers,拒绝teachers修改student表CREATE ROLE teachers;DENY SELECT ON OBJECT:studentTO teachers;7使帐户Mary123成为teachers的一个成员,Mary123能够查询和修改student表吗?为什么?EXEC sp_addrolememberteachers,Mary123能,因为teachers具有查询和修改的权限;而Mary123是teachers的一员。8如果希望角色teachers能够修改student表,应该怎么做?GRANT ALTER ON student TO teachers;9授予帐户Mary123在数据库sc中创建表和创建视图的权限GRANT ALTER ON schema:dbo TO mary123GRANT CREATE TABLE,CREATE VIEW To Mary123;10若mary123分别是固定数据库角色db_accessadmin,db_datareader和db_denydatawriter的成员,它有何能力,解释原因。db_accessadmin:可以添加或删除用户ID。db_datareader:可以选择数据库内任何用户表中的所有数据db_denydatawriter:可以更改数据库内任何用户表中的所有数据3.主要设备及软件1PC2Microsoft SQL Server2005实验七使用ADO连接数据库(综合型实验4学时1.目的要求:1在高级语言中通过ADO或者ADO.NET连接SQL SERVER数据库,做一些简单应用2.实验内容:在高级语言中使用ADO控件(ADO.NET控件或ADO对象(ADO.NET对象连接SQL SERVER数据库,实现对表格的基本操作,能与数据库交互,包括数据查询,元组的添加,删除和修改。建立一个C#窗体应用程序,使用ADO.NET访问数据库建立数据表use dbCREATE TABLE test(name char(10,city char(10,;插入数据INSERT INTO test VALUES(Neal,New York;程序主界面 加载数据: 实现原理:public void button1_Click(object sender,EventArgs e string readSql=select name,city from test;string connectString=System.Configuration.ConfigurationSettings.AppSettingsDB;SqlConnection connection=newSqlConnection(connectString;SqlCommand command=new SqlCommand(readSql, connection;connection.Open(;SqlDataReader reader=command.ExecuteReader(;Book.CControl.AddListViewByDataReader(this.listView1,reader;connection.Close(;添加记录:按 button1 后界面如下 实现原理： private void button1_Click(object sender, EventArgs e nameStr = this.textBox1.Text; cityStr = this.textBox2.Text; string insertSql = string.Format(INSERT INTO test(name,cityVALUES(0,1, new object nameStr, cityStr ; Console.WriteLine(insertSql; string connectString = server = localhost; integrated security = sspi; database = db; SqlConnection connection = new SqlConnection(connectString; SqlComm