实验一到实验七Word文件下载.docx
《实验一到实验七Word文件下载.docx》由会员分享,可在线阅读,更多相关《实验一到实验七Word文件下载.docx(17页珍藏版)》请在冰豆网上搜索。
6))
3.主要仪器设备及软件:
(1)PC
(2)MicrosoftSQLServer2005
实验二建立表格,并插入若干记录
1.目的要求:
学会使用CreateTable语句和Insert语句
2.实验内容:
1)使用sql语言建立student,course和sc共三张表格(包括主键,外码的指定),
CREATETABLEStudent(
Snochar(9)primarykey,
Snamechar(40),
Ssexchar
(2),
Sagesmallint,
Sdepe[char](20),
);
CREATETABLESC
SnoCHAR(9),
CnoCHAR(4),
GraditSMALLINT,
PRIMARYKEY(Sno,Cno),
FOREIGNKEY(Sno)REFERENCESStudent(Sno),
FOREIGNKEY(Cno)REFERENCESCourse(Cno),
2)
3)分析具体情况适当给出一些用户自定义的约束.
4)使用Insert语句向这四张表格里添加至少10条记录(数据如教材56页所示),如果出现错误,分析错误原因
INSERTINTO[student].[dbo].[st]
([Sno]
[Sname]
[Ssex]
[Sage])
VALUES
('
1'
'
lili'
'
2'
在这里我只写了在一个表中加入一个记录的语句,而其他表中的,插入数据,语句,和上述语句相同。
5)在“对象资源管理器”中实现
(1)题中的三张表
6)单击资源对象管理器,选择资源对象管理器中的数据库,,然后单击数据库,选择新建数据库,在对话框中填写数据库的名称,便可以建立一个数据库,然后建立表的算法,和1,2题的算法一样算法如下:
7)
8)在“对象资源管理器”中向这
(1)题中的三张表添加至少10条记录(数据如教材
单击对象资源管理器下的数据库,然后再将数据库展开,寻找表,单击右键选择,修改表,insert到新建查询窗口,便可以对表进行插入数据,其插入数据的格式为,
INSERTINTO[sc1].[dbo].[Course]
([Cno]
[Cname]
[Cpno]
[Ccredit])
7'
'
数据库'
2)
9)这里我只对一张表进行了修改,插入了一个内容,其余表的插入内容的方法与上面的5
10)56页所示),如果出现错误,分析错误原因
3.主要仪器设备及软件:
实验三修改表格结构,修改和删除表格中的数据
(验证型实验4学时)
用ALTER语句修改表结构:
添加列,修改列定义,删除列。
使用UPDATE和DELETE语句修改和删除Student,sc,course表格中的数据。
如下所示,创建表s,并完成随后的操作
Createtables(snochar
(2)primarykey,snamechar(10));
1)向表中添加属性列status,数据类型为int
altertablestudentaddstatusint
2)向表中添加属性列city,数据类型为varchar(20),并限定其取值范围为“上海”,“北京”,“天津”之一
3)
4)修改属性列status的数据类型为smallint
5)altertablestudentaltercolumnstatussmallint
6)删除(3)题中取值范围的约束
8)删除属性列status
altertablestudentdropcolumnstatus
如教材85页所示,建立student,sc,course等表,并插入若干数据,完成如下操作:
1)列出没有成绩的学生的学号和课程号
select*fromscwheregrade=0
2)列出2号课程成绩在70分到80分学生的学号
select*fromscwherecno='
andgrade>
=70andgrade<
=80
3)查询所有2005级的学生的姓名,性别和所在系
4)selectSname,Ssex,SdepefromStudentwhereSsyear=2005;
5)
6)查询计科系2004级全体学生的所有信息
select*fromstudentwheressyear=2004andsdepe='
cs'
7)查询计科系2006级3班和4班学生的姓名和性别
selectSname,SsexfromStudentwhereSUBSTRING(Sno,1,6)='
200603'
orSUBSTRING(Sno,1,6)='
200604'
8)查询所有以“数”打头的课程的名称和学分
selectCname,CcreditfromCoursewhereCnamelike'
数%'
9)查询数学系所有学生的姓名,性别和出生年份
selectSname,ssex,SagefromStudentwhereSdepe='
ma'
由于建表时没有建立出身年月,所以我将出生年月改成了,学生的实际年龄
将course表中名为PASCAL语言的课程更名为“C语言”
updateCoursesetCname='
C语言'
whereCname='
PASCALL'
10)将所有课程的学分增加1分
updateCoursesetCcredit=Ccredit+1
11)删除没有选课成绩的选课记录
deletefromscwherecnoisnull
12)删除“IS”系的所有学生信息
deletefromStudentwhereSdepe='
IS'
;
13)删除所有的课程信息
Sp_constraintsc
altertablescdropconstraint
Deletefromcourse
实验四查询(多表查询,嵌套查询,分组查询)
(验证型实验12学时)
实现单表和多表的普通查询和嵌套查询。
包括返回单值的子查询和返回多值的子查询。
使用5个聚合函数以及GROUPBY子句和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)输出所有员工的姓名和工资
selectename,salaryfromemp
2)输出薪水少于10000或者大于100000的雇员的名字
3)selectenamefromempwheresalary<
10000orsalary>
100000
4)输出所有姓“欧阳”,且全名为四个字的雇员的姓名和工资
selectename,salaryfromempwhereenamelike'
欧阳__'
5)输出薪水在20000和50000之间的雇员的名字
selectenamefromempwheresalarybetween20000and50000
6)输出部门名字中含有“_”的所有部门的名字和楼层号
selectdname,floornumfromdeptwherednamelike'
%\_%'
escape'
\'
7)查询公司的员工数
selectcount(*)fromemp
8)查询所有还没有部门经理的部门的名字和编号
selectdid,dnamefromdeptwheremanageridisnull
9)
10)查询所有已分配楼层的部门的所有信息
select*fromdeptwherefloornumisnotnull
用连接查询完成如下操作:
1)查询“电视”部门的职工人数
selectCOUNT(*)astotalfromdept,workswheredept.did=works.didanddname='
电视'
2)输出每个部门的名字和平均工资
selectdname,AVG(salary)fromdept,emp,workswhereworks.eid=emp.eidandworks.did=dept.didgroupbydname
3)查询每个部门的部门编号,及其拥有的雇员的人数
selectdid,COUNT(eid)fromworksgroupbydid
由于在works表中只输入了四个部门的信息,所以,输出结果中,也只有四个部门的信息
4)查询在第10层工作,同时薪水少于¥50000的所有雇员的名字
5)selectenamefromemp,dept,workswhereworks.eid=emp.eidandworks.did=dept.didandfloornum='
10'
andsalary<
50000
6)输出同时管理三个或者更多部门的管理者的名字
7)输出管理在同一层上10个以上部门的所有管理者的名字
8)输出雇员“李丽丽”工作的部门的名字
selectdnamefromemp,dept,workswheredept.did=works.didandemp.eid=works.eidandename='
李丽丽'
用嵌套查询完成如下操作:
1)查询工资最高的雇员的名字
selectenamefromempwheresalary>
=(selectMAX(salary)fromemp)
2)查询工资最低的雇员的名字及其所在部门的编号和名字
selectename,works.did,dnamefromemp,dept,workswheresalary<
=(selectMIN(salary)fromemp)andworks.eid=emp.eidanddept.did=works.did
3)输出与Santa工作部门相同的所有雇员的所有信息
selectename,eid,salaryfromempwhereeidin(selecteidfromworkswheredidin(selectdidfromworkswhereeid=(selecteidfromempwhereename='
santa'
)))
4)找出薪水在20000以上,并且在电视部门或者玩具部门工作的雇员的名字
selectenamefromempwhereexists(selectdidfromdeptwherednamein('
电视部'
玩具部'
)andemp.salary>
=20000)
5)输出与刘丽在同一层工作的雇员的名字
selectenamefromempwhereexists(selectfloornumfromdeptwheredidin(selectdidfromworkswhereeidin(selecteidfromempwhereename='
)))
6)输出比所在部门的经理挣的还要多的雇员的名字
selectenamefromempwheresalary>
(selectsalaryfromempwhereeidin(selectmanageridfromdeptwheremanageridisnotnull))
7)输出满足如下条件的各个部门的名字:
经理的姓为张,同时他的薪水既不是本部门最高也不是最低
selectdnamefromdept,emp,workswheremanageridin(selectmanageridfromdeptwheremanageridin(selecteidfromempwhereenamelike'
张%'
)andsalary>
(selectmin(salary)fromemp)andsalary<
(selectMAX(salary)fromemp))
8)输出比“玩具”部门所有职工工资都高的雇员的姓名
(selectall(salary)fromempwhereeidin(selecteidfromworkswheredidin(selectdidfromdeptwheredname='
)))
10)输出比“电视”部门职工平均工资高的雇员的姓名
(selectAVG(salary)fromempwhereeidin(selecteidfromworkswheredidin(selectdidfromdeptwheredname='
11)找出所有有职工的部门的名字和楼层号
selectdname,floornumfromdeptwheredidin(selectdidfromworkswhereeidisnotnull)
12)查询所有没有职工的部门编号和名字
selectdid,dnamefromdeptwheredidin(selectdidfromworkswhereeidisnull)
13)输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
14)selectename,salaryfromempwhereeidin(selecteidfromworkswheredidin(selectdidfromdeptwherednamein('
糖果部'
'
玩具'
实验五为表格建立约束,修改约束和查询约束
使用ALTER语句和CREATE语句建立、修改、删除和查询约束
执行以下SQL语句,完成随后的操作,若有错误,分析错误原因并改正错误:
Createtablestudent(snochar(9)notnull,snamechar(10),ssexchar
(2),sagetinyint,sdeptvarchar(40));
Createtablecourse(cnochar(4)notnull,cnamevarchar(30),cpnochar(4),credittinyint);
Createtablesc(snochar(9),char(5),gradenumeric(3,1));
执行第三条时,出现了错误。
原因是数据类型char(5)前面,没有属性名,加上属性名后就可以成功执行
1)在student表中,使sdept只能取值“计算机科学学院”,“数软学院”,“电子工程学院”,“化学与材料科学学院”
altertablestudentaddconstraintacheck(sdeptin('
计算机科学学院'
数软学院'
电子工程学院'
化学与材料科学学院'
))
2)在student表中sage有默认值18
altertablestudentdropcolumnsage
altertablestudentaddsagevarchar(10)default(18)
3)为student表建立主键
altertablestudent1addconstraintstudentkeyprimarykey(sno)
4)为course表建立主键和外键,其中外键约束名为C_FK_CPNO
altertablecourse1addconstraintcoursekeyprimarykey(cno)
altertablecourse1addconstraintC_FK_CPNOforeignkey(cpno)referencescourse1(cno)
5)为course表建立检查约束,限定credit的取值只能取3,2,4,5;
altertablecourse1dropcolumncredit
altertablecourse1addcreditsmallintconstraintC1check(creditin(2,3,4,5))
6)为course表建立唯一约束,确保每们课程名字唯一
altertablecourse1dropcolumncname
altertablecourse1addcnamechar(20)unique
7)为sc表建立主键和外键,并给出相应的约束名
8)在course表中插入元组(1,数据库,5,4)和(2,数学,null,2),若不能正确插入,分析原因,给出解决办法
第一个不能正确插入,因为第一个的先选课程是5号课程,但是课程表中根本就没有5号课程的信息,所以无法将5号课程作为先选课插入到选课表中.解决办法是:
先把1号课程的先选课设置为空,等到5号课程的记录插入了以后,再修改一号课程的先选课程的信息,就可以了,由于,一号课程没有插入,所以2号课程没法插入到课程表中,要想插入二号课程,就必须先插入一号课程的信息
10)在sc表中插入元组(95001,1,92)和(95001,3,88),若不能正确插入,分析原因,给出解决办法
不能插入,因为,sc的外码是课程号,而
(2)使用系统存储过程,sp_help,sp_helpconstraint等对约束进行查询和管理
查阅联机帮助文档,选中“索引”选项卡,分别输入sp_help,sp_helpconstraint,阅读其帮助信息。
使SC数据库成为当前数据库,执行如下命令,简要解释执行结果
1)Execsp_help
2)Execsp_helpstudent
3)Execsp_helpcourse
4)Execsp_helpconstaintsc
5)Execsp_helpconstraintstudent
6)对存储过程sp_help和sp_helpconstraint进行总结,简要解释其用途
(3)修改约束:
关闭和打开某个约束。
1)在course表中插入元组(4,数据结构,6,4),能够插入吗?
为什么?
2)禁用约束C_FK_CPNO,再次插入元组(4,数据结构,6,4),能够插入吗?
3)重新启用约束C_FK_CPNO
实验六SqlServer数据库安全管理
建立用户,为用户赋权限,收回权限,建立角色,给用户赋角色
分别在对象资源