数据库系统sql实验.docx

数据库系统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范