数据库系统sql实验.docx
- 文档编号:23821803
- 上传时间:2023-05-21
- 格式:DOCX
- 页数:31
- 大小:248.61KB
数据库系统sql实验.docx
《数据库系统sql实验.docx》由会员分享,可在线阅读,更多相关《数据库系统sql实验.docx(31页珍藏版)》请在冰豆网上搜索。
数据库系统sql实验
创立架构
架构是一个对象的容器,可以包含多个数据表,存储过程和视图等根底对象,SQLServer2021采用架构别离的方式,增强了管理对象的灵敏性。
架构创立有两种方式:
1、图形化界面
选择“数据库〞,→架构→角色→配置权限
2、命令
CreateSchema架构名称〔选择数据库已分类定义过〕Authorization用户名或角色
例,先创立用户“李雪0501〞
后创立架构
createschemaadminauthorization李雪0501
删除架构
dropschemaadmin
1数据定义
1、定义形式
在查询窗口输入,
[例1]createdatabasetestStudCour
创立数据库testStudCour,在对象资源管理器中的目录“数据库〞单击右键“刷新〞,出现“testStudCour〞数据库
[例2]createdatabase[testStudCour2]onprimary
(name=N'lixuetestStudCour',filename=N'd:
\lixuetestStudCour.mdf',size=3072KB,maxsize=unlimited,filegrowth=1024KB)
LOGON
(name=N'lixuetestStudCour_log',filename=N'd:
\lixuetestStudCour_log.ldf',size=1024KB,maxsize=2048GB,filegrowth=10%)
[例3]createdatabasetestStudCour3
CreatetabletestStudCour3.TabStudCour(
学号nchar(9)primarykey,
姓名nchar(10)NULL,
性别nchar
(2)NULL,
系别nchar(20)NULL,)
消息2760,级别16,状态1,第8行
指定的架构名称"testStudCour3"不存在,或者您没有使用该名称的权限。
修改:
先执行createdatabasetestStudCour3
在执行CreatetabletestStudCour3.dbo.TabStudCour(
学号nchar(9)primarykey,
姓名nchar(10)NULL,
性别nchar
(2)NULL,
系别nchar(20)NULL,)
2、删除形式
[例4]dropdatabasetestStudCour2
删除数据库testStudCour,在对象资源管理器中的目录“数据库〞单击右键“刷新〞,“testStudCour〞数据库被删除
[例5]dropdatabasetestStudCour3Restrict
消息156,级别15,状态1,第13行
关键字'Restrict'附近有语法错误。
[例6]dropdatabasetestStudCour3cascade
消息156,级别15,状态1,第13行
关键字'cascade'附近有语法错误。
1.2根本表的定义、删除与修改
定义根本表
先执行createdatabasestud_cour
后执行
[例7]createtable[stud_cour].[dbo].student(snonchar(9)primarykey,
snamenchar(20)notnull,
ssexnchar
(2),
Sageint,
sdeptnchar(20))
[例8]createtable[stud_cour].[dbo].Course(Cnonchar
(2)primarykey,
Cnamenchar(10)notnull,
Cpnonchar
(2),
Ccreditsmallint)
[例9]createtable[stud_cour].[dbo].SC(Snonchar(9),
Cnonchar
(2),
Gradesmallint,
primarykey(Sno,Cno),
foreignkey(sno)referencesStudent(sno),
foreignkey(cno)referencesCourse(cno))
修改根本表
[例10]altertablestud_cour.dbo.studentaddS_inschoolDate
[例11]altertablestud_cour.dbo.studentalterColumnssexsmallint
[例12]altertablestud_cour.dbo.courseaddunique(cname)
删除根本表
[例13]droptablestud_cour.dbo.course
消息3726,级别16,状态1,第2行
[例14]
先执行createtable[stud_cour_test].[dbo].Course(Cnonchar
(2)primarykey,
Cnamenchar(10)notnull,
Cpnonchar
(2),
Ccreditsmallint)
再执行
[例15]droptable[stud_cour_test].[dbo].Course
索引的建立与删除
索引的建立
选择要建立索引的数据库对象“stud_cour〞
[例16]createindexsno_idxonstudent(sno)
建立的索引文件可以在student表中的“索引〞项中可以看见。
[例17]createuniqueindexsname_idxonstudent(sname)
索引的删除
[例18]dropindexsno_idx
消息159,级别15,状态1,第6行
必须为DROPINDEX语句指定表名和索引名。
[例19]dropindexsno_idxonstudent
删除的索引文件可以在student表中的“索引〞项中可以看见。
[例20]dropindexsname_idxonstudent
2数据查询
2.1单表查询
[例6]查询全体学生的学号与姓名
selectsno,snamefromstudent
查询结果
[例6]查询全体学生的姓名、学号、所在系
SELECT*FROMstudent
[例6]查经过计算的值(查询学生姓名和出生年份)
SELECTSno,2021-sageASSDATEFROMstudent
[例6]查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名
SELECTSno,'YearofBirth:
',2021-Sage,LOWER(sdept)FROMstudent
SELECTSno,'YearofBirth:
''YearofBirth:
',2021-Sage出生年份,LOWER(sdept)所在院系FROMstudent
SELECTsnoNAME,'YearofBirth:
'BIRTH,2021-sageBIRTHDAY,LOWER(sdept)DEPARTMENTFROMStudent
选出所有选课的学生学号
SELECTDISTINCTSNOFROMSC
•查计算机系全体学生名单
SELECTsnameFROMstudentWHEREsdept='cs'
查年龄20岁以下学生姓名及年龄
SELECTsno,sageFROMstudentWHEREsage<20
查年龄在18—20间学生姓名及年龄
SELECTsname,sageFROMStudentWHEREsageBETWEEN18AND20
查IS,MA,CS三个系学生姓名和性别
SELECTsname,ssexFROMstudentWHEREsdeptIN('IS','MA','CS')
查既不是IS,MA,也不是CS系的学生姓名和性别
SELECTsname,ssexFROMstudentWHEREsdeptNOTIN('IS','MA','CS')
学号为200215121的学生情况
SELECT*FROMstudentWHERESNOLIKE'200215121'
查姓刘学生姓名性别
SELECTsname,ssexFROMstudentWHEREsnameLIKE'刘%'
.查年龄在20—29间的学生姓名和年龄
Selectsname,sagefromstudentwheresagelike'2_'
cs系年龄在以下的学生姓名
Selectsnamefromstudentwheresdept='cs'andsage<20
查询DB_Design课程的课程号和学分。
SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\'
选修了3号课程的学生的学号及成绩按成绩降序排列:
Selectsno,gradefromscwherecno=3orderbygradedesc;
查全体学生情况,按所在系升序排列,同一系按年龄降序排列
Select*fromstudentorderbysdept,sagedesc
查询课程号为2且成绩高于分以上的学生
SELECT*FROMScWHERECno='2'ANDGrade>85
查询选修1或2且不低于分的学生
SELECT*FROMScWHERE(Cno='1'ORCno='2')ANDGrade>=70
查选修了3号课程的学生的平均成绩
Selectavg(grade)平均分fromscwherecno=3;
求200215121学生的总分和平均分
SELECTSUM(Grade)总分,AVG(Grade)平均分FROMScWHERESno='200215121'
求计算机系的学生总数
SELECTCOUNT(Sno)计算机系人数FROMStudentWHERESDept='CS'
或
SELECTCOUNT(*)计算机学生数FROMStudentWHEREsDept='CS'
查询共有几个系
SELECTCOUNT(sDept)院系FROMstudent〔x〕
SELECTCOUNT(DISTINCTsdept)院系FROMStudent
求课程C1的最高分和最低分以及上下分之间的差距
SELECTMAX(Grade)最高分,MIN(Grade)最低分,MAX(Grade)-MIN(Grade)分差FROMScWHERECno='2'
查各个课程号与相应的选课人数:
Selectcno课程号,count(sno)as人数fromscgroupbycno
或
SELECTCno课程号,COUNT(*)人数FROMScGROUPBYCno
[例6]查选修了3门以上课程的学生学号
Selectsnofromscgroupbysnohavingcount(*)>3
[例6]在数据库中添加了数据
Selectsnofromscgroupbysnohavingcount(*)>3
[例6]查询出至少选修了4门课程的学号和门数
SELECTSno,COUNT(Sno)课程门数FROMScGROUPBYSnoHAVINGCOUNT(Sno)>=4
求选课在4门以上的平均成绩,不统计不及格的课程,按降序排列总成绩
SELECTSno,AVG(Grade)平均成绩FROMScWHEREGrade>=60GROUPBYSnoHAVINGCOUNT(*)>=4ORDERBY平均成绩desc
或
[例6]SELECTSno,AVG(Grade)平均成绩FROMScWHEREGrade>=60GROUPBYSnoHAVINGCOUNT(*)>=4ORDERBY2DESC
2.2连接查询
一般连接〔广义笛卡尔集〕
selectstudent.*,sc.*fromstudent,sc;
非等值连接〔连接条件是不等号〕
selectstudent.*,sc.*fromstudent,scwherestudent.sno>sc.sno
查每个学生及选课情况
selectstudent.*,sc.*fromstudent,scwherestudent.sno=sc.sno
2.3嵌套查询
2.4集合查询
3数据更新
3.1插入数据
[例6]insertintostudent(sno,sname,ssex,sdept,sage)
values('200215121','李勇','男','CS',20)
(1行受影响)
insertintostudent(sno,sname,ssex,sdept,sage)
values('200215122','刘晨','女','CS',19)
insertintostudent(sno,sname,ssex,sdept,sage)
values('200215123','王敏','女','MA',18)
insertintostudent(sno,sname,ssex,sdept,sage)
values('200215124','张立','男','IS',19)
[例6]
insertintoCourse
values('1','数据库','5',4)
(1行受影响)
批量命令执行
insertintoCourse
values('2','数学',NULL,2)
insertintoCourse
values('3','信息系统','1',4)
insertintoCourse
values('4','操作系统','6',3)
insertintoCourse
values('5','数据构造','7',4)
insertintoCourse
values('6','数据处理',NULL,2)
insertintoCourse
values('7','PASCAL语言','6',4)
insertintosc
values('200215121','1',92)
批量命令执行
insertintosc
values('200215121','2',85)
insertintosc
values('200215121','3',88)
insertintosc
values('200215122','1',90)
insertintosc
values('200215122','1',80)
(1行受影响)
(1行受影响)
(1行受影响)
(1行受影响)
[例6]
[例6]
[例6]
[例6]
3.2修改数据
updatestudentsetsdept='MA'
wheresno='200215123'
[例6]
[例6]
[例6]
[例6]
3.3删除数据
4、视图
4.1定义视图
4.2查询视图
4.3更新视图
[例21]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
创立触发器
当插入一个学生student时,触发表studnum学生人数自动计数
在student创立一个Insert触发器
createtriggerT_addnumonstudent
forinsertas
updatestudnumsetstudnum=studnum+1
在插入前查询
select*fromstudnum
插入
insertintostudent(sno,sname)values('200215134','测试')
select*fromstudnum
数据库的保存:
1、为了在不同电脑上使用自己创立的数据库,对数据库进展复制和拷贝,需通过数据库的“别离〞后,将文件拷贝到U盘,然后通过数据库的“附加〞,选择相应途径添加上去。
别离后在原数据库中不在出现,除非在通过数据库的“附加〞,选择相应途径添加上去
2、为了保存自己上次在同一电脑上的数据库操作,可利用数据库的快照,详细操作如下:
Createdatabasedatabase_snapshot_name‘自己对数据库快照命名
On
(Name=logical_snapshot_name,Filename=’os_file_name’)‘自己对数据库快照存取途径和文件命名,可与database_snapshot_name同名
[,......n]
AsSnapshotofsourse_database_name
绿色表示的内容要求内在的文件在数据库中实际存在的。
[例6]
createdatabaseteststudcour3_snap
on
(name=teststudcour3,filename='f:
\teststudcour3_snap.mdf')
assnapshotofteststudcour3
一个数据库建立多个快照时,在同一时间只能存在一个数据库快照,所以要建立下一个快照时必需删除前面所建的快照。
[例6]
restoredatabaseteststudcour3
fromdatabase_snapshot='teststudcour3_snap'
在恢复的过程中需注意:
1、在置于master的环境下
2、无需快照的途径名
数据库快照的限制
1、不能对数据库进展删除、别离或复原
2、源数据库的性能受影响
3、源数据不能在任何快照中删除文件
4、源数据库必须处于在线状态
数据库的复制
选择数据库,单击“右键〞,选择“任务〞→“复制数据库〞,翻开复制数据库向导,根据数据库向导提示操作,源数据库效劳器,默认的名称和使用WINDOWS身份验证,目的效劳器,选择“LOCAL〞和使用WINDOWS身份验证,选择“使用别离和附加方法〞,(根据提示要启动SQLServerManager10.msc.),选择要复制和挪动的数据库,在目的数据库文件的途径要选择存取途径,如“U盘〞,根据提示,,选择“立即运行〞,到“完成〞。
复制后恢复有待解决
数据库的备份后的恢复有待解决
临时表的创立
临时表分为本地临时表和全局临时表
临时表不能永久保存的表,存在数据库的缓冲区
本地临时表名称一个#打头,对当前的用户连接可见,断开连接自动删除
全局临时表名称以两个##打头,对所有的用户连接可见,断开连接自动删除
[例6]本地临时表createtable#test(idintnotnull,namenchar(10)null,addressnvarchar(50)null)
[例6]全局临时表createtable##test(idintnotnull,namenchar(10)null,addressnvarchar(50)null
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
[例6]
1、说明:
创立数据库
CREATEDATABASEdatabase-name
2、说明:
删除数据库
dropdatabasedbname
3、说明:
备份sqlserver
---创立备份数据的device
USEmaster
EXECsp_addumpdevice'disk','testBack','cmssql7backupMyNwind_1.dat'
---开始备份
BACKUPDATABASEpubsTOtestBack
4、说明:
创立新表
createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)
根据已有的表创立新表:
A:
createtabletab_newliketab_old(使用旧表创立新表)
B:
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
5、说明:
删除新表:
droptabletabname
6、说明:
增加一个列:
Altertabletabnameaddcolumncoltype
注:
列增加后将不能删除。
DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、说明:
添加主键:
Altertabletabnameaddprimarykey(col)
说明:
删除主键:
Altertabletabnamedropprimarykey(col)
8、说明:
创立索引:
create[unique]indexidxnameontabname(col….)
删除索引:
dropindexidxname
注:
索引是不可更改的,想更改必须删除重新建。
9、说明:
创立视图:
createviewviewnameasselectstatement
删除视图:
dropviewviewname
10、说明:
几个简单的根本的sql语句
选择:
selectfromtable1where范围
插入:
insertintotable1(field1,field2)values(value1,value2)
删除:
deletefromtable1where范围
更新:
updatetable1setfield1=value1where范
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 sql 实验
![提示](https://static.bdocx.com/images/bang_tan.gif)